This walkthrough builds a small three-statement financial model in Excel. It connects sales and cost assumptions to the income statement, cash flow statement and balance sheet, then tests what happens when sales fall.
Download the three-statement practice workbook (.xlsx). It contains the worked Model sheet and a Practice sheet with tasks and answer checks. The business and all inputs are fictional. Amounts use one consistent currency unit.
What this model includes
The workbook starts with an opening balance sheet and forecasts two annual periods. Blue numbers in pale-yellow cells are editable inputs. Black numbers are formulas. Columns D and E contain Year 1 and Year 2; column C contains the opening balances.
The case includes revenue, variable costs, fixed expenses, depreciation, interest, tax, working capital, equipment, debt and equity. It deliberately leaves out deferred tax, tax-loss carryforwards, a cash sweep, automatic funding and an asset-by-asset depreciation schedule. The purpose is to make the statement links easy to inspect.
Enter the assumptions and opening balances
Year 1 assumes 1,000 units sold at 50 each, variable cost of 30 per unit and fixed operating expenses of 15,000. Capital expenditure is 4,000 and depreciation is 3,000. Interest is 6% of opening debt; tax is 25% of positive pretax profit.
Opening assets are cash of 20,000, receivables of 5,000, inventory of 6,000 and net equipment of 20,000. They total 51,000. Opening payables of 4,500, debt of 10,000 and equity of 36,500 also total 51,000.
Keep historical or opening data separate from forecast assumptions. For a real company, reconcile source statements and investigate missing periods, inconsistent units and accounting changes before forecasting. There is no universal rule that every model must contain exactly three years of history.
Build the income statement
| Year 1 item | Calculation | Result |
|---|---|---|
| Revenue | 1,000 × 50 | 50,000 |
| Variable costs | 1,000 × 30 | 30,000 |
| Gross profit | 50,000 − 30,000 | 20,000 |
| Operating profit | 20,000 − 15,000 − 3,000 | 2,000 |
| Interest expense | 10,000 × 6% | 600 |
| Pretax profit | 2,000 − 600 | 1,400 |
| Tax | 1,400 × 25% | 350 |
| Net income | 1,400 − 350 | 1,050 |
In the workbook, revenue in D27 is =D10*D11. Net income in D36 is =D34-D35. Tax uses =MAX(D34,0)*D20, so a loss produces no current tax expense in this simplified case. It does not model the full tax treatment of losses.
Calculate the supporting balances
Closing receivables equal 10% of revenue, inventory equals 20% of variable costs and payables equal 15% of variable costs. These are assumed year-end balance ratios, not days-based schedules.
For Year 1, the resulting balances are 5,000, 6,000 and 4,500. They match the opening balances, so there is no working-capital cash adjustment in that year.
- Net equipment: opening 20,000 + capital expenditure 4,000 − depreciation 3,000 = 21,000.
- Debt: opening 10,000 + new borrowing 0 − principal repayment 1,000 = 9,000.
- Equity: opening 36,500 + net income 1,050 − dividends 0 = 37,550.
Interest uses opening debt to avoid a circular calculation in this case. A real facility may require monthly timing, average balances, fees, amortization or another agreed method.
Connect profit to cash
Start the indirect cash flow statement with net income. Add back depreciation because it reduced profit without being a current cash payment. Then adjust for changes in working capital.
Increases in receivables and inventory use cash in this model; an increase in payables provides cash relative to paying those costs immediately. The signs must follow the transaction logic.
Year 1 operating cash flow is 1,050 + 3,000 = 4,050. Investing cash flow is −4,000. Financing cash flow is −1,000 for the principal repayment. Net cash change is −950, so closing cash is 20,000 − 950 = 19,050.
In Year 2, receivables rise by 720, inventory rises by 820 and payables rise by 615. Operating cash flow is 2,295 + 3,500 − 720 − 820 + 615 = 4,870. After capital expenditure of 4,500 and debt repayment of 1,000, closing cash becomes 18,420.
Complete and check the balance sheet
Link cash from the cash flow calculation and the other balances from the supporting schedules. Do not insert a balancing amount to hide a difference.
| Check | Year 1 | Year 2 |
|---|---|---|
| Total assets | 51,050 | 52,960 |
| Total liabilities and equity | 51,050 | 52,960 |
| Balance sheet difference | 0.00 | 0.00 |
A zero difference is necessary but does not establish that every formula or assumption is correct. Inspect the schedules and test changes with a known answer.
Run a downside case
Change Year 1 units sold in D10 from 1,000 to 800. Leave the other inputs unchanged. Revenue becomes 40,000, net income becomes −2,600 and closing cash becomes 16,700. The balance sheet should still balance.
Explain the result before restoring D10 to 1,000. Lower sales reduce profit, but receivables and inventory also fall. Payables fall too, partly offsetting that cash benefit. A downside can therefore affect profit and cash by different amounts.
What Excel’s Forecast Sheet does not do here
Microsoft’s Forecast Sheet works with historical time-series data. Selecting two arbitrary columns does not create a connected three-statement model. The example here uses explicit operating assumptions and accounting links.
Valuation is also a separate task. A DCF requires a cash-flow definition, discount rate and other assumptions consistent with the claim being valued. It is not an automatic final step for every operating model.
Practise without the solution
Use the independent model-building assignment to rebuild this case from a blank sheet. If references or formulas are unfamiliar, start with Excel skills for finance. For the broader sequence, follow the study plan.
The workbook was checked using formula recalculation, a lower-sales input test and recalculation of the exported file in LibreOffice. No macros are required. It remains a simplified teaching model, not a forecast of an actual company.
