Sales Forecasting Formula

**benno87** · 10-17-2010, 09:25 PM

Hi all
I'm trying to find a sales forecasting formula that I can use that can forecast 12 months into the future and not be an average or 'smoothed'.

I have the attached data and as you can see, the data is all over the place. Some months have had 0 sales, then the next month peak, then peak again, then drop, then peak, then nothing. Basically I'm trying to say there is no attainable pattern to this data, therefore a smoothing technique can't be used because a high month could clear stock out and the next incoming order, won't be enough to fulfill backorders + fulfill regular orders. Therefore, I need a month by month forecast by trying to find a pattern, ie. 2 low months, 1 high, 1 low, 3 high, etc.

Not sure if I'm making sense but there has to be a way of forecasting without using an average or smoothing technique (ie. month's can't be the same figures for every month looking 12 months forwards).

I have attached an example...any help is GREATLY appreciated. I have been hunting around on the net for a while and the formula's that dont confuse the heck out of me aren't doing what I want and the other's are just waaay too confusing.

Thanks a lot!
benno

**MarvinP** · 10-17-2010, 10:09 PM

Hi benno,
The normal techniques for forecasting are to draw lines through your data into the future to see where they would be. You can eyeball it with a ruler or do some math wizzy stuff and do the Least Squares Regression Line. They also have Least Squares Regression Logs and other types of lines.

If you draw a chart with your data and click on the chart and then on chart tools there is a TrendLine feature. Look at this and search the net. I've put a few trendlines in your data and see chart.

I hope that helps you get started. You need to find the Trendline feature of charting in your version of Excel.

I hope this helps.

**ron2k_1** · 10-17-2010, 11:46 PM

Marvin's recommendation is a cool regression analysis tool. However, in reality it is averaging out your whole year of actual sales data.

Maybe we're still not sure what is it that you really want. Further clarification may help. However, if you believe that the sales per product is seasonal and consistent every year. Why would a formula pointing out to the previous year's month of actual data plus a discretionary increase/decrease percentage wouldn't work. In other words. For instance, on N3 insert =B3*1.03 and drag to the right if you are expecting an increase of 3% in sales in the new following year...

NOTE: if you want to try the trend line feature in 2007, After charting your data, select the line of the data you want to find a trend for and then under the Layout tab, in the Analysis group, click Trendline.

Ron

**Sofistikat** · 10-18-2010, 01:59 AM

G'Day Benno87

I've created several models for Sales Forecasting to assist in Production and Distribution Planning, and as MarvinP and Ron2k_1 point out, the aim is to achieve as smooth an outlook as possible, bearing in mind organic demand, Production/Supply Constraints and Lead Times (which will determine your Safety Stock and Re-Order points). If the spikes you've experienced are due to Seasonality, then previous years' data can be used to formulate expected trends during different times of the year. My experience of these types of calculations has been that they are by nature complicated given the various unique factors that businesses have to deal with. If the examples I have weren't proprietary, I would have been happy to post an example of the kind of stuff I've done, particularly a major ERP project I recently completed for an offshore Milling Operation with Production Constraints that were quite challenging.

But very basically, your Safety Stock should equal the expected Demand Quantity during the Lead Time, and your Cyclic Stock is dependent upon your Actual Forecasted Demand.

More detailed information is required around your Ordering Frequency and Patterns to be able to provide a complete answer to your question.