01 — Hardcoded formulas
Hardcoded numbers inside formulas
A number typed inside a formula is an assumption nobody can see, change or audit. It is the most common error in submitted models and the easiest to prevent.
Every number a formula uses lives in a labelled input cell that the formula references. A formula contains cell references and operators only, with the narrow exception of structural constants such as 12, 100 or 365 that are labelled in the model's conventions.
Quick rules
- =C5*1.05 hides a 5% growth assumption; =C5*(1+$D$2) exposes it in D2 where it can be labelled, formatted as an input and changed once.
- A plug (=C5+C6+3) to make a total agree is a hardcode that hides an error; find the difference instead.
- Retyping a figure that exists elsewhere in the workbook is a hardcode too; link to the source cell.
- Structural constants (months in a year, percentage scaling, days in a year) are the exception; state the convention once in the model notes and use them consistently.
- Historical figures typed from a source document are inputs, not hardcodes: they belong in input cells, formatted as inputs, with the source noted.
How to fix it
- Use Find (Ctrl+F or Command+F) with Look in: Formulas and search for each digit 0 to 9 in turn to list formulas that contain constants, or run the Inquire add-in's workbook analysis; Go To Special selects formulas by their result type, not by what they contain. Review each hit.
- Move each embedded number into the assumption block, label it, and replace the constant in the formula with a reference.
- Worked example (invented): a cost line reads =B10*0.42 in every column. Put 42% in one input cell labelled variable cost ratio and change every column to =B10*$C$3; the ratio can now be flexed in one place.
- Format the new input cell in the model's input colour so the next reviewer can see it is an assumption.
02 — Sign conventions
Inconsistent signs
A model needs one rule for whether costs and outflows are stored as negatives or positives. Mixing the two produces totals that subtract when they should add.
Choose one sign convention for the whole workbook, state it in the model notes, and build every subtotal to match it: either store outflows as negatives and add every line, or store all lines as positives and subtract the outflow lines explicitly.
Quick rules
- Negative-outflow convention: costs, capex, tax and repayments are stored as negatives and every subtotal is a SUM.
- Positive convention: every line is positive and each subtotal spells out the arithmetic (=revenue−costs), so the formula carries the sign.
- Never mix them within a statement; a cash flow statement that adds a positive capex line to operating cash flow overstates cash by twice the capex.
- Working capital and debt movements are where the sign flips most often: an increase in a liability is a cash inflow, an increase in an asset is an outflow.
- A number format that shows negatives in parentheses displays the sign; it does not set it. Check the stored value.
How to fix it
- Pick the convention that the case brief or the template uses and write it at the top of the model.
- Walk each statement top to bottom and check every subtotal formula against the convention: a SUM over lines that are stored as positives and negatives together is the first place to look.
- Worked example (invented): operating cash flow 120, capex stored as 30 (positive), free cash flow calculated as =SUM(120,30) shows 150 instead of 90. Store capex as −30 under the negative convention, or write =120−30 under the positive one.
- Add a check that free cash flow equals the change in cash plus financing flows, which catches a flipped sign anywhere above it.
03 — Broken links
Broken links and #REF! errors
A reference to a deleted cell, a moved range or another workbook breaks silently until someone reads the number it now produces.
Every reference in the model points at a live cell in the same workbook. Deleting rows or sheets, and pasting values over formulas, are done only after tracing what depends on the range; links to other workbooks are removed before delivery.
Quick rules
- #REF! means a referenced cell no longer exists; the formula must be rebuilt, not wrapped in IFERROR.
- A cross-sheet link to a cell that has since been overwritten with a value returns a stale number and no error; only tracing finds it.
- External workbook links (a path in square brackets in the formula) break when the file moves and must not exist in a submitted model.
- Named ranges that point at #REF! keep working in appearance and return errors only where used; review Name Manager before delivery.
- Use Trace Precedents and Trace Dependents, or Ctrl+[ and Ctrl+], before deleting anything a formula might use.
How to fix it
- Search the workbook for #REF! (Find, Look in: Values) and for an opening square bracket in formulas (Find, Look in: Formulas) to list external links.
- Open Data → Edit Links to see any remaining external sources and break or replace them.
- Worked example (invented): a summary sheet references a row on the assumptions sheet by cell address; a row inserted above it moved the assumption, and the summary now reads the row above. Rebuild the link to the labelled cell and add a check that the label next to the referenced cell is the one expected.
- After any structural change, recalculate and scan every sheet with Go To Special → Formulas → Errors.
04 — Cell references
Absolute and relative references
The dollar sign decides what moves when a formula is copied. A missing or misplaced anchor is invisible in the first cell and wrong in every other one.
Anchor the row, the column or both in a reference according to what must stay fixed when the formula is copied across periods and down lines, and write each formula once so it can be copied to every cell that needs it.
Quick rules
- $C$3 fixes row and column, for one assumption used everywhere. C$3 fixes the row, for a header row read across columns. $C3 fixes the column, for a per-line rate read down rows. C3 fixes nothing, for period-on-period calculations.
- Press F4 (Windows) or Command+T (macOS) with the cursor in a reference to cycle the anchoring.
- A formula that must be edited after copying was anchored wrongly; go back to the first cell and fix the reference so it copies clean.
- Mixed references are the usual answer in a two-dimensional table: one assumption per row read across every period is $B5, one assumption per period read down every row is B$4.
- Check the anchoring at the far corner of the copied range, where every mistake shows.
How to fix it
- Worked example (invented): a growth rate in C3 is applied with =B10*(1+C3) and copied across; column D reads D3, which is empty, so growth is zero from the second period. Anchor it as =B10*(1+$C$3), or as =B10*(1+C$3) if there is a growth rate per period in row 3.
- Select the whole copied range and use Ctrl+` (show formulas) to read the pattern; references that should be identical across the range should look identical.
- Rebuild the first formula with the correct anchoring, then copy it over the whole range in one action rather than editing cells individually.
- Add a check row under any table of copied formulas that compares the last column's result to a direct calculation.
05 — Formula consistency
Copied irregular formulas
One formula per row, copied across every period, is what makes a model auditable. A row where the formula changes part-way across hides a different calculation in one column.
Within a row of forecast periods, every cell holds the same formula copied across. Where the calculation must genuinely differ (the first forecast year, a stub period), the exception sits in its own clearly labelled row or column, not inside the run.
Quick rules
- Excel's inconsistent-formula flag (the green triangle) marks a cell whose formula differs from its neighbours; treat each one as a question to answer.
- A period column that was edited by hand to fix a number is an irregular formula and a hidden hardcode at once.
- A stub period or a year with a different calculation belongs in its own column with its own label, so the run of copied formulas starts after it.
- Ctrl+` (show formulas) turns a row into a pattern the eye can check faster than reading each cell.
- The first and last forecast columns are where irregular formulas cluster: a first year that references the last actual differently, a last year with an extra term for terminal value.
How to fix it
- Select a row across the forecast, press Ctrl+\ (Windows) to select row differences, and every cell whose formula differs from the active cell is highlighted.
- Worked example (invented): a depreciation row reads =D20/5 in every column except one, which reads =D20/5+8 to make the total match a source. Remove the +8, find the reason for the difference, and put it on its own labelled row if it is real.
- Re-copy the first cell across the row once the exception is moved out, and confirm the outputs still reconcile.
- Turn on error checking for inconsistent formulas in Excel's options so new irregularities are flagged as they are created.
06 — Formatting consistency
Inconsistent formatting and units
Formatting is how a reader knows what a cell is and what its number means. When it varies across a block, the reader stops trusting it and starts checking every cell.
Apply one legend for inputs, calculations and links across the whole workbook, one number format per kind of value per block, and one unit and period basis per sheet, stated in the header.
Quick rules
- Colour marks provenance (input, calculation, link) and never changes meaning between sheets.
- Money in a block shares one scale (thousands or millions) and one precision; a mixed block of 1,250 and 1.25 with no unit label is unreadable.
- Percentages are stored as fractions and formatted as percentages; a growth rate stored as 5 next to one stored as 0.05 is an error waiting to be multiplied.
- Negatives display the same way throughout: parentheses or a minus sign, not both.
- Units and the period basis (fiscal year end, months in the period, currency) appear once in the sheet header and nowhere else conflict with it.
How to fix it
- Select a whole block and apply the number format in one action, then test a positive, a negative and a zero.
- Use Find and Select → Go To Special → Constants to locate typed values and confirm each is in the input style.
- Worked example (invented): a revenue schedule shows 1,200 in one year and 1.3m in the next because a column was entered in millions. Restate the column in the sheet's unit, then add a units label to the header so it cannot recur.
- Copy the number format from a correct block with Format Painter rather than rebuilding it, so the format code is identical.
07 — Checks
Missing checks
A model without checks can be wrong without anyone knowing. A checks block turns the identities a model must satisfy into flags that go red.
Maintain a checks block that tests every identity the model must satisfy (the balance sheet balances, sources equal uses, cash never goes below its minimum, schedules reconcile to the statements) and roll the results into one master flag displayed at the top of every sheet.
Quick rules
- A check compares two things that must be equal and returns zero or a flag; it never compares a figure to a typed expected value.
- Use a tolerance for floating-point differences (ABS(a − b) < 0.001), and show the difference, not just TRUE or FALSE, so the size of a break is visible.
- The master check is a single cell that sums or counts every individual check, linked to a visible cell on each sheet.
- Checks live in the model, not in a reviewer's head: if a reconciliation was done once by hand, add it as a row.
- A check that is permanently green because it compares a cell to itself is worse than none; test each check by deliberately breaking the model once.
How to fix it
- List the identities the model must satisfy and write one row per identity in a checks block near the outputs.
- Worked example (invented): the balance sheet check =ROUND(total assets − total liabilities − equity, 3) shows 0 in every period; a working-capital sign error makes it show 12 from the first forecast period, which points at the cash flow statement immediately.
- Link the checks block to a master flag and reference the flag in the header of every sheet in the model's warning style.
- Break one thing on purpose (flip a sign, delete a link) and confirm the relevant check turns red before restoring it.
08 — Sensitivities
Stale or broken sensitivities
A sensitivity table that no longer recalculates, or that points at a copy of the input, presents old numbers with the authority of a live analysis.
Sensitivity tables use Excel's data table feature or live formulas driven by the model's actual input cells, recalculate with the model, and show a centre value equal to the model's headline output.
Quick rules
- A data table's row and column input cells must be the cells the model actually reads; a table pointed at a copy of the input returns the same value in every cell.
- The centre of the table (base-case row and column) must equal the headline output; if it does not, the inputs or the output reference are wrong.
- Calculation set to automatic except for data tables leaves the table stale until F9; state the setting or set it to automatic.
- Pasting a table as values to speed up the workbook freezes it; keep the live table and hide it if performance is the problem.
- A data table must sit on the same sheet as its input cells; a reference to an input on another sheet breaks the table.
How to fix it
- Worked example (invented): a valuation table over discount rate and growth shows the same figure in every cell. Its row input cell was a copy of the discount rate that nothing references; repointing it to the live assumption cell brings the table to life.
- Nudge an input by a round amount and watch the table's corner cells: if they do not move, the table is stale or broken.
- Compare the centre cell of the table to the headline output with a check row, and flag a difference above a tolerance.
- Set calculation to automatic (including data tables) before delivery, or state in the model notes that tables need F9.
Final pass
Checks before handover
- 01No formula contains a typed number beyond the model's stated structural constants.
- 02One sign convention is stated and every subtotal follows it.
- 03No #REF! errors, no external workbook links and no stale cross-sheet references remain.
- 04Every copied formula is anchored so the far corner of the range is right.
- 05Each forecast row holds one formula copied across, with exceptions in their own labelled rows.
- 06One legend, one unit and one number format per block, stated in the sheet header.
- 07A checks block with a master flag exists and each check has been seen to turn red.
- 08Sensitivity tables are live, point at the real inputs and centre on the headline output.
StreetCred practice cases assess submitted workbooks and return cell-level feedback.
Start the free diagnostic →