Building a simple financial model in Excel: structure, inputs, assumptions and checks

A financial model is a spreadsheet that answers “what happens if”. What if we raise prices by 5%? What if the new hire starts in March instead of January? What if conversion is half what we hoped? A good model gives a clear answer in seconds. A bad one gives a confident answer that nobody can check.

The difference is rarely the maths. Most small business models need nothing harder than multiplication and SUM. The difference is structure: where the inputs live, how the calculations flow, and whether the model tells you when something is wrong. This guide sets out a simple structure based on ICAEW’s Financial Modelling Code and walks through a small worked model.

Start with the question and the outputs

ICAEW’s summary of its code, Getting your financial models right, starts before any cell is filled: set the scope and goals, define the outputs, and plan the design before building. Write down the question the model must answer and the few numbers the decision depends on, such as monthly cash balance, break-even month or profit by product.

This step stops scope creep. A model built to decide whether to open a second shop does not need a five-year balance sheet. It needs monthly revenue, costs and cash for the next 18 to 24 months, and the handful of assumptions that drive them.

Separate inputs, calculations and outputs

A principle found in most modelling standards is to keep three things apart. ICAEW’s code says to separate inputs, calculations and outputs wherever possible, and the Wikipedia article on financial modelling lists the same separation, plus clear labelling and documentation of assumptions, as good practice.

• Inputs sheet. Every assumption, in one place, each with a label, a unit and a source or note. Colour input cells consistently so anyone can see what may be changed.
• Calculations sheet. Formulas only, no typed numbers. Each row does one thing and refers back to the inputs.
• Outputs sheet. The summary the decision-maker reads: key figures, a chart or two, and the check summary.

Two habits make this work. First, never hard-code a number inside a formula. ICAEW gives tax rates as the classic example: if 20% is typed into fifty formulas, a rate change means fifty edits and at least one missed. Put it on the inputs sheet once. Second, keep timelines consistent. If month 1 is in column D on one sheet, it should be in column D on every sheet, so formulas read straight across.

Write assumptions you can defend

An assumption is a decision about the future, so treat each one as something you may need to justify. Next to each input, note where it came from: last year’s actuals, a supplier quote, a market benchmark or a plain guess. Guesses are fine if they are labelled as guesses. They tell you which inputs most need research.

The Wikipedia article sums up common critiques of models: they often contain unrealistic implicit assumptions and internal inconsistencies, such as revenue growing fast while working capital and fixed assets stay flat. It also notes that modellers tend to use single point values, which hides the range of outcomes. Build at least a low, base and high case for the inputs that matter most. ICAEW also warns against filling a model with dummy data that could later be mistaken for real figures.

Build checks into the model

ICAEW’s code starts from a realistic premise: errors are “both natural and unavoidable”. So build ways to catch them:

• Section checks that compare two routes to the same number, for example total revenue by product against total revenue by month. The difference should be zero, within a small tolerance for rounding.
• Input checks that flag blanks or values outside a sensible range.
• A master check on the outputs sheet that shows “OK” only when every individual check passes.

The same guidance advises avoiding circular references, which usually point to a wrong formula, breaking long formulas into simpler stages, and not hiding sheets or using white text to conceal cells. Restricting what can be entered in input cells, with alert messages, also helps.

A worked example (illustration only)

A founder wants to know whether a subscription box can cover its costs within a year.

The calculations sheet runs month by month across columns. Opening subscribers plus new subscribers minus churned subscribers gives closing subscribers. Closing subscribers times price gives revenue, times cost per box gives variable cost. Revenue minus variable and fixed costs gives monthly profit, and a running total gives cumulative profit.

Two checks sit at the bottom. One confirms that closing subscribers never go negative. The other recomputes total twelve-month revenue a second way, from the sum of subscribers across all months times price, and compares it with the monthly total. The outputs sheet shows the month profit turns positive and the cumulative position at month 12, with a master check cell.

With the structure in place, the founder can test the guesses. Excel’s what-if analysis tools help here. Goal Seek finds the single input value needed to hit a target, such as the churn rate at which month 12 breaks even. A data table shows results for one or two changing inputs at once. Scenario Manager stores sets of inputs, though Microsoft notes it handles up to 32 values and its summary reports do not update automatically.

Common mistakes

• Typing numbers into formulas instead of referring to the inputs sheet.
• Different timelines on different sheets, so formulas no longer read straight across.
• One giant formula that nobody, including its author, can follow.
• No checks, so an error shows up only when the bank balance does.
• Assumptions with no source, so nobody knows which ones to challenge.
• Formatting for its own sake. ICAEW’s code puts it plainly: “Formatting for formatting’s sake is unnecessary.”

When a simple model is enough

A structured spreadsheet like this suits early-stage decisions: pricing, hiring, a new product line, a funding conversation. ICAEW stresses that its code is principles-based, and that there is “no one way of building a model”, so adapt the structure to the question. When the business grows into multiple entities, debt, tax and a full three-statement forecast, the same principles still apply, but it is worth having an experienced modeller review the work before anyone relies on it.