Program to Estimate Feedlot Breakeven Purchase Price (BEFLCALC)

This program is a special application of FLCALC, modified to estimate the necessary purchase price of feeder cattle with the objective of a feedlot breakeven. To facilitate easy use, feed cost is computed from corn price per bushel, plus a fixed markup for feedlot margin and feed additives. Using the price of corn to determine the cost of a feedlot ration is based on the assumption that all the alternative feeds are priced in relation to corn. Most yearling cattle do not require much supplemental protein above what can be obtained from the grains plus urea. Roughages in the case of the larger custom lots are as expensive as corn in the ration. The ration price will be computed by the following formula: (((corn price per bu. / .56) / (corn dry matter percent /100)) * 2000) + fixed markup per ton. Many commercial feeders mark up feed to generate about $.20 per day. If commercial cattle eat about 20 pounds of dry matter per day, this would imply a markup of $20 per ton. In this example program, $7.50 was added to the $20 to cover the cost of feed additives, etc., in the supplement.

 

To use this program, the user is REQUIRED to calculate the Net energy for maintenance (NEm) and Net energy for gain (NEg) values of the diet on a dry matter basis (the values of 95 NEm and 61 NEg seem to be about right for high plains custom feedlots). The user then inputs the expected average daily feed intake for the feeding period. Producers will often estimate feed intake from past feeding experiences. If in doubt, custom feedlots can usually help producers estimate feed intake. The following general rule may be helpful. Dry matter intake should average about 2.3  to 2.5 percent of the mean feeding weight (H-9). Gain is the product of feed intake, thus if feed intake is too low or too high, check the DAILY FEED DRY MATTER #.

 

Inputs necessary to evaluate a cattle feeding venture involve the usual cattle and financial information. This program is designed for the user to enter data on a trial basis, for example, make an entry and to calculate how much you can afford to pay for feeder cattle.

 

The program format is shown in Table 1. In copies of this program distributed by Oklahoma State University, all cells, except those the user should change, are protected. The protection of cells containing equations is necessary because any value entered over a calculated number will result in the equation being erased. The unprotected cells (a * in the label) requiring inputs are listed below in the same order that they appear on the screen of the spread sheet. (Note: Bold face items different from FLCALC)

 

1. Purchase weight in pounds (this program works pay to pay).
2. Days to be fed (days on feed).
3. Sex, steers (1) or heifers (2); no other choices.
4. Fixed markup per ton dm. (total other non-corn costs including markup).
5. Corn dry matter percent (this must be the purchase basis dry matter for corn).
6. Expected selling price ($/cwt) of the cattle at market time.
7. Percent cattle equity (this represents interest free money).
8. Cattle Interest Rate (percent)
9. Freight to the feedlot ($/head).
10. Estimated death loss (percent).
11. Medical cost ($/head).
12. Beef check off ($/head)
13. Other costs such as hedge or option costs ($/head).
14. Profit objective ($) head (may be a positive or negative number).
15. Yardage cost, if charged ($/day)
16. Estimated feed intake (average pounds of dry matter per day).
17. Your estimate of the cattle daily gain (pounds per day).
18. Interest cost on the operating capital (percent).
19. ***OTHER INPUTS (to the right top)***

Corn Price per bu. (net delivered corn price to feedlot).
Ration NEm (entered as mcal per 100 pounds).
Ration NEg (entered as mcal per 100 pounds).
Both of the energy values are entered on a dry matter basis.

 

ANSI-3273Table1

 

Table 2 shows the spread sheet with column headings and rows numbered to correspond to the program listing, which follows in Appendix 1. To save space, the headings have been removed from the program listing. If entering this program from a key board, it is suggested that all headings and labels be typed in first, followed by the data, and finally equations from the listing in Appendix.

 

This program may be custom tailored for additional inputs (for example, an additional input slot for “trucking expense in $/head) by using the insert command and adding rows between rows 16 and 19. If properly done, data entered in column D (i.e. trucking expense) may be transferred to column F and calculations of trucking expense on a cost/day basis can be calculated in column H by replicating the equations above each insertion. It will be necessary to turn off the global protection before any modifications may be made to the program. See the spread sheet users manual.

 

ANSI-3273Table2

 

Note in the listing that some very critical data and equations are stored in column K (off the sheet) on an 80 column screen. These are the net energy equations and a number of other critical statements. This is done to avoid clutter on the screen. The net energy equations used are the steer and the heifer equations published in the 1976 edition of the NRC Nutrient Requirements for Beef Cattle. These equations have worked well for estimations made on a “pay to pay” basis, assuming normal and reasonable weighing conditions and shrinks. When making modifications to the program, it is critical that these statements not be damaged. It is wise to make a re-named working copy of the program before making modifications.

 

This program uses a circular calculation to estimate mean feeding weight because the mean feeding weight of the animal cannot be determined until the program is recalculated. To ensure accuracy, recalculation is set to iteration, with the count equal to 9. The Lotus 123 keystrokes will be: /, W, G, R, I, 9, to set iterations to 9.

 

Some cattle feeders may desire more options for evaluating weather conditions, cattle types, multiple rations or modifications of the energy requirements. In these cases, users may wish to use a basic language program entitled OSU Beefgain, which is more flexible program. It may be obtained from Extension Animal Science at Oklahoma State University.

 

Appendix 1. Listing of non label cell contents.

 

H19: (C2) PR + F19/D5
D20: (C2) U 0.05
G1: (D4) PR @NOW

 

F20: (C2) PR (D5*D20)
D3: (C2) PR (K15)

 

H20: (C2) PR (F20/D5)
H3: (C2) U 3.8

 

D21: (F2) U  20.5
D4: U 700

 

K21: PR ((D21-(((D11*0.43)/((H4*0.01))))))
H4: (F2) U 94

 

D22: (F2) U 3
D5: U 130

 

K22: PR((((H8/2000)*D21))* D5)
H5: (F2) U 61

 

D23: (F2) U12
D6: U 1

 

F23: (C2) PR (((@SUM(F16..F20)+K22-F19)*((D23/100)))
D7: (C2) U 27.5 *((D5/720)))
D8: (F2) U 84.5

 

H23: (C2) PR (F23/D5)
H8: (C2) PR (((H3/0.56)/D8/100)*20)+D7

 

K23: PR ((((((((((((((K21 H5)/100)) 0 003112))/K11))
K8: PR 75 +0.00017474))ˆ 0.5))-0.01322))/0 001556)
D9: (C2) U 70

 

K24: PR ((((((((((((((K21*H5)/100))*0 00576))/K11))
H9: (F2) PR((((F35-D4)/2))+D4) +0.0001974)) 0.5))-0.01405))/0.00288)
K9:  PR (H9 0.75)

 

F25: (C2) PR @SUM(F13..F23)
K10: PR @ISERR(H9)

 

H25: (C2) PR @SUM(H23..H13) – H19
K11: PR @IF(K1O,K8,K9)

 

F26: (C2) PR + K22
D12: (F2) U 25

 

H26: (C2) PR (F26/D5)
D13: (F2) U 12

 

F27: (C2) PR @SUM(F25..F26) -F19
F13: (C2)PR ((((((D3*D4)/100)+F14)-K12)*((D13/100)))*

 

H27: (C2) PR (F27/D5)
((D5/360)))

 

C29: (D4) PR @DATEVALUE(D5+G1)
H13: (C2) PR (F13/D5)

 

D31: (F2) PR + D22
K13: PR (D4/100)*D3

 

F31: (F2) PR @CHOOSE(D6,10000000,K23,K24)
D14: (C2) U 0

 

D32: (F2) PR (D21/D31)
F14: (C2) PR + D14

 

F32: (F2) PR (D21/F31)
H14: (C2) PR + F14/D5

 

D33: (C2) PR (((((F26+F20)+F16))/((D5*D22)))*100)
K14: PR (K13*D12/100)

 

F33: (C2) PR (((((F26+F16)+F20))/((D5*F31)))*100)
D15: (F2) U 0.7

 

D34: (C2) PR (((F27)/((D5*D22))) 100)
F15: (C2) PR (((((D3*D4)/100))+D16)*((D15/100)))

 

F34: (C2) PR ((F27/((D5*F31))) 100)
H15: (C2) PR (F15/D5)

 

D35: (F2) PR (D4+((D5*D31)))
K15: (C2) PR ((F36-F19-F27)/D4)*100

 

F35: (F2) PR (D4+((D5*F31)))
D16: (C2) U 8

 

D36: (C2) PR ((D9*D35)/100)
F16: (C2) PR + D16

 

F36: (C2) PR ((D9*F35)/100)
H16: (C2) PR (F16/D5)

 

D37: (C2) PR (D36-(((D3*D4)/100)))
D17: (C2) U 1

 

F37: (C2) PR (F36-(((D3*D4)/100)))
F17: (C2) PR + D17

 

D38: (C2) PR ((((((D3*D4)/100))+F27)/D35)*100)
H17: (F2) PR (F17/D5)

 

F38: (C2) PR ((((((D3*D4)/100))+F27)/F35)*100)
D18: (C2) U 0

 

D39: (C2) PR ((D36-(((D3*D4)/100)))-F27)
F18: (C2) PR + D18

 

F39: (C2) PR ((F36-(((D3*D4)/100)))-F27)
H18: (C2) PR (F18/D6)

 

D40: (F2) PR (((((D39/K14)*100))/((D5/365))))
D19: (C2) U 0

 

F40: (F2) PR (((((F39/K14)*100))/((D5/365))))
F19: (C2) PR + D19

 

D41: (C2) PR ((D36-F27)/D4)*100

 

David Lalman
Extension Beef Cattle Specialist