Problem 9-25 Suppose that in January 2006, Kenneth Cole Productions had EPS of $1.65 and a book value of equity of $12.05 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 9.1 (far below), estimate KCP's share price. b. What range of share prices do you estimate based on the highest and lowest P/E multiples in Table 9.1 (far below)? c. Using the average price to book value multiple in Table 9.1 (far below), estimate KCP'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 9.1 (far below)? EPS Book value of equity $1.65 $12.05 a. Using the average P/E multiple in Table 9.1 (far below), estimate KCP's share price. Price per share b. What range of share prices do you estimate based on the highest and lowest P/E multiples in Table 9.1 (far below)? Highest P/E Lowest P/E Highest share price Lowest share price c. Using the average price to book value multiple in Table 9.1 (far below), estimate KCP's share price. Price per share d. What range of share prices do you estimate based on the highest and lowest price to book value multiples in Table 9.1 (far below)? Highest price-to-book Lowest price-to-book Highest share price Lowest share price P/E 16.64 Price/Book 3.59 5.02 3,451 2.41 Table 9-1 Stock Prices and Multiples for the Footwear Industry, January 2006 Market Enterprise Stock Price Capitalization Value Ticker Company ($) (S million) (5 million) NKE Nike 84.20 21,830 20,518 PMMA Puma AG 312.05 5,088 4,593 RBK Reebok 58.72 3,514 www Wolverine World Wide 22.10 1,257 1,253 BWS Brown Shoe 43.36 800 1,019 SKX Sketchers 17.09 683 614 SRR Stride Rite 13.70 497 524 DECK Deckers Outdoor 30.05 373 367 WEYS Weco Group 230 226 RCKY Rocky Shoes & Boots 19.96 106 232 DFZ R.G. Barry Corp. 6.83 68 92 BOOT LaCross Footwear 10.40 62 75 Average (excl. KCP) Maximum/Average Minimum/Average 14.99 14.91 17.42 22.62 17.63 20.72 13.32 11.97 8.66 Enterprise Value/Sales 1.43 2.19 0.90 1.20 0.47 0.62 0.89 1.48 1.06 0.92 0.87 0.76 1.06 106% -56% Enterprise Value/EBITDA 8.75 9.02 8.58 9.53 9.09 6.88 9.28 7.44 19.90 2.71 1.91 2.02 1.87 2.29 1.75 1.12 8.11 1.28 2.84 186% -61% 6.66 9.20 12.09 7.55 10.75 8.30 8.49 27% -22% 15.01 51% -42% rements 1. Start Excel - completed. 2. In cell D15, by using cell references, calculate the share price based on average P/E multiple (1 pt.). 3. In cell D19, by using cell references and the function MAX, determine the highest P/E multiple (1 pt.). Note: Be sure to enter the corresponding cells for all the twelve companies in the function MAX. 4. In cell D20, by using cell references and the function MIN, determine the lowest P/E multiple (1 pt.). Note: Be sure to enter the corresponding cells for all the twelve companies in the function MIN. 5. In cell D21, by using cell references, calculate the highest share price based on the industry's P/E multiple (1 pt.). Note: Use the highest P/E multiple determined above in your calculations. 6. In cell D22, by using cell references, calculate the lowest share price based on the industry's P/E multiple (1 pt.). Note: Use the lowest P/E multiple determined above in your calculations. 7. In cell D26, by using cell references, calculate the share price based on average price to book value multiple (1 pt.). 8. In cell D30, by using cell references and the function MAX, determine the highest price to book value multiple (1 pt.). Note: Be sure to enter the corresponding cells for all the twelve companies in the function MAX. 9. In cell D31, by using cell references and the function MIN, determine the lowest price to book value multiple (1 pt.). Note: Be sure to enter the corresponding cells for all the twelve companies in the function MIN. 10. In cell D32, by using cell references, calculate the share price based on the highest price to book value multiple (1 pt.). Note: Use the highest price to book value multiple determined above in your calculations. 11. In cell D33, by using cell references, calculate the share price based on the lowest price to book value multiple (1 pt.). Note: Use the lowest price to book value multiple determined above in your calculations. 12. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. Problem 9-25 Suppose that in January 2006, Kenneth Cole Productions had EPS of $1.65 and a book value of equity of $12.05 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 9.1 (far below), estimate KCP's share price. b. What range of share prices do you estimate based on the highest and lowest P/E multiples in Table 9.1 (far below)? c. Using the average price to book value multiple in Table 9.1 (far below), estimate KCP'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 9.1 (far below)? EPS Book value of equity $1.65 $12.05 a. Using the average P/E multiple in Table 9.1 (far below), estimate KCP's share price. Price per share b. What range of share prices do you estimate based on the highest and lowest P/E multiples in Table 9.1 (far below)? Highest P/E Lowest P/E Highest share price Lowest share price c. Using the average price to book value multiple in Table 9.1 (far below), estimate KCP's share price. Price per share d. What range of share prices do you estimate based on the highest and lowest price to book value multiples in Table 9.1 (far below)? Highest price-to-book Lowest price-to-book Highest share price Lowest share price P/E 16.64 Price/Book 3.59 5.02 3,451 2.41 Table 9-1 Stock Prices and Multiples for the Footwear Industry, January 2006 Market Enterprise Stock Price Capitalization Value Ticker Company ($) (S million) (5 million) NKE Nike 84.20 21,830 20,518 PMMA Puma AG 312.05 5,088 4,593 RBK Reebok 58.72 3,514 www Wolverine World Wide 22.10 1,257 1,253 BWS Brown Shoe 43.36 800 1,019 SKX Sketchers 17.09 683 614 SRR Stride Rite 13.70 497 524 DECK Deckers Outdoor 30.05 373 367 WEYS Weco Group 230 226 RCKY Rocky Shoes & Boots 19.96 106 232 DFZ R.G. Barry Corp. 6.83 68 92 BOOT LaCross Footwear 10.40 62 75 Average (excl. KCP) Maximum/Average Minimum/Average 14.99 14.91 17.42 22.62 17.63 20.72 13.32 11.97 8.66 Enterprise Value/Sales 1.43 2.19 0.90 1.20 0.47 0.62 0.89 1.48 1.06 0.92 0.87 0.76 1.06 106% -56% Enterprise Value/EBITDA 8.75 9.02 8.58 9.53 9.09 6.88 9.28 7.44 19.90 2.71 1.91 2.02 1.87 2.29 1.75 1.12 8.11 1.28 2.84 186% -61% 6.66 9.20 12.09 7.55 10.75 8.30 8.49 27% -22% 15.01 51% -42% rements 1. Start Excel - completed. 2. In cell D15, by using cell references, calculate the share price based on average P/E multiple (1 pt.). 3. In cell D19, by using cell references and the function MAX, determine the highest P/E multiple (1 pt.). Note: Be sure to enter the corresponding cells for all the twelve companies in the function MAX. 4. In cell D20, by using cell references and the function MIN, determine the lowest P/E multiple (1 pt.). Note: Be sure to enter the corresponding cells for all the twelve companies in the function MIN. 5. In cell D21, by using cell references, calculate the highest share price based on the industry's P/E multiple (1 pt.). Note: Use the highest P/E multiple determined above in your calculations. 6. In cell D22, by using cell references, calculate the lowest share price based on the industry's P/E multiple (1 pt.). Note: Use the lowest P/E multiple determined above in your calculations. 7. In cell D26, by using cell references, calculate the share price based on average price to book value multiple (1 pt.). 8. In cell D30, by using cell references and the function MAX, determine the highest price to book value multiple (1 pt.). Note: Be sure to enter the corresponding cells for all the twelve companies in the function MAX. 9. In cell D31, by using cell references and the function MIN, determine the lowest price to book value multiple (1 pt.). Note: Be sure to enter the corresponding cells for all the twelve companies in the function MIN. 10. In cell D32, by using cell references, calculate the share price based on the highest price to book value multiple (1 pt.). Note: Use the highest price to book value multiple determined above in your calculations. 11. In cell D33, by using cell references, calculate the share price based on the lowest price to book value multiple (1 pt.). Note: Use the lowest price to book value multiple determined above in your calculations. 12. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed