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

## Introduction

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.

## Input data

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.

Alloy | A | B | C | D | E | F | G | H | I | Desired blend |

% Lead | 10 | 10 | 40 | 60 | 30 | 30 | 30 | 50 | 20 | 30 |

% Zinc | 10 | 30 | 50 | 30 | 30 | 40 | 20 | 40 | 30 | 30 |

% Tin | 80 | 60 | 10 | 10 | 40 | 30 | 50 | 10 | 50 | 40 |

Costs/lb | 4.1 | 4.3 | 5.8 | 6 | 7.6 | 7.5 | 7.3 | 6.9 | 7.3 |

## 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.

## The solution

Hitting solve presents the following results:

Percentage | 0 | 0.6 | 0 | 0.4 | 0 | 0 | 0 | 0 | 0 | 1 |

Costs | 0 | 2.58 | 0 | 2.4 | 0 | 0 | 0 | 0 | 0 | 4.98 |

% Lead | 0 | 6 | 0 | 24 | 0 | 0 | 0 | 0 | 0 | 30 |

% Zinc | 0 | 18 | 0 | 12 | 0 | 0 | 0 | 0 | 0 | 30 |

% Tin | 0 | 36 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 40 |

## Extensions and Remarks

Source: Dantzig, G B, Chapter 3.4. In Linear Programming and Extensions. Princeton University Press, Princeton, New Jersey, 1963.