Problem 4: Module 8 (30 points)
The Exeter Company produces two basic types of dog toys. Two resources are crucial to the output of the toys: assembling hours and packaging hours. Further, only a limited quantity of type 1 toy can be sold. The linear programming model given below was formulated to represent next week’s situation.
Let, X1 = Amount of type A dog toy to be produced next week
X2 = Amount of type B dog toy to be produced next week
Maximize total contribution Z = 35 X1 + 40 X2
Subject to
Assembling hours: 4 X1 + 6 X2 48
Packaging hours: 2 X1 + 2 X2 18
Sales Potential: X1 < 6
Non-negativity: X1 0, X2 0
Use Excel Solver OR graphical method to find the optimal solution of the problem. If you use Excel Solver, please paste your output here.
Note 1: Place X1 along the horizontal axis and X2 along the vertical axi.
Note 2: Clearly mark the feasible region on the graph.
Note 3: Find the points of intersection points algebraically.
Note 4: Clearly show all steps to find the optimal solution by the graphical method.
To solve this problem using Excel Solver, follow these steps:
Step 1: Open Microsoft Excel and create a new spreadsheet.
Step 2: Set up the necessary columns and rows. In column A, label the cells from A1 to A6 as "Variables," "X1," "X2," "Assembling hours," "Packaging hours," and "Contribution." In column B, label the cells from B1 to B6 as "Coefficients," "35," "40," "4," "6," and "-1." In column C, label the cells from C1 to C6 as "RHS," "0," "0," "48," "18," and "0."
Step 3: Enter the coefficients and RHS values in the designated cells for each constraint. In cell B2, enter "35" for the coefficient of X1. In cell B3, enter "40" for the coefficient of X2. In cells B4 and B5, enter "4" and "6" for the coefficients of X1 and X2 in the assembling hours constraint. In cells B6 and C6, enter "-1" and "6" for the coefficient and RHS of the sales potential constraint. Repeat the same process for the packaging hours constraint in cells C4 and C5, entering "2" and "2" for the coefficients of X1 and X2 and "48" and "18" for the RHS values.
Step 4: Set up the objective function. In cell B7, enter the formula "=B2*B3" to calculate the contribution.
Step 5: Define the decision variables. In cell C2, enter "X1" for the decision variable. In cell C3, enter "X2" for the decision variable.
Step 6: Set up the constraints. In cell C4, enter the formula "=B4*C2+B5*C3" to represent the assembling hours constraint. In cell C5, enter the formula "=B6*C2+B7*C3" to represent the packaging hours constraint. In cell C6, enter the formula "=B7*C2" to represent the sales potential constraint.
Step 7: Apply the Solver add-in. Go to the "Data" tab and click on "Solver" in the "Analysis" group. If the Solver add-in is not installed, you may need to install it first.
Step 8: Set up the Solver parameters. In the Solver Parameters window, set the "Set Objective" field as "$C$7" (the cell containing the contribution formula). Select "Max" in the "To" field. In the "By Changing Variable Cells" field, enter "$C$2:$C$3" to specify the decision variable range.
Step 9: Add constraints. Click on "Add" to add the constraints. In the "Cell Reference" field, select "$C$4:$C$5" (the cells containing the constraint formulas). Select "<=" in the "Constraint" field. In the "Value" field, select "$C$4:$C$5" (the cells containing the RHS values). Click on "Add" again to add the sales potential constraint. In the "Cell Reference" field, select "C6" (the cell containing the sales potential formula). Select "<=" in the "Constraint" field and enter "0" in the "Value" field.
Step 10: Solve the problem. Click on "OK" to close the Solver Parameters window. Click on "Solve" in the Solver dialog box to find the optimal solution. Select "Keep Solver Solution" and click on "OK" to return to the spreadsheet.
Step 11: Interpret the results. The values for X1 and X2 in cells C2 and C3 represent the optimal amounts of each dog toy to be produced. The maximum total contribution Z is displayed in cell C7.
To solve the problem using the graphical method, follow these steps:
Step 1: Draw the axes. Draw a horizontal x-axis with the label "X1" and a vertical y-axis with the label "X2."
Step 2: Plot the constraints. Start with the assembling hours constraint. Plot the line 4X1 + 6X2 = 48 by choosing two points and connecting them with a straight line. Repeat the same process for the packaging hours constraint, 2X1 + 2X2 = 18, and the sales potential constraint, X1 = 6.
Step 3: Determine the feasible region. Shade the region that satisfies all the constraints. The feasible region is the intersection of the shaded areas from the constraint lines.
Step 4: Identify the corner points. Find the points of intersection between the constraint lines to determine the corner points of the feasible region.
Step 5: Evaluate the objective function. Calculate the total contribution Z = 35X1 + 40X2 for each corner point. The highest value of Z represents the optimal solution.
Step 6: Determine the optimal solution. Identify the corner point with the highest Z value. This point represents the optimal solution showing the amount of each dog toy to be produced.
By following these steps, you can find the optimal solution to the linear programming problem either using Excel Solver or the graphical method.