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”

 

  1. $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.
  2. Production is 10,000 units (year 1) with 15% growth over previous year
  3. Sales Price set to $70 ea
  4. Variable Cost is $35 per unit
  5. Since this is a high-risk venture, MARR is 20%
  6. Ordinary tax rate of 29%
  7. Annual fixed costs are $200,000; and increase to $300,000 for production rates over 12,500 units
  8. Determine the “Base Case” NPV. Provide a copy of the excel spreadsheet model with your NPV calculation.
  9. Does this appear to be a good investment? (why or why not in 50 words or less)
  10. Now, let’s integrate uncertainty into your three primary input values. Develop the three distributions for the following uncertainties:
    1. Production output is 10,000 units (year 1)
      1. Production Growth expected to be 15% each year
        1. 10% Chance for worst case of -10% (for net 5% growth)
        2. 10% Chance for best case +20% (for net 35% growth)
        3. Present a copy of your probability table.

 

  1. Sales Price set to $70 ea
    1. 10% Chance for worst case of -20% (for net $56)
    2. 5% Chance for best case +10% (for net $77)
    3. Present a copy of your probability table.

 

  1. Variable Cost is $35 per unit
    1. Variable Costs are expected to drop mostly due to learning
      1. 5% Chance for worst case of +5% (for net $36.75)
      2. 20% Chance for best case -30% (for net $24.50)
      3. Present a copy of your probability table.

 

  1. Record the NPV values for the uncertainty profiles of the variables over at least 100 simulations. Present your entire probability table.

 

  1. Plot the sorted NPV values and present your final plot.
  2. Based on your plot, what is the approximate probability that the investment will have a negative NPV?
  3. What is the approximate probability that the investment will have an NPV of $200,000 or greater?
  4. Should the manufacturer pursue the investment? Why or why not? (in 50 words or less)