Forecasting Analytics Group members: - Arpita - Kapil - Kaushik - Ridhima - Ushhan
Business Problem Forecast daily sales of dairy products (excluding milk) to make a good prediction of future demand, and predict the stock level required to meet the demand. Evaluate different forecasting methods on data distribution and forecast period, and pick the best one based on the results. Dairy Products Ice Lassi Srikhand Cream Cups & Family Family Family Cones Packs Packs Packs Forecasting Analysis Marketing and Communications Council Class of 2013 2
Ice Cream & Gelato – Analysis & Forecast Marketing and Communications Council Class of 2013 3
Ice Cream Sales (2011 – 12) 31 st March A day before Raksha Bnadhan 1 days before Mahavir Jayanti Good Friday Budha Purnima 2 days before Valentine’s day Muharram Gandhi Jayanti Forecasting Analysis Marketing and Communications Council Class of 2013 4
Data Visualization- Sales Distribution Across subclasses Cups & Cones - Monthly Week day seasonality Family Pack - Monthly Forecasting Analysis Marketing and Communications Council Class of 2013 5
Analysis & Forecast – Sales ( Cups and Cones ) Regression Moving Holts ( Single Average Winter Seasonality) Method Method MAPE 0.89 MAPE 0.58 MAPE 0.69 RMSE 13.22 RMSE 16.46 RMSE 8.75 Forecasting Analysis Marketing and Communications Council Class of 2013 6
Analysis & Forecast – Sales ( Cups and Cones - Continued) Regression – Polynomial Trend and Multiple Seasonality ( Weekly and Half Yearly) The Regression Model Input variables Coefficient Std. Error p-value SS Constant term 11.43823338 2.29202032 0.00000094 87293.44531 t 0.07057966 0.02275607 0.0020786 1976.065308 t ^2 -0.00017919 0.00006641 0.00730373 43.97101974 day of week_2 -5.56998158 2.0351541 0.00651356 139.6604157 day of week_3 -5.55328846 2.03511047 0.00667317 196.7801056 day of week_4 -4.37169743 2.04480267 0.03320085 51.11870575 day of week_5 -6.26070452 2.0447247 0.00236676 629.9749756 day of week_6 -3.29685378 2.0446682 0.10775778 89.6808548 day of week_7 -3.32565784 2.04463482 0.10472196 284.4305725 month of year_1 6.26655626 1.61968482 0.00012995 1627.033813 Training Data scoring - Summary Report Total sum of Average RMS Error squared errors Error 38694.54028 10.28215871 -0.0001072 Validation Data scoring - Summary Report Total sum of Average MAPE improved RMS Error MAPE squared errors Error 5471.241832 13.28501682 3.254043135 0.55182345 Forecasting Analysis Marketing and Communications Council Class of 2013 7
Lassi & Srikhand Marketing and Communications Council Class of 2013 8
Data Visualization-Weekly Demand Forecasting Analysis Marketing and Communications Council Class of 2013 9
Daily Quantity Sold Data Forecasting Analysis Marketing and Communications Council Class of 2013 10
Correlation between daily sales ACF Plot for Sum of Quantity_Sold 1 0.5 ACF 0 0 1 2 3 4 5 6 7 -0.5 -1 Lags ACF UCI LCI Next day related to the previous day and a week before Forecasting Analysis Marketing and Communications Council Class of 2013 11
Naïve Forecast ACF Plot for Residual 1 0.5 ACF 0 MAPE 0 1 2 3 4 5 6 7 130% -0.5 -1 Lags ACF UCI LCI Negative correlation between a day's sales and sales previous day? Using Naïve we are just taking previous day and forecasting but it seems lot of signal is not captured It says that naïve forecasting model is not able to explain Forecasting Analysis Marketing and Communications Council Class of 2013 12
Forecast using Multiple Regression Input variables Coefficient Std. Error p-value SS -1936.91626 406.079040 5 0.00000276 203580.109 Constant term 4 0.04776152 0.00991758 0.00000222 7851.68261 Row Labels 7 -2.3526628 3.8025558 0.53654903 4072.53735 Weekday_Mon 4 3.802526 0.01022904 342.777923 Weekday_Sat 9.82291985 6 0 24514.2578 Weekday_Sun 26.75549126 3.82146454 1 4.24133205 3.88327575 0.27556217 456.080169 Weekday_Thu 7 -2.26675916 3.92769527 0.56426156 535.565490 Weekday_Tue 7 4.0734067 3.92513132 0.30015546 373.083984 Weekday_Wed 4 Residual df 321 Multiple R-squared 0.255420386 Std. Dev. estimate 18.61228561 Residual SS 111199.9063 Training Data scoring - Summary Report Total sum of squared RMS Error Average Error MAPE: 133% errors 111199.9052 18.38460315 -6.71529E-06 Validation Data scoring - Summary Report Total sum of squared RMS Error Average Error errors 9126.699565 18.05418389 -11.31025751 Forecasting Analysis Marketing and Communications Council Class of 2013 13
ACF & PACF with Multiple Regression ACF Plot for Residual PACF Plot for Residual 1 1 0.5 0.5 PACF 0 ACF 0 1 2 3 4 5 6 7 0 1 2 3 4 5 6 7 -0.5 -0.5 -1 Lags -1 Lags PACF UCI LCI ACF UCI LCI Regression model tells us that next day’s sales are dependent on last two days’ sales along with regular time component Forecasting Analysis Marketing and Communications Council Class of 2013 14
Smoothing Moving Average(7) Time Plot of Actual Vs Forecast (Training Data) Time Plot of Actual Vs Forecast (Validation Data) 160 70 140 60 Sum of Quantity_Sold Sum of Quantity_Sold 120 50 100 40 80 30 60 20 40 10 20 0 0 Row Labels Row Labels Actual Forecast Actual Forecast Error Measures (Training) Error Measures (Validation) MAPE 128.55637 MAPE 241.73763 MAD 17.43578 MAD 27.321429 MSE 605.26376 MSE 866.89286 Shows strong correlation between days’ sales with last 2 days It also tells that moving average could be used in roll forward manner Forecasting Analysis Marketing and Communications Council Class of 2013 15
Holt Winter Forecasting Method-Additive Error Measures (Training) Error Measures (Validation) MAPE 121.54953 MAPE 56.46093 MAD 15.887538 MAD 18.750176 MSE 494.58883 MSE 367.86672 Forecasting Analysis Marketing and Communications Council Class of 2013 16
Moving Average (MA2) MAPE 48.290598 MAPE 123.82985 MAD 16.5 MAD 16.119131 MSE 308.25 MSE 513.18213 Model Fits quite well but can only forecast for next 1-2 days Forecasting Analysis Marketing and Communications Council Class of 2013 17
Holt Winter Smoothing-Additive Error Measures (Training) Error Measures (Validation) MAPE 125.72335 MAPE 48.290598 MAD 17.082153 MAD 16.5 MSE 577.27479 MSE 308.25 Forecasting Analysis Marketing and Communications Council Class of 2013 18
Conclusions & Suggestions Different in forecasting methods used for effective forecast Recommendations to Business • Stock level on weekly or monthly basis can be predicted for dairy products. • ERP system that could directly tell the vendor how much to deliver • Model is useful to predict for next day given previous two days sales, need to implement roll forward Forecasting Analysis Marketing and Communications Council Class of 2013 19
THANK YOU Marketing and Communications Council Class of 2013 20
Recommend
More recommend
