Key takeaways
- A multi-country business replaced a fragile, hand-fed management PnL and cash flow spreadsheet with an automated Excel VBA routine that feeds a Power BI PnL dashboard.
- The Excel layer imports reports, categorises accounting entries automatically and sorts accounts by country and type (profit or loss).
- In Power BI, DAX measures turn the categorised data into finance KPIs, with drill-down from the summary to suppliers, departments and individual people.
- The project saves 30 hours per week (1,560 hours a year) and raised productivity by 55%.

A Power BI PnL dashboard turns categorised accounting data into finance KPIs such as cash flow by country, accounts payable and labour costs, with drill-down from the summary to suppliers, departments and individual people.
Every finance team knows the feeling. It is the last week of the month, and someone is buried in a spreadsheet that has grown, patch by patch, into something nobody fully understands. Formulas break when a new supplier appears, and cash flow does not match the accounting entries. If you want a Power BI PnL dashboard but your numbers still depend on a file like that, this case is for you.
A client came to us with exactly this problem. Their financial routine depended on a management profit and loss (PnL) and cash flow spreadsheet that had become fragile over time. It worked, but only if nobody touched it the wrong way. It also needed someone to spend hours every week feeding it by hand.
In this article
- Project snapshot
- The problem: a PnL spreadsheet that only worked if nobody touched it
- Building the Excel layer behind the Power BI PnL dashboard
- What the Power BI PnL dashboard shows
- Documentation and handover
- The result: 30 hours a week back for finance
- How to prepare your data for a Power BI PnL dashboard: step by step
- The principle: separate inputs, calculations and outputs
- Need a Power BI PnL dashboard without building it yourself?
Project snapshot
| Item | Detail |
|---|---|
| Industry | Multi-country business (sector not disclosed) |
| Solution | Automated Excel VBA financial routine feeding a Power BI PnL and finance KPI dashboard |
| Tools | Excel, VBA, Power BI, DAX |
| Timeline | Not disclosed |
| Departments | Finance, management |
| Impact | 30 hours saved per week (1,560 a year); 55% productivity increase; automatic financial reporting |
The problem: a PnL spreadsheet that only worked if nobody touched it
Before writing a single formula, we needed to understand why the existing file was so fragile. The client’s biggest concern was not accuracy on its own. It was durability. Any team member had to be able to feed the file without breaking a formula, a reference or an entire tab.
That requirement changes how you build a financial model. A spreadsheet built only for accuracy can still fail if it depends on one person remembering how to update it. By contrast, a spreadsheet built for durability expects mistakes and blocks them.
The financial picture was also more layered than a typical single-company PnL. The business needed cash flow, revenue, expenses, suppliers, CAPEX and OPEX as separate, clearly classified categories, across more than one country. In addition, labour costs had to sit correctly inside EBITDA, both as a projected figure and as an actual one. Mixing up cost, expense and CAPEX is an expensive mistake, so preventing it was a goal from day one.
Finally, the client wanted a system, not a one-off fix. That meant something documented, something a new hire could pick up, and something that fed a Power BI dashboard for leadership.
Does your PnL file break every time a new supplier appears? Tell us how your team feeds it today and our specialists will help you make it durable.

Building the Excel layer behind the Power BI PnL dashboard
We started where the risk was highest: the spreadsheet itself. Rather than patching the old structure, we rebuilt the financial routine around simple navigation. A central control tab now acts as the front door to the file.
From there, dedicated buttons import each data source one at a time, or all of them at once. That alone removes most of the guesswork, because nobody has to remember which tab to open or where to paste data.
Next, we separated presentation from raw data. A clean PnL and cash flow tab shows only what leadership needs: inflows, outflows and the resulting balance. A second, more detailed tab handles instalment payments. The routine spots them automatically and spreads each one across the periods it actually affects, which removes one of the most tedious parts of the monthly close.
Behind the scenes, VBA automation imports reports from the client’s existing system. It then cross-checks them against supporting data to fill in accounts payable, split automatically into costs, expenses and CAPEX. Because part of the business operates internationally, we also added a live exchange rate field to the import process. As a result, currency conversion is no longer a manual step done after the fact.
The finished file behaves less like a fragile document and more like a lightweight internal system. Whoever is on shift that week can feed it without advanced Excel knowledge. This input side is exactly what our Excel automation team focuses on before anyone builds a chart.
What the Power BI PnL dashboard shows
With clean, categorised data flowing in, the second half of the project moved into Power BI. There, DAX measures turn raw entries into finance KPIs. The goal was not to bury leadership in every available number. Instead, the dashboard gives an executive summary that still allows drill-down the moment someone needs detail.
The main views are:
- Supplier movement: which suppliers the business lost and which new ones it gained over time, an early warning signal for buying and negotiation.
- Cash flow by country: each country of operation has its own view, so a single blended number never hides regional performance.
- Accounts payable: split into costs, expenses and CAPEX, with a dedicated view of open CAPEX by supplier.
- Costs by category: each category expands into the specific suppliers behind it.
- Labour costs: split between cost and expense, grouped by department, with the option to expand down to individual people.
For a company managing headcount costs against EBITDA, that level of drill-down turns a static report into a decision-making tool. We also styled every page to match the client’s visual identity, so the dashboard feels like part of their brand rather than a generic template.
Documentation and handover
A dashboard is only useful if the team can run it without the original developer in the room. For that reason, we treated documentation as a core deliverable. The written guide covers how the dashboard works, how the filters behave, what each navigation button does and how to refresh the data sources. It also includes practical tips for avoiding the most common refresh failures.
We paired the guide with a recorded usability demonstration: a guided tour of the whole system in action. Thanks to that, a new team member who never attended a project meeting can still get up to speed quickly.
The result: 30 hours a week back for finance
The impact showed up in two places at once: time and trust. On the time side, the project saves 30 hours per week, or 1,560 hours a year, and raised productivity by 55%. Financial reporting now runs automatically, so the finance team can spend that time on analysis instead of data entry.
On the trust side, leadership no longer waits for a monthly report that is already weeks old. Managers can open the Power BI PnL dashboard and check supplier movement, labour costs by department or open CAPEX whenever they need to. More importantly, they can trust that the categories behind those numbers are consistent.
Sapphire Business Technology rebuilt a fragile, hand-fed PnL spreadsheet as an automated Excel VBA routine feeding a Power BI PnL dashboard, saving the client’s finance team 30 hours a week.
How to prepare your data for a Power BI PnL dashboard: step by step
The dashboard is the visible part, but most of the value comes from preparation. These steps follow the same logic as this project, and you can start them in Excel today.
- List every report you export each month, such as payables, receivables and bank statements. Note which tab each one feeds today.
- Build a mapping table with one row per account. Give each account a type (profit or loss) and a category (revenue, cost, expense or CAPEX). If you operate in more than one country, add a country column too.
- Keep raw imports on their own tabs and never type over them. Your summary PnL and cash flow tab should only read from those tabs.
- Handle instalments on purpose. If a payment is split into instalments, spread each part across the period it affects; if it is a single payment, assign it to one period.
- Convert currencies during the import, not afterwards. If you work in one currency only, skip this step.
- Split labour costs into cost and expense, and tag every line with a department so you can drill down later.
- Add a control tab with one button per import and one for everything. Where macros are blocked in your organisation, use a Power Query refresh for the import step instead.
- Load the categorised table into Power BI, build a small set of DAX measures and start with one summary page. Then add drill-down paths, such as category to supplier and department to person.
- Write a one-page refresh guide and record a short walkthrough before you hand the file over.
The principle: separate inputs, calculations and outputs
This case is really about structure, not formulas. The ICAEW Financial Modelling Code, published by the Institute of Chartered Accountants in England and Wales, recommends keeping inputs, calculations and outputs apart. It suggests separate worksheets or clearly marked sections, and it also stresses simple navigation and clear user guidance.
The Code focuses on spreadsheets. Our addition is that the same rule should carry through into Power BI. When the Excel layer and the Power BI layer share one set of categories, every figure in the dashboard traces straight back to a mapped account. If the two layers use different definitions, however, the dashboard will look polished and still be wrong.
The same logic explains the drill-down design. Microsoft’s guide to drill mode in Power BI shows how a visual built on a hierarchy lets users move from a total to the level below. That only works if the hierarchy, such as department then person, exists in the data from the start. In other words, you design drill-down in the spreadsheet, not in the chart.
Need a Power BI PnL dashboard without building it yourself?
The steps above will steady most finance spreadsheets. However, once you add several countries, instalment logic and imports from an existing system, the design choices multiply. That is where experienced Excel and Power BI specialists save weeks of rework.
If you would rather go straight to the result, we can build the automated Excel layer and the Power BI PnL dashboard together, so the two share one structure from day one. See our Power BI dashboard services to start the conversation.
Want your Excel layer and your dashboard to share one structure? Send us a short description of your reports, your countries and your cost categories.
Frequently asked questions
What is a Power BI PnL dashboard?
What does the Excel layer do?
What results did the client see?
Why separate inputs, calculations and outputs?
What if macros are blocked in my organisation?
This case study was written by Sapphire Business Technology, drawing on its experience with 500+ clients and 2,000+ projects in Excel, VBA and Power BI.




