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.
How to Calculate Optimal Product Mix in Excel: Step-by-Step Procedure
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, use 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
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.
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.
- Then, press Enter.
- Afterward, to calculate the Explicit Constraints – Labor Used, type the following formula.
- Then, press Enter.
- Afterward, to calculate the Explicit Constraints – Machine time Used, type the following formula.
- Then, press Enter.
Step 5: Use Excel Solver to Solve 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.
- 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.
Excel does not always have a solution. In this situation, you might need to change your constraints to get a solution.
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.
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.
Keep learning new methods and keep growing!
- How to Make Price Optimization Models in Excel
- Excel Optimization with Constraints
- How to Perform Route Optimization in Excel
- Schedule Optimization in Excel
- How to Optimize Multiple Variables in Excel
- Mean Variance Optimization in Excel