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):
   1. Forecast horizon of five years (years 3 7)
   2. Sales growth of 10% for Best Buy per year over the forecast period and 3.5% thereafter (terminal year).
   3. All financial ratios remain at Year 2 levels.
   4. 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.

Best Buy Data:

(Exhibit 9.6)

Best Buy Year 2 Year 1 BALANCE SHEET Cash Receivables Inventories Other Total current assets Year 2 746 313 1,767 102 2,928 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.267 3,059 2,251 167 641 245 396 208 12,494 10.101 2,393 1,728 103 562 215 347 200 JU 1,987 543 1,444 466 4,838 1,093 395 698 59 2,995 Property, plant & equipment Accumulated depreciation Net property & equipment Other assets Total assets Accounts payable & accrued liabilities Short-term debt & cmltd Income taxes Total current liab RATIOS 2,473 1,704 22.67% 19.96% 14.69% 15.28% 114 127 2,714 Sales growth Gross Profit Margin Selling General & Administrative Exp / DEPRECIATION (depn exp/ pr yr PPE gross) Tax (Inc Tax / Pre-tax inc) 16 65 1,785 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 4,838 1.095 2,995 RATIOS AR turn INV turn AP turn Tax Pay (Tax pay / tax exp) FLEV Divlsh 48.96 6.94 4.96 51.84% 2.66 $0.00 47.69 8.53 5.93 30.23% 2.74 $0.00 CAPEX CAPEX/Sales 1029 6.71% 416 3.33% Sales growth Net profit Margin (Net income/Sales) NYC turn (Sales/NYC) FA turn (Sales/ FA) Net operating assets/Total equity Cost of equity Historical figures 2004 2005 2006 8.5000% 8.6957% 8.9000% 9.0500% 9.1554% 9.2000% 22.7353 11.8271 11.8271 1.8341 1.9878 1.9878 2.3362 2.5186 2.5186 12.5% Forecast Horizon 2007 2008 9.1000% 8.0000% 9.4000% 9.4000% 11.8271 11.8271 1.9878 1.9878 2.5186 2.5186 2009 7.0000% 9.4000% 11.8271 1.9878 2.5186 2010 6.0000% 9.4000% 11.8271 1.9878 2.5186 Terminal Year 2011 3.5000% 9.4000% 11.8271 1.9878 2.5186 ($ Thousands) Sales Net income ($ Mil) Net working capital Fixed assets Net Operating assets L-T Liabilities Total Stockholder's Equity $ 81,324 $ $ 7,360 $ $ 3,577 $ 44,340 $ S 47,917 $ $ 27,406 $ $ 20,511 $ 88,396 $ 96,263 $ 8,093 $ 8,856 $ 7,474 $ 8,139 $ 44,469 $ 48,427 $ 51,943 $ 56,566 S 31,319 $ 34,107 S 20,624 $ 22,459 $ 105,023 9,872 $ 8,880 $ 52,834 $ 61,714 S 37,211 S 24,503 $ 113,425 s 10,662 $ 9,590 S 57,061 S 66,651 $ 40,187 $ 26,463 $ 121,365 $ 11,408 $ 10,262 $ 61,055 $ 71,316 S 43,001 S 28,316 $ 128,647 12,093 10,877 64,718 75,595 45,581 30,015 s S $ $ $ $ $ 133,149 12,516 11,258 66,983 78,241 47,176 31,065 S S Residual Income Computation Net Income Beginning Equity Required Equity Return Expected Income Residual Income Discount factor $ 8,856 $ $ 20,624 $ 12.5% $ 2,578 $ $ 6,278 $ 0.8889 9,872 S 22,459 $ 12.5% 2,807 $ 7,065 $ 0.7901 10,662 $ 24,503 $ 12.5% 3,063 $ 7,599 $ 0.7023 11,408 $ 26,463 $ 12.5% 3,308 $ 8,100 $ 0.6243 *** 12,093 28,316 12.5% 3,539 8,553 0.5549 12,516 30,015 12.5% 3,752 8,764 S S $ 5,581 S $ 5,581 $ 5,582 $ 11,163 $ 5,337 $ 16,500 $ 5,057 $ 21,557 $ Present value of residual income Cum PV residual income Terminal value of abnormal earnings Beg book value of equity Value of equity Common shares outstanding (thousands) 0 0 0 4,746 26,303 54,039 20,624 100,966 1737 58.13 per share S Best Buy Year 2 Year 1 BALANCE SHEET Cash Receivables Inventories Other Total current assets Year 2 746 313 1,767 102 2,928 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.267 3,059 2,251 167 641 245 396 208 12,494 10.101 2,393 1,728 103 562 215 347 200 JU 1,987 543 1,444 466 4,838 1,093 395 698 59 2,995 Property, plant & equipment Accumulated depreciation Net property & equipment Other assets Total assets Accounts payable & accrued liabilities Short-term debt & cmltd Income taxes Total current liab RATIOS 2,473 1,704 22.67% 19.96% 14.69% 15.28% 114 127 2,714 Sales growth Gross Profit Margin Selling General & Administrative Exp / DEPRECIATION (depn exp/ pr yr PPE gross) Tax (Inc Tax / Pre-tax inc) 16 65 1,785 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 4,838 1.095 2,995 RATIOS AR turn INV turn AP turn Tax Pay (Tax pay / tax exp) FLEV Divlsh 48.96 6.94 4.96 51.84% 2.66 $0.00 47.69 8.53 5.93 30.23% 2.74 $0.00 CAPEX CAPEX/Sales 1029 6.71% 416 3.33% Sales growth Net profit Margin (Net income/Sales) NYC turn (Sales/NYC) FA turn (Sales/ FA) Net operating assets/Total equity Cost of equity Historical figures 2004 2005 2006 8.5000% 8.6957% 8.9000% 9.0500% 9.1554% 9.2000% 22.7353 11.8271 11.8271 1.8341 1.9878 1.9878 2.3362 2.5186 2.5186 12.5% Forecast Horizon 2007 2008 9.1000% 8.0000% 9.4000% 9.4000% 11.8271 11.8271 1.9878 1.9878 2.5186 2.5186 2009 7.0000% 9.4000% 11.8271 1.9878 2.5186 2010 6.0000% 9.4000% 11.8271 1.9878 2.5186 Terminal Year 2011 3.5000% 9.4000% 11.8271 1.9878 2.5186 ($ Thousands) Sales Net income ($ Mil) Net working capital Fixed assets Net Operating assets L-T Liabilities Total Stockholder's Equity $ 81,324 $ $ 7,360 $ $ 3,577 $ 44,340 $ S 47,917 $ $ 27,406 $ $ 20,511 $ 88,396 $ 96,263 $ 8,093 $ 8,856 $ 7,474 $ 8,139 $ 44,469 $ 48,427 $ 51,943 $ 56,566 S 31,319 $ 34,107 S 20,624 $ 22,459 $ 105,023 9,872 $ 8,880 $ 52,834 $ 61,714 S 37,211 S 24,503 $ 113,425 s 10,662 $ 9,590 S 57,061 S 66,651 $ 40,187 $ 26,463 $ 121,365 $ 11,408 $ 10,262 $ 61,055 $ 71,316 S 43,001 S 28,316 $ 128,647 12,093 10,877 64,718 75,595 45,581 30,015 s S $ $ $ $ $ 133,149 12,516 11,258 66,983 78,241 47,176 31,065 S S Residual Income Computation Net Income Beginning Equity Required Equity Return Expected Income Residual Income Discount factor $ 8,856 $ $ 20,624 $ 12.5% $ 2,578 $ $ 6,278 $ 0.8889 9,872 S 22,459 $ 12.5% 2,807 $ 7,065 $ 0.7901 10,662 $ 24,503 $ 12.5% 3,063 $ 7,599 $ 0.7023 11,408 $ 26,463 $ 12.5% 3,308 $ 8,100 $ 0.6243 *** 12,093 28,316 12.5% 3,539 8,553 0.5549 12,516 30,015 12.5% 3,752 8,764 S S $ 5,581 S $ 5,581 $ 5,582 $ 11,163 $ 5,337 $ 16,500 $ 5,057 $ 21,557 $ Present value of residual income Cum PV residual income Terminal value of abnormal earnings Beg book value of equity Value of equity Common shares outstanding (thousands) 0 0 0 4,746 26,303 54,039 20,624 100,966 1737 58.13 per share S