Cost-Benefit Analysis Spreadsheet Calculator

The calculator was commissioned by the Pasture Renewal Charitable Trust. It has been developed by John Stantiall of Stantiall & Keeling Ltd, Feilding, plus a significant input by Rob Brazendale of DairyNZ on the dairy version. A number of people throughout the industry have also offered comments.

A workshop was held to finalise some of the technical issues in the spreadsheet. The constructive comments and feedback has been greatly appreciated and have helped to improve the final version of the spreadsheet.

Calculators

PRCT Dairy NZ 28 Oct 2009.xls
( 638 kb)

PRCT Sheep, Beef & Deer 28 Oct 2009.xls
(788 kb)

General Comments

• The spreadsheet is designed to analyse the direct costs and benefits of pasture renewal.
• The spreadsheet does not look at whole farm systems nor at multiple cropping programmes.
• The spreadsheet can be used for a range of renewal situations: e.g.
  - following a crop
  - as a double-spray or summer fallow programme
  - pasture-pasture with spray-only or spray and cultivation.
• The spreadsheet does not contain any databases of products or prices. It is the responsibility of the user to keep these figures up-to-date.
• It would be helpful if the Trust members provided an up-to-date list of products and prices on a website.
• It is the operator’s responsibility to ensure that up-to-date and sensible figures are used throughout the spreadsheet
• NOTE: It is feasible to get a negative result, and this must be analysed in terms of costs, performance and returns.
  

Layout

The spreadsheet is set-up in three sections: Paddock Characteristics, Costs and Cost- Benefit analysis. The initial section about the paddock characteristics may be skipped, but a lack of adequate knowledge about the situation may lead to incorrect decisions.

Section 1: Paddock characteristics and cost of any capital development. This provides an opportunity to find out more about the planned re-grassing and to provide tailored advice.

Section 2: The activities, materials and costs for the re-grassing programme.
This page can be used alone as a cost estimation.

Section 3: The cost-benefit analysis.
This page will require some sensible inputs by the operator (eg estimates of annual pasture production and current product prices). It may need extra training to ensure the answers are sensible. However, the analysis is very powerful in terms of identifying the likely returns, and the factors which have the biggest impact. To keep the livestock gross margins current, some updating of stock prices will be required from time to time. A sensitivity section shows the financial impact of achieving lower or greater pasture yield than expected.

Cover: The completed spreadsheet may be printed out together with the cover page and be used as a report for the client.

Introduction to the Pasture Renewal Spreadsheet

General

     Potential Benefits of Pasture Renewal:

• More dry matter grown
• Increased ME content
• Improved utilisation
• Improved seasonal growth
• Improved animal health

  Pasture renewal has three costs:

1. The actual cost of renewing the pasture (establishment cost).
2. The value of pasture not grown during the establishment phase (feed gap).
3. The cost of extra stock to eat the extra feed grown.

How to use this spreadsheet

Paddock Characteristics: This section allows you to record why the pasture in the paddock needs renewing and what corrective procedures may be required prior to pasture renewal.

Capital Development Costs: This is not used in the economic analysis, but is included for completeness where capital work is being undertaken. The cost of such work should still be a consideration in the overall process.

Establishment Costs: Enter the actual costs depending on which method of establishment you are planning.
It is recommended to get quotes from your local contractor and rural retailers.

Feed Gap
The feed gap arising from pasture renewal must be filled either through purchased feed or by growing more feed on farm.
Enter pasture growth rates to calculate how much pasture growth is sacrificed and the number of days each month that the pasture is out of production.
Enter the cost of replacement feed in cents per kgDM.
If you consider that there is no cost while the paddock is out of production, then enter 0 c/kgDM for the cost of feed.

Cost Benefit Analysis
Enter the estimated pasture yield of old pasture and the expected yield of new pasture.
Update the expected average milksolids price and expected extra costs (as a % of 4 extra income (for dairy)

OR expected product prices and direct costs for the next 5 years at the bottom of the last page (for sheep beef deer).

The Gross Margin includes an extra fertiliser cost, based on maintaining a higher pasture yield.
Estimate how well the pasture will persist over a 5 year period (see guidelines on right hand side of that section).

Please Note:
This spreadsheet calculator does not include any animal health benefits nor any other costs or benefits which can not easily be quantified or valued.
Also Note:
Apart from some initial example figures, there is no database of products nor prices built into this spreadsheet. It is the operator's responsibility to check that all figures are up-to-date.
AND:
It is feasible that in some situations the return on investment will be negative.

In some cases this can be changed by reviewing the pasture renewal process and associated costs.

In some situations, especially when returns are low, the reality is that the return is negative.

Poor performance or rapid pasture reversion may also lead to a negative return.

This spreadsheet is flexible enough to use in a range of situations that make up an overall pasture renewal programme. eg: following a cereal or fodder crop; via spray and cultivation or via one or two sprays.

In a double spray situation, double the chemical and application costs, and if necessary, allow extra feed costs to fill the gap.

The sensitivity analysis is an automatic calculation based on I,000 and 2,000 kgDM/ha more and less than the expected yield of the new pasture. For some of the calculations, if the inputs include “0” or negative values, then no result will be given.

Guidelines/Notes in spreadsheet

Guidelines for pasture yields/yield increase

An improved feed conversion due to average ME increasing from 10.5 to 11.0MJME/kgDM for new cultivars.

Pasture utilisation and conversion to product
These relationships have been fixed within the spreadsheet.
A high level of knowledge and experience would be required to make sensible changes to these variables.
While there may be some variation around the fixed values, it will not make a significant difference to the outcome.

Financial Inputs

Dairy:
Milksolids payout: Best estimate of the average price for the next 5 years

Allowance for costs:
10-20% If no change in stocking rate required to utilise extra feed
20-50% If increased stocking rate required

Sheep, Beef cattle, Deer:
Gross Margin (GM) per stock unit is calculated based on inputs at the bottom of the page

• Capital cost per stock unit is used in the livestock gross margin calculations
• If it is anticipated that the increased returns will come from increased per head performance rather than extra stock, then put the capital cost/su at $0.
• If it is anticipated that half the extra feed will be consumed by extra stock then enter a value 50% of the capital cost/su

Guide to change in relative yield over time (sheep, cattle, deer):
If year 1 = 100%, then it is likely that the pasture yields will slowly deteriorate over time.
The rate will be site- and management-specific.
Factors affecting the rate of decline in production:
- Winter-spring pugging (faster decline)
- Set stocking = faster decline
- The rate of reversion to original species is driven by pre-sowing preparation, weed control, grazing management

eg.

Financial indicators used

• Total investment cost: ($)
• Return from new pasture in Year 1: ($)
• Return on Investment over 5 Years (IRR): (The rate of return calculated by IRR is the interest rate corresponding to a 0 (zero) net present value.)
• Break even in: (No. of years for cumulative return to exceed the investment)

Sensitivity analysis

Notes:
1. If the extra income/ha is negative for all years the return on investment will show as #NUM!
2. If the Payback period shows #DIV/0! then ensure the new pasture yield is NOT rounded to the nearest 1,000 (eg. make it 1,001).
3. If the lower pasture yields are less than the "old pasture" yield, no results will be displayed.

Send us your feedback Your constructive comments and feedback are appreciated and will be considered to update the calculators in future.

PRINTABLE VERSION

TOP