Body

The present analysis is based on the sales and price data of Expresso coffee with its competitors. The management of makers of Expresso coffee have collected data regarding its sales and price for the period of march 98 to Nov 2016. It has also collected the data of 8 of its competitors. Expresso coffee analyses its sales volume trend during the period. Further the data also analysis the effect of disease which occurred during Jan-Dec 05 and 12. Moreover, the management of Expresso coffee also wishes to gain information on how the prices of competitors affects its sales volume. Finally, the analysis of the data investigates the impact of muffins (a necessary compliment) on the sales of Expresso Coffee.

The sales volume of Expresso Brand in Tonnes (’00) is shown in figure 1. From the figure it can be seen that there is a continuous rise and fall of the sales volume during the period. In addition there is a fall in sales volume between Mar 07 and Mar 08 (Jelen, 2013).

Figure 1: Sales Volume of Expresso Coffee

Table 1: Trend of the sales Volume

	Coefficients	Standard Error	t Stat	P-value
Intercept	2.793	0.082	34.246	0.000
Trend	0.006	0.001	10.338	0.000

Table 1 above presents the regression coefficients for the sales trend of Expresso Coffee. The sales trend of Expresso coffee can be represented by the Equation:

• Volume of Sales = 2.793 + 0.006*Trend

From the above equation it can be said that volume of sales increase by 0.006*100 tonnes every month (Anderson et al., 2017). In addition the sales volume at the start of the period i.e., feb-98 was 2.793*100 tonnes. Moreover, the coefficients for both the intercept and trend are statistically significant at 0.05 level of significance. 

Table 2: Regression Statistics
Multiple R	0.5692
R Square	0.3240
Adjusted R Square	0.3209
Standard Error	0.6095
Observations	225

From table 2 it is seen that the value of R² is 0.3240 (Babin et al., 2012). Thus 32.40% of the volume of sales can be predicated with the trend line equation.

Table 3: ANOVA
	df	SS	MS	F	Significance F
Regression	1	39.70	39.70	106.865	0.000
Residual	223	82.85	0.37
Total	224	122.56

Table 3 presents the analysis of variance of the trend with sales volume. From table 3 it can be said that at 0.05 level of significance the model is statistically significant, F-value < 0.001.

Thus it can be said that the model is a good fit since the model is statistically significant.

Figure 2: Sales volume of Expresso Coffee

Table 4: Seasonal Average Sales

Month	Average
Jan	3.627202
Feb	3.608981
Mar	3.344271
Apr	3.848122
May	3.512182
Jun	3.302159
Jul	3.486348
Aug	3.361345
Sep	3.169612
Oct	3.258198
Nov	3.503662
Dec	4.309573

Table 4 represents the average sales per month for the period of March 98 to November 2016. From the table it is seen that the average sales for the month of December is the highest. This can be attributed to the fact people consume more coffee in the month of December due to New Year and Christmas.

Q1

	Coefficients	Standard Error	t Stat	P-value
Intercept	-0.791	0.434	-1.821	0.070
Trend	0.007	0.001	11.917	0.000
Seasonal Index	0.036	0.004	8.362	0.000

Table 5 represents the model sales volume taking into consideration the trend and seasonal index. The model can be represented by the equation as

• Sales Volume = 0.007*Trend + 0.036*Seasonal Index – 0.791

Thus the seasonal index remaining constant the sales volume increase by 0.7 Tonnes from the previous month. Further keeping the sales trend constant the sales volume increases by 3.6 tonnes every month from January to December.

Table 6: Regression Statistics
Multiple R	0.6971
R Square	0.4859
Adjusted R Square	0.4813
Standard Error	0.5327
Observations	225

Table 6 presents the regression statistics for the model using trend and seasonal index.  48.59% of the variability in the sales volume can be predicted with the variables trend and seasonal index.

Table 7: ANOVA
	df	SS	MS	F	Significance F
Regression	2	59.55	29.78	104.908	0.000
Residual	222	63.01	0.28
Total	224	122.56

The model using trend and seasonal index is statistically significant at 0.05 level of significance, F-value < 0.001.

Table 8: Regression coefficient of trend with de-seasonal index

	Coefficients	Standard Error	t Stat	P-value
Intercept	2.784	0.072	38.706	0.000
Trend	0.007	0.001	11.855	0.000

Table 8 presents the regression coefficient of trend and de-seasonal index. The equation for the model can be represented as:

• De-seasonal index = 2.784 + 0.007*Trend

Thus, for December 2016 the Trend value is 226

Thus the sales volume = 2.784 + 0.007*226 = 4.366 Tonnes (’00)

From the model using de-seasonal data the predicted sales volume of December 2016 would be 4.366 Tonnes (’00).

Table 9: Regression coefficient for model of diseases

	Coefficients	Standard Error	t Stat	P-value
Intercept	2.790	0.074	37.850	0.000
Trend	0.006	0.001	11.335	0.000
Dummy1	-0.023	0.161	-0.146	0.884
Dummy2	0.112	0.164	0.684	0.494

Table 9 represents the model of sales volume for expresso coffee during the two disease period. The equation for the sales volume cane be written as:

• Sales Volume = 2.790 + 0.006*Trend – 0.023*Dummy1 + 0.112*Dummy2

Where Dummy1 represents the condition of disease of coffee plant during Jan to Dec 2005 and Dummy2 represents the disease during the period Jan-Dec 2012.

From the above model it is seen that keeping the trend of sales constant, due to disease in coffee plants there was a fall in coffee sales during the period of Jan-Dec 2005.

Moreover keeping the sales trend constant there was an increase in sales volume of Expresso coffee during the period Jan-Dec 2012. However the coefficients of sales volume during the disease period is not statistically significant (p-value > 0.05).

Table 10: Regression Statistics
Multiple R	0.6229
R Square	0.3880
Adjusted R Square	0.3796
Standard Error	0.5395
Observations	225

Further from table 10 it is seen that the model of sales trend and disease can predict 38.8% of the variation in sales volume.

Table 11: ANOVA
	df	SS	MS	F	Significance F
Regression	3	40.77	13.59	46.695	0.000
Residual	221	64.32	0.29
Total	224	105.10

At 0.05 level of significance the model is a good fit, F-value < 0.001 (Table 11).

Table 12: Effect of price of Expresso on sales trend

	Coefficients	Standard Error	t Stat	P-value
Intercept	6.111	0.399	15.310	0.000
Trend	0.007	0.000	13.509	0.000
BRU	-0.456	0.054	-8.440	0.000

Table 12 presents the effect of the price of Expresso coffee on the sales volume. The model can be represented as:

• Sales volume = 6.111 + 0.007*Trend -0.456*BRU

The coefficient of trend is statistically significant at 0.05 level (p-value < 0.001).

Q2

From the above equation it is seen that the coefficient for the price of BRU is negative (-0.456). Thus with increase in price of 1MYR/100g of BRU coffee the sales volume would decrease by 0.456 tonnes (’00). In addition the coefficient of price of BRU coffee is statistically significant for the model.

Table 13: Regression Statistics
Multiple R	0.7318
R Square	0.5356
Adjusted R Square	0.5314
Standard Error	0.4689
Observations	225

Table 13 represents the regression statistics for the above model. 53.56% of the variability in the sales volume can be predicted using the above model. In addition at 0.05% level of significance the model is a good fit (F-value < 0.001) (Black, 2016).

Table 14: ANOVA
	df	SS	MS	F	Significance F
Regression	2	56.29	28.14	128.017	0.000
Residual	222	48.81	0.22
Total	224	105.10

Prices of competitor brands were collected and compared to test how they impact the sales volume of Expresso coffee. Backward method was used to select the model. Brands for which the coefficients were not statistically significant were removed from the model. The final model contained Kopi Jawa and Africafe as the only coffee brand which can compete Expresso coffee (Gujarati, 2014).

Table 15: Regression coefficient with competitor brand

	Coefficients	Standard Error	t Stat	P-value
Intercept	3.807	0.559	6.806	0.000
Trend	0.007	0.000	14.691	0.000
BRU	-0.471	0.050	-9.431	0.000
Africafe	0.108	0.054	2.006	0.046
Kopi Jawa	0.228	0.041	5.600	0.000

The final model with the most significant competitor can be represented as:

• Sales Volume = 3.807 + 0.007*Trend – 0.471*BRU + 0.108*Africafe + 0.228*Kopi Jawa

From the above model it can be said that, for Expresso Ceteris Paribus for 1MYR/100g decrease in the price of Kopi Jawa the sales of Expresso coffee would decrease by 0.228 Tonnes (’00). Similarly decrease in the price of Africafe the sales of Expresso Coffee would decrease by 0.108 Tonnes (’00). 

In addition the decrease in sales volume due to reduction in price by Kopi Jawa and Africafe is statistically significant at a = 0.05.

Table 16: Regression Statistics
Multiple R	0.7823
R Square	0.6121
Adjusted R Square	0.6050
Standard Error	0.4305
Observations	225

61.21% of the variability of sales volume of Expresso can be predicted from the above model.

Table 17: ANOVA
	df	SS	MS	F	Significance F
Regression	4	64.32	16.08	86.774	0.000
Residual	220	40.77	0.19
Total	224	105.10

At 0.05 level of significance the model containing the price of the competitor is a good fit, F-value < 0.001 (Table 17).

The prices of the competitors Nescafe, Starbucks, Folgers, Millstone, Gloria Jean’s and Eight o’clock do not affect the sales volume since the coefficient is not statistically significant.

Table 18: Model with muffin

	Coefficients	Standard Error	t Stat	P-value
Intercept	4.031	0.700	5.761	0.000
Trend	0.006	0.000	14.609	0.000
BRU	-0.472	0.050	-9.424	0.000
Africafe	0.110	0.054	2.026	0.044
Kopi Jawa	0.228	0.041	5.575	0.000
Bran’s muffin	-0.028	0.052	-0.535	0.593

Table 18 represents the model with consumers purchasing muffin to have as compliment with coffee. The model can be represented as:

• Sales Volume = 4.031 + 0.006*Trend– 0.471*BRU + 0.110*Africafe + 0.228*Kopi Jawa – 0.028*Bran’s muffin

The above equation can be interpreted as:  with unit increase in price of muffin the sales volume of Expresso coffee would fall by 0.28 Tonnes (’00). Though the prices of muffin would not make a significant impact on the sales volume of Expresso coffee (p-value = 0.593 for coefficient of muffin).

Q3

The hypothesis of the director is true, both muffins and coffee are complimentary items. With increase in prices of muffins there would a fall in the sales volume of coffee.

Table 19: Regression Statistics
Multiple R	0.7827
R Square	0.6126
Adjusted R Square	0.6037
Standard Error	0.4312
Observations	225

The model can predict 61.26% of the variability in the sales volume of Expresso Coffee. In addition the model is a good fit, is statistically significant at 0.05 level, F-value < 0.001 (Table 20). 

Table 20: ANOVA
	df	SS	MS	F	Significance F
Regression	5	64.38	12.88	69.251	0.000
Residual	219	40.72	0.19
Total	224	105.10

The final model can be represented as:

• Sales Volume = 4.031 + 0.006*Trend– 0.471*BRU + 0.110*Africafe + 0.228*Kopi Jawa – 0.028*Bran’s muffin

The above model shows that Africafe and Kopi Jawa are significant competitors for Expresso Coffee. Increase in the prices of both the competitors would have a significant impact of increase in sales volume of Expresso Coffee. Moreover since muffins are an essential compliment with coffee, hence the prices of muffins inversely affect the sales volume.

The organization should keep an eye on the unit price of both Africae and Kopi Jawa Brand of Coffee. In addition, the organization should also look out for the price of muffin, since any rise in the price of muffins would result in lowering of the sales volume.

To ensure that the final model is robust the model is subjected to normality test. The residuals of the model is test for normality using Jarque-Bera and Kolmogorov Smirnov tests.

Jarque-Bera Test

The test statistics is 73.451

The p-value for the test statistics < 0.001

Since the p-value of the Jarque-Bera Test statistics is < 0.05 Thus, the residuals of the data is not normally distributed. Hence, it can be concluded that the predicted values of Expresso Coffee is not normally distributed.

Kolmogorov Smirnov Test

From the Kolmogorov-Smirnov test it is seen that the max of difference < the KS table value, hence the residuals of the model is normally distributed. Thus it can be concluded that the predicted values are normally distributed.

The coefficient of own brand of coffee from the model is -0.472.

The standard error of the coefficient is 0.050.

Thus the test statistics is

At 5% level of significance the p-value < 0.001.

Thus the coefficient of price of own coffee brand is statistically significant. 

Conclusion

The analysis of sales volume shows that there has been a frequent rise and fall of sales of Expresso Coffee. In addition there is no significant impact of bacterial disease of the coffee plant on sales volume of Expresso Coffee. Futher, the price of Expresso coffee significantly impacts the sales volume. The increase in price of coffee results in a fall in sales volume and vice-versa. In addition the analysis of the data shows that the price of Africafe and Kopi Jawa brand has a statistically significant impact on the sales volume of Expresso Coffee. Moreover, the price of muffins also impacts the sales volume of Expresso coffee although not significantly.

Thus, it can be recommended that the management of Expresso Coffee should not only keep an eye on the price of its coffee brand but also the prices of Africafe and Kopi Jawa. Further the management of Expresso Coffee should also be concerned with the price of muffins, since it influences the sales volume.

References

Anderson, D., Sweeney, D., Williams, T., Camm, J. and Cochran, J. (2017). Essentials of Statistics for Business and Economics. 8th ed. Cengage Learning.

Babin, B., Carr, J., Griffin, M. and Zikmund, W. (2012). Business research methods. 9th ed. Cengage Learning.

Black, K. (2016). Business Statistics: For Contemporary Decision Making, 9th Edition: For Contemporary Decision Making. 9th ed. Wiley.

Gujarati, D. (2014). Econometrics by example. 2nd ed. Palgrave Mcmillan.

Jelen, B. (2013). Microsoft Excel 2013: charts and graphs. Que.