Skip to main content
ARS Home » Southeast Area » Mississippi State, Mississippi » Poultry Research » Research » Publications at this Location » Publication #231257
Title: Micosoft Excel Sensitivity Analysis for Linear and Stochastic Program Feed Formulation

Author
item Roush, William
item Purswell, Joseph - Jody
item Branton, Scott

Submitted to: Journal of Applied Poultry Research
Publication Type: Peer Reviewed Journal
Publication Acceptance Date: 11/24/2008
Publication Date: 1/15/2009
Citation: Roush, W.B., Purswell, J.L., Branton, S.L. 2009. Micosoft Excel Sensitivity Analysis for Linear and Stochastic Program Feed Formulation. Journal of Applied Poultry Research 13(1):85-89.

Interpretive Summary: Animal diets are usually formulated by computer with mathematical programs to meet specified nutritional requirements. Nutritionists traditionally use linear programs which is based on the assumption that nutrients in ingredients are linear. However, nutrients in ingredients are variable. A more appropriate mathematical approach is stochastic programming which formulates the diets by including nutrient variability in the formulation process. The stochastic result is a more accurate and precise formulation of the requested requirements. After formulation, nutritionists are usually interested in how changes in requirements and ingredient costs would effect the optimal diet formulation. A comparision was made of linear and stochastic dietary formulation approaches in which a spreadsheet feed problem was solved to illustrate how closely sensitivity results compare for the two methods.

Technical Abstract: Sensitivity analysis is a part of mathematical programming solutions and is used in making nutritional and economic decisions for a given feed formulation problem. The terms, shadow price and reduced cost, are familiar linear program (LP) terms to feed formulators. Because of the nonlinear nature of stochastic programming (SP), different methods and terminology are used to define shadow prices and reduced costs. The Lagrange multiplier is used instead of shadow price to describe marginal value of nutrients. Reduced gradient is used instead of reduced cost to describe the price at which ingredients, not used in the formulation, would be included in the solution. A spreadsheet feed problem was setup with 11 ingredients and 11 constraints. LP and SP solutions were determined using the Excel Solver algorithm. Two problems compared LP and SP solutions at 50% and 69% probabilities for the protein constraint. All other constraints were held at a 50% probability. Results for the 50% probability comparison showed that the feed formulations, as expected, were the same for both LP and SP. Wheat was not included in the solution. The LP reduced cost and the SP reduced gradient for unused wheat were equivalent. The LP Shadow prices and the SP Lagrange multipliers were equivalent. Results for the 69% probability problem showed a difference in the formulated rations. The LP reduced cost was $34.25 and the SP reduced cost was $34.52 showing the respective amounts that the cost of wheat would have to be reduced to be included in the solution. The shadow price and the Lagrange multiplier were $2.73 and $2.71, respectively, for the amount of increase in diet cost that could be expected by a unit of change in the protein requirement. Some differences in precision were noted with the results. A caveat is that, because of nonlinearity, sensitivity analysis for SP is valid only for the single point of the optimal solution.