An automobile manufacturer produces four types of cars: compact, crossover suv (cuv), intermediate, and luxury. ACA also produces trucks and suv's (built on truck platforms). Due to vendor constraints, the manufacturer's production capacity is 1.2 million autos annually. Compacts and cuv's are built together in a facility with a capacity of 620,000 total units per year. Intermediate and luxury cars are produced in a second facility with capacity of 400,000 total units; and the truck/suv facility has a capacity of 275,000. The company's marketing strategy requires that compacts and cuv's will make up 50% or more of the product mix for the four car types. The Corporate Average Fuel Economy (CAFE) standards in the Energy Policy and Conservation Act require an average fuel economy across all six models of at least 27 mpg.
Profit margins, market potential, and fuel efficiencies are summarized here:

Build an LP to maximize the manufacturer's profit and in less than one page, answer the questions below. Submit both your Excel and Document files.

What is the manufacturer's optimal profit?
What can you say about the optimal allocation?
How much would optimal annual profits drop if the fuel economy requirement were raised to 28 mpg?

Note 1: Calculating the avg fuel economy of the fleet is a "weighted average" calculation, which would require dividing by Decision Variables (DVs). But this will cause your model to no longer be a "Linear" Program and make it non-linear instead. Linear programs are preferred due to their lightning-fast solution speeds and their guarantee of finding the optimal solution (neither of which is necessarily true for non-linear programs). 

For this problem, you will use a clever constraint substitution for the fuel economy requirement. Rather than actually calculating the avg fuel economy and constraining it to be at least 27 mpg, you will:

1. Subtract the required average (27) from the fuel economy of each vehicle class. For example, for compacts, you get 40 - 27 = 13, and for SUVs, you get 25 - 27 = -2. In effect, this "shifts" the requirement to "the average of the shifted mpg's should be at least 0." 
2. Now that the minimum allowed average is shifted to 0, we don't have to actually do the division part of the calculation. "Why not?" you ask. Because it is enough to know that the numerator part of the calculation is not negative to conclude that the average will also not be negative. 

While we haven't actually calculated the average mpg, we have guaranteed that it will be at least 27 mpg, and we've done it while keeping our model as an LP!

Note 2: The Product Mix constraint could also potentially be written in a way that includes dividing by DVs: (Compacts + CUV's)/SUM(the four car type pes) >= 50%. This one is easier than the Fuel Economy constraint to re-write in a way to avoid that. There are many possible ways to do it and you should be able to discover one on your own. Just think about what needs to happen for comp+cuv's to be more than 50% of the total.

Note 3: It is good form (and a requirement for Solver on a Mac to work properly) to not include anyDecision Variables in the RHS of your constraints, directly or even indirectly (which happens when the cell reference in the constraint's RHS does not reference a DV but does reference another cell which itself references a DV). This is simple to avoid. If the natural way to formulate a constraint is, for example, DV1 <= DV2, then re-write it as DV1 - DV2 <= 0. You may or may not run into this issue, depending on how you first formulate the Product Mix constraint discussed in Note 2.