How to Solve Blending Linear Programming Problem with Excel Solver

The article will show you how to solve a blending Linear Programming problem with Excel Solver. Linear programming is an important application when we want to minimize cost or maximize profit in the business sector. It’s also important for our daily life as we should optimize our food habits and other expenditures. Blending Linear Programming is a special kind of Linear Programming where profit or cost optimization is calculated on one or multiple products that are made from the mixture of other raw materials. We are going to use Excel to apply this Blending Linear Programming to two real life examples to illustrate how this works.

Download Practice Workbook

What Is Blending Linear Programming?

If you work in a chemical industry or food factory, you need to make products by mixing various materials. For example, if you want to produce a medicine, you need the necessary elements for it and you have to mix them in a defined proportion. In addition, you have to keep in mind how many of these elements you need to buy, how your products’ quality will be, and so on. In this case, you need to optimize the mixing amount of the products that you want to deliver to the customers. The solution to this problem is provided by the Blending Linear Programming application. This application helps us learn how to use raw materials in production.

2 Examples to Solve Blending Linear Programming Problem with Excel Solver

In this article, we are going to explain two types of examples for Blending Linear Programming. One example is to show how to solve Blending LP (Linear Programming) for Fixed Recipe and the other one is for Flexible Recipe.

Fixed Recipe refers to the scenario where you know the exact ratio or mixing amount of the materials. On the other hand, if you don’t have to use a defined amount of raw materials in the production of a commodity, then you should apply Blending LP for Flexible Recipe.

The first example is dedicated to show you how to solve the Fixed Recipe problem. Here, we have three types of liquid materials A, B, and C. We will make two new products using A-B and A-C with a combination of 60%-40% and 80%-20% respectively. There are other parameters like Revenue/Liter, Cost/Liter, and the number of raw materials available in the factory. We will maximize the profit out of this scenario.

For the Flexible Recipe problem, we will produce different types of steel like regular, exclusive and super quality steel from the mixture of different raw materials. We have the data about how much of these raw materials are available, the cost of these raw materials per ton, and the quality rating of them. We also set the required production amount, price per ton of produced different classes of steels and their minimum rating. There is another parameter which we call the Linearized Rating.

1. Blending Linear Programming for Fixed Recipe Problem

We have discussed this problem in the previous section. Let’s just go through the process below.

Steps:

• First, we will be setting some necessary formulas. We want to know about the Raw Usage of the material and use the following formula in cell C12 to calculate it.

Here we used the SUMPRODUCT function which will return the Raw Usage of material A.

• After that, drag the fill icon to the right to AutoFill the cells up to E12.

• Later, use the following formula in I11 to calculate the Revenue.

Here, we multiplied the formula by the value of cell C16 which is 3.7854 because one gallon equals 3.7854 Liter.

• Next, type the following formula in cell I12 and press ENTER.

This formula will return the cost of production.

• Thereafter, we apply the following formula to calculate the profit.

• Now, set your minimum requirement of production.

• After that, go to Data >> Solver. If you don’t know how to add Solver Add-in in the Data Tab, select File >> Options >> Add-ins >> Excel Add-ins >> Go >> Solver Add-in and click OK or follow this Link.
• To open the Solver Add-in, go to Data >> Solver.

• We want to maximize our profit so we set the objective reference to I13 where we stored the Profit
• Also, our variable is the mixing percentage of the products. So we added the G5:G9 range to ‘By Changing Variable Cells’ section.
• After that, click on Add to add the constraints.

• The Raw Material Usage cannot exceed the Available Material So our first constraint is that the range C12:E12 will be less than or equal to C14:E14.
• After that, click on Add.

• Similarly, we added another constraint which implies that the production amount is greater than the minimum required production amount.
• Next, click OK.

• After that, check ‘Make Unconstrained Variable Non-Negative’.
• Thereafter, select Simplex LP as the Solving Method.
• Later, click on Solve.

• After that, a confirmation message box will appear. Just click OK.

• Finally, you will get the values of how much Raw Materials you should use to get the Maximum Profit.
• In addition, you will also get the results of Revenue, Cost of production, and Profit

Thus you can solve the Blending Linear Programming problem with Excel Solver for the Fixed Recipe.

Read More: How to Solve Integer Linear Programming in Excel (With Easy Steps)

2. Excel Solver for Flexible Recipe Blending Linear Programming Problem

In this section, I’ll show you how to solve the Blending Linear Programming Problem for the Flexible Recipe. To know about this problem, please go to this link of this article. Now, let’s go through the following description.

Steps:

• First, we are going to set up some formulas for our solution. Type the following formula in cell F5 and press the ENTER

The formula uses the SUM function to calculate the total amount of the type 1 Steel (Regular, Exclusive and Super) that will be produced from the first Available resources.

• Next, drag the Fill icon below to fill the cells up to F7.

• Later, type the following formula for the calculation of total Regular Steel production amount.

• Similarly, write down the formula below in cell C14 to calculate Linearized Rating and fill the adjacent cells up to E14.

• After that, type the following formula C16 and fill the cells up to E16.

The formula will give us the Linearized Rating of the produced Steels.

• Next, write down the formula below in cell I10 to determine the Revenue.

• To calculate the production cost, use the following formula.

• And the following formula will return the Profit

• After that, to enter the Subject, Changing Variable and Constraints, please follow the link of Method 1 that will lead you to the process. I’ll simply explain these inequalities in the following description.
• We want to maximize the profit amount, so we reference the cell that contains profit (I12).
• Next, our changing variables are the Steel products, so this range will be C5:E7 where the amount of production will be stored.
• After that, we added some constraints. The Linearized Raw Rating will be greater than or equal to Linearized Minimum Required Rating, so the range C14:E14 will be greater or equal to C16:E16.
• Thereafter, the production amount will be greater than the required amount. So the range C8:E8 will be greater than C10:E10.
• And the usage of raw materials will be lower than the available raw materials. So the range F5:F7 will be greater than H5:H7.

• After clicking on Solve, you will get the values of how much Raw Materials you should use to get the Maximum Profit.
• In addition, you will also get the results of Revenue, Cost of production and Profit.

Thus you can solve the Blending Linear Programming problem with Excel Solver for the Flexible Recipe.

Read More: How to Use Excel Solver for Linear Programming (With Easy Steps)

Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

Conclusion

Suffice to say, you will achieve the basic idea of how to apply Blending Linear Programming to solve real life optimization problems with Excel Solver.  If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.

Related Articles

• How to Graph Linear Programming in Excel (with Detailed Steps)