Estimating Crude Protein Variability and Savings of Broiler Feeds Using Microsoft Excel25 July 2014
Formulating poultry feeds using the two-bin method based on linear programming will greatly decrease variability in crude protein content. Rashed A. Alhotan (graduate student at the Department of Agricultural and Applied Economics), Gregory J. Colson (Department of Agricultural and Applied Economics) and Gene M. Pesti (Department of Poultry Science), all at the University of Georgia explain the process.
One source of inefficiency in poultry production comes from variation in feed ingredients. Using standard feed mixing techniques, grains and meals are each stored in their own bins (one-bin method). Linear programmes are typically used to find the combination of ingredients meeting nutrient restrictions based on average ingredient compositions. To increase the probability of meeting nutrient restrictions greater than 50 per cent of the time, stochastic models may be implemented. For instance, feed cost may be increased by 20 per cent to meet the minimum crude protein requirement in 80 per cent of batches instead of 50 per cent. In-line equipment (such as NIR) is now available to quickly estimate ingredient compositions, e.g. per cent protein, and facilitate improved formulation techniques.
This publication describes how to use Microsoft Excel workbooks designed to calculate 1) the effects of dividing ingredients into above- and below-average portions (two-bin method) and 2) the costs of providing nutrients at specified confidence levels.
By dividing ingredients into above- and below-average portions, efficiency is increased by:
- improving performance due to under-feeding with below-specification batches of feed and
- minimising waste from reduced over-feeding.
Feedstuffs are characterised by inherent nutrient variability. When formulating poultry feeds using the standard linear feed formulation techniques, negative outcomes can be expected due to the inherent variability.
This publication explains how to calculate and reduce measures of nutrient variability in feed formulated by linear techniques. Calculating the costs of providing nutrients at specified confidence levels by the non-linear (stochastic) techniques is also discussed. Crude protein content of a corn-SBM broiler starter diet has been chosen as an example. Microsoft Excel workbooks have been constructed to achieve the objectives of this publication.
How is Poultry Feed Formulated?
The majority of poultry feeds are formulated by least-cost feed formulation software that is based on linear programming. Linear programming software formulates feeds with only a 50 per cent assurance of meeting nutrient requirements, i.e. half the batches will be below average. Stochastic programming can also be used to formulate feeds with a 99.99 per cent or even higher assurance of meeting the requirement of any nutrient. The stochastic programming method takes into account nutrient variability. In practice, feeds currently formulated by either linear or stochastic programming methods are formulated from feed ingredients each stored in its own, single bin.
Why is Estimating Nutrient Variability in Poultry Feeds Important?
Batches of feed ingredients arrive at feed mills from different sources. The batches are often not analysed for the actual content of nutrients, and feeds are formulated based on the expected nutrient averages, ignoring the inherent nutrient variability in feedstuffs. As a result, feeds formulated based on historical averages may only meet the nutrient requirements of the birds 50 per cent of the time, with high variation in meeting the minimum requirements. Estimating nutrient variability in finished batches of feed would be beneficial for helping feed formulators overcome the problem of nutrient variability.
How is Crude Protein Variability Estimated?
A large number of ingredient samples collected from poultry producers in North America have been analyzed for their approximate composition, including crude protein (CP) (Tahir et al., 2012). Microsoft Excel spreadsheets (Microsoft Corp., Redmond, WA) were constructed with thousands of simulated batches of feed to estimate CP variability of feeds using two grain-handling methods.
The two methods are:
- feed formulation from undivided, unseparated batches of corn and soybean meal (SBM) (one-bin method) and
- feed formulation from batches of corn and SBM separated into above- and below-average batches (two-bin method).
The two-bin method is proposed to reduce nutrient variability in finished batches of feed, improve live performance, reduce input costs and decrease waste and environmental impact.
Why is the Simulation Method Used?
The distributions of CP in corn (mean = 6.9 per cent; SD=0.59) and SBM (mean = 47.51 per cent; SD=1.42) samples in Figures 1 and 2 do not seem to be normal when the data are graphed. Using an average value does not represent the true value of feed ingredients that may be delivered to a mill. The Monte Carlo simulation was used to represent the CP data as normal distributions by matching the mean and standard deviation of the observed data with the simulated distribution. Figures 1 and 2 demonstrate the shapes of the distribution curves before and after the Monte Carlo simulation.
How to Estimate CP Variability for Feeds Formulated by a Linear Programming Model and the One-Bin Method
The workbook “CP-VEW1” was constructed for this purpose. The “Simulations” worksheet contains thousands of simulated batches of finished feed (Figure 3).
- The numbered cells in column B (B9:B10008) represent the ID numbers of 10,000 batches of finished feed.
- The entries in cells C9 through C10008 and D9 through D10008 are simulated CP values for corn and SBM, respectively. These simulated values were generated using the Monte Carlo simulation method. The simulation process was done using the function NORMINV (RAND (), CP population mean of the ingredient, CP population standard deviation of the ingredient).
- The entries in cells E9 through M10008 are the quantities of feed ingredients that make up the feeds (i.e., corn, SBM, poultry fat, limestone, dicalcium phosphate, salt, vitamin premix, mineral premix and dl-methionine). Each row is a simulation of one batch of finished feed formulated with the WUFFDA workbook (WUFFDA, 2004), which is linear programming software, using the average CP of corn (6.9 per cent) and SBM (47.51 per cent). It should be noted that the quantity of each feed ingredient is the same among the batches of feed/rows to reflect the real- life situation when feeds are formulated based on the average CP values of the ingredients.
- Column N (N9:N10008) represents the total amount of each batch of feed calculated by summing the amount of the ingredients in each row.
- Column O shows the formula cost in dollars for each batch of feed based on the ingredient prices listed in cells F3 through K3.
- The dietary CP value for each batch of feed is listed in column P, and has been calculated from the three protein sources in the feeds (corn, SBM and Dl-methionine).
- Column Q shows the level of dietary CP in each batch of feed; if it is equal to or above 23 per cent, then the number 1 is assigned to this level and if it is below this level, the assigned number is 0.
- CP means and standard deviations for corn and SBM being investigated are entered in cells C4 through D5.
- The results of this worksheet appear in cells M4 through R6.
- The mean, standard deviation and coefficient of variation of all the CP values in column P are displayed in cells N4 through N6.
- The proportion of the batches of feed that lie above any desired CP level in cell D6 is displayed in cell P6.
- The number and percentage of batches that meet the specified minimum are displayed in cells R5 and R6.
- Data for CP are graphed in separate worksheets. Histograms were generated by using the histogram tool of the analysis toolpak. The worksheet titled “G1” shows the distribution of corn CP in column C, while the worksheet “G2” shows the distribution of SBM CP in column D. CP levels in the 10,000 batches of finished feed are graphed in the worksheet titled “G3.”
Understanding the Results of the “CP-VEW1” Workbook
Feeds formulated in this example were intended to meet the requirements of broiler starter feeds (NRC, 1994) at a CP level of 23 per cent. All the feeds were formulated at corn CP of 6.9 per cent and SBM CP of 47.51 per cent.
It is possible to modify this workbook to be used for any stage of production of any species by reformulating the feed according to the nutritional requirements of the stage or species of interest and then the ingredients’ quantities can be transferred to this workbook.
One objective of this work was to estimate CP variability in the finished feed and to know the distribution of CP in the batches as well. In our example, the entries in cells C4 through D5 produced the results in cells M4 through R6. The mean CP of the 10,000 batches of finished feed is approximately 23 per cent, which is the specified CP level in this example. The measures of variability of CP for the finished feed are standard deviation (SD) ≈ 0.64 and coefficient of variation (CV) ≈ 2.79.
The percentage of batches of feed that lie above any CP level can be determined by entering any value in cell D6 and the result will appear in cell P6. For instance, if we want to know the percentage of batches of feed that lie above 20 per cent, we simply enter “20” in cell D6 and recalculate the worksheet (press the F9 key) to get the value of 100 per cent in cell P6.
The specified CP of 23 per cent was achieved approximately 50 per cent of the time, as shown in cell R5, which is what we expect when we formulate feeds with linear programing techniques (Figure 4; G3 worksheet). The average formula cost of the feeds is presented in cell R6. It should be noted that the outputs change slightly as the formulas in this spreadsheet are recalculated either automatically or manually. With 100 simulations, the fluctuations are considerable; with 10,000, the fluctuations are quite small. The number of simulations can be adjusted to meet the needs of the operator subject to the speed of the operator’s computer.
How to Estimate CP Variability for Feeds Formulated by a Linear Programming Model and the 2-Bin Method
Modern technology allows for the rapid estimation of the nutrient content of feed ingredients. It is possible to divert above- and below-average rail car loads to bins designated as the low CP portion (below-average) or high CP portion (above-average) and then formulate the feed. The feed was formulated with equal portions of each ingredient (i.e., 50 per cent below-average and 50 per cent above-average for both corn and SBM). This step was done by forcing WUFFDA to use the new portions of the same ingredient in 1:1 ratios. It should be noted that a new CP mean (truncated distribution mean) was used in the ingredient matrix of WUFFDA with each portion.
To determine these new means, the Excel spreadsheet named “TND Calculator” was constructed to generate 1,000 CP simulations for both corn and SBM. These simulated CP values were grouped based on the mean CP of each ingredient into “low CP” values (below the mean; assigned 0 in columns J and K) or high CP values (above the mean; assigned 1 in columns J and K) and the new means were obtained for each group as presented in cells L3 through M6.
The workbook “CP-VEW2” is similar to “CP-VEW1” with the exception of the grouping of the simulated CP values in columns K and L into “low CP” or “high CP” values (grouped into columns M through P) based on corn and SBM population means (Figure 5).
The number of the batches of finished feed in this workbook is lower (n = 2,500) than the CP-VEW1 workbook because it takes more time to conduct the simulations.
- Feed ingredient quantities obtained from WUFFDA are listed in columns Q through AA.
- All the ingredients that constituted the batches of feed in this workbook are totaled in column AB.
- Formula cost, dietary CP, and the level of CP for the batches of feed are presented in columns AC, AD and AE, respectively.
- The ingredient characteristics (mean and SD) are entered in cells N4 through O5 and the results are in cells Z4 through AD6.
- The histograms of CP can be found in other worksheets.
Understanding the Results of the 'CP-VEW2' Workbook
As in CP-VEW1, means and SDs for CP populations being studied are entered in the upper left-hand side of this worksheet (cells N4 through O5) and the results are displayed in the upper right-hand side (Z4 through AD6). The same entries for CP statistics were used in this workbook.
The results show that the CP average for the batches of feed is again 23 per cent (Z4) but the SD is much lower than with the 1-bin method (~0.27 (Z5)). The percentage of batches of feed above any CP value of interest can be obtained in the same way as in the CP-VEW1 workbook. For example, if 22.5 per cent is entered in cell N6, the percentage of batches above this level are ~96.30 per cent. The percentage meeting the CP in the feed is roughly the same as in CP-VEW1 workbook (~50 per cent; cell AD5). CP values of the batches of feed (column AD) when graphed are characterised with a tall and narrow distribution (Figure 6) compared to Figure 4.
How to Determine the Level of CP in the Feed Being Formulated at Any Probability Level
To directly and practically determine the level of CP at any given probability when formulating feed, two automated worksheets were constructed. These worksheets calculate the standard deviation and the CP content of the feed being formulated at any probability level.
For ease of use, the worksheets were implemented into the WUFFDA workbook to obtain these important pieces of information during feed formulation. The 'CP Estimator 1' workbook (Figure 7) is designed to be used with the one-bin method, while the 'CP Estimator 2' workbook is designed for the two-bin method (click here to download CP Estimator1; click here to download CP Estimator2).
- In the “simulations” of the workbook CP Estimator 1, the entries in cells B4 through B8 are CP means and SDs for corn and SBM being used in WUFFDA.
- Any probability values between 0 and 1 can be entered in cell B9.
- The amounts of corn and SBM obtained from WUFFDA formulations are updated in cells B14 and B15.
- The expected CP in feed and its SD are presented in cells B19 and B20, respectively.
- The CP content related to the probability entered in cell B9 is presented in cell B22.
- Column D contains the IDs of the simulated CP values for corn (column P) and SBM (column Q).
- Column S represents the final CP content for each of the simulated feeds.
- The workbook CP Estimator 2 is very similar to CP Estimator 1 except the CP simulations were grouped into low- or high-CP populations as presented in columns R through U.
Understanding the Results of the 'CP Estimator' Workbooks
After the feed is formulated normally with WUFFDA, the amounts of corn and SBM used in the formula should appear in the corresponding cells of the simulations sheet. The DL-methionine column in the simulations worksheets is also updated.
The next step is to input any probability value in cell B9 to determine the level of CP associated with this value. For example, if we decide to use 0.9 in cell B9 of the CP Estimator 1 workbook, the result in cell B22 after worksheet recalculation (the F9 key for Windows users) will be 22.17 per cent. There is a 90 per cent probability that the actual CP average in the feed being formulated meets or exceeds 22.17 per cent. The CP value in cell B19 represents the specified CP limit in the feed, which is 23 per cent in this example. The calculated standard deviation is ~0.65 for the one-bin method and ~0.28 per cent for the two-bin method (cell B20).
These statistics are very similar to those obtained by the workbooks CP-VEW1 and CP-VEW2.
What Grain Handling Method Minimises CP Variability: the One-Bin or the Two-Bin Method?
As seen from the results of the workbooks (Table 1), formulating feeds with the two-bin method resulted in a significant reduction in the standard deviation and CV of CP in finished feeds compared to formulating feeds with the regular 1-bin method. The standard deviation for feed formulated by the two-bin method was approximately 0.27 (CV≈1.18) while the standard deviation for the one-bin method was 0.64 (CV≈2.79).
The distribution of the CP values around the mean was altered as well. Almost 96 per cent of the batches of feed for the two-bin method lie above 22.5 per cent compared to only 78 per cent for the one-bin method. On the other hand, only 3.5 per cent of the batches of feed for the two-bin method lie above 23.5 per cent, compared to 22 per cent for the one-bin method.
The majority of the batches of feed (≈93 per cent) formulated with the two-bin method have CP values within one percentage point (between 22.5 and 23.5). In contrast, only 56 per cent of the batches of feed for the one-bin method fall within this percentage point.
The reduction in the number of batches having very low CP contents (CP<22.5 per cent) can support the growth performance of all birds. On the other hand, the reduction in the number of batches having excessive CP content (CP>23.5 per cent) can reduce nitrogen pollution to the environment.
How to Determine the Cost of Providing CP at Specified Confidence Levels for Feeds Formulated by Stochastic Programming and the One-Bin Method
The stochastic programming spreadsheet in Figure 8 (SPW1) was constructed based on previous work by Pesti and Seila (1998).
- The ingredients as well as their prices ($ per 100 lbs.) used above were used here (cells B1 to L1) and (cells B2 to L2).
- The weight of each ingredient appears in cells B3 to L3.
- CP values of the ingredients and the corresponding standard deviations are presented in cells B4 to L4 and B5 to L5, respectively.
- The entries in cells B6 through L19 are the nutrient compositions of the feed ingredients used based on NRC (1994).
- The quantity of each ingredient used in the formula is presented in cells B21 to L21.
- The outputs in cells B24 to L24 indicate the cost of each ingredient used.
- The minimums and maximums of the ingredients are specified in cells B22 to L22 and cells B23 to L23, respectively.
- The formula cost ($ / 100 lbs.) is presented in cell B25.
- Column M contains the nutrient specification and column N contains the maximum amount of each nutrient to be used.
- The supplied amount of each nutrient in the final formula is output in column O.
- A stochastic constraint was implemented into the spreadsheet and the constraint is
where uij is the mean of the ith nutrient in the jth ingredient; xj is the fraction of the jth ingredient; Zi is the standard normal deviate of the ith nutrient; δ2ij is the variance of the ith nutrient in the jth ingredient; and bi is the confidence level of meeting the ith nutrient (D’Alfonso, et al., 1992). The ith nutrient in this example is CP. The first part of the constraint level of meeting the ith nutrient (D’Alfonso, et al., 1992). The ith nutrient in this example is CP. The first part of the constraint
is the total CP of the formula while the second part
is the product of multiplying the Zi value by the square root of the summed cells in row 20. The value of this constraint is computed by the formula in cell O4.
- The Zi value is displayed in cell B26 and is calculated based on the probability value in cell B28, which is the desired probability of success in meeting the specified protein level.
- Column P calculates the average content of each nutrient.
- To optimize the stochastic formulation problem, the solver option must be selected. Once selected, a dialog box is produced that contains the objective value (formula cost) that needs to be minimized and subject to a set of constraints (Figure 9). The solving method selected in the dialog box is GRG Non-Linear since the problem to be solved is not linear (stochastic). The stochastic problem is optimized by clicking “solve” and the solver results dialog should appear.
Understanding the Results of SPW1
The SPW1 workbook is designed to calculate the average content of CP in feeds formulated by the one-bin method at any confidence level and to estimate the cost of feed at that confidence level. For example, to be 60 per cent confident that the feed contains at least 23 per cent (in cell O4), we simply enter 0.6 (in cell B27) and optimise the formulation problem by clicking “solve” to get 23.17 per cent (in cell P4).
The formula cost is $23.50 (in cell B25). In other words, to be 60 per cent sure the feed contains at least 23 per cent, the average content has to be increased to 23.17 per cent. When the confidence level is increased to 80 per cent, the average CP content required increases to 23.56 per cent, leading to increased formula cost ($23.63).
How to Determine the Cost of Providing CP at Specified Confidence Levels for Feeds Formulated by Stochastic Programming and the 2-Bin Method
The SPW2 workbook (Figure 10) is very similar to SPW1 except that corn and SBM were divided into two equal portions as discussed previously.
- Corn was divided into low CP corn (Column B) and high CP corn (Column C) while SBM was divided into low CP SBM (Column D) and high CP SBM (Column E).
- Two more rows each were added for corn (rows 7 and 8) and SBM (rows 10 and 11) to force the program to use the two portions of each ingredient in a ratio of 1:1 (cells B7 and C8 for corn portions; cells D11 and E10 for SBM portions).
- The corresponding CP and SD for each portion discussed previously were used to formulate feeds by SPW2.
Understanding the Results of SPW2
The workbook SPW2 is designed to calculate the average content of CP in feeds formulated by the two-bin method at any confidence level and to estimate the cost of feed at that confidence level. This workbook can be optimised in the same way as in SPW1, as discussed previously. To be 80 per cent sure the finished feed has at least 23 per cent, the average CP content has to increase to 23.24 per cent, with a formula cost of $23.52. In the same manner, the cost of feed at any confidence level can be determined.
What Grain Handling Method Maximises Savings: the One-Bin Method or the Two-Bin Method?
Feed formulation using the two-bin method costs more at low probabilities of success (P<50 per cent) and less at high probabilities of success (P>50 per cent) compared to feed formulation using the one-bin method (Table 2). For example, at P=1 per cent, formula cost increases $5.38 per ton but at P=99 per cent, formula cost decreases $6.47 per ton with the two-bin method compared to the 1-bin method.
In practice, no one should formulate feed at a low probability of success. The two-bin method can be an economically efficient way to reduce formula costs. Normally, when we buy something that is labelled to contain a certain amount of anything, we do not expect to receive less than that 50 per cent of the time. Separating feed ingredients into different categories helps reduce the amount of sub-standard feed, and stochastic programming demonstrates the cost of achieving a minimum specification.
Formulating poultry feeds using the two-bin method based on linear programming will greatly decrease CP variability - the CV is reduced by as much as 50 per cent - compared to the regular feed formulation with the one-bin method with no influence on formula cost.
Formulating feeds with stochastic programming models shows how formula costs to meet the minimum nutrient specification change and the CP variability is reduced when the two-bin method is applied.
1. Tahir, M., M.Y. Shim, N E. Ward, C. Smith, E. Foster, A.C. Guney and G.M. Pesti. 2012. Phytate and other nutrient components of feed ingredients for poultry. Poult. Sci. 91:928–935.
2. Microsoft corporation one Microsoft way Redmond WA 98052 USA
3. Windows User-Friendly Feed Formulation (version 1.02), 2004.
4. National Research Council. 1994. The Nutrient Requirements of Poultry. 9th rev. ed. Academic Press, Washington, DC.
5. Pesti, G.M. and A.F. Seila. 1999. The use of an electronic spreadsheet to solve linear and non-linear 'stochastic' feed formulation problems. J. Appl. Poultry Res. 8:110– 121.
6. D’Alfonso, T.H., W.B. Roush and J.A. Ventura. 1992. Least cost poultry rations with nutrient variability: A comparison of linear programming with a margin of safety and stochastic programming models. Poultry Sci. 71:255–262.