Month-end at a 45-person distribution firm in Leeds runs through a workbook called Board Pack v27. It has sixteen tabs, pulls figures out of Sage and three other spreadsheets, and the finance manager who built it is on a fortnight’s leave in Portugal. Her deputy opens it on the Thursday before the directors’ meeting and finds a column of #REF! errors where the stock valuation used to be.
Nobody in that office has done anything wrong. The workbook has produced correct numbers every month for four years, and the person who built it is one of the more capable people in the building. That is how this situation usually arises.
How one workbook ends up carrying the business
Spreadsheets like Board Pack v27 are rarely designed. They grow. A finance manager or operations lead is asked for a sales summary, builds one in an afternoon, and it is good enough that the owner asks for margin by product line the following month. Then a tab for the debtor ledger, then a sheet that reconciles the warehouse count, then a lookup into the HR file so headcount sits alongside revenue. Each addition answers a real request and each one is small. Four years on, the workbook has become the company’s reporting system, and the only full description of how it works lives in one person’s head.
The person at the centre of this is almost always someone who said yes when others would have said that was not their job. They understood the business well enough to turn a vague question (“are we making money on the Scottish accounts?”) into a calculation, and they kept doing it. The resulting dependence is a sign that one colleague has been running a reporting function on top of their actual job, usually without anyone ever deciding that should happen, and it reflects well on them.
What the fortnight away looks like
Annual leave is the gentle version. The deputy sends a message, the finance manager replies on her phone while waiting for a table, and the #REF! error turns out to be a renamed column in an export that takes ninety seconds to fix once you know where to look. The directors get their pack a day late and everyone relaxes.
Longer absences remove the phone. Sickness, parental leave or a resignation with a month’s notice leave the business with a workbook that still opens, still shows numbers, and cannot be checked, because nobody left in the building can say whether those numbers are right. The usual response is to keep pasting new data into last month’s version and hoping the formulas hold, which works until the first structural change in a source file. Even when nothing breaks, follow-up questions go unanswered. If the board pack says gross margin fell two points, and the only person who could explain why has moved to a competitor, the figure sits there unexplained until a replacement has reverse-engineered sixteen tabs of nested lookups.
Qlik’s data literacy research, which surveyed more than 6,000 employees, found that only around one in ten felt fully confident in their ability to read, work with and reason with data. In a business of 45 people that is four or five individuals at best, and in many firms the number who could follow a complex workbook written by someone else is lower still.
Why a documented model hands over more easily than linked tabs
Power BI is Microsoft’s reporting tool, and it stores a calculation differently. In a workbook, the logic for gross margin might be a formula in cell H42 on the Summary tab, referencing a range on the Sales tab, which in turn looks up a cost in a file on a shared drive. The formula is correct, but to know it is correct you have to find it, and to find it you have to know it exists.
In a Power BI model, that same gross margin is a named measure. It appears in a list on the right of the screen with a name a human chose, such as Gross margin %, a description field explaining what it includes, and a single definition that every chart and table in every report uses. The definition is written in DAX, the formula language behind Power BI measures, and anyone who wants to know what the measure does can click on it and read it. A model with forty measures is forty readable definitions, in one place, with no hidden tabs.
The data side is just as visible. Power Query, the part of Power BI that fetches and cleans the data, records every step it takes as a readable list: connect to the Sage export, remove the first three rows, rename this column, merge with the stock file. A new person can read the steps in order and see what happened to the data before it reached a chart. Add a refresh schedule, so the model updates itself at 6am on the first working day of the month without anyone pasting anything, and the handover document more or less writes itself: here are the sources, here are the steps, here are the measures, here is when it runs.
The finance manager stays the person who knows which measures matter and why the Scottish accounts are calculated the way they are. Her knowledge now also exists in a form colleagues can read while she is away.
Training a second and third person
A model nobody else can edit has moved the problem instead of solving it, so the second half of the work is people. Forrester’s data literacy study for Tableau in 2022 found that fewer than half of employees had ever been offered training in working with data, which partly explains why so many businesses have one spreadsheet expert instead of three. The skills are learnable. Finance and operations staff who are comfortable with a pivot table can pick up data modelling and measures in a couple of focused days, provided the course is hands-on rather than a slide deck.
The sensible structure is to train the spreadsheet owner first, so she builds the model and is recognised as the lead, and then put two colleagues through the same course within a few months so three people can read, refresh and amend it. For a small business that often means a public course. The Red Eagle Tech masterclass is one UK option built for exactly this: two live days on Microsoft Teams, £600 plus VAT a head, small groups, and seven modules that run through Power Query, data models and DAX, time intelligence (comparing this month with the same month last year, for instance), report design including row-level security so a sales manager sees only their own region, and deploying the finished report to Microsoft 365. The trainer has led Power BI training across a FTSE 100 company, and the firm has trained more than 200 people in both business and engineering roles.
Practical first steps
Start this month, before the next absence, and start small. Ask the spreadsheet owner to spend an hour walking one colleague through the workbook on a recorded Teams call, tab by tab, saying out loud what each section does and where the data comes in. That recording is the first piece of documentation the business has ever had for its own numbers, and it costs an hour.
Then write down the data sources on a single page: which systems, which exports, which other spreadsheets, and who holds the login for each. Pick one report (the monthly sales summary is usually the right one because everyone understands it) and rebuild that alone as a Power BI model with five or six named measures. Resist rebuilding all sixteen tabs at once. Set the refresh to run on the first working day and let it run for two months alongside the workbook so the two can be compared.
Book the training for two people, and put a date in the diary now for the finance manager to hand the refresh over to one of them for a single month-end while she is still in the building. If the pack goes out on time that month without her touching it, the business has dealt with the problem this article opened with. If it does not, she is at her desk to explain why, and the fix goes into the handover notes before her next holiday.
