# Free Lease Agreement Template - Excel

Document Sample

This spreadsheet is for use in estimating the income and expenses for the beef cow enterprise.
This spreadsheet uses a economic approach rather than an financial approach to estimate income
and costs incurred by the enterprise. The financial approach uses actual income and expenses
incurred, even though they are estimated. An example of this approach is using actual interest expense
and the actual principal payments made. An alternative approach, economic, is to use opportunity cost to
estimate the costs of using assets in the beef cow enterprise. To clarify these two approaches an
example is presented below that describes the basics of each approach.

Assume:
1- You have a land worth \$5,000,000 used for the beef enterprise (range, hay base, etc.)
This is what it is worth, not what you paid for it.
2- You have a loan on this land (\$400,000) with an annual payment of approximately \$26,000.
3- You are 22 years in to a thirty year loan schedule.
See the LoanCalc1 tab in this spreadsheet to see what the principle and interest
would be on a loan for this property. This is just an example
5- Long term real interest rates are estimated to about 4%
Real interest rate is the "Nominal" or quoted rate minus the expected inflation rate
For example 7% nominal minus 3% expected inflation rate is a 4% real interest rate
Financial Approach:
1- You would enter the principle and interest rates shown in number 4 above in this spreadsheet
These are the financial cost (the cost you would enter in your record keeping system)
Economic Approach.
1- The economic approach ignores information about loan principal and interest amounts and uses
an opportunity costs approach. The opportunity cost approach uses the real rate of interest, assumed
above at 4%, to estimate the cost of using the resources in the beef cow enterprise. Using the
assumption of a 4% real rate of interest and a "market value" of \$5,000,000, the opportunity cost
of using this land to produce beef cows is \$5 million times 4% = \$200,000. The opportunity cost
estimate of \$200,000 is used instead of the principal and interest payments estimated in # 4 above.

Other differences between the two approaches exist but this example serves to illustrate a major difference.
Most income and expense that are cash (income actually received in cash or an expense actually paid in cash)
are the same in both approaches. Examples are fuel, repairs, fertilizer, chemical, hired labor costs paid in cash
or cash received for calf sales.

Both of these estimation methods can be useful, even for the same operation. An example would be using the
financial approach on an operation that is complete debt free. Using the financial approach does provide useful
results, but it may not represent the true economic costs of using such valuable resources to produce
beef. These approaches are presented with judgments left to the user on which may best fit their particular needs.

Cost of Production Estimates for Commercial Cow Calf Enterprise
Blue text and double lined boxes indicate numbers/text/labels that should be changed to match

Production Information
Breeding Herd Size Beginning of Breeding Season - Include cows and 1st calf heifers
Pregnancy percentage
Number Pregnant After Breeding Season in                   2007
Number Open, culled and sold before winter feeding period
Calving Percentage based January 1 cow numbers
Number cows that calved based on January 1 cows in
Cull cows sold after calving & before next breeding cycle in
Total Cull Cows available for sale after breeding and/or calving
Percent calf loss at calving time
Number of live calves born in         2008
Weaning Percentage based on live calves born
Number of Calves Weaned in            2008
Rep. Heifers Needed
Replacements Needed (from culled animals plus death loss of 1% beginning herd size)
Average Cull Rate based on beginning breeding inventory
Replacements are Purchased or Raised (P or R)
Replacements Heifers kept from calf crop                Rep. Heifers Kept
Replacement Breeding Cows or Heifers Purchased and added to breeding herd
Average No. Cows per Bull, Adjust for No. animals A.I.
Number bulls at beginning of Breeding Season
Average Purchase Price of Bulls
Value of Avg Cow in Breeding Herd

Net Market
Strs to sell @ weaning                  126            600
Hfrs to sell @ weaning                   76            575
Other Animals                             0              0
Other Animals                             0              0
Cull Cows (1% death loss)                40           1250
Cull Rep. Heifers                        6             900
Cull Bulls                               4            1800
Other Income (semen, Etc.)               0              0
Total Calves Sold      202
Totals Animals Sold      252
Livestock Cash Sales per Head of Breeding Cows at the Start of the Breed

Replacement Heifers                                     Quantity    Weight
Purchased Replacement Heifers                              0            0
Raised Replacements                                   Non-Cash Adjustments to the "Cash" Value of Production
Transfer of Raised Rep. Hefr. To Breeding                    44            530
Death Loss of Replacement Heifers                            0               0
Total Revenue (Calf & Non-Calf, Cash and

Since this is a "budget" for a typical years operation, herd size should not vary significantly from the beginning to the
If the numbers entered show significant variation from the beginning to the end of the year in the numbers check bel
adjust the cull cow numbers, death loss, or replacement heifer numbers until that variation is eliminated.
Retained Ownership Profitability Analysis - Cost of Production (Breakeven) Calculator

This worksheet can be used to analyze retained ownership for feeding at home, or feeding away
from home. If feeding away from home, the expenses you incur may come in different forms (\$
per pound of feed plus lot charges, or dollars per pound of gain). This spreadsheet will handle all
these situations. Simply enter the information relavent to the situation you want to anlayze. Make
sure all other numbers not relating to a particular situatioin are set to zero so expenses are not
overstated.

Enter text in blue below                                                 Shading = number is calculated/protected.
Enter a Description of the retained ownership option being analyzed:
Double lined boxes and blue text indicate numbers that are entered.

1.) Weighted Average Purchase Price of Cattle (\$/CWT): \$             110.00                       Cost/Value/Head
2.) Weighted Average Purchase Weight of Cattle (LBS):                   628                           \$690.80
3.) Number of Head You Intend to Feed:                                  200

4.) Feed Information:                       Feed Name         LBS/Head/Day           \$/LB             Cost
For Each Feed Item, Enter The          1)                             0      \$             -               0
Pounds Fed Per Head Per Day and          2) Alfalfa Hay                15      \$            0.03          0.45
The Cost in Dollars Per Pound of Feed      3) Supplement               0.06      \$            0.08       0.0048
4)                             0      \$             -               0
5)                             0      \$             -               0
6)                             0      \$             -               0
7)                             0      \$             -               0
8)                             0      \$             -               0
9)                             0      \$             -               0
10)                              0      \$             -               0
Totals:                         15.06                   0.11       0.4548

5.) Number of Days on Feed:                                                          180
6.) Expected Average Daily Gain (LBS/Head):                                         1.25
7.) Interest Rate on Borrowed Money (%): (OR Opportunity Cost of Own Money) 8.00
8.) Tax on Livestock (\$/Head):                                             \$         -
9.) Veterinary and Medical Expense (\$/Head):                               \$        7.50
10.) Transportation Cost to the Feedlot (\$/Head):                          \$         -
11.) Transportation Cost to Market (\$/Head):                               \$         -
12.) Facility Repair Costs for the Enterprise (Total \$):                   \$      400.00
\$
13.) Total Fuel Costs for the Enterprise (\$): (Excluding Cattle Transporation) 1,350.00            Straight
14.) Capital Purchases and Improvements:   Item Name             Cost          Life                Line Dep.
1)                   \$         -                0                 0.00
For Each Capital Purchase or Improvement 2)                        \$         -                0                 0.00
For This Enterprise, Enter the Cost and 3)                      \$         -                0                 0.00
Expected Years of Life.               4)                   \$         -                0                 0.00
5)                   \$         -                0                 0.00
6)                   \$         -                0                 0.00
7)                   \$         -                0                 0.00
8)                   \$         -                0                 0.00
9)                   \$         -                0                 0.00
10)                   \$         -                0                 0.00
Total Capital Costs:                            0.00                                0.00

15.) Custom Charges (\$/Head/Day):                                        \$          -
\$
16.) Enter a custom charge that is based on pounds of gain (enter \$\$/pound charged) -
17.) Death Loss (%):                                                               1.00
18.) Shrink at Time of Purchase (%):                                               1.00
19.) Shrink at Time of Sale (%):                                                   3.00
20.) Sale Commission and Yardage (\$/Head):                               \$          -
21.) Miscellaneous Costs (\$/Head):                                       \$         1.00
22.) Cash Costs of Hired Labor for This Enterprise (\$):                  \$          -
23.) Expected Selling Price (\$/CWT):                                     \$       100.00
RESULTS
Computed sale weight (including shrink)                                          821.32
Interest charge or opportunity cost per head                                      \$29.25
Total cost of gain/head (excl. purchase cost)                                   \$136.58
Total Cost/Head (Including Purchase Cost)                                       \$827.38
Total cost per head per day of wintering                                          \$0.76
Cost per pound of gain (including interest)                                       \$0.71

Breakeven selling price at given purchase price                                       \$100.74
Breakeven purchase price at given selling price                                       \$109.04

Net return per head using Expected Selling Price                                       (\$6.06)
Total net return                                                                      (\$1,211)
CALF PASTURING COST ANALYSIS
Double lined boxes and blue text indicate numbers that are entered.
INPUT DATA (1 throught 22):
1.) Purchase price of stocker cattle (\$/cwt):                             \$        100.00
2.) Average purchase weight per head (lbs):                                        825.00
3.) Number of head you intend to pasture:                                              200
4.) Monthly pasture charge/head (\$):                                                15.00
5.) Number of days on pasture:                                                         150
6.) Expected average daily gain (lbs/head):                                           1.50
7.) Interest rate on borrowed money (%):                                              8.00
(or opportunity cost of own money)
8.) Tax on livestock (\$/head):                                                        3.50
9.) Veterinary and medical expenses (\$/head):                                         4.00
10.) Cost of salt and mineral supplements (\$/head):                                   2.00
11.) Transportation costs to pasture (\$/head):                                        0.00
12.) Transportation costs to market (\$/head):                                         4.00
13.) Fencing repair costs for enterprise (\$):                                         0.00
14.) Total fuel costs for enterprise (\$):                                          400.00                 Straight
(excluding cattle transportation)                                                                     Line
15.) Capital purchases and improvements:                   Item Name            Cost           Life        Costs
1) Oilers                   800.00             7    114.29
For each capital purchase or improvement 2) Water Tank                     600.00             7      85.71
for this enterprise, enter the cost and 3)                              0.00            0       0.00
expected life in years.             4)                             0.00            0       0.00
5)                             0.00            0       0.00
6)                             0.00            0       0.00
7)                             0.00            0       0.00
8)                             0.00            0       0.00
9)                             0.00            0       0.00
10)                             0.00            0       0.00
Total capital costs:                        1400.00                  200.00

16.) Expected death loss (%):                                                        1.00
17.) Expected shrink at time of purchase (%):                                        0.00
18.) Expected shrink at time of sale (%):                                            3.00
19.) Sale commission and yardage (\$/head):                                          12.00
21.) Cash cost of hired labor for enterprise (\$):                                    0.00                            PIPH:    825.00
22.) Expected selling price (\$/cwt):                                                95.00                            RINTP:     0.08
POPH:    967.58   0.28   27.12329   1.430137
RESULTS
Computed sale weight (including shrink):                                         1018.50
Interest charge or opportunity cost per head:                                     \$28.83
Total cost of gain/head (excl. purchase cost):                                   \$142.51
Total Cost/Head (Including Purchase Cost):                                       \$967.51
Total cost per head per day of pasturing:                                          \$0.95
Cost per pound of gain (including interest):                                       \$0.74

Breakeven selling price at given purchase price:                                  \$94.99
Breakeven purchase price at given selling price:                                 \$100.01

Total net return:                                                                  \$13.17
If these macros do not work properly the first
time, go to File, then Page Setup and make
sure that you check the Fit to 1 pages wide
and 1 pages tall option.

To test the macro with minimal paper use
select the print stockers only button. It shoul
print only one sheet of paper.
Cost of Production Estimates for Commercial Cow Calf Enterprise
Blue text and double lined boxes indicate numbers/text/labels that should be changed to match your situation.

Calendar Year
Production Information
Breeding Herd Size Beginning of Breeding Season - Include cows and 1st calf heifers
Pregnancy percentage
Number Pregnant After Breeding Season in                               = January 1 on hand
Number Open, culled and sold before winter feeding period
Calving Percentage based January 1 cow numbers
Number cows that calved based on January 1 cows in
Cull cows sold after calving & before next breeding cycle in
Total Cull Cows available for sale after breeding and/or calving
Percent calf loss at calving time
Number of live calves born in
Weaning Percentage based on live calves born
Number of Calves Weaned in
Rep. Heifers Needed
Replacements Needed (from culled animals plus death loss of 1% beginning herd size)
Average Cull Rate based on beginning breeding inventory                               Cull Rate
Replacements are Purchased or Raised (P or R)
Replacements Heifers kept from calf crop                Rep. Heifers Kept
Replacement Breeding Cows or Heifers Purchased and added to breeding herd
Average No. Cows per Bull, Adjust for No. animals A.I.
Number bulls at beginning of Breeding Season
Average Purchase Price of Bulls
Value of Avg Cow in Breeding Herd

Net Market        Price
Strs to sell @ weaning                      0
Hfrs to sell @ weaning                      0

Cull Cows (1% death loss)                      0
Cull Rep. Heifers                              0
Cull Bulls                                     0
Other Income (semen, Etc.)

Replacement Heifers                                           Quantity   Weight          Per Pound
Purchased Replacement Heifers
Raised Replacements                                      Non-Cash Adjustments to the "Cash" Value of Production
Transferred of Raised Rep. Hef. To Breeding
Death Loss of Replacement Heifers

Operating Costs for a Cow-calf Enterprise
Value     Cow Owner
Feed Costs (Raised and Purchased)                                        Units           Quantity        Price        Value       Per Head    Share (%)
IF you include a rental
rate for pasture on
owned land here, you
should not include the
value of the land in the
fixed cost section. The
to the pasture rental
rate (\$\$ per AUM).
Property Taxes and other
costs associated owning
land should not be listed
below IF a cost is
included here.             Subtotal of Feed Costs
Cow Owner
Operating Costs Directly Associated With Livestock Care and Handling                                                                          Share (%)
Vet and Medicine
Livestock Hauling (Not Related to Marketing)
Professional Fees (Dues, Subscriptions, Legal, etc.)
Hired Labor
Marketing Costs                                          Price/Unit      No. of Units
Sales Commission
Hauling to Market
Yardage
Custom Hire
Rent or Lease (Vehicle, Machinery, Equipment)
Utilities
Supplies

Operating Costs of Facilities and Equip. Used in Lvstk Production.
Note: the costs of        Operating Costs of Equipment (Fuel, Oil, Repairs)
machinery and equipment Operating Costs on Machinery (Fuel, Oil, Repairs)
used to put up hay        Operating Costs of Vehicles (Fuel, Oil, Repairs)
should not be included in Facility Repairs and Maintenance
this section IF there was  Fences
a charge made for raised   Corrals
hay under feed costs       Buildings
above. The charge for      Water Facilities
raised hay in the feed    Hired Labor
costs section includes    Rent or Lease
the cost of machinery and Supplies
equipment to put up       Utilities
the hay.

SUBTOTAL OTHER VARIABLE COSTS

Interest on Operating Costs
Sum of Operating Costs x Months Borrowed
x Interest Rate Per Month
Annual Interest Rate (11% = .11) = Nominal Rate
Avg. Number of Months Money Borrowed                                                                              Cow Owner
Annual Inflation Rate ( 9% = .09)                                                                                  Share (%)
Total Interest Cost (Using Nominal Interest Rate)

Ownership Costs for a Cow-calf Enterprise
Machinery and Equipment used for Livestock
Dollars           Useful        Salvage     Calculated     Value     Cow Owner
Depreciation Item Name                                    Invested          Life Yrs        Value     Depreciation   Per Head    Share (%)
Include equip. used
exclusively on OR
percent of Equip.
value that is used
partially on livestock

Do not include
hay handling and
harvesting equip.
here. The cost
of hay is
included in feed
costs.                                                  Totals

Interest (Opportunity Cost Using Real Rate of Int.)
Personal Prop. Taxes on Mach & Equip Used for Lvstk
Insurance

Buildings and Improvements Used for Livestock
Dollars           Useful        Salvage     Calculated     Value     Cow Owner
Item Name                                   Invested          Life Yrs        Value     Depreciation   Per Head    Share (%)
Depreciation

Totals

Interest (Opportunity Cost Using Real Rate of Int.)
Personal Property Taxes and Buildings & Imprv.
Insurance

Owned Land Used for Livestock Operations                                                                                                     Cow Owner
Parcel Description                   No. Acres              Value/Acre     Total Value                               Share (%)
The land charged to the
cows that are on shares
should include only the
those acres that are
actually utilized by the
share animals.
Acres used by other
animals (the tenant's)
livestock) should not be
included here. Some
type of proration may be                                                             Total
needed in this section.  Interest (Opportunity Cost Using Real Rate of Int.)
Real Estate Taxes
Insurance -- Include in insurance cost under buildings and improvments

Livestock Breeding Herd
Dollars               Useful         Cull       Calculated     Value     Cow Owner
Depreciation--Replacement Allowance                                Invested              Life Yrs        Value     Depreciation   Per Head    Share (%)
Bulls
Breeding cows
Note: If the cow herd
size remains constant                               Totals
through raising
replacements and normal Interest (Opportunity Cost Using Real Rate of Int.)
culling, then do not      Personal Property Taxes
charge a depreciation
expense for the cow herd.

DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 2143 posted: 7/7/2010 language: English pages: 6
Description: Free Lease Agreement Template document sample