I'm needing some assistance on the best calculation to use in Excel to forecast daily usage. I know that there are 11 days were usage is approx 30% lighter than the other 19-20 days. I also know that Sunday is lower and Friday is the heaviest.

I've created an allocation matrix that based on days of the week and days of the month. Here's where I'm stumped! I will be given the month totals, but I need to allocate it by day and the monthly allocation needs to total the monthly figure provided. What's the best calculation to forecast the next months activity based on this information? I have the matrix and I've tired numerous calculation based on days of the week and a separate calculation based on days of the month, but overall total does not equal the number provided.

My question for all the forecast experts: is an allocation matrix the best to forecast the daily fluctuations or should I be using another methodology? I don't think a moving average will work b/c it will remove the seasonality that I need to reflect the monthly activity. Any other ideas?

Thanks!

On 21/05/2012 16:13, forecastnovice wrote:
I'm needing some assistance on the best calculation to use in Excel to
forecast daily usage. I know that there are 11 days were usage is
approx 30% lighter than the other 19-20 days. I also know that Sunday
is lower and Friday is the heaviest.

I've created an allocation matrix that based on days of the week and
days of the month. Here's where I'm stumped! I will be given the month
totals, but I need to allocate it by day and the monthly allocation
needs to total the monthly figure provided. What's the best calculation
to forecast the next months activity based on this information? I have
the matrix and I've tired numerous calculation based on days of the week
and a separate calculation based on days of the month, but overall total
does not equal the number provided.

My question for all the forecast experts: is an allocation matrix the
best to forecast the daily fluctuations or should I be using another
methodology? I don't think a moving average will work b/c it will
remove the seasonality that I need to reflect the monthly activity. Any
other ideas?

Thanks!

How many months of training data do you have? Any half decent model must
allow for seasonal monthly variation, day of month and day of week.
Least squares fit your model against your training data and then look at
the residuals for any other systematic factors.

Icecream for instance sales tend to follow the weather strongly. You
will always be lagging the reality if you only use just the last month.

Special cases include that you don't sell stuff on days when you are
closed like National Holidays (or you might sell more if you are one of
the destination places that are open and where people go on such days).

--
Regards,
Martin Brown

--

Thank you Martin for your advice! I really appreciate it!