Learn how to use the UNL Feed Cost Cow-Q-Lator Microsoft Excel® spreadsheet to estimate feed costs for crude protein, energy, and dry matter; compare feeding values of various feeds; and estimate the amount of feed and its cost for a specific group of livestock.
Brian A. Strauch, UNL Extension Educator, Red Willow County
Matt C. Stockton, UNL Extension Agricultural Economist, West Central Research and Extension Center
The Feed Cost Cow-Q-Lator is a Microsoft Excel® spreadsheet designed to assist those who manage livestock in three important tasks:
Feed is the most significant cost of livestock production, often representing as much as 60-70 percent of production costs. Making a small change in the amount or type of feed may have a greater impact on profitability than any other cost due its large impact on production costs. The purchase cost of a feedstuff is only part of the cost of feeding animals; producers must also take into account transportation, storage, labor, and waste related to each specific feed or feeding regime. These additional costs are accounted for in the Feed Cost Cow-Q-Lator.
The spreadsheet can be used to price individual feed types on a cost per pound of DM, cost per pound of TDN, or cost per pound of CP at purchase, at delivery, after storage, and after feeding. Animal production is related to nutrition and is the cost of the units of nutrition that produce the value (i.e. reproduction, animal weight, etc.).
The second task is to compare the feeding value of different feed sources. The calculator compares feeds based on three criteria (DM, CP, TDN) at several stages between purchasing and feeding, just as in the case of estimating true feeding costs. Upon purchase, feed must be transported to where it will be used. This cost, and any amount of loss, become part of its cost. Once the feed is transported to the appropriate location, it may be stored or it may spoil or suffer other loss, all of which can add to its cost.
The next phase in the process is to actually feed the animals. This last phase may require labor, machinery, or facilities (for example, feed troughs or bunks) and other miscellaneous costs, all of which are part of the cost of feeding the feed. The final cost is associated with animal use or waste. Not all feeds offered are used by the animal with the same efficacy.
The following example illustrates how the Feed Cost Cow-Q-Lator might be used to determine actual cost of feeding the feed. (Loss estimates for transport and storage are based on information in “Minimizing Hay Losses and Waste,” published by North Dakota State University.)
A rancher buys hay based on a scale measurement taken before the truck travels 150 miles to his hay storage site. The wind and the vibration of the trip, and the process of unloading creates some degree of loss, so what started out as 22 tons is now 21.67 tons, assuming a 1.5 percent loss (660 lb). The hay is stored and, depending on the length and method of storage, spoilage may range between 5 percent and 50 percent. In this example, we will assume the hay will be stored on the ground and uncovered for five to seven months, resulting in a loss of 15 percent or 6,402 lb, due to weather and aging. This reduces the feed available to 18.12 tons. Next, the hay is fed to animals using a bale feeder (5 percent loss). After transport, storage, and feeding, only 17.25 tons of the original 22 tons of hay is actually consumed by the animal, resulting in a total loss of 5.75 tons. This number may vary according to the operation, feed type, feed quality, season fed, and time purchased. This fact is what makes the Feed Cost Cow-Q-Lator so valuable. This example demonstrates how important it is to consider and compare feeds before their purchase.
This task — determining the total quantity and associated cost of feeds for a specific time for a given herd of cattle using the true cost of the feeds — helps provide accurate budgeting and cost control. This last function of the Feed Cost Cow-Q-Lator reduces the guesswork in buying the right amount of feed and helps provide information to estimate the true cost of feeding groups of animals over an extended time. This function, if used appropriately, can provide better estimates of production response to specific feed regimes such as supplementation and supplement purchases.
The Feed Cost Cow-Q-Lator has nine feed worksheets, each for a single feed. These worksheets are represented as tabs, F1, F2, etc., to F9. F1 is used in place of Feed 1 and F2 for Feed 2, and so on. The F10 worksheet contains the Cornstalk Cow-Q-Lator. This worksheet provides a method to quantify the cost of grazing corn stover and estimates the same CP, TDN, and DM costs for the same levels as the other nine worksheets.
All of the worksheets are designed for users to enter data in the cells with a white background. Calculated results are displayed in the cells with the magenta background. No calculation results appear until sufficient data is entered in the appropriate worksheet cells. New data is entered on a separate worksheet for each feed being considered. Some worksheets already have sample information. This can be easily replaced by typing over it. Similarly, once a feed entry is no longer needed, type over it to replace it.
The quality of information derived from these worksheets is directly proportional to the accuracy of the information entered. For best results, users should have their feed sources analyzed for specific nutritional content. Most analyses provide accurate estimates of DM as a percentage of the feed, CP (DM basis) as a percent, and TDN (DM basis) as a percent. The more accurate these factors are, the more accurate the cost per unit, and the better the comparison among feeds.
Other items to be input on the nine “F” tab worksheets include
This task is accomplished using the information calculated in Task 1. Once the needed “F” tabs have been completed, each worksheet will have the calculated purchased, delivered, fed, and consumed cost of the DM, CP, and TDN at all levels from purchase to feeding. Since these costs are calculated on a per pound basis, they are comparable among all feeds being considered. Remember that only nine feeds can be entered at any time.
To accomplish Task 2, the “Per Pound” tab is used. This tab allows for side-by-side comparison of up to four of the nine feeds. This page is limited to four to keep the comparison limited to a single computer screen. The calculations for a specific feed are easily displayed in this worksheet by simply designating the “F” tab number in the red cells at the top of the page with the number position of the worksheet. For example, entering the number “3” in red cell “E4” of the “Per Pound” worksheet results in the calculated information about the feed source from the worksheet with the tab “F3” to appear in column “E”. Once the worksheet numbers of the appropriate sheets are entered into the “E4”, “F4”, “G4”, and “H4” cells, comparison is easily done. Additional feeds can be compared by changing the number in any red cell to the number of the next feed to be analyzed.
The “Whole Herd” tab allows users to calculate the total quantity and costs of all feeds needed for a specific number of animals for a specific time. This last tool provides information that informs the buyer of volume and cost, which includes transportation, storage, feeding, and waste. This tool uses the “F” tab number, just as the “Per Pound” worksheet does to transfer feed information from the worksheets. Users enter the feed number of the corresponding “F” tab in the red highlighted cells of the “D” column; up to four feeds may be used. In addition to the feed number, the user specifies the average amount of feed to be consumed by a single animal in a single day. These inputs are specified in pounds per day on an as-fed basis. In addition to this information, the number of days and animals to be fed are indicated. The worksheet will in turn show how much feed is required, compensating for waste, hauling, storage, and feeding costs. These costs are listed by level just like in all of the other worksheets. A reminder about the “Whole Herd” tab: The amount of feed needed to purchase (at left) does not include waste; one must look in the table to the right under “Units Purchased” to determine the total amount of feed, including waste, to be purchased.
The Feed Cost Cow-Q-Lator is a useful tool that can be applied in many ways. Two unique uses include establishing a value for hay produced or in determining the economic hauling distance for alternative feed sources.
For example, a producer wants to know how much to charge for hay he produced. He believes that this hay is of a higher quality than the “standard” hay on the market. To verify this he needs to calculate the price per unit of CP, DM, and TDN for his hay and the standard hay on the market. To do this, the nutrient analysis and cost factors for the commercially available hay and the produced hay are entered into two feed tabs, in this case “F1” and “F2.”
Once all of the necessary information is entered into both worksheets, the costs and value of the feeds can be compared. In this case, we assume that the hay producer is interested in the value of CP. The user can then enter different prices in the “Price per Unit” cell for his produced hay until the price per pound of CP is equal to that of the commercially available hay. When the cost per pound of CP for the commercially produced hay and his own hay are equal, he will know the value of his hay relative to the commercial hay. This provides needed information to the hay producer about the value of his hay relative to the market “standard” and may provide leverage in negotiating price or in determining management choices relative to future hay production.
A similar process can be used to determine how far a user could transport a certain feed before a lower quality, more readily available feed might be used. For example, a producer wants to feed dried distillers grains (DDG) instead of alfalfa to meet his animals’ CP requirements. DDG is available at various distances from his ranch. The producer would like to know how far he can transport the DDG given a certain price, or how much he can afford to pay for the DDG given he is a certain distance from the source. The user would enter all the pertinent information in the appropriate “F” tabs and then adjust the mileage of transport or the price of the DDG until the DDG and alfalfa become equal on a cost per pound of CP basis. Once they are equal, the Feed Cost Cow-Q-Lator will indicate which commodity would be the best choice. The possible uses for this calculator are numerous and we hope users will find one that benefits them.
Producers are continually looking for any advantage to achieve success. This decision support tool is one way to help minimize production costs, make informed feed purchases, and establish production cost and values. The Feed Cost Cow-Q-Lator can help anyone who buys or sells feed make better decisions. Its use is just one strategy that may increase the user’s likelihood of having a profitable and sustainable business. For a free download of the Feed Cost Cow-Q-Lator and other electronic decision support tools from the University of Nebraska–Lincoln Extension, visit www.agmanagerstools.com.
The software, Feed Cost Cow-Q-Lator, is supplied “as is” and all use is at your own risk. University of Nebraska–Lincoln disclaims all warranties of any kind, either express or implied, as to the software, including, but not limited to, implied warranties of fitness for a particular purpose, merchantability or non-infringement of proprietary rights. Neither this agreement nor any documentation furnished under it is intended to express or imply any warranty that the operation of the software will be uninterrupted, timely, or error-free.
Under no circumstances shall University of Nebraska–Lincoln be liable to any user for direct, indirect, incidental, consequential, special, or exemplary damages, arising from or relating to this agreement, the software, or user’s use or misuse of the software or any other services provided by University of Nebraska–Lincoln. Such limitation of liability shall apply whether the damages arise from the use or misuse of the software or any other services supplied by University of Nebraska–Lincoln (including such damages incurred by third parties), or errors of the software.
Gaebe, Randy, Karl Hoppe, and Greg Lardy. “Minimizing Hay Losses and Waste” AS-1190, North Dakota State University, 2000.
Reference to commercial products or trade names is made with the understanding that no discrimination is intended of those not mentioned and no endorsement by University of Nebraska–Lincoln Extension is implied for those mentioned.