Enter and show the valid formulas for the quantitative calculations, not just hard number only a. Forecast the parts of the income statement and balance sheets necessary to calculate free 800.0 576 11 Net Sales 12 Costs (except depreciation) 13 Depreciation 14 Earning before int. & tas 15 Less interest 16 Earning before tases 17 Takes 1257) 18 Net income before pref. div. Preferred div. 20 Net Income avail for com div. Common dividends 22 Addition to retained earnings 59 Partial Income Statement for the Year Ending December 31 (Millions of Dollars) Actual Projected Projected Projected Projected 61 Income Statement Items 12/31/2019 12/3120 12/3121 12/3122 12/31/23 62 Net Sales $800.0 63 Costs (except depreciation) $576.0 64 Depreciation $60.0 65 Earning before int. & tax $164.0 Number of shares (in millions) Dividends per share 3 00 67 Partial Balance Sheets for December 31 (Millions of Dollars) Actual Projected Projected Projected Projected 69 Operating Assets 12/31/2019 12/31/20 12/31/21 12731122 12131123 70 Cash $8.0 71 Accounts receivable $80.0 72 Inventories $160.0 73 Net plant and equipment $600.0 20 Balance Sheets for December 31 (Millions of Dollars) 29 Assets Liabilities and Equity Accounts Payable 31 Short-term investments Notes pagable 40.0 32 Accounts receivable 80.0 Accruals 40.0 33 Inventories 160.0 Total current liabili 34 Total current assets 2680 Long-term bonds + 300.0 35 Net plant and equipment 600.0 Preferred stock 100.0 Common Stock 36 Total Assets B68. 257.0 Retained earnings 211.0 Common equity $ 468.0 Total liabilities and $868.0 40 41 Projected ratios and selected information for the current and projected gears are shown below. 75 Operating Liabilities 76 Accounts Payable 77 Accruals $16.0 $40.0 43 Inputs 15% 10% 10% 79 D. Calculate free cash flow for each projected year. Also calculate the growth rates of free 80 cash flow each year to ensure that there is constant growth i.e., the same as the constant 81 growth rate in sales) by the end of the forecast period. Actual Projected Projected Projected Projected 83 Calculation of FCF 12/31/2013 12/31/20 12/31/21 12/31/22 12/3123 84 Operating current assets 85 Operating current liabilities 88 Net operating working capital 87 Net PPE 88 Total net operating capital 89 NOPAT 90 Investment in total net operating capital na 91 Free cash flow 92 Growth in FCF Growth in sales 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 (Acet. Pag.)/Sales 53 Accruals Sales 54 Tax rate 55 Veighted average cost of capital (VACC Actual Projected Projected Projected Projected 12/31/2013 12/31120 12131121 12/31/22 12/31/23 62 6% 72% 72% 72% 722 72% 10% 10% 10% 10% 1% 1096 10% 1024 20% 20% 20% 202 75% 75% 75% 75% 2% 22 224 5% 40% 40% 40% 40% 40% 10 5% 10.5% 10.5% 10.5% 10.52% 1024 20% 75% 2% 2x 5% 5% Enter and show the valid formulas for the quantitative calculations, not just hard number only Net Sales Costs (except depreciation) 3 Depreciation 14 | Earning before int&tas 95 . Calculate the return on invested capital (ROIC-NOPAT/Total net operating capital) and the 36 growth rate in free cash flow. What is the ROIC in the last year of the forecast? Vhat is the long- term constant growth rate in free cash flow is the growth rate in FCF in the last forecast 98 period because all ratios are constant)? Do you think that Hensley's value would inerease if it 99 could add growth without reducing KS ROIC (Mint Growth will add value of the ROIC VACCHI-VACC. Do you think that the company will have a value of operations greater than its total net operating capital? (lintis ROIC VACC/1.9.12) 16 Earning before taxes 17 Tases (25%) 18 Net income before pref. div. 18 Preferred die 20 Net income avail for com. div. 21 Common dividends Addition to retained earnings Actual Projected Projected Projected Projected 1213112013 12/31/2012/31/2112131122 1213123 Return on invested capital FROIC-NOPAT Toral net operating 105 capital] 107 Veighted average cost of capital (VACC O VACCI(19.) 109 VACCHI.VACC) 10.5% 10.5% 10.5% 10.5% 10.5% 24 Number of shares (in millions) 25 Dividends per share 28 Tarrate 28 Balance Sheets for December 31 Millions of Dollars) 29 Assets 114 d. Caleulate the current value of operations. (Hint: First calculate the horizon value at the end 115 of the forecast period, which is equal to the value of operations at the end of the forecast 116 period. Assume that the annual growth rate beyond the horizon is equal to the growth rate at the 117 horizon.) How does the current value of operations compare with the current amount of total 31 Short-term investments 32 Accounts receivable 119 Veighted average cost of capital (VACC 10.5% 34 Total current assets 15 Net plant and equipment Liabilities and Equity 2019 Accounts Payable 16.0 Notes pagable 40.0 Accruals Total current liabili $ 96.0 Long-term bonds 3000 Preferred stock $ 100,0 Common lock (Purple) $257.0 Retained earnings Common equity 468.0 Total Habilities and $ 868.0 Actual Projected Projected Projected Projected 12/31/2013 12231120 12131221 12/31/22 12/3123 38 Total Assets 123 Free cash flow 124 Long-term constant growth in FCF 126 Horizon value Projected ratios and selected information for the current and projected gears are shown below. 127 Present value of horizon value 128 Present value of forecasted FCF 129 Value of operations (IPV of HVPV of FCF 43 Inputs Actual Projected Projected Projected Projected 12/31/2019 12/21/20 12/3121 12/3122 12121222 131 Total net operating capital 136 e. Calculate the price per share of common equity as of 12/31/2019 72% 72% 72% 72 137 138 Millions except price per share 139 Actual 12/31/2019 1% 45 Sales Growth Rate 26 Costs Sales 47 Depreciation'[Net PPE) & Cash/Sales 49 (Acct. Rec.)/Sales 50 Inventories/Sales 31 Net PPEVSales 52 (Acet. Pal/Sales 53 Accruals/Sales 54 Tatrate 55 Veighted average cost of capital (VACC 20% 203 75% 20% 75% 75% 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 Value of common equity 146 Divided by number of shares 147 Price per share $ 5% 145 10.5% 10.5% 40% 105% 10.5% 10.5% Enter and show the valid formulas for the quantitative calculations, not just hard number only a. Forecast the parts of the income statement and balance sheets necessary to calculate free 800.0 576 11 Net Sales 12 Costs (except depreciation) 13 Depreciation 14 Earning before int. & tas 15 Less interest 16 Earning before tases 17 Takes 1257) 18 Net income before pref. div. Preferred div. 20 Net Income avail for com div. Common dividends 22 Addition to retained earnings 59 Partial Income Statement for the Year Ending December 31 (Millions of Dollars) Actual Projected Projected Projected Projected 61 Income Statement Items 12/31/2019 12/3120 12/3121 12/3122 12/31/23 62 Net Sales $800.0 63 Costs (except depreciation) $576.0 64 Depreciation $60.0 65 Earning before int. & tax $164.0 Number of shares (in millions) Dividends per share 3 00 67 Partial Balance Sheets for December 31 (Millions of Dollars) Actual Projected Projected Projected Projected 69 Operating Assets 12/31/2019 12/31/20 12/31/21 12731122 12131123 70 Cash $8.0 71 Accounts receivable $80.0 72 Inventories $160.0 73 Net plant and equipment $600.0 20 Balance Sheets for December 31 (Millions of Dollars) 29 Assets Liabilities and Equity Accounts Payable 31 Short-term investments Notes pagable 40.0 32 Accounts receivable 80.0 Accruals 40.0 33 Inventories 160.0 Total current liabili 34 Total current assets 2680 Long-term bonds + 300.0 35 Net plant and equipment 600.0 Preferred stock 100.0 Common Stock 36 Total Assets B68. 257.0 Retained earnings 211.0 Common equity $ 468.0 Total liabilities and $868.0 40 41 Projected ratios and selected information for the current and projected gears are shown below. 75 Operating Liabilities 76 Accounts Payable 77 Accruals $16.0 $40.0 43 Inputs 15% 10% 10% 79 D. Calculate free cash flow for each projected year. Also calculate the growth rates of free 80 cash flow each year to ensure that there is constant growth i.e., the same as the constant 81 growth rate in sales) by the end of the forecast period. Actual Projected Projected Projected Projected 83 Calculation of FCF 12/31/2013 12/31/20 12/31/21 12/31/22 12/3123 84 Operating current assets 85 Operating current liabilities 88 Net operating working capital 87 Net PPE 88 Total net operating capital 89 NOPAT 90 Investment in total net operating capital na 91 Free cash flow 92 Growth in FCF Growth in sales 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 (Acet. Pag.)/Sales 53 Accruals Sales 54 Tax rate 55 Veighted average cost of capital (VACC Actual Projected Projected Projected Projected 12/31/2013 12/31120 12131121 12/31/22 12/31/23 62 6% 72% 72% 72% 722 72% 10% 10% 10% 10% 1% 1096 10% 1024 20% 20% 20% 202 75% 75% 75% 75% 2% 22 224 5% 40% 40% 40% 40% 40% 10 5% 10.5% 10.5% 10.5% 10.52% 1024 20% 75% 2% 2x 5% 5% Enter and show the valid formulas for the quantitative calculations, not just hard number only Net Sales Costs (except depreciation) 3 Depreciation 14 | Earning before int&tas 95 . Calculate the return on invested capital (ROIC-NOPAT/Total net operating capital) and the 36 growth rate in free cash flow. What is the ROIC in the last year of the forecast? Vhat is the long- term constant growth rate in free cash flow is the growth rate in FCF in the last forecast 98 period because all ratios are constant)? Do you think that Hensley's value would inerease if it 99 could add growth without reducing KS ROIC (Mint Growth will add value of the ROIC VACCHI-VACC. Do you think that the company will have a value of operations greater than its total net operating capital? (lintis ROIC VACC/1.9.12) 16 Earning before taxes 17 Tases (25%) 18 Net income before pref. div. 18 Preferred die 20 Net income avail for com. div. 21 Common dividends Addition to retained earnings Actual Projected Projected Projected Projected 1213112013 12/31/2012/31/2112131122 1213123 Return on invested capital FROIC-NOPAT Toral net operating 105 capital] 107 Veighted average cost of capital (VACC O VACCI(19.) 109 VACCHI.VACC) 10.5% 10.5% 10.5% 10.5% 10.5% 24 Number of shares (in millions) 25 Dividends per share 28 Tarrate 28 Balance Sheets for December 31 Millions of Dollars) 29 Assets 114 d. Caleulate the current value of operations. (Hint: First calculate the horizon value at the end 115 of the forecast period, which is equal to the value of operations at the end of the forecast 116 period. Assume that the annual growth rate beyond the horizon is equal to the growth rate at the 117 horizon.) How does the current value of operations compare with the current amount of total 31 Short-term investments 32 Accounts receivable 119 Veighted average cost of capital (VACC 10.5% 34 Total current assets 15 Net plant and equipment Liabilities and Equity 2019 Accounts Payable 16.0 Notes pagable 40.0 Accruals Total current liabili $ 96.0 Long-term bonds 3000 Preferred stock $ 100,0 Common lock (Purple) $257.0 Retained earnings Common equity 468.0 Total Habilities and $ 868.0 Actual Projected Projected Projected Projected 12/31/2013 12231120 12131221 12/31/22 12/3123 38 Total Assets 123 Free cash flow 124 Long-term constant growth in FCF 126 Horizon value Projected ratios and selected information for the current and projected gears are shown below. 127 Present value of horizon value 128 Present value of forecasted FCF 129 Value of operations (IPV of HVPV of FCF 43 Inputs Actual Projected Projected Projected Projected 12/31/2019 12/21/20 12/3121 12/3122 12121222 131 Total net operating capital 136 e. Calculate the price per share of common equity as of 12/31/2019 72% 72% 72% 72 137 138 Millions except price per share 139 Actual 12/31/2019 1% 45 Sales Growth Rate 26 Costs Sales 47 Depreciation'[Net PPE) & Cash/Sales 49 (Acct. Rec.)/Sales 50 Inventories/Sales 31 Net PPEVSales 52 (Acet. Pal/Sales 53 Accruals/Sales 54 Tatrate 55 Veighted average cost of capital (VACC 20% 203 75% 20% 75% 75% 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 Value of common equity 146 Divided by number of shares 147 Price per share $ 5% 145 10.5% 10.5% 40% 105% 10.5% 10.5%