Forecasting using Excel

[Herbiec09]

Good afternoon All,

I am working for a super market chain and am looking for some Excel/Stats resources to help me understand the different types of forecasting I can perform in Excel, & implement. I am not trying to be a mathematician, just very good in forecasting for a supermarket environment.

Any assistance greatly appreciated.

Thank you

Herbz

 

[Kamolga]

Forecasting is pretty straight forwards as you have perfect historical data. In my case it was a simple linear regression from one year to another, no need of big math. What I did, per rayon, per alley, per department and per store was simply take 3 years of historical data, you add all january and divide them by total of 3 years. You now know that January represents x% of the year. Do same thing for all the months and you can basically predict next September with the figure of July or August. This approach is standard and works pretty well in retail. I applied this approach to weeks actually with some kind of kalendar match up (from a year to another such as Ramadan, easter, big promo folders, etc.). Note that I simply use the forecast formula on years total to predict next year. If you supermarket does not sell only food, be carefull as trends in non-food can be decreasing much faster than actual trends (people to buy on internet, more specialised big stored, etc.)

But my concern was that per store I had more than 500.000 lines of data, so had to go through access to manipulate data for the organisation.

 

[chicagocomputerclasses]

For a start look into FORCAST, LINEST functions & chart trendlines.

 

[Herbiec09]

Forecasting is pretty straight forwards as you have perfect historical data. In my case it was a simple linear regression from one year to another, no need of big math. What I did, per rayon, per alley, per department and per store was simply take 3 years of historical data, you add all january and divide them by total of 3 years. You now know that January represents x% of the year. Do same thing for all the months and you can basically predict next September with the figure of July or August. This approach is standard and works pretty well in retail. I applied this approach to weeks actually with some kind of kalendar match up (from a year to another such as Ramadan, easter, big promo folders, etc.). Note that I simply use the forecast formula on years total to predict next year. If you supermarket does not sell only food, be carefull as trends in non-food can be decreasing much faster than actual trends (people to buy on internet, more specialised big stored, etc.)

But my concern was that per store I had more than 500.000 lines of data, so had to go through access to manipulate data for the organisation.

Thanks Kamolga,

Just so we are on the same page. The process of adding the January's together and dividing by 3 and doing the same for the other months, was to come up with an average for each month by by alley, department and store. This would then allow you to say January is X% of the year's sales? Then use the forecast formula to predict the following year's sales and then calendarise using the split you got earlier?

Were you using the forecast formula on just the annual figures or the month by month numbers?

Would you have a dummy model you can share?

Thanks

 

[Herbiec09]

For a start look into FORCAST, LINEST functions & chart trendlines.

Thank you

 

[Kamolga]

This would then allow you to say January is X% of the year's sales? Then use the forecast formula to predict the following year's sales and then calendarise using the split you got earlier?

Exactly! You can work on last 36 rolling months. -> sorry no model to share, I am on hollidays without any file

Are you forecasting turnover, entring volumes or sold volumes? What is the forecast goal? As an example, in fruits and veg, they might buy by weight, sell per unit and turnover might dépends much more on market price than volumes (so not good to calculate manload requirement).