How Does Car Type and Investment Limit Affect Dealership Profit Maximization?

[jianfeyyu]

Homework Statement

A manager of an automobile dealership must decide how many cars to order for the new model year in order to maximize his profit. There are two types of cars: midsize cars and compact cars. The selling price and costs are listed in the following table:

Car type -- Selling price -- Cost

Midsize -- 25,000−−18,178

Compact -- 18,000−−12,000
No more than $790,000 can be invested. The manager wants at least 10 of each type, but no more than 50 of the midsize cars and no more than 60 of the compact cars. (The manager assumes that as long as he meets these quantity limits, he will be able to sell all the cars that he orders.)

1.Formulate a linear programming model for this problem. That is, write out an algebraic statement of the model.
2.Create a spreadsheet model for this problem and solve it using Solver. What is the optimal solution and resulting profit?
3.The selling price of the midsize car might be as low as 22,000 or as high as 27,000. How does your solution found in #2 change with these prices?

Homework Equations

max: z = (25000 - 18178)x + (18000 - 12000)y
= 6822x+6000y
18178x + 12000y <= 790000
x>=10
x<=50
y>=10
Y<=60
x,y>=0

The Attempt at a Solution

I tired to set up the equation already. However, I spent a whole day to figure out the Constraints' part in the Excel. Can someone help?

 

[adjacent]

Please fix your picture

 

[jianfeyyu]

what's wrong with the picture?

 

[adjacent]

what's wrong with the picture?

It's not shown.
Use the attachment function to attach the picture.It seems the site(MathHelpForum) does not allow non-members to see the picture!

 

[D H]

Do you still need help? Based on the edit, it appears that you have correctly solved the problem.

 

[jianfeyyu]

yes, i think the D 22- D 25 should not be zero. Can you show me your calculation for B22 to D 25 and the excel solever?

 

[D H]

yes, i think the D 22- D 25 should not be zero.

Two questions with regard to this:
1. What do you mean by this?
2. Why do you think this is the case?

Can you show me your calculation for B22 to D 25 and the excel solever?

No, that would be against the rules of this site.

What happens in your spreadsheet when you change the selling price of the midsize car from $25,000 to $22,000? When you change it from $25,000 to $27,000?

Here are two more questions that might help you understand what's going on:
- What happens when you change the midsize selling price from $25,000 to $28,000?
- What happens when you change it from $25,000 to $27,267?

If you've set up your spreadsheet nicely, all you have to do is change one value and rerun the solver to find the answers to each of these four questions.

It turns out that this problem is easy to solve by a simple calculator. A number of the constraints are superfluous. Since you already know that your x and y must each be at least ten means the constraints that x and y are non-negative are superfluous. If the manager orders 60 compacts, that leaves room for only 3.85 midsize cars in the $790,000 budget. If the manager orders 50 midsize cars, he's already blown the $790,000 budget by $118,900. The 60 car limit on compacts and the 50 car limit on midsize cars are both superfluous. That leaves three constraints: The $790,000 budget and the desires to have at least ten midsize cars and and least ten compacts.

This means the feasibility region is a triangle with one of the vertices representing ordering the minimum, and the other two vertices represent ordering ten cars of one type and maxing out that $790,000 budget on the other type. That vertex with ten cars each obviously is not the solution.

Note that changing the selling price doesn't change the feasibility region one iota. The same feasibility region applies to all of the subquestions.

Note also that your solution has the manager buying 50.685 compacts. How do you buy 50 cars plus 68.5% of another car?