Common Financial Modelling Mistakes (and How to Avoid Them)

Even professional analysts and investment bankers make financial modelling errors—not because they lack skills, but because Excel is unforgiving, and one misplaced decimal or broken formula can cascade through an entire model. A balance sheet that doesn't balance, a circular reference that loops infinitely, or a hardcoded number hiding in a formula can undermine months of work and lead to terrible business decisions. The difference between a model that's trusted and one that's ignored is precision, clear structure, and systematic validation.

In this guide, we'll walk through the 15 most common mistakes we see in financial models—the sneaky ones that pass casual review but fail when scrutinized. For each, we'll show you exactly what people do wrong, why it breaks, and the fix to implement today. We'll also provide colour conventions, a validation checklist, and internal links to our 3-statement financial model guide and Excel financial modelling best practices.

The Five Most Damaging Modelling Mistakes and Their Common Outcome

Structure Mistakes: Building on Sand

Mistake 1: Hardcoded Numbers in Formulas

What People Do: They embed assumptions directly into calculations instead of linking to a central Assumptions sheet.

// WRONG
=Revenue * 1.05

// CORRECT
=Revenue * Assumptions!B2

Why It's Wrong: Scenario analysis becomes impossible. You have to hunt through dozens of formulas to change the growth rate. Auditors hate it. When you hand the model to someone else, they won't know where the assumptions are.

The Fix: Dedicate one sheet to "Assumptions" and link everything to it. If a number appears more than once in a formula, it belongs in Assumptions.

Mistake 2: Mixing Inputs and Calculations on the Same Sheet

What People Do: Assumptions and calculated results are scattered across the same sheet, making it impossible to tell which cells are user inputs and which are derived.

Why It's Wrong: Someone changes what they think is an input and breaks three downstream calculations. The model loses credibility.

The Fix: Use separate sheets: Assumptions, Income Statement, Balance Sheet, Cash Flow Statement. Colour-code ruthlessly (see table below). Never let an input cell sit next to a calculation cell without a clear visual boundary.

Mistake 3: Broken Balance Sheet Check

What People Do: They either omit the balance check entirely or hide it where no one will look.

// Your balance sheet balance check (should be ZERO in all periods)
=Total Assets - Total Liabilities - Total Equity

Why It's Wrong: If the balance sheet doesn't balance, something is broken—maybe a forgotten link, a sign error in working capital, or cash not flowing correctly from the CFS. Without the check, you'll never find the error, and your model will produce garbage forecasts.

The Fix: Add a row at the bottom of your Balance Sheet in every period. It must equal zero. If it doesn't, the model is broken and unusable. Make it bright red and hypervisible.

Mistake 4: Date Timeline Built on Inconsistent Assumptions

What People Do: Years 1, 2, 3 are hardcoded as 2024, 2025, 2026, or each column header is manually entered.

Why It's Wrong: When you rebuild the model six months later, the dates are stale. Or someone copies the model forward and forgets to update the headers, and suddenly you're forecasting Year 5 as if it's Year 2.

The Fix: Create a single "Model Start Year" cell in Assumptions. Every column header derives from it:

// First period
=Assumptions!$B$1

// Next period
=B2 + 1

Now the timeline is always consistent and maintainable.

Calculation Mistakes: Where Formulas Fail

Mistake 5: Hardcoded Interest Expense (No Debt Schedule)

What People Do: They model interest as a fixed growth rate or a simple percentage of revenue.

// WRONG
=Prior Year Interest * 1.03

// WRONG
=Revenue * 2%

// CORRECT
Interest Expense = Debt Balance * Interest Rate

Why It's Wrong: Interest has no relationship to revenue—it's tied to your actual debt obligations. If you pay down debt, interest should fall. If this formula is disconnected from your debt schedule, interest expense will drift from reality, your net income will be wrong, and your free cash flow calculations will be unreliable.

The Fix: Build a debt schedule on your Balance Sheet showing opening balance, new debt raised, repayments, and closing balance. Calculate interest as:

=Closing Debt Balance (Prior Period) * Interest Rate

Or, for more precision (requires enabling iterative calculations):

=AVERAGE(Opening Debt, Closing Debt) * Interest Rate

Mistake 6: Retained Earnings as SUM(Net Income)

What People Do: They use a cumulative sum formula to calculate Retained Earnings.

// WRONG
=SUM($C$3:C3)  // Cumulative Net Income

// CORRECT
=Prior Period RE + Current Net Income - Dividends

Why It's Wrong: This double-counts profits. If your model runs for 5 years, Retained Earnings will include each year's net income multiple times. And it completely ignores dividend policy—if you've decided to pay out 50% of earnings, the formula won't reflect it.

The Fix: Use the correct formula:

=C10 (Prior Period RE) + C18 (Current NI) - C22 (Current Dividends)

Now Retained Earnings flows correctly to equity, your balance sheet balances, and dividends actually affect the model.

Mistake 7: Sign Convention Errors in Cash Flow Working Capital

What People Do: They apply inconsistent signs to working capital changes. One formula has a minus sign; another doesn't. The results don't make sense.

// Asset increases are uses of cash (negative)
CFO adjustment for AR = -(Current AR - Prior AR)

// Liability increases are sources of cash (positive)
CFO adjustment for AP = +(Current AP - Prior AP)

Why It's Wrong: Inconsistent signs break the relationship between the CFS and Balance Sheet. Closing cash from your CFS won't match closing cash on the Balance Sheet. Your model will fail its audit.

The Fix: Write out the logic explicitly in a comment. Never assume the sign; always derive it from first principles. An asset increase is a use of cash. A liability increase is a source of cash.

Mistake 8: Single-Driver Revenue Model (No Segmentation)

What People Do: They model all revenue as one line item with a single growth rate.

// OVERSIMPLIFIED
=Prior Revenue * 1.10  // 10% growth every year

Why It's Wrong: This masks business dynamics. If your SaaS company is shifting from 80% recurring revenue to 50%, a flat 10% growth assumption is dangerously misleading. You're not capturing the margin impact of that mix shift.

The Fix: Segment revenue by product, geography, or service type. Model units and prices separately:

// Segment 1
Segment 1 Revenue = Units * Price

// Segment 2
Segment 2 Revenue = Units * Price

// Total
Total Revenue = Sum of all segments

Now you can model different growth rates, margins, and mix shifts for each segment.

Mistake 9: Depreciation on New Assets Only (Forgetting the Opening Base)

What People Do: They calculate depreciation as a percentage of new CapEx, ignoring the existing PP&E base.

// WRONG
Depreciation = New CapEx * Depreciation Rate

// CORRECT
Depreciation = (Opening PP&E + New CapEx) / Useful Life

Why It's Wrong: The opening balance sheet has PP&E assets that are still depreciating. If you ignore them, depreciation expense will be understated, net income will be overstated, and cash flow will be wrong.

The Fix: Create a PP&E schedule:

Closing PP&E = Opening PP&E + CapEx - Depreciation Expense
Depreciation = (Opening PP&E + CapEx) * Depreciation Rate

Now every asset depreciates, and your model reflects economic reality.

Mistake 10: Missing Terminal Value Sanity Check

What People Do: They calculate a terminal value in a DCF but never check if it's reasonable.

Why It's Wrong: Terminal value often represents 60-80% of a DCF valuation. A bad assumption here can make your entire model worthless. If you assume 5% perpetual growth but the company's market is shrinking, you've overvalued it by billions.

The Fix: Add a sanity check row in your terminal value section:

// Terminal Value as % of Enterprise Value
=Terminal Value / (PV of Explicit Forecast + Terminal Value)

// Should be between 50-80% for most models
// If it's >85%, your explicit forecast period is too short
// If it's <40%, your long-term growth assumption is too low

If the ratio is outside the normal range, investigate before presenting the valuation.

Mistake 11: Circular References Without Iteration Enabled

What People Do: They create a formula that depends on itself (e.g., interest on debt, which depends on closing cash, which depends on net income, which depends on interest), but don't enable iterative calculations.

Why It's Wrong: Excel will either error or produce infinite #REF! values. Your model will be broken.

The Fix: Option A (simple): Calculate interest on the prior period's closing balance to avoid circularity entirely. Option B (precise): Enable iterative calculations in Excel:

File > Options > Formulas > Enable Iterative Calculation
Set maximum change to 0.01
Set maximum iterations to 100

Then use average balances:

=AVERAGE(Opening Debt, Closing Debt) * Interest Rate

Validation Mistakes: What You Should Check

Mistake 12: No Unit Consistency (Mixing 000s and Millions)

What People Do: Revenue is in thousands, COGS is in millions, and CapEx is in units. The model is a unit soup.

Why It's Wrong: Your balance check passes, but your numbers are nonsense. EBITDA might show as £50 instead of £50 million, and you'll make a terrible business decision based on garbage data.

The Fix: Pick one unit and stick to it. Put a label in the top-left of your model: "All figures in £000s" or "All figures in £m". Convert any inputs that come in different units.

// If AP comes in from a source in full units but your model is in £000s
AP Input = AP Source Data / 1000

Mistake 13: No Error Check Cells (Circular References, Broken Links)

What People Do: They build the model and assume it's correct. They don't add any diagnostic formulas to catch common errors.

Why It's Wrong: A small error propagates silently. By the time you notice, you've already made a business decision.

The Fix: Add a "Model Diagnostics" tab with checks:

// Balance Sheet Check (should be zero)
=SUMPRODUCT((Balance Sheet!Balance Check = 0) * 1)

// Cash reconciliation (CFS closing cash = BS cash)
=ABS(CFS!Closing Cash - BS!Cash)

// Any circular reference errors
=SUMPRODUCT(--(IFERROR(Model!A:Z, -1) = -1))

If any of these deviate from expected values, you'll spot the error immediately.

Mistake 14: Formulas Stretched Across Hidden Rows

What People Do: They hide some rows for "cleanliness" but leave formulas that reference them. The hidden rows are accidental, or someone hid them to hide a mistake.

Why It's Wrong: An auditor or colleague unhides the rows and finds broken logic. Or the hidden row contains a hardcoded assumption no one knew about. Trust evaporates.

The Fix: Never hide rows that contain active formulas. Either:

(a) Delete the row and move the calculation elsewhere, or (b) Leave the row visible and clearly label it as a helper row

If you must hide rows, use a comment in the formula to explain why they're hidden:

// This row is hidden because it's a helper calculation
// See row 15 for the final result
=Row 14 + Row 12

Mistake 15: No Scenario Toggle or Sensitivity Analysis

What People Do: They build a single forecast and call it done. There's no way to run a bull/base/bear scenario or stress-test assumptions.

Why It's Wrong: A model with no flexibility is a model no one will use. When the CFO asks "What if growth is 5% instead of 10?", you have to manually change 30 cells.

The Fix: Add a "Scenario" selector in your Assumptions sheet:

// In cell B1
Scenario: [Dropdown: Base / Bull / Bear]

// In cell B2 (Revenue Growth)
=IF($B$1="Bull", 12%, IF($B$1="Bear", 3%, 8%))

// In cell B3 (Operating Margin)
=IF($B$1="Bull", 25%, IF($B$1="Bear", 18%, 22%))

Now a single dropdown changes the entire forecast. Add a sensitivity table below the model to show how valuation changes with different assumptions.

Presentation Mistakes: How Your Model Looks

Mistake 16: No Colour Convention (Data Soup)

What People Do: Every cell is black text on white background. There's no visual distinction between inputs and formulas. It's impossible to navigate.

Why It's Wrong: Auditors can't quickly identify where assumptions are. Clients assume the model is sloppy. You lose credibility.

The Fix: Use a consistent colour scheme. Here's the professional standard:

Apply this consistently. Every model you build should use the same scheme so anyone can navigate instantly.

Quick Validation Checklist

Before you present a model, run through this checklist. If all boxes are ticked, your model is solid.

• Assumptions Sheet: Every input is on a dedicated sheet. All formulas in other sheets link to it.
• Balance Check: Total Assets = Total Liabilities + Equity in every period. Cell shows exactly zero.
• Cash Reconciliation: Closing cash on the CFS matches closing cash on the Balance Sheet.
• Net Income Flow: Net income from the Income Statement is correctly linked to Retained Earnings and the CFS.
• Working Capital Signs: Asset increases are negative in the CFS; liability increases are positive.
• Interest Calculation: Linked to an actual debt schedule, not a growth rate or percentage of revenue.
• Depreciation Basis: Includes both opening and new assets, not just new CapEx.
• Unit Consistency: All numbers are in the same unit (£000s, £m, or actual). Label is clearly visible.
• No Hardcoded Numbers: Search your model (Ctrl+H) for any formula referencing hard values that should be assumptions.
• Colour Scheme: Inputs are blue, formulas are white, links are green. Consistent across all sheets.
• No Circular References: Either resolved by prior period balance, or iterative calculations enabled.
• Scenario / Sensitivity: Model supports at least base/bull/bear scenarios or a sensitivity table.
• No Hidden Rows with Formulas: All active calculations are visible.
• Error Check Cells: Diagnostics tab (or cells) catching broken links, circular refs, and balance sheet errors.
• File Size & Performance: Model opens and recalculates in under 5 seconds. No unnecessary volatility.