Ration Calculator Spreadsheet

 

List of Worksheets & Description

 

1. Answer Report 1

 

This worksheet is a least cost ration using limited feed ingredients from the worksheet Feed Mixture Calculator which was calculated in Excel for a 550 lb. medium frame heifer gaining 1.25 lbs. per day.

 

2.Feed Mixture Calculator

 

This is a tutorial sheet for using a least cost ration balancer. Instructions for using this option are included in the attached Word Perfect document Least Cost Instructions 2000.wpd (Excel) or Least Cost Instructions Qpro 2000.wpd (Quattro Pro) or are pasted below. There is also a matrix on the spreadsheet to estimate total digestible nutrient (TDN) percentages from feed tag ingredients. Another matrix shows crude protein and TDN levels for different combinations of common hays and grains.

 

3. Energy Calculator

 

Calculates Mcal of metabolizable energy (ME), net energy for maintenance (NEm), or net energy for gain (NEg) for different TDN levels of feeds. You may change or enter values for any items shown in red. There is also a matrix present to convert Mcal of ME required for cow maintenance to lbs. of TDN.

 

4. Animal Req. - Feedlot Steer

 

Contains NRC tabular nutrient requirements for medium and large frame steer calves.

 

5. Show Steer Timeline

 

Calculates the suggested purchase date and the last practical dates for step up rations for show steers. This worksheet also has application for rancher fed feedlot steers.

 

6. Show Steer Rations

 

Includes a simplified least cost ration balancer for show steers and some examples of grower and step up rations using better quality alfalfa hay.

 

7. Animal Req. - Feedlot Heifer

 

Contains NRC tabular nutrient requirements for medium and large frame heifer calves.

 

8. Animal Req. - Cowherd

Contains tables for range cows, bulls, and replacement heifers for energy, crude protein, and trace minerals.

 

9. Range Cow Forage Intake

 

Contains estimates for range cow forage intake at various forage energy or protein contents.

 

10. TM Forage Sampling Sheet

 

A data sheet to enter results of forage trace mineral analyses. The data shown are from an actual Arizona Ranch near the Mogollon Rim.

 

11. Trace Mineral Calculator

 

A spreadsheet developed by Dr. Howard Frederick to calculate levels of trace minerals required in a salt mix without any feed by-products. You enter your forage trace mineral and mineral values, cow weight, forage intake, and trace mineral salt intake for your ranch in the items shown in blue and a suggested trace mineral mix is calculated for you.

 

12. Supplement Calculator

 

This worksheet calculates supplement requirements for forage for either TDN (cells A1:AG18) or crude protein (cells AC67:AF72). You enter cow breed, weight, body condition score, % of body weight forage intake (from Range Cow Forage Intake worksheet) , lactating or nonlactating status, trimester of pregnancy, estimated milk production (from provided tables), breed adjustment factor (from table), and forage TDN percentages by month for the cells shown in red. You will also enter whether or not the cow receives winter supplement in Jan. & Feb. in cell K2. In cells AC7 to AE7 and AC69 to AE69 enter the desired energy or protein supplement you wish to evaluate. In the Protein Calculator (cells AC67:AF72), the crude protein content of the forage needs to be entered in cell AF69, cow protein requirements in cell AC72, forage intake in cell AD72, and cow weight in cell AE72.

 

13. Body Condition Scoring

 

The Body Condition Scoring worksheet includes tables to determine body condition score and weight by frame size and milk production by frame size and calf weaning weight.

 

14. Phos. Req. by Month

 

This worksheet calculates dietary requirements for phosphorus for range cows at different months (or stage of gestation or lactation) of the year with different forage qualities. The main items which need data entry are in cell A5 for average calving date and in cells C16 to C18 for cow weight, peak milk production (from Body Condition Scoring or Supplement Calculator worksheets), and calf birth weight. If calving is at some other month other than March, then the table which calculates the percentage phosphorus needed in forage (F23:F34) will need to have the default TDN (B23:B34) and forage intake values (C23:C34) rearranged according to season. The default values shown below are for the January to December calendar year. If the calving date in cell A5 is any month other than March or if you donít think the default TDN or forage intake values fit the forage curve for your ranch, then adjust accordingly.

 

Month††† Percentage TDNEstd. Forage Intake, % Body Weight

January††††††††††††††††† 50††††††††††††††††††††††††††††††††††††††††††† .017

February††††††††††††††† 52††††††††††††††††††††††††††††††††††††††††††† .018

March††††††††††††††††††† 59††††††††††††††††††††††††††††††††††††††††††† .018

††††††††††††††††††††††††††††††† April†††††††††††††††††††††† 60††††††††††††††††††††††††††††††††††††††††††† .022

††††††††††††††††††††††††††††††† May††††††††††††††††††††††† 62††††††††††††††††††††††††††††††††††††††††††† .024

††††††††††††††††††††††††††††††† June††††††††††††††††††††††† 58††††††††††††††††††††††††††††††††††††††††††† .023

††††††††††††††††††††††††††††††† July†††††††††††††††††††††††† 60††††††††††††††††††††††††††††††††††††††††††† .026

††††††††††††††††††††††††††††††† August††††††††††††††††† 62††††††††††††††††††††††††††††††††††††††††††† .028

††††††††††††††††††††††††††††††† September†††††††††††† 60††††††††††††††††††††††††††††††††††††††††††† .026

††††††††††††††††††††††††††††††† October††††††††††††††††† 58††††††††††††††††††††††††††††††††††††††††††† .024

††††††††††††††††††††††††††††††† November††††††††††††† 55††††††††††††††††††††††††††††††††††††††††††† .021

††††††††††††††††††††††††††††††† December††††††††††††† 52††††††††††††††††††††††††††††††††††††††††††† .019

 

15. Ca Req. by Month

 

This worksheet calculates dietary requirements for calcium for range cows at different months (or stage of gestation or lactation) of the year with different forage qualities. The main items which need data entry are in cell A5 for average calving date and in cells C16 to C18 for cow weight, peak milk production (from Body Condition Scoring or Supplement Calculator worksheets), and calf birth weight. If calving is at some other month other than March, then the table which calculates the percentage calcium needed in forage (F23:F34) will need to have the default TDN (B23:B34) and forage intake values (C23:C34) rearranged according to season. The default values shown above are for the January to December calendar year. If the calving date in cell A5 is any month other than March or if you donít think the default TDN or forage intake values fit the forage curve for your ranch, then adjust accordingly.

 

16. Feed Tables

 

Contains NRC feed composition tables.

 

17. Feed Matrix

 

This worksheet contains a least cost ration balancer for all the feedstuffs shown in Feed Tables worksheet. It also includes a few additional feed ingredients commonly used such as urea and Rumensinģ. Because of the enormous amount of feeds included in this feed matrix, it is advisable to use the Feed Matrix Calculator worksheet for least cost ration balancing instead of the Feed Matrix worksheet. Otherwise, you will have to set constraints for many feed ingredients to 0 to avoid minuscule inclusions of numerous feedstuffs in the final ration. Instructions for using the least cost ration balancer are included in the attached Word Perfect document Least Cost Instructions 2000.wpd (Excel) or Least Cost Instructions Qpro 2000.wpd (Quattro Pro) or are pasted below.

 

18. Not Enough Roughage Ans. Report

 

This is an answer report for the first attempt to calculate a least cost ration for a 600 lb. steer gaining 3.0 lbs. per day. This answer report was generated using the least cost solver option with the worksheet Feed Matrix Calculator. The ration shown in the answer report is too low in roughage and could predispose cattle to acidosis and founder. It also has negative numbers listed for some feed ingredients.

 

19. With More Roughage Ans. Report

 

This is an answer report for the second attempt to calculate a least cost ration for a 600 lb. steer gaining 3.0 lbs. per day. This answer report was generated using the least cost solver option with the worksheet Feed Matrix Calculator. An additional constraint was placed in the Least Cost Solver for fiber. Some negative numbers are still present for some feed ingredients. Nevertheless, the answer report furnished information on starting values for major feed ingredients in the worksheet Feed Matrix Calculator in order to provide a least cost solution.

 

20. Feed Matrix Calculator

 

This is a least cost ration analyzer for feedstuffs commonly found in southern Utah or in Arizona. Instructions on how to use this worksheet are contained in the attached Word Perfect document Least Cost Instructions 2000.wpd (Excel) or Least Cost Instructions Qpro 2000.wpd (Quattro Pro) or are pasted below.

 

21. QPro Solution Run1 (Quattro Pro only)

 

This Quattro Pro worksheet contains a least cost solution for a 600 lb. steer gaining 3.0 lbs. per day using the worksheet Feed Matrix Calculator. Lots of feed ingredients were used with minuscule amounts. From the information contained in this feed matrix, it was seen how to combine ingredients for a simplified ration that generated the solution for the final least cost ration.

 

22. Feed Matrix Calc Answer Report(Quattro Pro only)

 

This is a detailed answer report for the final least cost ration for a 600 lb. steer gaining 3.0 lbs. per day using the worksheet Feed Matrix Calculator.

 

23. Feed Matrix Calc Detail Report(Quattro Pro only)

 

This worksheet contains starting and ending values for each feed ingredient considered for the final least cost ration for a 600 lb. steer gaining 3.0 lbs. per day using the worksheet Feed Matrix Calculator.