Analysis $2-$ Extended Break Even Analysis

$2$a $-$ Capacity Issues$)$ Modify your model to take the following complication into

account. If SPEPtedictedDemand $($from the SPF$)$ is greater than $1000$ but less

than or equal to $1200$ we have to use overtime to be able to meet dernand. In this

case, we decide to model this by increasing the unit production cost from $$90$ to

$$110$ for all units of demand above $1000$ and less than or equal to $1200.$ So$,$

if demand is equal to $1050,$ we pay $$90$ unit production cost on the first $1000$

units and pay $$110$ on the last $50$ units. If demand is greater than $1200,$ we can

only satisfy demand for $1200$ units and we simply lose all the demand above that.

Of course, we have to pay the overtime costs for all those above $1000$ and less

than or equal to $1200,$ as just described.

IMPORTANT: In making your model modifications to address the capacity issues

you MUST NOT hard code any of the values such as the $1000,$ the $1200$ or the

$$110$ into any formulas. Numeric inputs such as these are base inputs that

should be put in cells and then referenced by formulas. For example, here's

what that part of my model looks like:

After making the necessary model modifications:

modify the one way data table to also include the number of units sold

use Goal Seek to find the new break even selling price.

Write a few short comments describing the differences in the results obtained

with this extended model as compared to the original base model. For example,