Show Excel Formulas

Show Formulas

1 5 2 See below for Henley Corporation's most recent financial statements. Use the following ratios and other selected 3 information for the current and projected years to answer the following questions (fill in the blanks in the highlighted 4 areas) 6 Income Statement for the Year Ending December 31 (Millions of Dollars) 7 2018 8 Net Sales $ 800.0 9 Costs (except depreciation) $ 576.0 10 Depreciation $ 60.0 11 Total operating costs $ 636.0 12 Eaming before int. & tax $ 164.0 13 Less interest $ 32.0 14 Eaming before taxes $ 132.0 15 Taxes (40%) $ 52.8 16 Not income before prof. div. $ 79.2 17 Preferred div. 1.4 18 Net income avail. for com. div. $ 77.9 19 Common dividends $ 20 Addition to retained earnings $ 46.7 21 22 Number of shares (in millions) 10 23 Dividends per share $ 3.11 24 $ 31.1 $ 2018 16.0 40.0 40.0 96.0 300.0 15.0 $ $ $ 25 Balance Sheets for December 31 (Millions of dollars) 26 Assets 2018 Liabilities and Equity 27 Cash $ 8.0 Accounts Payable 28 Short-term investments 20.0 Notes payable 29 Accounts receivable 80.0 Accruals 30 Inventories 160.0 Total current liabilities 31 Total current assets $ 268.0 Long-term bonds 32 Net plant and equipment 600.0 Preferred stock CURITIUIT OKULA 33 Total Assets $ 868.0 (Par plus PIC) 34 Retained earings 35 Common equity 36 Total liabilities and equity 37 38 Projected ratios and selected information for the current and projected years are shown below. 39 40 Inputs Actual Projected Projected Projected 41 12/31/18 12/31/19 12/31/20 12/31/21 $ 257.0 200.0 457.0 868.0 $ $ Projected 12/31/22 Build a Model JX 56 F F A Projected 12/31/22 6% 72% 10% 1% 10% 20% 75% 2% 5% 40% 10.5% B D E 8 Projected ratios and selected information for the current and projected years are shown below. 39 10 Inputs Actual Projected Projected Projected 41 12/31/18 12/31/19 12/31/20 12/31/21 15% 42 Sales Growth Rate 10% 6% 43 Costs/Sales 72% 72% 72% 72% 44 Depreciation/(Not PPE) 10% 10% 10% 10% 45 Cash/Sales 1% 1% 1% 1% 46 (Acct. Rec.)/Sales 10% 10% 10% 10% 47 Inventories/Sales 20% 20% 20% 20% 48 (Net PPE)/Sales 70% 75% 75% 75% 49 (Acct. Pay.)/Sales 2% 2% 2% 2% 50 Accruals/Sales 5% 5% 5% 5% 51 Tax rate 40% 40% 40% 40% 52 Weighted average cost of capital (WACC) 10.5% 10.5% 10.5% 10.5% 53 54 a. Forecast the parts of the income statement and balance sheets necessary to calculate free cash flow. 55 56 Partial Income Statement for the Year Ending December 31 (Millions of Dollars) 57 Actual Projected Projected Projected 58 Income Statement Items 12/31/18 12/31/19 12/31/20 12/31/21 59 Net Sales $800.0 60 Costs (except depreciation) $576.0 61 Depreciation $60.0 62 Total operating costs $636.0 63 Earning before int. & tax $164.0 64 65 Partial Balance Sheets for December 31 (Millions of Dollars) 66 Actual Projected Projected Projected 87 Operating Assets 12/31/18 12/31/19 12/31/20 12/31/21 68 Cash $8.0 69 Accounts receivable $80.0 70 Inventories $160.0 71 Net plant and equipment $600.0 72 73 Operating Liabilities 74 Accounts Payable $16.0 75 Accruals $40.0 76 Projected 12/31/22 Projected 12/31/22 77 b. Calculate free cash flow for each projected year. Also calculate the growth rates of free cash flow each year to 78 ensure that there is constant growth (1.o., the same as the constant growth rate in sales) by the end of the forecast 79 period 80 Actual Projected Projected Projected Projected 81 Calculation of FCF 12/31/18 12/31/19 12/31/20 12/31/21 12/31/22 Build a Model Ready AutoSave ORT apo... Draw Page Layout Formulas Some Insert Data Review View Tell me Arial 10 - A A Paste Wrap Text Merge & Center Xfx 636 G 12/3119 Calculate free cash flow for each projected year Aise calculate the growth rates of the cash fow each years ensure that there is constant growth the same constant while by the end of the forecast period Actual Projected Projected Projected Project Calculation et FCF 12/31110 12/3120 123121 12:22 Operating current Opening current les Net operating working capital Net PPE Toalet perling capital NOPAT Investment in total reteersing Epital cash flow Grown FC Grein ne e Calculate the current value of First see how the end of the opere which is out the value of the end of the cared. Assume that the whole beyond the horizon is equal to the growth rate of the horion) Projects Prophed 2012 Weighted average coloca 10.5% Actual Project Project 12/31 12/31211 23120 Long term constant growth in FCF Harian 04 Present value of the 02 Preset value of forecasted FC e Value of operation Pow.Pof FCF) 110 Total met sporting capital 11 Caleate the price thereof December 31, 2018 Aca Value of ne Valsts Tommy -Tale of all - Valfred View by shares Share Buda Model 1 5 2 See below for Henley Corporation's most recent financial statements. Use the following ratios and other selected 3 information for the current and projected years to answer the following questions (fill in the blanks in the highlighted 4 areas) 6 Income Statement for the Year Ending December 31 (Millions of Dollars) 7 2018 8 Net Sales $ 800.0 9 Costs (except depreciation) $ 576.0 10 Depreciation $ 60.0 11 Total operating costs $ 636.0 12 Eaming before int. & tax $ 164.0 13 Less interest $ 32.0 14 Eaming before taxes $ 132.0 15 Taxes (40%) $ 52.8 16 Not income before prof. div. $ 79.2 17 Preferred div. 1.4 18 Net income avail. for com. div. $ 77.9 19 Common dividends $ 20 Addition to retained earnings $ 46.7 21 22 Number of shares (in millions) 10 23 Dividends per share $ 3.11 24 $ 31.1 $ 2018 16.0 40.0 40.0 96.0 300.0 15.0 $ $ $ 25 Balance Sheets for December 31 (Millions of dollars) 26 Assets 2018 Liabilities and Equity 27 Cash $ 8.0 Accounts Payable 28 Short-term investments 20.0 Notes payable 29 Accounts receivable 80.0 Accruals 30 Inventories 160.0 Total current liabilities 31 Total current assets $ 268.0 Long-term bonds 32 Net plant and equipment 600.0 Preferred stock CURITIUIT OKULA 33 Total Assets $ 868.0 (Par plus PIC) 34 Retained earings 35 Common equity 36 Total liabilities and equity 37 38 Projected ratios and selected information for the current and projected years are shown below. 39 40 Inputs Actual Projected Projected Projected 41 12/31/18 12/31/19 12/31/20 12/31/21 $ 257.0 200.0 457.0 868.0 $ $ Projected 12/31/22 Build a Model JX 56 F F A Projected 12/31/22 6% 72% 10% 1% 10% 20% 75% 2% 5% 40% 10.5% B D E 8 Projected ratios and selected information for the current and projected years are shown below. 39 10 Inputs Actual Projected Projected Projected 41 12/31/18 12/31/19 12/31/20 12/31/21 15% 42 Sales Growth Rate 10% 6% 43 Costs/Sales 72% 72% 72% 72% 44 Depreciation/(Not PPE) 10% 10% 10% 10% 45 Cash/Sales 1% 1% 1% 1% 46 (Acct. Rec.)/Sales 10% 10% 10% 10% 47 Inventories/Sales 20% 20% 20% 20% 48 (Net PPE)/Sales 70% 75% 75% 75% 49 (Acct. Pay.)/Sales 2% 2% 2% 2% 50 Accruals/Sales 5% 5% 5% 5% 51 Tax rate 40% 40% 40% 40% 52 Weighted average cost of capital (WACC) 10.5% 10.5% 10.5% 10.5% 53 54 a. Forecast the parts of the income statement and balance sheets necessary to calculate free cash flow. 55 56 Partial Income Statement for the Year Ending December 31 (Millions of Dollars) 57 Actual Projected Projected Projected 58 Income Statement Items 12/31/18 12/31/19 12/31/20 12/31/21 59 Net Sales $800.0 60 Costs (except depreciation) $576.0 61 Depreciation $60.0 62 Total operating costs $636.0 63 Earning before int. & tax $164.0 64 65 Partial Balance Sheets for December 31 (Millions of Dollars) 66 Actual Projected Projected Projected 87 Operating Assets 12/31/18 12/31/19 12/31/20 12/31/21 68 Cash $8.0 69 Accounts receivable $80.0 70 Inventories $160.0 71 Net plant and equipment $600.0 72 73 Operating Liabilities 74 Accounts Payable $16.0 75 Accruals $40.0 76 Projected 12/31/22 Projected 12/31/22 77 b. Calculate free cash flow for each projected year. Also calculate the growth rates of free cash flow each year to 78 ensure that there is constant growth (1.o., the same as the constant growth rate in sales) by the end of the forecast 79 period 80 Actual Projected Projected Projected Projected 81 Calculation of FCF 12/31/18 12/31/19 12/31/20 12/31/21 12/31/22 Build a Model Ready AutoSave ORT apo... Draw Page Layout Formulas Some Insert Data Review View Tell me Arial 10 - A A Paste Wrap Text Merge & Center Xfx 636 G 12/3119 Calculate free cash flow for each projected year Aise calculate the growth rates of the cash fow each years ensure that there is constant growth the same constant while by the end of the forecast period Actual Projected Projected Projected Project Calculation et FCF 12/31110 12/3120 123121 12:22 Operating current Opening current les Net operating working capital Net PPE Toalet perling capital NOPAT Investment in total reteersing Epital cash flow Grown FC Grein ne e Calculate the current value of First see how the end of the opere which is out the value of the end of the cared. Assume that the whole beyond the horizon is equal to the growth rate of the horion) Projects Prophed 2012 Weighted average coloca 10.5% Actual Project Project 12/31 12/31211 23120 Long term constant growth in FCF Harian 04 Present value of the 02 Preset value of forecasted FC e Value of operation Pow.Pof FCF) 110 Total met sporting capital 11 Caleate the price thereof December 31, 2018 Aca Value of ne Valsts Tommy -Tale of all - Valfred View by shares Share Buda Model