One of the more detailed parts of doing a cash flow forecast for the next 13 weeks is projecting out the payments on your accounts payable.

I’ve seen some companies go through a very manual process for this. They were able to slug their way through it with the enthusiasm of doing this forecast for the first time. However, when it comes time to do this on a repetitive basis, the process gets very old. What usually happens is the forecast gets updated infrequently at best and often times dropped altogether.

It doesn’t have to be that way. There is a way to streamline this process and turn it into more of a weekly review than a big manual exercise.

The first step is to get a download of the accounts payable trial balances. This download should show the vendors, invoice dates, and invoice amounts and probably invoice numbers for reference.

With that information downloaded into Excel, you can then put together the second leg. This would be a separate table that would list the supplier in the first column and the number of days intended to pay their invoices in the second column.

Going back to the first sheet, we have the download. There would be a column added to the right and in this column you perform a lookup to match up the number of the days to pay that particular supplier. The column after that would show the expected payment date, which is adding the number of days to be paid from the invoice date of that supplier.

Now, you have a database on your accounts payable trial balance that can be summarized in Excel through a pivot table. This pivot table report would show the vendors going down the left-hand side, going across could be the dates probably in week. So you’re showing the 13 weeks going across, plus, perhaps, a future column. Finally, the data in the body of the report will be the amount that will be paid for each week by each supplier. Down at the bottom will be a summary total of the total payments by week.

Now, you might want to make some manual adjustments to this. In order to do that, you would take a copy of the pivot table report and paste that into another sheet, but paste the values (inaudible) pivot table. This will allow you to move numbers around in the particular forecast. So you could slide payments back a week or move them forward as need be in order to balance out the payments or to make adjustments for individual situations as you see fit.

The beauty of this approach is that you now can have a more automated process going forward. Let’s look at what an update would be like. You’d do the same download of the accounts payable trial balance and override the download done from the previous sheet. If you have more data this time, you would then extend the last two columns down to the bottom of your data set. If there have been any new vendors that have been added since the previous download you could add those into a separate table where you listed the payment days, so that you’ll then have a complete lookup. You’d then refresh the pivot table to reflect the update information and you could go through this same process to make a copy and do manual adjustments as need be.

You can see that really cuts the time out of doing an update on a weekly basis. The beauty is you’ll then be more likely to keep up with the cash flow forecast.

Jon Paul, MBA, CPA, CMC, CM&AA

President, Value Added Finance Resources
Bringing new insights on results and maximizing company value