Data Analysis And Regression Models In Retailing Company

Summarizing the distribution of profits of the twenty branches and comment on the results

1.a). The mean of the profits was 11.729 (£000s) and standard error was 2.959 (£000s). Median of the distribution was 6.8 (£000s). A positive value of 0.593 of kurtosis indicated that profits were accumulated in right hand tail of the curve and the curve was leptokurtic. The measure of skewness was 1.237 and it indicated that right tail of the profit curve was greater than that of the left tail of the curve. Profits were all ranged between 2.69 (£000s) and 42.13 (£000s). The 95% confidence interval was [5.536, 17.922] (figures in (£000s)). (Table 1 in appendix contains the excel calculations).

b). The population mean was hypothesized as 78 lines and one sample t-test was used to test the difference between sample mean of 100.5 lines and hypothesized population mean. The value of the t-statistic was 2.596 where critical t-value was 1.729 for 95% level of significance. Hence the sample mean indicated that number of lines per store was significantly different from 78. (Excel tabular values in Table 2 of appendix)

c). The given data was divided into two groups. Sales for the first group were less than £150,000 and greater than £150,000 for the second group of data (Table 3 of appendix). Comparison between two groups was performed by t-test, where unequal variance for the two data groups was assumed. The value of the t-statistic was -4.102 for one tail test and lied in the critical region. Hence profits of sales group above £150,000 were significantly higher than less than £150,000 sales group (t-test calculation in table 4 of appendix)

d). Confidence interval of 99% was [3.263, 20.195] (table 5 in appendix). The confidence interval indicated 99% chance of the profits of the population data to lie in the range of 3.263 (£000s) and 20.195 (£000s). The sample data had 8 branches with profit figure outside the confidence interval. Irregular nature of the sample profits was evident from this derived information.

e). Spearman’s rank correlation was calculated using excel tool pack (Table 6 of appendix). The correlation between profit and other three variables were almost perfect positive. Correlation coefficients between profits and sales, profits and size, profits and lines were 0.966, 0.967 and 0.917 respectively. It was evident that all the three variables were significantly affecting the profits in positive way.

f). Three regression models were constructed by excel data tool pack and were given in tables 7, 8 and 9 of appendix. The regression model of profits with size was the best as the independent variable size was able to explain 93.2% of the profit variable. The regression model of profits with sales was also able to explain the dependent variable in 93.07% cases. From the residuals, diagrammatic representation of actual profits curve with predicted profits curve for both the regression models were given. These representations were very similar and the residuals were compared as in figure 3. From the residuals it was again clear that regression model of profits with size was the best among three models. The adjusted R square for the regression model of profits with lines was 0.833 and hence discarded from this comparison.

Testing if the average number of lines stocked per store is significantly different

                                                              

                                                                   

                                                                           

2.a). The summary table of correlation coefficients between all the variables taken pair wise has been provided in table 10 of the appendix. Mileage per gallon (Milpgal) had high negative correlation with cylinder, distance and weight with correlation coefficient of -0.684, -0.756 and -0.825 respectively. Company and country were positively correlated with milpgal with correlation of 0.458 and 0.508. Price was almost un-correlated with milpgal whereas it had low positive correlation with acceleration. Displacement had high positive relations with cylinder, horse power and weight with correlation coefficients of 0.932, 0.778 and 0.83. Price had significant positive correlation with year and weight whereas very low positive correlation with all other variables. It was noticed that price was the only variable with positive correlation with all the variables. Companies had very high positive correlation with country where correlation coefficient was 0.789.

2.b) 1.Price and milpgal were the two possible dependent variables. Price was dependent on all of the other variables and hence was taken as the dependent variable.

2.From the correlation summary (table 10 in appendix) of price, independent variables year, weight and country were supposed to be useful in multiple regression model.

2.c).Multiple regression analysis was done using excel data tool pack and presented in table 11 of appendix. Price was the sole dependent variable with all other variables as independent variable. The regression equation from the coefficients of regression table was constituted as 

                                                             

2.d). From the regression model it was evident from the p values of milpgal, displace, weight, year and country that these independent variables were the significant variables. Significance level for these independent variables was less than 0.05. Other independent variables had higher significant values than 0.05 and hence were considered insignificant in contribution to the regression model. Comparing with results of (b2) it was discovered that milpgal and displacement instead of low correlations with price had significant effect in regression analysis. Prediction about year, country and weight as significant independent variables was established again by regression model.

Comparing profits of two sales groups

2.e). Regression model with significant variables in excel has been given in table 12 of    appendix. The current model was better than the previous model. It was evident from value of adjusted R square that significant variables explained 48.77% of price whereas all the variables together were able to explain 49.19% of the price variable. Hence significant independent variables could explain 99.15% situations of those cases which were explained by all the independent variables. The p values of the modified model indicated the highly significant nature of the independent variables.

3.a) The Sales data was represented in a bar diagram as well as in a line diagram

                                                                      

                                                                       

From figure 4 it was evident that sales were high in first quarters in all six years. Sales dipped in second quarter for all the six years only to bounce back in third quarter. From the line diagram fluctuation of the sales data was noticed, it was also clear that sales did not increased significantly in given six years.

 3.b). A 3-point or 3 month moving average was calculated in excel and the summary has been given in table 13 of appendix. The diagrammatic depiction of moving average with actual sales data has been given in figure 6. The moving average line smoothed out the fluctuation of original sales data and was easily a linear trend line for the sales data.

                                                       

3.c). Seasonally adjusted time series data were calculated in excel using moving average method and the summary has been given in table 14-16 of appendix. The seasonally adjusted data with the original sales data has been plotted together in figure 7. The 24 quarters of the data was in the horizontal axis. Seasonally adjusted series smoothed out seasonal fluctuation in sales figures. The seasonally adjusted time series curve was almost a mean line for the original sales data.

                                                           

3.d). Exponential smoothing is a technique to smooth out a time series data with use of exponentially weighted calculation of moving average (Taylor, J.W. et.al, 2012). A new smoothed time series data gets generated from the actual time series data by using the formula  

In normal moving average method each observation is given an equal weight whereas in exponential smoothing recent data is given higher weight compared to the old data. The old variables are adjusted by assigning exponentially decreasing weights.

Exponential smoothing allows the smoothing parameter to change over a period of time with a smoothing factor. Time series data for any variable generally contains noise or fluctuations over period of time. Exponential smoothing adjusts these arbitrary noise or fluctuations by assigning improved value to the smoothing parameter (De Livera, A.M., et. al., 2011). The improved model surpasses the adaptive methods in case of existence of outlier values. The importance of the recent data increases with exponential smoothing. The trend line from exponential smoothing forecasts future data much more efficiently compared to the normal time series.

Reference:

De Livera, A.M., Hyndman, R.J. and Snyder, R.D., 2011. Forecasting time series with complex seasonal patterns using exponential smoothing. Journal of the American Statistical Association, 106(496), pp.1513-1527.

Taylor, J.W. and Snyder, R.D., 2012. Forecasting intraday time series with multiple seasonal cycles using parsimonious seasonal exponential smoothing. Omega, 40(6), pp.748-757.