A E G H J K Chapter 12. Build a Model Cumberland Industries' financial planners must forecast the company's financial results for the coming year. The forecast for many items will be based on sales, and any additional funds needed will be obtained as notes payable. a. Assuming the historical trend continues, what will sales be in 2017? Base your forecast on a trend analysis of the 2011-2016 sales data below. By what percentage are sales predicted to increase in 2017 over 2016? Is the sales growth rate increasing or decreasing? (Hint: Use the Trend function to forecast sales for 2017) 2016 Sales 2017 Sales % increase Year Sales Growth Rates 2011 129,215,000 2012 180,901,000 2013 235,252,000 2014 294,065,000 2015 396,692,000 2016 455,150,000 2017 455,150,000 A B C E F G H 7 b. Cumberland's management believes that the firm will actually experience a 20 percent increase in sales during 2017. Construct 2017 pro 8 forma financial statements. Cumberland will not issue any new stock or long-term bonds. Assume Cumberland will carry forward its current 9 amounts of short-term investments and notes payable, prior to calculating AFN. Assume that any Additional Funds Needed (AFN) will be raised O as notes payable (if AFN is negative, Cumberland will purchase additional short-term investments). Use an interest rate of 9 percent for short- 1 term debt (and for the interest income on short-term investments) and a rate of 11 percent for long-term debt. No interest is earned on cash. Use 2 the beginning of year debt balances to calculate net interest expense. Assume that dividends grow at an 8 percent rate. 3 4 Key Input Data: 5 6 Tax rate 7 Dividend growth rate 8 S-Tra 9 L-Tra 0 Used in the forecast 40% 8% 9% 11% 1 December 31 Income Statements (in thousands of dollars) 2 3 4 2016 Forecasting basis 2016 Ratios 2017 2017 Inputs Forecast 5 Sales $455,150 Growth 20.00% 6 Expenses (excluding depr. & amort.) $386,878 % of sales 85.00% 85.00% 7 EBITDA $68,273 8 Depreciation and Amortization $7,388 % of fixed assets 11.00% 11.00% 9 EBIT $60,885 0 Net Interest Expense 1 EBT 2 Taxes (40%) 3 Net Income 4 Common dividends $8,575 Interest rate x beginning of year debt $52,310 $20,924 $31,386 $12,554 Growth 8.00% J K L M A Common dividends B Addition to retained earnings (ARE) December 31 Balance Sheets (in thousands of dollars) D E F G H J K M $12,554 $18,832 Growth 8.00% 2016 Forecasting basis 2016 Ratios 2017 2017 Inputs Without AFN AFN With AFN Assets: Cash and cash equivalents $91,450 % of sales 20.092% 20.092% Short-term investments $11,400 Previous Accounts Receivable $103,365 % of sales 22.710% Inventories $38,444 % of sales 8.446% 22.710% 8.446% Total current assets $244,659 Fixed assets $67,165 % of sales 14.757% 14.757% Total assets $311,824 Liabilities and equity Accounts payable $30,761 % of sales 6.758% 6.758% $0 Accruals $30,477 % of sales 6.696% 6.696% $0 Notes payable $16,717 Previous $0 Total current liabilities $77,955 $0 Long-term debt $76,264 Previous $0 Total liabilities $154,219 $0 Common stock $100,000 Previous $0 Retained Earnings Total common equity Total liabilities and equity $57,605 Previous + ARE $157,605 $311,824 $0 $0 $0 A Total common equity B C D E $157,605 Total liabilities and equity Required assets Specified sources of financing: Additional funds needed (AFN) = = Required additional notes payable = Additional short-term investments = $311,824 LL F G H | J K L M $0 $0 c. Now create a graph depicting the sensitivity of AFN for the coming year to the sales growth rate. To make this graph, compare the AFN at sales growth rates of 5%, 10%, 15%, 20%, 25%, and 30%. (Hint: Use the Data Table function in Excel) Sales Growth rate 2017 AFN $0 5% 10% 15% 20% 25% 30% Additional Funds Needed $1 $1 $1 $1 $0 $0 $0 Chart Area 5% AFN Sensitivity to Growth Rates 10% 15% 20% 25% 30% Growth Rate A B C D E G H 11 d. Calculate the Net Operating Working Capital (NOWC), Total Operating Capital, and NOPAT for 2016 and 2017. Also, calculate the FCF 12 for 2017. 13 4 Net Operating Working Capitul 15 16 NOWC1= Operating CA Operating CL 17 18 19 20 NOWC Operating CA 21 22 23 24 Total Operating Capital 25 26 TOC - Operating CL NOWC + Fixed assets + 27 28 29 30 TOC NOWC + Fixed assets 81 + 32 83 34 Net Operating Profit After Taxes 35 36 NOPAT16 EBIT 87 x (1-T) Chart Area x 38 39 40 NOPATI- EBIT x (1-T) 41 12 43 14 Free Cash Flow 15 16 FCF 47 48 NOPAT Increase in TOC J K M N P