Using a TMR Mixing Sheet to Control Rations and Determine Feed Costs

Instructions for using the TMR Mix Sheet spreadsheet to accurately mix total mixed rations.
Using a TMR Mixing Sheet to Control Rations and Determine Feed Costs - Articles



Accurately mixing total mixed rations (TMR) is essential if animal groups are to receive what the nutritionist has formulated. The best ration on paper will not be realized if the TMR is not being mixed properly. This concept applies to all animal groups receiving a TMR, including dry cows and heifers.

The Penn State TMR Mix Sheet spreadsheet allows the user to enter nine rations for various animal groups. Mix sheets print according to cow number, batch size or percent of mix. Feed costs track for the whole herd and incorporate into the Penn State spreadsheet on monitoring dry matter intake, milk production and milk margin (DAS 00-2).

Main Menu

There are two main areas to enter data: Enter Feeds Used and Enter TMR Formulations, (Figure 1). Clicking on a button will take the user to the desired spreadsheet. In addition, the user can maneuver to the different worksheets by clicking on the tabs at the bottom of the workbook.

The user records information pertaining to the feed ingredient, the feed's dry matter percent and the price per ton in Enter Feeds Used. Record the dry matter pounds for each ingredient fed in each animal group mixed in Enter TMR Formulations.

The Print Mix Sheets buttons allow the user to print sheets according to the number of animals in each group, by the desired batch sizes, or by the percent of the mix as-fed. The Print Mixing Record Sheet button allows the user to enter the pounds of feed actually mixed. Whole herd ration costs tabulate in the print mix spreadsheets.

Figure 1. Main menu.

Enter Feeds Used

When opening this worksheet, a pop-up menu comes up (Figure 2). Data is entered one of two ways.

Figure 2. Example entry for feeds used.

The first method uses the pop-up menu. There are several inputs for data:

  • Feed ingredient name
  • Dry matter percent (example: 32.3 NOT. 323 )
  • Cost, price/ton as-fed (example: 45 )

The only calculated value on the sheet is cost per pound of as-fed ingredient. The tab key can be used to move from one data entry box to another; however, after the last entry (cost per ton) make sure to hit the enter key. This will save the entered data and will bring up a new entry box. Because the worksheet is protected, not all of the buttons in the box will be used. Most users will need only "Find Prev," "Find Next," and "Close."

The second manner in which data can be entered is by closing the pop-up box and entering data only in the yellow shaded areas. Either method of entering data in the spreadsheet is acceptable.

Enter TMR Formulations

Figure 3 illustrates what appears on the TMR formulation sheet. The two data entries on this sheet are:

  • Group name
  • Pounds of dry matter

The other information on this sheet carries over from the previous worksheet or is a calculated value. Follow a nutritionist's ration formulation program to correctly enter the pounds of dry matter fed.

Figure 3. Example entry for TMR formulation.

The buttons to the left of the worksheet help the user to maneuver around the spreadsheet (Figure 3). The user can go to any of the groups by either clicking on the appropriate button or by using the scroll bar. The print button will print the desired group sheet as shown in Figure 3.

Under "Ingredients" there are thirteen places for entering feeds. When the user clicks on the arrow, all the feeds that have been entered in the Enter Feeds Used worksheet will be in the scroll box. Scroll to the desired ingredient, highlight it, and click. The ingredient name, dry matter percent, and cost per pound will automatically appear. This process must be followed to enter each ingredient used for each animal group.

Once all ingredients have been selected for the particular animal group, then the dry matter pounds can be entered. Make sure dry matter pounds and NOT as-fed pounds are entered.

Many TMR mixers have a suggested mixing order. For this spreadsheet, enter ingredients in the order they are mixed. This mixing order will carry over into all the printed mix sheets. Check the TMR mixer manual for a recommendation on mixing order.

Print Mix Sheets

At the main menu (Figure 1), mix sheets can be selected to print by cow number, batch size, or as percent of mix. Figure 4 shows an example of printing the mix sheet by cow number. The only entry on this sheet is for the number of animals to mix. There are five places to enter animal numbers for each group. The batch size sheet is identical to the cow number sheet except that the size of the batch is entered instead of the number of cows. The one disadvantage of the mix sheets by cow number or batch size is that each ration group prints on a separate piece of paper. Printing the mix sheet as a percent of the mix prints out all ration groups on one page.

The Print Mixing Record Sheet is for the feeder to record what is actually mixed. This can be useful in controlling inventory of feeds and checking accuracy of the ration mixed.

The buttons to the left of the sheet allow the user to print out the sheet specific for a particular animal group. Information that is entered on all nine groups (cow numbers and batch sizes) can be printed by clicking on "Print All Groups."

Feed costs tabulate for each group in the sheets for cow number and batch size. The total feed costs for all animal groups appear at the bottom of the spreadsheet. This number is reflective of the entire herd only if the feeder follows cow number or batch size for all animal groups in the same column. The total feed cost per day value can be used in the Penn State spreadsheet on monitoring dry matter intake, milk production, and milk margin (DAS 00-2). The mix sheet as a percent of the mix does not calculate feed cost since a specific mixing weight is not listed.

Figure 4. Print mix sheet by cow number.

Software Requirements

This publication supports an Excel spreadsheet program, the Penn State TMR Mix Sheet, that was created and tested on Excel 97 for Windows or Excel 98 for Macintosh.