Three-statement financial model template (Excel)

What it is

A three-statement model projects a business's income statement, balance sheet and cash flow statement together, so that each one feeds the others. Net income flows into retained earnings, depreciation and capital spending change property and equipment, and the cash flow statement produces the cash that sits on the balance sheet.

This workbook is a skeleton: one product line and the standard links, with five forecast years. It is meant to be extended, not used unchanged for a complicated business.

Who it is for

Founders and finance staff building a first forecast, analysts and students learning how the statements connect, and anyone who needs a clean base to extend with their own lines.

When to use it, and when not to

• Use it to build a forecast for a plan or funding discussion, to test how a change in growth, margin, working capital or financing affects cash, or to teach how the statements link.
• Do not use it unchanged for a business with leases, deferred tax, several currencies, loss carry-forwards or complex financing. Add those lines first.
• Do not use it to value a company on its own. Use the DCF template for that.

What is inside

• Inputs. The last actual year, the opening balance sheet, last year's revenue and 14 forecast assumptions for each of five years: growth, gross margin, operating expenses, depreciation, capital spending, receivable, inventory and payable days, tax, interest rates, debt raised or repaid, shares issued and dividend payout.
• IS. Revenue to net income, with margins.
• BS. Cash, receivables, inventory and property; payables and debt; paid-in capital and retained earnings. It shows the difference between assets and liabilities plus equity, and a balance flag.
• CF. Cash from operations, investing and financing, opening cash and closing cash.
• Checks. Balance, no negative cash, cash flow against balance sheet movement, inputs in range and inputs filled in, with an overall result.

Named ranges, such as Revenue_Growth, Tax_Rate and Open_Cash, mark the inputs.

How to fill it in

1. Enter the last actual year and your units. Use one unit, such as thousands, throughout.
2. Enter the opening balance sheet from your last accounts. If assets do not equal liabilities plus equity, the checks will say so. Fix it before you go on.
3. Enter last year's revenue.
4. Fill the forecast assumptions for each year. Anchor each to history or a stated plan, and write down why.
5. Read the three statements. Look for anything odd: margins that jump, cash that swings, working capital that does not match the business.
6. Open Checks. Every cell should read OK. If cash goes negative, add funding (debt raised or shares issued) and look again.
7. Add the lines your business needs, and keep the checks working as you do.

Worked example (an illustration)

Illustration only. The company and every number below are made up, in thousands. The workbook ships with these numbers so you can see it work. Replace them all.

With the made-up inputs in the file (revenue of 10,000 in the last actual year, growth of 10% then 8% then 6%, and a 40% gross margin rising to 42%):

Try a change: set payable days to 20 in every year. Payables fall, so cash from operations falls and cash ends lower, while the balance sheet still balances. That is the point of linking the statements.

Common mistakes

• Typing numbers over formulas on the statements. Change inputs, not results.
• Forecasting growth with no link to capacity, customers or price.
• Forcing the balance sheet to balance with a plug. If it does not balance, a link is broken.
• Letting cash go negative without funding. A model that shows negative cash needs a financing assumption.
• Ignoring working capital. Fast growth usually needs more of it.

Download

Free to download. The numbers in the file are made up, so replace them.

Use and limits

Free to use and adapt for your own work. If you publish or share it, a link back is appreciated.

This model is a starting point for learning and planning. It is not financial, accounting or investment advice, and a model built on made-up numbers says nothing about any real business.

Sources and further reading

Sources are linked so you can check them. Statements that vary by employer, or that accounts disagree about, are hedged in the text. Found a mistake? Email support@forwardeployed.work and we will correct the page and update its date.

1. Financial statement. Encyclopedia entry.
2. Balance sheet. Encyclopedia entry.
3. Cash flow statement. Encyclopedia entry.
4. Income statement. Encyclopedia entry.
5. Working capital. Encyclopedia entry.