**2.2 Nutrient requirements and estimation of nutrient requirements**

Nutrient requirements are estimated based on the Indian Council of Agricultural Research (ICAR) (2013) and it was programmed in Excel VBA. A balanced ration should meet the nutrient requirement. If the growing goat does not get the nutrients, it will affect milk yield and weight at the time of slaughter [16, 17]. The nutrient requirements for the growing goats are given in **Table 2**. There are three major factors of balanced ration: DMI, CP and TDN.

Dry Matter Intake (DMI)(kg/d): Dry matter intake is dependent upon many factors like fodder quality and quantity, climate condition, and nutrient requirement of goat. The DM requirements of goats for different body weights and growth rate functions are different [18]. The dry matter requirement is calculated based on body


#### **Table 1.**

*Composition of most commonly used feeds, fodders and its nutrient composition (DM, CP, TDN) was used as per ICAR (2013) and present cost of the ingredients\*.*


*Design and Development of Self-Made Cost-Effective Microsoft Excel Visual Basic… DOI: http://dx.doi.org/10.5772/intechopen.102470*


**Table 2.**

*Dry matter intake, energy and protein requirements of Indian goat based on body weight (kg) and body weight gain (g).*

weight and average daily gain as per the Indian standard [19]. The total DMI intake calculated is in terms of 'kg' and the formula used in VBA code is given below:

> *Dim a As Integer Dim z As Integer Dim y As Integer z* ¼ *TxtADG:Text a* ¼ *TxtWeight:Text*

*If a*<10 *Then TxtDMIKg:Text* ¼ ð Þ ð Þ *a* � 4*:*1 *=*100 *ElseIf a*<15 *Then TxtDMIKg:Text* ¼ ð Þ ð Þ *a* � 3*:*6 *=*100 *ElseIf a*<20 *Then TxtDMIKg:Text* ¼ ð Þ ð Þ *a* � 3*:*3 *=*100 *ElseIf a*<25 *Then TxtDMIKg:Text* ¼ ð Þ ð Þ *a* � 3*:*025 *=*100 *ElseIf a*<30 *Then TxtDMIKg:Text* ¼ ð Þ ð Þ *a* � 2*:*84 *=*100 *ElseIf a*<35 *Then TxtDMIKg:Text* ¼ ð Þ ð Þ *a* � 2*:*8 *=*100 *Else TxtDMIKg:Text* ¼ 1 *End If*

*Energy:* Energy is expressed as "Total Digestible Nutrients" (TDN). Energy allows doing physical reproductivity. It also provides for the development, lactation, reproduction, and other physiological functions such as feed digestion [20]. The TDN is calculated based on metabolic body weight and average daily gain as per the Indian standard (ICAR 2013).

From the **Table 1**. Will find CF1 (common factor 1) for 5, 10, 15, 20, 25, 30 kg with respective metabolic body weight (BW^0.75). the formula of CF1 is given below in eq (1)

$$\text{CF1} = \frac{\text{TDN}(\text{kg}/d) \times \text{1000}}{\text{BW}^{0.75}} \tag{1}$$

CF1 values for 5, 10, 15, 20, 25, 30 kg are 30.21, 30.05, 30.04, 30.14, 30.14, 30.11 respectively. As we can observe CF1 values for 5 kg is 30.21 and values for 10 and

*Design and Development of Self-Made Cost-Effective Microsoft Excel Visual Basic… DOI: http://dx.doi.org/10.5772/intechopen.102470*

15 kg are taken as 30.04 (average), for more than 15 kg can be taken as 30.13 (average of 3).

Then we find CF2 (common factor 2) with respective average daily gain (ADG) by the following formula (eq (2))

$$\text{CF2} = \frac{\text{TDN}(\text{kg}/d) \times \text{1000} - \left(\text{BW}^{0.75}\right) \times \text{CF1 values}}{\text{BW}^{0.75}} \tag{2}$$

CF2 values are found to be 1.6. Therefore (see eq (3))

$$\text{TDN} \left( \frac{\text{g}}{d} \right) = \left( \text{BW}^{0.75} \right) \times \text{CF1} + \text{ADG} \times \text{CF2} \tag{3}$$

ME (Mcal/d) can be found by using the following formula (eq (4))

$$\text{ME}(\text{Mcal}/d) = \frac{\text{TDN}(\text{g}/d)}{\text{1000} \ast \text{0.28}} \tag{4}$$

Where 0.28 is the common factor.

*If a*<10 *Then TxtTdn:Text* <sup>¼</sup> *<sup>a</sup>*<sup>0</sup>*:*<sup>75</sup> ð Þ� <sup>30</sup>*:*<sup>21</sup> <sup>þ</sup> *<sup>z</sup>* � <sup>1</sup>*:*<sup>6</sup> *ElseIf a*<20 *Then TxtTdn:Text* <sup>¼</sup> *<sup>a</sup>*<sup>0</sup>*:*<sup>75</sup> ð Þ� <sup>30</sup>*:*<sup>04</sup> <sup>þ</sup> *<sup>z</sup>* � <sup>1</sup>*:*<sup>6</sup> *Else TxtTdn:Text* <sup>¼</sup> *<sup>a</sup>*<sup>0</sup>*:*<sup>75</sup> ð Þ� <sup>30</sup>*:*<sup>13</sup> <sup>þ</sup> *<sup>z</sup>* � <sup>1</sup>*:*<sup>6</sup> *End If y* ¼ *TxtTdn:Text TxtMe:Text* ¼ ð Þ *y=*1000 *=*0*:*28

*Protein:* Protein is expressed as crude protein (CP). It is one of the major nutrients in terms of nutrition and cost. CP represents the percentage of protein present in feedstuff. CP is essential for maintenance, increasing the forage intake [21, 22]. It varies for every stage of goat life. Therefore, setting CP level is very important. If the balanced ration does not meet the CP requirement, protein supplies are to be used at a greater cost [23]. At the same time, they were producing a balanced ration for these four requirements to be met compulsorily with the least cost [24]. Then the production will be more and farmers will be benefitted. The CP is calculated based on metabolic body weight and average daily gain as per the Indian standard (ICAR 2013).

From the **Table 1**. Will find CF1 (common factor 1) for 5, 10, 15, 20, 25, 30 kg with respective metabolic body weight (BW^0.75). the formula of CF1 is given in eq (5).

$$CF1 = \frac{CP(\text{g}/d)}{BW^{0.75}}\tag{5}$$

CF1 values for 5, 10, 15, 20, 25, 30 kg are 5.68, 5.87, 5.77, 5.82, 5.81, 5.85 respectively. As we can observe CF1 values for 5 kg is 5.68 and values for 10 kg– 30 kg are taken as 5.82 (average),

Then we find CF2 (common factor 2) with respective average daily gain (ADG) by the following formula (eq (6))

$$\text{CF2} = \frac{\text{CP}(\text{g}/d) - \left(\text{BW}^{0.75}\right) \times \text{CF1 values}}{\text{BW}^{0.75}} \tag{6}$$

CF2 values are 0.46 for less than 10 kg and 0.44 for greater than 10 kg. Therefore (see eq (7))

$$\text{CF(g/d)} = \left(\text{BW}^{0.75}\right) \times \text{CF1} + \text{ADG} \times \text{CF2} \tag{7}$$

*If a*<10 *Then TxtCp:Text* <sup>¼</sup> *<sup>a</sup>*0*:*<sup>75</sup> ð Þ� <sup>5</sup>*:*<sup>68</sup> <sup>þ</sup> *<sup>z</sup>* � <sup>0</sup>*:*<sup>46</sup> *Else TxtCp:Text* <sup>¼</sup> *<sup>a</sup>*0*:*<sup>75</sup> ð Þ� <sup>5</sup>*:*<sup>82</sup> <sup>þ</sup> *<sup>z</sup>* � <sup>0</sup>*:*<sup>44</sup> *End If*

#### **2.3 Research design**

The developed tool (RBT) uses VBA (Visual Basic Application) as front end and back end as excel. It is a simple excel file that is saved as .xlsm form and integrated with VBA code [25]. The user form or first page asks for input data, mainly body weight (BW) in kg and expected average daily gain (ADG) in g, depending upon which, it will calculate the minimum nutrient requirements, i.e., total DMI in 'Kg', CP in 'g', TDN in 'g'. Then should be selected from the list on the second page from roughages, concentrate, and minerals. Once this information is fed, tool RBT will solve for minimization of cost with DMI, TDN, CP as constraints. The tool RBT followed the following linear programming model [26]. (see eq (8))

Objective Function:

$$Minimize \, z = \sum\_{i=1}^{n} c\_i \mathbf{x}\_j \tag{8}$$

*Subjected to constraints.* DMI

$$\sum\_{i=1}^{n} a\_i x\_j = b$$

TDN & CP:

$$b\_{\min} \le \sum\_{i=1}^n a\_i \mathbf{x}\_j \le b\_{\max}$$

Feeds:

$$\mathbf{0} \le \mathbf{x}\_j \le c\_{\max} j = \mathbf{1}, \mathbf{2}, \mathbf{3} \dots n.$$

Where *ci* is the cost of each feed ingredient, *x <sup>j</sup>* represents the number of feeds, *ai* represents the composition of DM, TDN and CP in all feeds, *bmin* and *bmax* are the minimum and maximum requirement of DMI, TDN and CP, *cmax* is the maximum limit for each feed.

#### **3. Results and discussion**

Ration balancing tool for growing goat – a Microsoft Excel VBA based software can calculate the nutritional requirements for animals, such as dry matter intake (DMI), crude protein (CP), total digestible nutrient (TDN), and metabolizable energy (ME), for which equations are derived by using common factor method based on the data of ICAR (2013). The table values of nutrients as per ICAR (2013) and calculated values as per software have high *R*<sup>2</sup> values (DMI-0.989; TDN- 1; CP-0.999) shown in **Figures 2**–**4**.

*Design and Development of Self-Made Cost-Effective Microsoft Excel Visual Basic… DOI: http://dx.doi.org/10.5772/intechopen.102470*

**Figure 2.**

*Comparison of table values of DMI (kg/d) as per ICAR (2013) and calculated values as per software having high R^2 values.*

**Figure 3.**

*Comparison of table values of TDN (g/d) as per ICAR (2013) and calculated values as per software have high R^2 values.*

#### **Figure 4.**

*Comparison of table values of CP (g/d) as per ICAR (2013) and calculated values as per software have high R^2 values.*

Once the nutrients requirements are found, the application asks the user to select the available feeds which are listed in the application. Then it will provide a balanced ration using LP model [27]. The expert nutritionist has examined the created application in NIANP, and the results of some specific animal categories (Goat 1 and Goat 2) are given in **Table 3**. The developed RBT will find the least-cost ration with the consumer selected feed without breaching the nutrient requirement shown in the sixth column of **Table 3**.


#### **Table 3.**

*Feed formulation for two different goats based on DMI, CP, TDN and ME by RBT depending on the weight and average daily gain.*

As per **Table 3**, It is observed that two different categories of goat listed for validation, DMI, CP, TDN and ME criteria, are determined depending on both the weight and average daily gain as in the second column of **Table 3**. The developed RBT will find the least-cost ration with the consumer selected feed without breaching the nutrient requirement shown in the fifth column. To find the optimal solution, the RBT uses the Excel solver. The Excel Solver is efficient in obtaining feasible solutions nonlinear model for goats and increased daily gain and milk yield [28]. The nutrients TDN and CP required for growing goat according to Mandal, 2005 [29] for goat 1 with the body weight of 20 kg and an ADG of 75 g are 351 g and 79 g, respectively, for goat 2 with the body weight of 20 kg and 100 g of ADG, the requirements are 446 g and 100 g respectively. The nutrients TDN and CP calculated by developed application for Goat 1 are 349.5 g and 77.74, and for Goat 2 are 444.7 g and 99.51 g. There is a very small difference 1.5 g (0.4%) and 1.26 g (0.3%) in TDN and CP for goat 1, for goat 2 TDN and CP difference is 1.3 g (1.6%) and 0.49 g (0.49%) between RBT and Mandal et al. (2017). The required nutrients for two different goat categories and calculated by balanced ration are shown in **Figures 5** and **6** for goat1 and 2, respectively.

*Design and Development of Self-Made Cost-Effective Microsoft Excel Visual Basic… DOI: http://dx.doi.org/10.5772/intechopen.102470*

#### **Figure 5.**

*Graphical representation of required vs. calculated nutrients by balanced ration for goat 1.*

#### **Figure 6.**

*Graphical representation of required vs. calculated nutrients by balanced ration for goat 2.*

From the above studies and evaluation, it can be confirmed that the calculated values for DMI, TDN and CP are almost equal to the values of ICAR (2013). A Ration Balancing Tool (RBT)" is developed using Excel VBA, which gives a balanced ration for the goats with the available ingredients that satisfy all the nutrient requirements. Many software programs are available to customize ration for the lowest cost [30]. However, scanty applications are available for goat least-cost ration formulation. This study explains how the application is exceptional and more efficient and convenient compared to all other software programs, most of which are not user-friendly, and farmers must rely on expert assistance to implement it. For the commercial business reason, many software programs are developed for the client, wherein small dairy farmers still have to rely on specialists for optimized rationing. This tool is very simple to execute and user friendly. It is designed to determine the nutritional requirement of goats, depending on their weight and daily gain and to optimize goat ration at least cost.

#### **3.1 Steps followed for formulation**

*Step1:* once the excel sheet is open, page 1 of the user form will appear as shown in **Figure 7**. This page contains three tabs '*Introduction', 'Methodology' and*

**Figure 7.**

*Screenshot of the first page of developed VBA application showing the introduction, methodology and help sections.*

*'Help'.* Detailed information on goat feed formulation is given in the 'Introduction' tab. The procedure to use the application is given step by step in the 'Methodology' tab. How to add excel options to the applications is mentioned in the 'help' tab.

*Step2:* Clicking the NEXT button, page 2 will be displayed as shown in (**Figure 8**) where users are required to enter input data such as body weight (kg), expected average daily gain (g) of goat. Depending on the input data, the tool RBT calculates the DMI, TDN and CP required for a particular goat.

#### **Figure 8.**

*Screenshot of the second page developed VBA application where the user can input the basic data.*

*Design and Development of Self-Made Cost-Effective Microsoft Excel Visual Basic… DOI: http://dx.doi.org/10.5772/intechopen.102470*

#### **Figure 9.**

*Screenshot of the third page developed VBA application where users can select feeds from roughages, concentrate and minerals.*

*Step3:* Users must select feeds from roughages, concentrate and minerals on page 3 as shown in (**Figure 9**). The option is given to fix the minimum and maximum feed selected. If not, it will take standard values set by the tool. Users can also add new feeds depending on the availability in the subpage shown in (**Figure 10a**).

*Step4:* After the selection of feed has been completed, the tool RBT will provide an optimized ration cost to satisfy all nutrients at the minimum cost. If any nutrient requirement has not been met, the app will ask for feed refining where the user has to reselect the feed, this case is shown in (**Figure 10b**).

*Step5:* For the final output, the user must click on the 'Go to Result'. It opens excel sheet where the user can find.

1. Single goat ration cost.


Printouts can be taken for all the results.

The features of the developed application are as follows, firstly Data Maintenance; if no feed is listed, the feed with nutrient composition can be uploaded by the consumer while selecting the feed. It allows the consumer to reasonably apply the feed available locally and reduce the cost. Consumers can get optimized ration in maximum effective steps by selecting the animal data and picking the feeds and then tapping on the "Solve" icon to get the result as it is user-friendly. For Display and printing, after the solution is found, there is an option for the result to be printed on a fed basis with feed quantity and total DM intake per kg. System requirements are also minimal as All MS Office versions can be used, and no special hardware or RAM is needed. The macros and solver options in VBA reference need to be enabled. The application provides the result in three ways: cost of single goat ration: Here, the total DMI is provided for one goat. This will help the consumer get an optimized ration and provide the right amount of roughage, concentrate, and


**Figure 10.**

*(a) Screenshot of the sub page developed VBA application where user can add new feed. (b) Screenshot of the fourth page developed VBA application where user can refine the feed.*

minerals to be included in the ration. Finally, the price of each feed is given in the result. Cost of 100 kg ration on dry matter basis: Here, it estimates 100 kg at a time, which can be fed to goats at periodic intervals. The amount of roughage, concentrates, and minerals to be added to make 100 kg and its cost will be shown. Ration cost for the number of goats available: Here, the ration will be estimated on a dry matter basis for the number of available goats. The output is given in **Figure 11**.

*Design and Development of Self-Made Cost-Effective Microsoft Excel Visual Basic… DOI: http://dx.doi.org/10.5772/intechopen.102470*


#### **Figure 11.**

*Screen shots of output result estimated on a dry matter basis for the number of available goats.*

#### **4. Conclusions**

This study showed that how the excel VBA can be used to analyzing the nutrient requirement and producing a balanced ration for livestock (goat) are fundamental aspects of reducing goats' feeding cost. Hence Excel Visual Basic Application (VBA) has been developed. Developed 'RBT' for beneficial for dairy farmers, which is based on a linear programming model. The ICAR (2013) table values for nutrients and the software-calculated values have high R2 values (DMI-0.989; TDN- 1; CP-0.999). It can be confirmed from the aforementioned studies and evaluations that the computed values for DMI, TDN, and CP are nearly identical to the ICAR (2013) values. Just by giving the goat's minimum input, the application will calculate the nutrient requirement and the balanced ration at the lowest cost. Adding extra feed allows the user to add the feed available, which can lower the cost. The answer produced by the application is verified by a nutritionist at the National Institute of Animal Nutrition and Physiology (NIANP), Bangalore. Hence, it is concluded that even this application could be used quite effectively by dairy farmers. By understanding the nutrient requirements, the same can be developed for other livestock animals such as cattle, buffalo, and pigs.
