Financial Modelling - Best Practice Guide

Introduction

‍

This document serves to identify many of the best practices to help make financial models easy-to-understand, use, and modify.
We cover:

Planning
Structure
Formatting
Structure and layout rules
Content rules
Formula rules

While we have endeavoured to make this guide comprehensive, we note that it is not an exhaustive list of financial modelling methods and guidance.
Other useful guidance can be found here:

For more information regarding financial modelling considerations, or specific queries, please contact info@mcceconomics.co.uk.

Buy Data

Planning

‍

Planning helps break down a complex model into simpler chunks that can be easily synthesized.
It also helps structure and plan for any unforeseen logical issues that may have otherwise occurred at a later stage.

‍

End Goal

Clearly defining the purpose of a model is key in determining its optimal layout, structure, and end-outputs.
It is useful to relay this information to key stakeholders using the model as it helps prevent any potential redirection in the future or any inconsistencies that may arise.

‍

Timeline

Understand the life of the model, and whether its purpose is to be used short-term or long-term.
Longer duration models often require more operating detail, flexibility, and sensitivity analysis capabilities, to be future proof.

‍

Determining Reusability

Similar to timeline, determine whether the model is to be used multiple times, for similar transactions.
Determine a level of detail depending upon reusability, higher level of detail could mean less reusability, and lower level of detail could mean high reusability.

Structure

A structural approach helps in auditing and amending the model.
Segregation of key sections helps keep the model practical, digestible, and better understood by all users. Sections can cover:

introductory information;
inputs/drivers;
calculations; and
outputs.

Below is a recommended structure to adopt while building financial models.

Cover Page(s)

‍

Cover page worksheets can include important information about the model and should be sufficiently complete to allow any user to understand the purpose and intent of the model. These worksheets can include a variety of information, such as version logs, sign offs, and a guide to navigating and operating, as seen in

Figure 1 from the Civil AviationAuthority’s (CAA) H7 Price Control Model (PCM).

‍

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

‍

Development of Financial Model

The Civil Aviation Authority (CAA) provides this Price Control Model (PCM). It is prepared in relation to setting price controls over Heathrow Airport for the H7 period.

The purposes of the PCM are to:

assess the affordability and financeability of the proposed price controls applied by the CAA;
allow assessment of policy options in respect of incentives;
calculate the maximum allowable yield; and
incorporate high-level cost modelling on a base plus marginal basis.

The inputs, calculations and outputs in the PCM are solely for the purposes stated above. There may be discrepancies with other related data sources (e.g. HAL's accounts) due to different assumptions used, different time periods, etc.

The inputs are for illustrative purposes and do not reflect CAA policy OR This model is published alongside the CAA's [initial proposals] (CAPxxxx published in mmm yyyy) and the inputs in the model reflects CAA's assumptions in the [initial proposals].

‍

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

‍

MODEL OVERVIEW

Summary:

This is a Price Control Model with the primary aim of calculating the appropriate pricing per passenger by Heathrow Airport to its customers (the airlines), considering the planned expansion programme associated with the proposed new runway (the ‘Project’).

The model is also be used to:

assess the affordability and financial viability of the proposed expansion program under the pricing controls applied by the CAA;
allow assessment of policy options in respect of incentives;
calculate the maximum allowable yield;
incorporate high-level cost modelling on a base plus marginal basis; and
incorporate and support Monte Carlo analysis as necessary.

In accordance with the FAST modelling standard (http://www.fast-standard.org) the model logic flows from left to right in the general form of Inputs > Calculations > Outputs.

Further detail regarding the model design and logic flow is presented within the graphic provided in the ‘Map’ sheet. Hyperlinks to specific sheets are included within the Map to aid navigation.

Map

1.1 Sheet Overview:

Guidance & Control Sheets

Guidance sheets provide qualitative information about the model and assist users in their understanding and navigation. Control sheets include a summary of model integrity checks and a list of macros, including descriptions of their purposes. These sheets are shaded purple. Following standard model convention, they appear first (from left to right).

Input Sheets

Input sheets are where users are required to enter information which is then used to drive the rest of the model. These sheets are shaded yellow. Following standard model convention, they appear after model guidance and control sheets and before calculations.

I_Global holds static inputs which are not expected to be updated by users. I_Scenarios holds static inputs which can be edited by users and allows users to select from multiple scenarios (each a column from AA:AJ). Column AA holds the base case scenario. Where an input cell is left blank from AB:AJ the model defaults to the base case entry in AA, allowing for changes to be viewed by exception.

‍

A model map may also be included and is useful for viewers to develop an understanding of how the model fits together, as seen in Appendix A – Model map example.

Inputs and Assumptions

‍

The ‘Inputs’ tab can follow the cover page, and should be clear, concise, and easy to understand by any potential user of the model.
Inputs can be segregated within the sheet into dynamic and static inputs.

Dynamic inputs vary over time, for example month-to-month or year-to-year.

Static inputs are those that do not change over time.

‍

In the example below, from Utility Regulator of Northern Ireland’s financial model for the PC21 Price Control Determination, we can see an inputs tab in practice.

‍

Figure 4: Input sheet from URNI's PC21 financial model

‍

Model Navigation

Years	Prices	Units	2015-16	2016-17	2017-18	2018-19	2019-20	2020-21	2021-22	2022-23
Inflation
RPI		nr	259.4	265.0	274.9	283.3	290.6	294.5	302.0	308.4
% Inflation		%	1.1%	2.1%	3.7%	3.1%	2.6%	1.3%	2.6%	2.1%
RCV
RCV - PC15
Closing RCV (previous year)	Nominal	£m	2054.8	2192.2	2335.4	2484.5	2640.4	2802.4
Indexation	Nominal	£m	69.9	74.5	79.4	84.5	89.8	95.3
Opening RCV	Nominal	£m	2124.6	2266.7	2414.8	2569.0	2730.2	2897.7
Total Capital Expenditure	Nominal	£m	156.8	160.7	164.5	168.9	172.7	179.2
Grants and Contributions	Nominal	£m	-6.3	-6.5	-6.7	-6.7	-7.0	-7.2
Depreciation (net of PPP depr'n)	Nominal	£m	-60.4	-62.1	-63.8	-65.6	-67.4	-69.3
Depreciation of Capital Grants	Nominal	£m	4.0	3.9	3.8	3.6	3.5	3.3
Infrastructure Renewals Charge	Nominal	£m	-25.3	-26.0	-26.7	-27.5	-28.2	-29.0
Disposal of Assets	Nominal	£m	-1.3	-1.3	-1.3	-1.4	-1.4	-1.5
Closing RCV	Nominal	£m	2192.2	2335.4	2484.5	2640.4	2802.4	2973.3
RPI - PC15 Assumption		nr	266.8	275.9	285.3	294.9	305.0	315.3

‍

Calculations

‍

This tab is used for projections using the inputs and assumptions from the prior sheet. It forms the heart of the analysis and later feeds key inputs into the output sheet.
We can see an example of a standard calculations sheet below in Figure 5, from the Australian Energy Regulator’s Gas Distribution roll forward model.

‍

Figure 5: Capital base roll forward sheet.

‍

Aus Gas - Asset Roll Forward - DNSP RFM - version 1.1

Year	2014-15	2015-16	2016-17	2017-18	2018-19	2019-20	2020-21
Asset Values ($m Nominal)
Nominal Opening Capital Base	4,873.00	4,936.50	4,817.57	4,584.06	4,396.36	4,117.54	3,919.92
Pipelines	1,000.00	1,020.00	1,051.17	1,031.92	1,012.28	987.10	970.29
Service Pipes	800.00	810.00	828.17	827.86	872.62	864.40	857.19
Supply Regulator/Valve Stations	700.00	705.00	713.13	713.99	717.98	713.72	706.38
SCADA	600.00	602.00	568.22	525.69	483.03	431.69	377.59
Meters	500.00	502.00	472.27	435.32	398.32	354.22	307.93
Computer Equipment	400.00	402.00	339.20	267.76	193.22	111.17	24.98
Vehicles	300.00	302.00	235.42	162.26	86.62	5.78	6.34
Land and easements	500.00	508.00	527.92	543.68	562.75	576.54	590.40
Spare straight-line tax asset class	-	-	-	-	-	-	-
Buildings - capital works	40.00	48.00	52.00	55.48	59.19	62.22	65.13
In-house software	30.00	34.00	26.57	16.62	6.88	7.28	8.35
Equity raising costs	3.00	3.50	3.51	3.48	3.46	3.41	3.36
Nominal Actual Net Capex	114.50	118.27	67.14	105.64	68.01	81.39	-
Nominal Forecast Regulatory Depreciation	51.00	237.19	300.65	293.34	346.83	279.01	-

‍

Outputs

‍

The output tab is of premier importance to any user of the model and, as such, should be structured in a way to relay information most efficiently.
The output tab may be divided into further tabs for complex financial models. The divisions may be:

An executive summary tab which presents a mix of graphs, charts, and tables, displaying the trends and analyses that may be useful for strategic decision-making.
A financial output tab that lists a summary of the financial projections from the Calculations tab to a considerably high level of detail.

An example of an output sheet can be seen in Ofgem’s GT2 PCFM in Figure 6.

Figure 6: Outputs sheet from Ofgem's GT2 Price Control Financial Model PCFM.

‍

Live results

Note: This sheet displays the output of the PCFM for the live company in the selected year.
As well as displaying output it performs the role of recalling results generated with data at earlier stages from the "SavedResults" sheet required for the MOD calculation.

	PCFM year ending	Unit	Licence term	31 Mar 2022	31 Mar 2023	31 Mar 2024
	Totex
1	Slow pot	£m 18/19 prices	-	207.7	284.4	288.5
2	Fast pot	£m 18/19 prices	-	111.7	152.9	155.2
3	Post-TIM totex allowance	£m 18/19 prices	-	319.4	437.3	443.7
	Regulatory Asset Value (RAV)
4	Opening asset value (before transfers)	£m 18/19 prices	-	5,951.6	5,861.2	5,845.1
5	Transfers	£m 18/19 prices	-	-	-	-
6	Opening asset value (after transfers)	£m 18/19 prices	-	5,951.6	5,861.2	5,845.1
7	RAV additions (after disposals)	£m 18/19 prices	-	207.7	284.4	288.5
8	Depreciation	£m 18/19 prices	-	(298.1)	(300.5)	(306.0)
9	Closing asset value	£m 18/19 prices	-	5,861.2	5,845.1	5,827.6
	Final Proposals allowances (calculated revenue)		R_t
10	Fast money	£m 18/19 prices	FM_t	105.7	146.0	149.5
11	Pass-through expenditure	£m 18/19 prices	PT_t	146.8	146.8	147.1
12	Depreciation	£m 18/19 prices	DPN_t	298.5	300.3	305.3
13	Return	£m 18/19 prices	RTN_t	171.1	164.3	160.6

‍

Sensitivity or Scenario Analysis (if required)

‍

This may be useful for model users in looking at a set of pre-programmed scenarios (with a sufficient degree of customisability).
In this tab, it is useful to build intuitive scenarios and sufficiently varied sensitivities which will be sufficient to yield a wide view of possible outcomes in the sensitivity tables, as shown below from the CAA’s H7 PCM.

Figure 7: Scenario analysis from the CAA's H7 PCM

‍

Scenario Selector

1. Live Scenario

Model Mode

Live combination

Live input set being run in model

Run live outputs

Scenario 1

1

Model run based on Scenario for financial statements and ratios

(Price scenario, Column scenario)

Outturn scenario

Navigate to Q_Dashboards

Live

Live Scenario		Input set used to determine prices		Input set used to determine outturn financial statements & ratios		Base vs Stress
Scenario 1	Mid case	Input set 1	Base case	Input set 1	Base case	Base
column number		1		1

2. a Scenario names

ID	Scenario		Input set used to determine prices		Input set used to determine outturn financial statements & ratios		Base vs Stress
1	Scenario 1	Mid case	Input set 1	Base case	Input set 1	Base case	Base
2	Scenario 2	Not used	Input set 2	Low case	Input set 2	Low case	Base
3	Scenario 3	Not used	Input set 3	High case	Input set 3	High case	Base
4	Scenario 4	Low outturn	Input set 4	Low outturn	Input set 4	Low outturn	Base
5	Scenario 5	High outturn	Input set 5	High outturn	Input set 5	High outturn	Base
6	Scenario 6	Not used	Input set 6	-	Input set 6	-	Base
7	Scenario 7	Not used	Input set 7	-	Input set 7	-	Base
8	Scenario 8	Not used	Input set 8	-	Input set 8	-	Base
9	Scenario 9	Not used	Input set 9	-	Input set 9	-	Base

Formatting

‍

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.

‍

Colour Rules

‍

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.

‍

Text colour

‍

‍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.

‍

ofgem

Making a positive difference for energy consumers

RIIO-2 Price Control Financial Model (PCFM)

Electricity Transmission

Model key

Sample

Calculated value

Import

Export

‍

Figure 9: Use of text colour in Ofgem's RIIO-2 Electricity Transmission PCFM

Return & RAV
Parameter	Units	Annual values
PCFM year ending		31 Mar 2022	31 Mar 2023
RAV

Running total

Opening RAV (before transfers)	£m 18/19 prices	14,055.8	14,446.6
Transfers	£m 18/19 prices	-	-
Opening RAV (after transfers)	£m 18/19 prices	14,055.8	14,446.6
Net additions (after disposals)	£m 18/19 prices	1,261.3	1,278.0
Depreciation	£m 18/19 prices	(870.5)	(871.8)
Closing RAV	£m 18/19 prices	14,446.6	14,852.8

Post-vesting balance

Cost

Opening balance brought forward (before transfers)	£m 18/19 prices	24,969.8	26,231.1
Transfers	£m 18/19 prices	-	-
Opening balance brought forward (after transfers)	£m 18/19 prices	24,969.8	26,231.1
Net additions (after disposals)	£m 18/19 prices	1,261.3	1,278.0
Removals	£m 18/19 prices	-	-
Closing value carried forward	£m 18/19 prices	26,231.1	27,509.1

‍

Cell colour

‍

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

‍

ofgem

Making a positive difference for energy consumers

RIIO Financial Model

Electricity Distribution

EDI PCFM for AP November 2021.xlsm

RIIO EDI Price Control Financial Model for the Annual Iteration Process that will take place by 30 November 2020

Model key

Sample

Information and interface

Fixed input value

Annual update input

Input linked from annual update

Notes and instructions

‍

Figure 11: Use of cell colour with Ofgem's ED1 PCFM

‍

Parameter

Units

Annual values

PCFM year ending

31 Mar 2016

31 Mar 2017

31 Mar 2018

Actual

Actual load related capex

Actual non-load related capex - asset replacement

Actual non-load related capex - other

Actual faults

Actual tree cutting

Actual 100% 'revenue pool' expenditure

Actual controllable opex

£m 12/13 prices

9.8

71.5

23.8

35.6

2.9

12.1

74.8

2.5

70.8

17.8

26.2

3.3

11.7

63.2

3.4

78.2

18.4

26.1

3.6

13.2

83.9

Non-totex (all categorised as 'opex' for tax purposes)

Non-controllable opex (excluding Exit Charges)

Non-controllable opex (Exit Charges)

Non-controllable opex

£m 12/13 prices

29.8

13.6

43.41

30.3

14.9

45.2

30.4

14.6

45.1

‍

Tab colour

‍

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

‍

Model formatting

SHEET TABS

Light Yellow

Input sheets

No colour (default Excel tab colour)

Calculation and documentation sheets

Pale Blue

Key output sheets

Turquoise

Quality control sheets

Yellow

To be completed, temporary, restructured, or deleted

Orange

To be used for internal purposes only, removed prior to publication

Figure 13: Tab colour example from Ofwat's PR19 Revenue Forecasting Incentive model

Cover

Model formatting

Inputs

F_Outputs

Check

‍

Structure and layout rules

‍

40. General rules to follow when constructing, testing, and auditing financial models are included in the subsections below

‍

Rule	Description
Structure and layout rules
Limitation to one row	Limit inputs and formulas to one row so users understand the model vertically as they scroll down the model.
Not hiding sheets/rows	Do not hide rows/columns as this improves transparency.
Limit the number of tabs	Fewer tabs with more content is preferable to many tabs with less content. It is easier to follow and audit a continuous array of data across a large tab rather than multiple, smaller tabs.
No merged cells	Do not merge cells for presentation purposes. This often leads to problems later when moving/copying or deleting cells. A better solution is normally to change the colour of the cell border and relocate or change text alignment.
Hide gridlines	Use the “View” tab to hide gridlines in order to make the sheets more presentable.
Identical spacing for columns and rows	Columns and rows should be identically spaced for each sheet in the workbook, with the same font and font size being used for all sheets.
Simple workbook and sheet names	Ensure that workbook and sheet names are short and simple.
Hide unused rows and columns	Unused rows and columns at the bottom of each sheet and the right hand side of each sheet should be hidden.
Ensure flow of sheets is logical	A sheet should start logically from the top and flow to the bottom before being used in another sheet at the top and flowing to the bottom subsequently. Similarly, the sheets on the left should feed the sheets on the right inputs on the left-hand side and outputs on the right-hand side.
Spacing calculations	Each worksheet should have 1-2 spare rows between each relevant section of calculations, such that each distinguishable section above feeds each distinguishable section below. For example, it is good to finish one set of calcs and calculate a total Total1 then leave a spare row to do another set of calcs that involve Total1.
Spare columns	It is good to have multiple spare columns to the left, using one to confirm units for that row and using one to confirm any constants for that section of the spreadsheet.
Hide filter button	When making tables especially when said tables are to be used in presentations/screenshots, hide the filter button on the header row. As well as the filter icon hiding the cell text, this can create other issues particularly if sorting when columns have been excluded.
No empty cells in data tables	Data tables or any other tables should have no empty cells, need to be complete vertically and horizontally.
Create a model map	As shown in Appendix A – Model map example, a model map should be included regardless of the size or simplicity/complexity of the model.
Add contents page	For ease of reference especially in larger models, add a content sheet with hyperlinks.

Content rules

‍

41. General rules to follow when constructing, testing, and auditing financial models areincluded in the subsections below.

‍

Rule	Description
Content rules
Reference sources for inputs	Verify any inputs in the model with hyperlinks to track down any references made.
Differentiate cell contents	Clearly highlight and separate the inputs from the formulas preferably altogether in the Inputs tab.
Sign convention	Follow a consistent sign convention for e.g. displaying deductions as negative or positive numbers throughout the model.
Organization of inputs	Consolidate all inputs in an Input tab to make it easier for users to reference from a single point of origin.
Avoidance of cross-linking	Avoid linking to other files and instead include inputs from other files as hard-coded inputs, which are then manually updated.
No external links	When going to “Data” > “Edit Links”, there should be no files shown, no external excel files, or links to SharePoint. Any inputs used from other spreadsheets should be hardcoded but include backup to source files.
Short text notes for each sheet	Each separate section of each worksheet should include a short text note, less than 20 words in length, to explain briefly what this section of the spreadsheet is designed to do.
Consistent styling	When making graphs/charts, make sure styling is consistent with what is being sent to clients in presentations/briefs. This is so the same font is being used for graphs and charts as is being used elsewhere aesthetic difference.
Don’t copy across formatting infinitely	"Copying across" formatting is to be avoided, such as when colouring in cells for a non-infinite range. This is also known as "blank space" in sheets and Excel has a "used range" for every sheet in the workbook. Copying across cell colours, for instance, from column A to column XFD causes Excel to regard that as a "used range" and significantly increases file size as this goes on during the sheet.
Excess information in sheets	Avoid having any extra/unnecessary or irrelevant rows or columns of data see ‘Trace Dependents’ in a sheet to make it easy for the user to track output.

Formula rules

‍

42. General rules to follow when constructing, testing, and auditing financial models areincluded in the subsections below.

‍

Rule	Description
Formula rules
No hardcoding	Never use hard-coded numbers embedded in formulas because they are very difficult to spot if the user is less familiar with the model.
Simplicity	Avoid complicated formulas and, instead, break up the formula into easily digestible steps to avoid confusion and improve the ability of others to follow.
Creating ‘checks’ using formulas	Create checks throughout the model to quickly review the integrity of the model. It is usually better to include checks at the bottom of each spreadsheet and then consolidate them in a separate Checks tab. This makes it easier in tracing errors that may have originated while building the model.
Consistency in formulas	Formulas should be consistent from left to right. If cell C5 is C3 + C4 then cell D5 should be D3+D4 not D1+D2+D3+D4.
Avoid using auto-updating cells	Avoid using cells that update each time any cell is updated in the workbook formulas such as “=NOW” or “=TODAY” as these slow down the sheet. If use is deemed necessary, set calculation options to manual from Preferences.
No daisy-chaining formulas	Daisy-chaining formulas is where the result of one affects the calculation in another cell, which affects the calculation of another, and another, and so on.
Avoid using specific text in Lookups	Lookups should avoid looking for specific text, and rather use a reference number if available. Reference lists should be kept in a single and separate tab, if needed.

Appendix A – Model map example

‍

Figure 14: Model map from the CAA's H7 Price Control Model

‍