Before you can use Excel Solver to find a solution to C&A’s product mix decision problem, you need to set up two tables. The first table contains information about the
decision variables and the objective function.
There are two decision variables in the leather belt problem, namely, BeltA and BeltB. Enter “BeltA” in cell B1 and “BeltB” in cell C1. Enter “Total” in cell D1 to form the column headings for these decision variables. For the row headings, enter “Decision” in cell A2 and “Profit” in cell A3.
Leave cells B2 and C2 empty for Excel to fill in the optimal decision variable values here. Cells B2 and C2 are called target cells.
Enter the profit margin for BeltA, i.e., “40”, in cell B3. Enter the profit margin for BeltB, i.e., “30”, in cell C3. The objective function expressed as an Excel formula is entered in cell D3 as “=B3*B2+C3*C2”.
The second table contains information about the constraints.
The column headings for the constraint table are “BeltA” in cell B5, “BeltB” in cell C5, “Used” in cell D5, and “Availability” in cell F5.
The row headings are “BuckleA” in cell A6, “BuckleB” in cell A7, “Leather” in cell A8, and “Labor” in cell A9.
The first constraint is to limit the availability of BuckleA to 400. Enter “1” in cell B6, “0” in cell C6, “<=” in cell E6, and “400” in cell F6. The actual amount of BuckleA used when the optimal amounts of leather belts are made will be computed by Excel. Thus, we will enter a formula “=B6*B2+C6*C2” in cell D6.
The second constraint is to limit the availability of BuckleB to 700. Enter “0” in cell B7, “1” in cell C7, “<=” in cell E7, and “700” in cell F7. Enter the formula “=B7*B2+C7*C2” in cell D7 to compute the actual amount of BuckleB used when the optimal amounts of leather belts are made.
The third constraint is to limit the availability of leather to 800 yards. It requires one yard of leather per belt. Thus, enter “1” in cell B8, “1” in cell “C8” and “800” in cell F8. Enter the formula “=B8*B2+C8*C2” in cell D8 to figure out the actual amount of leather consumed when the optimal amounts of leather belts are made.
The fourth constraint is to limit the availability of labor hours to 1000. It requires one hour of labor to make one BeltA and two hours of labor to make one BeltB. Thus, enter “1” in cell B9, “2” in cell C9, “<=” in cell E9, and “1000” in cell F9. Enter the formula “=B9*B2+C9*C2” in cell D9 to compute the actual amount of labor hours used when the optimal amounts of leather belts are made.
After these tables are set up, we can then start Excel Solver.
Select Tools and then Solver from the Excel menu. Select Tools and then Add-in from the Excel menu if Solver is not found in that location.
The “Solver Parameters” window will pop up.
Click on the “Set Target Cell:” parameter and select cell D3 from the worksheet. Recall cell D3 contains a formula to compute the optimal profit for C&A.
Since the objective here is to maximize profit, click on the “Max” option of the “Equal To:” parameter.
The “By Changing Cells:” parameter is the cell addresses of the decision variables. Enter B2:C2 for this parameter.
Finally, we will add to constraints in the “Subject to the Constraints:” parameter. Notice that all four constraints are of the same kind in which the total amount of resources used (i.e., D6:D9) cannot exceed its availability (i.e., F6:F9). Thus we can enter the constraints as a group. To do so, click the “Add” button to open the “Add Constraint” window. The “Cell Reference:” parameters are set by selecting cells “D6:D9”. Change the logical operator parameter to “=”. Set the “Constraint:” parameter to cell “F6:F9”. Then click “OK”.
Next we need to specify two assumptions for this problem. Click the “Options” button to open the “Solver Options” window. Check the “Assume Linear Model” and the “Assume Non-Negative” options. Then Click “OK” to return to the “Solver Parameters” window.
Now we are ready to click the “Solve” button from the “Solve Parameters” window. Select both the “Answer Report” and “Sensitivity Report” under the Reports box. Make sure the “Keep Solver Solution” option is checked before clicking the “OK” button.