This article discusses using Solver, a Microsoft Excel add-in program you can use for what-if analysis, to determine an optimal product mix.
How can I determine the monthly product mix that maximizes profitability?
Companies often need to determine the quantity of each product to produce on a monthly basis. In its simplest form, the product mix problem involves how to determine the amount of each product that should be produced during a month to maximize profits. Product mix must usually adhere to the following constraints:
-
Product mix can’t use more resources than are available.
-
There is a limited demand for each product. We can’t produce more of a product during a month than demand dictates, because the excess production is wasted (for example, a perishable drug).
Let’s now solve the following example of the product mix problem. You can find the solution to this problem in the file Prodmix.xlsx, shown in Figure 27-1.
Let’s say we work for a drug company that produces six different products at their plant. Production of each product requires labor and raw material. Row 4 in Figure 27-1 shows the hours of labor needed to produce a pound of each product, and row 5 shows the pounds of raw material needed to produce a pound of each product. For example, producing a pound of Product 1 requires six hours of labor and 3.2 pounds of raw material. For each drug, the price per pound is given in row 6, the unit cost per pound is given in row 7, and the profit contribution per pound is given in row 9. For example, Product 2 sells for $11.00 per pound, incurs a unit cost of $5.70 per pound, and contributes $5.30 profit per pound. The month’s demand for each drug is given in row 8. For example, demand for Product 3 is 1041 pounds. This month, 4500 hours of labor and 1600 pounds of raw material are available. How can this company maximize its monthly profit?
If we knew nothing about Excel Solver, we would attack this problem by constructing a worksheet to track profit and resource usage associated with the product mix. Then we would use trial and error to vary the product mix to optimize profit without using more labor or raw material than is available, and without producing any drug in excess of demand. We use Solver in this process only at the trial-and-error stage. Essentially, Solver is an optimization engine that flawlessly performs the trial-and-error search.
A key to solving the product mix problem is to efficiently compute the resource usage and profit associated with any given product mix. An important tool that we can use to make this computation is the SUMPRODUCT function. The SUMPRODUCT function multiplies corresponding values in cell ranges and returns the sum of those values. Each cell range used in a SUMPRODUCT evaluation must have the same dimensions, which implies that you can use SUMPRODUCT with two rows or two columns, but not with one column and one row.
As an example of how we can use the SUMPRODUCT function in our product mix example, let’s try to compute our resource usage. Our labor usage is calculated by
(Labor used per pound of drug 1)*(Drug 1 pounds produced)+ (Labor used per pound of drug 2)*(Drug 2 pounds produced) + ... (Labor used per pound of drug 6)*(Drug 6 pounds produced)
We could compute labor usage in a more tedious fashion as D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. Similarly, raw material usage could be computed as D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. However, entering these formulas in a worksheet for six products is time-consuming. Imagine how long it would take if you were working with a company that produced, for example, 50 products at their plant. A much easier way to compute labor and raw material usage is to copy from D14 to D15 the formula SUMPRODUCT($D$2:$I$2,D4:I4). This formula computes D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (which is our labor usage) but is much easier to enter! Notice that I use the $ sign with the range D2:I2 so that when I copy the formula I still capture the product mix from row 2. The formula in cell D15 computes raw material usage.
In a similar fashion, our profit is determined by
(Drug 1 profit per pound)*(Drug 1 pounds produced) + (Drug 2 profit per pound)*(Drug 2 pounds produced) + ... (Drug 6 profit per pound)*(Drug 6 pounds produced)
Profit is easily computed in cell D12 with the formula SUMPRODUCT(D9:I9,$D$2:$I$2).
We now can identify the three components of our product mix Solver model.
-
Target cell. Our goal is to maximize profit (computed in cell D12).
-
Changing cells. The number of pounds produced of each product (listed in the cell range D2:I2)
-
Constraints. We have the following constraints:
-
Do not use more labor or raw material than is available. That is, the values in cells D14:D15 (the resources used) must be less than or equal to the values in cells F14:F15 (the available resources).
-
Do not produce more of a drug than is in demand. That is, the values in the cells D2:I2 (pounds produced of each drug) must be less than or equal to the demand for each drug (listed in cells D8:I8).
-
We can’t produce a negative amount of any drug.
-
I’ll show you how to enter the target cell, changing cells, and constraints into Solver. Then all you need to do is click the Solve button to find a profit-maximizing product mix!
To begin, click the Data tab, and in the Analysis group, click Solver.
Note: As explained in Chapter 26, "An Introduction to Optimization with Excel Solver," Solver is installed by clicking the Microsoft Office Button, then Excel Options, followed by Add-Ins. In the Manage list, click Excel Add-ins, check the Solver Add-in box, and then click OK.
The Solver Parameters dialog box will appear, as shown in Figure 27-2.
Click the Set Target Cell box and then select our profit cell (cell D12). Click the By Changing Cells box and then point to the range D2:I2, which contains the pounds produced of each drug. The dialog box should now look Figure 27-3.
We’re now ready to add constraints to the model. Click the Add button. You’ll see the Add Constraint dialog box, shown in Figure 27-4.
To add the resource usage constraints, click the Cell Reference box, and then select the range D14:D15. Select <= from the middle list. Click the Constraint box, and then select the cell range F14:F15. The Add Constraint dialog box should now look like Figure 27-5.
We have now ensured that when Solver tries different values for the changing cells, only combinations that satisfy both D14<=F14 (labor used is less than or equal to labor available) and D15<=F15 (raw material used is less than or equal to raw material available) will be considered. Click Add to enter the demand constraints. Fill in the Add Constraint dialog box as shown in Figure 27-6.
Adding these constraints ensures that when Solver tries different combinations for the changing cell values, only combinations that satisfy the following parameters will be considered:
-
D2<=D8 (the amount produced of Drug 1 is less than or equal to the demand for Drug 1)
-
E2<=E8 (the amount of produced of Drug 2 is less than or equal to the demand for Drug 2)
-
F2<=F8 (the amount produced of Drug 3 made is less than or equal to the demand for Drug 3)
-
G2<=G8 (the amount produced of Drug 4 made is less than or equal to the demand for Drug 4)
-
H2<=H8 (the amount produced of Drug 5 made is less than or equal to the demand for Drug 5)
-
I2<=I8 (the amount produced of Drug 6 made is less than or equal to the demand for Drug 6)
Click OK in the Add Constraint dialog box. The Solver window should look like Figure 27-7.
We enter the constraint that changing cells must be non-negative in the Solver Options dialog box. Click the Options button in the Solver Parameters dialog box. Check the Assume Linear Model box and the Assume Non-Negative box, as shown in Figure 27-8 on the next page. Click OK.
Checking the Assume Non-Negative box ensures that Solver considers only combinations of changing cells in which each changing cell assumes a non-negative value. We checked the Assume Linear Model box because the product mix problem is a special type of Solver problem called a linear model. Essentially, a Solver model is linear under the following conditions:
-
The target cell is computed by adding together the terms of the form (changing cell)*(constant).
-
Each constraint satisfies the “linear model requirement.” This means that each constraint is evaluated by adding together the terms of the form (changing cell)*(constant) and comparing the sums to a constant.
Why is this Solver problem linear? Our target cell (profit) is computed as
(Drug 1 profit per pound)*(Drug 1 pounds produced) + (Drug 2 profit per pound)*(Drug 2 pounds produced) + ... (Drug 6 profit per pound)*(Drug 6 pounds produced)
This computation follows a pattern in which the target cell’s value is derived by adding together terms of the form (changing cell)*(constant).
Our labor constraint is evaluated by comparing the value derived from (Labor used per pound of Drug 1)*(Drug 1 pounds produced) + (Labor used per pound of Drug 2)*(Drug 2 pounds produced)+ …(Labor used per pound of Drug 6)*(Drug 6 pounds produced) to the labor available.
Therefore, the labor constraint is evaluated by adding together the terms of the form (changing cell)*(constant) and comparing the sums to a constant. Both the labor constraint and the raw material constraint satisfy the linear model requirement.
Our demand constraints take the form
(Drug 1 produced)<=(Drug 1 Demand) (Drug 2 produced)<=(Drug 2 Demand) § (Drug 6 produced)<=(Drug 6 Demand)
Each demand constraint also satisfies the linear model requirement, because each is evaluated by adding together the terms of the form (changing cell)*(constant) and comparing the sums to a constant.
Having shown that our product mix model is a linear model, why should we care?
-
If a Solver model is linear and we select Assume Linear Model, Solver is guaranteed to find the optimal solution to the Solver model. If a Solver model is not linear, Solver may or may not find the optimal solution.
-
If a Solver model is linear and we select Assume Linear Model, Solver uses a very efficient algorithm (the simplex method) to find the model’s optimal solution. If a Solver model is linear and we do not select Assume Linear Model, Solver uses a very inefficient algorithm (the GRG2 method) and might have difficulty finding the model’s optimal solution.
After clicking OK in the Solver Options dialog box, we return to the main Solver dialog box, shown earlier in Figure 27-7. When we click Solve, Solver calculates an optimal solution (if one exists) for our product mix model. As I stated in Chapter 26, an optimal solution to the product mix model would be a set of changing cell values (pounds produced of each drug) that maximizes profit over the set of all feasible solutions. Again, a feasible solution is a set of changing cell values satisfying all constraints. The changing cell values shown in Figure 27-9 are a feasible solution because all production levels are non-negative, production levels do not exceed demand, and resource usage does not exceed available resources.
The changing cell values shown in Figure 27-10 on the next page represent an infeasible solution for the following reasons:
-
We produce more of Drug 5 than the demand for it.
-
We use more labor than what is available.
-
We use more raw material than what is available.
After clicking Solve, Solver quickly finds the optimal solution shown in Figure 27-11. You need to select Keep Solver Solution to preserve the optimal solution values in the worksheet.
Our drug company can maximize its monthly profit at a level of $6,625.20 by producing 596.67 pounds of Drug 4, 1084 pounds of Drug 5, and none of the other drugs! We can’t determine if we can achieve the maximum profit of $6,625.20 in other ways. All we can be sure of is that with our limited resources and demand, there is no way to make more than $6,627.20 this month.
Suppose that demand for each product must be met. (See the No Feasible Solution worksheet in the file Prodmix.xlsx.) We then have to change our demand constraints from D2:I2<=D8:I8 to D2:I2>=D8:I8. To do this, open Solver, select the D2:I2<=D8:I8 constraint, and then click Change. The Change Constraint dialog box, shown in Figure 27-12, appears.
Select >=, and then click OK. We’ve now ensured that Solver will consider changing only cell values that meet all demands. When you click Solve, you’ll see the message "Solver could not find a feasible solution." This message does not mean that we made a mistake in our model, but rather that with our limited resources, we can’t meet demand for all products. Solver is simply telling us that if we want to meet demand for each product, we need to add more labor, more raw materials, or more of both.
Let’s see what happens if we allow unlimited demand for each product and we allow negative quantities to be produced of each drug. (You can see this Solver problem on the Set Values Do Not Converge worksheet in the file Prodmix.xlsx.) To find the optimal solution for this situation, open Solver, click the Options button, and clear the Assume Non-Negative box. In the Solver Parameters dialog box, select the demand constraint D2:I2<=D8:I8 and then click Delete to remove the constraint. When you click Solve, Solver returns the message "Set Cell Values Do Not Converge." This message means that if the target cell is to be maximized (as in our example), there are feasible solutions with arbitrarily large target cell values. (If the target cell is to be minimized, the message "Set Cell Values Do Not Converge" means there are feasible solutions with arbitrarily small target cell values.) In our situation, by allowing negative production of a drug, we in effect “create” resources that can be used to produce arbitrarily large amounts of other drugs. Given our unlimited demand, this allows us to make unlimited profits. In a real situation, we can’t make an infinite amount of money. In short, if you see “Set Values Do Not Converge,” your model does have an error.
-
Suppose our drug company can purchase up to 500 hours of labor at $1 more per hour than current labor costs. How can we maximize profit?
-
At a chip manufacturing plant, four technicians (A, B, C, and D) produce three products (Products 1, 2, and 3). This month, the chip manufacturer can sell 80 units of Product 1, 50 units of Product 2, and at most 50 units of Product 3. Technician A can make only Products 1 and 3. Technician B can make only Products 1 and 2. Technician C can make only Product 3. Technician D can make only Product 2. For each unit produced, the products contribute the following profit: Product 1, $6; Product 2, $7; and Product 3, $10. The time (in hours) each technician needs to manufacture a product is as follows:
Product
Technician A
Technician B
Technician C
Technician D
1
2
2.5
Cannot do
Cannot do
2
Cannot do
3
Cannot do
3.5
3
3
Cannot do
4
Cannot do
-
Each technician can work up to 120 hours per month. How can the chip manufacturer maximize its monthly profit? Assume a fractional number of units can be produced.
-
A computer manufacturing plant produces mice, keyboards, and video game joysticks. The per-unit profit, per-unit labor usage, monthly demand, and per-unit machine-time usage are given in the following table:
Mice
Keyboards
Joysticks
Profit/unit
$8
$11
$9
Labor usage/unit
.2 hour
.3 hour
.24 hour
Machine time/unit
.04 hour
.055 hour
.04 hour
Monthly demand
15,000
27,000
11,000
-
Each month, a total of 13,000 labor hours and 3000 hours of machine time are available. How can the manufacturer maximize its monthly profit contribution from the plant?
-
Resolve our drug example assuming that a minimum demand of 200 units for each drug must be met.
-
Jason makes diamond bracelets, necklaces, and earrings. He wants to work a maximum of 160 hours per month. He has 800 ounces of diamonds. The profit, labor time, and ounces of diamonds required to produce each product are given below. If demand for each product is unlimited, how can Jason maximize his profit?
Product
Unit profit
Labor hours per unit
Ounces of diamonds per unit
Bracelet
$300
.35
1.2
Necklace
$200
.15
.75
Earrings
$100
.05
.5
