Last updated: 4/28/2007

Alexander L. Kozachkov

An algorithm to calculate material requirements for multi-stepped technological processes defined on a table of mixes and ingredients

Some technological processes to produce consumer chemicals that do not involve chemical reactions can be defined on a table containing raw materials and mixes along with percentage of each raw material and each previously created mix used to create current mix.

Let’s place the ingredients, including the mixes, in the table’s rows and place the sequence of mixes in the table’s columns, Figure 1.

	Mix 1	Mix 2	Mix 3	Mix 4
Raw Material 1	10		25
Raw Material 2	20			20
Raw Material 3		50		25
Raw Material 4			25	5
Raw Material 5	70	30
Mix 1	100	20	25	20
Mix 2	x	100	25	10
Mix 3	x	x	100	20
Mix 4	x	x	x	100

Figure 1.

Very last mix is the target product.

The tables similar to the table above are developed by research groups and get transferred to the production.

However, this table does not answer to the question of how much of each ingredient must be used on each step to produce specific amount of target product. Neither it gives the exact amount of each raw material needed to start the technological process.

A simple algorithm below answers these questions by building another table using original table and amount of target product as it’s input. The algorithm is implicitly based on material balance: matter is neither created nor destroyed in industrial non-radioactive processes.

Definitions

T- is amount of the product that we are building.

Let’s assume that technological process consists of m-steps (mixes) and requires n-raw materials.

They form (n+m) X m table. (i,j)-element of the table is P_ij – percentage of an ingredient i used in mix j.

For any column j the following condition is true (see Figure 2):

	1	…	j	…	m
1			P_1j
…			…
i			P_ij
…			…
n+j-1			P_{n+j-1 j}
n+j			100	…
…			x
			…
n+m			x

Figure 2

This represent the fact that total percentages of the ingredients including previously built mixes in the current mix is equal to 100 %: P_{n+j j} = 100.

Remaining elements of the column (i=n+j+1 to m+n) are not defined since any mix can be used on subsequent mixes only.

A table being built is Target Table; each A_ij element of the table is an amount of an ingredient i used in the mix j.

It is not a good idea to try calculating each A_ij individually since any such formula would exponentially depend on the number (n-j) of remaining steps in the technological process.

However, there is a simple fact that lets us calculate the table at once starting from the last mix and moving backwards.

Statement 1

	1	…	j	j+1	…	m
1
…
n
n+1
…
n+j			A_{n+j j}	A_{n+j j+1}	…	A_{n+j m}
…			x
			…
n+m			x

Figure 3

To prove this point it is enough to say that amount of each mix created (A_{n+j j}) must be equal to the total amounts of this mix used on subsequent steps (material balance).

In the example from Figure 1, assuming the target amount is 1000, the Target Table is shown on Figure 4.

Note that each amount in bold is equal to additions of all others numbers from the raw, such as 280=30+50+200

	Mix 1	Mix 2	Mix 3	Mix 4
Raw Material 1	28		50
Raw Material 2	56			200
Raw Material 3		75		250
Raw Material 4			50	50
Raw Material 5	196	45
Mix 1	280	30	50	200
Mix 2	x	150	50	100
Mix 3	x	x	200	200
Mix 4	x	x	x	1000

Figure 4

Algorithm

Step 1. Calculate last mix.

Since last mix is the target product, amount of the last mix to build equals to the target amount.

It means that

(3) " i = 1 to m-1 A_im = P_im/100 * T

Step 2. Calculate next to last mix.

Target amount of this mix equal to the amount of this mix used in the very last mix (An+m-1 m).

Since it is previously calculated - we can use a formula similar to the (3)

(4) " i = 1 to m-2 A_im-1 = P_im/100 * A_{n+m-1 m}

…

Step j

Using Statement 1 to find the amount of mix j

Calculate each ingredient in this mix by using the following formula:

(5) " i = 1 to m-2 A_im-1 = P_im/100 * A_{n+m-1 m}

‚

Basically, calculation of current mix is done by finding target amount of the mix (using Statement 1) and proportions of the ingredients.

Conclusion

It should be noted that using negative amounts does not affect this algorithm since we never assumed that A_ij is a positive number in the first place. This represents the fact that some of the raw materials can be various types of water and we can loose water on any technological step.

An algorithm above is available in wide variety of implementations from a simple spreadsheet with fixed number of ingredients/steps to the Web based JFormula (server side Java).