This article describes a simplified Blending Problem in the Oil Industry by way of an example and shows how to solve it using the Excel solver. We look at which percentages of the available alloys to use to obtain the desired blend at minimal cost.
Blending problems in general deal with the question of which percentages of different materials should be blended together to obtain a blend with certain minimum number of ingredients. Each input material has different compositions of these ingredients and different prices. The objective is to minimize cost while obtaining the a blend with the desired ingredient levels. Blending problems occur in food manufacturing (for example Whiskas Cat Food) as the Diet Problem, in the oil industry and, as described here, in the metal industry.
There are several different alloys, named A through I, that have certain percentages of lead, zinc, and tin., see table below. Each alloy has a certain cost per pound as given in the table as well. The last column states the percentages of each ingredient in the desired blend. The question is which alloys should be used and in which fractions to obtain the desired blend at minimal costs.
Connecting the input data with the results through formulas
In the first step, the input data are connected with the results through several formulas, see screenshot below. The line “Percentages” highlighted in yellow is left blank (or may be filled with any values) and presents the decision variable. The sum of these must equal to 1 (it’s actually in fractions between 0 and 1.
The cost for each alloy used will be the cost/lb multiplied with its percentage. The sum over all alloys will be the total cost and represents the objective value (to be minimized).
In the lines below we compute the resulting amounts of each ingredient for each alloy given fraction of each alloy in the decision variables. The sum over each ingredient computes the amount of each ingredient for the actual blend (in column K). Now, these values can be used to enter the formulas in the Excel solver.
Entering the information into the Excel solver
First, the objective value, here in K9, the total amount of cost is entered as objective and Min is selected as this is a minimization problem.
The decision variables are entered into the line “Changing Variable Cells”. The constraints are that (1) the actual blend amounts are as least as great as the given desired blend and (2) the sum over all fractions equals to 1.
Hitting solve presents the following results:
Extensions and Remarks
Source: Dantzig, G B, Chapter 3.4. In Linear Programming and Extensions. Princeton University Press, Princeton, New Jersey, 1963.