4. NPV Analysis (Simulation)

The Aberdeen Development Corporation (ADC) is considering the Aberdeen Resort Hotel project. It would be located right on the picturesque banks of Grays Harbor and have its own championship-level golf course. The cost to purchase the land would be $3 million, payable right now. Construction costs would be approximately $2 million, payable at the end of year 1. However, the construction costs are uncertain they could be up to 20% higher or lower than the estimate. Assume the construction costs would follow a triangular distribution.

ADC is very uncertain about the annual operating profits (or losses) that would be generated once the hotel was constructed. Their best guess for the annual operating profit that would be generated in years 2, 3, 4, and 5 is $700,000. Due to their great uncertainty, they guess the standard deviation to also be about $700,000. Assume each year is independent and follows the normal distribution (for calculating NPV, assume all profits are received at year end).

At the end of year 5, they plan to sell the hotel. The selling price is likely to be somewhere between $4 and $8 million (all values in this range are equally likely).

a) Assume ADC uses a 10% discount rate and use Crystal Ball to generate a distribution of the NPV of the project over 1000 trials. What is the mean NPV? What is the probability that the project will yield a positive NPV?

ADC is also concerned about operating profits in years 2, 3, 4, and 5.

Use Crystal Ball to forecast what ADC should expect the worst of the four years (i.e., the year with the lowest operating profit of the four) to look like.

What is the mean value of the lowest annual operating profit over the four years?

What is the probability that the lowest annual operating profit over the four years will at least be greater than $0? (Hint: the NPV(rate, cash stream) function in Excel returns the NPV of a stream of cash flows assumed to start one year from now for example NPV(10%,C5:F5) returns the NPV at a 10% discount rate, if C5 is a cash flow at the end of year 1, D5 at the end of year 2, E5 at the end of year 3, and F5 at the end of year 4. A cash flow at year 0 should be added outside the NPV function since it is already at its present value.)