First when you open this spreadsheet, save it under your Last Name-Question8.xls then start solving the problem

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:

1:The expected market size is 8,000,000 units in the first year with 2% growth per year thereafter.

2:A 15% market share is anticipated in the first year, growing linearly by 0.3% per year afterwards.

3:In the first year, the price is likely to be $7 growing by 6% annually thereafter.

4: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.

5:An initial investment of $2,500,000 is planned. A discount rate of 15% is usually taken in calculating the Net Present Value 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 extremely unlikely that all the worse (or best) circumstances will occur together.

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?