Many years ago I developed a spread sheet that was versatile enough to demonstrate the effects of the cattle cycle on productivity in the national beef cattle breeding herd.
That spreadsheet accompanies this explanation of how to use it. The spread sheet has been included in each issue of the Cattle Cycle Booklet which is now in its third updated edition.
This spread sheet and explanation can be applied to the national herd or an individual herd. Readers will quickly note that in the spread sheet the numbers of cattle are always reported in whole numbers. That is to say, I have rounded the numbers following normal rounding rules. But the decimal is still there because it is needed when working with larger herd sizes such as the national herd. To illustrate 0.5 of a calf or cow is impossible. But 0.5 of a calf in a 100 cow herd becomes 5 calves in a 1000 head herd and 5000 in a million cow population. So, while I hide the decimal for practical purposes it is still there in all calculations and is necessary for mathematical integrity.
Following is the detailed explanation and throughout that explanation I will make reference to the cells or lines or columns in the spread sheet.
Spread Sheet Explanation and Instructions
The dynamics that are at play in any herd and that influence or govern the rate of herd expansion or contraction are:
The Culling Rate (including natural death loss)
The Reproductive efficiency of the national herd, and
The actual or desired Expansion Rate.
In the spread sheet (link is above) I present for explanation purposes a completely stable herd throughout one 10 year cycle. This is done just to explain the calculations. Year 11 is added only to complete some of the calculations for Year 10. Later in this paper I will offer some variations to show cyclical effects. Let us now look at Year 1.
Culling Rate (Line 6) – The Culling Rate chosen for this explanation is 11% or 0.11 for calculation purposes.
Reproduction Rate (Lines 7 and 8) – The components of the reproduction rate in this spread sheet are "Conception Rate" which I set at 95% or 0.95 and "Weaning Rate" which I also set at 95% or 0.95. I left out calving rate because in this spreadsheet it is included in the weaning rate. Thus in this exercise (0.95 x 0.95 = 0.912) meaning that each female exposed will wean 0.912 calves. 1000 such exposed females would wean 912 calves. Whether the reader judges this rate of reproduction unrealistically high or not is irrelevant to the explanation. One can change any of these inputs later.
Expansion Rate (Line 9) - In this first illustration we are holding the herd constant so the expansion rate is 1.0. If the expansion rate desired was to be 3% the factor would be 1.03
Natural Death Loss (Line 10) - This figure alludes only to the cow herd and is set at 1.5% or 0.015 per year.
These are variables and, because they are variables, they have been shaded green. Now we will move to the next section, Production at Weaning.
Production at Weaning
Cows Exposed and Heifers Exposed (Lines 14 and 15) – We begin with 90 cows and 15 heifers exposed for a total of 105 breeding females. These cells are also shaded green as they also are variables.
No. Safe in Calf (Line 18) - Here we encounter the first calculation (=B6 x B15) or 99.75. Here we also encounter our first fraction of an animal. If I were dealing with small herds I would truncate or round to the nearest whole number but, as explained above, I am also concerned with the National herd so 99.75 becomes 9,975 head in a herd of 10,000 or 997,500 in a herd of 1 million.
No. of Cows Culled and Cow Death Loss (Lines 20 and 21) - the formulas are straightforward.
Residual Herd (Line 22) - We are left in this case with 91.88 cows and when we move on to year 2 note that the number of cows exposed is the residual herd from year 1 (i.e. C13 = B22 x1). In this stable scenario the residual cow numbers stabilize at 91 head.
Number of Weaned Calves (Line 23) – This is the product of the number of females safe in calf by the weaning rate, which includes the calving rate.
Saleable Weaned Calves (Line 25) - The explanation needed here is that I am using a sex ratio of 50:50 male and female calves. Herd owners will realize that it rarely, if ever works out that way. But they will also realize that in large populations that is what happens. One can flip a coin 5 times and get 5 heads just by chance. Many human families have only sons or only daughters. But the odds on the next calf are still 50:50 in natural matings so, in very large populations the ratio will never vary significantly from 50:50.
Steers (Line 26) and Heifers (Line 27) - I noted above that the sex ratio at birth in large populations is very close to 50:50. In Year 1 that means there were 45.94 steer and 45.94 heifer calves ( I have to use decimals here to get it right). But we know that in this case 13.13 heifers were needed to replace the cows that were culled or died of natural causes. That’s a replacement rate of 28.58%. The question now is how many bulls were needed to replace culled bulls? Many, if not most commercial herds retain no intact males but breeders herds retain a high percentage of males. On average the number of bulls slaughtered annually approximates 1/11th the number of cows culled or approximately 9% as many bulls as cows. So, on that basis 9% of 28.58% is 2.57% so at a minimum one would need to retain that percentage of bulls. In this spread sheet I have rounded that number up to 3% to make allowance for bulls culled in the selection process, infertile bulls etc.
Heifer Retention Rate (Line 30) - In this static scenario 28.81 % of the heifers were retained for herd replacements and saleable heifers were 71.26% as numerous as the steers available for production (Line 31).
Once this spread sheet is understood one can then test many scenarios. The first thing I suggest is that the interested reader save this spread sheet as a template. One can then run as many scenarios as one can imagine. In the Template Scenario the herd is producing 46 Saleable Steers and 34 Saleable Heifers each year. From year 3 onward 14 heifers are needed each year as replacements . The Heifer Retention Rate from Year 2 through Year 10 was 28.81% and the Saleable Heifers were 71.26 % of Saleable Steers.
For a first example, let us see what the result would be if one reduced both the Conception Rate and the Weaning Rate to 90% in Years 1 through 10 and left everything else the same. Under this scenario we have only 41 Saleable Steers and 29 Saleable Heifers. Because the culling rate wasn’t changed we still needed the same 14 replacement heifers but we had fewer heifers born so the Heifer Retention Rate rose to 32.10% in and the Saleable Heifer numbers dropped to 67.96% of the reduced Saleable Steer numbers in Years 2 through 10.
An obvious scenario that is very timely in 2017 might be to work out the practical maximum rate of expansion. This is timely because the national herd has declined sharply and international markets are opening. So, change the conception and weaning rates back to .95 in each year and let us set a scenario for maximum expansion. We will achieve this by reducing the culling rate to 8% each year by maintaining a high reproductive rate and by attempting an annual increase in the cow herd of 5% each year.
When these performance and management parameters are entered the number of females exposed rises steadily from 105 to 163 head in year 10 and steer marketings rise in parallel from 46 to 68 head over the 10 year period. Because the ONLY way to expand the herd is through retained heifers, the number of heifers available for marketing rises more slowly and in fact decline initially. In this scenario the first increase is not seen in total fed saleable steers and heifers until the third year of deliberate expansion, and those increased numbers will not reach the slaughter market until year 4 at the earliest.
It would be nice to get a proper fix on what the reproductive rate actually is in the beef herd. This calculation is complicated by the fact that the dairy herd contributes some fed steers or dairy steer crosses to the beef supply so knowing exactly how many beef steers are produced and marketed per 100 BEEF cows is problematic. However, this matter is discussed further in another article posted on this website that deals with the Productivity or the National herd.
 Some will assert that retained heifers is not the only way to expand the herd. This is true in individual herds where one can buy in breeding stock. In this context I am referencing the national herd where buying and selling breeding stock is a zero sum exercise. Of course imported breeding stock is another alternative but its impact is not significant numerically.