Search the site:  
Chapin Livestock Supplements Dairy Nutrition  

Home Page
Roy Chapin's CV
Swine Nutrition
Dairy Nutrition
Human Issues
Agrobusiness Administration

Directions & Explanations on how to use Roy Chapin's (1) Feed Analysis Spreadsheet & (2) Interactive Milk Money Maker Budging Tool.

The purpose of this document is to serve as an aid in using and understanding the (1) feed analysis spreadsheet that I assembled in Microsoft Excel using data from the U.S. National Research Council's Nutrient Requirements of Dairy Cattle, 2001 edition and my (2) Interactive Milk Money Maker budgeting and feed selection software tool that uses rations formulated using NRC data. The feed analysis spreadsheet will help the feed formulator make informed feed ingredient choices without doing a lot of additional calculations.

WARNING: In using the spreadsheets, don't change any data in the feed nutrient analysis spreadsheet (black). For sure, don't change any figures shown in red or other colors for these are calculated from the data shown in black. Typing over the colored numbers will invalidate the spreadsheet. For the Interactive Milk Money Maker budgeting and feed selection spreadsheet, change only the numbers shown in BLUE as they are the independent variable you're testing. Changing any other colored number will inactivate the spread sheet. Learn how to reinstall linkages and keep an original.


The feed analysis spreadsheets contain important messages mixed in with the data. Please read these messages to improve your understanding of what is being presented.

The spreadsheet with the feed nutrient data has a tab marked "legend". Toggle this and print out these two pages so that you will understand the headings that contain many abbreviations. It will also help you understand why the data are there in the first place.

Before printing out the Milk Money Maker budgeting tool, go to the price sheet (toggle the tab at the bottom of the spreadsheet book) and enter your local costs of feed so that the economic data will be correct. Then print out the various pages of both spreadsheets so that they are easy to view. Study them along with these instructions and comments.

To use this material, it is important to remember that a ruminant has a large fermentation vat (rumen and reticulum) full of microbial activity preceding the small intestine. This distinguishes a ruminant from a non-ruminant (monograstric) and creates special considerations in formulating rations for ruminants. You can't get to the small intestine of a ruminant except through the rumen. The rumen has both positive and negative effects on feeds ingested. For cows to make milk, they must extract nutrients from the blood as it passes through the udder. The energy in the blood originates from volatile fatty acids (VFA) absorbed across the rumen wall. VFA's are produced by rumen microorganisms (RMO) digesting carbohydrates. Amino acids are absorbed from the small intestine. Forty to 45% of the amino acids should bypass the rumen intact from ingested feedstuffs. The rest of the amino acids originate from RMO's that grow in the rumen using ammonia, carbohydrates, vitamins and minerals. The RMO's are killed in the abomasum and digested.

The 2001 dairy edition contains a feed formulation software disk. I've used this software to formulate the rations included in various editions of my "Milk Money Maker" (MMM) budgeting tool (created in Microsoft Excel) that predicts income over feed cost. The MMM allows the user to put in the price of local feedstuffs and choose the ration that will earn the most income over feed cost. The MMM is a great decision making tool. So far I've created MMM editions for a collective farm in Novosibirsk oblast (Siberia, Russia) and Kosovo dairy producers. Others will be assembled. Each MMM requires a separate set of rations using local feeds. The number of rations that can be formulated for the MMM is virtually endless. There are 38 rations in the Milk Money Maker for Siberia; 250 for Kosovo (some duplicates).

The NRC dairy cattle nutrient requirement book is the "Bible" that gives basic data - and lots of it. The purpose of my spreadsheet is to use these data to supply useful information for choosing appropriate feedstuffs to balance forages used locally. I have included all the feedstuffs (121 different ones) listed in the 2001 edition plus many of the nutrient analyses so you will have them all in one place for easy reference. Plus the spreadsheet does some important calculations for you. These calculations use NRC data for crude protein (CP), rumen degradable protein and RUP digestibility to determine RDP and grams RDP & RUP digested/100 g intake. This is the main justification for the feed analysis spreadsheet.

The spreadsheet calculates the amount of non-fiber carbohydrates (NFC) contributed by a feedstuff. NFC is an important measurement that is calculated by subtracting the percentage of crude protein, neutral detergent fiber, ether extract and ash from 100. The feed analysis spreadsheet does this calculation for you. This lets the feed formulator see at a glance how much NFC is in each feedstuff without additional calculations. You'll find this very helpful.

Of great importance in formulating dairy rations for high producing cows is balancing the rumen degradable protein (RDP) and rumen undegradable protein (RUP) in the ration so that the protein requirements of rumen microorganisms (RMO) are met as well as the protein requirements of the host animal. A ruminant animal requires that specific amino acids reach the small intestine for absorption, just like mono-gastric animals. Some feed proteins bypass the rumen undegraded. Other proteins are degrade to ammonia in the rumen. The RMO's use ammonia, along with non-fiber carbohydrates plus vitamins and minerals to grow. The RMO's eventually are digested by the ruminant making the amino acids they contain available for absorption and use by the animal. Currently, there is considerable interest in how much of the amino acids lysine and methionine reach the small intestine. The amino acid make up of RMO's is 3 to 1, lysine to methionine. This is thought to be the ideal ratio. The spreadsheet predicts the amount of lysine and methionine that will escape rumen degradation (be converted to ammonia) and will be digested. This is new technology.

The RMO's have a finite ability to use ammonia. If there is not enough RDP (ammonia) in the rumen, the RMO don't grow at a maximum rate and thus the animal doesn't perform as well as if there were enough RDP. By balancing the ration to meet RDP needs, more microbial protein is produced and the ruminant can produce more milk, grow faster, etc. than if the RDP requirement is not met. Urea can be used as well as feedstuffs high in RDP, such as sunflower meal. This may be necessary when corn silage and mature grasses are fed as they supply low levels of RDP. When other forages are fed, such as green pasture, early cut grass hay and silage and alfalfa hay and silage, there will be more than enough RDP for the RMO's. Don't formulate rations using CP. Use RDP & RUP instead. Study the spreadsheet!

As milk production increases, more RUP is required as the animal needs more amino acids reaching the small intestine that are digested and absorbed into the blood stream. Since all protein sources contain both RDP and RUP, the RDP needs are usually met with greater intake of crude protein so the problem becomes one of supplying enough RUP, not RDP.

If there is more RDP that the RMO's can use, the ammonia is absorbed across the rumen wall and is transported to the liver where the ammonia is converted to urea. From here some of the urea is recycled via saliva to the rumen. Some is excreted in the urine and some ends up in the milk and can be measured as Milk Urea Nitrogen (MUN). The amount of ammonia escaping the rumen into the blood stream can be measured as Blood Urea Nitrogen (BUN). Measurements of BUN and MUN give an indication of ration adequacy of RDP and NFC to support RMO growth without wasting ammonia. Too much BUN can lead to infertility. There is no benefit in over-feeding RDP, for if it isn't used by the RMO's, it is lost & may be a liability to the animal. Adding urea is worthless if RDP in the ration is already too high.

The terms, rumen degradable protein and rumen undegradable protein refer to the percentage of the protein that is either RDP or RUP. To find the amount of RDP and RUP supplied by a feedstuff, you must multiply the percent crude protein in the feedstuff times the percent of that protein that is RDP and RUP. If the protein is rumen bypass protein (meaning it reaches the small intestine in tact without being converted to ammonia), there is the further concern as to its digestibility. For example, shoe leather would be all RUP but since it is undigestible, it is of no value as a feed for a ruminant, even if it is high in RUP.

Here's where the spreadsheet I created is of real value to a feed formulator because it does the calculations for you. The NRC book tells percentage of crude protein, percent of the protein that is RUP and amount of the RUP that will be digested but NRC doesn't tell values calculated for you on my spreadsheet or digested RUP/100 grams consumed (also done for you on the spreadsheet). This is important information that makes the tables user-friendly.

Wheat straw is high in RUP (78%) but you wouldn't choose it to supply RUP as it is so low in crude protein (4.8%) and the RUP is poorly digested (65%) so that the grams of RUP reaching the small intestine/100 grams consumed is small (2.4 grams). Consider that the RUP of corn grain (9.4% CP) is fairly high (47%) with a digestibility of 90%, so 100 grams of corn supplies 4 grams of RUP to the host animal. Wheat bran, although higher in crude protein than corn (17% CP vs 9%) is low in RUP with lower digestibility (75%) so that corn (9% CP) supplies more digestible RUP than wheat bran (17'% CP) (4.0 grams vs. 2.1 grams).

Even more illuminating, consider the situation with sunflower meal (28% CP). It is low in RUP (16%) and even with a digestibility of 90% supplies only 4.1 grams of RUP to the animal, about the same as corn (9.4% CP). Sunflower (SFM) can be used with forages of low RDP, such as corn silage and mature grasses, but it should not be used with forages high in RDP such as alfalfa and growing grass as most of the 28% crude protein in SFM is not usable since it is 84% RDP. Besides, SFM is low in net energy for lactation (NEl) at 1.30 mcal/kg vs.1.9 mcal/kg for ground corn. Except under special circumstances you should forget feeding SFM as it is (1) low in energy (full of sunflower hulls, which are wood) and (2) its protein is 84% RDP and thus not a good feed to support high milk production. Feed SBM.

By studying the feed analysis charts you will discover that most forages supply about the same amount of RUP (except for intensively managed grass and/or alfalfa pastures that supply lots of RUP and RDP) but forages vary substantially in the amount of RDP they supply. Study the spreadsheet to see this relationship. As milk production goes up, feed intake as a percentage of body weight goes up and feed passes through the animal faster. A study of the feed analysis table shows that this results in less energy (net energy for lactation - NEl) being extracted from the feedstuff. Since, as intake increases with greater milk production and there is less time spent in the rumen, less of the protein degrades to ammonia so that RUP increases with greater feed intakes. This is good as a high producing cow needs more RUP and RDP usually will be adequate when high protein diets needed for milk are fed.

To understand the above, it is helpful to know that protein is divided into three categories. Category A is always soluble (thus it is RDP). Category C is never soluble (thus it is RUP). Category B can go to either RDP or RUP depending on conditions in the rumen, such as the amount of feed intake. Soluble protein is part of RDP and should be about 50% of RDP.

Look at the RDP and RUP of steam rolled corn versus ground corn. Steam rolling whole corn increases RUP of corn (compared to ground corn) from 47% to 75%, resulting in 6.3 grams of digested RUP instead of 4.0 grams/100 grams of intake of ground corn. Processing counts!

Heat increases the percentage of crude protein that is RUP. Consider soybean meal. Solvent extracted 44% SBM delivers 16 grams of digested RUP/100 grams intake. Solvent extracted 48% SBM delivers 21 grams. If the soybean oil is removed mechanically by extrusion, the heat produced by friction increases RUP, so mechanically extracted SBM supplies 30 grams of RUP/100 grams SBM consumed. If the SBM is heated further to near burning temperatures, RUP goes up to 37 grams/100 grams consumed. There is a big business for soybean meal processors that heat SBM to high temperatures to increase the RUP for high producing milk cows. Not more than 2.1 kg/day of whole soybeans should be due to high oil.

A review of the rations (MMM) using (1) whole soybeans, (2) 44% and (3) 48% protein SBM, (4) extruded soybeans and (5) heat treated soybeans shows the amount needed of each to supply enough RUP to meet the requirements for milk production. By putting a price on each feedstuff times the amount of the feedstuff used in the ration, the cost of feed can be determined. By inserting the price of milk times the liters of milk produced, milk revenue can be calculated and income over feed cost can be determined. This is of great help in making informed decisions on what ration to feed and to help you in predicting your profit.

The column down the right hand side of the feed analysis sheets shows the grams of rumen undegradable protein (RUP) digested per 100 grams intake of each feedstuff. When you're looking for feedstuffs that will support high milk production, look at this column. Notice that corn gluten meal and fish and animal products (except whey) are high in RUP. Heat treating increases RUP as described earlier. You can learn a lot of nutrition by studying these tables.

Use the feed analysis spreadsheet to compare the nutrient contribution of various feedstuffs. Use this information to choose feedstuffs to feed your cows for high production. Use a computer with the NRC 2001 disk or other software to formulate the rations. Doing it by hand is too complicated when you want to consider many variables. I've formulated thousands of rations by hand, but using a computer allows you to do it faster and better.

The Milk Money Maker (MMM) looks at various feedstuffs including urea plus the Chapin Dairy Premix, limestone, salt and mono calcium phosphate. If two kg of wheat bran is fed, there is usually adequate phosphorus in the ration for moderate milk production. High producers (over 25 to 30 liters per day) may need additional phosphorus in their ration. If wheat bran is not fed, a phosphorus source should be fed to prevent a phosphorus deficiency.

Most rations (not alfalfa) require added calcium, which can be supplied by limestone (chalk).

The MMM spreadsheet predicts dry matter intake, the level of milk production that can be supported by the energy in the ration and the metabolizable protein produced from ration protein and by rumen microorganisms. These amounts of milk are usually different. The RDP, RUP. calcium and phosphorus balances are shown. Pay particular attention to the RDP and RUP balances. Other measurements are explained in the legend. Read it!

There are two places to insert milk prices. The milk price of the first ration sheet (blue) is linked to all other sheets. An alternative milk price can be entered on each sheet. Milk prices are used to calculate income over feed costs (IOFC). This allows you to play the "what if" game to see what changes in milk price will do to IOFC. Print out the price sheet so you have it to record price changes and then use it to enter the new prices in the computer.

Note the big differences in income over feed costs predicted for the various rations. Choose concentrate rations that balance the forage fed. Notice how forage quality affects the ration and IOFC. Milk production can be increased significantly by improving forage quality.

It is common to hear that "you can't buy that ingredient here." That may be right, but in the case of protein, you must find a source of supply in order to maximize milk production and profit. A good way to use the MMM is to increase prices to see how much you can afford to pay for a particular ingredient, such as one of the soybean meals, and then determine if it is worth it to find it. Don't be passive! Cows must have protein to give high levels of milk!

The NRC computer program calculates rumen microbial protein synthesis. You can't do this by hand. Useful when comparing feeds such as sunflower meal and sunflower seeds.

Use Interactive MMM budgeting tool to choose feedstuffs. Then refine the formulation.

I welcome your comments.

Roy Chapin, Ph.D., Animal Nutritionist

11145 Chapin Lane, Amity, Oregon 97101
Phone: 503-835-7317
Fax: 503-835-3333
E-mail: <>

© Roy Chapin, 2018
  Home  Search the site  CV  Works Top