Design and Development of Self-Made Cost-Effective Microsoft Excel Visual Basic Application for Livestock Ration Formulation

Abstract

One of the most important aspects of the livestock sector is ration cost optimization, which results in profit and ideal animal health. Manually preparing rations is time consuming and unsafe. Whereas computers can quickly formulate a ration that meets all of the nutritional requirements, after giving standard data on feeds. However, the existence of the ideal computer programme is questionable; if it exists, it is more expensive, less user-friendly, exclude local feeds, be limited to a particular region/country, feed composition may differ. As a result, in this chapter, the user will learn how to create and develop a self-made least-cost ration formulation using the locally available feeds, so that user may easily build their computer Programme using Visual Basic application of Microsoft Excel. There are three phases to ration formulation for any animal (ruminant or non-ruminant). The first phase requires the user to know the available feeds and their nutrient composition. The second part involves determining which nutrients are important for animals and creating nutrient equations. The third phase involves the creation of a linear programming model. Finally, the interface is being designed. Each phase is thoroughly explained in excel, with suitable data and reference coding.

1. Introduction

The primary goal of ration formulation in animal production is to offer a balanced diet that supports physiological functions such as growth, maintenance, reproduction, and lactation while also providing energy for physical and metabolic activity [1]. A standard and efficient feed formulation must include all the classes of feedstuffs (Animal Nutrition Group, India) [2] as provided in Figure 1.

Figure 1.

Different classes of standard feedstuff must be included in the feed formulation according to animal nutrition group, India.

A concentrate is a feed or feed mixture that provides increased levels of primary nutrients (protein, carbohydrates, and fat) while containing less than 18 percent crude fiber (CF) and low moisture. These are high in nitrogen-free extract (NFE) and Total Digestible Nutrients (TDN) and low in crude fiber [3]. There are classified into two categories: energy-rich concentrates and protein-rich concentrates, based on the content of crude protein (CP). When the crude protein content of dry concentrates is less than 18 percent, they are categorized as energy-rich concentrates, and when the CP value is greater than 18 percent, they are defined as protein-rich concentrates [4, 5].

Roughages are heavy foods that contain substantially less digestible material, such as crude fiber greater than 18% and a low TDN content (about 60%) on a dry basis. Roughages differ in the level of protein, mineral, and vitamin composition. Some roughages, particularly legumes, are good suppliers of calcium and magnesium [6]. The phosphorus concentration is likely to be moderate to low, whereas the potassium content is likely to be high, these concentrations are affected by the plant species, soil, and fertilization strategies all have an impact on trace minerals. Roughages are categorized into two classes based on moisture content: dry and green or succulent roughages [7]. Green roughages often have 60–90 percent moisture, while dry roughages have just 10–15 percent moisture. Green roughages are divided into several types for ease of use, including pasture, produced fodder crops, tree leaves, and roots. Based on the nutritional content and preparation methods, dry roughages have been further categorized as hay and straw [8].

Minerals available in the feeds are of different types i.e., Micro minerals, macro minerals and chelated minerals. Microminerals, also known as trace minerals, are needed in milligrams (mg) or microgram (g) quantities [9]. They’re found in trace amounts in animal tissues and feeds. They’re frequently found in enzyme cofactors and hormones. Cobalt, iodine, zinc, copper, manganese, and selenium are examples of micro or trace minerals. Macro-minerals play a specific role in the formation and function of the animal’s body. Animals require the following seven macro-minerals: calcium (Ca), phosphorus (P), sodium (Na), magnesium (Mg), potassium (K), sulfur (S), chlorine (Cl) [10]. Chelated minerals are a class of organic minerals that are divided into proteinates, chelates, and other complexes based on their molecular structure [11]. A chelated mineral is one that has two or more chemical interactions with peptides or amino acids, such as copper or zinc. Each one has a different level of absorption and effectiveness.

The National Research Council has studied nutrient requirements based on several criteria such as dry matter intake (DMI), total digestible nutrients (TDN), crude protein (CP), metabolic energy (ME), calcium (Ca), phosphorus (P), and other elements that affect intake and techniques of prediction [12]. The entire weight of feed minus the weight of water in the feed is expressed as a percentage and is known as dry matter. In feeding studies, dry matter intake is determined by weighing the whole ration supplied and the amount of feed left by the animal [13]. The term “total digestible nutrients” stems from an old system of calculating available energy in feeds and animal energy requirements using a complex calculation of measured nutrients. The whole amount of protein present, determined from the total nitrogen available, is referred to as a crude protein. The percent nitrogen is multiplied by 6.25 to get the percent protein. The digestible energy intake minus the energy in the urine minus the energy in the gaseous result of digestion equals the metabolizable energy. Calcium is required for bone formation, neuron function, and the production of milk and eggs in animals. Phosphorus is also included in a wide range of co-enzymes, nucleic acids, proteins and amino acids [14].

It is essential to know the significance of these nutrients in animal feed. Animal disease control, as well as feed and fodder shortages, are the most pressing issues in animal husbandry. Farmers frequently encounter the following issues.

1. Nutrient requirement for the livestock animal,

2. The amount of feed that must be supplied every day to boost productivity,

3. Feed ingredient costs must be kept under control.

Animal nutrition is necessary for livestock production to be effective. Animal feed efficiency and growth rate can both benefit from good nutrition. Diets that meet the demands of animals must be provided.

This work used goats to fully understand nutritional needs and feed composition through the use of a visual basic application. The three phases of the study are explained in this chapter, the first phase is the selection of feeds and understanding their nutrient composition. The second part involves determining which nutrients are important for animals and creating nutrient equations. The third phase entails the establishment of a linear programming model, followed by the design of the interface.

2. Material and methods

2.1 Data collection

Goats can grow well and produce maximum milk if balanced and nutritious food is fed [15]. A balanced ration should contain digestible nutrients, vitamins, and minerals, including concentrate feeds and green and dry roughages. The feed list was created based on the most commonly used feeds, fodders and its nutrient composition was used as per ICAR (2013) given in Table 1.

	sl. No	Name of the ingredients	Cost*	DM %	CP %	TDN %
Roughages	1	Bajra -Napier Green grass	0.5	20	8	52
	2	Maize Green fodder	0.5	20	8	60
	3	Multicut sorghum green fodder	1	90	7	50
	4	Lucerne Green Fodder	0.7	20	15.8	60
	5	Wheat straw	1	90	3.3	42
	6	Maize stover	0.9	90	3	42
	7	Ragi Straw (Dry)	0.9	90	6	42
	8	Cereal straw	1	90	3.5	40
Concentrate	10	Maize	1.7	90	8.1	79.2
	11	Wheat	1.8	90	8	75
	12	Wheat Bran	2.2	75	12	70
	13	Soya DOC	3	90	42	70
	14	cotton seed	2.2	90	16	72.2
	15	Cotton DOC	1	90	18	45
	16	Copra DOC	3	90	22	70
	17	Concentrate Mix Type I	3	90	22	75
	18	Concentrate mix Type II	2.5	90	16	70
Minerals	19	Urea	1	95	287.5	0
	20	MM (Mineral Mixture)	6	90	0	0
	21	DCP	3.8	90	0	0
	22	Trace Mineral Mixture	3.5	98	0	0
	23	Salt	0.5	90	0	0

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*.

*

Cost of the ingredients can be changed in the application.

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.

Body weight (BW) (kg)	Body weight gain (g/d)	Daily Dry Matter Intake (DMI)		Energy Requirements		Protein Requirements
		Kg	% BW	TDN kg/d	ME	CP
					Mcal/d	g/d
5	0	—	—	0.101	0.36	19
5	25	0.2	4	0.141	0.51	31
5	50	0.21	4.2	0.181	0.66	42
5	75	—	—	0.221	0.8	53
5	100	—	—	0.262	0.95	65
10	0	—	—	0.169	0.61	33
10	25	0.36	3.6	0.21	0.76	44
10	50	0.37	3.7	0.25	0.9	55
10	75	0.35	3.5	0.29	1.05	67
10	100	—	—	0.33	1.19	78
10	125	—	—	0.371	1.34	89
10	150	—	—	0.411	1.48	100
15	0	—	—	0.229	0.83	44
15	25	0.45	3	0.27	0.98	56
15	50	0.5	3.3	0.31	1.12	67
15	75	0.5	3.3	0.35	1.26	78
15	100	—	—	0.39	1.41	89
15	125	—	—	0.431	1.56	101
15	150	—	—	0.471	1.7	112
20	0	—	—	0.285	1.03	55
20	25	0.58	2.9	0.325	1.17	66
20	50	0.6	3	0.365	1.32	78
20	75	0.62	3.1	0.405	1.47	89
20	100	0.62	3.1	0.446	1.61	100
20	125	—	—	0.486	1.76	111
20	150	—	—	0.526	1.9	123
25	0	—	—	0.337	1.22	65
25	25	0.68	2.7	0.377	1.36	76
25	50	0.71	2.8	0.417	1.51	88
25	75	0.73	2.9	0.457	1.65	99
25	100	0.74	3	0.498	1.8	110
25	125	0.71	2.8	0.538	1.94	121
25	150	—	—	0.578	2.09	133
30	0	—	—	0.386	1.39	75
30	25	0.77	2.6	0.426	1.54	86
30	50	0.8	2.7	0.466	1.69	97
30	75	0.83	2.8	0.507	1.83	108
30	100	0.84	2.8	0.547	1.98	120
30	125	—	—	0.587	2.12	131
30	150	—	—	0.627	2.27	142

Table 2.

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

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 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:

$$\mathit{Dim}a\mathit{As}\mathit{\text{Integer}}$$

$$\mathit{Dim}z\mathit{As}\mathit{\text{Integer}}$$

$$\mathit{Dim}y\mathit{As}\mathit{\text{Integer}}$$

$$z=\mathit{\text{TxtADG}}.\mathit{\text{Text}}$$

$$a=\mathit{\text{TxtWeight}}.\mathit{\text{Text}}$$

$\mathit{\text{If}}a<10\mathit{\text{Then}}$

$\mathit{\text{TxtDMIKg}}.\mathit{\text{Text}}=\left(\left(a\times 4.1\right)/100\right)$

$\mathit{\text{ElseIf}}a<15\mathit{\text{Then}}$

$\mathit{\text{TxtDMIKg}}.\mathit{\text{Text}}=\left(\left(a\times 3.6\right)/100\right)$

$\mathit{\text{ElseIf}}a<20\mathit{\text{Then}}$

$\mathit{\text{TxtDMIKg}}.\mathit{\text{Text}}=\left(\left(a\times 3.3\right)/100\right)$

$\mathit{\text{ElseIf}}a<25\mathit{\text{Then}}$

$\mathit{\text{TxtDMIKg}}.\mathit{\text{Text}}=\left(\left(a\times 3.025\right)/100\right)$

$\mathit{\text{ElseIf}}a<30\mathit{\text{Then}}$

$\mathit{\text{TxtDMIKg}}.\mathit{\text{Text}}=\left(\left(a\times 2.84\right)/100\right)$

$\mathit{\text{ElseIf}}a<35\mathit{\text{Then}}$

$\mathit{\text{TxtDMIKg}}.\mathit{\text{Text}}=\left(\left(a\times 2.8\right)/100\right)$

$\mathit{\text{Else}}$

$\mathit{\text{TxtDMIKg}}.\mathit{\text{Text}}=1$

$\mathit{End}\mathit{\text{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)

$$\mathit{CF}1=\frac{\mathit{TDN}\left(\mathit{kg}/d\right)\times 1000}{{\mathit{BW}}^{0.75}}$$E1

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 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))

$$\mathit{CF}2=\frac{\mathit{TDN}\left(\mathit{kg}/d\right)\times 1000-\left({\mathit{BW}}^{0.75}\right)\times \mathit{CF}1\mathit{\text{values}}}{{\mathit{BW}}^{0.75}}$$E2

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

$$\mathit{TDN}\left(\frac{g}{d}\right)=\left({\mathit{BW}}^{0.75}\right)\times \mathit{CF}1+\mathit{ADG}\times \mathit{CF}2$$E3

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

$$\mathit{ME}\left(\mathit{\text{Mcal}}/d\right)=\frac{\mathit{TDN}\left(g/d\right)}{1000\ast 0.28}$$E4

Where 0.28 is the common factor.

$\mathit{\text{If}}a<10\mathit{\text{Then}}$

$\mathit{\text{TxtTdn}}.\mathit{\text{Text}}=\left(a^0.75\right)\times 30.21+z\times 1.6$

$\mathit{\text{ElseIf}}a<20\mathit{\text{Then}}$

$\mathit{\text{TxtTdn}}.\mathit{\text{Text}}=\left(a^0.75\right)\times 30.04+z\times 1.6$

$\mathit{\text{Else}}$

$\mathit{\text{TxtTdn}}.\mathit{\text{Text}}=\left(a^0.75\right)\times 30.13+z\times 1.6$

$\mathit{End}\mathit{\text{If}}$

$y=\mathit{\text{TxtTdn}}.\mathit{\text{Text}}$

$\mathit{\text{TxtMe}}.\mathit{\text{Text}}=\left(y/1000\right)/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).

$$\mathit{CF}1=\frac{\mathit{CP}\left(g/d\right)}{{\mathit{BW}}^{0.75}}$$E5

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))

$$\mathit{CF}2=\frac{\mathit{CP}\left(g/d\right)-\left({\mathit{BW}}^{0.75}\right)\times \mathit{CF}1\mathit{\text{values}}}{{\mathit{BW}}^{0.75}}$$E6

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

$$\mathit{CP}\left(g/d\right)=\left({\mathit{BW}}^{0.75}\right)\times \mathit{CF}1+\mathit{ADG}\times \mathit{CF}2$$E7

$\mathit{\text{If}}a<10\mathit{\text{Then}}$

$\mathit{\text{TxtCp}}.\mathit{\text{Text}}=\left(a^0.75\right)\times 5.68+z\times 0.46$

$\mathit{\text{Else}}$

$\mathit{\text{TxtCp}}.\mathit{\text{Text}}=\left(a^0.75\right)\times 5.82+z\times 0.44$

$\mathit{End}\mathit{\text{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:

$$\mathit{Minimize}z=\sum _{i=1}^n{c}_i{x}_j$$E8

Subjected to constraints.

DMI

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

TDN & CP:

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

Feeds:

$$0\le x_j\le c_{\mathit{max}}j=1,2,3\dots n$$EQ2

Where $c_i$ is the cost of each feed ingredient, $x_j$ represents the number of feeds, $a_i$ represents the composition of DM, TDN and CP in all feeds, $b_{\mathit{min}}$ and $b_{\mathit{max}}$ are the minimum and maximum requirement of DMI, TDN and CP, $c_{\mathit{max}}$ 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^2$ values (DMI-0.989; TDN- 1; CP-0.999) shown in Figures 2–4.

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.

Animal Details	Nutrient Requirement	Feeds Selected	Suggested feed quantity with price and total cost		Nutrients from feed
Weight (Kg): 15; ADG (g/d):75	DMI(Kg):0.5; CP (g):77.74; TDN(g):349.5; ME(Mcal):1.24	Name	Quantity	Cost (Rs)	DMI(Kg):0.54; CP (g):77.74; TDN(g):349.5; ME(Mcal):1.26
		Maize Green fodder	0.04	0.55
		Multicut sorghum green fodder	0.17	0.55
		Ragi straw (dry)	0.03	0.24
		Wheat	0.22	3.91
		Soya DOC	0.09	3.87
		Concentrate Mix Type 1	0.08	2.20
		MM	0.01	0.61
		Salt	0.01	0.04
		Total	0.63	11.97
Weight (Kg): 20; ADG (g/d):100	DMI(Kg):0.60; CP (g):99.51; TDN(g):444.76; ME(Mcal):1.58	Multicut sorghum green fodder	0.2	0.67	DMI(Kg):0.67; CP (g):99.52; TDN(g):444.76; ME(Mcal):1.61
		Maize	0.14	3.18
		Wheat	0.16	2.88
		Soya DOC	0.12	5.26
		Concentrate Mix Type I
		MM	0.01	0.75
		Salt	0.01	0.05
		Total	0.73	15.48

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.

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 ‘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.

Figure 7.

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

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.

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).

Figure 9.

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

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.

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.

2. Cost of 100 kg ration on dry matter basis.

3. Ration cost for the number of goats available.

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 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.

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.

Acknowledgments

Raw data were generated at ICAR-National Institute of Animal Nutrition and Physiology. Derived data supporting the findings of this study are available from the author Prof. Rajendran on request. The authors thank all co-workers for their help and cooperation in this trial. Vishal Patil Conceptualized developed Methodology Data Collection, wrote the manuscript, Radha Gupta and Rajendran D Supervised and Validated. Ravinder Singh Kuntal assisted in Writing- Reviewing and Editing.