Question

Amanager must decide on the mix of products to produce for the coming week. Product Arequires three minutes per unit for molding, two minutes per unit for painting, and one minute for packing. Product B requires two minutes per unit for molding, four minutes for painting, and three minutes per unit for packing. There will be 550 minutes available for molding, 500 minutes for painting, and 450 minutes for packing. Product A has a contribution of $1.50 per unit and Product B is $2.50 per unit. Answer the following questions: a. What combination of A and B will maximize contribution? [Select] b. What is the maximum possible contribution? $ [Select] c. Are there any unused resources? [Select] d. Which constraint has unused resources, if any? [Select ] e. If you were to increase painting by 10 minutes, how much would the profit increase? [Select]

Answer 1

Below is the screenshot of the LP table -

Page Layout Formulas Data File Home Insert A Cut En Copy Paste Format Painter Clipboard Calibri - V11 VÀ B I J K A Font A AB 550 Constraints Molding Painting Packing 500 450 Contribution 0

Below is the screenshot of the formula applied -

2020 - Excel ESSE File Home Insert Page Layout Formulas Data Review View Help * Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Function - * Time Reference Trigº Functions Manager Function Library fx F ? A .l le - Tell me what you want to Define Name Ft Use in Formula Create from Selection Defined Names X , fx A Constraints Molding Painting Packing =C2*3+D2*2 =C2*2+D2*4 =C2*1+D2*3 450 Contribution =C2*1.5+D2*2.5

Below is the screenshot of the solver -

Solver Parameters ES File Home Insert Page Layout Formulas Data a Show Queries Connections From Table Properties Get External New Refresh Data Query - Le Recent Sources All Edit Links Get & Transform Connections Set Objective $C$10 To: O Max Min O Value of: oreca By Changing Variable Cells: SCS2:5052 - X FA A B Subject to the Constraints: SC$2:$D$2 >= 0 SCS5:5C57 <= SE$5:SE$7 Add Change Delete Constraints Molding Painting Packing AAA 550 500 450 Reset All Load/Save Make Unconstrained Variables Non-Negative Contribution Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Below is the screenshot of the result -

Soes File Home Insert Page Layout Formulas Data Show Queries À Connectio From Table E Properties Get External New Refresh Data Query - Le Recent Sources All Edit Links Get & Transform Connections хv ft A AB А ТВ 150 Constraints Molding Painting Packing 550 500 300 550 500 450 Contribution 350

From above -

a.

A = 150 units

B = 50 units

b.

Contribution = 350

c.

Only painting has the surplus.

d.

Surplus of Painting = 450 - 300 = 150 minutes

e.

Since painting has already surplus available hence increasing time is painting will not change the optimal solution.

Profit will remain unchanged.