How to Make a Financial Model in Excel: The 10-Step Build (2026 Guide)

The 10-Step Build at a Glance

Making a financial model means turning a blank Excel sheet into a working forecast machine — inputs on one side, statements in the middle, decision numbers at the end. This guide gives you the exact build order professionals use, with the formulas, the checks and the rules that keep the model honest.

Two documents anchor everything here, and both are free to download. The FAST Standard (Flexible, Appropriate, Structured & Transparent — a widely used modelling standard, current edition 02c, July 2019) and ICAEW's Financial Modelling Code (from the Institute of Chartered Accountants in England and Wales). Where a rule below is theirs, we quote it exactly.

Plain takeaway: steps 1–3 are setup, 4–7 are the build, 8–10 turn a spreadsheet into something people can trust and use.

Plan Before You Touch Excel

Professionals sketch first. ICAEW's code is direct about why: "All models should have a clear purpose and defined output. It should be evident to all users what can and cannot be derived from its use." And on sequence: "Design the model carefully before constructing it, taking into consideration the various possible outcomes."

Your plan needs three sentences on paper, nothing more:

• The question. "Can this shop repay a ₹30 lakh loan?" or "What is this company worth if growth slows?" One model, one question.
• The output. The exact numbers that answer it — cash balance by year, or value per share, or DSCR.
• The drivers. The five to ten assumptions the answer genuinely depends on: growth, margin, working-capital days, capex, interest rate, tax rate.

Through this guide we will carry one tiny illustrative company, so every formula has real numbers. It is a trading business with revenue of ₹100 lakh, growing 10% a year, at a 20% EBITDA margin. (EBITDA — earnings before interest, tax, depreciation and amortisation — is the operating profit before financing and accounting charges.) Depreciation is ₹5 lakh, interest ₹3 lakh, tax 25%.

Steps 1–3: Historicals, Workbook Setup and the Assumptions Sheet

Step 1 — Pull three years of real financials. Models project forward from real history, so collect the last three annual statements of your company. For Indian listed companies, all of these are free:

• The company's own investor-relations page — the primary source. Every listed company hosts its annual reports (for example, Reliance Industries' financial-reporting page at ril.com).
• Screener.in — a free-to-start analysis tool (by Mittal Analytics, data from C-MOTS) that shows ~10 years of financials per company and has an "Export to Excel" button behind a free login.
• NSE and BSE portals — both exchanges host corporate filings and annual reports for every listed company.

Step 2 — Set up the workbook before any formula. The FAST Standard's very first structural rule: "Separate worksheets by type: Foundation, Workings, Presentation and Control." Foundation holds inputs and timing; workings hold calculations — "the model's 'engine'" in FAST's words; presentation holds the statements and summary a reader sees; control holds checks and version notes. For a first model, four tabs — Inputs, Workings, Statements, Checks — honour the same idea.

Two more FAST setup rules save hours later. "Maintain consistent column structure across all sheets" — if FY27 is column E on one sheet, it is column E everywhere. And "Maintain a consistent time ruler throughout the model" — every sheet's timeline starts in the same column and runs the same length. Sheets should also flow like reading: ICAEW says to set up the model "to read like a book: with logic flowing from top to bottom within each worksheet, and from left to right both within each worksheet and across sheets within a workbook."

Step 3 — Build the assumptions sheet. Every driver gets typed once, here, and nowhere else. ICAEW calls this the single-source rule: "Create a unique location for each input value, and structure all calculations that need the value to refer to that single input location." Group the inputs the way FAST recommends — constants separate from time series, then by commercial area (revenue, costs, capex, financing) — and add a comments column for notes.

Mark the input cells visually. The convention on most banking desks is blue font for typed inputs, black for formulas, green for values pulled from other sheets. That triad is industry habit rather than a written standard — what the standards do demand is clarity: ICAEW says to distinguish inputs "using a defined cell fill colour and/or a cell border, not just a defined font colour", and FAST requires a key: "Provide keys to colour coding, abbreviation, named ranges, and functions." Pick a scheme, apply it everywhere, and put the legend on the inputs sheet.

Steps 4–6: Build the Three Statements

Step 4 — Project the income statement. Start from the revenue driver and walk down. On our illustrative company:

Plain takeaway: seven rows, five formulas, two links to schedules — a full P&L projection is smaller than most people fear. All figures illustrative.

Write each formula the FAST way. "Series calculations must be constructed from consistent formulas along the axis of presentation" — FAST calls formula consistency "one of only a few universally accepted principles of good modelling." In practice: write year 1 once, copy it across all years, and never patch one year by hand.

Step 5 — Build the supporting schedules. Three small blocks on the workings sheet feed everything:

• Fixed assets: opening assets + capex − depreciation = closing assets. Produces the depreciation line.
• Working capital: receivables, inventory and payables driven by days-of-revenue assumptions. Produces the cash the business ties up as it grows.
• Debt: opening debt + new borrowing − repayment = closing debt. Produces the interest line.

Build each block the FAST way: "All ingredients must be presented as links immediately above the calculation" — pull the numbers a formula needs onto labelled rows just above it, then compute. Your formulas stay short enough to pass FAST's famous tests: "Do not write a formula longer than your thumb" and "No formula should take more than 24 seconds to explain."

Step 6 — Assemble the balance sheet and cash flow. The balance sheet collects closing balances from the schedules — assets on one side, liabilities and equity on the other. The cash flow statement reconciles profit to cash in three sections: operations (PAT + depreciation − working-capital increase), investing (capex out), financing (borrowings in, repayments and dividends out). Line-by-line detail for this stage lives in our three-statement model guide — the build order and rules are what matter here.

One rule protects every formula you write in these steps. FAST: "Do not write formulas with embedded constants" — never type a growth rate or tax rate inside a formula. FAST's own exception list is tiny: "Embedding a 24 (for 24 hours per day), 12 (12 months a year), 1000 (dollars in $ 000s) is permissible, even sensible." Everything else comes from the inputs sheet.

Step 7: Link the Statements — and Make the Balance Check Read Zero

Now wire the three statements together. Three links do it:

• Profit after tax flows from the P&L into retained earnings inside equity on the balance sheet.
• The cash flow statement's closing cash becomes the cash line on the balance sheet.
• Depreciation and capex flow consistently through the P&L, the fixed-asset schedule and the cash flow.

Then build the one formula every reviewer looks for first — the balance check: total assets minus total liabilities minus equity. It must read 0 in every single year. A concrete illustration: say your model shows assets of 500, liabilities of 300 and equity of 195 (all ₹ lakh, illustrative). Then 300 + 195 = 495 against assets of 500, and the check reads 5 — a broken link, usually profit not reaching retained earnings. Rewire it so equity reads 200, and the check returns to 500 − 500 = 0.

ICAEW's code formalises this: "Include an equality check for each set of values that are calculated separately but ought to be equal, such as a balance worksheet check" — with a practical softener: "Include a tolerance in checks to allow for very small acceptable variations, such as Excel rounding errors."

A word on circular references, because you will meet one here. If interest is computed on average debt, and debt depends on cash, and cash depends on interest — the loop bites its own tail. Excel has a switch for this (File > Options > Formulas > Enable iterative calculation; Microsoft notes it "stops after 100 iterations, or when values change by less than 0.001"). But Microsoft's own guidance is to "keep iterative calculation turned off" in most worksheets, and the FAST Standard is stricter: "Never release a model with purposeful use of circularity."

The beginner-safe answer: compute interest on opening debt instead of average debt. The tiny loss of precision is exactly the trade FAST endorses — circular models, it warns, suffer from "being blinded by precision over the principle of accuracy."

Step 8: Error-Check Like a Professional

Here is why this step is non-negotiable. Professor Raymond Panko's compilation of intensive spreadsheet audits — presented at EuSpRIG 2015 — found errors in 94% of the 85 operational spreadsheets inspected. His summary of decades of research: "Research on spreadsheet errors is substantial, compelling, and unanimous." Errors are not a beginner problem. They are a spreadsheet problem.

The classics are instructive. In 2003, Canadian power company TransAlta took a $24 million charge after a bidding mistake its CEO described with painful clarity: "It was literally a cut-and-paste error in an Excel spreadsheet that we did not detect when we did our final sorting and ranking bids prior to submission." And economists Reinhart and Rogoff's influential debt-and-growth paper contained a range error — a formula averaging L30:L44 where the data ran to L49 — that silently dropped five countries; fixing it (one of three issues critics found) moved a headline growth figure from −0.1% to +0.2%.

Your defence has four layers, all quick:

• A checks block. The balance check from step 7, plus alerts like "revenue growth outside −50% to +100%" or "cash never negative". ICAEW's definition of a check: "A visible indicator that an input or output falls outside of expected parameters or does not produce the expected result."
• A master check on every sheet. One cell that goes red if any check fails. ICAEW: "Display the result of the master check in the frozen pane on all worksheets so that it is clearly visible in all areas of the model."
• Trace your formulas. Select a formula cell and press Ctrl+[ — Excel jumps to the cells it pulls from (its precedents). Work back from every output once. F4 while editing cycles a reference through its anchored forms ($A$1, A$1, $A1, A1) so copied formulas point where you intend.
• Review against expectations. ICAEW's testing principle: "Create expectations as to how a model's results should vary with changes in inputs, apply these input changes to the model and compare the outputs with the expectations." Double the growth input — did profit move the way you predicted? If not, hunt.

Steps 9–10: Scenarios, Sensitivity and the Output Page

Step 9 — Scenarios. A model earns its keep when assumptions change. Build a scenario switch: one input cell (1 = base, 2 = best, 3 = worst) and a small table holding each scenario's growth, margin and capex. The drivers read from the chosen column, and the entire model flips cases from a single cell. Then add simple sensitivity: change one driver up and down and record the output — "at 5% growth, year-3 cash is X; at 15%, Y". This is the analysis committees actually ask for.

Step 10 — The output page. Build one presentation sheet that answers the original question from step 0: headline outputs, a small chart, the scenario results, and the assumptions that drive them. FAST's transparency pillar sets the bar — "simple, clear formulas that can be understood by other modellers and non-modellers alike" — and its visibility rule keeps you honest: "Do not hide anything" (rows, columns and sheets stay visible; hidden logic is where errors sleep).

Version-control the file the boring way: save as model_v1, v2, v3 with a change note on the control tab. ICAEW adds one last documentation rule worth quoting: "Don't store documentation in a separate file or email that may become separated from the model."

Which Mistakes Sink Beginner Models?

Every reviewer sees the same failures. Check your model against this list before anyone else does:

• Hardcoding. ICAEW's definition: "Fixed values embedded into a formula, such as a tax rate entered as 20% within a formula." Its verdict: hardcoding "should never be used for values that could foreseeably change during the life of the model."
• Inconsistent row formulas. One hand-patched year breaks the copy-across guarantee — the exact failure ICAEW warns reviewers about, since consistency "reduces the chance that an error will occur through copy-pasting over an inconsistent formula."
• Recalculating instead of linking. FAST: "A given calculation should appear only once in a model." Need the number again? Link to it.
• No checks block. A model without a balance check is unreviewable — nobody can tell whether it merely looks finished.
• Deliberate circularity. Ship-blocking per FAST. Use opening-balance logic instead.
• Hidden rows and sheets. Against FAST's "Do not hide anything" — and ICAEW notes hidden rows "are easily missed, can be confusing and are easy to change or delete inadvertently."
• False precision. Ten decimal places on a five-year forecast impresses nobody — FAST's "Appropriate" pillar exists to keep models tied to the decision, not to spurious detail.

How Do You Practise Building Models?

Reading builds familiarity; only building builds skill. A practice ladder that works:

• Build 1 — this guide's mini-model. The ₹100-lakh company above, through all 10 steps. A weekend project.
• Build 2 — a real company. Pull three years of a listed company's financials (step 1 sources) and rebuild the model on real data — messy history included.
• Build 3 — add valuation. Bolt a DCF and comps onto build 2; add an LBO if private equity interests you.
• Build 4 — defend it. Practise walking someone through the model aloud — the skill our FM interview questions guide tests, because interviewers will.

Would you rather climb that ladder with faculty reviewing your actual workbooks? QuintEdge's Financial Modeling course runs the same progression on real Indian company data. And our financial modeling overview maps where the skill leads career-wise.