One of the keys to success is effectively evaluating the investment opportunities is estimating

the potential cash flows and risk associated with those cash flows. Net Present Value and the

Internal Rate of Return are two methods widely accepted and utilized by businesses

throughout the world. NPV requires an estimate of likely cash flows discounted back to the

present at the appropriate cost of capital while the IRR equates the initial investment to the

present value of the future cash flows. The implied rate is then compared to the cost of capital

with adjustments for risk reflected through changes to the cost of capital. For example,

assume the following investment opportunity in which a $$250,000$ investment is expected to

generate annual cash flows of $$200,000$ per year:

Assuming a $12\%$ cost of capital the investment yields a NPV of approximately $$470,000$ and an IRR of $75\%.$ We have generated a point estimate of the possible benefit from investing in the project; however this level of analysis tells us nothing about the distribution around the point estimate.

Monte Carlo Simulation

Monte Carlo $($MC$)$ simulation is one of several techniques available to assess the risks surrounding the investment decision. In a MC simulation random scenarios are created using input values for key uncertain variables. The results provide a probability distribution of possible outcomes which then can be statistically analyzed to yield estimates of project risk.

Extending the above example let$$s assume that the expected number of product$$s sold is $7,000$ per year with a range from $4,100$ to $8,400$ and that the expected cost of a key input is $$150$ per unit with a range from $$130$ to $$190.$ Also assume that since the probability distribution for both the quantity sold and cost of the key input is unknown we will utilize the triangle distribution. By defining the range from $4,000$ to $8,500$ for the products sold and the cost of a key input from $$130$ to $$200$ the mean values are $6,333$ and $$160,$ respectively$\mathrm{.}5$

Develop a Monte Carlo simulation estimate of NPV when key inputs vary as suggested above. Run $5,000$ iterations at least. You can utilize RAND function or @risk excel add$-$in to estimated NPV$.$ Once you perform Monte Carlo simulation please take your stand regarding the claim that Monte Carlo analysis lowers NPV estimate.

What is the range of possible NPV values that your model generates? Provide a standard deviation for your estimate of possible NPV values.

Your simulation should provide a more conservative point estimate of the NPV and IRR but also provides the decision$-$maker with the probability distribution of possible outcomes as illustrated in Tables III and IV illustrate:

Your task is to develop your own Monte Carlo simulation model and present the summary statistics regarding NPV Estimates from this model.

Please build your model as a separate sheet in the excel spreadsheet which provides deterministic model from the Table I.