Financial modeling explained: how to build a 3-statement model

Mohammed Aamir ShaikhFounder, Aamir Capital

Updated 5 October 20269 min read

What a financial model is for

People use financial models to value businesses, plan budgets, raise money, test deals and decide on investments. The point of a model is to let you change an assumption and see what happens to profit, cash and debt.

• See how a change in sales growth or margin flows through to profit and cash.
• Compare a base case with an upside and a downside.
• Check whether the business can repay its debt, and when.
• Show lenders and investors how you reached your numbers.

How a model is organised

• Inputs. Every assumption in one place, clearly labelled.
• Schedules. Supporting calculations for revenue, costs, working capital, fixed assets, debt and tax.
• Financial statements. The income statement, balance sheet and cash flow statement.
• Outputs. Valuation, ratios and charts.
• Checks. Tests that show zero or TRUE when the model is working, such as a balance sheet that balances.

Keeping these parts separate lets anyone follow the flow from inputs to outputs and find mistakes quickly.

How the three statements connect

The three statements are linked. Net income from the income statement feeds the cash flow statement and, through retained earnings, the balance sheet. The cash flow statement produces the closing cash balance that sits on the balance sheet. Changes in working capital, capital expenditure and debt feed back into the cash flow statement.

If the balance sheet does not balance, something is not linked correctly. The balance check is the first test of any model.

A sensible build order

1. Collect and clean three to five years of historical financials.
2. Set the assumptions: growth, margins, working capital days, capital expenditure, tax and financing.
3. Forecast revenue.
4. Forecast costs and calculate EBITDA.
5. Build the working capital schedule: receivables, inventory and payables.
6. Build the fixed asset and depreciation schedule.
7. Build the debt schedule and calculate interest.
8. Calculate tax.
9. Link the three statements together.
10. Add checks, scenarios and outputs.

Interest and circularity

Interest depends on the debt balance. The debt balance can depend on cash flow, and cash flow depends on interest. This loop is called a circular reference. The simplest fix is to calculate interest on opening balances. Models that use average balances need a switch or a macro to control the loop.

Our free 3-statement model template calculates interest on opening balances for exactly this reason.

Scenarios and sensitivities

A base case, an upside case and a downside case help decision makers see the range of outcomes. Sensitivity tables show how one or two assumptions, such as growth and margin, change the result. Decisions rarely hinge on the base case alone.

Best practice

• Keep inputs separate from calculations, and never type numbers into formulas.
• Use one consistent formula across each row.
• Use colour coding: blue for inputs, black for calculations, green for links to other sheets.
• Add a balance check and other checks that show zero when the model is right.
• Write down the assumptions and where they came from.
• Keep the model as simple as the decision needs.

The FAST standard (Flexible, Appropriate, Structured, Transparent) is a widely used set of modelling rules that expands on these ideas.

Common errors, and how to review a model

• Numbers typed inside formulas.
• Time periods that do not match, such as annual and monthly figures mixed.
• Sign errors in the cash flow statement.
• A balance sheet forced to balance by plugging cash.
• Units that differ between sheets, such as thousands and millions.
• Growth rates applied to the wrong base year.

To review a model, check that it balances, trace a few numbers by hand, flex key inputs to see whether results move in the expected direction, and read formulas across each row to spot breaks in the pattern.

Questions

Related explainers

• What is free cash flow (FCF)?
• What is EBITDA?
• What is a DCF (discounted cash flow)?
• What is DSCR (debt service coverage ratio)?

Try it and keep reading

• Free 3-statement model template
• Financial modeling service
• Project finance modeling
• Damodaran: operating and net margins by sector
• Damodaran: working capital by sector