How to Develop a Cash Flow Forecast - Identifying and Projecting Cash Inflows

Today's will provide a discussion of how to prepare useful projections of cash inflows. Tomorrow's post will look at cash outflows and how the dynamics of business growth itself influences the projections themselves.

Identifying Sources of Cash Inflows

The first step in devising the projections for cash inflows is to identify what the sources of cash inflows are so as not to overlook them when you are preparing your projections. Some sources of revenue for your business could be:

1. Sales of product.
2. Revenue from providing services.
3. Google AdSense revenue.
4. Grant revenue from qualifying government programs.
5. Interest and other investment revenue earned on cash balances.
6. Proceeds received from sales of assets if any disposals are planned in the future.
7. Income tax refund monies.

The last two sources require some comment as you may not have thought of them at the start of your business plan. As your business expands it will be necessary to acquire premises and equipment that are suitable for the volume of business being conducted. Naturally, you will
be left with equipment that is no longer useful and can often be resold to recover a portion of your original investment in it. You will need to take these planned disposals into account in your projections. Also, there may be some years in the initial start-up period where the business will incur tax losses, or this can occur immediately after a period of expansion as the costs of expansion temporarily exceed the revenues. If this occurs and the business has paid taxes in the past, you may be able to recover some of those taxes by carrying a current year loss back and applying it against the year that had income. Here in Canada, our carryback period is three years. These refunds are cash inflows and should definitely be factored into your projections.

Of the seven sources listed above, only the fourth is generally a known amount. The others either have to be estimated, or are dependent on other calculations within the projection. This aspect of preparing the forecast is what makes Excel such an invaluable tool, as you can set up the columns and rows of the projection and fill in detail, and formulas as you go.

Interest income can be estimated by applying an assumed interest rate, which can be looked up online, against your monthly cash balance, as calculated at the bottom of the spreadsheet. What I did with mine is that I simply programmed a formula to take the ending cash balance from the previous month and apply the interest rate to it, prorated for the number of days in the year to the balance to determine how much interest was earned each month. Then I copied the formula horizontally across the spreadsheet, so that the interest revenue could be determined for each monthly period of the projection.

Now I should mention that I used monthly periods simply because I found that it helped me ensure that I didn't overlook anything important in coming up with the year. But you can use any period you like.

Projecting Product Sales or Service Revenue

In order to project revenue from product sales and service revenue, you first have to identify the main driver of those sales or that revenue. The driver will be that factor without which there can be no revenue, or sales and for which the amount of sales will vary in direct proportion to the amount of the driver.

For example, in my business, the main driver is how much inventory I have running on E-bay. I have found that for any given amount of inventory I list, a certain percentage of it will sell each month. I have further found that the percentage is highest when that inventory is fresh, at least in the absence of any marketing effort. A second driver, will indeed be the amount of additional traffic I succeed in driving to my listings by doing consistent marketing.

Another example that may apply to your business is the number of sales leads generated is the main driver, or the amount spent on advertising.

The problem of course for a start-up business is that you may not know what all the drivers are. That is fine because you are going to be refining your projections continuously as your business evolves and you identify new drivers and gain a better understanding of what the numeric relationship between those drivers and sales actually are. But you have to start somewhere.

So initially one of your assumptions that you will list in your assumptions and hypotheses will be what sales level you expect to achieve for a given level of driver, what your initial driver level is going to be and how that driver level will change over the period of the projection.

If you have been operating the business for a period of time, then you will actually have historic data that you can analyze to determine what the relationship between sales and the driver is that will make this task much easier.

Going back to my example, I found that approximately 6% of the dollar value of my inventory will sell in the first month that I list it, and then the sales rate drops to approximately 3% per month. Know that allowed me to build a basic formula for estimating sales each month:

sales for the month = 6% of inventory listed last month + 3% of all inventory listed earlier.

Interestingly, those percentages have been higher since I went full time because my initial projections did not take two factors into account:

1. The effect of my daily marketing efforts in driving traffic to my listings.
2. The effect on the visibility of my listings in E-bay's internal search engine that would come from listing items almost daily (it increases the visibility).

But at least when making the initial projections, I had a formula I could work with that I could copy across to the monthly cells in the spreadsheet.

However, to come up with actual sales numbers I still needed to know how much inventory I could actually expect to list each month in dollars. How do I figure that out? Well I know what the total retail value of my inventory running on-Ebay is, because I can actually obtain that from E-bay. I also know how many lots are running. So I can simply divide the total value by the number of lots to determine what the average lot value is. So the value of inventory lotted each month will be:

value of inventory listed = average lot value x number of lots placed that month.

So now the next thing I have to determine is how many lots I can place in a month. To do this, I spent a day lotting and kept track of when I started, when I stopped and how many lots I actually listed that day. I found that in general, it takes me an average of 8 minutes per listing to scan, describe, upload and note each lot in my inventory spreadsheet. So if I am lotting for say, 5 hours per day, then that is 300 minutes and at 8 minutes per lot, I should list 37 or so items. I can then determine how many items I can lot per month and that will enable me to determine how much inventory I can list per month, and in turn what the sales level is per month.

Now all of that assumes two things:

1. I have an unlimited supply of inventory to list.
2. I am listing (lotting) for the same amount of time each month.

Because I have purchased a large amount of inventory upfront before I went full time, in preparation for my move, the first assumption will hold true until probably January 2017, at which time I will have to start buying more inventory in order to keep expanding my selection. Whether or not you will need to stockpile inventory before starting your business or you can purchase as you need it will depend on how available your inventory is to purchase. For a specialized offering of stamps, it is necessary to accumulate it ahead of time, as you can't typically just go purchase a lot of it.

Until my business reaches the point where I can hire employees, the second assumption will only hold true for while because as sales expand, more and more of my time is going to be spent filling orders, answering questions and otherwise interacting with customers. So in formulating a realistic estimate of sales you will have to take into account your ability to actually service those customers. You may have the inventory, or the sales leads, but lack the actual time or manpower. So one of your expenses at these points in your projections  is going to be hiring employees to help you. That in turn is going to drive a number of estimates associated with the cost of hiring those employees, which I will get into on another post. Like the example I gave you above for listing, you can measure how long it takes to fill or otherwise process an order, determine what the average order value will be and therefore estimate how many orders you will have to fill and how long it will take to do so.

So this hopefully gives you an idea of what you have to consider when formulating sales or revenue projections. What I have given you is very detailed, but if you want potential investors or lenders to give you money to expand your business, you will find it much easier to convince them to do so if your underlying calculations for sales are detailed like this. Most investors understand that some components of the calculations are estimates, but detailing it like this shows that you are thinking methodically, logically and realistically by taking factors that will limit your ability to grow sales into account.

Tomorrow I will look at cash outflows. This is the most detailed section of a cash flow projection and involves the discussion of a number of accounting concepts, so it will have to be dealt with over several posts, but I will start with a general overview and then on subsequent days will discuss individual items in more detail.