The Major Motors Corporation is trying to decide whether to introduce a new midsize car. The directors of the company only want to produce the car if it has at least an 80% chance of generating a positive net present value over the next ten years. If the company decides to produce the car, it will have to pay an uncertain initial startup cost that is estimated to follow a triangular distribution with a minimum value of $2 billion, maximum value of $2.4 billion, and a most likely value of $2.1 billion. In the first year, the company would produce 100,000 units. Demand during the first year is uncertain but expected to be normally distributed with a mean of 95,000 and standard deviation of 7,000. For any year in which the demand exceeds production, production will be increased by 5% in the following year. For any year in which the production exceeds demand, production will be decreased by 5% in the next year, and the excess cars will be sold to a rental car company at a 20% discount. After the first year, the demand in any year will be modeled as a normally distributed random variable with a mean equal to the actual demand in the previous year and standard deviation of 7,000. In the first year, the sales price of the car will be $13,000 and the total variable cost per car is expected to be $9,500. Both the selling price and variable cost is expected to increase each year at the rate of inflation, which is assumed to be uniformly distributed between 2% and 7%. The company uses a discount rate of 9% to discount future cash flows.

a. Create a spreadsheet model for this problem and replicate it 5000 times. What is the minimum, average, and maximum NPV Major Motors can expect if it decides to produce this car? (Consider using the NPV( ) function to discount the profits Major Motors would earn each year.)

b. What is the probability of Major Motors earning a positive NPV over the next ten years?

c. Should Major Motors produce this car?