Problem 10-17 Suppose that in July 2013, Nike had EPS of $2.52 and a book value of equity of $12.48 per share. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. a. Using the average P/E multiple in Table 10.1, estimate Nike's share price. b. What range of share prices do you estimate based on the highest and lowest P/E multiples in Table 10.1? c. Using the average price-to-book value multiple in Table 10.1, estimate Nike's share price. d. What range of share prices do you estimate based on the highest and lowest price-to-book value multiples in Table 10.1? Table 10.1 Price/Book 5.07 Market Cap ($ million) 55,970 23,105 4,330 2,910 2,320 1,990 1,470 1,340 301 197 113 Name Nike, Inc. Adidas AG Puma AG Wolverine World Wide Steven Madden, Ltd. Deckers Outdoor Corp. Crocs, Inc. Skechers U.S.A. Weyco Group R. G. Barry Corp. Rocky Brands, Inc. Enterprise Value ($ million) 54,023 23,317 4,085 3,981 2,140 1,923 1,240 1,213 325 174 132 Average Enterprise Value EBITDA 15.71 11.88 9.34 9.28 10.70 P/E 23.29 32.33 70.56 37.60 18.40 16.74 11.46 67.41 16.53 14.92 12.46 29.25 3.06 1.96 4.13 3.68 2.67 2.40 1.54 1.71 Enterprise Value/Sales 2.03 1.20 0.96 1.22 1.74 1.36 1.10 0.78 1.11 1.19 0.58 1.21 8.73 6.74 18.16 9.69 6.44 6.61 10.30 2.31 0.89 2.67 All Ratios as of July 2013 55 Requirements In cell D34, by using cell references, calculate Nike's share price based on the average P/E multiple of the industry. (1 pt.). Note: Use cells D29 and F24. 2 In cell D38, by using the MAX function, display the highest P/E multiple of the industry excluding Nike itself (Cell F13) (1 pt.). 3 In cell D39, by using the MIN function, display the lowest P/E multiple of the industry excluding Nike itself (Cell F13) (1 pt.). In cell D40, by using cell references, calculate Nike's share price based on the highest P/E multiple of the industry (1 pt.). 5 Note: Use cells D29 and D38. In cell D41, by using cell references, calculate Nike's share price based on the lowest P/E multiple of the industry (1 pt.). Note: Use cells D29 and D39. In cell D45, by using cell references, calculate Nike's share price based on the average price-to-book multiple of the industry (1 pt.). Note: Use cells G24 and D30. In cell D49, by using the MAX function, display the highest price-to-book multiple of the industry excluding Nike itself (Cell G13) (1 pt.). In cell D50, by using the MIN function, display the lowest price-to-book multiple of the industry excluding Nike itself (Cell G13) (1 pt.). In cell D51, by using cell references, calculate Nike's share price based on the highest price-to-book multiple of the industry (1 pt.). Note: Use cells D30 and 149. In cell D52, by using cell references, calculate Nike's share price based on the lowest price-to-book multiple of the industry (1 pt.). Note: Use cells D30 and D50. 8 9 10