Cash Flow Forecasting

[JBG2007]

Hi all:

I need some help. What I am trying to do is take cash receipts and payables data from 1/1/2007 - 4/11/2008 (by week, period ending Friday of each week) and forecast what our weekly cash flow will be going forward, using the historical data as a baseline. Averaging doesn't work as some of the expenses are only paid once a month, so I'm looking for a function that will help ascertain that information. Anyone? anyone? Bueller?

Also, I'm taking the cash receipts data based on our average aging value of 44 days.

I would appreciate any assistance that can be provided. I'm sure it's been done by someone before but after trying to figure this out for a couple days my blinders are very likely on and I could use a fresh set of eyes. haha

Thanks, JBG

[Richard Buttrey]

Hi all:

I need some help. What I am trying to do is take cash receipts and payables data from 1/1/2007 - 4/11/2008 (by week, period ending Friday of each week) and forecast what our weekly cash flow will be going forward, using the historical data as a baseline. Averaging doesn't work as some of the expenses are only paid once a month, so I'm looking for a function that will help ascertain that information. Anyone? anyone? Bueller?

Also, I'm taking the cash receipts data based on our average aging value of 44 days.

I would appreciate any assistance that can be provided. I'm sure it's been done by someone before but after trying to figure this out for a couple days my blinders are very likely on and I could use a fresh set of eyes. haha

Thanks, JBG

We, or to be precise I, don't think we have enough information to help at the moment. As a minimum there must be some algorithm on which to base projections. What is your data? Is it seasonally sensitive, related to any other time series, dependent on rainfall, or what?

Even so are you sure there is any merit in a formulaic projection? What about shocks to the underlying model? Or to paraphrase Rumsfeld,

Are there known unknowns? That is to say are there some things we do not know. Are there also unknown unknowns, the ones we don't know we don't know?

If you can come up with some rules/algorithm then we can model it.


Rgds

[JBG2007]

I'm totally cracking up about the "known unknowns". That is an oldie but a goodie!!

Let me sanitize the data tomorrow morning and I'll provide an example that will hopefully bolster my explanation!

Thanks for the reply!

JBG

[JBG2007]

Here's the sanitized data. Sorry, I got pulled away on another fire drill task...to reduce the file size I broke the links between the main forecast page and the the historical A/P data so hopefully, it is somewhat helpful.

The latter is a messy cut and paste of some pivot table data gathered from our A/P distribution report. This pivot table took the base data and grouped it by the categories you see (row data) with corresponding column data relating to dates when the items were paid.

I further grouped that data by date - the Friday of each week in the analysis period) and assigned a week number based on its position in the month (e.g. - items paid from Saturday, January 6 - Friday, January 12, 2007 were assigned to week #2 for that month). After that, I took the average of all the "week 1's" and so forth in the period for each category and plugged it into the lower (cash paid out) section of the forecast. I then reviewed the data to see if it made sense and made some adjustments (see the "backouts" and "addbacks" noted below the A/P historical data averages). I'm making some final revisions to the expected payroll expenses, but same methodology.

Why I was hoping to find an easier way is because it seems like a *lot* of work to get to what I want. I think I'm pretty decent with Excel (having used it for many years) but this stumped me. Maybe it's also the fact that I haven't done a cash flow projection in a while and I'm very out of practice.

On the receipts side, I'm just taking cash receipts data, assigning an "assumed date of sale" (using the receipt date less 44 days to make a best guess) to determine historical patterns for two groups - 1) our top ten customers and 2) all other customers (overall a/r aging for both groups is also 44 days). Then, what I want to do is take that data and project expected receipts (assuming like sales patterns for this iteration) by week through the end of 2008.

And, to answer your questions:

question:

We, or to be precise I, don't think we have enough information to help at the moment. As a minimum there must be some algorithm on which to base projections. What is your data? Is it seasonally sensitive, related to any other time series, dependent on rainfall, or what?

answer:

The data is historical from 1/1/2007 - 3/31/2008. It is not, to my knowledge seasonally sensitive, but there are expenses that occur with some regularity (as in employee benefit payments, and the Corporate VISA card, that are paid at the beginning of each month). I am trying to forecast cyclical weekly cash inflows and outflows, if that helps.

question:

Even so are you sure there is any merit in a formulaic projection? What about shocks to the underlying model? Or to paraphrase Rumsfeld,

Are there known unknowns? That is to say are there some things we do not know. Are there also unknown unknowns, the ones we don't know we don't know?

answer:

The only merit I could think of is one of being able to forecast expenses more clearly, but maybe the best way is what I did (not sure of that by any means!) and take the data and organize it by date in pivot table form.


Completely confused yet? Boy, this project has taught me to improve my skills in determining project completion time (take how long you think you'll need to get an answer to your boss and multiply it by 4, a la Scotty in Star Trek 3).

Thanks, JBG