Question 8: (7 points) You can go to www.palisade.com and install the software in your laptop. They offer 15 days free trial. Or you can use the software We provided you but don't forget to connect to the VPN. ICG is thinking of launching a new product. The objective in ICG is to examine the robustness of decisions based on Net Present Value calculations, when some of the inputs change. The management team is trying to decide whether or not to launch a new product which is expected to have a market for the next five years. The following information about the product has been gathered: i) The expected market size is 8,000,000 units in the first year with 27. growth per year thereafter. ii) A 15% market share is anticipated in the first year, growing linearly by 0.3% per year afterwards. In the first year, the price is likely to be $7 growing by 6% annually thereafter. iv) The expected variable cost per unit is $5 increasing by 3% per year. Fixed costs are expected to start at $2,000,000 and to grow annually at 3% as well. An initial investment of $2,500,000 is planned. The discount rate of 15% is considered for projects of this kind. These figures represent the single figure estimates' for the product's future prospects in a growing market. This combination of estimates is referred to as the base case. To measure the feasability of this project, the management firm is using the NPV and IRR methodologies. A simple spreadsheet model can be quickly built, (see spreadsheet) with the cash flow modeled in $000s). It can be assumed that the estimates for initial market size and market share are firm, but there is uncertainty about market share increase and market growth. Optimistic, pessimistic and base case (most likely values) values for these parameters are set out in cells E17..G18 (see spreadsheet). These can be used to determine the worst and best values for the NPV of the proposal (see cells E19..G19). However, it is extre Introducing uncertainty about Market Growth & Market Share Increase Firstly, market growth is most likely to be a 2% increase, but could range from a 10% decrease to an 8% increase following a roughly triangular distribution. (A triangular distribution is one for which the probability density function takes a triangular shape. Secondly, market share increase can be assumed to be uniformly distributed between 0.2% (shrinkage) and 0.8% (in contrast to the single figure 0.3% assumed before). This means that growth is equally likely to take any value in the above mentioned range. Armed with these assessed distributions, Choose the following Simulation Settings: Iterations: 5,000 Simulations: 1 Sampling Type: Hypercube Simulation not Running, Distributions return=Static Values then When RiskStatic is not defined use Expected Values Generator: Marsenne Twister Initial seed: 1 All use same seed. Collect distribution samples: All Smart sensitivity analysis: Enabled First when you open this spreadsheet, save it under your Last Name-Question8.xls then start solving the problem 1. In this workbook please generate and attach the following @Risk Excel Reports: Quick Report, Input and Output Results Summary, Detailed Stats,Scenario and Sensitivity sheets 2. Show the Cumulative Ascending Cumulative - Line. What is the probability that the project will make its hurdle rate? 3. Show the Output Histogram for NPV and IRR What is the probability of IRR being less than 15%. 4. What are the key drivers of the project's success and what is their interpretation (Screenshots)? What @Risk function did you use to find the key drivers? 5. Show the sensitivity analysis screenshot and interpret the R squared. 6. Would you lunch this product or not? Why? 4 8.490 5 8.659 ICG SPREADSHEET FOR FIVE YEARS (cashflow in thousands) 1 2 3 MARKET SIZE 8,000 8.160 8.323 PRICE 7.0 VARIABLE COST 5.0 SALES (MS) 1,200 NET REVENUE 2.400 FIXED COSTS -2.000 CASHFLOW -2,500.00 400 RESULTS NPV IRR ASSUMPTIONS Discount Rate Prod Cost Price Market Share MS Incr MktGrowth 15% 5 7 15% 0.3% 102.0% BaseCase Pessimistic Most Likely Optimistic MS Incr -0.2% 0.3% 0.8% MktGrowth 90.0% 102.0% 108.0% NPV IRR Question 8: (7 points) You can go to www.palisade.com and install the software in your laptop. They offer 15 days free trial. Or you can use the software We provided you but don't forget to connect to the VPN. ICG is thinking of launching a new product. The objective in ICG is to examine the robustness of decisions based on Net Present Value calculations, when some of the inputs change. The management team is trying to decide whether or not to launch a new product which is expected to have a market for the next five years. The following information about the product has been gathered: i) The expected market size is 8,000,000 units in the first year with 27. growth per year thereafter. ii) A 15% market share is anticipated in the first year, growing linearly by 0.3% per year afterwards. In the first year, the price is likely to be $7 growing by 6% annually thereafter. iv) The expected variable cost per unit is $5 increasing by 3% per year. Fixed costs are expected to start at $2,000,000 and to grow annually at 3% as well. An initial investment of $2,500,000 is planned. The discount rate of 15% is considered for projects of this kind. These figures represent the single figure estimates' for the product's future prospects in a growing market. This combination of estimates is referred to as the base case. To measure the feasability of this project, the management firm is using the NPV and IRR methodologies. A simple spreadsheet model can be quickly built, (see spreadsheet) with the cash flow modeled in $000s). It can be assumed that the estimates for initial market size and market share are firm, but there is uncertainty about market share increase and market growth. Optimistic, pessimistic and base case (most likely values) values for these parameters are set out in cells E17..G18 (see spreadsheet). These can be used to determine the worst and best values for the NPV of the proposal (see cells E19..G19). However, it is extre Introducing uncertainty about Market Growth & Market Share Increase Firstly, market growth is most likely to be a 2% increase, but could range from a 10% decrease to an 8% increase following a roughly triangular distribution. (A triangular distribution is one for which the probability density function takes a triangular shape. Secondly, market share increase can be assumed to be uniformly distributed between 0.2% (shrinkage) and 0.8% (in contrast to the single figure 0.3% assumed before). This means that growth is equally likely to take any value in the above mentioned range. Armed with these assessed distributions, Choose the following Simulation Settings: Iterations: 5,000 Simulations: 1 Sampling Type: Hypercube Simulation not Running, Distributions return=Static Values then When RiskStatic is not defined use Expected Values Generator: Marsenne Twister Initial seed: 1 All use same seed. Collect distribution samples: All Smart sensitivity analysis: Enabled First when you open this spreadsheet, save it under your Last Name-Question8.xls then start solving the problem 1. In this workbook please generate and attach the following @Risk Excel Reports: Quick Report, Input and Output Results Summary, Detailed Stats,Scenario and Sensitivity sheets 2. Show the Cumulative Ascending Cumulative - Line. What is the probability that the project will make its hurdle rate? 3. Show the Output Histogram for NPV and IRR What is the probability of IRR being less than 15%. 4. What are the key drivers of the project's success and what is their interpretation (Screenshots)? What @Risk function did you use to find the key drivers? 5. Show the sensitivity analysis screenshot and interpret the R squared. 6. Would you lunch this product or not? Why? 4 8.490 5 8.659 ICG SPREADSHEET FOR FIVE YEARS (cashflow in thousands) 1 2 3 MARKET SIZE 8,000 8.160 8.323 PRICE 7.0 VARIABLE COST 5.0 SALES (MS) 1,200 NET REVENUE 2.400 FIXED COSTS -2.000 CASHFLOW -2,500.00 400 RESULTS NPV IRR ASSUMPTIONS Discount Rate Prod Cost Price Market Share MS Incr MktGrowth 15% 5 7 15% 0.3% 102.0% BaseCase Pessimistic Most Likely Optimistic MS Incr -0.2% 0.3% 0.8% MktGrowth 90.0% 102.0% 108.0% NPV IRR