4 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 $ 800.0 11 Net Sales 12 Costs (except depreciation) 576.0 13 Depreciation $ 60.0 14 Earning before int. & tax $ 164.0 15 Less interest $ 32.0 16 Earning before taxes $ 132.0 17 Taxes (25%) $ 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) 25 Dividends per share 26 Tax rate 10 $ 3.00 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 $ 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 Total liabilities and equi $ 868.0 40 40 41 Projected ratios and selected information for the current and projected years are shown below. 42 Actual Projected Projected 43 Inputs 12/31/2019 12/31/20 12/31/21 Projected Projected 12/31/22 12/31/23 44 15% 10% 6% 6% 45 Sales Growth Rate 46 Costs/Sales 72% 72% 72% 72% 72% 47 Depreciation/(Net PPE) 10% 10% 10% 10% 10% 48 Cash/Sales 1% 1% 1% 1% 1% 49 (Acct. Rec.)/Sales 10% 10% 10% 10% 10% 50 Inventories/Sales 20% 20% 20% 20% 20% 51 (Net PPE)/Sales 75% 75% 75% 75% 75% 52 (Acct. Pay.)/Sales 2% 2% 2% 2% 2% 53 Accruals/Sales 5% 5% 5% 5% 5% 54 Tax rate 40% 40% 40% 40% 40% 55 Weighted average cost of capital (WACC) 10.5% 10.5% 10.5% 10.5% 10.5% 56 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 63 Costs (except depreciation) 64 Depreciation 65 Earning before int. & tax 66 12/31/2019 12/31/20 $800.0 $576.0 $60.0 $164.0 67 Partial Balance Sheets for December 31 (Millions of Dollars) 12/31/21 Projected Projected 12/31/22 12/31/23 68 Actual Projected 69 Operating Assets 12/31/2019 12/31/20 Projected 12/31/21 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 $16.0 $40.0 Projected Projected 12/31/22 12/31/23 78 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 Projected Projected 12/31/21 12/31/2019 12/31/20 12/31/22 12/31/23 90 Investment in total net operating capital na 91 Free cash flow na 92 Growth in FCF na na 93 Growth in sales 94 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]?) 101 102 103 104 Actual Projected Projected 12/31/2019 12/31/20 12/31/21 Projected Projected 12/31/22 12/31/23 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 10.5% 10.5% 10.5% 10.5% 10.5% na na na na na na 112 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 10.5% Actual Projected Projected Projected Projected 12/31/22 12/31/2019 12/31/20 12/31/21 12/31/23 133 134 135 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