Construct a model with an objective function, decision variables, and constraints. Set it up on Excel Solver with values and run it. Do the results make sense? Tell me why.

You run two manufacturing lines for a car company that makes four models. This is a mix of cars and light trucks. You want to maximize revenue while satisfying demand. The facility has a max limit on annual production, with each vehicle having a cost and selling price. In addition to production, the company must comply with fuel consumption standards under various energy policies and national laws. (E.g., the average MPG of your production must be at or below 40 MPG the 2026 standard. Assume that each car model mileage is the same.)

1. The four models two cars lower end, two cars higher end

2. Each model has a cost and selling price, annual demand, mpg, production time

3. Assume that the two lines have specific operational hours/year

4. Solve

a) What are your objective function and constraints (show in Excel)

b) What is your result? $$/qty of each vehicle manufactured. Show your Solver report

c) Do you have any slack or surplus?

\begin{tabular}{|r|r|r|r|r|r|r|r|} \hline & \multicolumn{1}{|c|}{ price } & \multicolumn{1}{c|}{ cost } & \multicolumn{1}{c|}{ rev } & MPG & prod time & \multicolumn{1}{|c|}{ line } & min demand \\ \hline Corolla & 24,000 & 15,000 & 9,000 & 42 & 2 hrs & 1 & 300 \\ \hline Avalon & 60,000 & 23,000 & 37,000 & 39 & 2 hrs & 1 & 300 \\ \hline Tundra & 50,000 & 25,000 & 25,000 & 35 & 3 hrs & 2 & 300 \\ \hline RAV4 & 35,000 & 20,000 & 15,000 & 41 & 2.5 hrs & 2 & \\ \hline \end{tabular} \begin{tabular}{|r|r|} \hline total production time line 1 & 2500 \\ \hline total productio n time line 2 & 2300 \\ \hline \end{tabular}