I have a cash projection model in Excel.

I have around 10 years of monthly data.

There are around 10 sources of cash and around 10 uses of cash.

I have determined the type of distribution which best fits each of the 20 series.

I have calculated the pertinent statistical parameters of each of the 20 series (e.g. mean or mode, standard deviation, etc.)

I have fitted the selected distributions with their respective parameters.

Some of the series have a normal distribution around a relatively high mean value.

Some of the series have a more Rayleigh or Weibull distribution - always positive but not symmetrical.

The purpose of the model is to predict the cash position over each of the next 12 months.

In some cases, a series has a growth factor to be included.

Each month, for each series, the current actual value is appended resulting in a new series of actual values.

Then an expected value is calculated for each of the next 12 months.

The question is:

Given a random series with known distribution parameters, what is a good way of generating expected value predictions?

In some cases, it appears that the best prediction is the current value.

However, if there's an outlier in the current value, this doesn't work well.

I have around 10 years of monthly data.

There are around 10 sources of cash and around 10 uses of cash.

I have determined the type of distribution which best fits each of the 20 series.

I have calculated the pertinent statistical parameters of each of the 20 series (e.g. mean or mode, standard deviation, etc.)

I have fitted the selected distributions with their respective parameters.

Some of the series have a normal distribution around a relatively high mean value.

Some of the series have a more Rayleigh or Weibull distribution - always positive but not symmetrical.

The purpose of the model is to predict the cash position over each of the next 12 months.

In some cases, a series has a growth factor to be included.

Each month, for each series, the current actual value is appended resulting in a new series of actual values.

Then an expected value is calculated for each of the next 12 months.

The question is:

Given a random series with known distribution parameters, what is a good way of generating expected value predictions?

In some cases, it appears that the best prediction is the current value.

However, if there's an outlier in the current value, this doesn't work well.

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

Gowflow

Sorry I withdraw

Gowflow

That's always the problem when real life and mathematical modelling collide :)

So you have monthly and annual trends for about 10y worth of real world data and a separate trend for growth in the system (inflation/productivity increases)

It sounds like you have annual modelling already in place provided your monthly data fits witihn a previous pattern so what you could look at is highlighting these outliers and their variance from expectation . You've probably enough data to have a mean and SD for historic monthly totals that could do this.

You can then produce two predictions for the year; one that shows real data but highlights those months that are outliers and variance from expected mean (+ growth) - Range bars are useful for this; a second graph could use the outlier monthy figure as a mean of the outlier and all previous months - again showing variance from expected.

This kind of data profiling is useful where there are external influencers of productivity but are also useful in identifying external pressures but also damp out unexpected variation that can have an undue effect on predicting the rest of the year.

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial