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 2. Copy and paste your original model (from task 1) into a separate tab in the same excel file and run the following sensitivity analyses: Growth rate equals 1% each year during the forecast horizon (i.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. I E B C D Best Buy Year 2 Year 1 Income statement Net sales #### #### Cost of goods #11011 10.101 Gross profit 3,059 2,393 Selling general & administrative 2,251 1,728 Depreciation & amortization expense 167 103 Income before tax 641 562 Income tax expense 245 215 Net income 396 347 Outstanding shares 208 200 BALANCE SHEET Cash Receivables Inventories Other Total current assets H Year 2 Year 746 751 313 262 1,767 1,184 102 41 2.928 2,238 MEBELENI HORRELATED 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 #### #### RATIOS Sales growth Gross Profit Margin Selling General & Administrative 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 1,704 114 16 127 65 2,714 1,785 #### #13# 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 RATIOS AR turn INV turn AP turn Tax Pay (Tax pay / tax exp) FLEV Divish 48.96 47.69 6.94 8.53 4.96 5.93 #### #### 2.66 2.74 $0.00 $0.00 CAPEX CAPEXSales 1029 416 6.71% 3.33% 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 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 2. Copy and paste your original model (from task 1) into a separate tab in the same excel file and run the following sensitivity analyses: Growth rate equals 1% each year during the forecast horizon (i.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. I E B C D Best Buy Year 2 Year 1 Income statement Net sales #### #### Cost of goods #11011 10.101 Gross profit 3,059 2,393 Selling general & administrative 2,251 1,728 Depreciation & amortization expense 167 103 Income before tax 641 562 Income tax expense 245 215 Net income 396 347 Outstanding shares 208 200 BALANCE SHEET Cash Receivables Inventories Other Total current assets H Year 2 Year 746 751 313 262 1,767 1,184 102 41 2.928 2,238 MEBELENI HORRELATED 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 #### #### RATIOS Sales growth Gross Profit Margin Selling General & Administrative 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 1,704 114 16 127 65 2,714 1,785 #### #13# 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 RATIOS AR turn INV turn AP turn Tax Pay (Tax pay / tax exp) FLEV Divish 48.96 47.69 6.94 8.53 4.96 5.93 #### #### 2.66 2.74 $0.00 $0.00 CAPEX CAPEXSales 1029 416 6.71% 3.33% Best Buy +
