Cash Flow Forecasting Excel File
The Egg Module provides a cash flow forecasting model which helps to analyze the profit potential and capital requirements of a small scale poultry projects.
Please Download the Excel File by Clicking on the Link Below
EggModule_cashflow_USD_0109.xls
Begin With the Survey Sheet
To start please fill out row 2 on the "Survey Responses" sheet. The most important questions are those regarding the Market Conditions: prices for eggs, broilers and feed. Respond with the high and low prices over the past three months.
The Summary Sheet to Gives You an Overview of Project
Click on image above to enlarge.
The most important outputs from the model are the Cumulative Cash Flow (CF) and the Capital Investment. The Cumulative CF, column C, shows the net impact of the projects cash flow; it is critical that the Egg Module be a source of cash for the village. The Capital Investment, column I is the amount of money needed to start operations. This figure includes the construction costs, other one-time costs, and operating expenditures such as feed costs.
Design Experiment Cash Flow Forecasting
The economic goal of an Egg Module project is to generate a positive cash flow to help our partners become economically self-sustaining. We realize there is volatility in the feed and egg prices, which is why we have used a design experiment approach.
The Egg Module uses a cash flow forecasting model that was created using a mathematical approach based in design experiment methodology. This allows for the testing of optimistic and pessimistic scenarios and gives a better prediction of performance under volatile market conditions.
Within the cash flow model there
are two main factors: the
cost of the feed and the price of eggs. By using high and low data
points our design tests seven different cash flow scenarios, as shown
on the table
below.
Average Average
Low Low
High Low
Very High Low
Low High
High High
Very High High
Variables
Feed costs are determined by taking the price of feed multiplied by the assumed consumption per bird, per week. The feed prices and consumption rates have three variable levels throughout the life of the bird, as younger birds consume less feed than older birds, and prices for different feed formulas may change.
Egg prices are
assumed to be the market price in the area.
In addition to feed costs and egg prices, our background research also identifies other key factors, which are listed below. The values assigned to these data points are derived from our firsthand research done in conjunction with the village.
2. Yield Loss
3. Initial Construction Costs
4. Feed Consumption (per bird)
5. Number of Starter Chickens
6. Starter Chicken Costs
7. Vaccination Costs
8. Labor
9. Supplies
10. Transportation
11. Savings*
*This is a disciplined savings program that is intended to be put aside for other projects within the village, additional investment in the operation, or philanthropic projects outside the village.
Cash Flow Model Assumptions
Our cash flow model makes a few material assumptions as listed below.
2. No consumption factor by the village was factored into the analysis.
3. A typical layer, in conditions of the Egg Module, will produce 4 or 5 eggs per week, averaged over its lifetime. Actual performance will decline with age. See Appendix (Layer Performance Graph, Page 32).
4. The layers should be sold once cost of feed for the hens amounts to more than the revenue produced in eggs, typically occurring at about 80 weeks.
5. The analysis does not include the proceeds from the sale of the flock as broilers, occurring at the end of their productive life. These funds should be used to purchase new day-old chicks, and the remaining balance can provide an additional windfall profit.
Financial Projection Summary
This figure is important because if the
village does not have adequate funds to purchase feed or veterinary
care, then the long-term performance of the flock could be compromised.
It is imperative to keep the following factors in mind:
2. Feed quality can not be sacrificed. Long-term, it would be wise to investigate methods for creating feed supplements. (For example, sunflower mash has excellent potential to be used as a feed component and would dramatically reduce the feed costs.)
3. Theft and excessive yield loss are major risks to the operation.
If the project is feasible, the next step is to customize the business plan for the specific village. Building size, location, design, and construction materials should also be specified at this stage. Other critical success factors include the supply of chicks, feed, vaccinations, and training of on-site management. Finally, the path to market needs to be determined and price assumptions should be verified. The customized business plan will give each village a reasonable estimate of egg production output, as well as capital requirements and profit potential.