Model standards, review and audit

Video lecture

9 chapters · about 8 min · full transcript

Coming soon

Chapter 1 of 9

Standards that make models auditable

FAST and other published modelling conventions
A reviewer's checklist
Model audit, and where AI and code help

The narrated lecture is in production

Every chapter is scripted and ready. Browse the chapters and read the full transcript now — the video will appear here when it’s published.

Chapters

Transcript of the narration, chapter by chapter.

0:00 Standards that make models auditable

Every year, somewhere, a spreadsheet error changes a decision it shouldn't have. A row that sums one cell too few. A sign flipped in a tax line. A hard-coded number left over from an old scenario. In project finance, those errors can move debt sizes and equity returns by millions, and they're rarely spotted by the person who made them. That's why the industry has converged on published modelling conventions and independent review. In this lecture you'll learn what standards like the FAST Standard and the ICAEW Financial Modelling Code are for, the core rules they share, how to run a structured review of someone else's model, what a formal model audit covers, and how code and AI tools can help without replacing the reviewer. By the end, you'll be able to review a model with a checklist and write findings a modeller can act on.

1:03 Why it matters

Why does this matter? Because errors in large models hide in plain sight. They're consistent-looking formulas that are subtly wrong, or old hard-codes nobody remembers. Shared conventions make models easier to read, so errors are easier to see. When everyone formats inputs the same way, keeps one formula per row, and flows calculations in a predictable direction, a reviewer can spot the one row that breaks the pattern. And in project finance, independent model audit is normally a lender requirement before financial close. A model built to recognised conventions is cheaper and faster to audit, and produces fewer findings. That's time and money saved at exactly the point in a deal when both are scarce.

1:53 The concept: the FAST Standard and friends

Let's meet the standards. The FAST Standard, published openly by the FAST Standard Organisation and made available under a Creative Commons licence, is built around its acronym: flexible, appropriate, structured and transparent. It's one of the most widely adopted, independently administered modelling standards. The ICAEW, the Institute of Chartered Accountants in England and Wales, published a Financial Modelling Code in twenty eighteen, drawing out principles shared across several organisations' methodologies. And there are other published standards and firm-specific methodologies. Think of these like the style guides newspapers use. They differ in details, but they share the same goal: that any competent reader can pick up the work and follow it. Treat them as industry conventions. Adopt one consistently across your team, rather than arguing about which is best.

2:49 The shared rules

Whatever standard you adopt, the core rules overlap heavily. Separate inputs, calculations and outputs. Keep timelines consistent across sheets. Use one formula per row, consistent across the timeline, so a reviewer can check a row by checking one cell. Never hard-code numbers inside formulas. Keep formulas short, splitting complex logic into steps. Label units and sign conventions. Format inputs, calculations and links distinctly. Build checks, and show a master check on every output. Avoid hidden sheets, external links and unmanaged circularity. And document the model: a model book listing every input and its source, plus a version log. Here's the key idea: these rules exist to make errors visible. Every rule you break makes the next error harder to find.

3:41 Worked example one: a row that breaks the pattern

Let's walk through a classic finding. Row two fourteen calculates revenue. The formula is consistent across the timeline, except in period seventeen, where someone typed a number over the formula. It was probably a quick override to test a scenario, and it was never removed. Now every case the model runs has that fixed revenue in period seventeen, whatever the inputs say. How do you find it? With a consistency check: compare each cell's formula with the one to its left, and flag any difference. Tools do this automatically, but you can do it with a simple formula or with Excel's formula-display view. The finding is written like this: row two fourteen, period seventeen, hard-coded value overrides the revenue formula; restore the row formula and, if an override is genuinely needed, add it as a labelled input with a switch.

4:42 Worked example two: a pre-close model review

Now a realistic example, with illustrative figures. A sponsor's team is preparing a model for a fictional one hundred and fifty megawatt solar and storage project in the Gulf. Before the formal lender audit, a senior modeller who didn't build it runs a checklist review. She logs twenty-two findings: three high, seven medium and twelve low. The high ones matter. The debt service reserve was sized on the wrong forward period compared with the term sheet. Tax losses never expired in the model, although the applicable rules limit how long they can be carried forward. And a sign error on VAT recovery overstated cash in construction. Fixing those three changed minimum DSCR and equity IRR noticeably. Catching them internally, before the formal audit, saved a round of audit fees and protected the sponsor's credibility with lenders.

5:41 Watch me do it: checks with a script

Let me show you a small script that helps with the mechanical part of a review. Using the Python library openpyxl, I load the workbook with formulas rather than values. For each calculation row, I convert every formula to a relative form, so a formula in column G referring to column F looks the same as one in column H referring to column G. Then I compare each cell with its left neighbour and flag any change. I also flag any calculation cell that contains a typed number rather than a formula, and any formula containing numeric constants other than zero or one. The script prints a findings table: sheet, row, column and issue. Then the important part: a human reviewer goes through every flag and marks it confirmed or intended. Commercial review add-ins do this and more, but even a simple script makes a review faster and more systematic.

6:46 The formal model audit, and AI

A formal model audit, usually commissioned by lenders, typically checks that the model calculates correctly, that it reflects the signed contracts and term sheet, that tax and accounting treatments are appropriate, and that the agreed sensitivities run correctly. It produces a findings log that's worked through with the modeller, and ends with a sign-off letter on a specific version. Where does AI help? An approved assistant can explain a complex formula in plain English, draft test cases, summarise differences between two model versions, and help write clear findings. It can also make confident mistakes about how a spreadsheet works. So AI supports the reviewer. It never signs off a model, and every AI-identified issue must be confirmed in the model itself.

7:39 Recap and try this now

Let's recap. Published conventions like the FAST Standard and the ICAEW Financial Modelling Code exist to make models transparent and errors visible. Pick one and apply it consistently. Review other people's models with a checklist, logging findings with a location, a clear description, a severity and a proposed fix. Automate the mechanical checks, such as inconsistent rows and hard-codes, with add-ins or a simple script, and spend your human attention on logic, contracts, tax and reasonableness. Expect a formal model audit before financial close, and use AI only as an assistant to the reviewer. Your try-this-now: take ten calculation rows from a model you use, review them against the shared rules, and write up your findings in a log with severity ratings.

Why standards exist

Financial models used for project finance decisions are large, long-lived and read by many parties. Published conventions make them consistent, so reviewers can spot the row that breaks the pattern. Two widely referenced examples:

The FAST Standard (Flexible, Appropriate, Structured, Transparent), published openly by the FAST Standard Organisation and made available under a Creative Commons licence. It is one of the most widely adopted, independently administered modelling standards. Check fast-standard.org for the current version.
The ICAEW Financial Modelling Code (published 2018), which sets out principles of good practice drawn from a comparison of several organisations' modelling standards and methodologies.

Other published standards and firm-specific methodologies exist. Treat all of them as industry conventions: the value comes from applying one consistently across a team, not from choosing the "best" one.

The shared rules

Area	Convention	Why
Layout	Inputs, calculations and outputs separated	Assumptions can be found, changed and audited in one place
Timeline	Same columns and periods on every calculation sheet	Rows can be compared and linked without offsets
Formulas	One formula per row, consistent across the timeline	Reviewing one cell reviews the row
Constants	No hard-coded numbers in formulas	Assumptions are visible, labelled and sourced
Complexity	Short formulas; split logic into steps	Errors are easier to see
Units and signs	Every row labelled; a stated sign convention	Avoids sign and scale errors
Formatting	Distinct styles for inputs, calculations, links, checks	Readers know what they can change
Integrity	No hidden sheets, external links or unmanaged circularity	Nothing happens out of sight
Checks	Checks sheet with a master check on outputs	Breakages are visible immediately
Documentation	Model book (inputs and sources) and version log	Anyone can trace and reproduce results

Running a model review

Agree scope and version. Record the file name and version reviewed.
Understand the deal. Read the term sheet and key contracts first; a model can calculate perfectly and still not reflect the contracts.
Map the model. Sheet list, flow of calculations, where each output comes from.
Mechanical checks. Inconsistent rows, hard-codes, external links, hidden items, circularity handling, check sheet status.
Logic review. Timing and flags, revenue, opex and indexation, tax, working capital, CFADS definition, debt sizing, waterfall, reserves, ratios.
Reasonableness. Reconcile the first operating year by hand; compare ratios and returns against expectations; run the agreed sensitivities and confirm checks still pass.
Log findings with location, description, severity, proposed fix and status; agree resolutions with the modeller and re-test.

Findings log template

ID	Sheet	Row/cell	Finding	Severity (H/M/L)	Proposed fix	Status
F-01	Calc_Rev	214, period 17	Hard-coded value overrides row formula	H	Restore formula; add labelled override input with switch	Open
F-02	Debt	DSRA	Sized on next 12 months; term sheet says next 6 months	H	Link sizing period to input per term sheet	Open

Hands-on: flag inconsistent rows and hard-codes with Python

import re
from openpyxl import load_workbook
from openpyxl.formula.translate import Translator

wb = load_workbook("model.xlsx", data_only=False)       # formulas, not cached values
CONST = re.compile(r"(?<![A-Z$])\b(?!0\b|1\b)\d+(\.\d+)?\b")  # numeric constants other than 0 and 1

def rel(formula, origin):
    """Translate a formula as if it sat in column A, so row-consistent formulas compare equal."""
    col_row = re.match(r"([A-Z]+)(\d+)", origin)
    return Translator(formula, origin=origin).translate_formula(f"A{col_row.group(2)}")

findings = []
for ws in (wb[s] for s in wb.sheetnames if s.startswith("Calc")):
    for row in ws.iter_rows(min_row=5, min_col=6):              # timeline starts in column F
        prev = None
        for cell in row:
            v = cell.value
            if v is None:
                continue
            if not (isinstance(v, str) and v.startswith("=")):
                findings.append((ws.title, cell.coordinate, "hard-coded value in calculation area"))
                continue
            if CONST.search(v):
                findings.append((ws.title, cell.coordinate, f"numeric constant in formula: {v[:40]}"))
            r = rel(v, cell.coordinate)
            if prev is not None and r != prev:
                findings.append((ws.title, cell.coordinate, "formula differs from left neighbour"))
            prev = r

for f in findings[:50]:
    print(*f, sep=" | ")
print(f"{len(findings)} flags for human review")

Install with pip install openpyxl. Adapt the sheet prefix and timeline start column to your model's layout. The first column of each row (period 1) sometimes legitimately differs; the reviewer decides. Commercial review add-ins (for example Operis OAK or PerfectXL) and Excel's own Inquire add-in, where your licence includes it, offer richer versions of these checks; confirm current features with the vendor.

Using AI in a review (approved tools only)

Useful: explaining a long formula in plain English; drafting test cases ("what should happen to DSCR if the construction delay input is 6 months?"); summarising differences between two versions from a change log; improving the wording of findings. Not acceptable: treating an AI explanation as proof that a formula is correct, or uploading a confidential deal model to a tool your organisation has not approved. Every AI-suggested issue is confirmed in the model before it is logged.

How to measure success

Findings per review falling over successive versions, with no high-severity items open at audit.
Mechanical checks automated and run on every version.
Formal model audit completed with a sign-off letter on the version used for financial close.

Published conventions such as the FAST Standard and the ICAEW Financial Modelling Code exist to make models transparent and errors visible.
Adopt one convention consistently across a team; the shared rules matter more than the choice of standard.
Review with a checklist: understand the deal, map the model, run mechanical checks, review logic, test reasonableness, log findings.
Automate mechanical checks (inconsistent rows, hard-codes); spend human attention on logic, contracts and tax.
AI can explain formulas and draft tests but never signs off a model; lenders require an independent model audit.

Take ten calculation rows from a model you use, review them against the shared rules table, and write a findings log with location, severity and proposed fix.

Enrol for free to save your progress

Reading is always free. Enrol to keep your place, take the final assessment and earn a verifiable certificate.

Model standards, review and audit