Retained Ownership Budget Templates
|
|||
|---|---|---|---|
|
This "User's Guide" is to be used with either the MS Excel® version or the Corel Quattro Pro® version of the "Backgrounding Budget Template" and either the MS Excel® version or the Corel Quattro Pro® version of the "Custom Feeding Budget Template". It is recommended that you print either this document or the Adobe Acrobat® version of this "User's Guide" to use with the Backgrounding Budget Template and the Custom Feeding Budget Template. The following discussion of the retained ownership budget templates assumes that the budgets are run in Excel 97. The budgets will run in Quattro Pro; however, the background colors may not be the same as noted here and some cell addresses may be slightly different. The Appendix to this document shows each worksheet in its entirety for use as a "road map" to cell locations. This should help to overcome any confusion arising from any differences in Excel or Quattro programs. To begin using this budget template, start on the BKGRND page. Page names are located on tabs at the bottom of each worksheet. This sheet contains five distinct sections:
Notice the different colored fields on this sheet. These colors indicate which type of information is located in the cells. Yellow fields are for the input of user provided information. This includes performance information including daily feed consumption and average daily gain for the selected backgrounding alternatives as well as information on input prices, output prices, and cattle weights. The user must define the backgrounding program to be budgeted. The DATASHEET worksheet contains feed consumption and average daily gain figures for a wide variety of harvested-feed backgrounding programs. More will be said about this sheet later. If the user chooses to use his own figures to define a unique backgrounding program, it is important to be sure that these figures are realistic. These templates have the flexibility to accommodate a very wide variety of programs; however, the output from the budget will only be as good as the information provided. For the most part, the information required in the input sections is very straightforward; however, one important feature of this section of the budget needs to be pointed out. Cells D17:G19 provide space for the entry of three by-product feeds. Any by-products may be included in this space. By default, the three listed by-products are soybean hulls, corn gluten, and distiller's slop. To change these, all that must be done is to changes the by-product name in Cells G17:G19 and to include the appropriate price in Cells F17:F19. Notice that prices must be entered on a per pound basis. If by-product prices are quoted on a per cwt or per ton basis, the appropriate conversion must be made before entering a price. Also notice that it is not necessary to change the by-product names in every table. Other tables are linked to Cells G17:G19 and will be changed automatically as long as the change is made there. It is not a requirement that only by-products be entered in this section. For example, to define a preconditioning program, the price for a preconditioning feed could be entered in one of these spaces. Blue fields contain formulas which calculate the indicated production and financial information. The Cost of Production Itemization table in Cells A31:B50 calculates cost for each input item on a per head basis. The Cost Summary table in Cells D31:E44 provides more detailed cost information including feed cost on a per pound of gain and a per day basis as well as a breakdown of cash and non-cash costs. The Revenue Summary in Cells A53:B56 calculates an estimate of net revenue based on the user-provided output price. Keep in mind that net revenue calculations depend to a very large degree upon the estimate of output price. This estimate will be much less reliable than the input price information, which will, for the most part, be known at the beginning of the backgrounding process. It may be useful, therefore, to use a range of possible output prices to determine a likely range of net revenue. The Break-Even Price Summary table in Cells D53:E59 is probably the most important of the calculated fields. This table displays three important break-even prices. First, a break-even selling price over all costs is calculated. This is the price that a producer will have to receive at the end of the backgrounding program in order to cover all costs. Second, a break-even selling price over all cash costs is given. This is the price that a producer will have to receive to cover cash (or out-of-pocket) costs. Finally, a break-even purchase price is also calculated. This is the price which a producer can afford to pay for calves going into the backgrounding program and still cover all costs. Note that the two break-even selling prices depend only upon input prices and cattle performance. The estimate of output price does not affect these calculations. On the other hand, the break-even purchase price does depend upon the estimated output price. Consequently, as with the net revenue figure, it is wise to perform calculations using a range of possible output prices to observe how the break-even purchase price is affected. Defining a Backgrounding Program As noted, the DATASHEET worksheet provides technical information on a variety of backgrounding programs. Specifically, this sheet contains four tables, each of which describes six different harvested feed backgrounding programs. For each of these programs, daily feed consumption and average daily gain are provided. Table 1 is provides information on backgrounding steers. Days on feed (DOF) are calculated based on the average daily gain achieved under each program. Note that the yellow cells on this sheet are not protected while green cells are. The only unprotected cells in this table are for cattle weights (Cells L19:R20) and for defining a custom program. As weights are changed in these cells, feed intake is automatically adjusted in all tables. If you want to define a program which is not included in the table or if you want to consider different rates of gain, then use the rightmost column in the table to define a custom program. Table 2 provides production information relevant for backgrounding heifers separately from steers. In this table, differences in performance between heifers and steers are reflected in the amount of feed consumed. In this table, DOF and ADG will be the same as in table 1. Note that the information is this table is based on the data provided in table 1. Consequently, cattle weights must be entered in table 1. Linkages between the two tables will transfer this weight information to table 2. Alternatively, a producer who wants to feed heifers with steers should use the information in table 3. In this table, feed intake for heifers is the same as for steers in table 1; however, ADG is only 90% of that achieved by steers. Again, weights must be defined in table 1. Weights should be entered which are appropriate for steers in the backgrounding program. Linkages between the cells will transfer this information from table 1 to table 2. Notice that sale weight for heifers will be different from that for steers, reflecting the lower ADG of the heifers. Table 4 provides production information relevant for backgrounding Holstein steers. Here, performance differences are reflected by feed consumption that is 10% higher than in table 1 in order to achieve the same daily gain. There are two options for transferring information in the tables on the DATASHEET worksheet to the input fields on the BKGRND sheet. First, the tables may simply be used as a "scratch pad" for determining the appropriate parameters for a backgrounding program. Once the program has been defined, the information from the appropriate table may be written down and re-entered in the input fields on the BKGRND worksheet. Alternatively, the appropriate column from the appropriate backgrounding table can be highlighted and copied to the computer's clipboard. It can then be pasted directly into the input field using Edit>Paste Special>Values. Multi-stage Backgrounding The worksheet MULTI_STAGE provides a budget for use in evaluating more complicated backgrounding alternatives. Specifically, this budget template permits break-even analysis of 2-stage and 3-stage backgrounding. In this context, 2-stage backgrounding is defined by a period of fall grazing on stockpiled fescue followed by a period of backgrounding on harvested feeds or by-products. Three-stage backgrounding is different in that the initial fall grazing period is followed by a period of over-wintering calves on hay which is then followed by spring grazing. Colored fields on this sheet have the same significance as on the BKGRND sheet. The input price and performance information table is located in Cells A13:E34. There is a separate section is this table for each stage of backgrounding. Unlike the green section on the BKGRND sheet, this section requires not only feed usage and ADG figures, it also asks for pasture cost. Note that pasture cost is to be entered on a per head basis. It is not necessary to provide other input costs. Since this sheet is linked to BKGRND, those input prices will be used automatically in the calculations on the MULTI_STAGE sheet. Use of the MULTI_STAGE sheet requires that additional output prices be provided. The reason for this is that the output from one stage of backgrounding represents the feeder calf input for the subsequent stage. In order to completely and accurately account for costs, feeder calf cost for each stage must be based on the market price for feeder calves of the appropriate weight. The output price table is located in Cells A37:D45. Feeder calf weights are calculated based upon the production parameters previously entered. Thus, the user must provide a feeder calf price appropriate for the given weight. A Production Summary table is given in Cells G14:K17. This table indicates the expected weight of calves at the end of each backgrounding stage based on the production parameters provided. A Cost Summary table is located in Cells G19:M52. This is quite a long table because it provides a complete summary of each backgrounding stage separately. Thus, for each stage, total cost of production is provided along with an itemization of costs per head. Total cost per pound of gain and feed cost per pound of gain are provided as well. A Revenue and Cost Summary table is given in Cells A52:H66. The table provides total revenue, total cost, net revenue, and break-even prices as well as feed costs and cost of gain calculations for each stage of backgrounding. As mentioned, for the purpose of the individual stage calculations, feeder calf cost in each backgrounding stage is determined by the market value for feeder calves leaving the preceding stage. By calculating costs in this manner, it is possible to examine the profitability of the individual backgrounding stages. In addition, an "integrated program" line can be found in the Revenue and Cost Summary table. In the calculations given in this line, feeder calf cost is only assessed at the beginning of stage 1. Thus, this line calculates net returns from a 2-stage or 3-stage backgrounding program without revealing anything about the profitability of each stage. This line is useful because figures in this line will not be sensitive to changes in the prices used for each stage's output. Only the purchase price of calves going into the backgrounding program will be important in these calculations. Most producers will probably be interested in this "bottom line"; however, looking at the more detailed information about each stage can be useful. Remember when applying these budgets that the output is only as good as the information provided. This is a particularly important point to keep in mind when evaluating multi-stage programs since more output price projections are required. Ideally, budget calculations should be performed again at the end of each stage when prices are known--or at least do not require such long projections to estimate. Cull Cow Grazing The worksheet CULL_COW contains a budget template for evaluating cull cow grazing through the fall and winter. The format of this sheet is identical to that of the preceding two sheets. A Production Information table is located in Cells A3:D19. This table has two sections: feed usage and physical factors. Feed usage is entered in pounds per day. This section contains a fairly wide variety of possible feeds used to supplement grazing. The physical factors section of this table allow entry of ADG, length of feeding/grazing period, and purchase weight. Sale weight is calculated based on ADG and length of feeding/grazing period. This format is consistent with the way most producers manage cull cow programs. Cattle are fed or grazed for a specific length of time rather than to a specific weight as is usually the case with backgrounding calves. An Input Prices table is found in Cells F4:J19. As in the BKGRND worksheet, 3 by-products can be entered in this section. Again, it is critical that by-product prices be entered on a per pound basis. Expected output price is entered in Cell I21. Prices must be entered on a per hundredweight basis. A Cost of Production Itemization is found in Cells A23:C39. Calculations in this table are identical to those in the BKGRND and MULTI_STAGE worksheets. A Cost Summary table is located in Cells F23:I34. Here, costs are broken down on a per pound of gain and per day basis. The total cost figure in Cell I32 corresponds to the break-even price per head. A break-even price per hundredweight is also reported based on the expected sale weight provided in Cell D19. A Revenue Summary table is located in Cells F36:I40. Total revenue is calculated based upon expected sale weight and price. Net revenue is calculated on a per head and a per hundredweight basis. Custom Feeding Budget Template The custom feeding budget template consists of three separate sheets: RO_BUDGET, FIN_SUM, and RISK_MGT. RO_BUDGET is essentially an input form. Input prices and production information are entered here in the yellow fields. The input price table is located in Cells A8:C20. Production parameters are entered in Cells A22:C33. Most of the items in these tables are very straightforward, but a few key considerations need to be pointed out. First, it is important to pay attention to whether information related to feedstuffs is requested on a dry-matter or as-fed basis. This becomes critical when ration cost estimates are made on the next worksheet. Second, the last two cells in the input price table allow for the calculation of interest expense when all or any part of cattle and feed is financed. Cell C19 asks for the percent of equity in cattle. If no part of the cattle is financed, enter a 1 in this cell. If, for example, half of the value of the cattle is financed, enter a 0.5 in this cell. The percent of feed financed calls for a similar entry in Cell C20; however, rather than asking for percent equity, this cell asks for percent financed. In no feed is financed, enter a 0. If three-quarters of the feed is financed, enter a 0.75. Finally, it is very useful to enter an accurate placement date. Using this date along with other performance information, an expected slaughter date is estimated. Knowing this date can be very helpful in formulating risk management strategies using futures contracts and options. Cells A36:F41 provide a brief list of summary statistics which are calculated from the information provided in input tables. This table allows the user to observe how important variables such as break-even price and cost of gain respond to changes in input values without having to tab between worksheets. The user may wish to split the screen so that the summary statistics and input forms remain in view simultaneously. The FIN_SUM worksheet provides a financial summary of the custom feeding program. A brief summary of the feeding period is given in Cells A3:C6. This summary includes DOF, pounds gained, and expected slaughter date. A Revenue and Expense Summary is given in Cells B8:I25. In this table, revenue and expenses are itemized on a per head and per hundredweight basis. These figures are totaled as well, based on the number of head on feed as reported on the input form. A capital requirements table in Cells B29:E38 shows how much capital investment is required to feed the specified number of calves. Capital requirements are reported as total capital required and the producer's share of this total based on the equity information provided on the previous sheet. This may be critical information to producers facing a binding capital constraint. The RISK_MGT worksheet allows the user to compare alternative risk management strategies. Three different strategies are included on the sheet: forward contracting, options, and futures contracts. Side-by-side comparison can be made of the net returns resulting from each of the three strategies. Keep in mind that the options hedge and the futures contract hedge do not eliminate basis risk. Net revenue projections will depend upon the relationship between futures and cash prices at the time slaughter cattle are sold. Hedging summaries of the contracting, options, and futures contract alternatives are given in Cells G8:H10 , Cells G12:H16, and Cells F31:H33 respectively.
AppendixThe Appendix is available on the Adobe Acrobat (.pdf) version of this file. |
||
|
|||
