Development of a Multiple Regression Model for Sales Estimation

Business Analysis
Name:
Institution:
25th May 2018

Task One – Development of a multiple regression model
In this section, we sought to develop a multiple regression model that would estimate the sales. A
total of 12 independent variables were included in the first model where we observed that only 6
out of the 12 independent variables were significant in the model.
The p-value of the F-Statistics is 0.000 (a value less than 5% level of significance), this leads to
rejection of the null hypothesis hence concluding that the overall multiple regression model is
significant at 5% level of significance ( Armstrong, 2012).
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.9300
79
R Square
0.8650
46
Adjusted R
Square
0.8532
26
Standard Error
1.3680
87
Observations 150
ANOVA
df SS MS F
Significan
ce F
Regressi
on 12
1643.6
24
136.96
87
73.180
3 1.99E-53
Residual 137
256.41
75
1.8716
61
Total 149
1900.0
42
Coefficien
ts
Standa
rd Error t Stat
P-
value
Lower
95%
Upper
95%
Intercept 3.942 1.168 3.375 0.001 1.632 6.252
Wages $m 2.189 0.612 3.577 0.000 0.979 3.399
No. Staff -0.016 0.024 -0.659 0.511 -0.063 0.031
Age (Yrs) -0.021 0.022 -0.950 0.344 -0.063 0.022

GrossProfit $m 0.000 0.201 0.002 0.999 -0.398 0.399
Adv.$'000 0.022 0.003 7.466 0.000 0.016 0.028
Competitors -0.424 0.106 -3.994 0.000 -0.634 -0.214
HrsTrading 0.019 0.008 2.538 0.012 0.004 0.034
SundayD 0.523 0.273 1.916 0.057 -0.017 1.062
Mng-GenderD -0.260 0.322 -0.806 0.421 -0.896 0.377
Mng-Age -0.064 0.017 -3.754 0.000 -0.097 -0.030
Mng-Exp 0.178 0.032 5.559 0.000 0.115 0.242
Car Spaces 0.006 0.008 0.765 0.446 -0.010 0.022
The significant independent variables that had the strongest linear relationship with sales were;
Advertising and promotional expenses for the financial year, No. of years of experience in some
form of junior/senior management at Supermart, The number of competing stores in the
consumer catchment area, Age of the store manager, years, Total Wage and salary bill for the
financial year ($million) and The total number of hours open for trading per week in that order.
The list of insignificant independent variables is given below;
Variable Name Description
No. Staff The number of effective full-time staff employed on a weekly
basis
Age The age of the store in years
GrossProfit $m Gross profit for each store for the financial year ($ million)
Sundays Open on Sundays (code 1); Close on Sunday (code 0)
Mng-Gender Male store manager (code 1); Female store manager (code 0)
Car Spaces The number of parking spaces available to the store
In the next section, we present a regression model with only the significant variables.
The value of R-Squared is 0.8577; this implies that 85.77% of the variation in the dependent
variable (sales) is explained by the 6 independent variables in the model.
The overall model was also found to be significant at 5% level of significance (p-value < 0.05).
Regression Statistics
Multiple R 0.9261

18
R Square
0.8576
94
Adjusted R
Square
0.8517
23
Standard Error
1.3750
71
Observations 150
ANOVA
df SS MS F
Significan
ce F
Regressi
on 6
1629.6
55
271.60
91
143.64
61 5.62E-58
Residual 143
270.38
74
1.8908
21
Total 149
1900.0
42
Coefficien
ts
Standa
rd Error t Stat
P-
value
Lower
95%
Upper
95%
Intercept 3.474 0.994 3.495 0.001 1.509 5.439
Wages $m 2.115 0.340 6.223 0.000 1.443 2.787
Adv.$'000 0.022 0.003 7.750 0.000 0.017 0.028
Competito
rs -0.442 0.099 -4.454 0.000 -0.638 -0.246
HrsTrading 0.018 0.007 2.522 0.013 0.004 0.032
Mng-Age -0.069 0.016 -4.326 0.000 -0.100 -0.037
Mng-Exp 0.194 0.031 6.168 0.000 0.132 0.256
Out of these 6 significant variables, 2 were negatively related with the dependent variable while
4 were found to be positively related.
The coefficient of wages is 2.115; this means that a unit increase in wages (1 million increase)
would result to an increase in sales by 2.115 million dollars.
The coefficient for advertisement is 0.022; this means that increasing advertisements by one unit
(say $1,000) would result to an increase in sales by 22,000 dollars.