Determine the optimal product mix with Solver (2023)

This article describes how to use Solver, a Microsoft Excel add-in program that you can use for what-if analysis to determine an optimal product mix.

How can I determine the monthly product mix that maximizes profitability?

Businesses often need to determine the amount of each product to produce each month. In its simplest form is theproduct mixThe problem is to determine the amount of each product that should be produced during a month to maximize profit. The product mix must normally comply with the following restrictions:

• The product mix cannot consume more resources than are available.

• There is a limited demand for each product. We cannot produce more of a product than demand dictates in a month because the excess production is wasted (e.g. a perishable drug).

Now let's solve the following example of the product mix problem. The solution to this problem can be found in the Prodmix.xlsx file shown in Figure 27-1.

Let's say we work for a pharmaceutical company that makes six different products in its facility. Manufacturing each product requires labor and raw materials. Row 4 in Figure 27-1 shows the labor hours required to produce one pound of each product, and row 5 shows the pounds of raw material required to produce one pound of each product. For example, making one pound of Product 1 requires six hours of labor and 3.2 pounds of raw materials. For each drug, line 6 shows the price per pound, line 7 the unit cost per pound, and line 9 the profit contribution per pound Unit cost of $5.70 per pound and contributes $5.30 profit per pound. The monthly demand for each drug is given on line 8. For example, the demand for product 3 is 1041 pounds. 4500 hours of labor and 1600 pounds of raw material available this month. How can this company maximize its monthly profit?

If we didn't know about Excel Solver, we would tackle this problem by creating a worksheet to track profit and resource usage related to product mix. Then we would use trial and error to vary the product mix to optimize profit without using more labor or raw materials than is available and without producing a drug that exceeds demand. We only use solvers in the trial and error phase of this process. Essentially, Solver is an optimization engine that performs trial and error searching flawlessly.

A key to solving the product mix problem is to efficiently calculate the resource utilization and profit associated with a given product mix. An important tool we can use for this calculation is the SUMPRODUCT function. The SUMPRODUCT function multiplies corresponding values ​​in ranges of cells and returns the sum of those values. Each range of cells used in a SUMPRODUCT evaluation must have the same dimensions, which means 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 calculate our resource usage. Our labor input is calculated according to

(Work expended per pound of Drug 1)*(Drug 1 pound produced)+
(Work expended per pound of drug 2)*(Drug 2 pounds produced) + ...
(Work expended per pound of drug 6)*(Drug 6 pounds produced)

We could calculate labor input in a more tedious way thanD2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. Likewise, raw material consumption could be calculated asD2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. However, entering these formulas into a worksheet for six products is time consuming. Imagine how long it would take if you worked with a company that makes say 50 products in their facility. A much easier way to calculate labor and raw material consumption is to copy the formula from D14 to D15SUMPRODUKT($D$2:$I$2,D4:I4). This formula calculatesD2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4(this is our workload) but much easier to type! Note that I'm using the $ sign with the range D2:I2, so when I copy the formula I'm still capturing the product mix from row 2. The formula in cell D15 calculates the raw material consumption.

Similarly, our profit is determined by

(Drug 1 profit per pound)*(Drug 1 pound produced) +
(Drug 2 profit per pound)*(Drug 2 pounds produced) + ...
(Drug 6 profit per pound)*(Drug 6 lbs produced)

The profit is calculated in cell D12 simply using the formulaSUMPRODUKT(D9:I9,$D$2:$I$2).

We can now identify the three components of our product mix solver model.

• target cell.Our goal is to maximize profit (calculated in cell D12).

  (Video) Using the Solver Add In to Find an Optimal Product Mix in Excel

• change cells.The number of pounds produced by each product (listed in cell range D2:I2)

• Restrictions.We have the following restrictions:

  • Don't put in more labor or raw materials 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 resources available).

  • Do not produce more of a drug than is required. That is, the values ​​in cells D2:I2 (pounds produced by each drug) must be less than or equal to the demand for each drug (listed in cells D8:I8).

    See Also

    Everything you need to know about usage ratesHow to Calculate Labor Costs: A Step-by-Step Guide

  • We cannot produce a negative amount of any drug.

I'll show you how to enter the target cell, modify cells, and apply constraints in Solver. Then all you have to do is click the Solve button to find a profit-maximizing product mix!

First, click the Data tab, and then click Solver in the Analysis group.

Note:As discussed in Chapter 26, “An Introduction to Optimization with Excel Solver,” Solver is installed by clicking the Microsoft Office Button, then Excel Options, and then Add-Ins. In the Manage list, click Excel Add-Ins, select the Solver Add-In check box, and then click OK.

The Solver Parameters dialog box appears (see Figure 27-2).

Click on the Set target cell box and then select our profit cell (cell D12). Click the By Changing Cells box, and then point to the D2:I2 area, which contains the pounds produced by each drug. The dialog box should now look like Figure 27-3.

We are now ready to add constraints to the model. Click the Add button. You will see the Add Constraint dialog box shown in Figure 27-4.

To add the resource usage restrictions, click the cell reference box and then select the range D14:D15. Select <= from the middle list. Click the Restriction box, and then select the cell range F14:F15. The Add Constraint dialog box should now look like Figure 27-5.

We have now made sure that the solver tries different values ​​for the changing cells, only combinations that satisfy bothD14<=F14(work expended is less than or equal to available work) andD15<=F15(the raw material used is less than or equal to the available raw material) is taken into account. Click Add to enter the demand conditions. Complete the Add Condition dialog box as shown in Figure 27-6.

Adding these constraints ensures that when Solver tries different combinations for the changing cell values, it only considers combinations that meet the following parameters:

• D2<=D8(the quantity of drug 1 produced is less than or equal to the demand for drug 1)

  (Video) 5.Solving a Product Mix Problem using Solver | Optimization using Excel

• E2<=E8(the quantity of drug 2 produced is less than or equal to the demand for drug 2)

• F2<=F8(the quantity of drug 3 produced is less than or equal to the demand for drug 3)

• G2<=G8(the quantity of drug 4 produced is less than or equal to the demand for drug 4)

• H2<=H8(the quantity of drug 5 produced is less than or equal to the demand for drug 5)

• I2<=I8(the quantity of drug 6 produced is less than or equal to the demand for drug 6)

In the Add Constraint dialog box, click OK. The solver window should look like Figure 27-7.

We enter the condition that changing cells must not be negative in the Solver Options dialog box. In the Solver Parameters dialog box, click the Options button. Check the Assume Linear Model and Assume Non-Negative boxes as shown in Figure 27-8 on the next page. click OK.

Checking the Assume Non-Negative check box ensures that Solver only considers combinations of changing cells where each changing cell assumes a non-negative value. We checked the Assume Linear Model box because the product mixture problem is a special type of solver problem called alinear model. In essence, a solver model is linear under the following conditions:

• The target cell is calculated by adding the terms of the form(changing cell)*(constant).

• Each constraint satisfies the "linear model requirement". This means that each condition is evaluated by adding 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 calculated as

(Drug 1 profit per pound)*(Drug 1 pound produced) +
(Drug 2 profit per pound)*(Drug 2 pounds produced) + ...
(Drug 6 profit per pound)*(Drug 6 lbs produced)

This calculation follows a pattern in which the value of the target cell is derived by adding terms from the form(changing cell)*(constant).

Our working constraint is evaluated by comparing the derived value(Labor per pound of Drug 1)*(Drug 1 lb produced) + (Labor per pound of Drug 2)*(Drug 2 lbs produced)+ …(Labour usper pound of drug 6)*(drug 6 pounds produced)to the available workforce.

Therefore, the work constraint is evaluated by adding 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 requirement of the linear model.

Our demand constraints are taking shape

(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 since each is evaluated by adding the terms of the form(changing cell)*(constant)and comparing the sums to a constant.

Now that we've 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 for the solver model. If a solver model is nonlinear, solver may or may not find the optimal solution.

• If a solver model is linear and we select Assume Linear Model, Solver will use a very efficient algorithm (the simplex method) to find the optimal solution of the model. If a solver model is linear and we don't select Assume Linear Model, Solver will use a very inefficient algorithm (the GRG2 method) and may have trouble finding the model's optimal solution.

After clicking OK on the Solver Options dialog, we return to the main Solver dialog shown earlier in Figure 27-7. When we click Solve, Solver calculates an optimal solution (if any) for our product mix model. As I noted in Chapter 26, an optimal solution to the product-mix model would be a set of changing cell counts (pounds produced by each drug) that maximizes profit over the set of all possible solutions. Again, a workable solution is a set of changing cell values ​​that satisfy all constraints. The changing cell values ​​shown in Figure 27-9 are a viable solution because all production levels are non-negative, production levels do not exceed demand, and resource use does not exceed available resources.

The changing cell values ​​in Figure 27-10 on the next page represent aunworkable solutionfor the following reasons:

• We produce more drug 5 than demand for it.

• We use more labor than is available.

• We consume more raw material than is available.

After you click Solve, Solver quickly finds the optimal solution, as shown in Figure 27-11. You must select Keep solver solution to keep the optimal solution values ​​in the worksheet.

Our pharmaceutical company can maximize its monthly profit at the $6,625.20 level 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 get the max win of $6,625.20 any other way. All we can be sure of is that with our limited resources and demand, there is no way we can make more than $6,627.20 this month.