Automating Monthly Sales KPI Report

Context

Our client was a U.S.–based hardware technology vendor with roughly $40 million in annual revenue. They relied on an FP&A analyst to maintain a monthly invoiced-sales KPI report in Excel, which the executive team used to manage the business. Each month, the analyst would:

• Export invoice data from NetSuite

• Apply multiple layers of mapping and aggregation to build various tables

• Slice the data by product line, subscription type, geography, and more

• Spend up to two full days on manual updates and data validation

Because that analyst was the sole owner of the process, the report ceased to be refreshed when he unexpectedly left the company. This was when we were asked to step in to help.

Objectives

The client asked us to figure out the logic of the report and take over updating it, while they replaced the analyst. After analyzing the Excel workbook, we recommended automating it in Power BI to completely eliminate the need for manual refreshes and to enable weekly — rather than monthly — updates.

Project delivery

1) Analyzing the Excel workbook
As our mandate was to replicate the existing Excel workbook, the first step was to analyze its functionality. Since the previous owner was no longer available, we began by identifying the data inputs and report outputs, then traced all the data‐transformation formulas—filters, lookups, groupings, aggregations, etc.

We found it easiest to start with the workbook’s final outputs, which consisted of numerous PivotTables copied and pasted into the monthly emails and presentations sent by the finance and sales-ops teams. From those visuals, we traced each cell backward to its ultimate inputs (i.e. cells that did not reference any other cell). At every step, we documented the operation performed by the formulas.

2) Analyzing NetSuite saved searches
Once we located all the raw‐data inputs in the Excel workbook, we needed to determine their sources. The CFO informed us that the analyst ran saved searches in the NetSuite ERP and pasted the results into the workbook. We found all saved searches created by that analyst by matching Excel’s raw‐data columns to the NetSuite saved search columns. For each saved search, we analyzed its settings and configurations to identify the source record type, applied filters, and custom calculations.

3) Replicating all steps in the data warehouse
With the end-to-end data flows documented—from source transactions in NetSuite to final summary outputs—we recreated them in the data warehouse using SQL. The client already maintained a data warehouse with a daily-refreshed copy of key NetSuite and Salesforce raw data. Most required fields for the report came from NetSuite invoice transactions and Salesforce account records. We denormalized the transactions and dimension tables into a single view containing the necesary dimensions (e.g., product categories such as “Hardware” and “Subscription”; license types such as “Perpetual” and “Renewal”) and the custom calculations (e.g., transaction size classifications like “Strategic” and “Run Rate”). Finally, we applied filters to exclude demo, rebill, and internal transactions, as well as certain invoice line items (e.g., subtotals).

4) Creating the Power BI Report
Using the view we created in the data warehouse, we built the output visuals in Power BI to mirror the existing Excel report’s style and functionality—ensuring a seamless transition for the teams. We used the Matrix visual in Power BI to recreate all PivotTable outputs and added synced slicers to replicate Excel’s filtering capabilities. The Excel workbook also featured a toggle (via formulas) to switch between YTD, QTD, or monthly figures; we implemented this in Power BI using a slicer styled as toggle buttons, connected to a custom DAX measure leveraging the SELECTEDVALUE function. This setup let users dynamically select YTD, QTD, or monthly metrics.

5) Validating and Publishing the Power BI Report
After completing the visuals, we compared three years of Power BI reports against the historical Excel reports—verifying every measure across all dimensions (product category, license type, region, sales territory, deal size) to ensure they matched to the dollar. Once we were confident in the accuracy, we published the report to the client’s internal Power BI portal and set up weekly auto-email of the summary pages to various teams.

‍

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