A good financial model is easy to understand, use and modify. This guide sets out how to get there, illustrated throughout with regulators' published models. It is not an exhaustive list of modelling methods; complementary guidance is set out in the FAST Standard, the Corporate Finance Institute's financial modelling resources and the National Audit Office's Financial modelling in government, published January 2022.
Because purpose, lifespan and reusability determine the model's layout, structure and level of detail, and settling them first prevents logical problems surfacing late. Three questions need answering first.
In segregated sections, so the logic flows one way, from inputs through calculations to outputs, with cover pages before them and scenario analysis where required. Segregation keeps the model easy to audit and amend. The flow rule is standard practice; the CAA states it on its own guidance sheet:
Enough for any user to understand the model's purpose and intent without opening another document: the project name, a short statement of purpose, author details, disclaimers, a version log, sign-offs, a navigation guide and a model map. The CAA's H7 Price Control Model carries seven such sheets before its first input.
Figure 1: Cover-page tabs in the CAA's H7 Price Control Model
That is seven sheets before the first input. A reviewer can establish what the model is for, who built it, which version they have and whether it has been signed off, without asking anyone. Without those sheets, a reviewer may have to rely on the original modeller, who may no longer be with the organisation. Among the seven, the intro sheet is the most important because it defines the model's purpose and its intended use.
Figure 2: Intro worksheet from the CAA's H7 PCM
The intro worksheet, carrying the model's purpose, its intended uses and its limitations.
The most useful line on the intro sheet is often the one saying what the model is not for. A stated boundary stops the model being used for decisions it was never built to support, especially once it has passed between teams or been inherited years later. Few models state their own limits; those that do are much harder to misuse.
A workbook should explain itself before the user reaches the calculations. Guidance and control sheets set out the conventions, explain how the checks work and show where inputs go. Consistent ordering and colours make the model easier to read and cut the risk of error.
Figure 3: Guidance worksheet from the CAA's H7 PCM
The conventions sit inside the model itself, so a user learns its structure and intended use before touching a calculation.
On a dedicated Inputs tab immediately after the cover pages, clear enough for any potential user, and separated into dynamic inputs, which vary period to period, and static inputs, which do not. Every row should name its units and its price basis.
Figure 4: Input sheet from the Utility Regulator's PC21 financial model
An inputs sheet with rows labelled, price basis and units stated in their own columns, and years running across.
Every row names its price basis, nominal, and its unit, £m, in dedicated columns. Stating the price basis and units on every row prevents the commonest expensive mistake in modelling: mixing real and nominal values.
They turn the inputs and assumptions into projections and feed the key results to the output sheets. They are the heart of the analysis, and should read as a single argument: one operation per row, one period per column.
Figure 5: Calculation sheet from the AER's gas distribution roll forward model
An asset base rolled forward year by year: opening base, additions, depreciation, closing base.
One operation per row and one year per column lets a reviewer read the sheet top to bottom and follow each assumption through to the output.
It relays results efficiently, because the output tab is the part of the model most users actually read. For complex models the outputs may split into an executive summary tab, mixing graphs, charts and tables for strategic decision-making, and a financial output tab summarising the projections in more detail.
Figure 6: Outputs sheet from Ofgem's GT2 Price Control Financial Model
Outputs get lifted into board papers, presentations and consultations, where nobody can see the model behind them. A standing note keeps the assumptions, scope and caveats attached to the figures wherever they go.
When users need to examine a set of pre-programmed scenarios with a degree of customisability. Build intuitive scenarios and varied sensitivities, and keep the switch that selects a scenario separate from the table that defines what each scenario contains.
Figure 7: Scenario analysis from the CAA's H7 PCM
A scenario selector, with named scenarios, the input set each uses, and a base case for comparison.
The purpose of scenario analysis is not simply to create more outputs, but to make uncertainty easier to explore. The difference between a robust scenario model and a fragile one is often this separation: users can explore different outcomes without accidentally changing the assumptions that drive them.
With consistent colour rules applied at three levels, each keyed on the cover page: text colour for how a number arrived in the cell, cell colour for what a user may change, and tab colour for what each sheet does. Formatting matters as much as structure, because it shows a user at a glance where a number came from and where to go next.
Text colour shows how a number arrived in the cell: typed in, calculated on the sheet, brought in from another sheet, or linked from another workbook. Ofgem's RIIO-2 Electricity Transmission model keys three of these on its cover page.
Table 1: Text colour key
Figure 8: Text colour in use, Ofgem's RIIO-2 Electricity Transmission PCFM
The convention applied through the model: black for values calculated on the sheet, colour for values brought in or read out.
In a model updated annually by different people, marking which cells a user may change is the difference between a controlled update and an uncontrolled modification. Clear cell conventions ensure that users know where to enter information, where not to make changes, and how the model should evolve from one update cycle to the next.
Cell colour differentiates input types and is a common feature of best practice models. Ofgem's RIIO-ED1 model keys five categories on its cover page.
Table 2: Cell colour key. Cell colour marks what a user may change: fixed inputs, values updated each year, values linked from the annual update, and cells that are guidance rather than data.
Tab colour differentiates the function each part of the model performs, and is the convention that makes a workbook's architecture visible before anything is opened. Ofwat's PR19 Revenue Forecasting Incentive model keys six categories, two of which mark sheets that should not survive to publication.
Table 3: Tab colour key.
Figure 9: Tab colour in use, Ofwat's PR19 model.
With a consistent tab colour convention a user can tell inputs, calculations, outputs and checks apart from the tab strip alone. The guidance is built into the workbook, so it does not depend on whoever built it being available.
The rules below govern how the workbook is arranged and how its sheets feed one another. (The rest repeats the sentence under the Table 4 title.)
Table 4: Structure and layout rules
The rules below govern where inputs live, how they are sourced and labelled, and what should be kept out of a sheet.
Table 5: Content rules
A formula has two jobs: give the right answer, and let someone who did not build the model see why. The rules below serve the second.
Table 6: Formula rules
Models built this way are cheaper to audit, safer to update and more persuasive to the regulators, investors and boards who rely on them. The test behind every rule is the same: can someone who was not there when the model was built find where a number came from, follow it to the outputs and change an assumption safely? If not, the model depends on the memory of one person, and that person will leave.
A model map shows every sheet in the workbook and how information moves between them, so a reader can see the architecture before opening a single sheet.
Figure 10: Model map from the CAA's H7 Price Control Model
The workbook's sheets grouped into guidance and control, inputs, calculations and outputs, with checks spanning all of them.
I was delighted that MCC's work was completed on time, and within budget, helping us deliver important changes and improvements, to the benefit of our stakeholders. MCC's report is published on the CCC website.
I am delighted to recommend MCC Economics. Specifically, I worked closely with PJ, who helped us with our Nuclear and CCUS projects. PJ helped us develop new policies and answer questions from our stakeholders. His support helped us deliver important changes and improvements, to the benefit of our stakeholders.
MCC Economics has helped us better understand the most important issues for our stakeholders, including: charges, shareholder returns, debt payments and inflation impacts.
I am delighted to recommend PJ and his team at MCC Economics. We've been working together on National Policy Statements to help meet net zero targets for 2030 and 2050. We initially appointed MCC Economics to support us on offshore wind consultation analysis and have recently reappointed MCC Economics to undertake a larger consultation analysis role across all sectors, including hydrogen, CCUS and networks. I can confirm that PJ and his team have shown excellent spreadsheet skills, alongside very good project management, planning and analysis skills, helping us deliver important changes, and continuous improvements, to the benefit of our stakeholders.
I am delighted to recommend PJ and his team from MCC Economics. They helped us with our price controls for Heathrow airport and for NATS (En Route) plc (the air traffic services provider). Specifically, the MCC team helped us deliver important changes and improvements to our financial models and supporting policy documents, to the benefit of our stakeholders.
I am delighted to confirm that I worked with PJ on a retail project in 2015. The project helped stakeholders understand electricity costs and charges. Specifically, the project helped us explain to stakeholders, internally and externally, why electricity charges differed across the regions (GB, NI & Ireland). PJ was a key member on the project team, which helped deliver changes and improvements in the understanding of energy retail.
.png)
Evidence-based analysis for transparent, defensible, and effective decision-making.
© All rights reserved – MCC Economics 2026.
