Please help

Please see excel data file to assignment 3. The file includes financial information for Best Buy. Tasks: 1. Using the residual income model, prepare a valuation of the common stock of Best Buy as of Year 2 under the following assumptions (use Exhibit 9.6 in the textbook as an example): a. Forecast horizon of five years (years 3 - 7) b. Sales growth of 10% for Best Buy per year (type in 10% in each year) over the forecast period and 3.5% thereafter (terminal year). c. All financial ratios remain at Year 2 levels. In other words, you create a model as show in Exhibit 9.6. You can download the "Residual Income Computation Exhibit 9.6" spreadsheet that is posted in module 12 and update it using the BestBuy data. After you have determined the values for year 2, you use the same financial ratios for the forecast horizon. The balance sheet and income statement info should update automatically for years 3 - 7 if you keep the formulas. d. Cost of equity capital is 12.5%. All tasks should be answered using excel. You may want to copy and paste financial information into the template file to be able to link cells to the financial model. 2. Copy and paste your original model (from task 1) into a separate tab in the same excel file and run the following sensitivity analysis: Growth rate equals 1% each year during the forecast horizon (1.e. type in 1% for sales growth in years 3 - 7) and remains at 3% thereafter. Explain your finding with respect to the new share price. 3. 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 1 38.22% B CDE Best Buy BALANCE SHEET Year 2 Year 1 Cash Income statement Receivables Net sales 15,326 12,494 Inventories Cost of goods 12,267 10.101 Other Gross profit 3,059 2,393 Total current assets Selling general & administrative expense 2,251 1,728 Depreciation & amortization expense 167 103 Property, plant & equipment Income before tax 641 562 Accumulated depreciation Income tax expense 245 215 Net property & equipment Net income 396 Other assets Outstanding shares 208 200 Total assets G Year 2 Year 1 746 751 313 262 1,767 1,184 102 41 2,928 2,238 1,987 543 1,444 466 4,838 1,093 395 698 59 2,995 347 2,473 1.704 RATIOS Sales growth Gross Profit Margin Selling General & Administrative Exp DEPRECIATION (depn exp/pryr PPE gross) Tax (Inc Tax / Pre-tax inc) 22.67% 19.96% 14.69% Accounts payable & accrued liabilities Short-term debt & cmitd Income taxes Total current liab 114 127 2,714 16 65 1.785 15.28% 38.22% Long term liabilities Long term debt Total long-term liabilities 122 181 303 100 15 115 Common stock Capital surplus Retained earnings OTHER EQUITIES Shareholder equity Total liabilities & net worth 20 576 1.225 20 247 828 1.821 1.095 4,838 2,995 12 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.23% 2.66 2.74 $0.00 $0.00 15 CAPEX CAPEX/Sales 1029 416 5.71% 3.33% Best Buy Please see excel data file to assignment 3. The file includes financial information for Best Buy. Tasks: 1. Using the residual income model, prepare a valuation of the common stock of Best Buy as of Year 2 under the following assumptions (use Exhibit 9.6 in the textbook as an example): a. Forecast horizon of five years (years 3 - 7) b. Sales growth of 10% for Best Buy per year (type in 10% in each year) over the forecast period and 3.5% thereafter (terminal year). c. All financial ratios remain at Year 2 levels. In other words, you create a model as show in Exhibit 9.6. You can download the "Residual Income Computation Exhibit 9.6" spreadsheet that is posted in module 12 and update it using the BestBuy data. After you have determined the values for year 2, you use the same financial ratios for the forecast horizon. The balance sheet and income statement info should update automatically for years 3 - 7 if you keep the formulas. d. Cost of equity capital is 12.5%. All tasks should be answered using excel. You may want to copy and paste financial information into the template file to be able to link cells to the financial model. 2. Copy and paste your original model (from task 1) into a separate tab in the same excel file and run the following sensitivity analysis: Growth rate equals 1% each year during the forecast horizon (1.e. type in 1% for sales growth in years 3 - 7) and remains at 3% thereafter. Explain your finding with respect to the new share price. 3. 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 1 38.22% B CDE Best Buy BALANCE SHEET Year 2 Year 1 Cash Income statement Receivables Net sales 15,326 12,494 Inventories Cost of goods 12,267 10.101 Other Gross profit 3,059 2,393 Total current assets Selling general & administrative expense 2,251 1,728 Depreciation & amortization expense 167 103 Property, plant & equipment Income before tax 641 562 Accumulated depreciation Income tax expense 245 215 Net property & equipment Net income 396 Other assets Outstanding shares 208 200 Total assets G Year 2 Year 1 746 751 313 262 1,767 1,184 102 41 2,928 2,238 1,987 543 1,444 466 4,838 1,093 395 698 59 2,995 347 2,473 1.704 RATIOS Sales growth Gross Profit Margin Selling General & Administrative Exp DEPRECIATION (depn exp/pryr PPE gross) Tax (Inc Tax / Pre-tax inc) 22.67% 19.96% 14.69% Accounts payable & accrued liabilities Short-term debt & cmitd Income taxes Total current liab 114 127 2,714 16 65 1.785 15.28% 38.22% Long term liabilities Long term debt Total long-term liabilities 122 181 303 100 15 115 Common stock Capital surplus Retained earnings OTHER EQUITIES Shareholder equity Total liabilities & net worth 20 576 1.225 20 247 828 1.821 1.095 4,838 2,995 12 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.23% 2.66 2.74 $0.00 $0.00 15 CAPEX CAPEX/Sales 1029 416 5.71% 3.33% Best Buy