Model review / Practical guide

How to Audit a Complex Excel Financial Model

Start with the decisions the model supports. Then work back through the calculations, assumptions and exceptions that could change those decisions.

An Excel financial model can reconcile perfectly and still answer the wrong question. A balanced balance sheet, a clean error check or a consistent formula pattern is useful evidence, but none establishes that the commercial logic is sound. A useful audit combines calculation review with a clear understanding of the transaction, operating business and intended use.

1. Define what the review covers

Write a short scope before opening individual formulas. Which outputs will someone rely on: valuation, equity IRR, liquidity, covenant headroom or a financing requirement? What period and scenario matter? List the model components included in the review and identify separate workstreams for VBA, Power Query, the Data Model and external data sources.

For a project finance model, a useful starting set is revenue, operating costs, tax, capital expenditure, financing, distributions and key ratios. Create a review record with the output, the relevant schedule, the issue, its possible effect and the evidence needed to resolve it. An unchecked component should remain visible as unchecked.

2. Establish the model’s operating state

Record the active case, dates, currencies and units. Check whether inputs are annual, quarterly or monthly and how the workbook handles partial periods. Note calculation mode, iteration settings, external links, hidden worksheets and unresolved errors. Cached values may be stale; agree on a safe recalculation process before treating displayed outputs as current.

Keep an unchanged baseline. If two reviewers use different assumptions or one refreshes external inputs, they may reach different results even when they review identical formulas. A small amount of version discipline saves a great deal of later reconciliation.

3. Trace material outputs back to assumptions

Pick one significant output and follow the calculation chain. For equity IRR, inspect the dated equity cash flows, the sign convention, distribution timing and any opening or terminal cash flow. For a debt-service coverage ratio, identify exactly what the model includes in cash available and debt service; then compare that definition with the financing documentation.

Use Excel’s Trace Precedents and Trace Dependents tools where applicable, but record references that require manual inspection, including external sources and dynamically constructed references. An incomplete trace is not proof that no further dependencies exist.

4. Compare formula patterns across periods

Period-based models repeat logic. Group formulas by their relative reference structure rather than by identical text. For example, =F18*F21 and =G18*G21 have the same relative pattern when entered in adjacent columns of the same row. Absolute and mixed references must retain their meaning when you compare patterns.

F24: =F18*F21
G24: =G18*G21
H24: =H18*H21
I24: =I18*$F$21

The last formula fixes the second reference to column F. That could be a deliberate base-period price or an unintended copied reference. Check the row labels, scenario assumptions and intended escalation logic before calling it an error. First and last periods, construction-to-operation transitions and debt maturity dates often justify exceptions.

5. Challenge the financial logic

Inspect the economics behind the formulas. Does revenue use the right capacity, utilisation, price and escalation dates? Do working-capital balances reflect the agreed day-count convention? Does tax recognise the applicable limitations and timing? Are debt drawdowns, interest, repayments and fees consistent with the transaction assumptions?

Check boundary cases explicitly. A repayment schedule that works in the base case may fail when cash is insufficient. A terminal value may be counted twice if proceeds flow through both the operating schedule and a separate valuation adjustment. Link each concern to a specific calculation path and reconcile it against independent inputs or documentation.

6. Test changes with an expected result

Choose a small, controlled set of sensitivities. Before changing an input, state the expected direction and any exceptions. For example, a lower operating price would ordinarily reduce revenue, but a price floor, tax interaction or contractual support mechanism can change the downstream response.

Change one driver at a time, recalculate using the agreed method and compare the outputs. Restore the baseline between tests. Include boundary dates, zero activity, negative cash flow and a stressed financing case where relevant. A plausible output is not enough: inspect the intermediate calculations when the result differs from the expectation.

7. Close findings with evidence

Separate observations, confirmed errors and unresolved questions. Give each finding a stable identifier, cell or range, description, potential impact, owner and resolution evidence. If you fix a formula, retain the original text and explain the change. Compare the revised workbook with the baseline and check whether the correction affected other scenarios.

Finish with a statement of scope and limitations, including components not reviewed and questions still open. Avoid translating a percentage of reviewed formulas into a percentage of model correctness: one unreviewed formula can still drive a material result.

A compact review checklist

1. Freeze a baseline and document scenario, units and calculation settings.
2. Map the material outputs to schedules and assumptions.
3. Investigate unusual formula patterns in their financial context.
4. Test important boundaries with stated expectations.
5. Resolve findings against evidence and retain the audit trail.