Forecasting Exercise

Income Statement Forecasting

1. The most important part of the forecast is the Sales growth. Apple’s growth has been all over the place in the last few years, but currently analysts are forecasting a 6.5% increase in Sales for 2018. Let’s assume a 6.5% increase in 2018 and slowly ratchet it down by 0.5% each year to fade into a long term growth rate of 4% by 2023. We’ll do this by growing Sales at 6.5% in 2018, 6% in 2019, 5.5% in 2020, 5% in 2021, and 4.5% in 2022.
2. Assume that future Cost of Goods Sold and SG&A remain the same percentage of current year Sales as in 2017. We do this by setting the 2018 COGS equal to the ratio of 2017 COGS to Sales multiplied by 2018 Sales. Then do the same with SG&A.
3. Assume that future Depreciation Expense remains the same percentage of prior year Net PP&E and that future Amortization Expense remains the same percentage of prior year Other Intangible Assets as in 2017. Note that we don’t yet have reference numbers for PP&E and Intangibles to calculate these expenses for 2019 and beyond. That is not a problem – enter the formulas into the cells anyway.
4. Assume that extraordinary gains and losses are zero in each future year.
5. Assume that future interest income remains the same percentage of prior year Other Investments as in 2017.
6. Assume that future interest expense remains the same percentage of prior year Short Term and Long Term Debt as in 2017.
7. Assume other income and expense remains constant in future years.
8. Normally we would assume an effective tax rate similar to prior years (around 25%), but the recent U.S. tax reform has brought the corporate tax rate to 21%. While Apple can probably avoid some of these taxes still, let’s assume a conservative 21% tax rate on pre-tax income. To do this, multiply pre-tax income by 21%. Make sure you put a negative sign on it!

Balance Sheet Forecasting

Assets

1. Assume that future Cash and Short Term Investments remains constant in future years.
2. Assume that future Accounts Receivable remains the same percentage of current year sales as in 2017.
3. Assume that future Inventories remain the same percentage of current year Cost of Goods Sold as in 2017.
4. Assume that future Prepaid Expenses remain zero in future years.
5. Assume that future Other Current Assets (for Apple these are primarily hedging instruments), as well as Other Investments, remain a constant percentage of current year Sales as in 2017.
6. Assume that future Plant, Property and Equipment - Net remains a constant percentage of current year sales as in 2017. Ignore PP&E – Gross and Accumulated Depreciation.
7. Assume Deferred Charges are zero in each future year.
8. Assume that Tangible Assets remains a constant in future years.
9. Assume that Intangible Assets remain constant in future years.
10. Finish the Asset section of the balance sheet by subtotaling total assets.

Liabilities

1. Assume that future Accounts Payable remains the same percentage of current year Cost of Goods Sold as in 2017.
2. Assume that both future Short Term Debt and Long Term Debt will remain the same percentage of current year Total Assets as in 2017.
3. Assume that Accrued Payroll and Income Taxes Payable remain zero in future years.
4. Assume that future Other Current Liabilities and Other Liabilities remain the same percentage of current year SG&A Expense as in 2017.
5. Assume that future Deferred Income remains the same percentage of current year Net Income as in 2017.
6. Assume that future Deferred Taxes remain the same percentage of current year Income Tax Expense as in 2017.

Equity

1. Assume that future Common Stock remains the same percentage of current year Total Assets as in 2017.
2. Assume that future Retained Earnings is equal to the previous year Retained Earnings plus the current year Net Income, minus dividends. Dividends are equal to current year Net Income multiplied by the dividend payout ratio (dividends/net income, provided on spreadsheet). I assume the future payout ratio will be 50%.
3. Assume that Accumulated Other Comprehensive Income/Loss remains the same numerical value as in 2017 (in other words, it remains constant).
4. Complete the subtotals for Total Shareholders’ Equity and Liabilities. Do you see a problem with the balance sheet? It doesn’t balance…
5. We need to set up a “plug account” to deal with the balance problem. Literally, we need to pick an account and enter a formula to set it equal to the difference in Assets versus Liabilities and Equity. Let’s use Cash. We need to set Cash equal to Total Liabilities and Shareholders’ Equity minus NON-CASH Assets.

Statement of Cash Flows Forecasting

1. Our income statement and balance sheet are complete, but we still need a statement of cash flows. The vast majority of our statement of cash flows will be assembled from the Income Statement and Balance Sheet numbers we have already forecasted.
2. Start with Cash Flows from Operating Activities. The very first line should pull the forecasted net income from the same year above.
3. Adjust for non-cash expenses, gains, and losses on the income statement. For us, this will just be depreciation expense and amortization expense.
4. Adjust for changes in non-cash working capital accounts like accounts receivable, inventory, and accounts payable (and many others).
5. Now consider Cash Flows from Investing Activities. This should reflect cash purchases and disposals of PP&E and other investments. What items on the balance sheet should be included? For PP&E, we grew PP&E – Net on the balance sheet. But we also have to remember that the growth in PP&E – Net is AFTER deducting depreciation. So how much PP&E – Gross was actually purchased? (Hint: Add Depreciation Expense to the change in PP&E - Net for each year) The same issue arises for purchases of intangibles, which were amortized.
6. Finally, consider Cash Flows from Financing Activities, which should reflect all inflows and outflows with investors and creditors. Make sure to include all debt and equity accounts here (but NOT retained earnings itself). Did the company pay any dividends? Be careful!
7. Check to make sure the change in cash flows on the statement of cash flows is equal to the change in cash on the balance sheet.

Valuation

1. Now for the “easy” part… do the DDM, FCF, and RIM models just like we learned in class, using the CAPM to get the cost of equity for Apple and assuming a 4% steady state growth rate for year 2023 and beyond. The cost of equity for Apple as of July 1, 2018 was approximately 8.52%, and 4.92 billion shares of common stock were outstanding at the time. The stock was trading at $190. Our valuation comes in slightly below that at $180.9. Feel free to make different assumptions and see which changes are required to justify a price of $190 or more.

Answer rating: 100% (QA)

Forecasting Exercise Income Statement Forecasting The most important part of the forecast is the Sales growth Apples growth has been all over the place in the last few years but currently analysts are ... View the full answer