Management Science Homework #3 MC Question 1: Equipment Problem - Investment Decisions Using NPV, IRR and Simulation A firm is considering an investment into a piece of equipment that they expect to increase profits by about $40,000 per year. The equipment costs about $200,000 and installation is about $20,000. Scheduled overhauls of the equipment are required at years 5 and 10, each costing about $100,000. The equipment is needed for a 15 year contract. At the end of the contract, it should still be saleable and its value is estimated at $100,000. The firm uses a MARR (Minimum Acceptable Rate of Return of 10% to evaluate these type of investments. (a) Create an excel model that determines the NPV and IRR of the investment. Based on this, would you recommend the firm pursue the investment? Reality recognizes that there is risk associated with each estimated number. After careful study, the firm estimates the probabilistic behaviours are best incorporated by the following: Investment (equipment and installation) - uniformly distributed (discrete) between $210,000 and $230,000 Profits - normally distributed with a mean of $40,000 and a standard deviation of $6,000 (each year profits would be different) Overhauls - 10% chance the cost will be $85,000; 60% chance $100,000; 30% chance $120,000 I Salvage Value - normally distributed with a mean of $100,000 and a standard deviation of $13,000 (b) Incorporate these distributions into your model, and run 1000 trials. What is the average, and maximum and minimum NPV and IRR? What percent of time is the investment desirable? Reality recognizes that there is risk associated with each estimated number. After careful study, the firm estimates the probabilistic behaviours are best incorporated by the following: Investment (equipment and installation) - uniformly distributed (discrete) between $210,000 and $230,000 Profits - normally distributed with a mean of $40,000 and a standard deviation of $6,000 (each year profits would be different) Overhauls - 10% chance the cost will be $85,000; 60% chance $100,000; 30% chance $120,000 I Salvage Value - normally distributed with a mean of $100,000 and a standard deviation of $13,000 (b) Incorporate these distributions into your model, and run 1000 trials. What is the average, and maximum and minimum NPV and IRR? What percent of time is the investment desirable