Cost Estimation & Financial Analysis – Monte Carlo Homework-13
Homework # 13: Monte Carlo Simulation
Refer to the associated MS Excel file, titled
“Monte Carlo Homework-13_withMacros.XLSM”
or
“Monte Carlo Homework-13_noMacros.XLSX”
- $350,000 investment is required for equipment, plus installation cost of $50,000. Investment is depreciated using 7 Yr MACRS table. The equipment will be sold in year five – anticipate salvage value will equal book value at that time.
- Production is 10,000 units (year 1) with 15% growth over previous year
- Sales Price set to $70 ea
- Variable Cost is $35 per unit
- Since this is a high-risk venture, MARR is 20%
- Ordinary tax rate of 29%
- Annual fixed costs are $200,000; and increase to $300,000 for production rates over 12,500 units
- Determine the “Base Case” NPV. Provide a copy of the excel spreadsheet model with your NPV calculation.
- Does this appear to be a good investment? (why or why not in 50 words or less)
- Now, let’s integrate uncertainty into your three primary input values. Develop the three distributions for the following uncertainties:
- Production output is 10,000 units (year 1)
- Production Growth expected to be 15% each year
- 10% Chance for worst case of -10% (for net 5% growth)
- 10% Chance for best case +20% (for net 35% growth)
- Present a copy of your probability table.
- Production Growth expected to be 15% each year
- Production output is 10,000 units (year 1)
- Sales Price set to $70 ea
- 10% Chance for worst case of -20% (for net $56)
- 5% Chance for best case +10% (for net $77)
- Present a copy of your probability table.
- Variable Cost is $35 per unit
- Variable Costs are expected to drop mostly due to learning
- 5% Chance for worst case of +5% (for net $36.75)
- 20% Chance for best case -30% (for net $24.50)
- Present a copy of your probability table.
- Variable Costs are expected to drop mostly due to learning
- Record the NPV values for the uncertainty profiles of the variables over at least 100 simulations. Present your entire probability table.
- Plot the sorted NPV values and present your final plot.
- Based on your plot, what is the approximate probability that the investment will have a negative NPV?
- What is the approximate probability that the investment will have an NPV of $200,000 or greater?
- Should the manufacturer pursue the investment? Why or why not? (in 50 words or less)