How to Forecast Accounts Receivable

Forecasting accounts receivable is really about understanding collections or how much cash will be collected over a particular period of time.  Forecasting is critical in projecting cash flow and making decisions.

Forecasting collections can be done using various methods each with different confidence levels.  One basic formula for forecasting collections is:  

Beginning accounts receivable + forecasted sales for the month - ending accounts receivable = collections for the month

How one goes about calculating the various components is what needs further discussion.

The easiest and possibly the most accurate method is using days sales in accounts receivable.  By estimating average daily sales and then dividing that into accounts receivable you determine how many days it will take to collect the current accounts receivable.  This then gives you an idea how long it takes to collect an accounts receivable balance.  You can then use this number to estimate ending accounts receivable and collections over longer or shorter periods of time.

With this brief description in mind let’s consider two of the most common methods in more detail.

How to forecast collections on accounts receivable

Method 1: Collections using days sales

Here are the calculations:

Average daily sales = Sales / 360 days

Number of days sales in accounts receivable = Beginning accounts receivable / average daily sales

Estimated ending accounts receivable = estimated sales for the month / 30 days * number of days sales in accounts receivable

Collections = estimated sales for the month + beginning accounts receivable – estimated ending accounts receivable

Example:

Let’s demonstrate with real numbers.

We will start with what we know.

Annual sales are $3,000,000
Beginning accounts receivable is:  $450,000
Estimated sales for next month:  $350,000

Average daily sales:  $3,000,000 / 360 = $8,333 per day
Number of days sales in accounts receivable:  $450,000 / $8,333 = 54 days
Estimated ending accounts receivable:  $350,000 / 30 = $11,667 * 54 = $630,000
Collections = $350,000 + $450,000 - $630,000 = $170,000

Using this method, the estimated sales is the wild card.  The more accurate you estimate sales the more accurate your collection number will be.  Sales can be estimated in various ways.

You could review historical data.  If you are trying to project collections for a month period, review historical sales for the given month.  If sales for that month are consistent year over year, you can have greater confidence in using the same sales estimate.

You could review trend analysis.  Maybe business is growing.  Review the growth rate over the past several months, and if the rate is consistent, apply that same rate to calculate or estimate the next month's sales.

Or, you could just estimate based on other known factors.

However, you estimate sales, you want to be as accurate as you can.  Nothing is ever perfect but with a little thought and analysis you can arrive at a workable number.

Method 2:  Collections using percent of sales

This is the easiest method to use.

By using historical data, you can go back a few years and compare collections to sales for the year by dividing the collection amount by total sales.  This will give you a percentage that could then be applied to estimated sales for the period to determine collections.

Example:

Let’s assume you have sales of $3,000,000 and $2,850,000 respectively over the past two years and you have collected $2,800,000 and $2,750,000 respectively over the past two years.  By dividing $2,800,000 by $3,000,000 = 93% collection rate and by dividing $2,750,000 by $2,850,000 = 96.5% collection rate.  You might then take the average of those two ratios (93 + 96.5 and then divided by 2 to get 94.75%).  The by multiplying 94.75 to the estimated sales amount will estimate your collections for the month.

Why is accounts receivable forecasting important?

Accounts receivable forecasting is important because it has a huge impact on the working capital of your business.  Everyone knows it takes cash to operate a business, but many fails to realize that the timing of cash flow is just as important if not more important.

In the example above it took 54 days to collect accounts receivables, that means from the date you make a sale you won’t be paid for 54 days.  You begin to see the challenge if you have to pay your employees every two weeks.  The first money won’t appear for almost 2 months which puts your business in a bind.  Forecasting accounts receivable and collections is essential to your business.

Why is accounts receivable forecasting difficult?

Accounts receivable forecasting is difficult because it is not an exact science, it relies on estimates and many moving parts.  

If you had 25 customers with open balances in your accounts receivables you are dependent upon 25 different people to pay all of whom operate differently.  Invoices do have due dates, but that doesn’t mean customers will pay on time.  Some pay early, some pay on time, some pay late, and some don’t pay at all.  Each customer has their own demands on their working capital.

Because of these issues forecasting can be challenging at times.  The more historical data you have and the more emphasis you place on collection the more reliable and confidence you can have in your calculations.

Where can I find accounts receivable forecasting software?

The answer to this question is you really can’t find stand alone software that only handles accounts receivable forecasting.  Mid to large companies will have accounting software that has automated reports that can help in forecasting software but for small companies and small startups you are mostly on your own.

Based on the discussion above, accounts receivable forecasting is primarily plugging numbers into a formula to estimate collections.  The formula is not complicated so a good suggestion would be to create a simple template in excel that would help you easily calculate the collection estimate each month.

Even if you don’t have the know-how, someone around you would have the knowledge if you just provided them the formula listed above.

What happens when collection is made on accounts receivable?

Another question people often wonder is what happens when collection is made on accounts receivable?  The answer is very simple.  Your accounts receivable balance decreases by the amount you collected, and your cash increases by the amount you collected.

Every transaction in accounting has 2 sides or 2 entries which ensures everything stays in balance.

The next question people will ask is does collecting a customer accounts receivable affect net income?  The answer to this question is no.  Based on the above explanation the only accounts affected by collecting accounts receivable is the balance sheet.

When you first made the sale transaction you recorded a sale on the income statement and an accounts receivable on the balance sheet.