Forecasting the sales of a big furniture retailer

Faculty Economics and Business, Amsterdam School of Economics Bachlor Thesis Econometrics

Forecasting the sales of a big furniture retailer

Lanne Hupkens (10358331)

Inhoudsopgave

1 Introduction 3

2 Time series models 5

2.1 Seasonality and trends . . . 5

2.2 Autoregressive models . . . 7

2.3 ARIMA models . . . 8

2.4 The Box-Jenkins approach . . . 9

2.5 Forecast accuracy . . . 10

3 Finding the best model 11 3.1 Data . . . 11

3.2 Detection of seasonal and trend patterns . . . 11

3.3 The ARIMA model for the sales of one store . . . 12

3.4 The ARIMA model for the total sales of the stores in Europe . . . 14

3.5 Accuracy of forecast . . . 14

4 Conclusion 15

Appendix A 17

1 Introduction

Revenue forecasting is an important issue for companies. It is an important step in deter-mining all expected incomes and the expected balanced sheet position (Cassar & Gibson, 2008), and therefore in predicting the firms position on the market. The best way to forecast a company’s sales revenue is to start with the forecast of one single product (Dangerfield & Morris, 1992).

Besides that are sales forecasts important for making marketing and production deci-sions, like production planning, inventory planning, sales budgeting and promotion planning (Danese & Kalchschmidt, 2008). Making decisions out of no accurate forecasts can have consequences for the company. To pessimistic forecasts may lead to reputation damage and losses of income of being out of stock while people still wanted to buy the product (Durand, 2003; Ittner & Larcker, 1998, as cited in Cassar & Gibson, 2008). Higher holding costs or higher costs and higher costs of products still produced while they are no longer needed can be consequences of to optimistic forecasts (Lee & Adam, 1986; Watson 1987, as cited in Cassar & Gibson, 2008).

Most companies use rather judgmental forecasting techniques than statistical forecasting techniques. Klassen and Flores (2001) did a survey at Canadian companies to find out which forecasting procedure is most used among Canadian companies and compared them with US companies. Among the 118 Canadian companies who completed the survey, judgmental techniques were most used, such as in the US. Time series models were only used in 54 of the 118 companies, while the judgmental approaches were used in 87 companies. McCarthey et al. (2006) also did a survey at 68 companies about their use of different forecasting techniques and compare this with the outcomes of the findings of Mentzer and Kahn (1994). Although more companies then in 1994 say to be familiar with qualitative forecast techniques, most of the companies still use quantitative techniques. Out of this survey, McCarthey et al. (2006) find that consumer expectation the most frequently used forecast technique is for a short forecast horizon of less than or equal to 3 months. For a forecast horizon of 4 until 2 years,

a jury of executive opinion is the most used forecasting technique. Both the technique of consumer expectations as the technique of a jury of executive opinion are qualitative forecast methods.

Although most companies use pure judgmental forecasts instead of statistical forecast, pure judgmental forecasts do not always give the best forecasts of sales. Lawrence, O’Connor and Edmundson (2000) investigate if the by the companies used judgmental sales forecasts were more accurate then computer based statistical forecasts. For only 4 of the 13 com-panies the judgmental sales forecast was more accurate than the computer-based forecasts. Therefore it can be more useful to use time series models for forecasting sales than to use a judgmental approach.

In this thesis is determined which forecasting model based on past sales is the best for the prediction of the sales of bookshelves of a big furniture retailer. Sales are determined as the revenue of sale of the product, so as the price multiplied by the amound of the products sold. The best forecasting model is operazionalized as the timeseries model with the best forecast accuracy. It is determined wich model is the best for forecasting the total sales of all stores in Europe of a bookshelve and what the best model is for all the stores in Europe apart. The time series model that is used in this thesis is the autoregressive integrated moving average model. This model is chosen because of its quit simple understanding and approach for estimating the model.

Forecasting with time series models is based on the assumption that a future observation is correlated with past observation of the variable. There are a lot of different time series models. The difficulty of forecasting with time series models is that there are a lot of different models and that in different situations, different models are preferred. The different time series models vary from very complex to quit simple. According to Green and Armstrong (2015) there is a trend going for the use of more complex models instead of simple models. Therefore they investigate simple versus complex models. Green and Armstrong (2015) operationalize simple models as models with an understandable relation between the elements of the model

and with a understandable relation among the models and forecasts. Complex models are operationalized by the opposite of simple models. After comparing different complex with a simple forecast model they conclude that simple models lead to more accurate forecasts.

At first the theory of the ARIMA model is described. Secondly the approach, the Box-Jenkins approach, for estimating the model is described. After that the Box-Box-Jenkins approach is used to find the best model for the forecast of the total sales of a bookshelves in Europe and for the forecast of the sales of a bookshelve for the different stores separately. At last statistical tests will be used to determine wich model gives the best forecast to answer the question which forecasting model is the best for predicting the sales of bookshelves for a big furniture retailer.

2 Time series models

In this section the theory that is used for determining the best forecast model is described. First the importance of detecting seasonality and trends in time series is described and how to detect these. Secondly the ARIMA model is described. Than the Box-Jenkins approach is described and at last is described how to measure the forecast accuracy of a forecast model.

2.1 Seasonality and trends

A basic assumption of a lot of time series forecasting models is that the time series must be stationary. Heij et al. (2004, p. 536) state that the time series yt is stationary if it satisfies the following conditions:

E[yt] = µ (1)

E[(yt µ)2] = 0 (2)

for all k, with 0 and k numbers that do not change over time. So a stationary time series has constant statistical properties over time.

When time series are non-stationary it might be because the data follows a deterministic or a stochastic trend or both. A time series follows a deterministic trend if the mean of the time series increases with the same amount by every step (Heij et al., 2004, p. 579). There is a stochastic trend if there is no clear overall trend direction and the trend direction can not be predicted (Heij et al., 2004, p. 579).

Another property of many time series that causes non-stationarity is a seasonal pattern in the time series. Seasonal patterns of time series are patterns that are repeated over a one-year period and that average out over the long run (Heij et al., 2004, p.604). Seasonal patterns can also be deterministic or stochastic.

A test for detecting trend or seasonal patterns in a time series is the augmented Dickey-Fuller test (ADF test). There are two ways to apply the ADF test. One with a trend term included and one without a trend. If the time series follows a overal trend direction, the trend term should be included and the test equation for this test is (Heij et al., 2004, p. 599):

yt= ↵ + t + ⇢yt 1+ ⇢1 yt 1+ ... + ⇢p 1 yt p+1+ "t (4)

and the null hypothesis of no unit-roots is tested. If the null hypothesis of no unit root is rejected, the time series follows a deterministic trend and is trend stationary. If the null hypothesis is not rejected the time series follows a stochastic trend.

If there is not a overall trend direction one should aply the ADF test without a trend term. The test equation becomes:

yt= ↵ + ⇢yt 1+ ⇢1 yt 1+ ... + ⇢p 1 yt p+1+ "t (5)

. Again the null hypothesis of no unit-roots is tested. If the null hypothesis is rejected, there are unit roots and the time series is not stationary. If the null hypothesis is not rejected, the

data is stationary.

For applying the ADF test the optimal order of p needs to be determined. If not enough lags are included, the test will be biased from remaining serial correlation in the errors of the test equation. If there are to many lags, the test will loose power. It is better to have to much lags, than having to little lags. Harris (1992) proceded the ADF test on Monte Carlo simulations of ARIMA processes and determines the order of lags with Akaike’s Information Criterion (AIC). He finds that with the AIC the correct order of lags is chosen consequently. There are some determinants of sales that do suspect that the time series data of sales are a seasonal process. For example, according to Starr-McCluer (2000) there is a effect of weather on sales. Besides that Wray (1958) concludes that there are seasonal fluctuations in consumer demand of furniture.

2.2 Autoregressive models

A model to forecast stationary time series is the autoregressive model (AR(p)). The standard AR(p) model as described by Heij et al. (2004, p. 537) is:

yt= ↵ + 1yt 1+ 2yt 2+ ... + pyt p+ "t, (6)

with "t white noise. This means that "t has the following properties:

E["t] = 0 (7)

E["2

t] = 2 (8)

E["s"t] = 0 (9)

for all t and t 6= s.

First the value of the integer p has to be determined to estimate the AR-model. There are several statistics that can be used for the determination of p. One of this statistics is the

AIC.

2.3 ARIMA models

The use of ARIMA models to analyse and forecast time series was popularized by Box and Jenkins in the 1970s (Da Veiga et al., 2014). From that time the model is widely used for forecasting different types of time series. According to Da Veiga et al. (2014) this is due to the attractive theoretical properties and the supporting emperical evidence for the ARIMA models. One advantage of the model is that it can be used to stationary processes, but also for unstationary processes. The model is based on the assumption that time series are either stationary or can be made stationary by differencing.

ARIMA models excist of an autoregressive polynomial and a moving avarage polynomial. Every stationary time series process can be approximated with every desirable accuracy by a autoregressive process (AR(p)) and also by a moving average proces (MA(p)) by taking large enough values for p respectively for q (Heij et al., 2004, p. 544). The AR(p) model is given by:

yt= ↵ + 1yt 1 + 2yt 2 + ... + pyt p + "t, t = p + 1, 2, ..., n, (10)

and the MA(q) model is given by:

yt= ↵ + "t+ ✓1"t 1+ ... + ✓q"t q. (11)

A accurate approximation sometimes desires large orders of p or q and hence many parame-ters. If that is the case, it may be more convenient to describe the proces ytby the ratio of the relatively low ordered polynomials (Heij et al., 2004, p. 544). This gives the autoregressive moving average model (ARMA):

with AR polynomial (L) = 1 1L ... pLp and MA polynomial ✓(L) = 1+✓1L+...+✓qLq. "t is the white noise and L is the lag operator with Lyt = yt 1. Such as the AR model and the MA model, the ARMA model can only be used for stationary data. In practice a lot of time series are non-stationary. When the data is indeed non-stationary the time series can be made stationary by differencing the data (Da Veiga et al., 2004). After differencing the model is called the ARIMA(p,q,d) model:

(L)(1 L)dyt= ↵ + ✓(L)"t, (13)

with the property that k_y

t, is non-stationary for all k < d and that dyt is stationary and follows a ARMA process (Heij et al., 2004, p. 580).

2.4 The Box-Jenkins approach

Box and Jenkins developed a strategy for forecasting ARIMA models, the Box-Jenkins ap-proach (Newbold, 1975, p. 397). According to Newbold (1975) the Box-Jenkins apap-proach has three stages. First the identification stage, second the estimation stage and third the diagnostic checking. In the identification stage, first the value of the integer d is chosen. The choise of the value of d is based on the graph of the time series data, by inspecting the graph for any trend or seasonal processes. Then the values of the orders p and q is chosen. For the values of p and q the graphs of the autocorrelation function (ACF) and the partial au-tocorrelation function (PACF) are inspected. The ACF of the ARIMA model is represented by:

⇢k=

E[(yt µ)(yt k µ)]

E[wt µ] (14)

and the kth _{order PACF is represented by}

⇢j = k X i=1

After choosing the values of p, d and q the parameters and ✓ are estimated in the estimation stage. There are different methods for estimating the parameter (Box et al., 1994, cited in De Gooijer & Hyndman). Newbold, Agiakloglou and Miller (1994, cited in De gooijer & Hyndman) tested the accuracy of different methods and recommend the use of full maximum likelihood. Hipel, MCleod & Lennox (1977) say that when there are different competing model the AIC can be used to choose the right model.

In the third stage the estimated model is tested. The residuals of the estimated ARIMA models needs to be homoskedastic, so their variance need to be constant. Besides a constant variance the residuals also need to have a zero mean and no autocorrelation, so the residuals are white noise. The ACF of the residuals can be used to see if the residuals are white noise (Hipel, Mcleod & Lennox, 1977).

2.5 Forecast accuracy

To test the forecast accuracy of a forecasting model it is best to test the forecast accuracy of out of sample forecast rahter than in sample forecast (Tashman, 2000). The reason for this is that the forecasting model is estimated to follow the in-sample observation. Particular nuances of the in-sample data are incorporate in the model. It is expected that the nuances of the out-of-sample data differ some from the in-sample nuances, so that the out of sample nuances are not exactly incorporate into the forecasting model (Tashman, 2000).

There are a lot of different forecast accuracy methods. Three easy appliable measures are the MSE, RMSE and MAPE. The MSE is given by:

M SE = mean((Yt Ft)2) (16)

and the RMSE by:

RM SE =qmean((Yt Ft)2) (17)

by:

M AP E = mean(100(Yt Ft) Yt

). (18)

Unfortunately have these measures some shortcomings to keep in mind when using them. The MAPE has problems when the time series is close to zero or equal to zero. The MAE and RMSE has the disadvantage that it is sensitive for scaling, like logaritmic transformations or differencing (De Gooijer & Hyndman, 2008).

3 Finding the best model

In this section is first the data is described. Secondly the data is tested for stationarity. Then the results of the different stages of the Box-Jenkins approach for finding the best model are described. First for every store separately, than for the total sales of the bookshelve of the stores in Europe.

3.1 Data

To forecast the sales of bookshelves for a big furniture retailer, the weekly data of one specific bookshelve of the retailer is used. The data consists of data of observations of 112 weeks, from 2013 week 36 until 2015 week 42 of six stores in Europe. For estimating the models, the last 12 observation are excluded. This because the first 100 observations are used to estimate the forecasting models and to forecast the last 12 observations. The last 12 observations are then used to test the forecast accuracy of the estimated models.

3.2 Detection of seasonal and trend patterns

The graphs of the time plots of the sales of the different stores are shown in graph 1 until 10 in appendix A and the graph of the total sales is shown in graph 15. It seems like almost all plots follow approximately the same pattern, for almost all stores the sales seems to fluctuate around upward and downward trends. For most of the stores the sales follow a downward

trend from the beginning of 2014 until around week 15 of 2015. From that moment the sales seems to increase again until the beginning of 2015. There are some stores with a different time plot. The plot of store in Brussels does not show any upward or downward trends, but the sales fluctuate around a constant mean. Most of the time plots, also the time plot of the total sales have a very low first value. It looks like this is a mistake in the data, so the first value is dropped from the data. The upward and downward trends in the time plots do suspect nonstationarity and differencing will be needed.

3.3 The ARIMA model for the sales of one store

Figure 9 until 11 in appendix A show the ACFs of the integrated series of the different stores. De ACFs of all the stores, except for the store in Rotterdam cut off after one lag. After one lag, the autocorrelation does not significantly differ from zero. This means that for the stores in Rome, Amsterdam, Berlin, London and Brussels the order of the moving average polynomial is 1. For the store in Rotterdam, the autocorrelations of lag 1, 3 and 4 differ significantly from zero, but the autocorrelation of lag 2 does not. After lag 4 the ACF cuts off and the autocorrelation differ not significantly from zero. Because the autocorrelation of lag 2 does not significantly from zero, but from lag 3 and 4 it does, it is a bit complicated to choose the order of the MA polynomial from this ACF. The order could be 4, but it also could be 1. Therefore the ARIMA model with 1 lag in the moving average polynomial and with 4 lags in the moving average polynomial will both be estimated.

The PACFs of Figure 14 until 19 in appendix A show the PACFs of the integrated sales of the stores. The PACFs of the stores differ more from each other than the ACFs. The PACF of the integrated series of the stores in Rome and Brussels truncate to zero fluently. This means that there are no AR terms necessary in their forecasting models. The PACF of the store in Amsterdam cuts off after one lag, but the autocorrelation of lag 3 differs significantly from zero again. After lag 3 the PACF cuts of again. Therefore an ARIMA model with 1 lag in the AR term and a ARIMA with 3 lags in the AR term will be estimated. The PACF

of the store in Berlin cuts off after two lags and the PACF of the store in London after three lags. So it would be appropriate to choose two an ARIMA model with two lags in the AR equation for the store in Berlin and with three for the store in London. The order of the AR equation of the store in Rotterdam is more difficult to detect. The autocorrelation of lag 1 is big, the autocorrelation of lag 2 does not differ significantly from zero and the third autocorrelation is big again. It also looks like if the partial autocorrelation of lag two and four would be bigger, the PACF would truncate exponential to zero. Because it is too difficult to choose the order of AR terms from the PACF, the ARIMA model would be estimated with no AR term, with one AR therm and with three AR terms as well.

The ACF and PACF indicate that the following models should be estimated. An ARIMA(0,1,1) for the store in Rome, an ARIMA(1,1,1) and an ARIMA(3,1,1) for the store in Amsterdam, an ARIMA(2,1,1) for the store in Berlin and an ARIMA(3, 1,1) for store London. For store in Brussels an ARIMA(0,1,1) should be estimated. Because of the difficulty of interpret-ting the ACF and PACF of the store in Rotterdam there have to be estimated six model for this store, an ARIMA(0,1,1), an ARIMA(0,1,4), an ARIMA(1,1,1), an ARIMA(1,1,4) an ARIMA(3,1,1) and an ARIMA(3,1,4).

For the stores in Amsterdam and Rotterdam the best ARIMA model is chosen by means of their AIC. Both the AIC of the ARIMA(1,1,1) and the AIC of the ARIMA(3,1,1) of the store in Amsterdam are quit big, but that of the ARIMA(1,1,1) is the smallest of the two, so this model is chosen. For the store in Rotterdam the ARIMA(0,1,4) gives the smallest AIC, so this model is chosen.

The ACF of the residuals of the estimated ARIMA models of the different stores are shown in figure 20 until 25. For all stores the autocorrelations do not significantly differ from zero. This means there is no serial correlation left in the residuals of the estimated model and the models are well specified. The models do not need further adjustment.

The following models are chosen to forecast the out-of-sample sales of the different stores. For the store in Rome the ARIMA(0,1,1). For the store in Amsterdam the ARIMA(1,1,1).

For the store in Berlin the ARIMA(2,1,1). For the store in London the ARIMA(3,1,1). For the store in Brussels the ARIMA(0,1,1). For the store in Rotterdam the ARIMA(0,1,4).

3.4 The ARIMA model for the total sales of the stores in Europe

The ACF of the integrated total sales of the bookshelve in the stores in Europe is shown in figure 26. The ACF cuts off after one lag and after that the autocorrelation does not significantly differs from zero. This indicates there must be one MA term in the ARIMA model. The PACF, shown in figure 27 of appendix A, cuts off after three lags. So there need to be three AR terms included in the model. The ACF and PACF indicate an ARIMA(3,1,1) for forecasting the total sales of the bookshelve in Europe.

There is no serial correlation in the residuals after estimating with the ARIMA model. This can be seen in figure 28, which shows the ACF of the residuals. There is no autocor-relation left who differs significantly from zero. Therefor there is no reason to adjust the estimated model.

3.5 Accuracy of forecast

The accuracy of forecast of both the AR(1) model and the ARIMA(1,0,1) model are tested by means of their mean absolute error (MAE), mean absolute percentage error (MAPE) and root mean squared error (RMSE).

Using the estimated ARIMA models for the different stores give the following forecast accuracy measures:

Using the ARIMA(3,1,1) model for forecasting the out-of-sample data of the total of sales of the bookshelves in Europe gives the following measures for forecast accuracy:

When the models for the sales in the different stores are used to forecast the sales in the different stores and than summed up to get the total sales forecast the following accuracy measures are given:

4 Conclusion

The Box-Jenkins approach for finding the best forecasting model for the sales of bookshelves of a big furniture retailer gives the following models. The following models are chosen to fore-cast the out-of-sample sales of the different stores. For the store in Rome the ARIMA(0,1,1). For the store in Amsterdam the ARIMA(1,1,1). For the store in Berlin the ARIMA(2,1,1). For the store in London the ARIMA(3,1,1). For the store in Brussels the ARIMA(0,1,1). For the store in Rotterdam the ARIMA(0,1,4). For the forecast of the total sales the of bookshelve of the stores in Europe the ARIMA(3,1,1) is chosen.

The forecasts of the models of the different stores are not very accurate. The mean absolute percentage error differs from 32% until 60%. So the modelsl would not be very appropriate to use for forecasting in the different stores. The forecasts of the model for the total sales of the bookshelve in the stores in Europe gives more accurate forecasts, with a mean absolute percentage error of 17%. But when you want to forecast the total sales of the bookshelves in Europe it might be better to forecast the sales of the different stores and then sum them up. This gives the most accurate forecasts. This seems a bit strange because the forecast of the sales separately are not accurate.

The weak forecast accuracy can be caused by choosing the wrong order of p and q in the identification stage of the Box-Jenkins approach. Interpretting the ACF & PACF is a subjective choice. Another forecaster may had chosen other orders of p & q which might gave better forecast. For further investigation it might be usefull to look for the best models with another approach.

Another shortcoming of this thesis is that there are not many observations. With not many observations it is difficult to find seasonal trends, even though they were there. If the underlying time series follows a seasonal trend, which is not included in the model, this can be the cause of the low accuracy of the forecast models.

Appendix A

Figure 2: Time plot store Amsterdam

Figure 4: Time plot store London

Figure 6: Time plot store Amsterdam

Figure 8: ACF store Rome

Figure 10: ACF store Berlin

Figure 12: ACF store Brussels

Figure 14: PACF store Rome

Figure 16: PACF store Berlin

Figure 18: PACF store Brussels

Figure 20: AC of the residuals of store Rome

Figure 22: AC of the residuals of store Berlin

Figure 24: AC of the residuals of store Brussels

Figure 26: AC total sales of Europe

References

Cassar, G., & Gibson, B. (2008). Budgets, Internal Reports, and Manager Forecast Accuracy*.Contemporary Accounting Research,25(3), 707-738.

DANESE, P., & KALCHSCHMIDT, M. G. (2008). The Impact of Forecasting on Performances: Is Accuracy the Only Matter?. In XV Working Seminar on Production Economics.

Dangerfield, B. J., & Morris, J. S. (1992). Top-down or bottom-up: Aggregate versus disaggregate extrapolations. International Journal of Forecasting, 8(2), 233-241.

DA VEIGA, C. P., DA VEIGA, C. R. P., CATAPAN, A., TORTATO, U., & DA SILVA, W. V. (2014). Demand Forecasting in Food Retail: A Comparison Between the Holt-Winters and ARIMA

Models. WSEAS Transactions on Business and Economics, 11, 608-614.

De Gooijer, J. G., & Hyndman, R. J. (2006). 25 years of time series forecasting. International journal

of forecasting, 22(3), 443-473.

Green, K. C., & Armstrong, J. S. (2015). Simple versus complex forecasting: The evidence. Journal of

Business Research.

Harris, R. I. (1992). Testing for unit roots using the augmented Dickey-Fuller test: Some issues relating to the size, power and the lag structure of the test.Economics letters, 38(4), 381-386.

Heij, C., De Boer, P., Franses, P. H., Kloek, T., & Van Dijk, H. K. (2004).Econometric methods with

applications in business and economics. OUP Oxford, 536-600.

Hipel, K. W., McLeod, A. I., & Lennox, W. C. (1977). Advances in Box-Jenkins modeling: 1. Model construction. Water Resources Research, 13(3), 567-575.

Klassen, R. D., & Flores, B. E. (2001). Forecasting practices of Canadian firms: Survey results and comparisons. International Journal of Production Economics, 70(2), 163-174.

Lawrence, M., O'Connor, M., & Edmundson, B. (2000). A field study of sales forecasting accuracy and processes. European Journal of Operational Research, 122(1), 151-160.

Newbold, P. (1975). The principles of the Box-Jenkins approach. Operational Research Quarterly, 397-412.

McCarthy, T. M., Davis, D. F., Golicic, S. L., & Mentzer, J. T. (2006). The evolution of sales forecasting management: a 20-year longitudinal study of forecasting practices. Journal of

Forecasting, 25(5), 303-324.

Starr-McCluer, M. (2000). The effects of weather on retail sales (pp. 2000-08). Divisions of Research & Statistics and Monetary Affairs, Federal Reserve Board.

Tashman, L. J. (2000). Out-of-sample tests of forecasting accuracy: an analysis and review. International Journal of Forecasting, 16(4), 437-450.

Wray, M. (1958). Seasonal Demand and Uncertainty in Consumer Goods Industries--Some Case Study Data. The Journal of Industrial Economics, 7(1), 44-65.