B 1 Build a Model Problem 2 Chapter: 3 Problem: 4 12 11 E G 11/26/18 5 Start with the partial model in the file Ch12 P11 Build a Model.xlsx on the textbook's Web site, which 6 contains Henley Corporation's most recent financial statements. Use the following ratios and other 7 selected information for the current and projected years to answer the next questions. 8 9 Income Statement for the Year Ending December 31 (Millions of Dollars) 2019 10 11 Net Sales $ 800.0 12 Costs (except depreciation) $ 576.0 13 Depreciation 60.0 14 Earning before int. & tax $ 164.0 15 Less interest 16 Earning before taxes 17 Taxes (25%) $ 32.0 $ 132.0 $ 33.0 18 Net income before pref. div. $ 99.0 19 Preferred div. $ 9.0 20 Net income avail. for com. div. $ 90.0 21 Common dividends $ 30.0 22 Addition to retained earnings $ 60.0 23 24 Number of shares (in millions) 10 25 Dividends per share $ 3.00 26 Tax rate 25% 27 28 Balance Sheets for December 31 (Millions of Dollars) 29 Assets 2019 Liabilities and Equity 2019 30 Cash $ 8.0 Accounts Payable $ 16.0 31 Short-term investments 20.0 Notes payable 40.0 32 Accounts receivable 80.0 Accruals 40.0 33 Inventories 160.0 Total current liabilities S 96.0 34 Total current assets $ 268.0 Long-term bonds $ 300.0 35 Net plant and equipment 600.0 Preferred stock $ 100.0 Common Stock 36 Total Assets $ 868.0 (Par plus PIC) 257.0 37 Retained earnings 211.0 38 Common equity $ 468.0 39 40 Build a Model Total liabilities and equi $ 868.0 41 Projected ratios and selected information for the current and projected years are shown below. 42 43 Inputs 44 45 Sales Growth Rate 46 Costs/Sales 47 Depreciation/(Net PPE) 48 Cash/Sales 49 (Acct. Rec.)/Sales 50 Inventories/Sales 51 (Net PPE)/Sales 52 (Acct. Pay.)/Sales 53 Accruals/Sales 54 Tax rate 55 Weighted average cost of capital (WACC) 56 Actual Projected Projected 12/31/2019 12/31/20 12/31/21 Projected Projected 12/31/22 12/31/23 15% 10% 6% 6% 72% 72% 72% 72% 72% 10% 10% 10% 10% 10% 1% 1% 1% 1% 1% 10% 10% 10% 10% 10% 20% 20% 20% 20% 20% 75% 75% 75% 75% 75% 2% 2% 2% 2% 2% 5% 5% 5% 5% 5% 40% 40% 40% 40% 40% 10.5% 10.5% 10.5% 10.5% 10.5% 57 a. Forecast the parts of the income statement and balance sheets necessary to calculate free cash flow. 58 59 Partial Income Statement for the Year Ending December 31 (Millions of Dollars) 60 Actual Projected Projected 61 Income Statement Items 62 Net Sales 12/31/2019 12/31/20 12/31/21 Projected Projected 12/31/22 12/31/23 $800.0 63 Costs (except depreciation) 64 Depreciation $576.0 $60.0 65 Earning before int. & tax $164.0 66 67 Partial Balance Sheets for December 31 (Millions of Dollars) 68 Actual Projected Projected 69 Operating Assets 12/31/2019 12/31/20 12/31/21 Projected Projected 12/31/22 12/31/23 70 Cash $8.0 71 Accounts receivable $80.0 72 Inventories $160.0 73 Net plant and equipment $600.0 74 75 Operating Liabilities 76 Accounts Payable 77 Accruals 78 79 h Calculate fron $16.0 $40.0 79 b. Calculate free cash flow for each projected year. Also calculate the growth rates of free cash flow 80 each year to ensure that there is constant growth (i.e., the same as the constant growth rate in sales) by 81 the end of the forecast period. 82 83 Calculation of FCF 84 Operating current assets 85 Operating current liabilities 86 Net operating working capital 87 Net PPE 88 Total net operating capital 89 NOPAT Actual Projected Projected 12/31/2019 12/31/20 Projected Projected 12/31/21 12/31/22 12/31/23 90 Investment in total net operating capital 91 Free cash flow. 92 Growth in FCF 93 Growth in sales 94 na na na na 95 c. Calculate the return on invested capital (ROIC-NOPAT/Total net operating capital) and the growth rate 96 in free cash flow. What is the ROIC in the last year of the forecast? What is the long-term constant growth 97 rate in free cash flow (g, is the growth rate in FCF in the last forecast period because all ratios are 98 constant)? Do you think that Hensley's value would increase if it could add growth without reducing its 99 ROIC? (Hint: Growth will add value if the ROIC > WACC/11 WACC). Do you think that the company will 100 have a value of operations greater than its total net operating capital? (Hint: Is ROIC > WACC/11+g.1?) 101 102 103 104 105 Return on invested capital (ROIC-NOPAT/[Total net operating 106 capital) 107 Weighted average cost of capital (WACC) 108 WACC/(1 g) 109 WACC/(1+WACC) 110 111 112 113 Actual Projected Projected Projected Projected 12/31/2019 12/31/20 12/31/21 12/31/22 12/31/23 10.5% 10.5% 10.5% 10.5% 10.5% na na na na na 113 114 d. Calculate the current value of operations. (Hint: First calculate the horizon value at the end of the 115 forecast period, which is equal to the value of operations at the end of the forecast period. Assume that 116 the annual growth rate beyond the horizon is equal to the growth rate at the horizon.) How does the 117 current value of operations compare with the current amount of total net operating capital? 118 119 Weighted average cost of capital (WACC) 120 121 122 123 Free cash flow 124 Long-term constant growth in FCF 125 Horizon value 126 127 Present value of horizon value 128 Present value of forecasted FCF 129 Value of operations (PV of HV] [PV of FCF) 130 131 Total net operating capital 132 133 134 135 10.5% Actual Projected 12/31/2019 12/31/20 Projected 12/31/21 Projected Projected 12/31/22 12/31/23 136 e. Calculate the price per share of common equity as of 12/31/2019 137 138 Millions except price per share 139 140 Value of operations 141 Value of short-term investments 142 Total value of company 143) Total value of all debt 144 Value of preferred stock 145 Value of common equity 146 Divided by number of shares 147 Price per share Actual 12/31/2019