You will extend your model five years forward from your drivers and get it to balance without plugs.
This is the project the rest of the course builds on. You'll extend your history five years forward from your drivers, link every line so the three statements move together, and get the balance sheet to balance in every forecast year without a plug. Module 8 values the company from the free cash flow this model produces, and module 9 compares it with peers.
Allow about ninety minutes. You'll need the history tabs from lesson 7.7, Enter five years of history into your model, and the drivers you wrote in lesson 7.6, Forecast from drivers, with mean reversion and reinvestment.
Add five forecast columns to each statement tab. 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 you change any driver.
Marcus's Inputs for Larkspur, made-up as always: revenue growth of 8%, 6%, 5%, 4% and 3% from FY6 to FY10; operating margin 13.5%; depreciation 6% of revenue; capital spending 8% of revenue; working capital 26% of revenue; tax 17% of profit before tax; interest 5% of opening debt and leases; a dividend of 6 cents a share on 300 million shares; and 10 of bank debt repaid in FY6, then none. Figures below are in S$ millions.
Revenue is last year's revenue times one plus growth. Operating profit is revenue times the margin. Interest is the rate times the opening balance of debt and leases, which avoids a circular reference: if you used the closing balance, interest would depend on cash, which depends on profit, which depends on interest.
Larkspur's FY6: revenue 432, operating profit about 58.3, interest 4.0 on opening debt and leases of 80, profit before tax about 54.3, tax about 9.2 and net profit about 45.1. Earnings per share come to about 15 cents.
Property and equipment closes at opening value plus capital spending minus depreciation. For FY6: 170 plus about 34.6 minus about 25.9, which is about 178.6.
Working capital closes at 26% of revenue: about 112.3, up about 7.3 from 105. If your model forecasts receivables, inventory and payables separately using days, as many analysts prefer, the total should land in the same place.
Goodwill stays at 30 unless you forecast an impairment. Debt closes at opening debt minus repayments: 80 minus 10 is 70, combining bank debt and leases in the forecast for simplicity. Equity closes at opening equity plus net profit minus dividends: 285 plus about 45.1 minus 18, about 312.1.
Cash isn't forecast directly. It comes from the cash flow statement, and that's what makes the model balance.
Operating cash flow is net profit plus depreciation minus the increase in working capital: about 45.1 plus 25.9 minus 7.3, about 63.7. Investing cash flow is minus capital spending: about minus 34.6. Financing cash flow is debt repaid plus dividends: minus 10 minus 18, which is minus 28. Change in cash: about 1.1, so cash closes at about 61.1.
Now check the balance sheet. Assets net of payables: cash of about 61.1, working capital of about 112.3, property and equipment of about 178.6 and goodwill of 30, which totals about 382.1. Debt of 70 plus equity of about 312.1 is also about 382.1. The check row reads zero.
If it doesn't, 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. Lesson 7.1, How the three statements tie together, showed how to hunt for the gap.
By FY10, Marcus's model showed revenue of about 515, net profit of about 54.8 and cash of about 136, because Larkspur's free cash flow kept exceeding its dividends. That cash pile is itself a finding. What management does with it is a capital allocation question for module 9.
A model that balances can still have dead links. Change one driver and watch all three statements.
Free cash flow in this model, operating cash flow minus capital spending, was about 29.1 in FY6 rising to about 40.6 in FY10. Marcus raised revenue growth by two points in every year. FY6 revenue rose to 440, and free cash flow fell, to about 27.8, because the extra growth needed more working capital and equipment before it produced more profit. Free cash flow overtook the base case only from FY9, reaching about 42.2 in FY10.
That's the right answer, and it's the test working. If a growth increase had raised free cash flow at once, a reinvestment link would be missing. If the balance check had broken, a cash flow line wouldn't have reached the balance sheet.
Five history and five forecast columns on each statement, an Inputs tab with every driver and its source, balance and cash checks at zero in all ten years, free cash flow for each forecast year, and a note of what happened when you changed growth by two points. If your forecast margins or returns look far better than the company has ever achieved, revisit lesson 7.6 before you move on, because module 8 will turn any optimism here 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.
Complete a five-year forecast that balances every year, then change revenue growth by two points and record what happens to free cash flow.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).