Design the model around the decision
Before opening Excel, write the question, historical period, forecast period, required outputs, key assumptions, and the level of detail justified by the decision. A valuation model, financing model, operating plan, and transaction model may share statements but require different architecture.
Typical workbook structure
A clear model may contain:
- cover and version notes;
- sources and assumptions;
- historical financial statements;
- operating drivers;
- supporting schedules;
- forecast statements;
- valuation or transaction mechanics;
- scenarios and sensitivities;
- summary output and checks.
The exact tab count doesn't matter. The flow should move from source data to assumptions, calculations, and outputs.
Historical data
Import and standardize reported periods. Record filing and page references or source links. Reconcile historical statements before forecasting. If management metrics are used, preserve the definition and reconciliation to reported figures.
Assumptions
Centralize major assumptions and label units, dates, and source. Use a clear distinction among historical data, formulas, and forecast inputs. One assumption should live in one place. Avoid typing the same growth rate into many formulas.
Formula discipline
Use consistent signs and time direction. Build formulas that can be copied across periods where possible. Avoid hidden constants inside formulas and circular references that aren't controlled deliberately. Use named ranges or structured references only when they improve transparency for the intended user.
Supporting schedules
Statements should be driven by schedules for revenue, working capital, fixed assets, debt and interest, taxes, equity, and shares. A schedule explains the mechanism and makes errors easier to identify.
Checks
At minimum, include balance-sheet balance, cash-flow reconciliation, debt roll-forward, retained-earnings roll-forward, sources-and-uses balance, and transaction ownership checks. Make errors visible at the summary level. Don't overwrite a check to make the model appear finished.
Scenarios and sensitivities
A scenario changes a coherent set of operating assumptions. A sensitivity changes one or two variables around a case. Keep the base case separate from the latest case if the model will be updated over time. Record what changed between versions.
Version control and handoff
Use file naming, dates, version numbers, and a change log. Preserve approved versions. The model should contain enough documentation for a new analyst to understand the structure and source data without reverse-engineering every formula.
Automation and AI
Code, data tools, and AI can accelerate data extraction, formula generation, and testing. They can also introduce incorrect definitions, silent unit changes, stale inputs, or confidential-data risk. Use approved tools, preserve source traceability, review formulas, and don't allow generated output to bypass the firm’s supervision or information controls.
Model design principles
A production model is correct, transparent, flexible enough for the decision, and no more complex than necessary. Speed matters, but a fast model that can't be reviewed is an operational liability.