How do I handle circular references in a financial model?

Circular references usually appear in interest-expense calculations (interest depends on average debt balance, which depends on net income, which depends on interest). Two options: (1) turn on iterative calculation (File → Options → Formulas → tick Enable iterative calculation, set max iterations 100, max change 0.001) — simple but makes models fragile; (2) restructure with a 'circ breaker' toggle: use beginning-of-period debt instead of average for the interest calc, which removes the circularity entirely. Option 2 is the industry standard — iterative calc is fine for early drafts, not for audited models.

Data Analysis

How to Build a Financial Model in Excel From Scratch

Q: How do I build a financial model in Excel from scratch?

Start with three linked financial statements: income statement, balance sheet, and cash flow statement. Put assumptions on a dedicated Assumptions sheet so every input can be changed in one place. Colour-code inputs blue, formulas black, and links to other sheets green. Use consistent period layout across every sheet (rows = line items, columns = months or years). Link statements so changes in assumptions flow through automatically. Add scenario analysis via Data Tables or Scenario Manager. Keep formulas simple and auditable — one formula per row, no nested IFs more than two deep.

Q: What's the difference between a 3-statement model and a DCF?

A 3-statement model projects the income statement, balance sheet, and cash flow statement over time — the foundation every other model sits on. A DCF (discounted cash flow) takes the free cash flows from the 3-statement model, projects them 5–10 years, applies a discount rate (WACC), and adds a terminal value to produce an enterprise valuation. You cannot build a DCF without a working 3-statement model first — the 3-statement is the input, the DCF is one of many possible outputs.

Q: Should I use SUMIFS or SUMPRODUCT in financial models?

SUMIFS is faster, more readable, and the modern default — use it for almost all conditional sums. SUMPRODUCT still wins when you need to multiply two ranges element-wise before summing (weighted averages, portfolio math), or when you need to sum across multiple columns of a lookup range that SUMIFS cannot handle in one formula. Rule of thumb: SUMIFS first, SUMPRODUCT only when SUMIFS runs out of features.

By Sagnik Bhattacharya24 Mar 202616 min read

Coding Liquids blog cover featuring Sagnik Bhattacharya for Excel for Financial Modelling — A Beginner's Guide, with forecast visuals, tables, and financial model elements.

Financial modelling is the art of building a mathematical representation of a company's financial performance. Excel remains the industry standard tool for this — from startup pitch decks to Wall Street analysis. Here's how to build your first model.

Coming Soon

Complete Excel Guide with AI Integration

Master formulas, pivot tables, data analysis, and charts — with AI integration.

Learn more

This tutorial is beginner-first and opinionated. It walks through the actual sequence a junior analyst follows on day one: lay out assumptions, build the revenue calc, layer in costs, wire up scenarios, then polish for an audit. We use a SaaS startup as the running example (ARPC, churn, growth), but the same structure works for retail, services, or manufacturing — swap the drivers, keep the architecture.

Everything shown works in Excel 2019, 2021, Microsoft 365, and Excel for the web. If you are still building the formula stack underneath, read the advanced formulas reference first — this tutorial assumes comfort with SUMIFS, XLOOKUP, LET, IF, and named ranges. For the broader Excel skill set, the Excel formulas and analysis hub collects every related tutorial in reading order.

What you will build: a 36-month revenue and cash-flow projection with colour-coded assumptions, three switchable scenarios (base/bull/bear), a cost model separated into fixed/variable/semi-variable, and a one-page Summary sheet with KPI cards. The techniques extend directly to 3-statement models and DCFs later.

Structure of a Financial Model

Every financial model has three core sections:

Assumptions — Input variables that drive the model (growth rate, costs, prices)
Calculations — Formulas that process the assumptions
Outputs — Financial statements and charts. Present them using interactive dashboards for maximum impact

Best practice: keep assumptions on a separate sheet, colour-code input cells (blue font for inputs, black for formulas), and never hardcode numbers inside formulas.

Colour convention (industry standard): blue for raw inputs/assumptions, black for formulas that reference same-sheet cells, green for formulas that reference OTHER sheets, red for external links (to other workbooks — avoid these if you can). Apply this with a saved cell style (Home → Cell Styles → New Cell Style) so you can one-click every cell type. Auditors scan colour before they read formulas — models that follow the convention are trusted faster.

Real-world scenario: A seed-stage founder preparing for a VC pitch hard-coded growth assumptions into formulas like =B5*1.05. When the lead VC asked "what happens if growth is 3%?", the founder had to find and change 36 formulas across 12 sheets — during the meeting. A properly-built model puts the 5% on the Assumptions sheet once, and every formula reads from that single cell. Change it to 3%, the whole model updates in one second. This is the single biggest skill difference between beginner and professional models.

Beginner pitfall: Resist the urge to copy-paste values into an output sheet "to freeze them for the board". The moment you do, someone updates an assumption and the board sheet silently lies. Instead, copy the whole file as Model_v1_BoardApproved_2026-04-15.xlsx for the audit trail and keep the live model live.

What we are building

One workbook, four sheets, in this order:

Assumptions (sheet name Assumptions). Every input lives here — growth, ARPC, churn, costs, scenario toggle. Three columns per assumption: Base, Bull, Bear, plus a live column that CHOOSEs based on the scenario dropdown.
Calc (sheet name Calc). 36 monthly columns. Customer corkscrew, revenue, cost lines, EBITDA, cash. Every formula reads from Assumptions — never hard-codes.
Summary (sheet name Summary). One-page KPI dashboard: total revenue, ending customers, EBITDA margin, runway, cash on hand. GETPIVOTDATA-style references back to Calc.
Checks (sheet name Checks). Single column of boolean checks: balance ties, cumulative reconciliations, sign tests. One red cell on this sheet means the model is broken — no exceptions.

The discipline: Assumptions is the only place anyone types numbers. Calc contains formulas only. If a number on Summary looks wrong, the fix is in Assumptions — never paint a cell on Summary to "correct" it. This split is the single change that separates a junior model from a defensible one.

Step 1 — Lay out the Assumptions sheet

On the Assumptions sheet, leave row 1 for a section header. In row 2, type the column headers:

A2: Assumption    B2: Unit    C2: Base    D2: Bull    E2: Bear    F2: Live

In rows 4 onwards, list every assumption. For a SaaS model, start with these:

A4: Starting customers          B4: count   C4: 100     D4: 100     E4: 100
A5: Monthly growth rate (Y1)    B5: %       C5: 5%      D5: 7%      E5: 3%
A6: Monthly growth rate (Y2)    B6: %       C6: 4%      D6: 6%      E6: 2%
A7: Monthly growth rate (Y3)    B7: %       C7: 3%      D7: 5%      E7: 1.5%
A8: ARPC Year 1                 B8: GBP     C8: 500     D8: 500     E8: 500
A9: ARPC Year 2                 B9: GBP     C9: 525     D9: 540     E9: 510
A10: ARPC Year 3                B10: GBP    C10: 550    D10: 565    E10: 530
A11: Monthly churn (Y1)         B11: %      C11: 3%     D11: 2%     E11: 4%
A12: Monthly churn (Y2)         B12: %      C12: 2.5%   D12: 1.5%   E12: 3.5%
A13: Monthly churn (Y3)         B13: %      C13: 2%     D13: 1%     E13: 3%

In cell H1, drop the scenario toggle: data validation list with source Base,Bull,Bear. Name the cell ScenarioSelected (Formulas → Define Name).

In F4, the live column formula:

=CHOOSE(MATCH(ScenarioSelected, {"Base","Bull","Bear"}, 0), C4, D4, E4)

Drag down through F13. Now every formula in the rest of the model reads from column F only — toggling H1 swaps the entire scenario in one click.

Colour every input cell blue. Cells C4:E13 are inputs (blue font). F4:F13 are formulas (black). H1 is an input (blue). Apply the convention with a saved cell style — Home → Cell Styles → New Cell Style — so the colour is one click for every new assumption.

Beginner pitfall. Forgetting to name the toggle cell ScenarioSelected and writing $H$1 in every CHOOSE. Then someone moves the toggle to row 2 and 50 formulas break silently. Named ranges survive moves; raw cell addresses do not.

Step 2 — Build the customer corkscrew on the Calc sheet

On the Calc sheet, freeze row labels in column A and date headers in row 4. In B4, the first month header:

B4: =DATE(2026, 1, 1)

In C4 through AM4 (36 months), drag =EDATE(B4, 1) right. Format as mmm yy.

The customer corkscrew runs in rows 6–9:

A6: Opening customers    B6: =Assumptions!F4
                         C6: =B9
                         (drag C6 right to AM6 — each month opens with prior month's close)

A7: + New customers      B7: =B6 * Assumptions!F5
                         (months 1–12 use Y1 growth at F5; months 13–24 use F6; months 25–36 use F7)
                         Use INDEX or CHOOSE on the year:
                         B7: =B6 * INDEX(Assumptions!$F$5:$F$7, INT((COLUMN(B7)-COLUMN($B7))/12)+1)

A8: − Churned customers  B8: =B6 * INDEX(Assumptions!$F$11:$F$13, INT((COLUMN(B8)-COLUMN($B8))/12)+1)

A9: = Closing customers  B9: =B6 + B7 - B8

The pattern — opening + additions − reductions = closing, with closing flowing into the next opening — is a corkscrew. It is the industry-standard layout for any running balance: customers, cash, debt, headcount, inventory. Once a reader sees this shape, they know exactly where to look to audit any number.

For revenue, two more rows below:

A11: ARPC (live)         B11: =INDEX(Assumptions!$F$8:$F$10, INT((COLUMN(B11)-COLUMN($B11))/12)+1)
A12: Monthly revenue     B12: =B9 * B11

Real-world scenario. A B2B SaaS model projecting 36 months uses three parallel corkscrews: customers, MRR, and headcount. When a VC asks "what drives the spike in Q3 of year 2?", the analyst points at the customer corkscrew — additions up from a new marketing hire — and answers in 10 seconds. Without the corkscrew the same question takes 5 minutes of formula-tracing.

Beginner pitfall. Don't mix the corkscrew pattern with dynamic-array spills. Corkscrews depend on cell-to-cell relative references, which break the moment a source range spills unexpectedly. Keep dynamic arrays (FILTER, SORT, GROUPBY) on separate helper sheets and link into the corkscrew with plain references.

Step 3 — Annual summary roll-ups

To the right of the 36 monthly columns (column AN onwards), build year totals:

AN4: Year 1    AO4: Year 2    AP4: Year 3
AN12: =SUM(B12:M12)        (Year 1 revenue)
AO12: =SUM(N12:Y12)         (Year 2 revenue)
AP12: =SUM(Z12:AM12)        (Year 3 revenue)

For closing customer counts (a balance, not a flow), pick the last month of the year — not the sum:

AN9: =M9      (Year 1 ending customers — December of Y1)
AO9: =Y9      (Year 2 ending customers)
AP9: =AM9     (Year 3 ending customers)

Beginner pitfall. Summing balance-sheet rows (customers, cash, debt) across months. A balance-sheet row is a state at a point in time, not a flow. Summing twelve monthly customer counts gives you a meaningless total. Sum income statement rows (revenue, costs); pick last-month-of-period for balance rows.

Step 4 — Cost modelling and EBITDA

Costs split into three shapes — fixed, variable, semi-variable — each with a different formula pattern. On Assumptions, add:

A20: Fixed monthly OpEx       (rent, base salaries) — single £ figure per year
A21: Variable cost (% of rev) (COGS, payment fees) — single % figure
A22: Customers per support agent  (semi-variable trigger)
A23: Support agent loaded cost (£/month)

On Calc, in rows 14–18:

A14: Fixed OpEx       B14: =INDEX(Assumptions!$F$20:$F$22, INT((COLUMN(B14)-COLUMN($B14))/12)+1)
                          (or just reference one cell if fixed costs don't step)
A15: Variable cost    B15: =B12 * Assumptions!$F$21        (revenue × COGS %)
A16: Support cost     B16: =CEILING(B9 / Assumptions!$F$22, 1) * Assumptions!$F$23
                          (one agent per N customers, rounded up)
A17: Total OpEx       B17: =B14 + B15 + B16
A18: EBITDA           B18: =B12 - B17

The CEILING(customers / per-agent, 1) is the semi-variable pattern: one agent up to 500 customers, two from 501 to 1000, and so on. Step-functions like this are how real cost lines behave — modelling them as a flat % of revenue is a common beginner error that hides the true unit economics.

Real-world scenario. An early-stage marketplace modelled marketing spend as a flat £10k/month. Board reviewed and asked "this implies CAC halves at 10× revenue — is that realistic?" Answer: no. The fix was modelling marketing as =NewCustomers * CAC with CAC on Assumptions. Now the model respects the spend-growth relationship and a realism check happens automatically.

Beginner pitfall. Including employer NI, pension, and benefits inside the gross salary line. Add a separate LoadedCostMultiplier assumption (typically 1.15–1.30 in the UK) and apply it once: =Salary * LoadedCostMultiplier. When statutory rates change, you update one cell, not 20.

Step 5 — Cash corkscrew and runway

Below EBITDA, build the cash corkscrew (rows 20–24):

A20: Opening cash       B20: =Assumptions!StartingCash
                        C20: =B23
                        (drag right)

A21: + Cash in (EBITDA > 0)    B21: =MAX(B18, 0)
A22: − Cash out (EBITDA < 0)   B22: =MAX(-B18, 0)
A23: = Closing cash             B23: =B20 + B21 - B22

A24: Monthly burn               B24: =MAX(B22 - B21, 0)
A25: Runway (months)            B25: =IF(B24=0, ">36", B23 / B24)

The runway row uses IF to handle the profitable case — when burn is zero, runway is effectively infinite, displayed as >36. Without the guard you get #DIV/0! the moment EBITDA flips positive.

Beginner pitfall. Computing runway from current cash divided by last month's burn. If burn is accelerating, that overstates how long you have. Best practice is a 3-month trailing average: =B23 / AVERAGE(OFFSET(B24, 0, -2, 1, 3)) — handles seasonality and ramp.

Step 6 — Build the Summary sheet and Checks sheet

On Summary, lay out the KPI cards. Each card is a label cell + a large number cell (36pt bold font, single-cell merge):

A1: SCENARIO              B1: =Assumptions!H1
A3: Total revenue (3yr)   B3: =SUM(Calc!AN12:AP12)
A4: Ending customers      B4: =Calc!AM9
A5: EBITDA Y3             B5: =SUM(Calc!Z18:AM18)
A6: EBITDA margin Y3      B6: =B5 / Calc!AP12
A7: Cash on hand (M36)    B7: =Calc!AM23
A8: Runway at M36         B8: =Calc!AM25
A9: Months to break-even  B9: =MATCH(TRUE, INDEX(Calc!B18:AM18>0, 0), 0)

The MATCH(TRUE, INDEX(...), 0) pattern finds the first month EBITDA goes positive. INDEX wraps the boolean array so MATCH works without Ctrl+Shift+Enter on older Excel.

On Checks, build the integrity tests. One row per check, formula returns "OK" or "ERR":

A2: Customers reconcile        B2: =IF(SUM(Calc!B7:AM7)-SUM(Calc!B8:AM8)=Calc!AM9-Calc!B6, "OK", "ERR")
A3: Revenue = customers × ARPC B3: =IF(ROUND(Calc!B12,2)=ROUND(Calc!B9*Calc!B11,2), "OK", "ERR")
A4: EBITDA = Rev − OpEx        B4: =IF(ROUND(Calc!B18,2)=ROUND(Calc!B12-Calc!B17,2), "OK", "ERR")
A5: Cash closes ≥ 0            B5: =IF(MIN(Calc!B23:AM23)>=0, "OK", "ERR — negative cash")
A6: Total checks               B6: =COUNTIF(B2:B5, "OK") & " / " & COUNTA(B2:B5)

Conditional-format B2:B5 red when value is anything other than "OK". The day a Check row goes red, stop — fix the model before you trust any output. Green checks are not "the model is correct"; they are "the model is internally consistent". Two different things.

Step 7 — Worked example: 36-month SaaS projection

With the assumptions from Step 1 (Base scenario: 100 starting customers, 5%/4%/3% monthly growth, £500/525/550 ARPC, 3%/2.5%/2% churn) and a £50k/mo Fixed OpEx, 18% variable cost ratio, and £4k/mo loaded support cost per 500 customers, the model produces:

Ending customers (Month 36): 171 — net monthly growth is only 2% / 1.5% / 1% across the three years (growth minus churn), so 100 × 1.71 ≈ 171 over 36 months. The "modest" shape is the correct lesson: high gross-growth headlines hide whatever the churn line is doing.
Year 1 revenue: ~£684k. Year 2: ~£881k. Year 3: ~£1.07M. Total 3-year: ~£2.63M.
Months to EBITDA-positive: Month 13. M1 revenue is £51k against £63k OpEx (£50k fixed + £9.2k variable + £4k support) — a £12k loss. Revenue grows ~2% per month while fixed cost is flat, so the gap closes; by M13 closing customers are ~127, revenue ~£67k, OpEx ~£62k, EBITDA flips positive.
Year 3 EBITDA margin: ~21%. The £50k fixed-cost base is heavy relative to a £1.07M revenue line — typical for an early-stage SaaS that has not yet hit operating leverage.
Cash on hand at Month 36 (assuming £200k starting cash): ~£420k. The cash dip bottoms at ~£113k around Month 12 before recovering — the model warns you exactly when the runway is tightest. Runway: >36 (positive, growing cash from M14 onward).

Now toggle H1 on Assumptions to Bear. Watch every number reflow: growth/churn worsen (3%/2%/1.5% growth vs 4%/3.5%/3% churn — net negative growth in Y1), customer count shrinks instead of grows, revenue collapses below the fixed cost base, and cash burns through the £200k starting balance well before Month 36 — the Checks!B5 row turns red ("ERR — negative cash"). The model just told you the bear case requires a funding round mid-projection. That insight took one dropdown click.

The full formula reference

Where	Formula	What it does
Assumptions!F4 (live column)	`=CHOOSE(MATCH(ScenarioSelected, {"Base","Bull","Bear"}, 0), C4, D4, E4)`	Picks the active scenario value; every Calc formula reads from F
Calc!B6 (opening customers, M1)	`=Assumptions!F4`	Seed the corkscrew with the starting-customers assumption
Calc!C6 (opening, M2 onwards)	`=B9`	Open with prior month's close — the corkscrew link
Calc!B7 (new customers)	`=B6 * INDEX(Assumptions!$F$5:$F$7, INT((COLUMN(B7)-COLUMN($B7))/12)+1)`	Picks Y1/Y2/Y3 growth rate based on which 12-month block this column is in
Calc!B8 (churned)	`=B6 * INDEX(Assumptions!$F$11:$F$13, INT((COLUMN(B8)-COLUMN($B8))/12)+1)`	Same INDEX pattern, churn rate
Calc!B9 (closing customers)	`=B6 + B7 - B8`	Corkscrew: opening + adds − reductions
Calc!B12 (monthly revenue)	`=B9 * B11`	Closing customers × current-year ARPC
Calc!B15 (variable cost)	`=B12 * Assumptions!$F$21`	Revenue × COGS percent
Calc!B16 (semi-variable support)	`=CEILING(B9 / Assumptions!$F$22, 1) * Assumptions!$F$23`	Step-function: one agent per N customers, rounded up
Calc!B18 (EBITDA)	`=B12 - B17`	Revenue minus total OpEx — the headline operating number
Calc!B23 (closing cash)	`=B20 + B21 - B22`	Cash corkscrew close
Calc!B25 (runway)	`=IF(B24=0, ">36", B23 / B24)`	Months until cash exhausts at current burn; guard against /0
Summary!B6 (EBITDA margin Y3)	`=B5 / Calc!AP12`	Year 3 EBITDA divided by Year 3 revenue
Summary!B9 (months to break-even)	`=MATCH(TRUE, INDEX(Calc!B18:AM18>0, 0), 0)`	First month EBITDA goes positive
Checks!B5 (cash never negative)	`=IF(MIN(Calc!B23:AM23)>=0, "OK", "ERR — negative cash")`	Single-cell sanity check on the cash corkscrew
Gross margin (anywhere)	`=(Revenue - COGS) / Revenue`	Standard profitability ratio
Operating margin	`=(Revenue - Total OpEx) / Revenue`	EBITDA as a % of revenue
Customer LTV (simple)	`=ARPC / Churn Rate`	Lifetime value, simple form; for gross-margin LTV multiply by GM%
CAC payback (months)	`=CAC / (ARPC * GrossMargin)`	Months to recoup acquisition cost from gross-margin contribution
Net Present Value	`=NPV(WACC, CashFlows) + InitialCash`	NPV excludes the initial outlay — add it back manually
Internal Rate of Return	`=IRR(CashFlows)`	The discount rate at which NPV = 0; first cash flow must be the negative outlay

Variations: customise for your business shape

The skeleton (Assumptions sheet → Calc corkscrew → Summary KPIs → Checks) stays identical. What changes is the revenue driver and the cost split.

Business model	Revenue driver	Distinct cost shape
SaaS / subscription	Customers × ARPC × (1 − churn)	Variable cost low (≈10–25%); fixed cost high (engineering)
Marketplace / two-sided	GMV × take rate	Acquisition cost on both sides; semi-variable trust & safety
E-commerce	Orders × AOV × repeat rate	Variable cost very high (≈60–75% COGS + fulfilment); fixed low
Agency / consulting	Billable hours × rate × utilisation	Cost dominated by salaries × loaded multiplier; small fixed overhead
Manufacturing	Units × ASP	Variable BOM cost + step-fixed factory capacity (CAPEX waves)
Subscription content / media	Subscribers × ARPU + ad CPM × impressions	Mostly fixed (content production); variable infra at scale

For investor-facing models, add a 5-year DCF tab: project free cash flow from EBITDA (subtract D&A, add back, subtract CapEx and ΔWC), discount at WACC, add a terminal value with Gordon growth. The =NPV() and =IRR() functions handle the math; the analytical work is choosing the WACC and terminal growth defensibly.

Scenario Analysis

Build three scenarios by changing assumptions:

Base case — Realistic projections
Best case — Higher growth, lower churn
Worst case — Lower growth, higher costs

Use Excel's Data Tables (Data → What-If Analysis → Data Table) to automatically calculate outputs for different input values. Or use Scenario Manager (Data → What-If Analysis → Scenario Manager) to save and switch between named scenarios. For a detailed walkthrough of all four tools, see the complete what-if analysis guide.

The scenario toggle pattern: Put a single cell on the Assumptions sheet called ScenarioSelected with data validation dropdown {Base, Bull, Bear}. Every assumption has three columns (one per scenario) plus a live column using =CHOOSE(MATCH($ScenarioSelected,{"Base","Bull","Bear"},0), BaseCol, BullCol, BearCol). All formulas in the model read from the live column only. Toggling the dropdown switches the entire model in one click — cleaner than Scenario Manager, easier to audit than Data Tables.

Real-world scenario: A growth-stage CFO running the monthly forecast review switches between scenarios mid-meeting to answer "what if churn stays at 4%?" in real time. Without the toggle, the same question meant 20 minutes of spreadsheet wrangling after the meeting. The scenario toggle pattern is the single highest-leverage Excel skill in a finance job.

Beginner pitfall: Sensitivity analysis on more than two variables gets unreadable fast. Use a one-variable Data Table for "how does NPV move with discount rate?" and a two-variable Data Table for "how does NPV move with discount rate AND terminal growth?". For three+ variables, use a chart of several two-variable tables rather than trying to tabulate all combinations.

Common mistakes

Hard-coding numbers inside formulas. =B5 * 1.05 hides the 5% growth assumption from the auditor. Move every number into a single named cell on Assumptions and reference it. Hard-coded model = unauditable model.
Mixing inputs and formulas in the same cell. Cells must be either pure input (blue) or pure formula (black). The moment you write =B5 + 1000, the 1000 has nowhere to live and nobody can find it later.
Different formula in different cells of the same row. Every cell in a row must compute the same thing. Two different formulas across one row breaks the "drag right" mental model and is the #1 source of silent errors. Use IF inside one formula instead of switching formulas.
Skipping the colour convention. Blue inputs, black formulas, green cross-sheet links, red external workbook links. Auditors scan colour before they read formulas — a model that follows the convention is trusted in 30 seconds; one that doesn't gets a 30-minute walk-through.
Merged cells in calculation ranges. Break sort, structured references, and SUMIFS. Use "Center Across Selection" (Ctrl+1 → Alignment → Horizontal → Center Across Selection) for visual alignment without the merge.
Summing balance rows across periods. Customers, cash, debt, headcount are states. Summing 12 months of customer counts produces a meaningless total. Sum income-statement rows; pick last-period for balance rows.
Volatile functions in long calc chains. INDIRECT, OFFSET, TODAY, NOW recalculate on every cell change. Replace INDIRECT with INDEX, OFFSET with structured references, and cache TODAY into a single Assumptions cell every formula reads from. Speed-up: 10–30×.
Freezing the wrong panes. Freeze at B5 (row 4 + column A locked) so the date headers in row 4 and the row labels in column A stay visible while scrolling through 36 monthly columns.
Treating the Summary sheet as something to "tweak". Summary is read-only output. If a number looks wrong, the fix is in Assumptions or Calc — never edit the Summary cell. Same rule as the Project Tracker tutorial.
No Checks sheet at all. A model without integrity checks is a model that will silently lie at the worst possible moment (the board meeting). Even four checks (cash positive, customers reconcile, revenue reconciles, EBITDA ties) catch most errors.

Troubleshooting

Five problems that derail nearly every beginner financial model, with exact fixes.

Balance sheet doesn't balance. The single most common error in 3-statement modelling. Fix: add an explicit check row — =Assets - (Liabilities + Equity) — formatted red when not zero. If off by a single-period retained-earnings figure, you forgot to link Net Income from the income statement into Retained Earnings on the balance sheet. If off by accumulated depreciation, the D&A link to fixed assets is broken. Binary-search the problem: check which period is the first non-zero, then trace the flows in/out of that period.
Circular reference warning on opening the file. Usually the interest-expense-on-average-debt loop. Quick fix: File → Options → Formulas → tick "Enable iterative calculation" (max 100, max change 0.001). Better fix: switch interest calc from average-balance to beginning-of-period balance — removes the circularity entirely and makes the model auditable.
#VALUE! errors spread across whole sheets after adding a new row. You pasted a value into a column that had a named-range anchor point, breaking the range. Open Formulas → Name Manager, look for ranges showing #REF!, and re-point them. Prevent this in future by converting every raw-data range into an Excel Table — names auto-extend.
SUMIFS returns 0 when the source clearly has matching values. Source cells are text-typed numbers. Select the column → Data → Text to Columns → Finish to re-parse. Alternative: wrap the sum range in VALUE(). Root cause is usually a CSV import — use the Power Query tutorial for a permanent fix.
Model takes 30+ seconds to recalculate after every edit. Volatile functions (INDIRECT, OFFSET, TODAY, NOW) recompute on every cell change. Replace INDIRECT with INDEX where possible, replace OFFSET with structured references, and cache TODAY() into a single cell at the top of the Assumptions sheet (everything references =Assumptions!ModelDate). Typical speed-up: 10–30×.

Frequently Asked Questions

How do I build a financial model in Excel from scratch?
Start with three linked statements (income, balance sheet, cash flow). Put assumptions on a dedicated sheet. Colour-code inputs blue, formulas black, cross-sheet links green. Consistent period layout (rows = line items, columns = months/years). Link statements so changes flow automatically. Add scenario toggles. Keep formulas simple and auditable.

What are the best practices for financial models in Excel?
One formula per row. Never hard-code numbers inside formulas. Colour-code (blue/black/green). Named ranges or structured references for key assumptions. Error-check rows (A = L + E, sum-of-parts = total). Sensitivity analysis. Documented assumptions. One-page Summary dashboard. Clarity beats complexity — auditors grade on readability.

What's the difference between a 3-statement model and a DCF?
3-statement = projected income statement + balance sheet + cash flow over time. DCF = takes free cash flows from the 3-statement, projects 5–10 years, applies WACC, adds terminal value = enterprise valuation. You can't build a DCF without a working 3-statement first.

Should I use SUMIFS or SUMPRODUCT in financial models?
SUMIFS is faster, more readable — use it for almost everything. SUMPRODUCT wins for multiply-then-sum (weighted averages, portfolio math) and some multi-column sums SUMIFS can't handle. SUMIFS first, SUMPRODUCT when SUMIFS runs out.

How do I handle circular references?
Two options: (1) File → Options → Formulas → Enable iterative calculation (simple but fragile); (2) restructure with a "circ breaker" — use beginning-of-period debt instead of average for interest calc (industry standard for audited models).

When this is not the right tool

Real-time multi-user collaboration. Excel locks the file. For teams editing the same model simultaneously (FP&A team during close), use a planning tool with proper concurrency: Anaplan, Pigment, Cube, or Workday Adaptive. Excel co-authoring on OneDrive helps for 2–3 editors, breaks for 5+.
Production financial reporting / consolidations. Audited month-end closes need an ERP (NetSuite, SAP) plus a consolidation tool (BlackLine, OneStream). Excel is fine for analysis on top of that data, never for being the system of record.
Models with >500k rows of source data. Excel chokes; Power Pivot helps but the workflow becomes painful. Move source data to a database and use Power Query as the bridge — see the Power Query tutorial.
Multi-currency consolidation with FX retranslation. Possible in Excel but error-prone. Use Cube or a proper EPM tool when you cross more than two currencies and care about the cumulative translation adjustment.
Statistical / Monte Carlo simulation at scale. Excel handles 1,000-iteration Monte Carlo with Data Tables; for 100k+ iterations or correlated distributions, switch to Python (NumPy + pandas) or R. The output table can come back to Excel for presentation.
You actually need a tracker, not a model. If the goal is monitoring status of in-flight work rather than projecting financials, use the project tracker or sales pipeline tracker.