MIS171 Business Analytics

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).

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;

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).

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.

The coefficient for competition is -0.442; this means that a unit increase in the number of competitors would result to a decrease in sales by 442,000 dollars.

The coefficient of trading hours is 0.018; this means that a unit increase in trading hours would result to an increase in sales by 18,000 dollars.

The coefficient for age of the store manager -0.069; this means that a unit increase in the age of the store manager would result to a decrease in sales by 69,000 dollars.

Lastly, the coefficient of mng-exp is 0.194; this means that a unit increase in the experience of the management would result to an increase in sales by 194,000 dollars.

The final regression equation model would therefore be like the one shown below;

Testing multicollinearity

We tested whether there any potential multi-collinearity problems. To do this we had to compute the tolerance and VIF.

Since the VIF is greater than 4 then there could be potential of multi-collinearity problems (O’Brien, 2007). The independent variables with collinearity problems are advertisements and wages; competitors and hours of trading.

Estimation

What would be the sales for a five year old store with 50 staff and 50 car spaces that is open for 100 hours per week including Sunday, managed by a 35 year old female manager with five years of experience, that pays $2.5 million on wages, spends $150,000 on advertising, reports $1 million gross profit, with two competitor stores? [Note, only use the values that you have found to be significant (α set at 0.05) contributors to the behavior of the dependent measure].

Substituting the values into the regression equation model yields;

Thus the sales given the input values is 11.5325 million dollars.

Task Two – Classifying customers according to RFM

Total net revenue of all customers without RFM coding

This is the sum of net revenues for all the customers and it is given as $57,594.22.

Net revenue generated by the top 10% of the customers under RFM

This is the sum of net revenues for the top 10% of customers under RFM (first 300 customers when arranged from descending order of the RFM scores) and the value is given as $733.04

Net revenue generated by the top 20% of the customers under RFM

This is the sum of net revenues for the top 20% of customers under RFM (first 600 customers when arranged from descending order of the RFM scores) and the value is given as $1830.94.

Response rate of the top 10% customers under RFM

This is the percentage of customer who responded and are in the top 10% of customers under RFM (first 300 customers when arranged from descending order of the RFM scores)

Response rate of the top 20% customers under RFM

This is the percentage of customer who responded and are in the top 20% of customers under RFM (first 600 customers when arranged from descending order of the RFM scores)

Lift ratio for the top 10% and 20% customers under RFM

This is the ratio of target response divided by average response (Thomas, 2003).

Task Three – Developing sales forecast

As can be seen from the plot, almost linear trend emerges, indicating that the company’s sales enjoyed a steady growth over the years (approximately 3 times more sales have been made in 2018 than in 2015).

Forecasting error

We computed forecast error % by considering the differences in the actual sales and the forecast sales (French, 2017).

The forecast error was found to be 19.88% which is not large enough hence showing that the model forecasts the sales almost accurately.

R² value of the model

The value of R-Squared for the model was found to be 0.7301; this implies that 73.01% of the variation in the dependent variable (sales) is explained by the change in time (Magee, 2000).

Prediction of the next time period

The regression model is given as;

The next time period is t = 37 hence the forecast sales is;

Thus the predicted sales for the month of April 2018 is 536.0638.

References

Armstrong, J. S., 2012. Illusions in Regression Analysis. International Journal of Forecasting, 28(3), pp. 689-696.

French, J., 2017. The time traveller's CAPM. Investment Analysts Journal, 46(2), pp. 81-96.

Magee, L., 2000. R2 measures based on Wald and likelihood ratio joint significance tests. The American Statistician, 44(5), pp. 250-253.

O’Brien, R. M., 2007. A Caution Regarding Rules of Thumb for Variance Inflation Factors. Quality & Quantity, 41(5), pp. 673-679.

Thomas, Z., 2003. Biased graphs IV: Geometrical realizations. Journal of Combinatorial Theory, 89(2), p. 231–297.