Forecasting Sales Figures for the Company That Specializes in the Sale of Central Heating Systems - Assignment Example

Question 1

A company that specializes in the sale of central heating systems wishes to forecast its sales figures for the year 2016.

The sales figures for the last four years are given in the data file (Excel file: “QM Assignment 2 Data 2016”).

• Plot the time series data and describe the pattern exhibited by the data.

(7 marks)

Sales data for the four years beginning in 2012 to 2015 represented in the time series plot above shows that sales are always at their highest in the fourth quarter of each year and their lowest in the second quarter of all the years. Sales decrease from the first to second quarter and start increasing in the third quarter to reach their peak on the fourth.

• Discuss the purpose of moving averages, and calculate an appropriate moving average and centred moving average for this time series data. Include the centred moving averages on your chart in (a).

(10 marks)

By definition, moving averages are used in technical analysis as indicators of a trend. Sales or prices can take three of different trends namely: bullish trends, bearish trends or sideways trends. A bullish trend means that the price or sales are moving in an upward direction, bearish indicate downward movement while sideways show a horizontal movement of sales or price. Thus, moving averages help smooth out the price or in this case, sales, by filtering noise from the sales fluctuations. This smoothing allows easy recognition and understanding of sales trends, and it may aid in the formulation of strategies since support and resistance levels are easily identified. The appropriate moving average for this time series is a four period MA since it encompasses all sales variations in a year.

Year

Quarter

Sales

4-MA

Centered A.

2012

1

100

2

50

90

3

70

95

92.5

4

140

100.5

97.75

2013

1

120

105

102.75

2

72

111

108

3

88

115

113

4

164

121.5

118.25

2014

1

136

127.5

124.5

2

98

131.5

129.5

3

112

136

133.75

4

180

141.5

138.75

2015

1

154

148

144.75

2

120

152.5

150.25

3

138

4

198

• Describe the essential differences between the additive and multiplicative models.
• marks)

In the additive model, a time series is the sum of the trend component, seasonal variations, and a random factor while in the multiplicative model, the time series is a product of the trend, seasonal variations, and the random factor. Therefore, in the additive model, seasonal effects are somehow constant over time while that is not the case for the multiplicative model since the variations increase over time.

• What type of decomposition model should you use here? Why do you think that this model is more appropriate than an alternative one?

(3 marks)

For this case, the additive model of decomposition is more appropriate than the multiplicative model of decomposition. It can be observed from the time series plot that the seasonal variations are relatively constant in the four-year period. Constancy of seasonal variations is one of the determining factors when choosing a decomposition model.

(e) List the steps that you would take to forecast future sales.

(3 marks)

-I would first organize the data so that sales values for the 1st, 2nd, 3rd and 4th quarters for the four years are listed separately.

-I would then calculate the slope (gradient) for each quarter from the data.

- Using the slope and the provided data, I would formulate a linear equation for each quarter.

-I would then use the equations to forecast future quarterly values.

(f) How can you measure the accuracy of a time series model? Discuss, and apply the measures in the context of this set of data.

(21 marks)

There are three ways the accuracy of a time series model can be measured, and they are training and set tests, forecasting accuracy measures and use of time series cross-validation. Training and set tests insist that the accuracy of a time series forecast is dependent on how well the model works on new data rather than how well it fits historical data. The forecast accuracy measure, on the other hand, uses errors between the actual and forecasted data to determine the accuracy of the time series model while time series cross-validation uses different training sets to measure the accuracy of forecasts. For this case, the forecasting accuracy test will be utilized, and the parameters used will be the mean absolute error (MAE), the root mean squared error (RMSE) and the mean absolute percent error (MAPE). The MAE will be calculated by summing all the errors and dividing by sixteen while the MAPE is the sum of the percentage errors divided by the number of quarters.

Using the formulae for linear equations and the methodology described in the previous question, the following equations are derived:

For the 1st quarter: y= 17.8x +83

For the 2nd quarter: y=23.6x + 26

For the 3rd quarter: y= 22.8x + 45

For the fourth quarter: y= 19x + 123

Year

Quarter

Actual Sales

Forecast

Error

Percentage Error (%)

2012

1

100

100.8

-0.8

-0.80

2

50

49.6

0.4

0.80

3

70

67.8

2.2

3.14

4

140

142

-2

-1.43

2013

1

120

118.6

1.4

1.17

2

72

73.2

-1.2

-1.67

3

88

90.6

-2.6

-2.95

4

164

161

3

1.83

2014

1

136

136.4

-0.4

-0.29

2

98

96.8

1.2

1.22

3

112

113.4

-1.4

-1.25

4

180

180

0

0.00

2015

1

154

154.2

-0.2

-0.13

2

120

120.4

-0.4

-0.33

3

138

136.2

1.8

1.30

4

198

199

-1

-0.51

Mean Absolute Error (MAE)

0

Root Mean Squared Error (RMSE)

0

Mean Absolute Percentage Error (MAPE)

0.00625

The results above show that the forecasting method used would give almost the same values as the actual values and produce almost the same errors as the real data.

Question 2

Relationship between Sales and Factors Affecting Sales

The longer a representative is with the company, the higher their sales and this fact can be supported by the moderately high correlation coefficient between sales and the time spent calculated to be 0.623. The positive value indicates that sales increase with the increase in time spent. See graph below.

Market potential includes all possible sales of a good even those sold by a competitor rather than the company. A correlation coefficient of 0.597 in this dimension indicates that when there is a greater market, more sales are made. The relationship is graphically represented by the figure below.

As shown in the graph below, advertising expenditure has a linear relationship with the amount of sales each representative produced. This means that the higher expenditure, the higher the number of sales. However, the correlation between advertisement and sales is only 0.596 calculated by taking the square root of R2, which means that this expenditure is not the only significant factor affecting the number of sales.

From the data, it can be seen that some representatives increased the market share in their regions while others lost some. The reason for this could be due to competition or even reduced market potential among other factors. The graph below accounts for that change in market share and with a correlation coefficient of 0.576, it can be seen that higher sales coincide with larger market shares.

Total workload calculated by multiplying the number of accounts a representative handles, by the weighted average workload per account is significant to the number of sales generated as shown in the figure below. With a correlation coefficient of 0.675, it can be implied that a large workload generates higher sales than a lesser workload.

Discussion

It is evident from the above linear regression that none of the factors affecting sales have a strong direct relationship with sales. Instead of univariate linear regression which does not account for the variations in the data provided, relating the factors to one another in a way that eventually relates back to the sales performance using multivariate regression is more appropriate.

Model Assumptions and Data Analysis

In this analysis, certain assumptions are made:

a) Reduction or increase of market share is a result of advertising efforts or competition rather than an increase or decrease of the entire market potential.

b) The rating is representative of a random factor in the calculations.

c) No one factor is significant enough to have a very strong correlation with sales performance.

All analysis of data is done through linear correlation and interpretation of the correlation coefficient and coefficient of determination.

Model

To evaluate the performance of the representatives, a multivariate linear regression model is the most appropriate, and it is represented by the following equation in this case:

Y=x1 + x2*(x4+ x5) + x3+ (x6*x7) + x8, where;

x1=Time spent with the company

x2=Total market potential

x3= Advertising expenditure

x4= Market share

x5= Change in market share

x6= Number of accounts

x7= Mean workload per account

x8= Rating

The graph below is a result of employing the above equation to the data provided.

R= square root of 0.8423=0.9176=91.78%

By observation, the coefficient of determination and correlation coefficient imply that most of the variability observed in the data is accounted for by the line of best fit. This means that performance, represented by sales Y, is determined by the amount of time a representative has been with the company, the computed total market share, the overall workload and the evaluation of a sales manager. Therefore, the higher the value of the determinant factors, the higher the performance. The sales manager can use the above graph to determine the best performing representative.