A D G 1 Problem 8-19 2 3 thinking about marketing a new software product. Up-front costs to market generate profits of $1 million Innovation Company and develop the product are $5 million. The product is expected per year for 10 years. The company will have to provide product support expected to cost $100,000 per year in perpetuity. Assume all profits and expenses occur at the end of the year. 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. Ifa specific Excel fun ction 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 da ta is found. Make your computa tions 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. What is the NPV of this investment if the cost of capital is 6%? Should the fim undertake the project? Repeat the analysis for discount rates of 2% and 11% b. How many IRRS does this investment onportunity have? 5 What does the IRR rule indicate about this investment? Up-front costs Annual profits Number of perio ds (years) Product support costs 5.000,000 1,000,000 10 10 11 100,000 12 13 a. What is the NPV of this investment if the cost of capital is 6%? Should the firm undertake the project? Repeat the analysis for discount rates of 2% and 11 % . 14 15 Cost of capital (1) Cost of Cost of capital (3) 6% 2% 11% 18 19 NPV (1) 20 Take opportunity (Yes/No) NPV (2) Take opportunity (Yes/No) NPV (3) Take opportunity (Yes/No) 21 22 23 24 25 26 b. How many IRRS does this investment opportunity have? 27 28 IRRS. Based on (a) there are at least 29 30 c. What does the IRR rule indicate about this investment? 31 32 Trust the IRR nule (Yes/No) 33 34. 35 36 Requirements In cell D20, by using the PV function and cell references, write an expression the NPV of the investment opportunity for the first cost of capital. Note: For the PV function 1 calculate omit the [fv] and [type] arguments (1 pt.) In cell D21, type Yes or No depending on whether 37 take the investment opportunity (1 pt.). 2 38 3. In cell D22, by using the PV function and cell references, write an expression the NPV of the investment opportunity for the second cost function omit the [fv] and [type] arguments (1 pt.). calculate f capital. Note: For the PV 39 In cell D23, type Yes or No depending on whether to take the investment opportunity (1 pt.). 4 40 5 In cell D24, by using the PV function and cell references, write an expression to calculate the NPV of the investment opportunity for the third cost omit the [fv] and [typel arguments (1 pt.) capital. Note: For the PV function 41 In cell D25, type Yes or No depending on whether to take the investment opportunity (1 pt.). 42 7 In cell D29, type two, three or four depending on how many IRRS you think are there in this project (1 pt 43 A D G 1 Problem 8-19 2 3 thinking about marketing a new software product. Up-front costs to market generate profits of $1 million Innovation Company and develop the product are $5 million. The product is expected per year for 10 years. The company will have to provide product support expected to cost $100,000 per year in perpetuity. Assume all profits and expenses occur at the end of the year. 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. Ifa specific Excel fun ction 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 da ta is found. Make your computa tions 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. What is the NPV of this investment if the cost of capital is 6%? Should the fim undertake the project? Repeat the analysis for discount rates of 2% and 11% b. How many IRRS does this investment onportunity have? 5 What does the IRR rule indicate about this investment? Up-front costs Annual profits Number of perio ds (years) Product support costs 5.000,000 1,000,000 10 10 11 100,000 12 13 a. What is the NPV of this investment if the cost of capital is 6%? Should the firm undertake the project? Repeat the analysis for discount rates of 2% and 11 % . 14 15 Cost of capital (1) Cost of Cost of capital (3) 6% 2% 11% 18 19 NPV (1) 20 Take opportunity (Yes/No) NPV (2) Take opportunity (Yes/No) NPV (3) Take opportunity (Yes/No) 21 22 23 24 25 26 b. How many IRRS does this investment opportunity have? 27 28 IRRS. Based on (a) there are at least 29 30 c. What does the IRR rule indicate about this investment? 31 32 Trust the IRR nule (Yes/No) 33 34. 35 36 Requirements In cell D20, by using the PV function and cell references, write an expression the NPV of the investment opportunity for the first cost of capital. Note: For the PV function 1 calculate omit the [fv] and [type] arguments (1 pt.) In cell D21, type Yes or No depending on whether 37 take the investment opportunity (1 pt.). 2 38 3. In cell D22, by using the PV function and cell references, write an expression the NPV of the investment opportunity for the second cost function omit the [fv] and [type] arguments (1 pt.). calculate f capital. Note: For the PV 39 In cell D23, type Yes or No depending on whether to take the investment opportunity (1 pt.). 4 40 5 In cell D24, by using the PV function and cell references, write an expression to calculate the NPV of the investment opportunity for the third cost omit the [fv] and [typel arguments (1 pt.) capital. Note: For the PV function 41 In cell D25, type Yes or No depending on whether to take the investment opportunity (1 pt.). 42 7 In cell D29, type two, three or four depending on how many IRRS you think are there in this project (1 pt 43
