Page Banner

United States Department of Agriculture

Agricultural Research Service

Title: An Adjustable Nutrient Margin of Safety Comparison Using Linear and Stochastic Programming in An Excel Spreadsheet

Authors
item Roush, William
item Purswell, Joseph
item Branton, Scott

Submitted to: Journal of Applied Poultry Research
Publication Type: Peer Reviewed Journal
Publication Acceptance Date: May 14, 2007
Publication Date: October 15, 2007
Citation: Roush, W.B., Purswell, J.L., Branton, S.L. 2007. An adjustable nutrient margin of safety comparison using linear and stochastic programming in an excel spreadsheet. Journal of Applied Poultry Research. 16:514-520.

Interpretive Summary: The inclusion of ingredients with highly variable nutrients in animal diets will increase as traditional feedstuffs like corn are diverted to the making of biofuels. Nutrient variability (e.g. in DDG/S) is of concern in accurately and precisely formulating animal diets. Incorporating nutrient variability in the feed formulation process has been has been a concern for sometime. Nott and Combs (1967) suggested adjusting the average nutrient values in a linear program database by a half of a standard deviation. This provides a probability of greater than 69% of meeting the requested nutrient level. The problem is that the linear program, commonly used for diet formulation, was not meant for problems involving variability. The mathematical result is that the linear program over formulates nutrients. As a consequence, rations have wasted nutrients and become more costly. An alternative approach is to use stochastic programming which can deal with the nonlinearity of nutrient variability. Stochastic programming offers more flexibility, precision and accuracy in meeting requested nutrient levels at a given probability. Nutritionists, in general, have not had the opportunity to directly examine the difference between formulation with a linear program and a stochastic program. In this manuscript, an Excel spreadsheet is developed that allows the comparison of linear and stochastic programming. This spreadsheet will allow the nutritionist to examine the formulation of a diet with the same specifications using linear and stochastic programming. It demonstrates that linear programming adjusted for a margin of safety will always over formulate, with a resulting higher cost ration, than a stochastic program.

Technical Abstract: A stochastic/linear program Excel workbook was developed consisting of two worksheets illustrating linear and stochastic program approaches. Both approaches used the Excel Solver add-in. A published linear program problem served as an example for the ingredients, nutrients and costs and as a benchmark in the development of the linear and stochastic programs. Standard deviations for metabolizable energy and nutrients were taken or calculated from CVs, and from the commercial publication of sources for amino acids. The Excel spreadsheet was setup so that the calculated margin of safety (MOS) value, according to the requested probability, was the same for both the linear and stochastic programs. As an example, the probability for meeting the nutrient value for protein was compared at 50% (MOS=0) and 69% (MOS=0.5) using both linear and stochastic programming. Spreadsheet results illustrated the flexibility, accuracy, and precision of the stochastic program over the linear program in meeting the requested nutrient probability.

Last Modified: 12/18/2014
Footer Content Back to Top of Page