Acta agriculturae Slovenica, suplement 2 (september 2008), 187–194. 16th Int. Symp. “Animal Science Days”, Strunjan, Slovenia, Sept. 17–19, 2008. Agris category codes: L02, U10 COBISS Code 1.08 SPREADSHEET TOOL FOR LEAST-COST AND NUTRITION BALANCED BEEF RATION FORMULATION Jaka ŽGAJNAR and Stane KAVČIČ a) Univ. of Ljubljana, Biotechnical Fac., Dept. of Animal Science, Groblje 3, SI-1230 Domžale, Slovenia, e-mail: jaka.zgajnar@bfro.uni-lj.si. ABSTRACT This paper points out some facts that might improve economic outcome of livestock production in the sense of diet formulation. A spreadsheet tool from two linked modules based on MS Excel platform was constructed, merging different mathematical deterministic programming techniques. The first module utilizes linear program for least-cost ration formulation, aiming to obtain rough estimate what magnitude of the costs might be expected. Resulting value is then considered as target value of cost goal in the second module. It is based on weighted goal programming with penalty function. Obtained results confirm benefits of applied approach. It enables formulation of least-cost ration not taking too much risk of worsening the ration’s nutritive value and balance between nutrients. This is especially important when improved economic and nutritive efficiency is the primal and common aim of optimization tool. Key words: cattle / bulls / spreadsheet tools / beef economics / beef ration optimization / linear programming / weighted goal programming / penalty function ORODJE ZA NAČRTOVANJE NAJCENEJŠIH IN PREHRANSKO IZRAVNANIH OBROKOV ZA PITANCE IZVLEČEK Prispevek izpostavlja nekatere dejavnike, ki z vidika sestavljanja krmnih obrokov lahko izboljšajo gospodarnost živinoreje. V Excelovem okolju je bilo v obliki elektronskih preglednic razvito modularno orodje, ki združuje različne tehnike determinističnega matematičnega modeliranja. Prvi modul vključuje tehniko linearnega programiranja in služi za oceno najcenejšega možnega krmnega obroka. Dobljeni rezultat kot ciljna vrednost vstopa v drugi modul, ki temelji na tehtanem ciljnem programiranju, nadgrajenem s kazensko funkcijo. Pridobljeni rezultati potrjujejo prednosti uporabljenega pristopa, ki omogoča sestavljanje najcenejših krmnih obrokov, ne da bi ob tem tvegali močnejše poslabšanje hranilne vrednosti in razmerja hranil. To je posebej pomembno, kadar je izboljšanje ekonomske in prehranske učinkovitosti temeljni cilj optimizacijskega orodja. Ključne besede: govedo / biki / pitanje / elektronsko orodje / ekonomika / optimiranje prehrane / linearno programiranje / tehtano ciljno programiranje / kazenska funkcija INTRODUCTION Due to changing economic and political environment, the beef sector is becoming one of the most sensible agricultural sectors in the European Union. Its economic position is mostly dependent on the efficiency of each agricultural holding production structure, with the crucial role playing the economy of scale. However, at the moment poor economics position of beef sector could be significantly imposed with progressive abolition of previous Common Agricultural Policy (CAP) production coupled support and increasing environmental and other http://aas.bf.uni-lj.si Acta agriculturae Slovenica, suplement 2 (september 2008). 188 public demands – in addition to World Trade Organization (WTO) pressures, which have led to rapid market fluctuations. Together with direct consequences on the beef market, there are indirect influences that are going to present an increasing economic challenge for beef farmers, especially through higher input prices. Since ration costs might present 40 to 70% of total variable costs, it follows that livestock ration formulation is becoming an increasingly important task also in management of beef sector. It is the fundamental lever in technological improvement that manifests in economic as also ecological terms. In order to help breeders to deal with these challenges many tools have been developed. The most frequent technique applied is deterministic linear programming (LP). It is a classical approach to formulate animal diets and also appropriate tool to optimize human nutrition (Darmon et al., 2002). When focusing only on livestock diets, one can find out that the most frequent manner of utilizing LP technique is least-cost ration formulation, for the first time used by Waugh (1951). As any optimisation technique also LP has some drawbacks. Common to all LP problems is single objective function as its basic concept. It means that one try to get the optimal solution in minimizing or maximizing desired objective within set of constraints imposed. From this point of view LP could be deficient method for ration formulation, since it exclusively relies on one objective (cost function) as the only and the most important decision criteria (Rehman and Romero, 1984; 1987). Lara and Romero (1994) are stressing that in practice decision maker never formulates ration only on the basis of a single objective, but rather on the basis of several different objectives, where economic issue is only one of many. Another drawback of pure LP is also mathematical rigidity of constraints (right hand side – RHS), which usually results in fact that set of equations does not have a feasible solution (Rehman and Romero, 1984). This means that no constraints’ (e.g. given nutrition requirements) violence is allowed at all, irrespective of deviation level. However, relatively small deviations in RHS would not seriously affect animal welfare, but would result in a feasible solution (Lara and Romero, 1994). The most appropriate and commonly used method that partly overcomes listed problems of LP paradigm is weighted goal programming (WGP) (Tamiz et al., 1998). It is a pragmatic and flexible methodology for resolving multiple criteria decision making problems what ration formulation definitely is. Its advantage is also in familiarity with LP, since simplex algorithm is utilized to find the solution (Rehman and Romero, 1993). The aim of this paper is to present developed spreadsheet tool, utilizing mathematical modelling techniques. In the first part a brief overview of WGP and penalty function is given. It is followed by a short description of the optimization tool. Then, the basic characteristics of the analysed case are presented, followed by the results and discussion. Brief conclusions are given in the last section. MATERIAL AND METHODS Weighted goal programming with penalty function Weighted goal programming’s formulation is expressed as mathematical model with a single objective (achievement) function (weighted sum of the deviations variables). Hence, the objective function in WGP model minimizes the undesirable deviations from the target goal levels and does not minimize or maximize goals themselves (Ferguson et al., 2006). In most cases obtained solution is compromise between contradictory goals, enabled with positive and negative deviation variables. Negative deviation variables are included in the objective function for goals that are of type “more is better” and positive deviations variables are included in the Žgajnar, J. and Kavčič, S. Spreadsheet tool for least-cost and nutrition balanced beef ration formulation. 189 objective function for goals of type “less is better”. Since any deviation is undesired, the relative importance of each deviation variable is determined by belonging weights. Since the goals are measured in different units and have different numerical values, the deviations are scaled with normalisation techniques (Tamiz et al., 1998). With this process incommensurability is prevented and all deviations are expressed as ratio difference (i.e. (desired – actual)/desired) = (deviation)/desired)). Rehman and Romero (1987) are pointing on the main drawback of WGP that is concerning the marginal changes. Namely, the method does not distinct between marginal changes within one observed goal; all changes (deviations) are of equal importance. This addresses another new issue in ration formulation example. Namely, in some situations too big deviation might lead to fail animal’s requirements within nutrition desirable limits, and obtained solution is useless. To keep deviations within desired limits and to distinguish between different levels of deviations, penalty function (PF) might be introduced into the WGP model (Rehman and Romero, 1984). Our approach enables one to define allowed positive and negative deviation intervals in more stages for each goal separately. Dependant on goal’s characteristics (nature and importance of 100% matching) these intervals might be different. Sensitivity is dependant on number and size of defined intervals and the penalty scale utilised (si; for i = 1 to n). Penalty system is coupled with achievement function (WGP) through penalty coefficients. Toll for two-phase beef ration formulation The aim of the paper is to present a simple optimization tool for beef ration formulation, developed in MS Excel framework. It is designed as two phase approach (modules) based on mathematical programming techniques (LP and WGP with PF). WGP with PF € RATION (LP) OPTIMAL RATION LP IN PU T D A T A MODULE 2 MODULE 1 Optimization tool Figure 1. Scheme of the optimization tool. The first module (Fig. 1) is based on LP paradigm and is an example of least-cost ration formulation. On the basis of the most important non-competitive constraints it searches for the roughly balanced ration at the least possible cost. On the solution obtained an estimate of cost magnitude expected might be made. Therefore the first module (LP) is as simple as possible (on constraints side), intended just to get crude cost estimation. Through cost function it is linked to the second module based on weighted goal program (WGP) with PF. Mathematical formulation of the first and the second module The first module (LP) is formulated as shown in equations (1), (4) and (7). It mostly relays on economic (cost) function (C) and satisfies only the most important nutrition requirements coefficients (bi), known also as right hand side (RHS). In the first optimization phase one is searching for the ration at the lowest possible cost. Except minimum requirements (bi) that should be met, prices (cj) are the most important factor that dictates the level of jth feed (Xj) included into the ration. Acta agriculturae Slovenica, suplement 2 (september 2008). 190 ∑ = = n j jj XcC 1 *min such that (1) ∑∑ = +−+− = + + + = k i i ii i i ii k i i g ddws g ddwsZ 1 22 2 11 1 1min such that (2) ∑ = ++−− =−−++ n j iiiiijij gddddXa 1 2121 for all i = 1 to r and gi≠0 (3) ∑ = ≤ n j ijij bXa 1 for all i = 1 to m (4) iiii gpgd min 11 −≤ − for all i = 1 to r (5a) iiiii gpgdd min 221 −≤+ −− for all i = 1 to r (5b) iiii ggpd −≤ + max 11 for all i = 1 to r (6a) iiiii ggpdd −≤+ ++ max 221 for all i = 1 do r (6b) 0,,,, 2211 ≥ −+−+ jiiii Xdddd (7) The second module (WGP with PF) is formulated as shown in equations (2) to (7). The achievement function (Z), expressed in equation (2) is defined as weighted sum of undesired deviation variables (di1+, di1–, di2+, di2–) from observed goals (gi), multiplied with belonging penalty coefficients (s1 and s2). Obtained sum-product is subject of minimization (2). The relative importance of each goal is represented by weights (wi) associated with the corresponding positive or negative deviations. To control deviations (5a, 5b, 6a, 6b) for each goal in WGP, penalty intervals (pi1min, pi1max, pi2min, pi2max) are in place. Because of the normalization process, only goals that have nonzero target values (3) could be relaxed with positive and negative deviations. Obtained target value (C) in the first module enters into the second module (WGP with PF) as cost goal (3) that should be met as close as possible. This is also the only case where negative deviation is not penalised and also not restricted with intervals. All other constraints that do not have defined target value or do not have priority attribute are considered in equation (4). One of the main assumptions of the LP paradigm is also non-negativity that is considered for both models in equation (7). Case analysis The tool has been tested on a hypothetical case. It was presumed that beef fattening starts at 200 kg of live weight and stops at 600 kg. For the reason of more precise ration formulation, whole fattening period has been split into four breeding periods (100 kg weight gains) with different average daily gains. In the first period bulls gained 0.9 kg per day, while in the second and the third period the average daily weight gain is the same (1.1 kg). The last quarter last 100 day which means that average daily weight gain was 1 kg. All nutritional requirements have been assessed with the spreadsheet model for ruminants’ nutritional requirements estimation (Žgajnar et al., 2007). The most important constraints and goals are presented in Table 1. Basic set of constraints in both modules (LP and WGP with PF) is more or less the same; they differ only in mathematical sign when they are transformed into goals. In the process of ration formulation one should also consider other ‘non-nutrition’ constraints. In our hypothetical case study we assume quite frequent example that might be met on Slovene beef farms. Because of our climate characteristics, the first or second grass mowing is usually Žgajnar, J. and Kavčič, S. Spreadsheet tool for least-cost and nutrition balanced beef ration formulation. 191 conserved as hay and from rest the grass silages are prepared. This is why the amount of hay in the diet is restricted and in all four periods maximal amount of hay is set to 2 kg per day (Table 1). Table 1. Nutrition requirements divided into four breeding periods, presented as constraints (LP) and set of goals in WGP Fattening period 200–300 kg 300–400 kg 400–500 kg 500–600 kg LP WGP I / II LP WGP I / II LP WGP I / II LP WGP I / II ME (MJ) >6 311 6 311 >6 574 6 574 >7 547 7 547 >9 105 9 105 MP (g) >46 880 46 880 >45 228 45 228 >48 114 48 114 >54 260 54 260 DM (kg) <632 632 <718 718 <920 920 <936 936 CF min (kg) >114 >129 >166 >168 CF max (kg) <164 <187 <239 <243 Ca (g) >4 152 4 152 >4 368 4 368 >4 462 4 462 >5 200 5 200 P (g) >2 358 2 358 >2 596 2 596 >2 958 2 958 >3 300 3 300 Price (cent) C1 C2 C3 C4 Hay (kg/day) <2 <2 <2 <2 LP = constraints for the first module (both scenarios); WGP I / II = constraints for the second module (both scenarios) Initial version of WGP model involves six goals (Table 2). Importance of each goal is defined with weights (wi) ranging between 0 and 100. For energy and protein requirements deviation intervals are very restricted, while for the rest of the goals deviations are more relaxed. For the dry matter intake that presents consumption capacity deviation intervals are defined only for underachievement of the goal, while overachievement is for practical reasons (consumption capacity) not allowed. Table 2. Weights of defined goals and penalty function intervals for two scenarios Penalty function intervals Goal weights Goal Interval 1 Interval 2 (wi) pi1– pi1+ pi2– pi2+ Unit/scenario SI SII SI SII SI SII SI SII ME (MJ) 1% 1% 5% 10% 70 MP (g) 1% 1% 5% 10% 100 DM (kg) 2% 0% 20% 0% 33 Ca and P (g) 2% 5% 20% 30% 5 Price (cent) 8 4% 10% 8 10% 15% 90 SI / SII = first/ second scenario; pi1–, pi1+, pi2–, pi2+ = penalty intervals at the first and the second stage Mineral appropriateness of the ration (preventing deficits as also toxic concentration) is assured through several safety nets (classical minimal and maximal constraints). This is also the reason why only two minerals (Ca and P) are considered as goals. Besides, their ratio should range between (1.1–1.5):1 in both modules to obtain solution. Applied approach of WGP with PF has been tested with varying extensions of cost deviation intervals (PF), which manifests in two scenarios (Table 2). In the first scenario price of obtained ration (WGP I) might deviate from set target value for the most 4% to be penalised within the first stage (s1) and at maximum 10% within the second stage (s2). In the second scenario (WGP II) both margins are relaxed (10% and 15%), while the penalty coefficients remain the same (s1 = 1 and s2 = 5). In analyzed hypothetical case seven different feed (Table 3) and four different mineral- vitamin components were on disposal. Acta agriculturae Slovenica, suplement 2 (september 2008). 192 We assumed that all forage (hay, grass silage and maize silage) is prepared on the farm. Since these forages are usually not tradable, we estimate full cost of their production on the basis of ‘model calculations’ prepared by Agricultural institute of Slovenia (KIS, 2007). All other forage on disposal could be purchased at market prices (Table 3). Table 3. Nutritive value of assumed feed DM ME MP CF Ca P Mg Na K Price or FC* (g/kg) (MJ/kg DM) (g/kg DM) (cent/kg) Feed on disposal Hay 860 9.93 85.00 270 5.70 3.50 2.00 0.35 18.25 15.30 Maize silage 320 10.76 45.00 200 7.06 6.00 1.91 0.12 10.76 3.70 Grass silage 350 9.50 62.00 260 6.00 3.51 2.20 0.35 21.30 6.14 Grain maize 880 13.42 83.00 0.00 0.23 4.09 1.25 0.23 3.75 30.00 Wheat 880 13.47 88.00 0.00 0.57 3.86 1.59 0.45 5.00 32.00 Rapeseed cake 900 12.31 125.00 0.00 2.89 7.00 2.78 2.22 10.00 37.00 Soya meal 880 13.19 215.00 0.00 3.41 7.84 2.61 1.14 20.00 46.00 *Full cost approach RESULTS AND DISCUSSION A hypothetical case has been chosen to test developed spreadsheet tool. Formulated rations for all four fattening periods are presented in Table 4. Between three analysed cases (LP, WGP I and WGP II) there is a significant difference in formulated rations, but in all three cases they are quite simple. The major differences occur as result of allowed deviations in WGP with PF compared to LP and because of the changes in penalty intervals between both WGP analyses (scenario I and II). The difference manifests in quantities of maize silage, grass silage and soya meal, dependant on economic parameters, while the hay quantities are the same in all three cases and are at the highest level allowed (2 kg/day). From obtained results it is obvious that soya meal and grass silage are substitutes for proteins. It is interesting that soya meal is included in the ration when prices are more important (LP and WGP I). With regard to Slovene circumstances one would expect the opposite situation. This fact could be explained with ‘economies of scale’ where costs for home produced forage (grass silage) are mostly dependant on tillage and quantity of yields. Due to high importance of cost goal (Scenario 1), deviations never exceed defined goals that much to be in the second interval of overachievement, nor in the second scenario where intervals are extended. This is not the case in other goals (dry matter intake, Ca and P), where also the second (s2) penalty interval operates. From nutrition quality aspect we can conclude that WGP supported by PF yields more balanced ration as LP. These confirm also absolute sums of total relative deviations from nutritional requirements (as one of those parameters that measure the ‘quality’ of obtained results). This is significantly manifested in the second and third fattening period (WGP I and especially WGP II), where penalty system reduces energy surpluses. Even though WGP I rations are more balanced in all four breeding periods, they are for only 4% more expensive as least-cost ration (LP). This fact is emphasised in the second scenario, where intervals for cost deviation are relaxed. As result they increase in comparison to the first scenario for 0.6 to 3.2%, but total deviations (as quality parameter) improve for 0.6 up to 9.8%, respectively. This could be understood as contradiction between nutrition quality and economics. However, when rations are not balanced – even if individual parameter requirements are fulfilled – one can not expect to achieve anticipated daily gains, resulting in higher per unit production costs. Table 4. Obtained results and daily rations formulated with spreadsheet tool and cost penalty function scenarios Fattening period, daily ration 200–300 kg 300–400 kg 400–500 kg 500–600 kg Whole period, 394 days 200–600 kg LP WGP I WGP II LP WGP I WGP II LP WGP I WGP II LP WGP I WGP II LP WGP I WGP II Duration, days 112 91 91 100 394 Feed used, kg/day Hay 2.00 2.00 2.00 2.00 2.00 2.00 2.00 2.00 2.00 2.00 2.00 2.00 788 788 788 Maize silage 8.81 4.05 3.99 14.93 7.06 4.14 21.17 9.04 6.31 19.39 13.17 10.68 6 211 3 237 2 465 Grass silage 6.18 6.20 8.31 11.92 9.91 13.15 8.61 11.18 0 3 211 4 093 Soya meal 0.77 0.41 0.43 0.72 0.34 0.17 0.41 0.17 0.03 0.62 0.08 251 100 66 Mineral components used, g/day Limestone 13.28 8.29 8.50 6.05 9.92 0.00 0.00 0.00 0.00 0.00 0.00 0.00 2 038 1 831 952 Bovisal 0.00 0.00 0.00 0.00 0.00 1.59 0.00 0.00 0.00 0.00 0.00 0.00 0 0 145 Salt 15.3 20.9 20.9 20.1 26.7 29.5 24.5 30.5 33.4 23.5 31.5 33.6 8 132 10 700 11 423 Price, cent/day 99.6 103.5 104.2 120.0 124.8 128.6 129.0 134.2 137.9 145.1 150.9 154.3 Price, EUR/period 111.5 116.0 116.7 109.2 113.5 117.0 117.4 122.1 125.5 132.0 137.3 140.4 470.12 488.92 499.60 Requirements deviations, % ME 0.0 0.0 0.0 6.4 1.0 1.0 14.3 0.2 0.0 0.0 1.0 0.0 MP 0.0 – 0.6 0.0 0.0 – 1.0 0.0 0.0 – 1.0 0.0 0.0 – 1.0 0.0 DM – 7.1 – 1.3 – 1.4 – 9.3 – 8.5 – 6.2 – 12.2 – 18.3 – 16.9 – 9.3 – 3.4 – 2.9 Ca 0.0 – 2.0 – 2.0 0.0 0.0 – 6.4 20.1 5.1 5.6 6.7 11.3 10.4 P 34.2 15.0 15.0 39.0 12.6 5.0 52.3 13.0 6.2 44.0 28.5 22.0 Total deviation 41.2 19.0 18.4 54.6 23.1 18.6 98.8 37.6 28.7 60.0 45.2 35.4 Price deviation, % 0.0 4.0 4.6 0.0 4.0 7.2 0.0 4.0 6.9 0.0 4.0 6.4 Ratio between minerals Ca:P 1.3 1.5 1.5 1.2 1.5 1.5 1.2 1.4 1.5 1.2 1.4 1.4 Physical ration attribute CF, kg/day 1.03 1.29 1.28 1.42 1.67 1.81 1.82 1.94 2.06 1.87 2.30 2.38 CF, % 20 23 23 20 23 25 20 24 25 20 23 24 DM, kg/day 5.2 5.6 5.6 7.2 7.2 7.4 8.9 8.3 8.4 9.3 9.9 10.0 LP = solution obtained by the first module; WGP I = solution obtained by the second module, first scenario; WGP II = solution obtained by the second module, second scenario 193 Žgajnar, J. and K avčič, S. Spreadsheet tool for least-cost and nutrition balanced beef ration form ulation. Acta agriculturae Slovenica, suplement 2 (september 2008). 194 CONCLUSIONS From the results obtained it is apparent that combination of deterministic linear programming technique and weighted goal programming supported by penalty function is useful approach, especially if this is the ‘engine’ from user-friendly optimization tool. Namely, it enables one to formulate least-cost ration not taking to much risk of worsening the ration’s nutritive value that is the main drawback of LP. Refined control is possible through penalty function system that differs between different deviation sizes for each goal separately. This is becoming more and more important in nutrition management. REFERENCES Darmon, N./ Ferguson, E./ Briend, A. Linear and nonlinear programming to optimize the nutrient density of a population's diet: an example based on diets of preschool children in rural Malawi. American Journal of Clinical Nutrition, 75(2002)2, 245–253. Ferguson, E.L./ Darmon, N./ Fahmida, U./ Fitriyanti, S./ Harper, T.B./ Premachandra, I.M. Design of Optimal Food- Based Complementary Feeding Recommendations and Identification of Key »Problem Nutrients« Using Goal Programming. The Journal of Nutrition, 136(2006)9, 2399–2404. Gass, S. The setting of weights in linear goal-programming problems. Computers and Operations Research, 14(1987)3, 227–229. KIS. 2007. Model calculations. Ljubljana, Agricultural institute of Slovenia (unpublished). Lara, P./ Romero, C. Relaxation of Nutrient Requirements on Livestock Rations through Interactive Multigoal Programming. Agricultural Systems, 45(1994)4, 443–453. Rehman, T./ Romero, C. Multiple-criteria decision-making techniques and their role in livestock ration formulation. Agricultural Systems, 15(1984)1, 23–49. Rehman, T./ Romero, C. Goal Programming with penalty functions and livestock ration formulation. Agricultural Systems, 23(1987)2, 117–132. Rehman, T./ Romero, C. The Application of the MCDM Paradigm o the Management of Agricultural Systems: Some Basic Considerations. Agricultural Systems, 41(1993)3, 239–255. Tamiz, M./ Jones, D./ Romero, C. Goal programming for decision making: An overview of the current state-of-the- art. European Journal of Operational research, 111(1998)3, 569–581. Waugh, F.V. The minimum-cost dairy feed. Journal of Farm Economics, 33(1951), 299–310. Žgajnar, J./ Kermauner, A./ Kavčič, S. Model za ocenjevanje prehranskih potreb prežvekovalcev in optimiranje krmnih obrokov. In: Slovensko kmetijstvo in podeželje v Evropi, ki se širi in spreminja, 4. konferenca DAES, Moravske toplice, 2007-11-08/09 (ed.: Kavčič, S.). Ljubljana, Društvo agrarnih ekonomistov Slovenije, Domžale, 2007, 278–288.