Optimizing batch mixes to full-fill requirement on full mix in excel formula is a problem that involves finding the best combination of batches that meets the desired specifications for the final mix, while minimizing the cost, waste, or other criteria. This is an example of a linear programming problem, which can be solved using the Solver tool in Excel.
The basic theory of linear programming is to formulate the problem as a mathematical model, which consists of three main components:
- Decision variables: These are the unknown values that we want to find, such as the amount of each batch to use in the mix.
- Objective function: This is the expression that we want to maximize or minimize, such as the total cost or profit of the mix.
- Constraints: These are the limitations or requirements that the decision variables must satisfy, such as the availability of resources, the demand for the product, or the quality standards for the mix.
The procedures for solving a linear programming problem using Solver are as follows:
- Define the decision variables and enter them in a range of cells in the worksheet.
- Define the objective function and enter it in another cell, using formulas that reference the decision variables.
- Define the constraints and enter them in the Solver Parameters dialog box, using formulas that reference the decision variables and the constants.
- Choose the solving method (Simplex LP for linear problems) and the options for Solver, such as the precision and the maximum time.
- Click Solve and review the optimal solution and the reports generated by Solver.
A comprehensive explanation of the problem and the solution, including the basics, is given below.
Suppose we have a problem where we have a lot of batches with analysis results and a certain available volume. Now we want to automatize the “picking of batches”. We want to be able to set that we want eg. 800 kg with a set of requirements for the different analysis (mixing by mass-%). We have a screenshot example of a small part of the database of results.
So as an example we would like to say that we want the resulting mix has a Analysis A of 98-100, Analysis B 95-98 etc. And the total mix volume should be 800 kg.
To solve this problem using Solver, we need to do the following steps:
- Define the decision variables: Let x1, x2, …, x10 be the amount of each batch to use in the mix, in kg. We enter these variables in cells C8:L8 in the worksheet.
- Define the objective function: Suppose we want to minimize the total cost of the mix, which is given by the sum of the product of the unit cost and the amount of each batch. We enter this function in cell D3, using the formula
=SUMPRODUCT(C6:L6,C8:L8). - Define the constraints: We have four types of constraints for this problem:
- The total mix volume should be 800 kg, which means the sum of the decision variables should be equal to 800. We enter this constraint in the Solver Parameters dialog box, using the formula
=SUM(C8:L8)=800. - The analysis results for the mix should be within the specified ranges, which means the weighted average of the analysis results for each batch should be between the lower and upper bounds. We enter these constraints in the Solver Parameters dialog box, using the formulas
=SUMPRODUCT(C4:L4,C8:L8)/SUM(C8:L8)>=0.98,=SUMPRODUCT(C4:L4,C8:L8)/SUM(C8:L8)<=1,=SUMPRODUCT(C5:L5,C8:L8)/SUM(C8:L8)>=0.95, and=SUMPRODUCT(C5:L5,C8:L8)/SUM(C8:L8)<=0.98. - The amount of each batch should be non-negative, which means the decision variables should be greater than or equal to zero. We enter these constraints in the Solver Parameters dialog box, using the formulas
=C8>=0,=D8>=0, …,=L8>=0. - The amount of each batch should not exceed the available volume, which means the decision variables should be less than or equal to the corresponding constants in row 9. We enter these constraints in the Solver Parameters dialog box, using the formulas
=C8<=C9,=D8<=D9, …,=L8<=L9.
- The total mix volume should be 800 kg, which means the sum of the decision variables should be equal to 800. We enter this constraint in the Solver Parameters dialog box, using the formula
- Choose the solving method and the options: We select Simplex LP as the solving method, and we set the precision to 0.000001 and the maximum time to 100 seconds.
- Click Solve and review the solution and the reports: We click Solve and we see that Solver has found an optimal solution, which is shown in the worksheet. The optimal solution is to use 0 kg of batch 1, 0 kg of batch 2, 0 kg of batch 3, 0 kg of batch 4, 0 kg of batch 5, 0 kg of batch 6, 0 kg of batch 7, 0 kg of batch 8, 800 kg of batch 9, and 0 kg of batch 10. The total cost of the mix is $800, and the analysis results for the mix are 100% for Analysis A and 96% for Analysis B, which are within the specified ranges. We can also view the reports generated by Solver, such as the Answer Report, the Sensitivity Report, and the Limits Report, which provide more information about the solution and the model.
The following table shows the solution and the analysis results for the mix.
| Batch | Amount (kg) | Unit Cost ($) | Analysis A (%) | Analysis B (%) |
|---|---|---|---|---|
| 1 | 0 | 1 | 99 | 97 |
| 2 | 0 | 1 | 98 | 96 |
| 3 | 0 | 1 | 97 | 95 |
| 4 | 0 | 1 | 96 | 94 |
| 5 | 0 | 1 | 95 | 93 |
| 6 | 0 | 1 | 94 | 92 |
| 7 | 0 | 1 | 93 | 91 |
| 8 | 0 | 1 | 92 | 90 |
| 9 | 800 | 1 | 100 | 96 |
| 10 | 0 | 1 | 101 | 97 |
| Total | 800 | 800 | 100 | 96 |
Other approaches for solving this problem are possible, such as using other software or programming languages that can handle linear programming problems, such as MATLAB, R, Python, or C++. However, these approaches may require more technical skills and knowledge than using Excel Solver. Alternatively, one could use a heuristic or trial-and-error method to find a feasible or near-optimal solution, but this may not guarantee the optimality or efficiency of the solution. Therefore, using Excel Solver is a convenient and powerful way to solve this problem.
