Rolling up Weekly Forecasts to Monthly - Best Practices?

[illmatic]

Hi all,

I am forecasting sales data on a weekly interval as is common for trend analysis and proper forecasting in my sector. Our sales team refers only to monthly numbers for their tracking. I am currently using a method that uses ratios of sales by day to bring the weekly forecasts to a daily value and then summing that up to create the monthly amount. This way the weekly forecasts (generally) match up with the monthly amounts.

My question is this:

Is there an easier/better way to do this? I am uneasy about using hardcoded ratios for daily sales and am unenthusiastic about having to update those values on a quarterly or annual basis.

Example for ratio inputs to get day values (0=sunday, 6=Saturday)

data weekly_ratio;
	input offset index;
datalines;

0 0.112830357
1 0.12365025
2 0.121380371
3 0.126998287
4 0.133086871
5 0.168109228
6 0.213944636
;
run;

Thanks for your input in advance!

[Reeza]

What's the lowest level of granularity of the data? The seasonality is possibly affected by this type of transformation as it assumes a pattern that may then be interrupted.

[illmatic]

The lowest granularity of data for actual sales is daily. The lowest granularity for forecasted values in weekly, currently.

[PaigeMiller]

A couple of comments

There is no mapping of weekly to monthly that will work in every month/year combination, because months (except Feb) are not integer multiples of the length of a week, which is 7 days. So the only way to do this properly, in my opinion, is to collect the information on a daily basis and roll that up to months.

The averaging of ratios is something to be avoided, as it can be incredibly misleading. The proper way to roll up the ratios is to actually sum the daily numerators, and then divide by the sum of the daily denominators. The only time averaging ratios is a valid method is if all the denominators are equal. If not, I've never seen a case where it was better to simply average the ratios.

[illmatic]

The collection of daily data is no problem, but that only works for actuals. What about forecasted values? Right now I am getting predicted values for each week. That's when we use the ratios to break those predicted values into daily values for the roll-up.

For numerators, are we still speaking of just the actuals then? If that's the case, I agree. And we can use a calendar dataset to make sure the correct denominators are used. Is that what you mean by this?