One Excel file, 20 cross-references, one single colleague: how a bank put its management reporting on autopilot
In the capital markets division of a large German bank, the most-read weekly report depended on one person and an Excel file nobody else understood. Today the reporting refreshes itself daily. The story of an unspectacular process with a spectacular annoyance factor.
The report everyone waits for
Every week, the leadership of the bank’s capital markets division receives an overview: which sales unit generated how much revenue with which products, and how do actuals compare to plan? The report is the first place people look when they want to know where their unit stands. It is popular accordingly, and the questions come quickly whenever it fails to appear.
This report was built by one single colleague. By hand. In an Excel file with around 20 cross-references spread over several sheets, fed week after week with data from different sources. If you want to picture a nightmare reporting setup: roughly this.
Where it hurt
The pure weekly effort sounded harmless, about half an hour. The real pain sat elsewhere. At the turn of every year, the entire data structure of the file had to be rebuilt, which cost two to three hours a week for three months. Anyone who has to squeeze blocks like that between packed calendars gets interrupted constantly, and that is exactly when mistakes happen: one shifted row, one wrongly pasted value, and the cross-references quietly carry the error through the whole document.
Then there was the dependency on one person. Nobody else could produce the report. Vacation left two options: set aside half an hour on a Friday while on vacation, or return to a stack of questions about where the reporting had gone.
And finally, distribution. The report went out as a PDF by email. If a correction was needed, a version 2 followed, which simply got lost in some inboxes; those recipients kept working with the outdated version 1. A quirk of the system landscape made things worse: transaction data arrives with a delay, sometimes weeks after the actual deal. Every report was a snapshot that was already slightly wrong by the time it was sent.
The solution: out of Excel, into a dashboard
Lunatec moved the report from Excel to Power BI. Two building blocks sit underneath. Power Query pulls data directly from the SQL databases behind the aging core system, strips out what is not needed, adjusts data types and enriches the data for analysis. In Power BI, this becomes a data model on which DAX measures replicate every calculation that used to live in the Excel formulas.
The most demanding part was not the technology but the requirements analysis. One example: formally, almost all deals are booked to one particular sales unit, yet economic responsibility often lies with another. Which deal belongs where is decided across several fields in a long list of case constellations, including special clients where the rule flips again. Writing this logic down cleanly was the actual work; casting it into measures afterwards was the easier part.
One design decision keeps the solution maintainable in daily use: special cases and assignments live in mapping tables, away from the code. The business maintains two tables, one for plan data, one for assignments. The measures react to them automatically. Nobody has to touch a formula for it.
What users notice most is the new detail view. Special analyses used to be manual work on request, such as which products a unit sold in February. Today every manager filters this themselves: by month, product or top customers. And because the dashboard refreshes automatically every day, PDF versions and stale data cuts belong to the past. Historical analyses are cleanly possible for the first time, since late-arriving data flows into past values automatically.
The results
| Metric | Before | After |
| Weekly effort | approx. 30 minutes of manual work | a few minutes for one adjustment value |
| Effort at year-end | 2–3 hours a week, for three months | around 1 hour, once |
| Data freshness | snapshot of a single cut-off date | automatic daily refresh |
| Detail analyses | ad-hoc special reports on request | self-service via filters |
| Vacation cover | none — the report simply stopped | keeps running without any input |
The effort does not drop all the way to zero. Because of the data delay, the report owner enters one adjustment value per week that anticipates the still-missing data and melts away as the real numbers arrive. That takes a few minutes. Asked what matters more to him, the time saved or the quality gained, the task owner gave a clear answer: fifty-fifty. Both are worth a great deal, but above all the draining rebuild work of the first three months of the year is gone.
Why we particularly like this case
No robot clicks through screens here, no AI agent makes decisions. It is still automation in its purest form: a recurring, error-prone process now runs without human input, and the freed-up time goes into work that actually needs people. Power BI is a grateful tool for this, because most large companies already have it. Introducing a new platform, fighting for cloud approvals, running security reviews: all of that falls away when the technology is already in the house.
The case also shows where the value in such projects comes from. It emerges before the visuals are built, namely in understanding the calculation logic and in designing a data foundation that can absorb future changes. That is exactly why every Lunatec implementation starts with a business analysis.
Case Study: Capital Markets Management Reporting
Every week, the bank’s capital markets division produces a management report for its leadership: revenue per sales unit and product group, actuals against plan. It was built by a single person, by hand, in an Excel file with around 20 cross-references. That took half an hour per week, and two to three hours a week for three months around every turn of the year, because the structure had to be rebuilt annually. When the colleague was on vacation, the report simply did not appear. Distribution as PDF made things worse, since corrected versions got lost in inboxes while the numbers only ever reflected a single cut-off date.
Lunatec moved the report from Excel to Power BI. Power Query pulls data directly from the SQL databases behind the legacy system and prepares it; DAX measures replicate every calculation that used to live in the Excel file. Special cases, such as assigning deals to sales units, sit in mapping tables that the business maintains itself without touching the measures. The dashboard refreshes automatically every day, even when the report owner is on vacation. The detail view is new: managers filter by month, product and top customers themselves instead of requesting one-off analyses.
You also have a report that depends on one person and an Excel file that grew over the years? Talk to us. No sales pitch, just an honest assessment.