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 as an example): a. Forecast horizon of five years (years 3 - 7) b. Sales growth of 10% for Best Buy per year over the forecast period and 3.5% thereafter terminal year). c. All financial ratios remain at Year 2 levels. 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. B CD E Best Buy Year 2 Year 1 BALANCE SHEET Cash Receivables Inventories Other Total current assets G Year 2 Year 1 746 7511 313 262 1,767 1,184 102 41 2,928 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 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 1,093 543 395 1,444 698 466 59 4,838 2.995 2.473 1,704 RATIOS Accounts payable & accrued liabilities Short-term debt & cmltd Income taxes Total current liab 114 16 127 65 2,714 1,785 Sales growth Gross Profit Margin Selling General & Administrative Expl DEPRECIATION (depn exp / pr yr PPE gross) Tax (Inc Tax / Pre-tax inc) 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 1.095 4,838 2,995 RATIOS AR turn INV turn AP turn Tax Pay (Tax pay / tax exp) FLEV Divlsh 48.96 47.69 6.94 8.53 4.96 5.931 .8000 10000 2.66 2.74 $0.00 $0.00 5 7 CAPEX CAPEX?Sales 9 1029 416 6.12.3.33% 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 as an example): a. Forecast horizon of five years (years 3 - 7) b. Sales growth of 10% for Best Buy per year over the forecast period and 3.5% thereafter terminal year). c. All financial ratios remain at Year 2 levels. 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. B CD E Best Buy Year 2 Year 1 BALANCE SHEET Cash Receivables Inventories Other Total current assets G Year 2 Year 1 746 7511 313 262 1,767 1,184 102 41 2,928 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 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 1,093 543 395 1,444 698 466 59 4,838 2.995 2.473 1,704 RATIOS Accounts payable & accrued liabilities Short-term debt & cmltd Income taxes Total current liab 114 16 127 65 2,714 1,785 Sales growth Gross Profit Margin Selling General & Administrative Expl DEPRECIATION (depn exp / pr yr PPE gross) Tax (Inc Tax / Pre-tax inc) 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 1.095 4,838 2,995 RATIOS AR turn INV turn AP turn Tax Pay (Tax pay / tax exp) FLEV Divlsh 48.96 47.69 6.94 8.53 4.96 5.931 .8000 10000 2.66 2.74 $0.00 $0.00 5 7 CAPEX CAPEX?Sales 9 1029 416 6.12.3.33%