You will enter five years of reported figures into linked tabs and check that the history balances.
A model is only as good as the history it starts from. If last year's balance sheet is entered wrong, every forecast year inherits the error, and you won't know which numbers to trust. This exercise enters five years of reported figures into linked tabs and proves they hold together before you forecast anything. Professional analysts spend more time here than on any other part of a model.
Allow about forty-five minutes, plus time to find five years of annual reports. Use the company from your reading log in lesson 6.8, Build an annual report reading log for one company.
Create four tabs: Inputs, Income statement, Balance sheet and Cash flow. Years run across the columns, oldest on the left, with five history columns now and five forecast columns to add in lesson 7.8. Put a row at the top of each statement tab that labels each column as reported or forecast, and shade the history columns so you never mistake one for the other.
Leave Inputs mostly empty for now. It will hold your forecast drivers from lesson 7.6, Forecast from drivers, with mean reversion and reinvestment.
Keep the lines simple. You don't need every line in the annual report, only enough to tie the statements together and calculate the ratios from lessons 7.2 to 7.5. For a company like Larkspur, these are enough.
Income statement: revenue, cost of sales, operating expenses, operating profit, one-off items, interest, tax, net profit. Balance sheet: cash, receivables, inventory, property and equipment including right-of-use assets, goodwill, payables, debt, lease liabilities, equity. Cash flow: net profit, depreciation, working capital changes, other non-cash items, operating cash flow, capital spending, asset sales, debt and lease repayments, dividends, change in cash
Group smaller lines into an "other" line on each statement, but only where the annual report itself gives you the total to check against, and write a note of what each "other" line contains.
Type the reported figures in, year by year, from the annual reports. Use the most recent report for each year where you can, because companies restate prior years when accounting rules change, and the restated figure is the comparable one. A report usually shows two years, so five years of history needs three or four reports.
Beside each year's column, or in a notes column, put the page reference for each statement: "AR FY5, p.94". When a figure was restated, note which report you took it from.
Here is a slice of Marcus's Larkspur entry, made-up figures in S$ millions, for the last two years. FY4 balance sheet: cash 50, receivables 75, inventory 72, property and equipment 162, goodwill 30, payables 42, debt 70, lease liabilities 22, equity 255. FY5: cash 60, receivables 80, inventory 70, property and equipment 170, goodwill 30, payables 45, debt 60, lease liabilities 20, equity 285. Property and equipment here combines owned equipment and right-of-use assets: 140 and 22 in FY4, 150 and 20 in FY5.
Under the balance sheet, add a check row: total assets minus total liabilities minus equity. It must show zero in every year.
For Larkspur's FY4: assets of 50, 75, 72, 162 and 30 total 389. Liabilities and equity of 42, 70, 22 and 255 also total 389. The check is zero. FY5: 410 on both sides, zero again.
Format the check row so any value other than zero turns red. When it isn't zero for a history year, the error is in your typing, not in the company's accounts, because a published balance sheet always balances. Compare your totals with the report's totals line by line until you find it.
Add a second check row under the cash flow statement: the closing cash it calculates, which is opening cash plus the change in cash, minus the cash on the balance sheet, and like the first check it should read zero.
For Larkspur's FY5, operating cash flow of 66, investing of minus 24 and financing of minus 32 add up to a change of 10. Opening cash of 50 plus 10 is 60, the figure on the balance sheet, so the check shows zero.
This check catches a different kind of error: a cash flow line entered with the wrong sign, or a definition of cash that differs between statements. Some companies include short-term deposits in cash on the balance sheet and exclude them in the cash flow statement, or subtract bank overdrafts. The reconciliation note in the annual report explains any difference, and your check row should allow for it rather than ignore it.
Four tabs, five shaded history columns, every statement line traceable to a page, a balance check showing zero in all five years and a cash check showing zero in all five years. Below the statements, add the ratio rows you've already met: margins, working capital days, net debt to EBITDA, free cash flow with and without leases, cash conversion and the accruals ratio. They calculate themselves once the history is in.
If a check won't reach zero after twenty minutes of searching, write down the size of the gap and the year, and move on to the next year. Coming back with fresh eyes usually finds it in minutes.
Now enter five years of your company's history and get both check rows to zero in every year.
Enter five years of history into your model's statement tabs and get the balance check to zero in every year.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).