Home › Forums › General Forums › Tools & Templates › Polynomial Regression – Advice Needed
This topic contains 12 replies, has 4 voices, and was last updated by John Noguera 4 months ago.
Six Sigma Green Belt here, Im trying to develop some kind of statistical model that lets me estimate sales by the end of the month based off of my sales at any given point of time during the month. Ive noticed that sales trend on a polynomial curve consistently increasing rapidly as I move towards the end of the month.
I’ve mapped 4 years of daily sales data against business day of the month to get a sales curve. Ive then plotted a polynomial regression line over my data and used excel solver on the coefficients to get a line with an R squared of .997. Pretty good line.
However, I now realize that all I can do with my equation is predict sales based off of a day or predict working day based off of sales. It does not let me predict sales at the end of the month based of off my sales number at a point in time. I can get an approximation by taking my % increase for a historical point in time to my line apex, but I feel like there are more scientific ways of going about this.
Any advice on any techniques I can employ to achieve my goal?
@tollemg – maybe concentrate more on selling instead of trying to predict sales?
Use Excel 2016 Forecast.ETS (easiest) or Minitab’s Exponential Smoothing or ARIMA.
Minitabâ€™s Exponential Smoothing
Thank you sir, this worked perfectly, should have a significant impact on my weekly forecast accuracy.
@MBBinWI cant sell if we don’t have lead time to set appropriate production schedules to fill demand. Also selling is not my job.
You are welcome. Be sure to check the autocorrelation (ACF) plot on the residuals to ensure that the model is adequate.
Also same assumptions as in a Regression model: residuals should be approximately normal and have equal variance.
Forgot to mention the Ljung-Box test on the residuals is a good complement to the ACF plot.
The smoothing and/or ARIMA may or may not have a significant impact on your ability to make accurate weekly forcasts. In addition to the basic trend line you also need to plot your 95% CI and watch what happens once you are in the realm of forecasting.
The terms in your model might be adequate and thus your prediction limits may be such that the prediction and the error associated with the prediction are good enough to provide some degree of confidence in the forecast. On the other hand, the error associated with the predictions could be so high as to reduce the entire effort to nothing more than expensive science fiction.
Robert, you might be interested in this article by Rob Hyndman:
https://robjhyndman.com/hyndsight/show-me-the-evidence/
Using thousands of data sets from the M3 forecast competition, he shows that the compound hybrid of Exponential Smoothing and ARIMA outperform all commercial forecast tools. Of course “all models are wrong, some are useful” but this is an interesting approach to more useful models!
@jnoguera How does exponential smoothing treat seasonality when there are different time intervals to the seasonality. It seems that I have to designate a seasonality cycle for the model to work over time, however, since my cycle is not always the same amount of business days
What I mean by this is that my “seasonality” is really just a big uptick in sales open orders in the last ~3-5 business days of the month. However, when adjusted for holidays and weekends some months will have 20 business days, some will have 19, some will have 21. Is there any way for my ETS model to account for this? I face the same problem when I try to use seasonality coefficients on working days.
The only real solution I have come up with is to run a separate ETS model on each month since people generally get a day off even if a moving holiday falls on a weekend.
De Livera and Hyndman developed a model for complex seasonality called TBATS:
https://robjhyndman.com/papers/ComplexSeasonality.pdf
Unfortunately (for now) this is only available in R, so I suggest:
Install Base R
Install R Studio
Install the forecast package.
I hope that helps. Let me know if that works for you.
@jnoguera Doesn’t look simple, but there’s no time like the present to learn something new. Thank you for your help, will report back with my success hopefully sooner rather than later.
I agree – this is not simple, but the hard part will be getting familiar with the basics of the R language, and getting the data in (which is why I suggested R Studio).
P.S. If you are successful with TBATS, that will have an impact on our DiscoverSim development plans – p.m. me for details.
© Copyright iSixSigma 2000-2017. User Agreement. Any reproduction or other use of content without the express written consent of iSixSigma is prohibited. More »