Forecast Daily Usage

[forecastnovice]

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

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

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

Thanks

 

[Martin Brown]

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).