Building A Financial Model

J.M. Smucker--Building a Financial Model Chapter 7 PURPOSE OF MODEL: BUILD FINANCIAL MODEL FOR SMUCKER IN ORDER TO: Predict future FCFs Value firm as a whole Value component securities (Note that this is a "top down" valuation") ANALYZING SMUCKER SALES J.M. Smucker Co. Adjusting Sales for Inflation Financial Year Ending April 30 1986 1987 1988 1989 1990 1991 1992 Sales ($M) 262.80 288.26 314.25 366.86 422.36 454.98 483.47 Average CPI 108.57 110.62 115.18 120.25 126.12 132.97 137.60 Constant Dollar Sales 333.07 358.57 375.41 419.79 460.80 470.82 483.47 Note: Smucker's financial year ends April 30. The CPI in each row of the table is the average monthly CPI for the year ending April 30. Benninga/Sarig, slides for Chapter 7 page 1 J. M. Smucker Co. Sales in Real and Nominal Dollars 500,000 450,000 400,000 350,000 Nominal Sales Real Sales 300,000 250,000 1987 1988 1989 1990 1991 1992 Benninga/Sarig, slides for Chapter 7 page 2 Building a sales regression model: Regressing Sales Growth on Household Growth 14% 12% 10% 8% 6% 4% y = 5.0972x - 0.0084 R2 = 0.5869 2% 0% 0.00% 0.50% 1.00% 1.50% 2.00% 2.50% % change in real sales = −0.00875 + 5.12852*% change in households R 2 = 58.65% Sales model (NOTE that base year is 1992) Domestic real sales Based on household growth--1.4% per year In short run: 5*1.4% = 7% (as in regression) In long run: 1.4% = household growth International real sales: 10% in 1993 & 1994 1.4% after 1997 Interpolated in 1995 & 1996 Benninga/Sarig, slides for Chapter 7 page 3 J.M. Smucker Co. Sales Projections, 1993-1997 year real growth rate of U.S. sales U.S. Sales ($millions) International real growth rate International sales Constant-dollar sales Annual inflation rate Cumulative adjustment factor Current-dollar (nominal) sales Projected nominal sales growth 1992 (base) $442.88 $40.59 483.47 1993 7% $473.88 10% $44.65 518.53 3% 1.0300 534.09 10.47% 1994 7% $507.05 10% $49.11 556.17 4% 1.0712 595.77 11.55% 1995 4% $527.34 10% $54.03 581.36 4% 1.1140 647.66 8.71% 1996 1.4% $534.72 6% $57.27 591.98 4% 1.1586 685.88 5.90% 1997 1.4% $542.20 1.4% $58.07 600.27 4% 1.2050 723.30 5.46% 483.47 Nominal sales = real sales * (1 + inflation projections). Assume 3% inflation in 1993 Assume 4% inflation/year afterwards Benninga/Sarig, slides for Chapter 7 page 4 Operating cost analysis COGS = 65% * Sales SG&A = 22% * Sales J.M. Smucker Co. Operating Cost Percentages FYE ending April 30 COGS as % of Sales SG&A as % of Sales 1986 66.9% 22.1% 1987 66.7% 21.5% 1988 65.6% 22.4% 1989 65.5% 22.9% 1990 66.6% 21.8% 1991 65.0% 23.7% 1992 65.0% 23.8% A/R = Sales/12 A/P = 13.7%*Sales Inventory = 87 days = 0.238 * Sales 87/365 = 0.238 J.M. Smucker Co. Analysis of Net Working Capital 1986 Average (days) collection period 33.16 53.47 49.75 24.92 74.67 1987 28.80 50.92 43.91 31.67 75.58 1988 29.49 51.38 45.03 30.17 75.20 1989 30.76 45.93 46.16 29.54 75.69 1990 33.96 48.57 52.25 34.31 86.56 1991 31.38 49.66 48.30 40.21 88.50 Average payable period (days) Inventory days--raw materials Inventory days--finished goods Inventory days Benninga/Sarig, slides for Chapter 7 page 5 FIXED ASSET ACCOUNTS FA at Cost is assumed to be a function of Sales Does Smucker have excess production capacity? J.M. Smucker Co. 4.20 Analysis of Sales/Fixed Assets at Cost 4.00 3.80 Sales/FA 3.60 3.40 3.20 3.00 1986 1987 1988 1989 1990 1991 1992 Does this imply excess capacity? Benninga/Sarig, slides for Chapter 7 page 6 J.M. Smucker Co. Analysis of Depreciation Charges, 1987-91 1988 Annual depreciation charges Depreciable fixed assets Total fixed assets Depreciable vs. total fixed assets Annual depreciation rate 8,018 82,846 88,036 94.1% 9.7% 1989 9,679 95,389 102,484 93.1% 10.1% 1990 10,452 112,693 122,168 92.2% 9.3% 1991 10,772 126,249 136,722 92.3% 8.5% 1992 11,309 138,894 150,879 92.1% 8.1% • On the basis of this table we assume that: o Land = 8% of fixed assets o Reported depreciation = 8% of FA; i.e, average depreciable life = 12 years • Assume that asset retirement (in 1991 = 3,148) grows at nominal rate of sales growth This gives the following table: Land PP&E net depreciable assets Accumulated depreciation This year's depreciation Retirement of assets Total depreciation 1992 11,985 138,894 1993 13,240 153,435 (11,693) 3,478 (70,772) 82,664 1994 14,769 171,155 (12,984) 3,879 (79,876) 91,279 1995 16,055 186,065 (14,289) 4,217 (89,948) 96,117 1996 17,003 197,043 (15,324) 4,466 (100,806) 96,237 Net PP&E 3,148 (62,556) 76,338 Benninga/Sarig, slides for Chapter 7 page 7 Smucker's financial policy: No new debt Smucker's dividend policy: Payout ratio = 1/3 ==> Cash & Cash Equivalents is B/S plug. End of year: Taxes payable = 25% of annual tax expenses SMUCKER (J.M.) CO. BALANCE SHEET FYE ending ASSETS Cash and cash equivalents Trade receivables, net Finished products Other current assets Land and land improvements Buildings and fixtures Machinery and equipment Construction in progress Accumulated depreciation April 92 36,268 41,565 34,604 5,961 11,985 47,191 88,781 2,922 (62,556) (62,556) Accumulated depreciation Initial Balances for pro forma 36,268 Cash & S.T. investments 41,565 Accounts receivable 77,777 Inventories--sum of Finished Products & Raw materials, etc. 5,961 11,985 Land 138,894 Net depreciable asssets new name R a w m a t e r i a l s , c o n t a i n e r s a n d s u p p l i4s , 1 7 3 e3 Intangible assets Amounts due from ESOP Other assets Total assets LIABILITIES Accounts payable 20,961 11,103 6,913 288,871 27,874 Other non-current assets--sum of Intangible assets & other assets eliminated 277,768 28,363 42,741 Accounts payable--sum of A/P, Salaries, and Accrued Marketing 2,249 3,101 5,469 3,827 6,692 1,474 7,384 7,034 209,586 Income taxes payable Dividends payable Other current liabilities Long-term debt Deferred income taxes Other liabilities Common stock Additional capital Retained income Currency translation, minus ESOP loans 277,768 S a l a r i e s , w a g e s a n d a d d i t i o n a l c o m p e n7,934 n satio Accrued marketing and merchandising 6,444 Income taxes Dividends payable Current portion of long-term debt Other current liabilities Long-term debt Deferred federal income taxes Other liabilities Class A common Class B common Additional capital Retained income Less: deferred compensation Currency translation adjustment 2249 3,101 0 5,469 3,827 6,692 1,474 3,692 3,692 7,034 209,586 (947) 261 (11,789) Miscellaneous adjustments--sum of Deferred compensation and Total liabilities and shareholder equity8 , 8 7 1 28 Benninga/Sarig, slides for Chapter 7 page 8 J.M. SMUCKER CO., Financial Statement Model, Parameters Parameter estimates for Pro Forma 1992 Sales growth (COGS+SG&A - Depreciation) to Sales Accounts Receivable to Sales Accounts Payable to Sales Inventory to Sales Fixed asset growth, before asset retirement Return on financial investments Interest on debt Dividend payout Income tax payable to income taxes Dividends payable to dividends Effective tax rate Depreciable Fixed Assets as % of Total Fixed Assets Depreciation rate Asset retirement ($K) Debt retirement 1993 10.47% 86.50% 8.60% 9.00% 16.00% 10.47% 5.00% 8.00% 33.00% 25.00% 25.00% 40.00% 92.00% 8.00% 3,478 440 1994 11.55% 86.50% 8.33% 9.00% 15.50% 11.55% 5.00% 8.00% 33.00% 25.00% 25.00% 42.00% 92.00% 8.00% 3,879 440 1995 8.71% 86.50% 8.33% 9.00% 15.00% 8.71% 5.00% 8.00% 33.00% 25.00% 25.00% 42.00% 92.00% 8.00% 4,217 430 1996 5.90% 86.50% 8.33% 9.00% 15.00% 5.90% 5.00% 8.00% 33.00% 25.00% 25.00% 42.00% 92.00% 8.00% 4,466 430 1997 5.46% 86.50% 8.33% 9.00% 15.00% 5.46% 5.00% 8.00% 33.00% 25.00% 25.00% 42.00% 92.00% 8.00% 4,710 27 1998+ 5.46% 86.50% 8.33% 9.00% 15.00% 5.46% 5.00% 8.00% 33.00% 25.00% 25.00% 42.00% 92.00% 8.00% 5.56% 20.00% 3,148 “Income tax payable to income tax” and “Dividends payable to dividends” represent the percentage of income tax and dividends (respectively) not paid by the end of the reporting year, which appear on the balance sheet of Smucker. Benninga/Sarig, slides for Chapter 7 page 9 J.M. SMUCKER CO., financial statement model Income statement 1992 Sales COGS + SG&A (excluding depreciation) Gross profit Depreciation expense Operating income Interest income Interest expense Income before taxes Income taxes Net income Dividend Increase in retained earnings Earnings per share Balance Sheet Assets Cash & short term investments Accounts receivable Inventories Other current assets Land PP&E net depreciable assets Accumulated depreciation This year's depreciation Retirement of assets Total depreciation 1993 534,089 (461,987) 72,102 (11,693) 60,409 1,813 (306) 61,916 (24,766) 37,150 (12,259) 24,890 1.26 1994 595,769 (515,340) 80,429 (12,984) 67,445 2,516 (271) 69,691 (29,270) 40,421 (13,339) 27,082 1.37 1995 647,666 (560,231) 87,435 (14,289) 73,146 3,158 (236) 76,069 (31,949) 44,120 (14,560) 29,560 1.50 1996 685,882 (593,288) 92,594 (15,324) 77,270 4,134 (201) 81,203 (34,105) 47,098 (15,542) 31,555 1.60 1997 723,304 (625,658) 97,646 (16,193) 81,453 5,403 (167) 86,688 (36,409) 50,279 (16,592) 33,687 1.70 483,472 1992 36,268 41,565 77,777 5,961 11,985 138,894 1993 50,327 45,932 85,454 5,961 13,240 153,435 (11,693) 3,478 (70,772) 82,664 27,874 311,452 1994 63,166 49,647 92,344 5,961 14,769 171,155 (12,984) 3,879 (79,876) 91,279 27,874 345,040 1995 82,687 53,972 97,150 5,961 16,055 186,065 (14,289) 4,217 (89,948) 96,117 27,874 379,816 1996 108,052 57,157 102,882 5,961 17,003 197,043 (15,324) 4,466 (100,806) 96,237 27,874 415,166 1997 136,992 60,275 108,496 5,961 17,930 207,794 (16,193) 4,710 (112,290) 95,504 27,874 453,032 Net PP&E Other non-current assets Total Assets 3,148 (62,556) 76,338 27,874 277,768 Liabilities and Equity Accounts payable Income taxes payable Dividends payable Other current liabilities Long-term debt Deferred income taxes Other long-term liabilities Common stock Additional capital Retained income Misc. adjustments Total Liabilities and Equity 1992 42,741 2,249 3,101 5,469 3,827 6,692 1,474 7,384 7,034 209,586 (11,789) 277,768 1993 48,068 6,192 3,065 5,469 3,387 6,692 1,474 7,384 7,034 234,476 (11,789) 311,452 1994 53,619 7,318 3,335 5,469 2,947 6,692 1,474 7,384 7,034 261,558 (11,789) 345,040 1995 58,290 7,987 3,640 5,469 2,517 6,692 1,474 7,384 7,034 291,118 (11,789) 379,816 1996 61,729 8,526 3,886 5,469 2,087 6,692 1,474 7,384 7,034 322,674 (11,789) 415,166 1997 65,097 9,102 4,148 5,469 2,060 6,692 1,474 7,384 7,034 356,361 (11,789) 453,032 Benninga/Sarig, slides for Chapter 7 page 10 J.M. SMUCKER CO., financial statement model, Pro Forma Cash Flow Statement Cash-flow statement Operating activities Net income Depreciation Changes in Accounts Receivable Changes in Inventories Changes in Accounts Payable Changes in taxes payable Cash Flow from Operations (CFO) Investing activities Land PP&E Net cash from investment activity Financing activities Increase in debt Dividends paid Net cash from financing activity Net increase in cash Cash at beginning of year Cash at end of year Free cash flow Net cash from operations Net cash from investing activity After-tax interest expense After-tax interest income Free cash flow 1992 1993 37,150 11,693 (4,367) (7,677) 5,327 3,943 46,069 1994 40,421 12,984 (3,716) (6,890) 5,551 1,126 49,476 1995 44,120 14,289 (4,325) (4,806) 4,671 670 54,619 1996 47,098 15,324 (3,185) (5,732) 3,439 539 57,483 1997 50,279 16,193 (3,118) (5,613) 3,368 576 61,685 (1,255) (18,019) (19,274) (1,529) (21,599) (23,128) (1,287) (19,127) (20,413) (947) (15,445) (16,392) (928) (15,460) (16,388) (440) (12,296) (12,736) 14,059 36,268 50,327 (440) (13,069) (13,509) 12,839 50,327 63,166 (430) (14,254) (14,684) 19,521 63,166 82,687 (430) (15,297) (15,727) 25,365 82,687 108,052 (27) (16,330) (16,357) 28,940 108,052 136,992 46,069 (19,274) 184 (1,088) 25,890 49,476 (23,128) 157 (1,459) 25,045 54,619 (20,413) 137 (1,832) 32,510 57,483 (16,392) 117 (2,398) 38,810 61,685 (16,388) 97 (3,134) 42,260 Benninga/Sarig, slides for Chapter 7 page 11 TERMINAL VALUE PROJECTION From Gordon Model: TerminalValue = Last Projected FCF * (1 + terminal Sales growth) WACC − growth Projecting FCFs (5.46% is the long-run nominal growth): Terminal Value = 42,260*(1 + 5.46% ) 44,567 = WACC − 5.46% WACC − 5.46% From CAPM: WACC = r f + βasset E (rm ) − rf [ ] In Chapter 8 we will see that rf should be (1-tc)*rfdebt. Benninga/Sarig, slides for Chapter 7 page 12 This gives: A 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 B C D E F G Value--discounting FCF + terminal value Beta Rf *(1-tc) E(Rm) RADR for FCF FCF growth, after year 5 Terminal FCF Terminal value, Gordon Model 0.9 0.03 0.12 11.10% <-- =B134+B133*(B135-B134) 5.46% 42,260 789,616 <-- =B139*(1+B138)/(B136-B138) Valuation based on FCF discounting and terminal value FCF terminal value TOTAL Value (full-year) Value (half-year) Add back initial cash Value 1993 25,890 1994 25,045 1995 32,510 32,510 1996 38,810 38,810 1997 42,260 789,616 831,876 25,890 25,045 584,236 <-- =NPV(B136,C146:G146) 615,808 <-- =B147*(1+B136)^0.5 36,268 652,076 VALUING SMUCKER'S SECURITIES • Debt value = Book value = $3.8 million. This leaves value of remaining securities (warrants plus shares) = $648.276 million. • Valuing Smucker's options: o Smucker has 1,000,000 warrants with average exercise price of $16 o Simple warrant value model: W = S - 16, where S = share price. o This means: $648,276,000 = 29,500,000 * S + 1,000,000 * ( S − 16) ⇒ S = $21.78 W = $5.78 Benninga/Sarig, slides for Chapter 7 page 13 SENSITIVITY ANALYSIS Sensitivity of Stock Price to Sales Growth 25.00 Stock Price 24.00 23.00 22.00 21.00 20.00 19.00 0.8 0.9 1 1.1 1.2 1.3 1.4 1.5 Increase/decrease over initial estimates ("1" indicates base model) B 191 192 193 194 195 196 197 198 199 200 201 202 203 204 C D E F G H I Terminal Growth Rate --> 0% 1% 2% 3% 4% 5% 6% 7% 8% 9% 10% Stock Price as Function of Terminal Growth Rate and WACC WACC 8% 9% 10% 11% 18.22 16.31 14.79 13.54 20.11 17.73 15.89 14.42 22.63 19.56 17.27 15.49 26.16 22.00 19.04 16.82 31.45 25.42 21.40 18.54 40.27 30.54 24.71 20.83 57.91 39.08 29.68 24.03 110.83 56.16 37.95 28.84 nmf 107.40 54.49 36.86 nmf nmf 104.12 52.88 nmf nmf nmf 100.96 12% 12.51 13.21 14.06 15.10 16.39 18.06 20.27 23.38 28.04 35.81 51.34 13% 11.64 12.21 12.90 13.72 14.72 15.98 17.59 19.74 22.75 27.27 34.80 Benninga/Sarig, slides for Chapter 7 page 14