Question

A food wholesaler has three kinds of individual bags of potato chips: regular, barbeque, and salt and vinegar. She wants to sell the bags of chips in bulk packages. The bronze package consists of 20 bags of regular and 10 bags of barbeque. The silver package contains 20 bags of regular, 10 bags of barbeque, and 10 bags of salt and vinegar. The gold package consists of 30 bags of regular, 10 bags of barbeque, and 10 bags of salt and vinegar. The profit is $10 on each bronze package, $60 on each silver package, and $80 on each gold package. The food wholesaler has a total of 8000 bags of regular chips, 4900 bags of barbeque, and 1500 bags of salt and vinegar. Assume all the packages will be sold. Use the simplex method to complete parts (a) and (b). (a) How many gold, silver, and bronze packages should be made up in order to maximize profit? What is the maximum profit? Set up the linear programming problem. Let xy, Xy, and xz represent the numbers of bronze, silver, and gold packages made up, respectively, and let z be the total profit. Maximize z= 10x, +60x2 + 80X3 subject to 20x, +20x2 + 30x3 s 8000 10x, + 10x + 10x3 s 4900 10x2 + 10x3 s 1500 Enter your answer in the edit fields and then click Check Answer. part Clear All Check Answer

Answer 1

We can solve given correctly formualte LP model using Excel Solver

Set up model as follows

с D E F G н 1 x1 B 19 20 Variables 21 Values 22 co-efficient of objective function 23 Objective function value 24 25 Constraints 26 Regular 27 Barbeque 28 Salt and vinegar 29 x2 x3 0 0 0 <-----Variable value appears here 10 60 80 0 <-----Objective function value appears here x1 x2 x3 Limit 20 10 20 10 10 Resources Used 30 10 0 <a 0 <= 0 <= 8000 4900 1500 0 10 30

Excel formulas shown below

Function Library Defined Names Formula Auditing H29 fir D E F G H 1 x2 x3 0 0 <--Variable value appears here 60 80 <------Objective function val B с 19 20 Variables x1 21 Values 0 22 co-efficient of objective function 10 23 Objective function value =SUMPRODUCT(C21:E21,C22:E22) 24 25 Constraints x1 26 Regular 20 27 Barbeque 10 28 Salt and vinegar 0 29 30 x2 x3 <= 20 10 30 10 10 Resources Used =SUMPRODUCT($C$21:$E$21,C26:E26) =SUMPRODUCT($C$21:$E$21,C27:E27) =SUMPRODUCT($C$21:$E$21,C28:E28) Limit 8000 4900 1500 10 <=

Now solve using solver function from the Data tab in the menu bar:

1. Set objective as shown in the figure below to MAX

2. Add constraints as shown in the figure below from clicking the Add button on right side of solver window

3. Set the solving method to Simplex LP

4. Click Solve

File Home Insert Page Layout Formulas Data Review View Developer Connections 2 1 2 3 + Show Detail 19 Data Anal - Hide Detail ? Solver ZA TE Properties ΤΑ From Access From Web Sort Refresh From From Other Text Sources Get External Data Existing Connections Clear Reapply Filter Y Advanced Sort & Filter Tel Solver Parameters Coll All- Go Edit Links Connections lysis C23 Set Objective: $C$23 ER B с D E F G H 1 K T To: O Max O Min O Value of: 0 x1 By Changing Variable Cells: x2 x3 0 0 0 <-----Variable value appears here 10 60 80 O<-----Objective function value appears here SC$21: SE $21 ES x1 x2 x3 20 Subject to the Constraints: $C$21:$E$21 >=0 $F$26:$F$28 <= $H$26:$H$28 Resources Used 30 10 20 10 Add Limit 8000 4900 1500 0 <= 0 <= 10 10 0 10 0 <= Change Delete Reset AN 19 20 Variables 21 Values 22 co-efficient of ob 23 Objective functio 24 25 Constraints 26 Regular 27 Barbeque 28 Salt and vinegar 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 Load/Save Make Unconstrained variables Non-Negative 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 Activate Windows

Excel solution as below

Format Painter 1 Clipboard Font Alignment F33 for с D E F G H - x1 B 19 20 Variables 21 Values 22 co-efficient of ot 23 Objective functio 24 25 Constraints 26 Regular 27 Barbeque 28 Salt and vinegar 29 x2 x3 175 0 150 <----Variable value appears here 10 60 80 13750 <--Objective function value appears here X1 x2 x3 20 20 10 0 Resources Used Limit 30 8000 <= 8000 10 3250 <= 4900 10 1500 <= 1500 10 10

Therefore,

The maximum profit is $ 13750. To get that profit, 175 Bronze package, 0 Silver packages and 150 Gold packages should be made up.