Chapter 09 - Prospective Analysis
9-1
Chapter 9
Prospective Analysis
REVIEW Prospective analysis is the final step in the financial statement analysis process. It includes forecasting of the balance sheet, income statement and statement of cash flows. Prospective analysis is central to security valuation. Both the free cash flow and residual income valuation models described in Chapter 1 require estimates of future financial statements. We provide a detailed example of the forecasting process to project the income statement, the balance sheet, and the statement of cash flows. We describe the relevance of forecasting for security valuation and provide an example utilizing forecasted financial statements to implement the residual income valuation model. We discuss the concept of value drivers and their reversion to long-run equilibrium levels. In the appendix, we provide a detailed example of short-term cash flow forecasting.
Chapter 09 - Prospective Analysis
9-2
OUTLINE The Projection Process
Projecting Financial Statements
Application of Prospective Analysis in the Residual Income Valuation Model
Trends in Value Drivers
Short-term Forecasting (Appendix)
Chapter 09 - Prospective Analysis
9-3
ANALYSIS OBJECTIVES
Describe the importance of prospective analysis.
Explain the process of projecting the income statement, the balance sheet and the statement of cash flows.
Discuss and illustrate the Importance of Sensitivity Analysis.
Describe the implementation of the projection process in the valuation of equity securities.
Discuss the concept of value drivers and their reversion to long-run equilibrium levels.
Chapter 09 - Prospective Analysis
9-4
QUESTIONS 1. Prospective analysis is central to security valuation. All valuation models rely on
forecasts of earnings or cash flows that are, then, discounted back to the present to arrive at the estimated value of the security. Prospective analysis is also useful to examine the viability of companies’ strategic plans, that is, whether they will be able to generate sufficient cash flows from operations to finance expected growth or whether they will be required to seek external financing. In addition, prospective analysis is useful to examine whether announcing strategies will yield the benefits expected by management. Finally, prospective analysis can be used by creditors to assess companies’ ability to meet debt service requirements.
2. Prior to the forecasting process, financial statements can be recast to better portray
economic reality. Adjustments might include elimination of transitory items or reallocating them to past or future years, capitalizing (expensing) items that have been expensed (capitalized) by management, capitalizing operating leases and other forms of off-balance sheet financing, and so forth.
3. In addition to trend analysis, analysts frequently incorporate external (non-financial)
information into the prospective process. Some examples are the expected level of macroeconomic activity, the degree to which the competitive landscape is changing, any strategic initiatives that have been announced by management, and so forth.
4. The forecast horizon is the period for which specific estimates are made. It is usually
5-7 years. Forecasts beyond the forecast horizon are of dubious value since estimates are uncertain.
5. Since all valuation models are infinite horizon models, analysts frequently assume a
steady state into perpetuity after the forecast horizon. A common assumption is that the company will grow at the long-run rate of inflation, that is, remaining constant in real terms.
6. The projection process begins with an expected growth in sales. Gross profit and
operating expenses are, then, estimated as a percentage of forecasted sales using historical ratios and external information. Depreciation expense is usually estimated as a percentage of beginning gross depreciable assets under the assumption that depreciation policies will remain constant. Interest expense is usually estimated at an average borrowing rate applied to the beginning balance of interest bearing liabilities. Projections of expected interest rates are used for variable rate indebtedness and new borrowings. Finally, tax expense is estimated using the effective tax rate on pre-tax income.
7. In the first step, balance sheet items are projected using forecasted income sales
(COGS) and relevant turnover ratios. Long-term assets are projected using forecasted capital expenditures. Long-term liabilities are projected from current maturities of long-term debt disclosed in the debt footnote, and paid-in-capital is assumed to be constant in this stage. Retained earnings are projected adding (subtracting) projected profits (losses) and subtracting projected dividends. Once total liabilities and equities are forecasted, total assets is set equal to this amount and forecasted cash is computed as the plug figure.
Chapter 09 - Prospective Analysis
9-5
In the second step, long-term liabilities and equities are adjusted to yield the desired level of cash. The analyst must be careful to maintain the historical leverage ratio and adjust liabilities and equities proportionately.
8. The residual income model expresses stock price as the book value of stockholders’
equity plus the present value of expected residual income (RI). Residual income can be expressed in ratio form as,
RI = (ROEt – k) * BVt-1
Where ROE=NIt/BVt-1. This form highlights the fact that stock price is only impacted
so long as ROE k. In equilibrium, competitive forces will tend to drive rates of return (ROE) to cost (k) so that abnormal profits are competed away. The estimation of stock price, then, amounts to the projection of the reversion of ROE to its long-run value for a particular company and industry. ROE is a value driver since it impacts our valuation of the stock price. Its components (asset turnover and profit margin) are also value drivers
9. We can make two observations regarding the reversion of ROE:
a. ROEs tend to revert to a long-run equilibrium. This reflects the forces of competition. Furthermore, the reversion rate for the least profitable firms is greater than that for the most profitable firms. And finally, reversion rates for the most extreme levels of ROE are greater than those for firms at more moderate levels of ROE.
b. The reversion is incomplete. That is, there remains a difference of about 12%
between the highest and lowest ROE firms even after ten years. This may be the result of two factors: differences in risk that are reflected in differences in their costs of capital (k); or, greater (lesser) degrees of conservatism in accounting policies.
The reversion of ROA and NPM are similar. While some reversion of TAT is evident, it is much less than that of the other value drivers.
10. Short-term cash forecasts are key to assessments of short-term liquidity. An asset is
called "liquid" because it will or can be converted into cash within the current period. The analysis of short-term cash forecasts will reveal whether an entity will be able to repay short-term loans as planned. This also means such analysis is extremely important for a potential short-term credit grantor. Short-term cash forecasts often are relatively realistic and accurate because of the shortness of the time span covered.
11. A cash forecast, to be most meaningful, must be for a relatively short-term period of
time. There are many unpredictable variables involved in the preparation of a reliable forecast for a highly liquid asset such as cash. Over a long period of time (that is, beyond the time span of one year), the difference in the degree of liquidity among items in the current assets group is usually insignificant. What is more important for long time spans are the projections of net income and other sources and uses of funds. The focus should be shifted to working capital (and other accrual measures), and away from cash flows, for longer forecast horizons of, say, thirty months—where the time required to convert current assets into cash is insignificant.
Chapter 09 - Prospective Analysis
9-6
Chapter 09 - Prospective Analysis
9-7
12. Cash inflows and outflows are highly interrelated. These two flows are crucial to a company’s ―circulation system." A deficiency in any part of the system can affect the entire system. For example, a reduction or cessation of sales affects the vital conversion of finished goods into receivables or cash, which in turn leads to a drop in the cash reservoir. If the system is not strengthened by "transfusion" (such as additional investment by owners or creditors), production must be curtailed or discontinued. Lack of cash inflows also will reduce other expenses such as advertising, promotion, and marketing expenses, which will further adversely affect sales. This can yield a vicious cycle leading to business failure.
13. Most would agree with this assertion. Cash is the most liquid asset and when
management urgently needs to purchase assets or incur expenses, a cash exchange is the quickest and easiest means to execute a transaction. Moreover, unless management has a credit line established with a reliable outsider (such as a revolving account at a bank), lack of cash can mean a permanent loss of profitable opportunities.
14. Ratio analysis is a static measurement tool. Ratios measure relations among financial
statement items as of a given moment and time. In contrast, funds flow analysis is a dynamic measure covering a period of time. A dynamic model of funds flow analysis uses the present only as a starting point and utilizes the best available estimates of future plans and conditions to forecast the future availability and disposition of cash or working capital. Analyzing funds flow also encompasses the projected operations of a company. Since one of the fundamental assumptions of accounting is the going-concern concept, some assert that the dynamic model is more realistic and is superior to static representations. However, care should be taken in placing too much reliance on funds flow analysis as it is primarily based on estimates, and not on realized observations.
15. Except for transactions involving the raising of money from external sources (such as
through loans or additional investments) and the investments of money in long-term assets, almost all internally generated cash flows relate to and depend on sales. Accordingly, the usual first step in preparing a cash forecast is to estimate sales for the period under consideration. The reliability of any cash forecast depends on the accuracy of this forecast of sales. In arriving at the sales forecast, the analyst should consider: (1) past trends of sales volume, (2) market share, (3) industry and general economic conditions, (4) productive and financial capacity, and (5) competitive factors, among other variables.
Chapter 09 - Prospective Analysis
9-8
EXERCISES Exercise 9-1 (45 minutes) Projected Income Statement for Year 12
Quaker Oats Company Forecasted Income Statement
For Year Ended June 30, Year 12
Revenues [given]................................................................. $6,000.0
Costs and expenses
Cost of goods sold [a] ................................................... $3,186.0
Selling, general, and administrative [b] ....................... 2,439.4
Other expenses [c] ......................................................... 35.2
Interest, net [d] ............................................................... 91.4
Total costs and expenses ................................................... 5,752.0
Income from continuing operations ................................. 248.0
Income taxes [e] .................................................................. 105.9
Income before discontinued operations .......................... 142.1
(Loss) on disposal of discontinued operations [given] ... (2.0)
Net income ........................................................................... $ 140.1
Notes:
[a] Cost of sales is estimated to be at a level representing the average percentage of cost of sales to sales as prevailed in the four-year period ending June 30, Year 11, which is 53.1% (19,909.2 – 9,331.3)/19,909.2. Therefore, 6,000 x .531 = $3,186.
[b] Selling, general & administrative expenses in Year 12 are expected to increase by the same percentage as these expenses increased from Year 10 to Year 11, which is 15%. Therefore, $2,121.2 x 1.15 = $2,439.4.
[c] Other expenses are expected to be 8% higher in Year 12. Therefore, 32.6 x 1.08 = $35.2.
[d] Interest expense (net of interest capitalized) and interest income will increase by 6% due to increased financial needs. Therefore, $86.2 x 1.06 = $91.4
[e] The effective tax rate in Year 12 will equal that of Year 11, which is 42.7% ($175.7/$411.5). Therefore, tax expense = $248 x .427 = $105.9.
Chapter 09 - Prospective Analysis
9-9
Exercise 9-2 (25 minutes)
Spreadsheet to Compute Forecasts of Sales and Income
Change In Dec.
Change in Dec.
Change In March
Change in March
Change In June
Change In June
Change In Sept.
Change in Sept.
Date Sales N.I. Sales NI Sales NI Sales NI Sales NI
Dec-Y1 $17,349 $1,263 Mar-Y2 12,278 964 Jun-Y2 13,984 1,130 Sep-Y2 13,972 996 Dec-Y2 16,040 1,215 -$1,309 -$48 Mar-Y3 12,700 1,085 $422 $121 Jun-Y3 14,566 656 $582 -$474 Sep-Y3 14,669 1,206 $697 $210 Dec-Y3 17,892 1,477 1,852 262 Mar-Y4 12,621 1,219 -79 134 Jun-Y4 14,725 1,554 159 898 Sep-Y4 14,442 1,457 -227 251 Dec-Y4 17,528 1,685 -364 208 Mar-Y5 14,948 1,372 2,327 153 Jun-Y5 17,630 1,726 2,905 172 Sep-Y5 17,151 1,610 2,709 153 Dec-Y5 19,547 1,865 2,019 180 Mar-Y6 16,931 1,517 1,983 145 Jun-Y6 18,901 1,908 1,271 182 Sep-Y6 19,861 1,788 2,710 178 Dec-Y6 22,848 2,067 3,301 202 Mar-Y7 19,998 1,677 3,067 160 Jun-Y7 21,860 2,162 2,959 254 Sep-Y7 21,806 2,014 1,945 226 Dec-Y7 24,876 2,350 2,028 283 Mar-Y8 22,459 1,891 2,461 214 Jun-Y8 24,928 2,450 3,068 288 Sep-Y8 23,978 2,284 2,172 270 Dec-Y8 28,455 2,671 3,579 321 Mar-Y9 24,062 2,155 1,603 264 Jun-Y9 27,410 2,820 2,482 370 Average change for each quarter
$1,586.57
$201.14
$1,683.43
$170.14
$1,918.00
$241.43
$1,667.67
$214.67
Forecast Sep.Y9*
25,645.67
2,498.67
Forecast Dec.Y9*
30,041.57
2,872.14
Forecast Mar. Y0*
25,745.43
2,325.14
Forecast Jun. Y0*
29,328.00
3,061.43
* Most recent actual quarter + average change for the quarter. Note: Reported quarterly sales and net income for General Electric are:
Sales Net income Sep Y9 $27,200 $2,653 Dec Y9 32,855 3,089 Mar Y0 29,996 2,592
Chapter 09 - Prospective Analysis
9-10
Exercise 9-3 (40 minutes) a. To illustrate how predictions of market share and total market sales can be
used in the forecasting process, consider the following example. If an analyst, for instance, predicts that (i) Cough.com will maintain its 0.08% share of the market for children's cough medicine and (ii) total Industry sales of children's cough medicine for year 2006 is $3.2 billion, then a reasonable estimate of Cough.com's year 2006 sales is $2.56 million. This is computed as 0.08% market share multiplied by the expected $3.2 billion of industry sales.
b. All relevant data should be sought out, subject to cost-benefit considerations,
in the prediction of sales. The importance of sales to predictions of financial performance and financial condition cannot be overemphasized. Accordingly, companies invest considerable research and effort in predicting sales. Regarding what types of data to seek and how to obtain them, let’s consider a retailer. To project the sales of a retailer, an analyst might consider visiting outlets that sell the retailers’ products and observe customer-buying patterns versus the patterns observed for key competing products. This activity can be done using anecdotal observation or using formal statistical sampling depending upon the analysts' perceived need for accuracy. Moreover, the analyst can seek information from insiders via interview or interpretation of formal or informal disclosures made by the company. The analyst can also review company strategies and industry trends. In sum, good predictions involve more than sophisticated models—they demand that the analyst take the perspective of a customer constrained by the economic environment predicted to exist.
c. Relying on predicted year 2006 total industry sales of $3.2 billion, the sales of
Cough.com are predicted to be as follows
2006 Market share is 5% greater 2006 Market share is 5% worse
[105% x .08%] x $3.2 billion [95% x .08%] x $3.2 billion = $2.688 million = $2.432 million d. What-If industry sales are 10% higher:
[105% x .08%] x [110% x $3.2 billion] [95% x .08%] x [110% x $3.2 billion] = $2.9568 million = $2.6752 million What-If industry sales are 10% lower:
[105% x .08%] x [90% x $3.2 billion] [95% x .08%] x [90% x $3.2 billion] = $2.4192 million = $2.1888 million
Chapter 09 - Prospective Analysis
9-11
Exercise 9–4A (30 minutes)
Lyon Corporation Cash Forecast
For July, Year 6
Beginning cash balance ..................................................... $ 20
Cash collections
Beginning accounts receivable ............................... $ 20
Sales for month ........................................................ 150
170
Less: Ending accounts receivable .......................... 21 149
Cash available ..................................................................... $169
Cash disbursements
Beginning accounts payable ................................... 18
Purchases (note a) ................................................... 115
133
Ending accounts payable (25% of purchases) ....... 29 104
Miscellaneous outlays ............................................. 11
Cash balance ............................................................ $ 54
Minimum cash balance desired ............................... 30
Excess cash .............................................................. $ 24
[a] Ending inventory ................................................................................................... $ 15 Cost of goods sold (5/6 of sales) .......................................................................... 125 140 Less beginning inventory ..................................................................................... 25 Purchases ............................................................................................................. $115
Chapter 09 - Prospective Analysis
9-12
PROBLEMS Problem 9-1 (90 minutes) a.
Coca-Cola
INCOME STATEMENT Year 3
Estimate Year 2 Year 1
Net sales 20,297 20,092 19,889
Cost of goods 6,106 6,044 6,204
Gross profit 14,191 14,048 13,685
Selling general & administrative expense 7,972 7,893 9,221
Depreciation & amortization expense 863 803 773
Interest expense -66 -308 292
Income before tax 5,422 5,660 3,399
Income tax expense 1,620 1,691 1,222
Net income 3,802 3,969 2,177
Outstanding shares 3,491 3,491 3,481
RATIOS
Sales growth 1.02% 1.02%
Gross Profit Margin 69.92% 69.92%
Selling General & Administrative Exp / Sales 39.28% 39.28%
Depreciation (depn exp / pr yr PPE gross) 12.14% 12.14%
INT (int / pr yr LTD) -5.45% -5.45%
Tax (Inc Tax / Pre-tax inc) 29.88% 29.88%
Chapter 09 - Prospective Analysis
9-13
Problem 9-1 — continued
BALANCE SHEET Year 3
Estimate Year 2 Year 1
Cash 587 1,934 1,892
Receivables 1,901 1,882 1,757
Inventories 1,066 1,055 1,066
Other 2,300 2,300 1,905
Total current assets 5,854 7,171 6,620
Property, plant & equipment 8,305 7,105 6,614
Accumulated depreciation 3,515 2,652 2,446
Net property & equipment 4,791 4,453 4,168
Other assets 10,793 10,793 10,046
Total assets 21,438 22,417 20,834
Accounts payable & accrued liabilities 3,717 3,679 3,905
Short-term debt & cmltd 3,899 3,899 4,816
Income taxes 815 851 600
Total current liab 8,431 8,429 9,321
Deferred income, taxes & other 1,403 1,403 1,362
Long term debt 1,219 1,219 835
Total liabilities 11,053 2,622 2,197
Common stock 873 873 870
Capital surplus 3,520 3,520 3,196
Retained earnings 19,674 20,655 18,543
Treasury stock 13,682 13,682 13,293
Shareholder equity 10,385 11,366 9,316
Total liabilities & net worth 21,438 22,417 20,834
RATIOS
AR turn 10.68 10.68 11.32
INV turn 5.73 5.73 5.82
AP turn 1.64 1.64 1.59
Tax Pay (Tax pay / tax exp) 50.33% 50.33% 49.10%
FLEV 2.06 1.97 2.24
Div/sh $1.37 $1.37 $1.21
CAPEX 1,200 1188 1165
CAPEX/Sales 5.91% 5.91% 5.86%
Chapter 09 - Prospective Analysis
9-14
Problem 9-1 — continued
Statement of Cash Flows Year 3
Estimate
Net income 3,802
Depreciation 863
Accounts receivable -19
Inventories -11
Accounts payable 38
Income taxes -36
Net cash flow from operations 4,636
CAPEX -1,200
Net cash flow from investing activities -1,200
Long term debt 0
Additional paid in capital 0
Dividends -4,783
Net cash flow from financing activities -4,783
_____
Net change in cash -1,347
Beginning cash 1,934
Ending cash 587
b. Based on our initial projection of Coca-Cola’s balance sheet, it appears that
the company will require approximately $1.5 billion of external financing in Year 3. This amount will yield a cash balance of approximately $2 billion, consistent with prior years.
Chapter 09 - Prospective Analysis
9-15
Problem 9-2 (95 minutes) a.
Best Buy
Year 3
Estimate Year 2 Year 1
Income statement
Net sales 18,800 15,326 12,494
Cost of goods 15,048 12,267 10,101
Gross profit 3,752 3,059 2,393
Selling general & administrative expense 2,761 2,251 1,728
Depreciation & amortization expense 304 167 103
Income before tax 688 641 562
Income tax expense 263 245 215
Net income 425 396 347
Outstanding shares 208 208 200
RATIOS
Sales growth 22.67% 22.67%
Gross Profit Margin 19.96% 19.96%
Selling General & Administrative Exp / Sales 14.69% 14.69%
DEPRECIATION (depn exp / pr yr PPE gross) 15.28% 15.28%
Tax (Inc Tax / Pre-tax inc) 38.22% 38.22%
Chapter 09 - Prospective Analysis
9-16
Problem 9-2 — continued
BALANCE SHEET Year 3
Estimate Year 2 Year 1
Cash 196 746 751
Receivables 384 313 262
Inventories 2,168 1,767 1,184
Other 102 102 41
Total current assets 2,850 2,928 2,238
Property, plant & equipment 3,249 1,987 1,093
Accumulated depreciation 847 543 395
Net property & equipment 2,403 1,444 698
Other assets 466 466 59
Total assets 5,719 4,838 2,995
Accounts payable & accrued liabilities 3,034 2,473 1,704
Short-term debt & cmltd 114 114 16
Income taxes 136 127 65
Total current liab 3,284 2,714 1,785
Long term liabilities 122 122 100
Long term debt 67 181 15
Total long-term liabilities 189 303 115
Common stock 20 20 20
Capital surplus 576 576 247
Retained earnings 1,650 1,225 828
Shareholder equity 2,246 1,821 1,095
Total liabilities & net worth 5,719 4,838 2,995
RATIOS
AR turn 48.96 48.96 47.69
INV turn 6.94 6.94 8.53
AP turn 4.96 4.96 5.93
Tax Pay (Tax pay / tax exp) 51.84% 51.84% 30.23%
FLEV 2.55 2.66 2.74
Div/sh $0.00 $0.00 $0.00
CAPEX 1,262 1029 416
CAPEX/Sales 6.71% 6.71% 3.33%
Chapter 09 - Prospective Analysis
9-17
Problem 9-2 — continued
Statement of Cash Flows Year 3
Estimate
Net income 425
Depreciation 304
Accounts receivable -71
Inventories -401
Accounts payable 561
Income taxes 9
Net cash flow from operations 827
CAPEX -1,262
Net cash flow from investing activities -1,262
Long term debt -114
Additional paid in capital 0
Dividends 0
Net cash flow from financing activities -114
____
Net change in cash -550
Beginning cash 746
Ending cash 196
b. Based on our projection, it appears that Best Buy will require about $550
Million of external financing to yield a cash balance of approximately $750 million. Analysts must allocate this external financing between debt and equity so as to preserve the financial leverage level presently used by Best Buy.
Chapter 09 - Prospective Analysis
9-18
Problem 9-3 (90 minutes) a.
Merck
INCOME STATEMENT Year 3
Estimate Year 2 Year 1
Net sales 56,435 47,716 40,343
Cost of goods 34,272 28,977 22,444
Gross profit 22,164 18,739 17,900
Selling general & administrative expense 7,725 6,531 6,469
Depreciation & amortization expense 1,661 1,464 1,277
Interest expense 237 342 329
Income before tax 12,541 10,403 9,824
Income tax expense 3,762 3,121 3,002
Net income 8,779 7,282 6,822
Outstanding shares 2,976 2,976 2,968
RATIOS
Sales growth 18.27% 18.27%
Gross Profit Margin 39.27% 39.27%
Selling General & Administrative Exp / Sales 13.69% 13.69%
DEPRECIATION (depn exp / pr yr PPE gross) 8.76% 8.76%
INT (int / pr yr LTD) 4.94% 4.94%
Tax (Inc Tax / Pre-tax inc) 30.00% 30.00%
Chapter 09 - Prospective Analysis
9-19
Problem 9-3 — continued
BALANCE SHEET Year 3
Estimate Year 2 Year 1
Cash 5,254 3,287 4,255
Receivables 6,168 5,215 5,262
Inventories 4,233 3,579 3,022
Other 880 880 1,059
Total current assets 16,536 12,961 13,598
Property, plant & equipment 24,056 18,956 16,707
Accumulated depreciation 7,514 5,853 5,225
Net property & equipment 16,543 13,103 11,482
Other assets 17,942 17,942 15,075
Total assets 51,020 44,007 40,155
Accounts payable & accrued liabilities 6,983 5,904 5,391
Short-term debt & cmltd 4,067 4,067 3,319
Income taxes 1,897 1,573 1,244
Total current liab 12,947 11,544 9,954
Deferred income, taxes and other 11,614 11,614 11,768
Long term debt 4,787 4,799 3,601
Total liabilities 29,347 27,957 25,323
Common stock 30 30 30
Capital surplus 6,907 6,907 6,266
Retained earnings 37,123 31,500 27,395
Treasury stock 22,387 22,387 18,858
Shareholder equity 21,673 16,050 14,832
Total liabilities & net worth 51,020 44,007 40,155
RATIOS
AR turn 9.15 9.15 7.67
INV turn 8.10 8.10 7.43
AP turn 4.91 4.91 4.16
Tax Pay (Tax pay / tax exp) 50.41% 50.41% 41.45%
FLEV 2.35 2.74 2.71
Div/sh $1.06 $1.06 $0.98
CAPEX 5,100 4312 3641
CAPEX/Sales 9.04% 9.04% 9.03%
Chapter 09 - Prospective Analysis
9-20
Problem 9-3 — continued
Statement of Cash Flows Year 3
Estimate
Net income $ 8,779
Depreciation 1,661
Accounts receivable -953
Inventories -654
Accounts payable 1,079
Income taxes 323
Net cash flow from operations 10,235
CAPEX -5,100
Net cash flow from investing activities -5,100
Long term debt -12
Additional paid in capital 0
Dividends -3,156
Net cash flow from financing activities -3,168
_____
Net change in cash 1,967
Beginning cash 3,287
Ending cash 5,254
b. Based on our initial projections, it appears that Merck will have excess cash of
approximately $2 billion in year 3. This excess cash should be used to reduce both debt and equity so as to maintain historical financial leverage.
Chapter 09 - Prospective Analysis
9-21
Problem 9-4 (90 minutes)
Historical
figures Forecast Horizon
Terminal Year
Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 20x8 20x8
Sales growth 8.50% 10.65% 10.65% 10.65% 10.65% 10.65% 10.65% 3.50%
Net profit Margin (Net income/Sales) 6.71% 8.22% 8.22% 8.22% 8.22% 8.22% 8.22% 8.22%
NWC turn (Sales/avg NWC) 8.98 9.33 9.33 9.33 9.33 9.33 9.33 9.33
FA turn (Sales/avg FA) 1.67 1.64 1.64 1.64 1.64 1.64 1.64 1.64
Total operating assets/Total equity 1.96 2.01 2.01 2.01 2.01 2.01 2.01 2.01
Cost of equity 12.5%
($ Thousands)
Sales 25,423 28,131 31,127 34,443 38,112 42,171 46,663 48,297
Net income ($ Mil) 1,706 2,312 2,558 2,831 3,132 3,466 3,835 3,969
Net working capital 2,832 3,015 3,336 3,692 4,085 4,520 5,001 5,176
Fixed assets 15,232 17,136 18,961 20,981 23,216 25,689 28,425 29,420
Total Operating assets 18,064 20,151 22,297 24,673 27,301 30,209 33,426 34,596
L-T Liabilities 8,832 10,132 11,211 12,405 13,727 15,189 16,807 17,395
Total Stockholder's Equity ($ Mil) 9,232 10,019 11,086 12,267 13,574 15,020 16,619 17,201
Residual Income Computation
Net Income 2,558 2,831 3,132 3,466 3,835 3,969
Beginning Equity 10,019 11,086 12,267 13,574 15,020 16,619
Required Equity Return 12.5% 12.5% 12.5% 12.5% 12.5% 12.5%
Expected Earnings 1,252 1,386 1,533 1,697 1,877 2,077
Residual Income 1,306 1,445 1,599 1,769 1,958 1,892
Discount factor 0.89 0.79 0.70 0.62 0.55
Present value of residual income 1,161 1,142 1,123 1,105 1,086
Cum PV residual income 1,161 2,303 3,425 4,530 5,616
Terminal value of abnormal earnings 11,665
Beg book value of equity 10,019
Value of equity - Abnormal Earnings 27,301
Common shares outstanding (mil) 1,737
per share $15.72
Chapter 09 - Prospective Analysis
9-22
Problem 9-5 (90 minutes) a.
Telnet Corporation Pro Forma Income Statement ($000s) Six Months Ended June 30, Year 2
Sales revenue ($250 x 6 mos.) ................................................................... $1,500
Cost of goods sold (note [a]) ..................................................................... 1,199
Gross margin ............................................................................................... 301
Selling and administrative expenses ($47.5 x 6 mos.) ............................. 285
Expected pre-tax income ............................................................................ 16
Estimated income taxes (at 50%) ............................................................... 8
Expected net income .................................................................................. $ 8
Note [a]: We use T-accounts to compute cost of goods sold ($ thousands)
Raw Material Inventory
Beginning (given) 0 Material purchases ($125 x 6 mos.) 750
715 To W.I.P. inventory [a] (plug)
Ending (given) 35
Work in Process Inventory Beginning (given) 0 From raw materials inventory [a] 715 Labor ($30.5 x 6 mos.) 183 Variable overhead ($22.5 x 6 mos.) 135 Rent ($10 x 6 mos.) 60 Depreciation ($35 x 6 mos.) 210 Patent amortization ($.5 x 6 mos.) 3
7 Prepaid expenses (given) 1,299 To F.G. inventory [b] (plug)
Ending (given) 0
Finished Goods Inventory Beginning (given) 0 From W.I.P. inventory [b] 1,299
1,199 Cost of goods sold (plug)
Ending (given) 100
Chapter 09 - Prospective Analysis
9-23
Problem 9-5 — continued b. Telnet Corporation Pro forma Balance Sheet ($000s) June 30, Year 2
ASSETS
Cash ............................................................................. $ 40 (minimum cash)
Accounts receivable ................................................... 375 (45 days' sales)*
Inventories ($35 + $100) ............................................. 135 (given)
Prepaid expenses ....................................................... 7 (given)
Total current assets .................................................. 557 (subtotal)
Equipment ................................................................... 1,200 (given)
Less accumulated depreciation ................................ 210 ($35 x 6 mos.)
Equipment, net .......................................................... 990 (subtotal)
Patents ........................................................................ 40 (given)
Less amortization ....................................................... 3 ($500 x 6 mos.)
Patents, net ............................................................... 37 (subtotal)
Total Assets ................................................................ $1,584
LIABILITIES AND STOCKHOLDERS’ EQUITY
Accounts payable ....................................................... $ 125 (30 days' purchases)**
Accrued taxes ............................................................. 8 (from Inc. Stmt.)
Stockholders' equity................................................... 1,300 (given)
Retained earnings....................................................... 8 (from Inc. Stmt.)
Additional funds needed ............................................ 143 "plug"
Total liabilities and equity .......................................... $1,584
* ($250,000 x 6) / 180 days = $8,333 per day x 45 days = $375,000 ** ($125,000 x 6) / 180 days = $4,166 per day x 30 days = $125,000
Chapter 09 - Prospective Analysis
9-24
Problem 9-5 — continued c.
Telnet Corporation Forecasted Statement of Cash Flows For Six Months Ended June 30, Year 2
Cash balance, beginning .................................................. $ 60,000
Add collection of accounts receivable * ........................... 1,125,000 $1,185,000
Less disbursements for
Material purchases ** .................................................... 625,000
Labor .............................................................................. 183,000
Rent ................................................................................ 60,000
Overhead ....................................................................... 135,000
Selling expense ............................................................. 285,000 (1,288,000)
Tentative cash balance ...................................................... $ (103,000)
Minimum cash balance required ....................................... 40,000
Additional borrowing required .......................................... $ 143,000
Ending cash balance ......................................................... $ 40,000
Loan balance ....................................................................... $ 143,000
* Collection of accounts receivable Jan. Feb. Mar. Apr. May June Sales ....................................................................... 250 250 250 250 250 250 Collections .............................................................. 0 125 250 250 250 250 Accumulated Collections ....................................... 0 125 375 625 875 1,125 ** Payment of accounts payable Jan. Feb. Mar. Apr. May June Purchases ............................................................... 125 125 125 125 125 125 Payments ................................................................ 0 125 125 125 125 125 Accumulated Payments ......................................... 0 125 250 375 500 625
Chapter 09 - Prospective Analysis
9-25
Problem 9-6 (95 minutes)
Quaker Oats Forecasted Statement of Cash Flows For Year Ended June 30, Year 12
Cash provided by (used for) operations
Net income (a) ............................................................................................. $ 238.8
Items in income not affecting cash
Depreciation & amortization (b) ............................................................... 196.6
Deferred income taxes (c) ........................................................................ 54.7
Provision for restructuring charges (given) ........................................... 0.0
Increase in receivables (d) ......................................................................... (8.9)
Increase in inventories (e).......................................................................... (45.2)
Increase in other current assets (f) ........................................................... (25.6)
Increase in accounts payable (g) .............................................................. 42.1
Increase in other current liabilities (h) ...................................................... 24.5
Cash provided by operating activities ...................................................... $ 477.0
Cash provided by (used for) investment activities
Capital expenditures, PP&E (given) .......................................................... $ (300.0)
Asset retirements (given) ........................................................................... 20.0
Other changes (given) ................................................................................ (30.0)
Cash used for investing activities ............................................................. $ (310.0)
Cash provided by (used for) financing activities
Repayments of L-T debt (given) ................................................................ $ (45.0)
Net decrease in S-T debt (given) ............................................................... (40.0)
Cash dividend paid (given) ........................................................................ (135.0)
Additions to L-T debt—plug (i) .................................................................. 55.8
Cash provided by financing activities ....................................................... $(164.2)
Net increase in cash (j) ............................................................................... $ 2.8
Cash, beginning balance ........................................................................... 30.2
Cash, balance at end of year ..................................................................... $ 33.0
Notes: (a) Average percent of income from continuing operations to sales, Years 9-11
($235.8 +$228.9 + $148.9) / ($5,491.2 + $5,030.6 + $4,879.4) = 3.98%
Net income in Year 12 = $6,000 x .0398 = $238.8
Chapter 09 - Prospective Analysis
9-26
Problem 9-6 – continued
(b) Depreciation and amortization in Year 12 = $238.8 x .8233 = $196.6 (c) Average percent of deferred income taxes (noncurrent) and other items to income from
continuing operations, Years 9-11: $140.4 / $613.6 = 22.9% Noncurrent deferred income tax in Year 12 = $238.8 x .229 = $54.7 (d) Ending accounts receivable = $6,000 x (42/360) = $700.0
For Year 12: Accounts receivable, beg $691.1 Accounts receivable, end 700.0 Increase $ 8.9
(e) Year 12 cost of sales = $6,000 x .51 = $3,060
Ending inventory = $3,060 x (55/360) = $467.5 For Year 12: Inventory, beg $422.3
Inventory, end 467.5 Increase $ 45.2
(f) ($13.7 + $14.1 + $48.9)/3 = $25.6 (g) Year 12 purchases = $2,807.2 x 1.12 = $3,144.1
Accounts payable, end = $3,144.1 x (45/360) = $393.0 For Year 12: Accounts payable, beg $350.9
Accounts payable, end 393.0 Increase $ 42.1
(h) ($43.2 + $83.4 - $53.1)/3 = $24.5 (i) Amount required to balance statement. (j) Percent of cash to revenues in Year 11 = $30.2 / $5,491.2 = 0.55%
Year-end cash in Year 12 = $6,000 x 0.55% = $33 Increase in cash for Year 12 = $33 - $30.2 = $2.8
Chapter 09 - Prospective Analysis
9-27
CASES Case 9-1 (60 minutes)
Kodak
INCOME STATEMENT 20x7 Est 20x6 20x5
Net sales 12,515 13,234 13,994
Cost of goods 8,199 8,670 8,375
Gross profit 4,316 4,564 5,619
Selling general & administrative expense (except depreciation) 1,761 1,862 1,776
Depreciation expense 766 765 738
Research & development costs 737 779 784
Goodwill amortization 0 154 151
Restructuring costs (credits) 0 659 -44
Earnings from operations 1,052 345 2,214
Interest expense 208 219 178
Other expense (income) 18 18 -96
Income before tax 827 108 2,132
Income tax expense 245 32 725
Net income 582 76 1,407
Outstanding shares 290 290 290
RATIOS
Sales growth -5.43% -5.43%
Gross Profit Margin 34.49% 34.49%
Selling General & Administrative Exp / Sales 14.07% 14.07%
DEPRECIATION (depn exp / pr yr PPE gross) 5.90% 5.90%
R&D/sales 5.89% 5.89%
INT (int / pr yr STD and LTD) 6.49% 6.49%
Tax (Inc Tax / Pre-tax inc) 29.63% 29.63%
Chapter 09 - Prospective Analysis
9-28
Case 9-1 – continued
BALANCE SHEET 20x7 Est 20x6 20x5
Cash $ 17 $ 448 $ 246
Receivables 2,210 2,337 2,653
Inventories 1,075 1,137 1,718
Other 761 761 874
Total current assets 4,064 4,683 5,491
Property, plant & equipment 13,972 12,982 12,963 (NOTE 4)
Accumulated depreciation 8,089 7,323 7,044
Net property & equipment 5,883 5,659 5,919
Other assets 3,020 3,020 2,802
Total assets $12,967 $13,362 $14,212
Accounts payable & accrued liabilities 3,098 3,276 3,403
Short-term debt 1,378 1,378 2,058
Current maturities of l-t debt 13 156 148 (NOTE 8)
Income taxes 544 544 606
Total current liab 5,033 5,354 6,215
Long term debt 1,653 1,666 1,166
Postemployment liabilities 2,728 2,728 2,722
Other long-term liabilities 720 720 681
Total liabilities 10,134 10,468 10,784
Common stock 978 978 978
Capital surplus 849 849 871
Retained earnings 6,773 6,834 7,387
Treasury stock 5,767 5,767 5,808
Shareholder equity 2,833 2,894 3,428
Total liabilities & net worth 12,967 13,362 14,212
RATIOS
AR turn 5.66 5.66 5.27
INV turn 7.63 7.63 4.87
AP turn 2.65 2.65 2.46
FLEV 4.58 4.62 4.15
Div/sh $2.22 $2.22 $1.88
CAPEX 990 1047 783
CAPEX/Sales 7.91% 7.91% 5.60%
Chapter 09 - Prospective Analysis
9-29
Case 9-1 – continued
Statement of Cash Flows 20x7 Estim.
Net income $ 582
Depreciation 766
Accounts receivable 127
Inventories 62
Accounts payable (178)
Net cash flow from operations 1,359
CAPEX (990)
Net cash flow from investing activities (990)
Long term debt (156)
Dividends (643)
Net cash flow from financing activities (799)
_____
Net change in cash (431)
Beginning cash 448
Ending cash $ 17
Chapter 09 - Prospective Analysis
9-30
Case 9-2 (120 minutes)
Miller Company Cash Forecast For Years Ended December 31, Years 2 through 4 Year 2 Year 3 Year 4
Cash balance at beginning of period .................. $ 0 $1,929,000 $254,500 Cash received from stockholders ....................... 100,000 0 0 Proceeds of loan (see [a]) .................................... 1,700,000 100,000 0 Cash receipts less cash payments (see [b]) ...... 129,000 125,500 146,500 Payments for construction .................................. 0 (1,700,000) (100,000) Payments on loan (see [a]) .................................. 0 (200,000) (200,000) Cash balance at end of period ............................. $1,929,000 $ 254,500 $101,000
Supporting Schedules for the Cash Forecast
[a] Schedule of interest and commitment fees
Amount of Interest Loan or Fee
Year 2: To be borrowed 1/1 ................................................................ $ 800,000 To be borrowed 4/1 ................................................................ 500,000 Commitment fee due 4/1 ($1,000,000 x 1% x 1/4) ................. $ 2,500 To be borrowed 7/1 ................................................................ 300,000 Commitment fee due 7/1 ($500,000 x 1% x 1/4) .................... 1,250 To be borrowed 12/31 ............................................................ 100,000 Commitment fee due 12/31 ($200,000 x 1% x 1/2) ................ 1,000 Interest due on loan: On $800,000 @ 5% ............................................................ 40,000 On $500,000 @ 5% x 3/4 ................................................... 18,750 On $300,000 @ 5% x 1/2 ................................................... 7,500 Total at 12/31/Year 2 ............................................................... $1,700,000 $71,000 Year 3: To be borrowed 4/1 ................................................................ 100,000 Commitment fee due 4/1 ($100,000 x 1% x 1/4) .................... 250 Repayment of loan: Due 6/30 ............................................................................ (100,000) Due 12/31 .......................................................................... (100,000) Interest due on loan: On $1,700,000 @ 5% x 1/4 ................................................ 21,250 On $1,800,000 @ 5% x 1/4 ................................................ 22,500 On $1,700,000 @ 5% x 1/2 ................................................ 42,500 Total at 12/31/Year 3 ............................................................... $1,600,000 $86,500 Year 4: Repayment of loan: Due 6/30 ............................................................................ (100,000) Due 12/31 .......................................................................... (100,000) Interest due on loan: On $1,600,000 @ 5% x 1/2 ................................................ 40,000 On $1,500,000 @ 5% x 1/2 ................................................ 37,500 Total at 12/31/Year 4 ............................................................... $1,400,000 $77,500
Chapter 09 - Prospective Analysis
9-31
Case 9-2 – continued [b] Schedule of Operating Results
Year 2 Year 3 Year 4
Results of operations
Operating profit (at $.04 per ton handled) ................. $200,000 $212,000 $224,000
Interest and commitment fees (above) ...................... 71,000 86,500 77,500
Cash derived from operations .................................... 129,000 125,500 146,500
Depreciation (at $.03 per ton handled) ....................... 150,000 159,000 168,000
Operating loss ............................................................. $ 21,000 33,500 21,500
Operating loss from prior year(s) ............................... 21,000 54,500
Accumulated operating loss ...................................... $ 54,500 $ 76,000
Interpretation and Evaluation As revealed in note [b], reporting on the "results of operations," Miller Company is expected to record operating losses for each of the next three years under analysis. Nevertheless, the analysis also reveals that Miller is expected to generate sufficient cash flow to cover the various cash commitments.
Chapter 09 - Prospective Analysis
9-32
Case 9-3 (100 minutes)
Royal Company Cash Forecast For Years Ending March 31, Years 6 and 7
Year 6 Year 7
Beginning balance of cash ...................................... $ 0 $ 75,000 Cash receipt from customers (see Schedule A) .... 825,000 1,065,000 Cash disbursements Direct materials (see Schedule B) ......................... 220,000 245,000 Direct labor ............................................................. 300,000 360,000 Variable overhead .................................................. 100,000 120,000 Fixed costs ............................................................ 130,000 130,000 Total cash disbursements ..................................... 750,000 855,000 Operating cash receipts less disbursements ........ 75,000 210,000 Cash from sale of receivables and inventories ..... 90,000 0 Total cash available ................................................. $165,000 $ 285,000 Payments to general creditors ................................ 90,000 270,000 2
Ending balance of cash ........................................... $ 75,000 1 $ 15,000
1 This amount could have been used to pay general creditors or carried forward to the beginning of the next year.
2 Computed as: ($600,000 x 60%) - ($50,000 + $40,000).
Schedule A
Cash Receipts from Customers Year 6 Year 7
Sales ....................................................................................... $900,000 $1,080,000 Beginning accounts receivable ............................................. 0 75,000 Total ...................................................................................... 900,000 1,155,000 Less: Ending accounts receivable ........................................ 75,000 90,000 Cash receipts from customers .............................................. $825,000 $1,065,000
Schedule B
Cash Disbursements for Direct Materials Year 6 Year 7
Direct materials required for production ................... $200,000 $240,000 Required ending inventory ......................................... 40,000 3 50,000 4
Total ........................................................................... 240,000 290,000 Less: Beginning inventory ......................................... 0 40,000 Purchases ................................................................. 240,000 250,000 Beginning accounts payable ...................................... 0 20,000
Total ........................................................................... 240,000 270,000 Less: Ending accounts payable ................................. 20,000 25,000 Disbursements for direct materials ........................... $220,000 $245,000
3 Computed as: 12,000 units x 2/12 = 2,000; 2,000 x $20 per unit = $40,000.
4 Computed as: 15,000 units x 2/12 = 2,500; 2,500 x $20 per unit = $50,000.
Chapter 09 - Prospective Analysis
9-33
Case 9-4 (115 minutes) a. (1)
Estimated Total Cash Receipts Sep. Oct. Nov. Dec.
Total sales ............................................. $40,000 $48,000 $60,000 $80,000 Credit sales (25%) ................................ 10,000 12,000 15,000 20,000 Cash sales ............................... $30,000 36,000 $45,000 $60,000 Receipts of past month's credit sales 10,000 12,000 15,000 Total cash receipts ............................... $46,000 $57,000 $75,000
(2)
Estimated Cash Disbursements for Purchases Oct. Nov. Dec. Total
Total Sales ....................................... $48,000 $60,000 $80,000 —
Purchases (70% next mo. sales) .... $42,000 $56,000 $25,200 $123,200 Less: 2% purchase discount .......... 840 1,120 504 2,464 Cash disbursements ....................... $41,160 $54,880 $24,696 $120,736
(3)
Estimated Cash Disbursements for Operating Expenses Oct. Nov. Dec. Total
Sales ................................................. $48,000 $60,000 $80,000 —
Salaries and Wages (15%) .............. $ 7,200 $ 9,000 $12,000 $28,200 Rent (5%) .......................................... 2,400 3,000 4,000 9,400 Other Expenses (4%) ...................... 1,920 2,400 3,200 7,520 Cash disbursements ....................... $11,520 $14,400 $19,200 $45,120
(4)
Estimated Total Cash Disbursements Oct. Nov. Dec. Total
Purchases [part (2)] ......................... $41,160 $54,880 $24,696 $120,736 Operating expenses [part (3)] ........ 11,520 14,400 19,200 45,120 Plant and equipment (given) .......... 600 400 1,000 Total cash disbursements .............. $53,280 $69,680 $43,896 $166,856
(5)
Estimated Net Cash Receipts and Disbursements Oct. Nov. Dec. Total
Total cash receipts .......................... $46,000 $57,000 $75,000 $178,000 Total cash disbursements .............. 53,280 69,680 43,896 166,856 Net cash increase ............................ $31,104 $ 11,144 Net cash decrease ........................... $ 7,280 $12,680
Chapter 09 - Prospective Analysis
9-34
Case 9-4 — continued (6)
Estimated Financing Required Oct. Nov. Dec. Total
Beginning cash balance ................. $12,000 $ 8,720 $ 8,040 $12,000
Net cash increase ............................ 31,104 11,144
Net cash decrease ........................... 7,280 12,680
Cash position before financing ...... $ 4,720 $(3,960) $39,144 $23,144
Financing required .......................... 4,000 12,000 16,000
Interest expense 1 ............................ (180) (180)
Financing retired ............................. (16,000) (16,000)
Ending cash balance ....................... $ 8,720 $ 8,040 $22,964 $22,964
1 Computed as: ($4,000 x .06 x 3/12) + ($12,000 x .06 x 2/12).
b. (1) Union Corporation Forecasted Income Statement For the Quarter Ended December 31, Year 6
Sales [see (1) in part a] ...................................................... $188,000
Deduct
Cost of goods sold (70% of sales) ............................... $131,600
Less: Purchase discounts taken [see (2) in part a] .... 2,464 129,136
Gross profit ......................................................................... 58,864
Selling and administrative expenses
Salaries and wages [see (3) in part a] ......................... 28,200
Rent [see (3) in part a] .................................................. 9,400
Other expenses [see (3) in part a] ............................... 7,520
Depreciation ($750 x 3 months) ................................... 2,250
Total selling and administrative expenses ...................... 47,370
Operating income ............................................................... 11,494
Interest expense ................................................................. 180
Net income ....................................................................... $ 11,314
Chapter 09 - Prospective Analysis
9-35
Case 9-4 — continued (2) Union Corporation Forecasted Balance Sheet As of December 31, Year 6
ASSETS Current Assets
Cash [see (6) in part a] ................................................. $ 22,964
Accounts receivable (25% of Dec. sales) .................... 20,000
Inventory [($30,000 + 70% of $36,000) x 98%] ............ 54,096
Total current assets ........................................................... $ 97,060
Plant and equipment .......................................................... 101,000
Less: Accumulated depreciation ...................................... 2,250 98,750
Total assets ........................................................................ $195,810
LIABILITIES AND EQUITY
Liabilities ............................................................................. $ 0
Stockholders' equity .......................................................... 195,810
Total liabilities and equity ................................................. $195,810