You will be able to enter five years of reported figures into linked tabs and check that the history balances.
Before you forecast anything, enter five years of the company's reported figures into linked spreadsheet tabs and prove they hold together. A model is only as good as the historical figures it starts from. An error in last year's balance sheet carries into every forecast year, and once that happens you can no longer tell which numbers to trust.
Professional analysts spend more time on this stage than on any other part of a model. Allow about forty-five minutes for the work itself, plus time to find five years of annual reports. Use the company from your annual report 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. On each statement tab, put the years across the columns with the oldest on the left. You need five history columns now, and Lesson 7.8 adds five forecast columns later. Add a top row that labels each column as reported or forecast, and shade the history columns so they are never mistaken for forecast columns.
The Inputs tab stays mostly empty for now. It will later hold the forecast drivers from Lesson 7.6, "Forecast from drivers, with mean reversion and reinvestment".
Keep the line items 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, a made-up company used in the examples below, these lines 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.
You can group smaller lines into an "other" line on each statement, but only where the annual report gives a total to check against. Write a note of what each "other" line contains.
Enter the figures year by year. Where possible, take each year's figures from the most recent report that shows that year. 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.
Record a page reference for each statement beside each year's column or in a notes column, in a form such as "AR FY5, p.94" (an illustrative reference). When a figure was restated, note which report it came from. Every line in your model should then be traceable to an annual report page.
Marcus is building a model for Larkspur. All Larkspur figures are made up and in S$ millions. A slice of Marcus's balance sheet entry for the last two years looks like this. In FY4: cash 50, receivables 75, inventory 72, property and equipment 162, goodwill 30, payables 42, debt 70, lease liabilities 22, equity 255. In FY5: cash 60, receivables 80, inventory 70, property and equipment 170, goodwill 30, payables 45, debt 60, lease liabilities 20, equity 285.
In this model, property and equipment combines owned equipment and right-of-use assets. For Larkspur in FY4 that is owned equipment 140 plus right-of-use assets 22, giving 162. In FY5 it is 150 plus 20, giving 170.
Under the balance sheet, add a balance check row equal to total assets minus total liabilities minus equity. It must be zero in every year. Format the row so any non-zero value turns red.
Marcus's FY4 assets are 50 + 75 + 72 + 162 + 30 = 389. Liabilities and equity are 42 + 70 + 22 + 255 = 389, so the check is 0. In FY5 both sides come to 410 (assets 60 + 80 + 70 + 170 + 30; liabilities and equity 45 + 60 + 20 + 285), and the check is 0 again.
A non-zero balance check in a history year means you have a typing error. It is not an error in the company's accounts, because a published balance sheet always balances. To find it, compare your totals with the report's totals line by line.
Under the cash flow statement, add a cash check row equal to calculated closing cash (opening cash plus change in cash) minus the cash on the balance sheet. It should be zero. This row catches different errors from the balance check: a cash flow line entered with the wrong sign, or a definition of cash that differs between the statements.
Marcus's FY5 cash flow shows operating cash flow of 66, investing of -24 and financing of -32, so the change in cash is 10. Opening cash of 50 plus 10 gives 60, which matches balance sheet cash of 60. The cash check is 0.
Definitions of cash can differ between the statements. Some companies include short-term deposits in balance sheet cash but exclude them from the cash flow statement, or subtract bank overdrafts. The annual report's reconciliation note explains any such difference, and your check row should allow for the difference rather than ignore it.
Below the statements, add the ratio rows covered in lessons 7.2 to 7.5: margins, working capital days, net debt to EBITDA, free cash flow with and without leases, cash conversion and the accruals ratio. Once the history is in, they calculate themselves.
If a check won't reach zero after twenty minutes, record the size of the gap and the year, then move on to the next year. Coming back later with fresh eyes usually finds the error in minutes.
Your history is finished when you have four tabs, five shaded history columns, every line traceable to a page, and the balance check and cash check at zero in all five years.
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 the statement tabs of your model 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).