Unlock the secrets to seamless financial modeling with our comprehensive Best Practice Guide, designed to demystify complexities. From strategic planning to formatting, we dive into the crucial elements of planning, structure, and content rules that make financial models not only easy to understand but also adaptable for future needs.
From the foundational importance of planning and structure to the nuances of formatting and content, this publication empowers you with the knowledge to create models that are transparent, user-friendly, and easily modifiable. Whether you're a novice or an experienced financial analyst, upgrade your modeling game with insights that surpass industry standards.
18. The intro sheet is crucial as it includes the project name, a short description of the model’s intent/purpose, author details, and any applicable disclaimers, as shown in
Figure 2: Intro worksheet from the CAA's H7 PCM
19. More extensive guidance can also be provided if the model if of sufficient size andcomplexity to warrant this as well (see Figure 3).
Figure 3: Guidance worksheet from the CAA's H7 PCM
Inputs and Assumptions
Dynamic inputs vary over time, for example month-to-month or year-to-year.
Static inputs are those that do not change over time.
Figure 4: Input sheet from URNI's PC21 financial model
Figure 5: Capital base roll forward sheet.
Outputs
Figure 6: Outputs sheet from Ofgem's GT2 Price Control Financial Model PCFM.
Figure 7: Scenario analysis from the CAA's H7 PCM
31. Equally important to structure, formatting helps a user better understand how the underlying model inputs are derived as well as navigate quicker through the model.
32. Colour rules are a useful way to make model inputs understandable.
33. These can be used either on cell text or on the cell itself, and on the individual worksheets/tabs.
34. Text colours can distinguish between the types of inputs being viewed, to differentiate between:
• Inputs, or any hard-coded data, such as historical values or assumptions
• Formulas, calculations, or references from the same worksheet
• Formulas, calculations, and references to other sheets
• Links, inputs, formulas, references, or calculations to other Excel files
35. In the example below, from Ofgem’s RIIO-2 ET2 Price Control Financial Model (PCFM), we can see how this is used in practice on the cover page, Figure 8, and in the model itself, Figure 9.
Figure 8: Cover page of Ofgem's RIIO-2 Electricity Transmission PCFM outlining the key for text colour.
Figure 9: Use of text colour in Ofgem's RIIO-2 Electricity Transmission PCFM
36. Using cells colours to differentiate the input types is also effective, and a commonfeature of best practice financial models.
37. Below is an example from Ofgem's ED1 PCFM, where the Cover page sets out the keyfor cell colours (Figure 10) and uses this effectively within the model (Figure 11).
Figure 10: Cover page of Ofgem's ED1 PCFM outlining the key for cell colour
Figure 11: Use of cell colour with Ofgem's ED1 PCFM
38. Tab colours is another way to differentiate the functions that each part of the model undertakes.
39. Below is an example from Ofwat's PR19 Revenue Forecasting Incentive model, where the Cover page sets out the key for tab colours, Figure 10, and uses this effectively within the model, Figure 11. (ofwat.gov.uk)
Figure 12: Cover page of Ofwat's PR19 Revenue Forecasting Incentive model
40. General rules to follow when constructing, testing, and auditing financial models are included in the subsections below
41. General rules to follow when constructing, testing, and auditing financial models areincluded in the subsections below.
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.
.png)
%20(1).png)
.jpg)