J. Appl. Poult. Res.
HOME HELP FEEDBACK SUBSCRIPTIONS ARCHIVE SEARCH TABLE OF CONTENTS
QUICK SEARCH:   [advanced]


     

J APPL POULT RES 2007. 16:514-520. doi:10.3382/japr.2007-00033
© 2007 Poultry Science Association
This Article
Right arrow Summary Freely available
Right arrow Full Text (PDF)
Right arrow Alert me when this article is cited
Right arrow Alert me if a correction is posted
Services
Right arrow Similar articles in this journal
Right arrow Alert me to new issues of the journal
Right arrow Download to citation manager
Right arrow reprints & permissions
Citing Articles
Right arrow Citing Articles via Google Scholar
Google Scholar
Right arrow Articles by Roush, W. B.
Right arrow Articles by Branton, S. L.
Right arrow Search for Related Content
PubMed
Right arrow Articles by Roush, W. B.
Right arrow Articles by Branton, S. L.

Research Reports

An Adjustable Nutrient Margin of Safety Comparison Using Linear and Stochastic Programming in an Excel Spreadsheet1

W. B. Roush2, J. Purswell and S. L. Branton

USDA, Agricultural Research Service, Poultry Research Unit, Mississippi State University, Mississippi State 39762

Correspondence: 2 Corresponding author: BRoush{at}msa-msstate.ars.usda.gov


   SUMMARY
TOP
 SUMMARY
DESCRIPTION OF PROBLEM
 MATERIALS AND METHODS
 RESULTS AND DISCUSSION
 CONCLUSIONS AND APPLICATIONS
 REFERENCES AND NOTES
 
A stochastic-linear program Excel workbook was developed that consisted of 2 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 ME and nutrients were taken or calculated from CV, and from a commercial publication of sources for amino acids. The Excel spreadsheet was set up 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) by 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.

Key Words: linear program • stochastic program • margin of safety • diet formulation


   DESCRIPTION OF PROBLEM
TOP
 SUMMARY
 DESCRIPTION OF PROBLEM
MATERIALS AND METHODS
 RESULTS AND DISCUSSION
 CONCLUSIONS AND APPLICATIONS
 REFERENCES AND NOTES
 

". . . I work on planning under uncertainty. That’s the big field as far as I’m concerned; that’s the future. Maybe I’m the only one who says that." George Dantzig, Father of Linear Programming [1]

Uncertainty and risk are inherent in biological variability. Stochastic programming is a nonlinear programming approach used to manage uncertainty and constrain risk when dealing with nutrient variability. The term "stochastic programming" is considered equivalent to the terms "nonlinear stochastic programming" and "chance-constrained programming," as used in other feed formulation publications.

The use of a margin of safety (MOS) was suggested by Nott and Combs [2] as a simple way to adjust the nutrient matrix to compensate for nutrient variability. To make the adjustment, they suggested subtracting one-half of a SD from the mean value of nutrients. This would increase the probability of meeting an animal’s requirement from 50% for a linear program to greater than or equal to 69% for a linear program adjusted for an MOS. An MOS of 0.5 was chosen, most likely for ease of calculation.

Several papers and presentations have addressed stochastic programming as a method of constraining the diet formulation risk of not meeting the requested nutrient amounts [3, 4, 5]. The philosophy of and assumptions about linear and stochastic programming are discussed elsewhere [6, 7, 8, 9, 10].

Pesti and Seila [11] made a breakthrough in showing how both linear and stochastic programming could be formulated in an Excel spreadsheet program. They used 2 problems with different scenarios, one showing linear programming, and the other showing stochastic programming.

The intent of this paper is to build on their paper [11] by demonstrating a comparison of linear and stochastic programming with a single problem, as proposed in their paper. Our hope is that nutritionists and others involved in feed manufacturing and the business of accurately feeding animals at a least cost will be able to make their own comparisons based on the illustrated principles.


   MATERIALS AND METHODS
TOP
 SUMMARY
 DESCRIPTION OF PROBLEM
 MATERIALS AND METHODS
RESULTS AND DISCUSSION
 CONCLUSIONS AND APPLICATIONS
 REFERENCES AND NOTES
 
A stochastic-linear program Excel workbook was developed, consisting of 2 worksheets. The first worksheet was developed into a linear program problem, and the second worksheet was developed into a stochastic program problem. Both approaches used the Solver function, an Excel add-in for optimization. The Visual Basic for Applications macros for Solver associated with the linear and stochastic problems are shown in Figure 1Go. Solver is not always installed with Excel and must be installed as an add-in. Instructions for installation can be found at the Web site http://support.microsoft.com/kb/843304 [12].


Figure 1
View larger version (26K):
[in this window]
[in a new window]

  		Figure 1. Linear and stochastic program Visual Basic for Applications macros for Excel spreadsheets.

 
The spreadsheet data for the problems are shown in Figures 2Go and 3Go for solving the linear and stochastic programs, respectively. For both sheets, the data include the ingredients, cost per ton, amount (to be solved for), ingredient restrictions, and cost ($) per ingredient, as well as the nutrient data. In this example, only the nutrient data for protein and the associated SD are shown. The calculated values, SD, requested levels, and final MOS levels for stochastic and linear programming are shown. The requested probability, the z-value, and the calculated probability for the diet are shown. The spreadsheet below the data shows the formulas associated with the data values.


Figure 2
View larger version (64K):
[in this window]
[in a new window]

  		Figure 2. Database spreadsheets for linear programming, including Microsoft Excel cell formulas (bottom spreadsheet). Only protein is shown with its accompanying SD and margin of safety (MOS). The requested level of probability for meeting the protein requirement was 69%. The resulting formulated probability was 77.26%.

 

Figure 3
View larger version (68K):
[in this window]
[in a new window]

  		Figure 3. Database spreadsheets for stochastic programming, including Microsoft Excel cell formulas (bottom spreadsheet). Only protein is shown with its accompanying SD. The requested level of probability for meeting the protein requirement was 69%. The resulting formulated probability was 69%, as requested.

 
Figures 4Go and 5Go show the inputs and results for the linear and stochastic programs, respectively. Inputs to be entered included the requested nutrient, requested probability, and restrictions. The ingredient amounts, calculated nutrients, calculated probabilities, and cost of the diet were calculated after the linear or stochastic macros were activated.


Figure 4
View larger version (53K):
[in this window]
[in a new window]

  		Figure 4. Input spreadsheets for linear programming, including Microsoft Excel cell formulas (bottom spreadsheet). The requested probability for protein was set at 69%. The result was an overformulated probability of 77.26%.

 

Figure 5
View larger version (57K):
[in this window]
[in a new window]

  		Figure 5. Input spreadsheets for stochastic programming, including Microsoft Excel cell formulas (bottom spreadsheet). The requested probability for protein was set at 69%. The formulated result was 69%.

 
The linear program problem proposed by Pesti and Seila [11] 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 energy and nutrients were taken or calculated from CV published by Zhang [13] and from a commercial publication of sources for amino acids [14], respectively. The Excel spreadsheet was configured so that the calculated 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) by using both linear and stochastic programming. Sensitivity analysis was not addressed at this stage of program development.


   RESULTS AND DISCUSSION
TOP
 SUMMARY
 DESCRIPTION OF PROBLEM
 MATERIALS AND METHODS
 RESULTS AND DISCUSSION
CONCLUSIONS AND APPLICATIONS
 REFERENCES AND NOTES
 
The results of the objective (minimization of cost), diet composition, and nutrient allocations for the linear and stochastic programs in the Excel spreadsheet were identical when the MOS values were set equal to zero (not shown). This was expected because multiplication of MOS at a value of zero cancels out the variance of the nutrients, and the nutrient mean values remain as a linear program problem. Therefore, the objective result ($163.28) matched the benchmark linear programming result of the spreadsheet program described by Pesti and Seila [11].

However, when insurance was incorporated in the formulation problem, such as a 69% chance of meeting the nutrient requirement of the animal, there was a difference in the answers obtained by linear and stochastic programming. As shown in Figures 4Go and 5Go, results for the objective equations (cost/ton) were $165.776 for the linear program and $165.344 for the stochastic program. Examination of the requested and calculated probabilities revealed that the linear program overformulated at a probability of 77.26%, whereas the stochastic program produced the requested probability of 69%.

However, should the formulator decide that 77.26% is more appropriate for a particular nutrient, then the stochastic program would more accurately provide that probability. The linear program with an MOS would again overformulate at a higher than requested level of probability. Stochastic programming provides more flexibility, accuracy, and precision in meeting the requested probability levels.

This study was designed to show the advantage of stochastic programming in controlling nutrient variation. However, we also illustrate the accessibility of linear and nonlinear algorithms through commonly available computer spreadsheets, which allow application of these formulation methods by companies in developing areas that do not have sophisticated software and have numerous ingredients that vary in quantity and quality.


   CONCLUSIONS AND APPLICATIONS
TOP
 SUMMARY
 DESCRIPTION OF PROBLEM
 MATERIALS AND METHODS
 RESULTS AND DISCUSSION
 CONCLUSIONS AND APPLICATIONS
REFERENCES AND NOTES
 

  1. An Excel spreadsheet, with Solver as an add-in, was effectively developed to compare formulation of a poultry diet with linear programming with an MOS and with stochastic programming.
  2. The developed program allows the formulator to determine the level of probability for requested nutrient specifications.
  3. Results showed that at a requested probability level of 50%, both the linear program and the stochastic program produced the same diet.
  4. The linear program with an MOS was shown to overformulate the requested probability, resulting in 77.26%, whereas the stochastic program formulated the requested probability of 69%.


   FOOTNOTES
 
1 Mention of trade names or commercial products in this publication is solely for the purpose of providing specific information and does not imply recommendation or endorsement by the USDA. Back


   REFERENCES AND NOTES
TOP
 SUMMARY
 DESCRIPTION OF PROBLEM
 MATERIALS AND METHODS
 RESULTS AND DISCUSSION
 CONCLUSIONS AND APPLICATIONS
 REFERENCES AND NOTES
 

  1. Horner, P. 2005. George Bernard Dantzig (1914–2005): Operations research loses a pillar of the profession. OR/MS Today 32:24–32.
  2. Nott, H., and G. F. Combs. 1967. Data processing feed ingredient composition data. Feedstuffs 39:21–22.
  3. Van de Panne, C., and W. Popp. 1963. Minimum cost cattle feed under probabilistic protein constraints. Manage. Sci. 9:405–430.
  4. St. Pierre, N. R., and W. R. Harvey. 1986. Incorporation of uncertainty in composition of feed into least cost ration models. 1. Single chance constrained programming, and 2. Joint chance constrained programming. J. Dairy Sci. 69:3051–3073.[Abstract/Free Full Text]
  5. St. Pierre, N. R. 1991. Ration balancing: From costs to profits. Pages 73–80 in Proc. 1991 Maryland Nutr. Conf. Feed Manuf., College Park, MD. Univ. Maryland, College Park.
  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. Poult. Sci. 71:255–262.[ISI]
  7. Cravener, T. L., W. B. Roush, and T. H. D’Alfonso. 1994. Laying hen production responses to least cost rations formulated with stochastic programming or linear programming with a margin of safety. Poult. Sci. 73:1290–1295.[ISI][Medline]
  8. Roush, W. B., R. L. Stock, T. L. Cravener, and T. H. D’Alfonso. 1994. Using chance constrained-programming for animal feed formulation at Agway. Operations Research Society/The Institute of Management Sciences. Interfaces 24:53–58.[Abstract/Free Full Text]
  9. Roush, W. B., T. L. Cravener, and F. Zhang. 1996. Computer formulation observations and caveats. J. Appl. Poult. Res. 5:116–125.[Abstract/Free Full Text]
  10. Roush, W. B. 2001. Stochastic programming has implications for precision feeding. Feedstuffs 73:14, 15.
  11. 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.
  12. Microsoft. 2006. How to create Visual Basic macros by using Excel Solver in Excel 97. http://support.microsoft.com/kb/843304 Accessed Feb. 28, 2007.
  13. Zhang, F. 1999. Stochastic models and software design for feed formulation. PhD Diss. Penn State Univ., University Park, PA.
  14. Degussa. 2001. The Amino Acid Composition of Feedstuffs. 5th rev. ed. Degussa Corp., Feed Addit., Kennesaw, GA.




This Article
Right arrow Summary Freely available
Right arrow Full Text (PDF)
Right arrow Alert me when this article is cited
Right arrow Alert me if a correction is posted
Services
Right arrow Similar articles in this journal
Right arrow Alert me to new issues of the journal
Right arrow Download to citation manager
Right arrow reprints & permissions
Citing Articles
Right arrow Citing Articles via Google Scholar
Google Scholar
Right arrow Articles by Roush, W. B.
Right arrow Articles by Branton, S. L.
Right arrow Search for Related Content
PubMed
Right arrow Articles by Roush, W. B.
Right arrow Articles by Branton, S. L.

HOME HELP FEEDBACK SUBSCRIPTIONS ARCHIVE SEARCH TABLE OF CONTENTS