Agribusiness Management Series: Oklahoma Wheat Stocker Spreadsheet Decision Aid

The Oklahoma Wheat Stocker Budget Generator was developed to assist producers project economic profitability of their wheat stocker enterprise. The program is a joint project of the Departments of Agricultural Economics and Animal Science at Oklahoma State University. (Author: DeVuyst; Type: Spreadsheet).

 

The program will work best in MS Excel 2007, but will also run on MS Excel 2003 if the user has downloaded and installed conversion software from Microsoft. For the program to function properly, the user must allow the macro features of MS Excel. In MS Excel 2007, the user is prompted with a warning just below the button bar that macros have been disabled. Click on the warning and enable macros. In MS Excel 2003, the user must change the security level to medium or low to enable macros.

 

Data Requirement

The program enables the user to supply price and production information. However, price forecasts for both purchase and sales can be generated by the program if the user has internet access.

 

Only cells with a yellow background and black text are changeable. All other cells are calculated automatically by the program, are not accessible to the user, and have a light blue or green background.

 

The first input cells request a name for the budget being generated and the current date. These fields are optional.

 

 

 Input fiields  and examples

 

 

The next cells request the purchase location for the stocker calves. A pop-up menu will ask the user to choose from either the Oklahoma City (OKC) or the “Oklahoma Combined” auctions. By pressing the “UPDATE PRICE DATA” button, the program will download a file from the internet with the most recent USDA price data available for Oklahoma locations. Note: if you have internet access requiring a login ID and password, open the internet browser and login before using the download feature. The program uses the price and location information to generate a purchase price forecast, if the user chooses to do so.

 

 

 

The third set of cells request the Expected purchase date, Days in the receiving program, and Days grazing wheat. The purchase month can be entered using a drop down menu. The Expected sale date is calculated using this information. 

 

 

The user next must supply information regarding the stocker Purchase weight (lb per head) and Purchase price ($ per cwt). If stockers have already been purchased, enter actual weight and price. Alternatively, the program can generate an expected purchase price. At cells F21 or H21, simultaneous pressing “control” (ctrl) and “t” will generate an expected purchase price based on location, current market price, and a seasonality index. Note: pressing ctrl-t will write over previously entered price data. If you choose to, you can write over the computer-generated forecast at any time and reproduce it again by pressing ctrl-t at cells F21 and H21.

 

Enter estimates of expected average daily gain (ADG) for both the receiving program and for the time grazing wheat. Also, enter an estimate of expected death loss. Stocking density must be entered in head per acre at the time calves are put on wheat pasture. The program then reports density in pounds per acre.

 

Production costs are entered under four general headings. Veterinary cost includes all anticipated expenses for vaccination, treatments, and parasite control in $ per head. Marketing cost includes transportation costs, buyer fees, commissions, and check off fees in dollars per head. Other costs per month include costs not included in the previous two headings and items in Cost of gain. Cost of gain can be entered directly into the budget or by using a detailed worksheet. Cost of gain, entered in $ per pound of gain, includes all purchased feeds, mineral, pasture rent, machinery and equipment expenses, labor, and management. The detailed worksheet can be accessed at cells F33 and H33 by pressing ctrl-t.

 

 

 

 

 

 

 

 

The Cost of Gain Analysis, accessed at cells F33 and H33 by pressing ctrl-t, allows the user to enter feed and other costs using a detailed worksheet. Hay, supplement, and mineral prices are entered in $ per ton (as fed). Hay and supplement quantities fed are entered in pounds per head per day and mineral in ounces per head per day for both receiving and wheat grazing. Wheat pasture rent can be entered as $ per cwt per month or $ per pound of gain using the pop-up menu at cell F12. The program then computes feed costs for both the receiving and grazing periods. Transportation to pasture and other receiving costs are entered as $ per head. The cost of implanting, equipment and machinery, management fees and other costs are entered as $ per head during the entire grazing period. Labor is enter as $ per head per day for receiving and grazing periods.

 

Results from the Cost of Gain Analysis are reported as Total Receiving and Grazing Expenses (in $ per head) and as Cost of Gain (in $ per pound of gain). These results can be automatically transferred to the Budget tab (cells F33 and H33) by left clicking on the Return to Inputs Page. If the user does not want these results to be transferred to the Budget tab, left click on Cancel and Return to Inputs. 

 

To approximate the opportunity cost of equity and cost of financing, enter an annual Interest rate as a percentage.

 

 

The final data requirements are expected sales prices. The program will generate a price forecast if the user presses ctrl-t at cells F42 or J42. Alternatively, the user can enter a price directly into cells F42 and J42. Note, the price should be based on the weight of animals being sold.

 

The remaining cells in the Wheat Stocker Spreadsheet Decision Aid are automatically calculated by the program. All results are report in $ per head purchased (i.e., “deads in”).

 

Selected results are reported in $ per day per head purchased. Results include Gross Revenue, Total Expense, Net Return, Breakeven Price and Net Return per acre.

 

Wheat Stocker Enterprise Budget

 

Eric A. DeVuyst
Associate Professor
Farm Management and Production Management

 

Francis Epplin
Professor
Agriculture Business and Commercial Agriculture

 

Derrell Peel
Professor and Extension Economist
Livestock Marketing

 

Karen Taylor
Senior Relations Manager, Farm Credit Canada

Gerald Horn

Professor, Cattle Nutrition and Management

David Lalman
Professor, Beef Cattle