Topic: Decision Making Analysis and Monte Carlo Simulation

Service Mini-Case:

A company must decide if it is viable to develop and market an innovative service that will have a useful life of six years. The development of the service would cost $ 50,000 and annual costs of $ 6,000. The income in the first year is random as shown in the following table.

Income Year 1 Probability

$20,000 20%

$24,000 50%

$26,000 10%

$28,000 10%

$30,000 10%

It is estimated that the expected income for the years 2 - 6 will be 90% of those obtained the previous year (for example, the estimated income in year 3 will be 90% of the income of year 2), so that the earnings decrease at a fixed rate every year. Due to the decrease in revenues, the company considers that after the sixth year, to discontinue marketing the service. The company requires an 8% yield (a.k.a. the interest required or discount rate).

The company considers this initiative viable if the net present value has a 20% probability of being at least $ 22,500. Based on a model in MS Excel, modified to take into account the uncertainty of income in the first year (perform 500 iterations), explain if the company should develop and market the product.

Answer the following as well:

a.Perform Monte Carlo Simulation in Excel.

b.Choose answer: The correct MS Excel function that should be used to perform a Monte Carlo simulation experiment for the data shown above is?

i.VLOOKUP

ii.HLOOKUP

iii.IFS or nested Ifs

iv.MATCH

c.How can Monte Carlo simulation help you assess the riskiness of the business based on the NPV?