Manual for Batch Mix Spreadsheet

Tips and instructions for using the Batch Mix spreadsheet, a simple tool for adjusting the amount fed as the dry matter of forages changes.
Manual for Batch Mix Spreadsheet - Articles



Monitoring forage dry matter is critically important to consistently feeding a balanced ration. Moisture content is the most variable aspect of forages. Other components remain relatively stable over time, but moisture can change as much as 5% from one week to the next.

This variability creates a problem because rations are formulated on a dry matter basis, but they are mixed and fed on an as fed or wet basis. Changes in the moisture content of a forage cause changes in the amount of that forage required to provide a specific amount of protein and energy (and all the other nutrients balanced in the ration). Consider this example:

Corn Silage
As Fed lb
Corn Silage
% DM
Corn Silage
DM lb fed
Amount Over/Under Fed
DM (lb)
Amount Over/Under Fed
CP (lb)
Amount Over/Under Fed
NE (Mcal)

At the time the ration was formulated, the corn silage was 28.5% dry matter and had 11% crude protein and 0.67 Mcal/lb of net energy. To balance the ration, 16.8 pounds of corn silage dry matter were required. This translates to 58.9 pounds of as fed corn silage. If the dry matter changes, but the amount fed is not adjusted, cows will be over or underfed corn silage. As a result, the amount of protein and energy in the ration will not match the cows' needs. Failure to adjust feeding amounts as forage dry matter changes can negatively affect cows' milk production and energy balance. On the other hand, overfeeding forages results in wasted feed, excess nutrients, and increased excretion of nutrients in manure.

We recommend testing forage dry matter two times per week (instructions for using a microwave to do this are provided in the spreadsheet). The Batch Mix spreadsheet can be used to quickly adjust your ration as forage dry matter changes.

Using the Batch Mix Spreadsheet

The Batch Mix spreadsheet  was designed to provide a simple method of adjusting the amount fed as the dry matter of forages changes. Type only in cells containing blue text; other cells contain formulas that may stop working if they are erased or changed. Also, be sure to type over or erase all examples to ensure accurate results.

This spreadsheet contains the following worksheets:

  1. Instructions
  2. Microwave
  3. Ration Info
  4. Forage DM
  5. Feed Sheet

The first two worksheets contain a shortened version of these instructions for using the spreadsheet and determining forage dry matter using a microwave.

Step 1. Enter Ration Information

The first step in using this spreadsheet is to enter a ration formulation on the Ration Info worksheet. Up to four rations may be entered; a total of 6 forages and a total of 18 other ingredients may be entered. For each ration, enter the following:

  1. Ingredient names or descriptions
  2. Pounds of dry matter per cow of each ingredient
  3. Dry matter content (as a percent) of each non-forage ingredient

The total dry matter in the ration will be calculated at the bottom of the column. Check this number to be sure it matches your ration formulation.

Figure 1.Ration info worksheet.

Step 2. Determine Forage Dry Matter

The next step is entering forage dry matters on the Forage DM worksheet. This is a separate sheet to allow you to enter initial and final weights from dry matter determination. The forage dry matter will then be calculated for you. Until the ration formulation changes, the Forage DM worksheet will be the primary place you will need to enter information.

Forage dry matter may be correct on the formulated ration the first time a new ration is entered. In this case, enter 100 for the initial weight and the dry matter percentage reported on the ration as the final weight.

Figure 2. Forage dry matter worksheet.

As you measure dry matter each week, enter the new initial and final weights into the Forage DM worksheet. Information about using a microwave to determine forage dry matter can be found on the Microwave worksheet.

Step 3. Enter Cow Numbers and Print

The last step is entering the number of cows in each feeding group on the Feed Sheet worksheet. Once cow numbers are entered, the pounds of each ingredient required in the mix are calculated for each ration. The sheet can be printed by choosing "Print" from the File menu.

Figure 3. Feed Sheet Worksheet. Example showing three rations; note the amounts for the dry cow ration are calculated for 10 and 100 cows.

The batch mix sheet can now be updated quickly and printed each time forage dry matter or group sizes change.

Possible Uses and Examples

  • Enter up to four different rations - Adjust batch sizes for changes in cow numbers. Every time the group size changes, the batch mix sheet must be printed.
  • Enter the same ration in all four columns - Enter four different group sizes to create a reference that shows incremental differences in the batch size.
  • To build in a cushion (for example 4% feed refusals), simply increase cow numbers.