Monte Carlo Simulation Homework
Internal Rate of Return: 7.5%
Machine Life: 5 years
Sales Standard Deviation
Cost Standard Deviation
Cost Standard Deviation
Hugo is the CEO of Hugo Enterprises. Hugo must determine whether to buy or lease a machine that is critical for production at his plant. He has asked his asset manager to determine which one of these options is the better alternative. Here is the data, which is common to both options: In option 1, Hugo will buy the machine for $1.5 million, and the machine will have a $100 thousand salvage value at the end of year 5. In option 2, Hugo will lease the machine for $350,000/year. Installation cost of the machine is $50,000. At the end of the fifth year, the lease contract requires a $25,000 final inspection fee.
a) Using Monte Carlo simulation in Excel, simulate sales and costs for the five years using the data in the table above. Perform 10,000 trials. Assume that sales and costs are normally distributed with the means and costs shown. b) Generate dynamic descriptive statistics and histograms of the NPV of the 1000 trials for each alternative (see the example spreadsheets for examples of how to do this). c) Based on your analysis in part b, which is the better alternative? Notes: 1. It is imperative that you watch the three videos on this material and carefully study the example spreadsheets before attempting this homework. I suggest that you set up a spreadsheet and simulate option 1 first. Then, copy that worksheet, and use that worksheet as a template for Option 2. 2. It is clear that the sales forecast is a trend projection. The increasing standard deviation of sales reflects what statisticians call the “cone of confidence” about the trend line. In short, this means that the confidence intervals increase each period reflecting less confidence in the trend the further it gets from its history. If you were to draw a line connecting the upper bound and a line connecting the lower bound of the confidence intervals together, the result would be a cone around the trend line. Internal Rate of Return: 7.5% t-Test: Two-Sample Assuming Unequal Variances Machine Life: 5 years Option 1 Option 2 Year Sales Mean Sales Standard Deviation Cost Mean Cost Standard Deviation Cost Mean Cost Standard Deviation Variable 1 Variable 2 1 $250,000 $25,000 $25,000 $5,000 $12,500 $250 Mean 179216.3335 210391.8701 2 $300,000 $50,000 $30,000 $6,000 $15,000 $300 Variance 29477866762 29738521895 3 $390,000 $79,000 $39,000 $7,800 $19,500 $390 Observations 10000 10000 4 $546,000 $117,800 $54,600 $10,920 $27,300 $546 Hypothesized Mean Difference 0 5 $819,000 $176,140 $81,900 $16,380 $40,950 $819 df 19998 t Stat -12.81129344 P(T
Answers: First of all, your trial results will look a bit different than mine because of the random effects. If we were to simulate a 100,000 trials, the differences would be much smaller. But, even with 10,000 trials, we can make some conclusions that should be consistent even if the numbers are a bit different. If you had only tried 1000 trials, the answer to this problem would not be easy because the confidence intervals of options 1 and 2 might overlap. But with 10,000 trials, Option 2 is a clear winner because the confidence intervals do not overlap. Statisticians would probably cringe at the use of this heuristic because they would prefer we used a hypothesis test. So, to test whether or not this is really true, I created a hypothesis test to determine whether Option 1 is better than Option 2. For this test, I used the data analysis tool . See above right. From the Data Analysis dialog, I selected t-Test: Two-Sample Assuming Unequal Variances. In the dialog box, I set the hypothesized difference to 0 (null hypothesis: there is no difference between the means), and I input the NPV cell ranges for the two options. Since the mean of the Option 2 is clearly larger, I would set the alternative hypothesis to Option 2 Mean > Option 1 Mean. This is a one tail test. So, we should look at the results for one tail (see highlighted cells). Since the p-value is much smaller than .05, we can reject the null hypothesis and conclude that the Option 2 Population Mean is probably greater than the Option 1 Population Mean. Important note: even with manual calculation on (see next sheet for explanation), when you save the file, Excel recaculates. So, the numbers in the t-test table will be slightly different than the current simulation results. Since we computed 10,000 trials, however, the differences are not large; and the results of the hypothesis test will be nearly the same. In your homework problem, ________________.
In you homework, _____
a) increasing the IRR by 5 percentage points increases the average NPV of the options.
b) what is an IRR?
c) decreasing the IRR by 5 percentage points increases the average NPV of the options.
d) making substantial changes to the IRR doesn't appear to make a significant difference in the NPVs.
In your homework, when you recalculate the spreadsheet, the simulation results will change, but the changes to the descriptive statistics are fairly small.