BookmarkSubscribeRSS Feed
illmatic
Quartz | Level 8

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!

4 REPLIES 4
Reeza
Super User
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
Quartz | Level 8
The lowest granularity of data for actual sales is daily. The lowest granularity for forecasted values in weekly, currently.
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
illmatic
Quartz | Level 8
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?

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 870 views
  • 0 likes
  • 3 in conversation