Forcasting

[gumby]

I have some call volume data and I would like to forcast the next pay
periods volume.

My table looks like this.

PP AHT Volume ServieL FTE Cost
Cost P C Log FTE Calls per F
1 08:14 36742 58.3% 160.5 $273,157 $7.43 80.70 455
2 08:04 39365 79.7% 158.8 $274,717 $6.98 81.06 486
3 08:21 38957 76.2% 170.0 $274,977 $7.06 87.78 444
4 09:44 32997 58.4% 163.6 $275,432 $8.35 88.67 372
5 08:45 47531 54.6% 158.3 $275,464 $5.80 96.18 494
6 08:13 46734 90.8% 161.0 $275,204 $5.89 100.38 466
7 08:07 43471 89.4% 183.6 $275,269 $6.33 99.12 439


Using the Forcast function in excel for instance with the volume I am
using 8 as my data point to find, then Volume range, then the PP range.
Is this the best way to forcast? Can I forcast with other var.

David

[vandenberg p]

Hello:

Given the data you have it is hard to recommend any forecasting tool.
There is just not much data. (Also is the next to last column really
the log of column 6 (FTE)? What base logs are you using?)

Anyway for "FTE" and "Calls per F" given the data you have you might as
well use the mean (average).

For "Cost" and "log FTE" you might as well use the last one
as the forecast for the next one.

The only data that shows any real variation is "Volume."
Usually for simple time series Exponential Smoothing is
frequently recommend. Here the weight for alpha
is .41 (for Volume). For ServieL and Cost PC the weight for alpha is
..14 and .19 respectively. (These weights minimize the Root
mean square of the last three error terms.)

The alphas for the last two (ServieL and Cost PC)
are small and perhaps not worth fooling with given the absolute
size of the errors. So the best estimate might still be the mean.
That's something you have to decide. These decisions usually
rest on the cost of the error, rather than the absolute size.

If you don't know about exponential smoothing let me warn you that
the description given in Excel help, I believe is generally
correct but the suggestion of the reasonable constants is wrong.
Generally Alpha is the value you use for current information
and 1-alpha is what you use for history. That is in fact what
Excel used if applied to your data. For all of your
data it used an alpha of .7 and and therefore gave history a
value of .3 (This is apparently hardwired into the technique.
It is not generally so. Normally an optimal Alpha is sought.)

I recomputed the alpha's to see what impact it would if you
used a different alpha. Generally if the best fit is an alpha
of zero then you use the mean, if the best is an alpha of
one then you use the last value to forecast next one.
Given that your data is not very variable; and that along
wth the paucity of data means that statistical results
are suspect and may just be an artifact of the particular
data you have.

BTW, exponential smoothing is on the Data Analysis menu in Tools.
(You must have the data analysis toolpak installed)


Pieter Vandenberg


gumby <[email protected]> wrote:
: I have some call volume data and I would like to forcast the next pay
: periods volume.

: My table looks like this.

: PP AHT Volume ServieL FTE Cost
: Cost P C Log FTE Calls per F
: 1 08:14 36742 58.3% 160.5 $273,157 $7.43 80.70 455
: 2 08:04 39365 79.7% 158.8 $274,717 $6.98 81.06 486
: 3 08:21 38957 76.2% 170.0 $274,977 $7.06 87.78 444
: 4 09:44 32997 58.4% 163.6 $275,432 $8.35 88.67 372
: 5 08:45 47531 54.6% 158.3 $275,464 $5.80 96.18 494
: 6 08:13 46734 90.8% 161.0 $275,204 $5.89 100.38 466
: 7 08:07 43471 89.4% 183.6 $275,269 $6.33 99.12 439


: Using the Forcast function in excel for instance with the volume I am
: using 8 as my data point to find, then Volume range, then the PP range.
: Is this the best way to forcast? Can I forcast with other var.

: David