Week 6

Ration Formulation

Sections One - Five

Section 1

In this section, we will cover Excel spreadsheets which will be used throughout the quarter to formulate rations for different species. In formulating rations for each species throughout the remainder of the quarter, we will use a very common spreadsheet. Using more than two ingredients and/or balancing for multiple nutrients is almost impossible without the use of a computer. For the most part, people that formulate rations for dairy cattle, beef cattle or any other specie use specific programs developed for that particular group of animals. However, it is beyond the scope of this class to teach a new program each week. Therefore, we will use a similar program each week for all species. The spreadsheet that we are going to cover in this discussion is one that we will use for dairy cattle. Subsequent spreadsheets for other species will be very similar. After doing the dairy problem set, you will be familiar with the workings of the spreadsheet and subsequent problem sets will be simpler from the standpoint of understanding how the spreadsheet works. You will need Microsoft Excel to be able to use the spreadsheets. I have prepared several spreadsheets for this discussion, so let us get started.

Section 2

By clicking on the link to the Dairy Spreadsheet – One, choose save to disk when prompted, remember where you saved your file. Saving to the disk will allow you to toggle back and forth between the web page, text, and audio and the spreadsheet. Open Excel and open the file you have just saved. You will be told that this workbook contains macros and given the option to enable or disable them. Click enable, the macros in the workbook are safe. If you were not given the option to enable or disable the macros, you need to change the security setting in Excel. Close the file you have just opened but not Excel. Click on tools, then options, select the security tab, and click on the macro security button and select the circle beside medium security, this gives you the option to enable or disable macros. Remember that macros from unknown sources may be detrimental, so do not enable them. If you had to change your macro security, (you have done it).

Now, open the spreadsheet that you saved to your disk. Within this workbook, there are three sheets and we will cover each in sufficient detail for you to formulate rations. At the bottom left are sheet tabs. Currently, you should be on the feed sheet, if not click on the feed sheet now. This is the feed library that we will use to balance rations. There are over 200 feeds that will be available to you to choose from. Notice that the nutrient concentration headers for each feedstuff are in rows 1-3. For example, feed number 1 in column A is alfalfa, fresh, mature or the name alfalfa, fresh, mature is in column B. And this is the first feed in the feed library. This is alfalfa pasture that is in a mature, vegetative state. Column C notes that this feedstuff is a forage. Other classes in this library are concentrate or C, mineral or M and vitamin or V. This is pasture and contains 20% dry matter as noted in column D. The next eight columns or columns E-L, are the energy value of this feed for different specie. Next is the protein content which is 17%, followed by that are carbohydrates, amino acids, minerals and vitamins. Take a moment to scroll to the right to look at the composition of this feedstuff. Each component or nutrient will not be used by all species. Some of these may be unfamiliar and we will cover each as the specie lectures progress. Take a moment to scroll down and look at the different feedstuffs that will be available to us to balance rations.

First, are forages or feeds 1-82. Next, are the high energy feeds or feeds 96-132. Next, are protein supplements, feeds 141-174. Next, are a few commercial ingredients for dairy cattle, feeds 179-194. This is followed by minerals, vitamins and then a couple of feeds for dairy calves. You will notice that there are several rows labeled blank forage, blank grain, etc… These are spaces that you could enter feeds that are not in the feed library. All rows on the feed sheet are protected except those rows that are labeled blank. This keeps one from inadvertently deleting feeds in the library.

For the next section, you will need your textbook, so get it out and have it available. Click on the dairy tab and we will cover the sheet that will be used in dairy cattle ration formulation. We will briefly look at four sections of the sheet and then go through an example. At the top left, there is an area to save, retrieve and delete rations. The area from cell D-3 to J-15 are inputs of animal variables and requirements. There are a total of 25 inputs for dairy cows and heifers ranging from body weight, feed intake, milk yield, energy requirement, protein requirement and vitamins and minerals. In the section from row 22-36, is the ration formulation area. This is where we will input feeds, change feeding amounts to meet the nutrient requirements of the animal. Scrolling on down, rows 44-134, is the ration in a printable format. Now on this spreadsheet, you can only enter values in cells shaded gray. Other cells may contain formulas and are therefore protected. Next, we will go through an example to get us more familiar with the spreadsheet. Make sure you have your textbook at hand.

Table 1 shows the inputs that we will use for this example and we will go through how we derive these inputs.

You will always be given body weight, milk yield and milk fat percentage for all problems or for all dairy problems. For other inputs, we will use the textbook. The first table that we will use is Table 14, page 541. It is laid out in four sections. The top section is requirements for maintenance of lactating cattle and the heading is Maintenance of mature lactating cows. The second section is nutrient requirements for pregnant nonlactating cows or dry cows and the section is labeled, Maintenance plus the last two months of gestation of mature dry cows. The third section is requirements for milk production and the heading is Milk production - nutrients/kilogram of milk at different fat percentages. The bottom section of the table is nutrients for gain and loss of body weight and will not be used in this class. There are two NEL values that we need, one for maintenance and the other for milk production and this is similar for crude protein, calcium and phosphorus. This table uses metric units, so we will have to do some conversions along the way. The cow in this example weighs 1,400 lbs which is equivalent to 636 kilograms. To convert from pounds to kilograms, divide by 2.2. To convert from kilograms to pounds, multiply by 2.2. In the top section of the table, with the heading Maintenance of Mature Lactating Cows, this cow would fall between the 600-650 kg rows. We will choose 650. Now you could choose either row, but we will choose the higher body weight to be liberal in energy estimates. I suggest that you make a table to keep track of the requirements for maintenance and milk production as shown below in Table 2.

If we choose a 650 kg row and go across, one of the first numbers we encounter is NEL, and this cow, at 650 kilos, will require 10.3 megacals of NEL per day. There are three other energy estimates, ME, metabolizable energy, DE, digestible energy, and TDN or total digestible nutrients, but we will not use them in this class. Going on across the top section of the table, you next come to crude protein and this cow would require 428 grams of protein per day. The next row is calcium or 26 grams. And finally, phosphorus or 19 grams. So those are the four components that we are going to get from this table, NEL, protein, calcium and phosphorus, and these values are shown in Table 2 below.

We will skip the second section of the table because this cow is lactating. If you were formulating rations for a pregnant, nonlactating or dry cow, then you would use only the second section of the table. Now we need to determine the requirements for milk production and we will use the third section of the table labeled Milk Production, Nutrients per Kilo of Milk at Different Milk Fat Percentages. Note that these are nutrients per kilogram of milk. The cow in this example is producing 79 lbs of milk or 36 kg of milk per day. You will need to know the fat percent of the milk and we will assume, in this example, that this cow is producing milk containing 3.5% fat since this is close to the average of Holstein milk. Milk fat percentages in this section of the table range from 3.0-5.5%. Find the row with 3.5% fat and go across to NEL. This cow needs .69 megacals of NEL per kg of milk for a total of 24.8 megacals of NEL. We arrived at this number by multiplying 36 kilos of milk times .69 megacals of NEL per kg of milk. The cow needs 84 grams of protein per kilo of milk or a total of 3,024 grams for milk production. The cow needs 2.97 grams of calcium per kilo of milk for a total of 106.9 grams for milk production and 1.83 grams of phosphorus per kilo of milk for a total of 65.9 grams of phosphorus for milk production. So, in our table, we have our four nutrients, NEL, crude protein, calcium and phosphorus and we have the requirements for both maintenance and milk production. If we sum these two requirements for each nutrient, we will get the total required for NEL or 35.1, 3,452 grams of crude protein, 132.9 grams of calcium and 84.9 grams of phosphorus. And these values are depicted in Table 1.

For the remaining minerals and vitamins, we will use Table 17 on pages 556 and 557. This table has headings for animals of different physiological states and are listed at the top of the table. Lactating cows of differing body weights at milk production are first to the left, followed by early lactation cows, dry cows and lastly is calves and heifers. Many times the exact group of animal that we are formulating for is not listed in the table, so we have to choose from a column that most closely fits the animal. For this example, there is not a row that contains a 636 kg cow, so we would choose either a 600 or a 700 kilo cow. This cow is producing milk with 3.5% fat. So, it would be logical to choose a 700 kilogram row, now go across the table until you find a milk yield similar to the example cow. Actually, there is a column within the 700 kilogram row that contains 36 kilos of milk and this is the third column from the left under milk yield. This table lists nutrient concentrations, not actual amounts, of nutrients required by the animal. For example, this cow would require an energy concentration of 1.62 megacals of NEL per kilogram of dry matter, 16% crude protein and so forth. Continue down the 36 kg column of the 700 kilo weight row, the first mineral that we need is magnesium, it should make up .2% of the diet. You would continue down the column and copy the other minerals and vitamins. These values are shown in Table 1. Take a few minutes to enter the values in the spreadsheet and then continue on to the next section or you can continue on to the next section and open the spreadsheet or open the new spreadsheet with the values already entered.

Section 3

Dairy Spreadsheet – Two

Now, we need to choose some feedstuffs to balance the ration for this group of cows. The feeds that we are going to use are feed number 31 which is corn silage, feed number 5 alfalfa hay, feed 108 corn grain, and feed 160 which is soybean meal.

Enter feed number 1 in cell A-22, enter 5 in cell A-23, 108 in cell A-24, and 160 in cell A-25. You will notice that the feed name and composition for each feed is now in the spreadsheet. And there is room to enter a total of 15 feeds.

Now that we have our nutrient requirements and feedstuffs, we are now ready to balance the ration. We’ll talk more about this in the dairy lectures, but for now remember that forages are the base of any dairy diet and at least 50% of the diet should be forage. The two forages that we have to choose from are corn silage and alfalfa hay. So enter 15 in cell C-22 for alfalfa and 40 in cell C-23. At this point, we are feeding 15 lbs of alfalfa hay and 40 lbs of corn silage. This is about half of what the cows are capable of consuming. Now we need to take a closer look at the output rows which begin on row 37. We are feeding the cows 26.7 lbs of dry matter which is in cell G-37, 18 megacals of energy or NEL which is in cell I-37 and 3.9 lbs of crude protein which is in cell S-37. In row 38, the nutrient requirements from the input table are listed. This cow requires 48 lbs of dry matter per day, 35.1 megacals of NEL and 7.6 lbs of crude protein. Row 39 gives a percentage greater or lower, if lower, it is in brackets, of the ration versus the requirement. This ration is 44.4% below intake, 48.8% below NEL requirement and 48.5% below crude protein requirement. If the ration or if the component in the ration is less than requirement, the number is in brackets. Row 40 lists whether the nutrient is within plus or minus 3% of the requirement and we should always try to balance rations within plus or minus 3% of the requirement. At this point, we are low on most nutrients. NEL is the lowest. And we still have room to add more feed because intake has not been exceeded. Look in column H and find the feed with the highest energy or NEL concentration. It is corn grain. We would add corn grain to meet the remainder of the NEL requirement. Changing cell C-24 to 21 lbs meets the NEL requirement, at this point we are .1 % below requirement. However, protein is still low. Which feed has the greatest protein concentration? It is soybean meal. Replacing 4.0 lbs of corn grain (change cell C24 to 17 and add 4.5 in cell C25) with 4.5 lbs of soybean meal meets the protein requirement. At this point, the ration is balanced for NEL or energy and protein. What about dry matter per pounds in column G? Are we too low or too high? Well, actually, we are too low at this point. Now we could feed the cows exactly 45.4 lbs of dry matter per day and NEL and protein requirements would be met. However, this practice is known as limit feeding and we rarely limit feed dairy cows. Actually, we want them to eat as much as possible and generally feed ad-lib or free-choice. So we need to get more feed into this diet. Forages are typically the cheapest ingredients and the foundation of dairy rations and we would add more forage to this diet. We will also have to adjust corn grain and soybean meal because NEL and protein will be in excess when more forage is added. Take a few moments and try to balance this diet for dry matter intake, NEL and protein and then move on to the next section.

Section 4

Click on the link, Dairy Spreadsheet – Three, and choose save to disk when prompted and remember where you saved the file. Open Excel and open the file you have just saved.

I balanced the diet with 57 lbs of corn silage, 20 lbs of alfalfa hay, 10 lbs of corn grain and 3 lbs of soybean meal. Scroll to the right and look at some of the other ration components. In column Z is ADF, we will cover this in more detail in the dairy lectures. In row 38, it says that ADF should be at least 19% and this is a minimum and our ration is slightly greater than 19%. We also met the requirement for NDF, effective NDF or eNDF and are slightly over on NFC or nonfiber carbohydrates. None of the minerals or vitamins are balanced at this point, but we do not have a supplement in the ration to balance for those that are deficient. The ones we are most concerned about are those ones that are deficient. Now this is an important point, calcium is over by 39.5%, do you think that we would reformulate the diet to get calcium to balance? Not likely. However, if we had limestone or dical in the diet, both sources of calcium, and calcium was being overfed, then we could do something about the problem. However, at this point, the calcium is coming from the forages and grains and we are not going to purchase additional feeds or new feeds just to balance for this mineral. Another macromineral that will always be high in dairy diets is potassium. Most of the trace minerals or microminerals are usually deficient with the exception of iron. Vitamins are also usually deficient. So we would need to add a mineral and vitamin premix to their ration to balance for these deficiencies.

Now lets take a look at the output beginning on row 44. There is a place for your name in cell B-44 and a place for the ration name in cell B-45. Rows 48-62, are the ration ingredients on an as-fed and dry-matter basis. Beginning on row 66, is the nutrient and ration components, column C is what is in the ration and column D is what is required, with the units of each of these in column E. Now these are our nutrients. We can compare these to what is in column F which is the concentration with the units in column G. Column H is a check of the ration versus what is required and so if the ration meets requirements, then a ‘Yes’ is displayed, if it does not, a ‘No’ is displayed. Beginning on row 101, is an area for comments for selected nutrients.

Scroll back to the top of the spreadsheet and we will review saving and retrieving rations. Click in cell B-45 and name this ration Example 1. Now scroll to the top left of the sheet and click on the ‘Save’ button. The name of the ration should now appear in the screen just below the ‘Save Ration’ button. To check and make sure our ration saved, let’s delete the inputs in cells F-3 through F-15 or delete the inputs from body weight to chlorine. Also, delete the inputs from cell J-3 to J-14 or the inputs for potassium through vitamin E. Now click on Example 1 which should be in the box just below the ‘Save Ration’ button and then click the button ‘Get Ration’ and the inputs should have been retrieved. The feeds and amounts of each feed are also retrieved. Now you can also click on Example 1 and click the ‘Delete Ration’ button and the ration will be deleted.

This has been a brief overview of the feed library and dairy ration formulation feed sheet.

Section 5

The last sheet that we will cover is feed value. Dairy Spreadsheet – Three

The two feeds most commonly fed in the U.S. are corn and soybean meal. Now click on the feed value sheet tab. At the top of the sheet, corn and 48% soybean meal are listed along with their dry matter percentages, their TDN concentrations and their crude protein concentrations. Cells F-6 and F-7 are shaded, this is where one would enter the market price for corn and soybean meal. In this example, we have the market price for corn at $110.00 per ton and that of soybean meal at $197.00 per ton. This spreadsheet calculates the value of crude protein and the value of TDN. In cell C-9, the value, in dollars, for each percent crude protein would be $2.17 and for each percent TDN the value is $1.17. In cells A-15 through A-24, we can enter feed numbers from the feed library to value feeds based upon the feed value of corn and soybean meal. In this example, we have chosen to evaluate two different feeds, feed 149 which is whole cottonseed and feed 143 which is corn distiller's grain. Entering feed number 145 brings in the feed name, the dry matter percent, the TDN percent and the crude protein percent and the spreadsheet calculates the as-fed value of this feed per ton based upon the value of corn and soybean meal. In column G, are cells where we can enter in the market price and cottonseed is trading at $188.00 per ton and corn distiller's would cost $140.00 per ton. Column H compares the market price to the value and denotes whether this feedstuff is a good buy or not. Whole cottonseed has a value of $154.00 per ton and if it costs $188.00 per ton, then it would not be a good buy. Corn distiller's grain has a value of $163.00 per ton, so if we could buy it at $140, then yes it is a good purchase.

This completes our review of the spreadsheet that we will use throughout the quarter. You will first use this spreadsheet after finishing the dairy cattle nutrition section.