Copy and paste your original model (from task 1) into a separate tab in the same excel file and run the following sensitivity analysis:
Cost of equity increases to 15%. Explain you finding with respect to the new share price.
3 B D E Best Buy Year 2 Year 1 F BALANCE SHEET Cash Receivables Inventories Other Total current assets G Year 2 746 313 1,767 102 2,928 H Year 1 751 262 1,184 41 2,238 Income statement Net sales Cost of goods Gross profit Selling general & administrative Depreciation & amortization expense Income before tax Income tax expense Net income Outstanding shares 15,326 12,494 12.267 10.101 3,059 2,393 2.251 1,728 167 103 641 562 245 215 396 347 208 200 Property, plant & equipment Accumulated depreciation Net property & equipment Other assets Total assets 1,987 543 1,444 466 4,838 1,093 395 698 59 2,995 RATIOS Sales growth Gross Profit Margin Selling General & Administrative Exp DEPRECIATION (depn exp / pr yr PPE gross) Tax (Inc Tax / Pre-tax inc) Accounts payable & accrued liabilities Short-term debt & cmltd Income taxes Total current liab 2,473 114 127 2,714 1,704 16 65 785 22.67% 19.967 14.697. 15.28% 38.22% Long term liabilities Long term debt Total long-term liabilities 122 181 303 100 15 115 20 576 1.225 20 247 828 Common stock Capital surplus Retained earnings OTHER EQUITIES Shareholder equity Total liabilities & net worth 1.821 4,838 1.095 2,995 RATIOS AR turn INV turn AP turn Tax Pay (Tax pay /tax exp) FLEV Div/sh 48.96 47.69 6.94 8.53 4.96 5.93 51.84% 30.237. 2.66 2.74 $0.00 $0.00 CAPEX CAPEX/Sales 1029 6.717 416 3.33% 3 B D E Best Buy Year 2 Year 1 F BALANCE SHEET Cash Receivables Inventories Other Total current assets G Year 2 746 313 1,767 102 2,928 H Year 1 751 262 1,184 41 2,238 Income statement Net sales Cost of goods Gross profit Selling general & administrative Depreciation & amortization expense Income before tax Income tax expense Net income Outstanding shares 15,326 12,494 12.267 10.101 3,059 2,393 2.251 1,728 167 103 641 562 245 215 396 347 208 200 Property, plant & equipment Accumulated depreciation Net property & equipment Other assets Total assets 1,987 543 1,444 466 4,838 1,093 395 698 59 2,995 RATIOS Sales growth Gross Profit Margin Selling General & Administrative Exp DEPRECIATION (depn exp / pr yr PPE gross) Tax (Inc Tax / Pre-tax inc) Accounts payable & accrued liabilities Short-term debt & cmltd Income taxes Total current liab 2,473 114 127 2,714 1,704 16 65 785 22.67% 19.967 14.697. 15.28% 38.22% Long term liabilities Long term debt Total long-term liabilities 122 181 303 100 15 115 20 576 1.225 20 247 828 Common stock Capital surplus Retained earnings OTHER EQUITIES Shareholder equity Total liabilities & net worth 1.821 4,838 1.095 2,995 RATIOS AR turn INV turn AP turn Tax Pay (Tax pay /tax exp) FLEV Div/sh 48.96 47.69 6.94 8.53 4.96 5.93 51.84% 30.237. 2.66 2.74 $0.00 $0.00 CAPEX CAPEX/Sales 1029 6.717 416 3.33%
