There is a meaningful difference between an Excel file that contains financial calculations and a financial model. The file contains numbers and formulas. The model is designed so that someone other than its author can understand it, verify it, update it, and trust it.
Most Excel files in finance functions are the first thing. The practices below are what makes them the second.
These seven principles are not theoretical. They are the habits that separate financial models that survive the departure of the person who built them from ones that collapse the first time someone else opens them. For a controller building management reporting templates, budgeting tools, or analytical models that will be used beyond the current month, internalising these practices is the highest-return investment of professional development time available.
1. Separate Inputs, Calculations, and Outputs
This is the foundational principle, covered in detail in article 49, and it belongs here again because it is the most important and the most consistently violated.
Every financial model has three types of cells: inputs (assumptions and data that a user enters or pastes), calculations (formulas that transform inputs into intermediate results), and outputs (the results that users read and act on).
These three types should never share a tab without clear visual distinction, and ideally they should be on separate tabs entirely. When inputs and calculations are mixed together, changing an assumption requires knowing which cells are inputs and which are formulas, and the risk of accidentally overwriting a formula with a value is ever-present.
The practical test: can you identify, in under thirty seconds, exactly which cells you need to change to model a different scenario? If yes, the model is well-structured. If no, the structure needs work.
2. Never Hardcode a Value That Appears More Than Once
If a value appears in more than one place in a model - a tax rate, an exchange rate, the current year, a discount percentage - it should be defined once in the inputs section and referenced everywhere else.
The failure mode: a tax rate of 8.1 percent typed directly into twelve MWST calculations across a model. When the rate changes (and tax rates do change), someone needs to find and update twelve cells. They will probably miss one. The model will produce inconsistent results that are hard to detect.
The correct approach: define the rate as a named cell (e.g., VAT_Rate) in the inputs section. Every calculation that uses it references VAT_Rate. When the rate changes, update one cell. Every calculation updates automatically.
This principle applies to any value that the model’s logic depends on: growth rates, salary inflation assumptions, payment terms, depreciation lives. One source, referenced everywhere.
3. Make Every Formula Auditable in One Step
Every formula in the model should be traceable to its sources without switching tabs or scrolling. This means using named ranges rather than cell addresses, using descriptive labels for input cells, and avoiding deeply nested formulas that are impossible to read.
The test for audit readiness: select a cell in the calculation layer and read the formula. Can you understand what it is calculating and where the inputs come from without looking anywhere else? If the formula reads =SUMIFS(Actuals_Amount, Actuals_CostCentre, B5, Actuals_Period, ReportingPeriod), yes. If it reads =SUMIFS($D:$D,$B:$B,B5,$E:$E,$C$1), only if you memorise the column structure of the data tab.
For complex formulas, break them into steps using intermediate calculations. A formula that is twenty characters long is readable. A formula that is two hundred characters long, with nested IFs and multiple criteria, should be decomposed into three or four intermediate cells, each calculating one part of the logic, with the final cell combining them. The decomposed version takes more rows but is auditable, debuggable, and maintainable.
4. Document Assumptions Explicitly
Every significant assumption in a financial model should be written down, in the model, with the rationale for it.
This documentation does not need to be long. A cell comment or a text cell adjacent to the assumption that says “Revenue growth: 12% based on signed contract pipeline as at 30 September 2025, approved by CEO in budget meeting 15 October” is sufficient. It records what the assumption is, where it came from, and when it was agreed.
Without this documentation, the model has a shelf life equal to the memory of the person who built it. When the model is handed to a new controller six months later, the assumption cell shows 12% and nothing else. The new controller does not know whether this is a management target, a bottom-up analysis, or a number someone guessed under time pressure. They cannot make an informed judgment about whether to change it.
Documented assumptions also make sensitivity analysis easier: when you know exactly what each assumption represents and why it was chosen, you know which ones are most uncertain and therefore most worth stress-testing.
5. Stress Test Before Distributing
Every financial model that will be used for decision-making should be stress tested before it is first distributed. Stress testing means deliberately breaking the model to find where it produces wrong results, errors, or unexpected behaviour.
The standard stress test for a financial model covers four scenarios.
Zero scenario: Set revenue to zero. Does the model produce zeros everywhere it should, and no division-by-zero errors? If it produces errors, add IFERROR handlers to the affected formulas.
Extreme high scenario: Set all growth rates to their maximum plausible value (double the base case, for example). Does the model remain coherent, or do some calculations produce physically impossible results (negative costs, percentages above 100%)?
Negative scenario: Set growth rates to large negative values. Does the model handle negative profit and negative cash gracefully, or do conditional formatting rules or formula logic break down?
Empty data scenario: Remove the ERP extract from the data tab, leaving it blank. Do the calculation formulas return zeros or blanks rather than errors? A model that breaks when the data tab is empty will break every month in the minutes between clearing the old data and pasting the new.
Finding and fixing these failure modes before the model is in regular use prevents the embarrassing experience of distributing a management report that contains #DIV/0! errors or inexplicable negative percentages.
6. Use Version Control Consistently
Financial models evolve. Assumptions change, management requests new cuts of the data, the business adds a new entity or product line. Each of these changes potentially introduces errors, and without version control, there is no way to identify when an error was introduced or recover to a prior working state.
The minimum version control practice: save a dated copy of the model at the end of each month-end process, named with the period (e.g., Budget_Model_2025-09.xlsx). Store these dated copies in a shared folder that is accessible to more than one person.
For models under active development, more frequent version saves are warranted: before any structural change, before any major data refresh, and at the end of any session that involved significant formula changes.
The version history also documents the model’s evolution in a way that is useful for audits, due diligence, and management discussions: what did the model look like when a specific decision was made?
7. Build a Reconciliation Check Into Every Model
The final practice is the quality control mechanism that catches errors before they reach the reader: a reconciliation check that verifies the model’s internal consistency.
For a management reporting model, the reconciliation checks are:
Total of all P&L line items equals the reported net profit. Total of all balance sheet assets equals total liabilities plus equity. Total actuals in the presentation equals total actuals in the calculation layer equals total actuals in the raw data. Cash flow statement ending cash equals balance sheet cash.
Each check is a single formula: if the two figures agree, display “OK” in green; if they disagree, display “CHECK” in red using conditional formatting. Place all reconciliation checks on a dedicated tab that the controller reviews before distributing any report.
A model with reconciliation checks that all show “OK” may still contain errors. A model with a reconciliation check that shows “CHECK” definitely contains an error. The checks do not guarantee correctness; they guarantee that the most common structural errors are caught before distribution.
Looking for professional, Swiss-context Excel templates? Browse the template library or book a free call.
Alessandro Ratzenberger is a fractional CFO and business controller based in Zurich, with 15 years of operational finance experience at Dufry Group and Bomi (UPS Group).
