1 / 26

Bay Area Operations Management

Statistical Demand Forecasting Methods and tools Alex Rivlin Joe Branham. Bay Area Operations Management. Statistical Demand Forecasting - Agenda. Goal: To achieve. Improved sales out Shorter lead times Reduced inventory Reduced E&O. Statistical Demand Forecasting - Assumptions.

fordon
Download Presentation

Bay Area Operations Management

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Statistical Demand Forecasting Methods and tools Alex Rivlin Joe Branham Bay Area Operations Management

  2. Statistical Demand Forecasting - Agenda Goal: To achieve Improved sales out Shorter lead times Reduced inventory Reduced E&O

  3. Statistical Demand Forecasting - Assumptions Prior to engaging in forecasting :

  4. Statistical Demand Forecasting - Process Effects of lead time Selection of what to NOT forecast How customer use the products Ordering practices

  5. Statistical Demand Forecasting – R project

  6. Statistical Demand Forecasting – R project • Provides a wide variety of statistical functions • linear and nonlinear modelling, • classical statistical tests, • time-series analysis, • classification, • clustering, ... • Easy to implement and integrate with Excel • Good fit for prototyping before investing in commercial packages

  7. Statistical Demand Forecasting - Markets Demand Segmentation Model Type I High Bookings $ Low Variability Type II High Variability 80% of units ~80% of bookings $ $ Annual Bookings Type III Med Bookings $ Low Variability Type V Low Bookings $ High Variability Type IV Low Bookings $ Low Variability 0.75 Lower Higher Coefficient of Variation (std dev/Mean Monthly Demand)

  8. Statistical Demand Forecasting – Time Series Overview of Time-Series Analysis 3. Reported the best fit, and the corresponding error 4. Generated Future Forecast 2. Measured performance over the recent 18 months

  9. Statistical Demand Forecasting – R example • Model: • Consider sales as time trend with random noise component • Lets run regression and review result.

  10. Statistical Demand Forecasting – R example • #------------------------------------------------- • # Read Toyota Sales data from csv file • #------------------------------------------------- • ToyotaDataFrame <- read.table("D:/ToyotaSalesReports/ToyotaSales.csv", • header=TRUE, sep = ","); • #------------------------------------------------------------- • # take first 20 months to use as basis for forecast • #------------------------------------------------------------- • HistoryDF <- ToyotaDataFrame[1:20,] • #------------------------------------------------ • # Build linear regression function vsMonthNumber – trend only • #------------------------------------------------ • myLinearModel <- lm( Toyota.Total~MonthNumber, data=HistoryDF); • Next lets inspect results

  11. Statistical Demand Forecasting – R example • > summary(myLinearModel) • Call: • lm(formula = Toyota.Total ~ MonthNumber, data = HistoryDF) • Residuals: • Min 1Q Median 3Q Max • -27813 -10867 -854 8543 54685 • Coefficients: • Estimate Std. Error t value Pr(>|t|) • (Intercept) 82461.9 8617.6 9.569 1.75e-08 *** • MonthNumber -508.8 719.4 -0.707 0.488 • --- • Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1 • Residual standard error: 18550 on 18 degrees of freedom • Multiple R-squared: 0.02704, Adjusted R-squared: -0.02702 • F-statistic: 0.5002 on 1 and 18 DF, p-value: 0.4885 • > confint(myLinearModel) • 2.5 % 97.5 % • (Intercept) 64356.976 100566.834 • MonthNumber -2020.147 1002.594 • > Aweful

  12. Statistical Demand Forecasting – R example • > #--------------------------------------- • > # Add parameters to improve accuracy • > #---------------------------------------- • > myLinearModel <- lm( Lexus.Total~MonthNumber+Oil.Price+S.P500+unemployment, data=HistoryDF); • > • > summary(myLinearModel); • Call: • lm(formula = Lexus.Total ~ MonthNumber + Oil.Price + S.P500 + • unemployment, data = HistoryDF) • Residuals: • Min 1Q Median 3Q Max • -3761.6 -785.6 -354.6 502.1 5231.6 • Coefficients: • Estimate Std. Error t value Pr(>|t|) • (Intercept) -9194.474 25065.038 -0.367 0.719 • MonthNumber -161.627 213.259 -0.758 0.460 • Oil.Price 52.725 185.820 0.284 0.780 • S.P500 8.512 13.819 0.616 0.547 • unemployment 808.175 3862.237 0.209 0.837 • Residual standard error: 2077 on 15 degrees of freedom • Multiple R-squared: 0.2484, Adjusted R-squared: 0.048 • F-statistic: 1.239 on 4 and 15 DF, p-value: 0.3363 Bad

  13. Statistical Demand Forecasting – R example • > #--------------------------------------- • > # Add dummy variables to improve accuracy • > #---------------------------------------- • > myLinearModel <- lm( Lexus.Total~MonthNumber+Oil.Price+S.P500+unemployment, data=HistoryDF); • > Remember “Cash for clunkers” and Recall?

  14. Statistical Demand Forecasting – R example • > #--------------------------------------- • > # Add more dummy variables to account for Cash for clunkers and recalls • > #---------------------------------------- • myLinearModel<- lm( Lexus.Total~MonthNumber+Oil.Price+ S.P500+unemployment+Insentive+ Recall, data=HistoryDF); • summary(myLinearModel); • Call: • lm(formula = Lexus.Total ~ MonthNumber + Oil.Price + S.P500 + • unemployment + Insentive + Recall, data = HistoryDF) • Coefficients: • Estimate Std. Error t value Pr(>|t|) • (Intercept) -18598.482 21722.763 -0.856 0.4074 • MonthNumber -81.877 195.785 -0.418 0.6826 • Oil.Price 9.099 164.447 0.055 0.9567 • S.P500 7.423 11.705 0.634 0.5370 • unemployment 2161.880 3369.402 0.642 0.5323 • Insentive 2357.164 1825.319 1.291 0.2191 • Recall -3121.510 1345.568 -2.320 0.0373 * • --- • Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1 • Residual standard error: 1756 on 13 degrees of freedom • Multiple R-squared: 0.5348, Adjusted R-squared: 0.3201 • F-statistic: 2.491 on 6 and 13 DF, p-value: 0.07916 Better

  15. Statistical Demand Forecasting – Simple moving average Moving Average performs very badlyon seasonal items • Moving Average • Forecast = Average of last X periods • High X to screen out noise, low X to respond rapidlyto true shifts in the business • Rather arbitrarily gives last X periods equal weight and previous period no weight at all • When to use • Can be a good technique for stable items with no clear trend or seasonality • Will consistently under-forecast items with upward trend • Will consistently over-forecast items with downward trend • Performs particularly badly on seasonal items(forecasts high when demand is low and low when demand is high; see example overleaf)

  16. Statistical Demand Forecasting – Exponential smoothing • Formulas • Level(t) = Alpha * Actual(t) + (1-Alpha) * Level(t-1) • Level(1) = Actual(1) • Forecast(t+n) = Level(t) • Parameters • “Alpha” is the weight put on the most recent data point • High “alpha” rapidly responds to demand changes but is susceptible to noise • When to use • Stable items with no clear trend or seasonality • Will consistently under- / over-forecast items with upward / downward trend

  17. Statistical Demand Forecasting – Exponential smoothing Performs poorly in case of trend

  18. Statistical Demand Forecasting – Holt’s method • Formulas • Level(t) = Alpha*Actual(t) + (1-Alpha)*[Level(t-1) + Trend(t-1)] • Trend(t) = Beta*[Level(t)-Level(t-1)] + (1-Beta)*Trend(t-1) • Forecast(t+n) = Level(t) + n*Trend(t) • Parameters • “Alpha” and “beta” are the weights put on the most recent data point for purposes of calculating level and trend • High alpha and beta respond rapidly to demand changes but are susceptible to noise • If data fairly noisy: recommend alpha = 0.2 and beta=0.1 • When to use • Items with clear trend

  19. Statistical Demand Forecasting – Exponential smoothing Holt’s Method gives big improvementson growing and declining items…

  20. Statistical Demand Forecasting – Holt’s Winter’s • Formulas • Similar to Holt’s Method, but with a seasonal multiplier for each month • Parameters • “Alpha” and “beta” are the weights put on the most recent data point for purposes of calculating level and trend • “Gamma” is the weight put on the most recent year for purpose of calculating seasonal profile • When to use • Items with clear seasonality or seasonality and trend • Function HoltWinters() generates the model • http://svn.r-project.org/R/trunk/src/library/stats/R/HoltWinters.R

  21. Statistical Demand Forecasting – Holt’s Winters Winters’ model gives big improvementson seasonal items…

  22. Statistical Demand Forecasting – Seasonal decomposition

  23. Statistical Demand Forecasting – Seasonal Decomposition Step 1 – Sales ratio Sales Ratio is ratio of demand in current week to average demand for the 13 weeks period centered around current week Detailed explanations at http://www.exceluser.com/solutions/seasonality-sales.htm

  24. Statistical Demand Forecasting – Seasonal Decomposition Step 2 –Seasonal index Seasonal Index is average of Sales Ratios for the given week. Formula : = AVERAGE(IF(WW_in_quarter=A7,SalesRatio)) Detailed explanations at http://www.exceluser.com/solutions/seasonality-sales.htm

  25. Statistical Demand Forecasting – Seasonal Decomposition Step 3 – underlying trend To calculate underlying trend divide actual value by seasonal index Formula: =J9/INDEX(SeasonalIndex,'4.Actuals, sales ratio'!F9) Detailed explanations at http://www.exceluser.com/solutions/seasonality-sales.htm

  26. Statistical Demand Forecasting – Seasonal Decomposition Step 4 – generate forecast Extend the line and apply seasonal factor yi= k * xi + b – where k and b come from linear regression of de-seasonalized data Forecast = yi * SFi where y comes from the straight line and SF is seasonal factor. You can download the file from Bay Area Operations Management Detailed explanations at http://www.exceluser.com/solutions/seasonality-sales.htm

More Related