For this assignment, please answer questions about your analysis of this "mini-case." Solve it and bring your model, details and solution to class so that you can answer questions with regards to it.

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:

a.Perform a 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 company based on the NPV?