dversloot1
Board Regular
- Joined
- Apr 3, 2013
- Messages
- 113
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:
<colgroup><col><col><col span="5"><col></colgroup><tbody>
</tbody>
Percentages represent daily sales as a percentage of the total monthly sales.
I can provide more information if needed.
Dan
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:
| %'s | Mon | Tue | Wed | Thu | Fri | Total | |
| Wk 1 | 3.98% | 4.63% | 4.72% | 4.08% | 4.42% | 21.83% | |
| Wk 2 | 4.75% | 4.57% | 6.18% | 4.62% | 4.53% | 24.65% | |
| Wk 3 | 5.39% | 5.40% | 5.32% | 3.77% | 4.91% | 24.77% | |
| Wk 4 | 3.97% | 4.63% | 2.94% | 3.49% | 2.37% | 17.39% | |
| Wk 5 | 2.42% | 2.42% | 2.28% | 2.68% | 1.56% | 11.36% |
<colgroup><col><col><col span="5"><col></colgroup><tbody>
</tbody>
Percentages represent daily sales as a percentage of the total monthly sales.
I can provide more information if needed.
Dan
