Financial Modelling Best Practices

Every financial model should follow three principles. First, flow left to right and top to bottom — inputs on the left, outputs on the right; assumptions at the top, results at the bottom. Second, one row per calculation — never nest three formulas when you can use three rows. Third, time flows across columns — each column is a period (month, quarter, year), and every row formula is the same across all periods.

Break your model into logical modules. A typical FP&A model has five to seven modules: assumptions and drivers, revenue build, headcount and personnel costs, operating expenses, capital expenditure, working capital, and the three-statement output (P&L, balance sheet, cash flow). Each module should be self-contained — you should be able to understand it without reading every other module first.

Design for growth. If you have three departments today, build the model structure to handle ten without restructuring. Use named ranges or table references rather than hard-coded cell references. Avoid circular references entirely — they cause performance problems and make the model unreliable. In Grove FP, modular architecture is built in: each planning module (revenue, headcount, OpEx) feeds into the consolidated P&L automatically.

Inputs are the numbers that someone types in: growth rates, prices, headcount, cost assumptions. In a spreadsheet, colour all input cells blue (the industry standard). Group them on a dedicated assumptions sheet or clearly labelled section. Every input should have a label, a unit (£, %, count), and a source reference. Never bury an input inside a formula — if someone needs to change an assumption, they should find it in exactly one place.

Calculations transform inputs into results using formulas. No hard-coded numbers should appear in the calculation layer — every number should trace back to either an input cell or another calculation. Keep formulas simple and readable. If a formula needs a comment to explain what it does, it is too complex. Break it into intermediate steps. For example, rather than a single formula for "fully loaded cost per employee", calculate base salary, employer NI (13.8% above £9,100), pension contribution, and benefits in separate rows, then sum them.

Outputs are the reports and summaries that stakeholders see: the P&L, cash flow, KPI dashboard, and board slides. Outputs should only contain references to the calculation layer — never raw calculations. This ensures consistency: the EBITDA shown on the dashboard is always identical to the EBITDA in the detailed P&L because both reference the same source cell.

Every cell in a row should use the same formula, adjusted only by relative references. If January uses =B10*B15, February should use =C10*C15, and March =D10*D15. If you find yourself writing a different formula for one month (perhaps because of a one-off adjustment), add a new row for the adjustment rather than modifying the base formula. This keeps the model auditable.

Deeply nested IF statements are the most common source of errors in financial models. IF(IF(IF(...))) is nearly impossible to audit and easy to break. Use lookup tables, named ranges, or helper columns instead. For complex logic, create a separate calculation block that resolves each condition in a dedicated row, then combine the results.

Several calculations recur in UK financial models. Employer NI: =MAX(0, (AnnualSalary - 9100) * 0.138). This calculates the 13.8% employer National Insurance contribution above the secondary threshold. Pension auto-enrolment minimum: =AnnualSalary * 0.03. Corporation Tax: =MAX(0, ProfitBeforeTax) * 0.25 (for profits over £250,000). Standardise these as named formulas or reusable functions in your model.

At minimum, include these checks. Balance sheet balance: Assets - Liabilities - Equity = 0. Cash flow reconciliation: Opening Cash + Net Cash Flow = Closing Cash. P&L integrity: Revenue - COGS - OpEx - D&A - Interest - Tax = Net Income. Headcount reconciliation: Opening HC + Hires - Leavers = Closing HC. Each check should display TRUE/FALSE or a green/red indicator prominently on a summary sheet.

Beyond mathematical checks, include sanity checks on key assumptions and outputs. Is revenue growth between -20% and +100%? Is gross margin between 30% and 80%? Is headcount cost per employee between £40,000 and £150,000? Is monthly cash burn less than 20% of total cash? These checks catch data entry errors (someone typed 50000 instead of 50.000) and flawed assumptions before they contaminate the entire model.

Add flags that highlight when the model is operating near critical thresholds. Cash balance below three months of operating costs: amber warning. Cash balance below one month: red warning. Gross margin below breakeven: red flag. Revenue per employee below £100,000: amber flag. These flags turn the model from a passive calculator into an active monitoring tool.

Create a single sheet (or section) that describes the model's structure. List every module, its purpose, its key inputs, and its key outputs. Include a flow diagram showing how data moves through the model: Assumptions -> Revenue Build -> P&L, Assumptions -> Headcount -> P&L, P&L -> Cash Flow -> Balance Sheet. This map is the first thing a new user reads.

Use consistent, descriptive names. Sheets: "01_Assumptions", "02_Revenue", "03_Headcount", "04_OpEx", "05_PL", "06_CashFlow", "07_Checks". Named ranges: "rev_growth_rate", "avg_salary_engineering", "employer_ni_rate". Avoid abbreviations that only you understand. If someone else cannot read the name and know what it means, rename it.

For complex calculations, add a comment row above the formula explaining the logic in plain English. "This calculates employer NI at 13.8% on salary above the secondary threshold (£9,100 for 2026/27)." For assumptions, add a source row: "Source: Bank of England Monetary Policy Report, November 2025." This creates a self-documenting model that remains trustworthy over time.

If you must use spreadsheets, establish a strict naming convention: "FY2627_Budget_v3.2_2026-01-15.xlsx" where the version number and date are always present. Store files in a shared location (SharePoint, Google Drive) with restricted edit access. Designate one person as the model owner who controls the master file.

Maintain a change log sheet within the model. Each row records: date, version number, who made the change, what changed, and why. "2026-01-15, v3.2, J. Smith, Updated employer NI rate from 13.8% to 15.0% per Autumn Statement announcement." This log is invaluable during audits and when onboarding new team members.

The fundamental problem with spreadsheet version control is that the file is the database, the application, and the user interface all in one. Dedicated FP&A platforms like Grove FP separate these concerns. Every change is logged automatically with a timestamp, user, and previous value. You can view the full history of any cell, roll back to any previous state, and see exactly who changed what and when. This eliminates the version control problem entirely and gives auditors the trail they need.