Convert Weekly Forecast to Montly

[cwrm4]

Hello -

I'm building a forecasting tool in Excel for my company that will
generate a weekly forecast based on historical data.I need to be able
to roll-up this forecast up into monthly buckets for budgeting and
long-range planning purposes.

My plan is to evenly distribute the weekly number into the (work) days
of that week, and then roll-up the workday forecast to the respective
months. Does anyone have any code snippets/tricks that will do this?

Thanks, Will

 

[cwrm4]

On the source data worksheet, the rows are products, and the columns
are weeks (id'd by both week start (Monday) and the ISO week number).
Thus each cell in the body of the worksheet represents the demand for a
particular product for a particular week.

On the budget reporting worksheet, the rows are products, and the
columns are months.

So, I need a macro that will update the monthly data from the weekly,
splitting the weeks as needed (with demand for a week assumed to be
even across each day of that week).

For example, for the week of 30 Jan 2007, we have 100 units of demand.
On the monthly spreadsheet, 40 units of this demand needs to be added
to the January total (for 30-31 jan), and 60 units of demand needs to
be added to the February total (for 01-03 Feb).

Thanks,

Will