You will be able to extend your model five years ahead from your drivers and make it balance without plugs.
A finished forecast balances in every year without a plug, a forcing figure typed in to make the two sides agree, because cash is never forecast directly. Every other balance sheet line comes from a formula, and cash comes out of the cash flow statement, which is what makes the two sides agree.
This project is the one the rest of the course builds on. Module 8 values the company from the free cash flow this model produces, and module 9 compares the company with peers. Allow about ninety minutes. You need the history tabs from lesson 7.7, Enter five years of history into your model, and the drivers from lesson 7.6, Forecast from drivers, with mean reversion and reinvestment.
Add five forecast columns to each statement tab, beside the five history columns. Every forecast cell is a formula reading from the Inputs tab or from another statement. The finished model must balance in every year, produce free cash flow for each year, and respond correctly when any driver changes.
Marcus is an analyst building this model for Larkspur, an invented company with figures made-up as always, in S$ millions unless noted. His forecast runs FY6 to FY10. On his Inputs tab he sets revenue growth at 8% (FY6), 6% (FY7), 5% (FY8), 4% (FY9) and 3% (FY10). Operating margin is 13.5%, depreciation is 6% of revenue, capital spending is 8% of revenue, working capital is 26% of revenue, and tax is 17% of profit before tax. Interest is 5% of opening debt and leases. The dividend is 6 cents a share on 300 million shares. Bank debt repaid is 10 in FY6, then none.
Revenue is last year's revenue x (1 + growth). Operating profit is revenue x operating margin. Interest is the rate x the opening balance of debt and leases; in the forecast, bank debt and leases are combined for simplicity. From there, work down to profit before tax, tax, net profit and earnings per share.
Using the opening balance for interest avoids a circular reference. With the closing balance, interest would depend on cash, cash depends on profit, and profit depends on interest.
For FY6, Marcus gets revenue of 432 and operating profit of about 58.3. Interest is 4.0, on opening debt and leases of 80. Profit before tax is about 54.3, tax is about 9.2, net profit is about 45.1, and earnings per share is about 15 cents.
Property and equipment closes at opening value + capital spending - depreciation. Working capital closes at a percentage of revenue. Many analysts prefer to forecast receivables, inventory and payables separately using days; the total should land in the same place as the percentage-of-revenue method. Goodwill stays flat unless you forecast an impairment. Debt closes at opening debt - repayments. Equity closes at opening equity + net profit - dividends.
In FY6, Marcus's property and equipment is 170 + about 34.6 - about 25.9 = about 178.6. Working capital is about 112.3, up about 7.3 from 105. Goodwill stays at 30. Debt is 80 - 10 = 70. Equity is 285 + about 45.1 - 18 = about 312.1.
Operating cash flow is net profit + depreciation - the increase in working capital. Investing cash flow is minus capital spending. Financing cash flow is debt repaid + dividends, both negative. Closing cash is opening cash + the change in cash, and that figure feeds the balance sheet.
For FY6, Marcus has operating cash flow of about 45.1 + 25.9 - 7.3 = about 63.7, investing cash flow of about minus 34.6, and financing cash flow of minus 10 minus 18 = minus 28. The change in cash is about 1.1, so closing cash is about 61.1.
Assets net of payables (cash + working capital + property and equipment + goodwill) must equal debt + equity, and the check row should read zero. For Marcus, cash of about 61.1 + working capital of about 112.3 + property and equipment of about 178.6 + goodwill of 30 comes to about 382.1. Debt of 70 + equity of about 312.1 also comes to about 382.1, so his check row reads zero.
If yours does not, hunt the gap as lesson 7.1, How the three statements tie together, showed. Every balance sheet change must appear in the cash flow statement with the right sign, and every cash flow line must change a balance sheet line.
By FY10, Marcus's model shows revenue of about 515, net profit of about 54.8 and cash of about 136, because free cash flow kept exceeding dividends. A growing cash pile is itself a finding. What management does with surplus cash is a capital allocation question, covered in module 9.
A model that balances can still have dead links, formulas that fail to pass a driver change through the statements. To find them, change one driver and watch all three statements.
First compute free cash flow, which in this model is operating cash flow minus capital spending. Marcus's base case gives about 29.1 in FY6, rising to about 40.6 in FY10.
Higher growth should lower free cash flow at first, because extra growth needs more working capital and equipment before it produces more profit. When Marcus raises revenue growth by two points in every year, FY6 revenue rises to 440 and FY6 free cash flow falls to about 27.8. Free cash flow overtakes the base case only from FY9, reaching about 42.2 in FY10. This is the correct response and shows the test working.
If a growth increase raises free cash flow immediately, a reinvestment link is missing. If the balance check breaks after a driver change, a cash flow line is not reaching the balance sheet.
Your finished model should have:
five history and five forecast columns on each statement an Inputs tab listing every driver and its source balance and cash checks at zero in all ten years free cash flow for each forecast year a note of what happened when growth changed by two points
Before you move on, compare your forecast margins and returns with the company's history. If they look far better than anything the company has achieved, revisit lesson 7.6, because module 8 will turn any optimism in the forecast into a higher value.
Now finish your five-year forecast, get it to balance in every year, then change revenue growth by two points and write down what happens to free cash flow.
Finish a five-year forecast that balances in every year. Then move revenue growth by two points and write down what happens to free cash flow.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).