Forecasting Monthly Sales using 5 years previous data and accounting for holidays.

[dversloot1]

Hello all,

I hope some of you can relate to this pickle I am in at the moment. I'm currently working on a forecasting tool in Excel where I would like to provide a spread of a monthly forecast across the weeks in the month; weekly forecast. On top of this, I would like to account for holidays which would occur in the particular month being forecast.
Each week of the month typically has a different sell through rate as does each day during the week. If holidays always fell on the same day of the week every month, this would be a little easier but because holidays can happen any day of the week each year, forecasting is more challenging.

I've managed to pull full fiscal weeks' sales for a particular month for the past 5 years. ie: Dec 2012 began on a Saturday therefore I pulled sales from the previous sunday (Nov 25th) to Jan 5th, 2013; Fiscal week 53's last date. I did this because I assumed full weeks data would be easier to work with rather than partial weeks.

Can anybody provide me with some guidance or input as to what I should do next in this exercise? I know this really isn't a typical excel topic but thought it could be intriguing to users of this forum.

Example of data:
[TABLE="width: 563"]
<colgroup><col><col><col span="5"><col></colgroup><tbody>[TR]
[TD]%'s[/TD]
[TD][/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Wk 1[/TD]
[TD] [/TD]
[TD]3.98%[/TD]
[TD]4.63%[/TD]
[TD]4.72%[/TD]
[TD]4.08%[/TD]
[TD]4.42%[/TD]
[TD="align: right"]21.83%[/TD]
[/TR]
[TR]
[TD]Wk 2[/TD]
[TD] [/TD]
[TD]4.75%[/TD]
[TD]4.57%[/TD]
[TD]6.18%[/TD]
[TD]4.62%[/TD]
[TD]4.53%[/TD]
[TD="align: right"]24.65%[/TD]
[/TR]
[TR]
[TD]Wk 3[/TD]
[TD] [/TD]
[TD]5.39%[/TD]
[TD]5.40%[/TD]
[TD]5.32%[/TD]
[TD]3.77%[/TD]
[TD]4.91%[/TD]
[TD="align: right"]24.77%[/TD]
[/TR]
[TR]
[TD]Wk 4[/TD]
[TD] [/TD]
[TD]3.97%[/TD]
[TD]4.63%[/TD]
[TD]2.94%[/TD]
[TD]3.49%[/TD]
[TD]2.37%[/TD]
[TD="align: right"]17.39%[/TD]
[/TR]
[TR]
[TD]Wk 5[/TD]
[TD] [/TD]
[TD]2.42%[/TD]
[TD]2.42%[/TD]
[TD]2.28%[/TD]
[TD]2.68%[/TD]
[TD]1.56%[/TD]
[TD="align: right"]11.36%[/TD]
[/TR]
</tbody>[/TABLE]

Percentages represent daily sales as a percentage of the total monthly sales.

I can provide more information if needed.

Dan