Documentation for Grid Calculator Programs

(IBC Grid Calc Full.xls)

(IBC Grid Calc Simple.xls)

Overview

The purpose of these two Grid Calculator programs is to allow the user to utilize historical carcass data for the estimation of cattle market performance in various grid markets. IBC Grid Calc Simple is set up to use basic carcass data (hot carcass weight, quality grade and yield grade) plus hide color to estimate market returns, while IBC Grid Calc Full uses complete carcass data (hot carcass weight, rib eye area, fat cover, % KPH, and quality grade) to do the same process. Summarization statistics are more complete with the full carcass data model.

Grid comparisons are benchmarked against what the “In the Beef Price” is. For logical grid comparisons and best estimates it is imperative that up-to-date base prices, premiums and discounts be utilized in the model.

The program contains eight worksheets, all of which either require important data or display results. All input data items are highlighted with the color blue. To protect the integrity of the program all formulas and calculations are hidden and protected using normal Excel procedures.

Grid Prices

The first worksheet of the program is critical. You must input the “In the Beef Price for Comparison” for the two grids you wish to compare. Other price elements critical to the process are premiums and discounts for quality and yield grade and discounted carcass weight breaks. These are obtained from the various packers and special market alliances.

Currently quality grade premiums are for Prime, CAB (Certified Angus Beef) and Non-Black cattle that fall into average and high choice quality.

Yield grade premiums vary a great deal in grid markets. The program allows for different premiums in Choice versus Select quality grades, plus allows 2A and 2B to be different.

For the price discounts to work correctly, the values must be entered as negative values and the quality ones are all discounted off of Choice. We realize some grids quote them off of Select, so be sure to translate that correctly.

Carcass weight discounts vary from grid to grid. Some grids will have one light weight discount category while some will have two categories. The same holds true for heavy weights. These are critical for appropriate analysis and grid comparisons.

Macros

There are five built in macros; all were developed using the Office 97 version of Excel. Because the program is set for manual recalculation there is a Recalculate Analysis macro. This was done because the complexity of this model can calculate very slowly on some early pentium based computers.

Three macros are provided to get various printouts. Field testing has shown them to be effective, but some computer and printer combinations will likely not work well.

The Enter Carcass Data macro is exactly as the name implies. Using this macro is imperative to get the data inputted and coded correctly. Correctly coding quality grade, hide color and yield grade (in simple model only) is important to doing the analysis precisely. For instance, one cannot use their own quality grade naming system. The formulation for analysis requires exact naming of the quality grades.

Summary Information

There are two major worksheets that prepare and present important summary data.

First, the Grid Summaries worksheet page is the important one. It gives an overview of the quality and yield grade distributions at the top of the page. Following that there is a summary of how Grid 1 compared to Grid 2. Total Lot Value is the amount the cattle group would bring in that particular gird with the base price utilized. The IBC Grid Calc program then further defines that value on a per head basis, how it compares to an “in the Beef” value and the premium per head over “in the Beef” pricing.

The second part of the Grid Summaries is where the premiums and discounts come from and how much is due to quality grade versus yield grade and whether the cattle suffer from weight discounts and to what degree.

Carcass Trait Distribution worksheet is a very important summarization of the data inputted. A cross-sectional type table gives the percentage of cattle falling into the various quality grade-yield grade combinations. This analysis can many times help in identifying genetic or management opportunities. The second part of this worksheet is a distribution analysis of the raw carcass traits; hot carcass weight, ribeye area and fat cover.

Other worksheets are summarizations and intermediate calculation stages. Certainly for the curious at heart, other pertinent information can be gained from them.