Your Reading List

The Ins And Outs Of Cash Flow Projections

Once you have the cash flow projection worksheet completed, scan across the bottom and look for the largest number — that will be how much operating loan you need

In my last article (March 22 issue) I talked about some of the sources of credit for operating expenses and how to manage them properly. In this article I want to discuss how you determine how much operating credit your farm needs. Most farms I see these days are using a combination of trade (or supplier) credit and an operating loan from a financial institution, so we’ll focus on these only.

The amount of trade credit from crop input suppliers that you need (or can get) is usually pretty easy to figure out — simply multiply acres by the rate of fertilizer and chemical to be applied (or seeding rate for seed) and then multiply that by your price per unit for each product. Some suppliers will figure this out for you on a cost per acre basis and that’s pretty handy. Trade credit may take care of most of the big cost inputs and for some farms there will be enough cash available from sales to cover the balance of the major expenses like fuel, repairs, labor, land rent and so on.

If, however, you are in a tighter cash flow position or are expanding the acres farmed this year, you will likely need more operating credit. The issue of figuring out how much operating loan you may need is more difficult. Often it seems that this year’s operating loan limit is just established based on last year’s. If you think you might need a higher limit and your financial position is strong you can likely get it bumped up, but if your lender thinks you are a bit over leveraged they will likely want to hold your limit the same or even lower it, even if your “gut” tells you you’ll need more operating money.

Although your gut instinct may be pretty accurate, a better approach from a cash flow and farm management perspective is to complete a cash flow projection. Some of the computerized accounting programs include a cash flow module and if you find that works for you and is not too complicated, it can be a good way to go. I tend to use and suggest a standalone spreadsheet approach, especially if you are just starting to do this type of projection, just to keep it simple.


A key tip here though is to make sure the chart of accounts in your accounting system, the cost of production detail in your crop planning spreadsheet or program and your inflow and outflow items in your cash flow projection all “align.” In other words if you are not using an integrated computer program that does all three functions — accounting, cash flow projections and crop planning, then you want to make it as easy as possible to transfer the numbers between these tools by keeping the categories the same.

For example, you may want to have a pretty detailed breakdown of different fertilizer products in your crop planner but only need one roll-up line for fertilizer in your cash flow. But it can cause real alignment problems if, using my example, your chart of accounts in your accounting system lumps fertilizer and chemicals into one account. If you have this issue and your accountant has set up your chart of accounts, I suggest you work with him or her to make some changes that help you get this alignment between your accounting system and your planning tools. A well thought out chart of accounts can support both what you need for management of the farm and what the accountant needs for financial statement and tax preparation.

Setting up the spreadsheet is easy — look at the Excel Screen Capture #1 pictured here to get an idea of how to set up the columns and rows. The screen capture just includes the first few rows and columns.

Simply list your cash inflow and outflow items down the left side using your income and expense statement as a guide for what items need to be included. Using more detail can sometimes make the job easier — for example a line for each crop rather than one line for all crop sales might be good but the level of detail is really a personal preference.

The months of the year go across the top as column headings. I recommend monthly cash flow, as quarterly is usually too long a period to be very useful. When you complete the cash flow projection you are simply taking next year’s budget numbers for the various cash inflow and outflow items and estimating in which month or months those cash inflows and outflows will occur. If you do not already do projections for income and expense this can be the start of that type of planning as well. Remember: this is just about when the actual cash will come into your bank account or leaves your account when you write a cheque, use your debit card or make a loan payment.

I include a column for the previous year’s actual numbers as well to compare those numbers to the projected numbers for next year. It is very easy to be too optimistic with projections both in terms of the amount and the timing; it can take a few years of practice to get good at this so be patient!

If you check out screen capture #1 again you will note that I have added something else to my spreadsheet that can help with both learning not to be too optimistic and doing progress checks or comparisons during the year. What I have added are “Actual” and “Projected” columns for each

month. The idea here is that you fill out the actual column after you get your books done each month and a formula adds the monthly actual into the “Current Year YTD” column on the left (YTD means Year To Date). And yes I know that April and May books will likely not get done until sometime in June (or later) but the exercise of inputting those numbers from your monthly report will give you a much better sense of your actual cash flow compared to your projections and also the impact on your operating loan requirements in future months.

Screen Capture #2 shows that the math at the bottom simply

takes the cash position (negative or positive) from the previous month adds the current month’s inflow, subtracts the current month outflow and provides the new monthly cash flow position. That is the same for both the actual and projected columns and presumably the “Closing Cash/Op Loan” number for the actual column should be pretty close to the real amount from your bank records.

Once you have the cash flow projection worksheet completed, scan across the bottom and look for the largest number –that will be how much operating loan you need. For grain farms I suggest that will usually be in August. Once you know that number then you need to manage accordingly –if your operating loan limit is higher, you are good to go. If not you need to either get a higher limit or look at ways you can speed up inflow, slow down outflow or find alternate sources of operating credit, otherwise bills are likely to go unpaid for a time.

If you are not that handy with Excel but would like to give this a try, just email me and I will send you a copy of the spreadsheet I used in the screen captures above. You can easily customize it for your operation and won’t have to figure out the formulas yourself.

Earl Smith, P. Ag., lives near Sundre, Alta., and is a farm and business consultant in the areas of business management, finance and succession. He’s also past manager of prairie agriculture and agri-business with RBC Royal Bank. Contact Earl at 403-586-2504 or [email protected]

About the author



Stories from our other publications