Spreadsheet model design: inputs, calculations and outputs

A spreadsheet model is easy to check when every number has one obvious home. Put the numbers you type (inputs) in one place, the formulas that work on them (calculations) in another, and the results people read (outputs) in a third. Then add five habits: format inputs so they look different from formulas, write one consistent formula per row, keep constants out of formulas, put units in every label, and build checks that show when something doesn't add up.

None of this is new. The ICAEW Financial Modelling Code, published by the Institute of Chartered Accountants in England and Wales, and the FAST Standard (Flexible, Appropriate, Structured, Transparent), maintained by the not-for-profit FAST Standard Organisation, both distill the same practice from people who build and review models for a living, and both can be downloaded free from their publishers. The rules below apply equally to engineering calculators and financial models.

Separate inputs, calculations and outputs

The ICAEW Code says logic should flow consistently from inputs through calculations to outputs, and that the three should be kept apart, either on separate worksheets or in clearly marked sections of one sheet. Which of those you choose depends on size. A one-screen calculator can hold three labeled blocks on one sheet. A model with a timeline, such as a discounted cash flow valuation, usually needs separate tabs.

A typical tab layout:

To run a scenario you change only the Inputs tab. To audit the model you read Calc top to bottom. A reviewer never has to wonder whether a number in a calculation block was typed or calculated.

A worked example: pipe pressure drop

Here is the structure applied to the Darcy-Weisbach equation, which gives the pressure lost to friction in a full pipe as ΔP = f × (L/D) × ρ × v² / 2. (The Darcy-Weisbach question page explains the physics.)

Inputs block

The density and viscosity are for water at about 20 degrees Celsius, and 0.045 millimeters is a commonly used roughness for commercial steel. A Source column next to each value would record where it came from.

Calculations block

Output

Notice what the layout does. Each calculation line does one thing, so a reviewer can check the velocity before worrying about the friction factor. Unit conversions are their own rows with their own labels instead of being buried as /1000 inside a long formula. Swamee-Jain is an explicit approximation of the Colebrook equation (iterating Colebrook here gives 39.5 kilopascals, within half a percent), and naming it in the label tells the reader which method you used. The pipe pressure drop calculator is a fuller version of this layout.

Make inputs look different from formulas

The most common convention in financial modelling, used widely in banking and corporate finance, is a font color code:

• Blue font: a hard-coded input you typed.
• Black font: a formula calculated on the same sheet.
• Green font: a formula that pulls a value from another sheet.

It is a convention, not a rule from Excel or any regulator. The ICAEW Code goes a step further and recommends distinguishing input cells with a defined fill color and/or a cell border, not just a font color, which also helps readers who have trouble telling blue text from black. Many teams therefore combine blue font with a pale yellow fill. Whatever you choose, keep it consistent within the workbook and explain it in a legend on the Notes or Inputs tab.

Excel's built-in cell styles help here. Home > Cell Styles includes Input, Calculation, Output, Check Cell and Note in the Data and Model group. Applying styles rather than manual formatting means you can change the look of every input at once by modifying one style.

One formula per row (or column)

In a timeline model, a row such as revenue should use the same formula in every period. The ICAEW Code puts it strictly: formulas should be consistent across a block so that one formula can be copied across the whole block, with any variation coming from how references are anchored. Its reasoning is that a block of identical formulas only has to be checked once, and an odd formula pasted into the middle stands out.

If the first period really does need a different formula, for example an opening balance, mark it with a note or a different fill so the inconsistency is visible. Excel's error checking can help: it flags a formula that doesn't match the formulas around it with a small green triangle and the message "Inconsistent formula."

No hard-coded numbers inside formulas

A formula such as =B12*1.08 hides an assumption. Is 1.08 sales tax, inflation or a markup, and when was it last checked? Put the 8% on the Inputs tab with a label, and write =B12*(1+Tax_rate) or =B12*(1+Inputs!$C$6).

The ICAEW Code treats this as a judgment call with a clear boundary. Low-risk constants such as the number of months in a year or hours in a day can stay in formulas. Anything that could change during the life of the model must be an input, and so must any constant whose meaning would not be obvious without a label; its example is the feet-to-meters factor 3.28084, which never changes but means nothing to a reader when it appears bare in a formula. The /1000 conversions in the pipe example get their own labeled rows for the same reason.

Units in every header

Write the unit in the label: "Flow rate (L/s)", "Revenue (US$ thousands)", "Area (m²)". Unit errors produce numbers that look reasonable and are wrong by a factor of 10, 1,000 or 3.28. When a model mixes units, convert once, in a dedicated row, and use only the converted value afterwards.

Absolute, relative and mixed references

Anchoring decides what happens when you copy a formula:

In Excel, pressing F4 while the cursor is on a reference cycles through the four forms. Named ranges such as Tax_rate are a readable alternative to $C$6 for a handful of key inputs. Getting anchoring wrong, or deleting a row that a formula refers to, is a common source of the #REF! error.

Checks and error flags

A check is a cell that compares two things that should agree and shows a flag when they don't. Useful checks include:

• Totals that are calculated two ways, such as the sum of the rows versus the sum of the columns: =IF(ABS(SUM(D10:D20)-SUM(E5:O5))>0.005,1,0).
• A balance sheet that must balance: =IF(ABS(Assets-Liabilities-Equity)>0.5,1,0).
• Inputs inside a sensible range: =IF(OR(C5<=0,C5>2000),1,0) for a pipe diameter in millimeters.
• Reynolds number in the range where the friction formula applies: =IF(C16<4000,1,0) flags laminar or transitional flow.

Use a small tolerance rather than an exact equality test, because floating-point arithmetic can leave differences such as 0.0000001; the ICAEW Code recommends a tolerance for exactly this reason, and conditional formatting so a failed check catches the eye. Sum all check cells into one master check on the Checks tab. The Code suggests displaying the master check in the frozen pane of every worksheet (see how to freeze a header row), so anyone working anywhere in the model sees when it turns to 1.

Name tabs and lay them out in reading order

Give tabs short names that describe their job: Notes, Inputs, Calc, Outputs, Checks. Excel limits sheet names to 31 characters, and a name with spaces has to be quoted in formulas (='Cash flow'!B3), so short single words keep formulas tidy. Order the tabs the way the logic flows, left to right, and use tab colors to group them (for example, one color for input tabs, another for outputs).

Within a sheet, keep the same column layout across all timeline tabs, so that column H is the same period everywhere. The FAST Standard puts weight on this kind of structural consistency across worksheets, because it lets a reader move between tabs without re-learning the layout.

Document assumptions on a notes tab

The Notes tab should answer the questions a stranger would ask:

• What the model is for and what it is not for.
• Where each input came from, with a date and a link or reference.
• Simplifications, such as "fluid treated as incompressible" or "cash flows assumed at year end."
• The color legend and the meaning of the checks.
• A version log: date, author, what changed.

Version control

Spreadsheets don't track their own history well, so add discipline:

• Save milestones under new names with an ISO date: pipe-sizing_2026-10-08_v3.xlsx. ISO dates sort correctly in a file list.
• If the file lives in OneDrive or SharePoint, File > Info > Version History lets you open and restore earlier saves.
• Before replacing a model that others rely on, compare old and new versions on the same inputs and confirm the outputs differ only where you expected. Microsoft's Spreadsheet Compare tool, included with some Office editions, lists cell-by-cell differences.

A checklist for your next model

1. Create Notes, Inputs, Calc, Outputs and Checks tabs before writing a formula.
2. Type every number once, on Inputs, with a unit and a source.
3. Format inputs with one style and explain it in a legend.
4. Write one formula per row and copy it across; mark any exception.
5. Move every changeable or unexplained constant out of formulas and into a labeled input.
6. Add at least one check per major block and a master check visible from every tab.
7. Test it the way the ICAEW Code recommends: decide how outputs should respond to an input change, make the change, and compare.
8. Log the version and save under a dated name.