Steps in formulating animal feeds in excel using linear programming method

Steps in formulating animal feeds in excel using linear programming method.

Dr. Dickson Machira. BVM, MSc

Head of Animal Science Department - Bradegate International College

This is a step by step guide on how to use MS excel to formulate animal feeds using the linear programming method. This method is superior to others (Pearson’s square and trial and error method) because many feed ingredients can be used in the calculations simultaneously and it gives you the feed formulation that will have the least cost of production.

The excel sheet can be found here.

Things needed before you start (Minimum Informational Requirements):
1. Find out what nutrients are needed in an animal. This can be done by attending a class on animal nutrition or reading online or in the library. Remember different species may have different nutrient requirements.
2. Find out which feed ingredients you want to use. – These may include things like maize germ, soya bean meal etc. Consider availability, palatability, nutritive value and cost when deciding which ingredients you will use.
3. Find out the cost of a kilogram of each feed ingredient you want to use. E.g. one kg of maize germ is 26 KShs. This can be done by travelling to the market and finding out the actual costs.
4. Find out the nutritive value of each ingredient you want to use. This will include: metabolizable energy (in Kcal/kg), crude protein (%), Crude fibre (%), crude fat (%), calcium (%), phosphorus (%), sodium chloride (%), lysine (%), methionine (%) etc. These can easily be found in literature. Alternatively you can take samples of your ingredients to a laboratory that measures this.
5. Find out which standards are set by KEBS on different feeds. You can get this from their offices, website etc.
6. A computer that can run MS Excel and some little knowledge on computer use.

Steps in formulating feed
Step 1.
Open up MS excel and make sure that the solver add in is enabled. This is done by clicking on the ‘Data’ tab and checking the upper right corner.

If it is not there you have to add it by following the steps below.
Step 1 (a)
Click on file and select options on the left hand panel.

This should open the ‘excel options’ dialog box.
Step 1 (b)
Click on ‘add Ins’ on the left part of the dialog box.

Step 1 (c)
This should open a box written ‘view and manage Microsoft Office Add-Ins’. Look into that box and look for something written ‘Solver Add-in’ under the ‘Inactive application add-ins’.

Step 1 (d)
Click on the ‘Solver add-in’ and then click ‘Go’ at the bottom of the dialog box.

Step 1 (e)
This will open the ‘Add-Ins’ dialog box. Select ‘Solver Add-In’ and click ‘OK’. This will install the add in on the data tab.

Step 2
Now that you now have the solver add in, let’s put in all the information you gathered earlier into excel.
Step 2 (a)
Create a table with the feed ingredients in forming the rows and their nutritive content forming the columns. Include a column with the cost per Kg of the feed at the end. The prices that I have used are hypothetical as I am too lazy to go find out the real prices.

Step 2 (b)
Create a table with the feed standards set by KEBS. In the example below I’ve used the poultry feed standards but any type of standard can be used for any species.

You now have everything you need to create a feed.
As an example let us create a 70 kg bag of layers mash that meets the KEBS standards at the most minimum cost using maize (M), wheat pollard (WP), Bone Meal (BM), Soy bean Meal (SBM), Limestone (L) and Salt (S).
Step 3 (a)
Create a table with the ingredients and cost of the feed ingredient. You can equate the cost to the particular cell number on the table that contained the feed ingredients. Also create a ‘Solution’ row that will contain the calculated amount of feed ingredients that will be needed.

In the figure above the cost of salt is highlighted. In the formula bar you can see it is equal to cell J11 which is where we input the cost of salt.
Step 3 (b)
This is where things start getting a little bit complicated.
Create a row beneath the box above. Name this row ‘total price’. This will contain the total price of the mixture you will make. The total price of your preparation (in this case a 70kg bag of layers mash) will be the sum of the cost of all feed ingredients you use. For one ingredient its price is the amount of that feed ingredient that you use multiplied by its price. The sum of all these gives the total price. Some may call this the cost of production.
In excel this is calculated as =SUMPRODUCT(cells containing cost : cells containing amount used).

In the figure above the value of total price is shown.
Step 3 (c)
Create a constraints table. This will contain the things that your feed must contain. These standards are set by KEBS.
First of all create a row called ‘total amount’. This will contain the total amount of feed you want to make. In our example it is 70 kg (found in cell E41 below).

Step 3 (c)
Next for each constraint create a row. For example Metabolizable energy (ME) is set by KEBS as 2750 Kcal/Kg (found in cell E16 below). To create a 70 kg feed, the total metabolizable energy needed in the feed will be 2750 x 70 (cells E16 x E41 below).

Do this for all the constraints.
The columns for the constraints table are ‘Total actually in feed’ , ‘Value’ and ‘Total needed in feed’.
The ‘total actually in feed’ is the calculated total amount of the nutrient in the feed. This is calculated by multiplying the amount of a feed ingredient and multiplying it by the amount of a nutrient it has. Then adding up all this to give the total amount actually in the feed. In the figure below the total amount of crude protein is gotten by adding the amount of crude protein in each feed ingredient multiplied by the amount in the feed for all feed ingredients.

Here C31 is the amount of maize in the feed and D3 is the CP content of maize. This is done for each feed ingredient and the results added up.
The column value contains the expression of how much of each should be in a feed. This is set by KEBS. For example the minimum amount of crude protein (CP) in your feed should be 16%. Thus your feed can have 16% or more crude protein and thus its value is set as > greater than or = equal.
You now have all you need to calculate the amount of feed ingredients that will cost you the least.
Step 5
Go to the data tab and click on ‘Solver’. This will open the ‘solver parameters’ dialog box.

Step 6
On the ‘Set Objective’ box select the cell you want the total price to be in. In our example this is C32.

Step 6 (b)
Where it says ‘To’ select ‘Min’. This means you want the minimum cost possible

Step 7
On the ‘By changing Variable Cells’ box select where you want the solutions in Kg of each feed ingredient to be. In our example it is from C31 to H31.

Step 8
Select your constraints by clicking on ‘Add’. This will open an ‘Add Constraint’ Dialog box. Select the Cell reference (total actually in feed), select the value and select the constraint (total needed in feed). And then click ok.

Do this for all constraints. Finally the dialog box will look like shown below after all constraints are added.

Step 9
Tick the check place written ‘Make Unconstrained variables non-negative’. This will ensure you don’t get negative values in your amounts

Step 10
On the ‘Select Solving method’ pull down menu select ‘Simplex LP’.

Step 11:
Click solve. This will display a ‘Solver Result’ Dialogue box.
In this box select ‘Keep Solver Solution’ radio button and click ‘Ok’.

And voila you have Finished.
The results show that you will need 46.3 Kg of maize, 0.5 kg of wheat pollard, 1.6 kg of bone meal, and 15.9 kg of soya bean meal, 5.6 kg of limestone and 0.21 kg of salt. This are the amounts that will have the least cost while still meeting standards. The total cost of production of your 70 kg bag will be KSh 3119.7.