This article describes how to use Solver, a Microsoft Excel addin program that you can use for whatif 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 271.
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 271 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).

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 profitmaximizing 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 AddIns. In the Manage list, click Excel AddIns, select the Solver AddIn check box, and then click OK.
The Solver Parameters dialog box appears (see Figure 272).
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 273.
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 274.
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 275.
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 276.
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 277.
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 NonNegative boxes as shown in Figure 278 on the next page. click OK.
Checking the Assume NonNegative check box ensures that Solver only considers combinations of changing cells where each changing cell assumes a nonnegative 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 277. 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 productmix 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 279 are a viable solution because all production levels are nonnegative, production levels do not exceed demand, and resource use does not exceed available resources.
The changing cell values in Figure 2710 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 2711. 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.
Suppose the demand for each productmustbe met (See theNo viable solutionworksheet in the Prodmix.xlsx file.) We then need to change our demand constraintsD2:I2<=D8:I8ToD2:I2>=D8:I8. To do this, open Solver, select the constraint D2:I2<=D8:I8, and then click Modify. The Modify Constraint dialog box (see Figure 2712) appears.
Select >= and then click OK. We have now ensured that solver only changes cell values that meet all requirements. When you click solve, the message "Solver could not find a workable solution" appears. This notification does not mean that we have made a mistake in our model, but that we cannot meet the demand for all products with our limited resources. Solver simply tells us that if we want to meet the 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 of each drug to be produced. (You can see this solver problem on the pageSpecified values do not convergeworksheet in the Prodmix.xlsx file.) To find the optimal solution for this situation, open Solver, click the Options button, and clear the Assume nonnegative check 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 "Specified cell values do not converge." This message means that if the target cell is to be maximized (as in our example), there are viable solutions with arbitrarily large target cell values. (If the target cell is to be minimized, the message “Specified cell values do not converge” means that there are feasible solutions with arbitrarily small target cell values.) In our situation, by allowing the negative production of a drug, we are actually “creating resources, out which any number of other drugs can be produced. Given our unlimited demand, this allows us to make unlimited profits. In a real situation, we cannot make infinite amounts of money. In short, if you see "Specified values do not converge", your model is defective.

Suppose our pharmaceutical company can purchase up to 500 hours of labor at a cost of $1 per hour more than current labor costs. How can we maximize profit?

In a chip factory, four technicians (A, B, C, and D) produce three products (products 1, 2, and 3). In this month, the chipmaker can sell 80 units of Product 1, 50 units of Product 2, and at most 50 units of Product 3. Technician A can only produce products 1 and 3. Technician B can only produce products 1 and 2. Technician C can only craft Product 3. Technician D can only craft Product 2. For each unit produced, the products contribute to the following profit: Product $1.6; Product $2.7; and product $3.10. The time (in hours) it takes each technician to produce a product is as follows:
Product
Technician A
Technician B
Technician C
technician d
1
2
2.5
Can not
Can not
2
Can not
3
Can not
3.5
3
3
Can not
4
Can not

Each technician can work up to 120 hours per month. How can the chipmaker maximize its monthly profit? Assume that a fractional number of units can be produced.

A computer factory makes mice, keyboards, and joysticks for video games. The profit per unit, labor input per unit, monthly requirement, and machine time utilization per unit are given in the following table:
mice
keyboards
Joysticks
profit/unit
$8
$11
$9
labor input/unit
.2 hours
.3 hours
.24 hours
machine time/unit
0.04 hour
0.055 hour
0.04 hour
Monthly demand
15.000
27.000
11.000

A total of 13,000 working hours and 3,000 hours of machine time are available every month. How can the manufacturer maximize its monthly profit contribution from the plant?
(Video) Optimum Product Mix by Using Excel 
Solve our drug example assuming that each drug has a minimum requirement of 200 units.

Jason makes diamond bracelets, necklaces and earrings. He wants to work a maximum of 160 hours a month. He has 800 ounces of diamonds. The profit, labor, and ounces of diamonds required to craft each product are given below. If the demand for any product is unlimited, how can Jason maximize his profit?
Product
unit win
working hours per unit
Ounces of diamonds per unit
Armband
$300
.35
1.2
Necklace
$200
.15
.75
earrings
$100
.05
.5
FAQs
How do I find optimal solution in Excel Solver? ›
Step through Solver trial solutions
After you define a problem, in the Solver Parameters dialog box, click Options. Select the Show Iteration Results check box to see the values of each trial solution, and then click OK. In the Solver Parameters dialog box, click Solve.
 Define and solve a problem. A list of reports appears in the Reports box of the Solver Results dialog box. ...
 Click to select one or more reports. ...
 Select any other options you want in the Solver Results dialog box, and click OK.
An optimal mix augments the potential unit deals while keeping up with or in a perfect world working on the organisation's profit. For instance, a blend that outcomes in the highest sales for the coming year may not set the organization up for future development. Also see: Product Mix. MCQs on Marketing Mix.
How do you calculate product mix? ›Number of actual units sold. Actual sales mix percentage: the number of actual units sold of a product divided by total units sold of all products. Budgeted sales mix percentage: the number of budgeted units sold of a product divided by budgeted total units sold of all products.
What is a Solver answer report? ›The Answer Report records the message that appeared in the Solver Results dialog, the Solving method used to solve the problem, Solver Option settings, and statistics such as the time, iterations and subproblems required to solve the problem.
What is Solver in Excel with example? ›Excel Solver allows specifying up to 200 variable cells. In this example, we have a couple of cells whose values can be changed: Projected clients per month (B4) that should be less than or equal to 50; and. Cost per service (B5) that we want Excel Solver to calculate.
What three things do you need to specify before running Solver? ›What must I do to use a solver? To use a solver, you must build a model that specifies: The resources to be used, using decision variables ♦ The limits on resource usage, called constraints, and ♦ The measure to optimize, called the objective.
What are the 2 ways to determine profit maximization output? ›A manager maximizes profit when the value of the last unit of product (marginal revenue) equals the cost of producing the last unit of production (marginal cost). Maximum profit is the level of output where MC equals MR.
What are the examples of product mix? ›Example of a Product Mix
For simplicity, assume that CocaCola oversees two product lines – soft drinks and juice (Minute Maid). Products classified as soft drinks are CocaCola, Fanta, Sprite, Diet Coke, Coke Zero, and products classified as Minute Maid juice are Guava, Orange, Mango, and Mixed Fruit.
If there is a solution y to the system AT y = cB such that AT y ≤ c, then x is optimal. By = cB and AT y ≤ c. m i=1 aijyi = ci. are obeyed, then x and y must be optimal.
How do you find the optimal optimization solution? ›
Optimal Solution: The optimal solution to an optimization problem is given by the values of the decision variables that attain the maximum (or minimum) value of the objective function over the feasible region. In problem P above, the point x∗ is an optimal solution to P if x∗ ∈ X and f(x∗) ≥ f(x) for all x ∈ X.
Which method is used to find the optimal solution? ›Explanation: MODI method is used for obtaining optimal solution of Transportation Problem .
What is optimal production method? ›The OPT method (Optimized Production Technology) is a method of production flow management. It is essentially based on the identification and the removal of bottle necks, origin of useless stocks (mudas) in the production line.
What is product mix in marketing with example? ›Product Mix, another name as Product Assortment, refers to several products that a company offers to its customers. For example, a company might sell multiple lines of products, with the product lines being fairly similar, such as toothpaste, toothbrush, or mouthwash, and also other such toiletries.
What factors determine product mix? › Profitability: Every business unit tries to maximize its profits. ...
 Objectives and Policy of Company: Company frames its product mix to achieve its objective. ...
 Production Capacity: ...
 Demand: ...
 Production Costs: ...
 Government Rules and Restriction: ...
 Demand Fluctuation: ...
 Competition:
The MIX is then simply: the total variance, minus price variance, minus volume variance, minus new products, minus discontinued products.
How do I use Solver table in Excel? › Click the File tab, click Options, and then click the Addins category.
 In the Manage box, click Excel Addins, and then click Go.
 In the Addins available box, select the Solver Addin check box. ...
 Now on the Data tab, in the Analysis group, you should see the Solver command.
Solver determines the results based on successive answers differing by a small (and user adjustable) percentage, exceeding a maximum number of iterations or exceeding a maximum time limit. As such, it is sensitive to the starting guesses.
What is the most important step you need to do before using Solver? ›Before using the Solver, you have to build a clear model for the problem that you want to solve. This helps both you and Excel understand the problem you are facing, its constraints & objectives.
What are the three Solver components known as? › Objectives: When working with the Solver tool, your objective is the number that you want the equation to output. ...
 Variables: Variables are the numbers that the Solver tool solves for. ...
 Constraints: The constraints are any limitations you want to add to the problem.
What algorithm does Solver use? ›
The Solver uses the GRG (Generalized Reduced Gradient) algorithm  one of the most robust nonlinear programming methods  to solve problems whenever the Assume Linear Model box in the Solver Options dialog is unchecked. (When the box is checked, the Solver uses the Simplex method for linear programming problems.)
How do you calculate optimal price in Excel? › Minimum price is our (x)
 The demand is calculated (y) = 74,351*0,42+119,36.
 Cost per unit is given (0,24)
 Profit per unit is price per unit – cost per unit.
 Total profit is estimated demand (y) * profit per unit, meaning 88*0,18 = 16 (15,64 to be accurate)
The profit maximization formula depends on profit = Total revenue – Total cost. Therefore, a firm maximizes profit when MR = MC, which is the first order, and the second order depends on the first order. This concept differs from wealth maximization in terms of duration for earning profit and the firm's goals.
How do you maximize profit example? › Find cheaper raw materials than those currently used.
 Find a supplier that offers better rates for inventory purchases.
 Find product sources with lower shipping fees.
 Reduce labor costs.
 Profit = Price x Volume – Total Costs.
 Total Costs = Total Fixed Cost + Variable costs.
 Profit = Price x Volume – Costs.
In a perfectly competitive market, MR is equal to the market price P for all levels of output. These points imply that a perfectly competitive firm will maximize profit by producing output where P = MC.
What are three strategies you can use in order to maximize your profit? ›There are 3 main ways to improve the profitability of your company: Sell more, price higher and reduce costs. But profits can also be increased by greater cost efficiency.
What are the 3 product mix strategies? ›Major elements of a product mix
Breadth: The number of product lines under a company. Depth: The options available in a particular product line, such as different quantities, sizes, etc. Consistency: How closely related product lines are to one another in their use, production, and distribution channels.
Captive product pricing – complementary products. Product line pricing – the products in the product line. Product bundle pricing – several products. Optional product pricing – optional or accessory products.
What are the 4 elements of the product mix? ›The product mix incorporates four normal components: Length, depth, breadth, and consistency.
How do you calculate optimal? ›
Calculate Your Optimal Order Quantity
The formula you need to calculate optimal order quantity is: [2 * (Annual Usage in Units * Setup Cost) / Annual Carrying Cost per Unit]^(1/2).
An optimal solution is a feasible solution where the objective function reaches its maximum (or minimum) value – for example, the most profit or the least cost. A globally optimal solution is one where there are no other feasible solutions with better objective function values.
How do you find the optimal price for a product? › Profit = Price x Volume – Total Costs.
 Total Costs = Total Fixed Cost + Variable costs.
 Profit = Price x Volume – Costs.
Optimal order quantity example
Your company pays $4 per unit to hold these candles in inventory, and the order cost comes in at $2 per purchase. In this scenario, the optimal order quantity = the square root of (2 x 1,000 candles x $2 order cost) / ($4 holding cost). When rounded, you should get an answer of 31.6.
Therefore, in order to get the optimal production quantity we need to set holding cost per year equal to ordering cost per year and solve for quantity (Q), which is the EPQ formula mentioned below. Ordering this quantity will result in the lowest total inventory cost per year.
What is optimal profit formula? ›The profit maximization formula depends on profit = Total revenue – Total cost. Therefore, a firm maximizes profit when MR = MC, which is the first order, and the second order depends on the first order. This concept differs from wealth maximization in terms of duration for earning profit and the firm's goals.
How do you find the optimal solution on a graph? › Step 1: Formulate the LP (Linear programming) problem. ...
 Step 2: Construct a graph and plot the constraint lines. ...
 Step 3: Determine the valid side of each constraint line. ...
 Step 4: Identify the feasible solution region. ...
 Step 5: Plot the objective function on the graph. ...
 Step 6: Find the optimum point.
Example 1: Solve the following linear programming problem using the graphical method. Solution: Using the constraints we get the equations of the lines as 4x + y = 40 and 2x + 3y = 90. As the minimum value of Z is 127, thus, B (3, 28) gives the optimal solution.
What is basic solution and optimal solution? ›A nonnegative vector of variables that satisfies the constraints of (P) is called a feasible solution to the linear programming problem. A feasible solution that minimizes the objective function is called an optimal solution. A.2 BASIS AND BASIC SOLUTIONS.
Is optimal solution the best solution? ›It is true that optimal solution is the best possible solution at a particular point in time based upon an existing constraints. The resources in attaining the optimal solution have been fully employed and no more can be employed without to make the solution better.
How do you find the optimal value of an objective function? ›
The objective function is of the form Z = ax + by, where x, y are the decision variables. The function Z = ax + by is to be maximized or minimized to find the optimal solution. Here the objective function is governed by the constraints x > 0, y > 0.
What is an optimal solution to in a linear optimization model? ›Definition: An optimal solution to a linear program is the feasible solution with the largest objective function value (for a maximization problem).