The Major Motors Corporation is trying to decide whether to introduce a new mid- size car. The directors of the company only want to produce the car if it has at least an 80% chance of generating a positive NPV over the next 10 years. If the company decides to produce the car, it will have to pay an uncertain initial start-up 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.

B m Input Parameter Production Adjustment Inflation Discount Rate Surplus Discount 2096 Initial investment At least $2.00 billions At most $2.40 billions Most likely $2. 10 billions 12 Initial Production 190 thousand 13 Demand 14 Initial Mean 95 thousand 15 Std. Dev. ? thousand 16 17 Sales Price $13.09 thousand 18 Cost $9.50 thousand 19 20 21 Calculations 22 Year 1 Year 2 Year 3 Year 4 Year 5 23 Production Amount 24 Demand 25 26 Sales Amout 27 Sold at Discount 28 29 Inflation rate 30 31 Sales Price 32 Cost 33 Discount Price 34 35 Revenue from full price 36 Revenue from discounted 37 Cost Production 38 39 Profit 40 41 Net Present Value 42 Initial Investment 43 44 Net Profit After Investment The screenshot above shows first five years of the Major Company problem. You can download the excel file and the problem statement by clicking here Do Consider the Major Motors Corporation problem and the screenshot above. Using dropdowns, construct the demand for year 2 (Cell D24), =GenNamall C24 C15