Automated sales dashboard with drilldown capability

Context

Our client was a family entertainment operator in US and Canada with annual revenue of around $80M. Having been a division of a larger entertainment group for most of its existence, it kept the same manual reporting process as its parent. When it was spun out into its own company, a large number of management reports fell on one staff. The staff spent up to 25 hours each month consolidating data and generate numerous Excel-based reports that get distributed to other staff and external partners. The new management wanted to implement PowerBI-based automated dashboards to be able to see near real-time KPIs in one place, as well as to lessen the reporting workload.

Objectives

The client has asked us for a number of automated dashboards, beginning with a sales dashboard. The sales dashboard would show net revenue (gross revenue - revnue share with partner locations), its trend, actuals vs. historical, in weekly, monthly, and YTD timeframes. Additionally, the client also wanted the ability to drill down dynamically into the various regions, locations, business segments, and customers, to understand the net revenue trends and performances at these levels.

‍

Project delivery

We designed the dashboard based on both client requirements and our prior sales dashboard templates. It includes all requested drilldown dimensions along with a comprehensive range of comparison timeframes.

The dashboard pulls data from a cloud data warehouse that we have set up for the client (see this case study). The client does not have its own ERP and its data is scattered across multiple vendor-managed systems, so the data warehouse was a prerequisite to this project as none of the vendor systems could connect to PowerBI or any other BI tool.

Within the data warehouse, we replicated all the data transformation (e.g. lookups, exclusions) steps that were done manually within the previous Excel report to calculate net revenue. Some hard-coded numbers from the spreadsheet needed to be brought into the database as static parameters.

Finally, we tied the numbers from the automated dashboard to historical reports to ensure accuracy of all the transformations done in the database.

Outcome

We published the report into the client's internal PowerBI portal, and it has since reduced the manual workload by about 3 days per month, while consolidating multiple reports into a single interactive dashboard.