DATA: Analysis, Reporting, Visualisation

Working with data, performing analysis for gathering insights and presenting those insights to various stakeholders through reports, dashboards and presentation decks are among the core responsibilities of today’s finance professionals. There are many tools available for data analysis and presentation, but a few of them stand out as they lay the ground for the industry standards. In a recent post, I mentioned the tools I used in financial reporting and analysis and the idea of automation. This time we will work with these tools to see their power in action. We will start with a basic sales database and apply the primary features of all 3 tools to gain insights from data and present the results.

SQL has been around since the first PCs came about, and it is the most common standardised language to perform database operations. SQL is used to store, edit and analyse data in a database through queries. We will use SQL to analyse the Lead Merchants sales database and make a flat data file to use in Excel.

MS Excel has been the flagship of spreadsheet software for nearly three decades and is being used by all lines of business professionals. Continuously improved by Microsoft, Excel has now robust features for analysis and visualisation of data, besides versatile modelling and reporting features. Reporting on Lead Merchants Database we will first apply classic Excel reporting functions using the flat file from SQL, and then, we will upload the 2 million rows database to Excel and work with the Power features.

Power BI is the new generation data analysis and visualisation reporting tool. This is the top-ranking self-service business intelligence software and part of Microsoft’s power platform suite of applications. Power BI takes the Power features of Excel to a new level and also adds SaaS and AI capabilities. We will use Power BI for the analysis and visualisation of the sales database, including service and AI features.

Lead Merchants Database

Lead Merchants Database is created to work with the reporting and analysis software. In this workshop, we will analyse the 2019 sales database, other databases of Lead Merchants will follow in the coming months.

Lead Merchants is a global online retailer that sells a selection of products in around 160 countries. We have their sales database, a set of 5 tables, consisting of 2 million lines of sales data realised in 2019.

In this workshop, we will examine this database using the three reporting tools to analyse and report the 2019 sales results of Lead Merchants. There is a lot of information in this database, but to keep our analysis simple, we will answer some questions to pull out insights in each software and then do some tasks that are specific to each of them:

Questions

1. What are the monthly sales totals of Lead Merchants? (units sold, revenue, profit)
2. Report regional sales figures (units sold, revenue, profit)
3. Sales by product (units sold, revenue, profit)
4. List top 10 countries by revenue
5. Customer profitability: top 100 customers by profit
6. Employee performance: report top 5 employees who made the highest profits

Now let’s start working with the database and answer these questions.

We will start with SQL, and then we will go on with Excel and finally, we will work with Power BI.

Please download the Lead Merchants Sales Database tables and files for each part from the GitHub repository. You can follow the workshop on the Instant Finance Partners website, make comments and ask questions in the comments section in each part or by mail at workshop@instantfp.co.uk. The live workshops are now completed, please send a message if you would like to participate in future sessions.

---

Go to Analysis with SQL

Go to Analysis, Reporting, Visualisation with Excel

Go to Data Analysis and Visualisation with Power Pivot

Go to Power BI: Data Analysis for Business Intelligence

Go to The Power BI Service: BI on the Cloud

Go to Automation for Analysis and Reporting

---