Forecasting current sales according to ongoing trend and history data

**manchoon** · 09-15-2017, 02:55 AM

Hello everyone, hope you'll be able to help me

I have everyday sales data of this month and I need to forecast daily sales till the end of this month. I have to take into consideration the ongoing trend and also the history sales of the same month of previous year. The solution has to be refreshable, I mean after I refresh the data and new daily sale is added in current month, the forecast should update too.
This is my chart. Blue line is current sales, red one is previous year (the same month) sales. I need blue line to be continued with for example orange line which will show forecasted everyday sales.
ag.PNG

I tried to do it with forecast sheet (from data tab), which does the instant forecast, but it either does linear forecasting which is not correct or I have to indicate seasonality, but I don't have seasonality, it's one month and every day sales are different. It doesn't show prev year sales too. So I guess it won't be useful for me.

Could you please tell me which tool to use or any formula?
Any ideas?

please help
Thank you in advance.

**MrShorty** · 09-15-2017, 01:54 PM

I don't know if anyone here is qualified to tell you which forecasting algorithm is "The One Correct" (TM) algorithm for this kind of forecasting. I am not in business, so I don't know all the details and nuances of this kind of forecasting, but I know enough about regressions and data modeling to know that there are many different algorithms and assumptions and such that go into choosing a forecasting algorithm.

If you already know what forecasting algorithm/strategy you want to use, describe it to us and help us understand the algorithm, then we should be able to help you program that into Excel.

If you don't know what algorithm you want, I would suggest that you take that question to someone more knowledgeable in sales forecasting strategies/algorithms and pose the question to him/her. If it helps:
Wikipedia with a brief description of several different forecasting strategies: https://en.wikipedia.org/wiki/Forecasting
Recent thread that started without knowing what strategy to use and seems to have ended with a polynomial regression type algorithm: https://www.excelforum.com/excel-for...recasting.html
Don't neglect Excel's built in FORECAST() functions. Perhaps one of these has your desired algorithm built in: https://support.office.com/en-us/art...#_forecast.ets

**Lanceh** · 09-15-2017, 04:21 PM

You actually do have the info to model some simple seasonality. All you need to do is create and index of historical sales over a time period and then use that index as a multiplier to get a future forecast. For example, you could calculate a 52 week seasonal profile by dividing each of the last 52 weeks sales by the 52 week average as a whole. You could also do a daily version if you want that level. The tricky part is understanding what seasonality model will best fit your business (weekly, monthly, daily) and then understanding how to shift your historical seasonal index to fit future dates (i.e. when the first of the month shifts from a Friday to a Saturday or when Easter shifts around each year).

Once you find your best seasonal index model, you can multiple that times your preferred historical sales measure (i.e. raw historical sales, some form of average sales, etc.) and generate a future forecast.

I am very familiar with different forecast models but not your business so there's an obvious gap in what to best recommend because I don't know your sales repeatability or stability. As a starter though, it sounds like you should explore a triple exponential smoothing model (3 "buckets" of exponential smoothing are: average volume, trend, and seasonality). You might also research Holt-Winters exponential smoothing. I have a spreadsheet that honestly has a lot of different sales average algorithms in place to generate different forecast models so I can select the best fit. I'd recommend you do some research and replicate the same thing for your business so you can speak to the methodology as well as the numbers. You have sales history and you can calculate simple seasonality with what you have and a simple forecast equals some version of historical sales times expected seasonality. From there, maybe the forum can then help with best formulas for excel, etc.

**Lanceh** · 09-15-2017, 04:31 PM

Here is a very simple starter. You have last year's sales for the entire month. You also have this year's month to date sales as well. I would recommend taking each day's sales last year and dividing that by the daily average for the month last year. That should give you a daily index of last year's sales. You also have this year's sales month to date. You could take an average or running average of this year's sales to date and multiple that by your previously create daily index of last year's sales. Because your index is historical, you should be able to create a simple rest of month forecast based on your current sales trends and replicating last year's index or seasonality. Hope that helps.

Simple example attached to go with above