An electric car company, Edison Motors, is trying to determine the proper production capacity level for its new electric car. A unit of capacity gives us the potential to produce one car per year. It costs $10,000 to build a unit of capacity and the cost is charged equally over the next 5 years, at $2000 per year. It also costs $400 per year to maintain a unit of capacity (whether or not it is used).

Each car sells for $14,000 and incurs a variable production cost of $10,000.

The annual demand for the electric car during each of the next 5 years is believed to be normally distributed with mean 500,000 and standard deviation 100,000. The demands during different years are assumed to be independent.

Profits are discounted at a 7.5% annual interest rate.

The company is working with a 5-year planning horizon. Capacity levels of 300,000, 400,000, 500,000, 600,000, and 700,000 are under consideration. In each year, the company does not produce more than the demand for that year, so no inventory is carried from one year to the next. Perform simulations to analyze this problem.

In this problem, you will build the model as before, but with less given in the starter shell file. You will need to fill in the inputs from the problem statement and the logic to calculate cash flow. The NPV formula is provided for you in cell B36.

Some things to note when you work on this problem:

• For all simulations, run at least 1,000 replications.
• Absolute references might be helpful when copying formulas across years (we reviewed these in class this week, but you can also see Chapter 1 in the textbook on absolute vs. relative references.

1. What is the capacity level that maximizes expected (mean) NPV?
2. What is the expected (mean) NPV at the optimal capacity level you determined in Question 1? Enter your answer in dollars (not millions of dollars, for example).

AutoSave OFF Edison Motors Shell Home Insert Draw Page Layout Formulas Data Review View DADM_UT @ Tell me Calibri (Body) 12AA ce Wrap Text v Conditional Format Cell Formatting as Table Styles Comments Share Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? B28 fx Ready { Accessibility: Good to go