Using Excel Solver to Determine the Optimal Product Mixtures

In many situations, we may want to find the best way to do something. For example, companies often need to determine the amount of each product that should be produced during a month to maximize profits on a monthly basis. There are two constraints when making product mix decisions. Resources like labor and raw materials are limited. Another constraint is that we cannot produce more products than demand dictates as the excess productions will be wasted. Here we have both objectives and requirements. This is a typical situation where mathematical programming (mathematical optimization) can be used. In fact, Linear Programming (LP) (also known as linear optimization and a special case of mathematical programming) will be applied in this article since the requirements can be represented by linear relationships.

Read More: Use Excel Solver to Determine Which Projects should be Undertaken

1. The problem to be solved in this article

Before introducing what is LP, let’s take a look at the problem we are going to solve today. Suppose that 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 0.2hour 0.3hour 0.24hour
Machine time/unit 0.04hour 0.55hour 0.04hour
Monthly demand 15,000 29,000 11,000

Each month, a total of 13,000 labor hours and 3,000 hours of machine time are available. How can the manufacturer maximize its monthly profit contribution from the plant?

2. How to solve above problem using Linear Programming

Linear Programming (LP) is a technique for the optimization of a linear objective function while subject to linear equality or linear inequality constraints. To solve the above problem, we need to develop an LP model by following steps:

  1. Identify and label the decision variable

In our example, we need to determine how many mice, keyboards, and joysticks are to be produced each month.

A Number of mice to be produced each month
B Number of keyboards to be produced each month
C Number of joysticks to be produced each month
  1. Determine the objective and use the decision variable to write an expression for the objective function.

Maximize profit where profit = 8A + 11B+9C

Read more: Excel Solver with a Case Study [Rate Your Sports Team]

  1. Determine the explicit constraints and write a functional expression for each of them. Explicit constraints are those that are explicitly given in the problem statement.
Labor hour constraint 0.2A + 0.3B + 0.24C <= 13000
Machine time constraint 0.04A + 0.55B +0.04c <= 29000
  1. Determine the implicit constraints.

Implicit constraints are not explicitly given in the problem statement. They are typically associated with “natural” or “common sense” restrictions on the decision variable.

Mice 0 <= A <= 15000
Keyboards 0 <= B <= 29000
Joysticks 0 <= C <= 11000
  1. Graph the feasible region by plotting the line associated with each of the linear inequality constraints. Draw in the vector representing the gradient of the objective function. Place a straight-edge perpendicular to the gradient vector and move the straight-edge in the direction of the gradient vector for maximization (or in the opposite direction of the gradient vector for minimization) to the last point for which the straight-edge intersects the feasible region. Compute the resulting optimal value associated with these points.

Read More: Solving Transportation or Distribution Problems using Excel Solver

It is not easy to draw an LP graph with three variables, therefore, I will not show you how to draw the feasible region and compute solution for above problem. And that’s the very reason why I will introduce Microsoft Excel Solver. It can help you solve an LP problem with more than two variables without having to draw an LP graph.

3. How to activate Excel Solver

Before using the Excel Solver feature, we need to activate it first. Click the File tab, choose Options to open the Excel Options dialog box. Then click Add-Ins, select Solver Add-In, and click Go at the bottom of the dialog box to open the Add-Ins dialog box. Select the Solver Add-In check box in the Add-Ins dialog box and click Ok.

How to activate Excel Solver Image 1

How to activate Excel Solver [click on the image to get a full view]

Following Figure 1.2 shows you the difference on the Data tab before and after activating Excel Solver.
How to activate Excel Solver Image 2

Data tab before and after activating Solver Add-in [click on the image to get a full view]

To run Excel Solver, just click on Solver in the Analysis group marked in the lower part of Figure 1.2. A Solver Parameters dialog box similar to Figure 1.3 will be prompted. You can see that there are three essential parts that need our inputs: Set Objective, By Changing Variable Cells and Subject to the Constraints. When looking back at the LP model, you will find that Set Objective represents our objective (maximize profit in our case). By Changing Variable Cells represents decision variable which we can adjust or change to optimize our objective. And Subject to the Constraints needs us to add our explicit or implicit constraints.
Using Solver to Determine the Optimal Product Mix Image 3

Figure 1.3

4. How to put our problem into worksheet cells

Here Figure 2.1 shows you how to extract information and put the information into worksheet cells as Excel can only manipulate on worksheet cells.

Using Solver to Determine the Optimal Product Mix Image 4

Figure 2.1

Range C3:E6 includes essential information about our problem. Cells C8, D8, E8 represent a number of mice, keyboards, and Joysticks to be produced each month respectively. Since these numbers are to be decided and thus range C8:E8 will be left blank at the moment. Cell C10 was filled into the formula “=$C$3*$C$8+$D$3*$D$8+$E$3*$E$8” to compute the profit. Here you can see that the formula will be extremely complex if we have a lot of products. In order to avoid manual work and prevent from exposing you to a high risk of making mistakes, we will use SUMPRODUCT function in cell C12 (“=SUMPRODUCT($C$4:$E$4,$C$8:$E$8)”) and C13 (“=SUMPRODUCT($C$5:$E$5,$C$8:$E$8)”) to compute labor and machine time that will be used. You can write a similar function using the SUMPRODUCT function in cell C10 by yourself. Please note that values in cells C10, C12, C13 are all 0. That is because Excel treats values in blank cells (C8, D8, and E8) as 0. Another thing that needs your attention is the relationship between cells in the yellow square. The amount produced of each product should be less than or equal to the demand for that product. Therefore, the value in cell C8 should be less than or equal to the value in cell C6 . This logic is also applied to cells D8 and D6. Of course, the value in cell E6 cannot be less than the value in cell E8 too.

Read More: Resource Allocation Model in Excel Using Solver

5. How to use Excel Solver to solve our LP problem

Let’s click on Solver in the Analysis group to open the Solver Parameters dialog box. Fill “$C$10” in the Set Objective field and “$C$8:$E$8” in the By Changing Variable Cells field.

Using Solver to Determine the Optimal Product Mix Image 3.1

Figure 3.1 [click on the image to get a full view]

Now it’s time to add constraints in the Solver Parameters dialog box. To add a constraint, we need to click on Add (in Figure 3.1) to open Add Constraint dialog box (see Figure 3.2). In the prompted dialog box, fill our first explicit constraint – labor used must be less than or equal to 13,000. And then click OK to finish.
Using Solver to Determine the Optimal Product Mix Image 3.2

Figure 3.2 [click on the image to get a full view]

Use the same approach to add our second explicit constraint and implicit constraints discussed in section 4 – The amount produced of each product should be less than or equal to the demand of that product. Finally, the Solver Parameters dialog box will look like below:
Using Solver to Determine the Optimal Product Mix Image 3.3

Figure 3.3

If you look at section 2 closely, you will notice that we forgot to add one implicit constraint – the amount of each product should be non-negative. That’s the reason why we selected the “Make Unconstrained Variable Non-Negative” check box instead. This can ensure that all the changing cells are forced to be greater than or equal to 0. Since our constraints can be represented by linear relationships, we will choose Simplex LP in the Select a Solving Method field.

The solver will return the solution (see Figure 3.4) after we click on Solve button. You can see that cells C8:E8 are no longer blank. Values in Cells C10, C12, and C13 are no longer 0. However, there is one problem. The number in D8 is not an integer. This reminds me that I forgot another implicit constraint – the number in C8:E8 should be integers.

Using Solver to Determine the Optimal Product Mix Image 3.4

Figure 3.4 [click on the image to get a full view]

Figure 3.5 shows you how to add the above constraint – number in C8:E8 should be integers. By just selecting int (in left part), the integer will be filled into Constraint field automatically.
Using Solver to Determine the Optimal Product Mix Image 3.5

Figure 3.5 [click on the image to get a full view]

After adding the constraint, we got a solution as below in Figure 3.6. It tells that you can get maximum profit by producing about 15,000 mice, 3563 keyboards, and 11,000 joysticks. The maximum profit will be $258,193. After clicking on OK, the solution will be kept in the worksheet.
Using Solver to Determine the Optimal Product Mix Image 3.6

Figure 3.6 [click on the image to get a full view]

Remark

Excel does not always have a solution. In this situation, you might need to change your constraints to get a solution.

Download working file

Download the working file from the link below.

Zhiping Yan

Zhiping Yan

I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as a SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet via programming. This will save me a lot of time. I am keen to learn new things.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo