QMT 3001 BUSINESS FORECASTING TERM PROJECT

Sales Revenue Forecasting of Tat Company

20.05.2014

DOKUZ EYLUL UNIVERSITY

2009432015 – MERT ALİ BOZKURT
2009432021 – BURAK CANBAY

1. INTRODUCTION
Firstly, we analyzed the quarterly financial reports of Tat Company that shows us sales revenue of quarterly from 2008 to the first quarter of 2014. And then we created our data tables by using annual reports. Our aim is to forecast last three quarters of 2014 based on the sales revenues of the past years.
There are many types of variables that affect the sales of companies like demand, cost, economic and political conditions etc. however we handled macro factors in the economy as inflation rates and gross domestic products.
Most of big companies determine their sales revenue forecast for planning the budgeting using various forecasting models. Companies that do not implement these forecasting models may have some problems about the financial situation in the future.
When we focus on the food sector in Turkey, we see that Turkey has started to be an effective player in the world food and beverage market every passing day. At the same time Turkey is ranked at 7th biggest agriculture country with the 62 billion dollars of agricultural revenue.
Consumers have become more conscious about well-balanced and healthy nutrition. Along with this developments and changes leads to improvement of the food and beverage industry.
Food and Drink Industry Associations of Turkey determine their food and beverage export targets as 40 billion dollars for 2023.
Food industry is the one of the biggest industry in Turkey with about 280 billion dollars share in GDP. Food industry has 40.000 businesses and over 400.000 employees. Food sector has also 4.4 billion dollars foreign trade surplus and showed 7.2 % growth.

2. PRELIMINARY DATA ANALYSIS 3.1. Time Series Plots

Time series plot of shows us that quarterly sales revenues of Tat company from 2008 to first quarter of 2014. When we analyzed, we see that there is seasonality because after passing from 3. quarter to 4. quarter, there is a sharp decrease in the sales revenue every year.

Also when we look at this graph, we can see an linear increasing trend from 2008 to first quarter of 2014.

3.2. Descriptive Statistics

Statistics | SALES REVENUE | N | Valid | 25 | | Missing | 3 | Mean | 185079,486160 | Median | 190347,619000 | Mode | 136218,3850a | Std. Deviation | 26508,0410018 | Range | 91104,6980 | Minimum | 136218,3850 | Maximum | 227323,0830 | a. Multiple modes exist. The smallest value is shown |

The mean of the sales revenue is 185079,486160, the median of sales revenue is 190347,619000, the mode is 136218,3850a, the standard deviation is 26508,0410018. Minimum sales revenue is 136218,3850 and the maximum sales revenue is 227323,0830 in our data set.

3.3. Histograms
This graph shows us normal distribution of sales revenues; also it gives us frequency of our sales revenues. Frequency is less than 2 in the two different intervals. Frequency has become 8 among the mean and 200 millions of revenue. There are also 8 values between the 200 million and over 225 million.

3.4. Box-Plots

This figure shows a box plot for our sales revenue. A line is drawn across the box at the median is 190.347,619. This point is central position for the data set. The line divides data into two sections. The lower edge of the box is the first quartile and the upper edge is the third quartile.

3.5. ACF/PACF Plots
Autocorrelation is the correlation between a variable lagged one or more time periods and itself. In our ACF, successive observations are highly correlated and autocorrelations are significantly different from 0 for the different lags. So there are trend and seasonality.

Partial autocorrelation computes and plots the partial autocorrelations of a time series. Partial autocorrelations, like autocorrelations, are correlations between sets of ordered data pairs of a time series.

3.6. Scatter Plots/Correlations
Correlations

Correlations | | SALES REVENUE | GDP | INFLATION | SALES REVENUE | Pearson Correlation | 1 | ,467* | ,051 | | Sig. (2-tailed) | | ,021 | ,809 | | N | 25 | 24 | 25 | GDP | Pearson Correlation | ,467* | 1 | -,095 | | Sig. (2-tailed) | ,021 | | ,660 | | N | 24 | 24 | 24 | INFLATION | Pearson Correlation | ,051 | -,095 | 1 | | Sig. (2-tailed) | ,809 | ,660 | | | N | 25 | 24 | 25 | *. Correlation is significant at the 0.05 level (2-tailed). |

Correlations | Control Variables | GDP | INFLATION | SALES REVENUE | GDP | Correlation | 1,000 | -,129 | | | Significance (2-tailed) | . | ,557 | | | df | 0 | 21 | | INFLATION | Correlation | -,129 | 1,000 | | | Significance (2-tailed) | ,557 | . | | | df | 21 | 0 |

Sales revenue and Gdp are positively correlated with each other. It means; when Gdp increases, sales revenue increases as well. Sales revenue and inflation are also positively correlated with each other. Gdp and inflation are negatively correlated with each other according to this table. When inflation increases at a separate rate, Gdp decreases.

Scatter Plots

Scatter Plot shows relationship between production of Gdp and inflation. And, we can say their relationship is imperfect, negative and linear.

3. MODEL FITTING 4.7. Moving Averages

Moving average methods generate forecast based on an average of past observations.

MSE MAD TS
638152029,3 22572,07092 1,37962032

Model Summary and Parameter Estimates | Dependent Variable: PMA(SALESREVENUE,4) | Equation | Model Summary | Parameter Estimates | | R Square | F | df1 | df2 | Sig. | Constant | b1 | b2 | b3 | Linear | ,519 | 20,512 | 1 | 19 | ,000 | 161365,723 | 1609,529 | | | Logarithmic | ,643 | 34,230 | 1 | 19 | ,000 | 124577,729 | 23338,560 | | | Inverse | ,721 | 49,027 | 1 | 19 | ,000 | 207449,015 | -265924,606 | | | Cubic | ,778 | 19,905 | 3 | 17 | ,000 | 73681,452 | 20879,394 | -1214,572 | 23,174 | Compound | ,527 | 21,158 | 1 | 19 | ,000 | 161487,178 | 1,009 | | | Power | ,657 | 36,351 | 1 | 19 | ,000 | 131143,327 | ,132 | | | S | ,741 | 54,220 | 1 | 19 | ,000 | 12,252 | -1,506 | | | Growth | ,527 | 21,158 | 1 | 19 | ,000 | 11,992 | ,009 | | | Exponential | ,527 | 21,158 | 1 | 19 | ,000 | 161487,178 | ,009 | | |

When we analyzed R square values, we see that cubic, S and inverse are more suitable for us than the others. Based on these equations, our graph is as follow:

We found that the last three quarter of 2014 in three way as cubic, S and inverse. Those are indicated in the yellow area:

5: INVERSE 6:CUBIC 7:S-CURVE

4.8. Exponential Smoothing

Single Exponential Smoothing (SES)
Exponential smoothing is a procedure for continually revising a forecast in the light of recent experience. The model is often appropriate for data with no predictable upward or downward trend. The aim is to estimate the current level which is then used as the forecast of future values.

Double Exponential Smoothing (Holt’s Method)
Double exponential smoothing method (Holt’s linear exponential smoothing) that allows for evolving local linear trends in a time series and can be used to generate forecast. In this case we have determined the alpha as 0.3 and gamma also as 0.3 to forecast the last three quarters in 2014. These are;
Lag 26 -- 208859,37609
Lag 27 -- 211751,66730
Lag 28 -- 214643,95850 and they are shown as graphically as follow:

Model Statistics | Model | Number of Predictors | Model Fit statistics | Ljung-Box Q(18) | Number of Outliers | | | Stationary R-squared | R-squared | RMSE | MAPE | MAE | MaxAPE | MaxAE | Statistics | DF | Sig. | | SALES REVENUE-Model_1 | 0 | ,863 | ,190 | 24375,626 | 10,89 | 19351,649 | 29,099 | 46599,895 | 29,334 | 16 | ,022 | 0 |

Triple Exponential Smoothing (Winter’s Method)
If a time series data has seasonality, Winter’s method is implemented. We determine the alpha as 0.1 gamma as 0.2 and delta as 0.3 to forecast. In this smoothing model might represent the data better and reduce forecast error. We use this procedure when both trend and seasonality are present, with these two components being either additive or multiplicative. Winters' Method calculates dynamic estimates for three components: level, trend, and seasonal.

Model Statistics | Model | Number of Predictors | Model Fit statistics | Ljung-Box Q(18) | Number of Outliers | | | Stationary R-squared | R-squared | RMSE | MAPE | MAE | MaxAPE | MaxAE | Statistics | DF | Sig. | | Fit for SALESREVENUE from EXSMOOTH, MOD_12 HO A ,30 G ,00-Model_1 | 0 | ,550 | ,933 | 5358,957 | 2,200 | 4183,993 | 5,266 | 9617,612 | 12,881 | 15 | ,612 | 0 |

4.9. Decomposition
One approach to the analysis of time series data involves an attempt to identify the component factors that influence each of the values in a series. This procedure is called decomposition. Each component is identified separately. Projections of each of the component can then be combined to produce forecast of future values of the time series. Decomposition methods are used for both for short-run or long-run forecasting. Our forecasting is short-term because our data consist of last 25 quarters. Decomposition calculates the forecast as the linear regression line multiplied by multiplicative model or added to additive model the seasonal indices.

Seasonal Factors | Series Name: SMEAN(SALESREVENUE) | Period | Seasonal Factor | 1 | 1755,8315364 | 2 | -3433,6126936 | 3 | 20845,9269059 | 4 | -19168,1457486 |

4.10. Regression Analysis
Regression involves the use of more than one independent variable to predict a dependent variable. Most commonly, regression analyzes estimates the conditional expectation of the dependent variable given the independent variable that is the average value of the dependent variable when the independent variables are fixed. Our dependent variable is volume of sales revenue. Our independent variables are inflation rate and gross domestic products through quarterly.

Model Summaryb | Model | R | R Square | Adjusted R Square | Std. Error of the Estimate | 1 | ,475a | ,226 | ,152 | 24058,0630406 | a. Predictors: (Constant), INFLATION, GDP | b. Dependent Variable: SALES REVENUE |

In this table, we can say that Adjusted R Square 15,2% shows us forecasting power of the equation. ANOVAa | Model | Sum of Squares | df | Mean Square | F | Sig. | 1 | Regression | 3541055440,524 | 2 | 1770527720,262 | 3,059 | ,068b | | Residual | 12154598342,596 | 21 | 578790397,266 | | | | Total | 15695653783,119 | 23 | | | | a. Dependent Variable: SALES REVENUE | b. Predictors: (Constant), INFLATION, GDP |

In this Anova table, Sig value is 0,068 and it’s higher than 0,05. So this is not a significant model.

Coefficientsa | Model | Unstandardized Coefficients | Standardized Coefficients | t | Sig. | 95,0% Confidence Interval for B | | B | Std. Error | Beta | | | Lower Bound | Upper Bound | 1 | (Constant) | 114245,888 | 34004,560 | | 3,360 | ,003 | 43529,535 | 184962,242 | | GDP | ,198 | ,080 | ,475 | 2,464 | ,022 | ,031 | ,365 | | INFLATION | 1181,760 | 2626,580 | ,087 | ,450 | ,657 | -4280,512 | 6644,033 | a. Dependent Variable: SALES REVENUE |

According to this Coefficients table, Sig. value of the inflation variable is 0,657>0,05. So we can say that this variable has no significant efficient on the dependent variable. GDP value is 0,022<0,05. So it is a significant independent variable.

4.11. ARIMA
We used ARIMA to model time series behavior and to generate forecasts. ARIMA fits a Box-Jenkins ARIMA model to a time series. ARIMA stands for Autoregressive Integrated Moving Average with each term representing steps taken in the model construction until only random noise remains. ARIMA modeling differs from the other time series methods in the fact that ARIMA modeling uses correlational techniques. ARIMA can be used to model patterns that may not be visible in plotted data.

Model Description | | Model Type | Model ID | SALES REVENUE | Model_1 | ARIMA(1,1,0)(0,1,2) |

Model Statistics | Model | Number of Predictors | Model Fit statistics | Ljung-Box Q(18) | Number of Outliers | | | Stationary R-squared | R-squared | RMSE | MAPE | MAE | MaxAPE | MaxAE | Statistics | DF | Sig. | | SALES REVENUE-Model_1 | 0 | ,428 | -,055 | 25627,787 | 10,295 | 19029,020 | 30,249 | 48387,162 | 15,736 | 15 | ,400 | 0 |

ARIMA(1,1,1)(0,0,0)Model Description | | Model Type | Model ID | SALES REVENUE | Model_1 | ARIMA(1,1,1)(0,0,0) |

Model Statistics | Model | Number of Predictors | Model Fit statistics | Ljung-Box Q(18) | Number of Outliers | | | Stationary R-squared | R-squared | RMSE | MAPE | MAE | MaxAPE | MaxAE | Statistics | DF | Sig. | | SALES REVENUE-Model_1 | 0 | ,530 | ,094 | 25882,575 | 10,878 | 19899,564 | 26,768 | 44467,439 | 23,018 | 16 | ,113 | 0 |

ARIMA(0,0,0)(1,1,1)

Model Description | | Model Type | Model ID | SALES REVENUE | Model_1 | ARIMA(0,0,0)(1,1,1) |

Model Statistics | Model | Number of Predictors | Model Fit statistics | Ljung-Box Q(18) | Number of Outliers | | | Stationary R-squared | R-squared | RMSE | MAPE | MAE | MaxAPE | MaxAE | Statistics | DF | Sig. | | SALES REVENUE-Model_1 | 0 | ,300 | ,177 | 22951,306 | 9,212 | 16792,936 | 23,857 | 51334,204 | 11,003 | 16 | ,809 | 0 |

ARIMA(0,0,1)(2,1,1)

Model Description | | Model Type | Model ID | SALES REVENUE | Model_1 | ARIMA(0,0,1)(2,1,1) |

Model Statistics | Model | Number of Predictors | Model Fit statistics | Ljung-Box Q(18) | Number of Outliers | | | Stationary R-squared | R-squared | RMSE | MAPE | MAE | MaxAPE | MaxAE | Statistics | DF | Sig. | | SALES REVENUE-Model_1 | 0 | ,343 | ,250 | 23238,699 | 9,119 | 16643,777 | 25,985 | 45381,337 | 13,356 | 14 | ,499 | 0 |

ARIMA(0,0,2)(0,1,2)

Model Description | | Model Type | Model ID | SALES REVENUE | Model_1 | ARIMA(0,0,2)(0,1,2) |

Model | Number of Predictors | Model Fit statistics | Ljung-Box Q(18) | Number of Outliers | | | Stationary R-squared | R-squared | RMSE | MAPE | MAE | MaxAPE | MaxAE | Statistics | DF | Sig. | | SALES REVENUE-Model_1 | 0 | ,323 | ,205 | 23917,568 | 9,612 | 17543,375 | 22,728 | 42120,488 | 10,415 | 14 | ,731 | 0 |

We found the lowest MAPE in the fourth model and where is 0 is the number of autoregressive terms, 0 is the number of differences, and 1 is the number of moving average under non-seasonal, 2 is the number of autoregressive terms, 1 is the number of differences, and 1 is the number of moving average under seasonal. So we prefer this ARIMA model.

4. MODEL SELECTION AND FORECASTING

When we analyzed all data and all model, we chose Triple Exponential Smoothing (Winter’s Method). We estimated our company’s forecasting data to last three quarters. Winter’s Method is the most suitable model for our problem. Because the best forecasting results and the lowest MAPE value is provided by using this model. Winter’s three parameter linear and seasonal exponential smoothing method, an extension of Holt’s method, might represent the data better and reduce forecast error. In Winter’s Method one additional equation is used to estimate seasonality.
When we also analyzed our data set, the time series plots table shows us that there are trend and seasonality. We decided to eliminate Moving Average model because it doesn’t handle trend and seasonality very well. In order to choose right model, we analyzed preliminary data analyze and model fitting. Our data are not-stationary and forecasting is difficult with other models.
We analyzed ARIMA models as well and found different MAPE values. Those values are not very big but no one is close to MAPE value of Winter’s Method. So we decided to choose Winter’s Method for our sales revenue forecasting.

5. RESULTS AND DISCUSSION
After analyzing sales revenue data by implementing time series analysis and triple exponential smoothing model. We can say that there is linear increasing trend and also seasonality. From autocorrelation plot we can see our data set is non-stationary data.
Sales revenue and Gdp are positively correlated with each other. It means; when Gdp increases, sales revenue increases as well. Sales revenue and inflation are also positively correlated with each other. Gdp and inflation are negatively correlated with each other according to this table. When inflation increases at a separate rate, Gdp decreases.
We tried to use moving average, exponential smoothing, decomposition, regression, and ARIMA method. Except for Winter’s Method, we found high MAPE in others. That’s why we choose Winter’s Method to reach better result.

Appendices