Question

A company makes three models of garage doors for homes, Models A, B, and C. The next production cycle is limited to a total of 100 doors. There is only 640 gallons of paint available. Each model A requires 5 gallons of paint, each model B requires 7 gallons of paint, and each model C requires 10 gallons of paint. Marketing wants the following product mix: exactly 20 model A doors must be produced; at least 5 model B’s must be produced, and the number of Model C’s produced can be no more than twice the number of model B’s produced. (Hint: after writing this last constraint for model C, algebraically rewrite it so that only “0” (zero) is on the RHS. This must be done). In addition:

    Model                               Sales Revenue                 Cost

        A                                        $16,000                      $12,000

        B                                        18,200                        13,000

        C                                        20,000                        17,000

Determine the number of each model that must be produced so that total profit is maximized. If necessary, round only production numbers. Sensitivity report is required.

1. If any of the models are not being produced, explain why, in terms of reduced costs, why they are not being produced. Explain, very specifically, what steps would be necessary for that item(s) to be produced.
2. The manager insists on obtaining 360 more gallons of paint. What would your advice be and exactly how would this change the optimal solution? Be specific in your discussion.
3. Which resource constraints are non-binding and why?

Answer 1

Let A, B, C be the number of models produced for each Model A, Model B and Model C respectively.\

Profit A=16000-12000=4000, Profit B= 18200-13000=5200, Profit C= 20000-17000=3000

Maximize Profit Z=A*4000+B*5200+C*3000

We know A=20 exactly given

Z = 4000*20+B*5200+C*3000=80000+B*5200+C*3000

Constraints:

1. Total production is limited to 100 which means A+B+C<=100;

20+B+C<=100; i.e., Constraint 1 is B+C<=80

2. Total paint available is 640 gallons where 5,7,10 gallons are required to paint A,B,C doors repectively.

5*A+7*B+10*C<=640; which is 5*20+7*B+10*C<=640; i.e., Contraint 2 is 7*B+10*C<=540

3. Constraint 3 is B>=5

4. Constraint 4 is C<=2*B

Now i am using Excel Solver to find the solution for the LPP model. You may use Simplex method as well or any other methods and you will get the same result

We have to find the values of B and C so that the profit is maximum and constraints are followed.

Put Data in Excel as shown:

After entering the data go to the Solver option and enter the fields you want to maximize, Changing the parameter values of B and C and putting the constraints using Add option on right of Constraint box and method to solve. I have used Simplex method as shown below.

Press Solve and the values of B and C will appear as:

Since B Cannot be in fraction or decimal, we round it of to 77. Rounding off to 78 will Exceed the paint requirement to 646 from 640. So rounding off to 77 we get

So you have model B Doors =77 and Model C doors = 0 for max profit of 480400 making total 97 products and using 639 gallons of paint available.