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!