In many situations, you may want to know how to calculate the optimal product mix in Excel. 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**

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.

## Step-by-Step Procedure to Calculate Optimal Product Mix in Excel

In the following section, we will use one effective and tricky method to calculate the optimal product mix in Excel. As a first step, we create a dataset, then use linear programming, and finally, apply the Excel solver to solve the problem. This section provides extensive details on this method. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the **Microsoft Office 365** version here, but you can utilize any other version according to your preference.

### Step 1: Create Dataset

Here, we will demonstrate how to calculate the optimal product mix in Excel. 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 image.

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?

### Step 2: Use 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 these steps:

**1. Identify and label 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

**2. Determine 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]**

**3. Determine 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

**4. 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

**5. Graph feasible region:**

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 the solution for the 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.

### Step 3: Activate Excel Solver

Before using the Excel Solver feature, we need to activate it first. You can access **Solver** by choosing **Data âžŞ Analyze âžŞ Solver**. Sometimes it may happen that this command isnâ€™t available, you have to install the Solver add-in using the following steps:

- First of all, choose the
**File**tab.

- Secondly, select
**Options**from the menu.

- Thus, the
**Excel Options**dialog box appears. - Here, go to the
**Add-Ins**tab. - At the bottom of the
**Excel Options**dialog box, select**Excel Add-Ins**from the**Manage**drop-down list and then click**Go**.

- Immediately, the
**Add-ins**dialog box appears. - Then, place a checkmark next to
**Solver Add-In**, and then click**OK**.

To run Excel **Solver**, just click on **Solver** in the **Analysis** group. A **Solver** Parameters dialog box will be prompted. You can see that there are three essential parts that need our input: **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 a decision variable that we can adjust or change to optimize our objective. **And Subject to the Constraints** needs us to add our explicit or implicit constraints.

### Step 4: Input Problem into Worksheet Cells

In this step, we are going to input the problem into worksheets. Here, we will use **the SUMPRODUCT function** to calculate some parameters. The following image shows you how to extract information and put the information into worksheet cells as Excel can only manipulate worksheet cells.

This range from** C5:E8** includes essential information about our problem. Cells **C10**, **D10**, and **E10** represent the number of mice, keyboards, and joysticks to be produced each month respectively. Since these numbers are to be decided, and thus, the range **C10:E10** will be left blank at the moment. Cell **C10** was filled with a formula to compute the profit. Here, you can see that the formula will be extremely complex if we have a lot of products.

In cells **C14** and **C15,** we use formulas to compute the labor and machine time that will be used. Please note that values in cells **C12**, **C14**, and **C15** are all **0**. That is because Excel treats values in blank cells (**C10**, **D10**, and **E10**) as** 0**. Another thing that needs your attention is the relationship between cells in the red square. The amount produced of each product should be less than or equal to the demand for that product. Therefore, the value in cell **C10** should be less than or equal to the value in cell **C8**. This logic is also applied to cells **D10** and **D8**. Of course, the value in cell **E8** cannot be less than the value in cell **E10** too.

Letâ€™s walk through the following steps to do the task.

- First of all, we will be setting some necessary formulas. To calculate profit, type the following formula.

`=$C$5*$C$10+$D$5*$D$10+$E$5*$E$10`

- Then, press
**Enter**.

- Afterward, to calculate the
**Explicit Constraints â€“ Labor Used**, type the following formula.

`=SUMPRODUCT($C$6:$E$6,$C$10:$E$10)`

- Then, press
**Enter**.

- Afterward, to calculate the
**Explicit Constraints â€“ Machine time Used**, type the following formula.

`=SUMPRODUCT($C$7:$E$7,$C$10:$E$10)`

- Then, press
**Enter**.

**Read More:** **Resource Allocation Model in Excel Using Solver**

### Step 5: Use Excel Solver to Solve LP Problem

Letâ€™s click on **Solve**r in the **Analysis** group to open the **Solver Parameter**s dialog box. Fill â€ś**$C$10**â€ť in the **Set Objective field** and â€ś**$C$8:$E$8**â€ť in the **By Changing Variable Cells** field.

- Now itâ€™s time to add constraints in the
**Solver Parameters**dialog box. To add a constraint, we need to click on**Add**to open**Add Constraint**dialog box. 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.

- Afterward, 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.
- Next, 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. - Finally, the
**Solver Parameters**dialog box will look like the below:

- Now, click on the
**Solve**button. The following figure shows the**Solver Results**dialog box. Once you click**OK**, your result will be displayed.

- Therefore, the solver will return the solution after we click on
**Solve**button. You can see that cells**C10:E10**are no longer blank. Values in cells**C12**,**C14**, and**C15**are no longer**0**. - Consequently, 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**.

**đź’ˇ Note:**

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

## Conclusion

Thatâ€™s the end of todayâ€™s session. I firmly believe that from now, you may learn how to calculate optimal product mix in Excel. If you have any queries or recommendations, please share them in the comments section below.

Donâ€™t forget to check our website **Exceldemy.com** for various Excel-related problems and solutions. Keep learning new methods and keep growing!