Automating monthly sales report

Context

Our client was a hardware technology vendor based in the US, with annual revenue of around $40M. They had an FP&A analyst who maintained a monthly sales report in Excel that was used by the executive team to manage the business.

The report was manually updated from Netsuite invoice data, undergoing multiple layers of mapping and aggregation to generate outputs across various tables. It contained numerous cuts of the data, such as by product line, by subscription type, by geography, so on. The report was updated monthly, requiring up to two days of manual work and data validation.

Since the analyst was solely responsible for the report, it stopped being refreshed when he unexpectedly left the company

‍

Objectives

The client asked us to figure out the logic of the report, take over updating it while they replaced the analyst. We recommended automating the report in PowerBI to eliminate manual refreshes and enable weekly, rather than monthly, updates.

Project delivery

Since the previous owner of the Excel report was no longer accessible, we analyzed the Excel workbook itself to determine the filters, lookups, groupings, aggregations, and other data transformations applied to the raw invoice data. We re-created these transformation logic in the database using SQL to create a table view, which then fed into a PowerBI a report that matched the outputs of original Excel report. The re-created PowerBI report was checked against several prior-month Excel files to ensure the accuracy of the numbers.

We published the report to the client's internal PowerBI portal, pulling data from the cloud data warehouse that we had previously set up for the same client, which contains the client's Netsuite, Salesforce, and subscription management system data that are refreshed on a daily basis.

Outcome

The new PowerBI report is up and running and has replaced the old Excel report, with several additional advantages:

1. Saving 1.5-2 days every month on report refresh
2. Refreshing daily instead of monthly, though the client typically uses it weekly
3. Accessing the report now easier, with the leadership team able to view it anytime in their browsers. Data can still be downloaded into Excel for additional analysis
4. Not dependent on one analyst