This webpage contains a few examples of financial models for agricultural projects. Items to consider in the evaluation are the price and volatility of various agricultural commodities; the productivity of land; the gestation period and pregnancy period of animals that are held to be slaughtered in an abattoir; and the cost structure of a farm including staff costs, costs of equipment, costs of seeds, costs of fertilizer and so forth.
Model of Integrated Farm
The model that you can download below is for a large integrated farm in Africa. The farm employees many people and includes raising a number of different products. The analysis evaluates the effects of increasing productivity of land and expenditures from developing a farm. The analysis also includes the effects of different commodity prices. Part of the above model addresses and abattoir which requires capital expenditures, acquisition of animals and the cost of increasing the size of animals.
Comprehensive Model of an Abattoir Project
Evaluating the risks and returns from an abattoir can be quite difficult because you must evaluate the life cycle of animals including the time to slaughter, the time of gestation and many other factors. A model that includes analysis of the life cycle of animals is available for download below.
In making a model of an abattoir (a slaughterhouse), the following steps can be taken
- Establish the animal balance that works through the gestion and the life cycle of different animal types. It also establishes the amount of animals slaughtered.
- Work through the margin of each animal type using assumptions for animal weight, cost of feed, feed ratios, selling prices and other factors.
- Work through a financial model with capital expenditures revenues and expenses
- Add financing to the model
- Make a nice presentation of the model
An illustration of the various sections of the model is shown in the screenshot below. The screenshot is of the table of contents for the model. You can make these table of contents sheets with the generic macros file.
The summary page or the abbatoir model and the cash flow waterfall is shown below. The page allows you to change the price of various animals and the amount of the feed required. The graph shows the amount of debt required that is affected by the amount of cash equity invested. You should carefully look at the project IRR to evaluate the competitive position of the project. The IRR looks high which should make you ask why others will not enter the market.
The cash flow waterfall that has a lot of MIN and MAX functions including investing equity before debt and then putting in debt up to a commitment level is show below. When the cash flow is positive, the debt is paid before equity.
The annual cash flow that is computed with the SUMIF function is shown in the screenshot below. This is taken from the periodic model.
Establishing Animal Balances
In the case of raising animals for slaughter and purchasing an abattoir, the amount of animals for feeding must be tracked and the amount of animals for slaughter must be established. The screenshot below illustrates the type of table you can create to establish these numbers. Once the inputs for the gestation period, the pregnancy period, the required number of maternal animals and the number for termination, you can use the OFFSET function a lot to find when animals will be born and when they will terminate. The screenshots below illustrate inputs and creating a table of the balance of animals. Note that you could create a vintage table like a vintage table for the asset age if you want to get more detailed with respect to the cost of feeding animals at different ages. In modelling the life cycle I think it is good to separate maternal breeds balance and terminal balance.
The screenshots below illustrate use of the OFFSET function to model the lifespan of animals.
Modelling Capacity Constraints
In addition to modelling the gestion cycle of animals, capacity constraints of the abattoir should be reflected. As with other models that involve capacity limits, the MIN function can be used and deferred balances can be maintained. The screenshot below demonstrates how the capacity constraint can be modelled in an agricultural model.
Modelling Animal Rations and the Cost of Getting Animals Fat
The cost of raising animals depends on the balance of animals, the amount of ration required to make the animals fat and the cost of the feed. There may be different feeding strategies and the percent of hay, corn, soybeans, wheat and other nutrients can change. The screenshot below illustrates some assumptions with respect to rations and modelling costs.
You can get indicative pricing from FRED and from the Pink Data. This demonstrates the variability of prices over time. You can use the commodity price database and the automatic downloads. Alternatively you can use the comprehensive stock price database file. The files for downloading are available for download below.
The screenshots illustrate the type of pricing data and the volatility of the agricultural prices.