ASSIGNMENT: Data Mining, Analytics and Visualisation with EXCEL
Regression Analysis
Does Operational Performance Predict Business Outcomes?
A war of words has been raging within Capital City Telco, about whether or not customer satisfaction is important in explaining customer retention.
Ima Bignoise, Director of Capital City Telco has strongly contended that measuring how satisfied its competitors' customers are (High Street Telco) returns no managerial value. Indeed, it has been his view that Capital City Telco should not even be measuring its own customer's satisfaction, as it too is a waste of time and resources.
Ms Wannabe Alegend has been called in by management to settle the argument once and for all. She has commissioned marketing research and collected the following information from the customers of both Telcos:
Variable 1: Satisfaction with their telephone enquiry centre ("How satisfied are you with the telephone enquiry centre of your Telco?")
Variable 2: Satisfaction with local and long distance telephony ("How satisfied are you with the local and long distance telephony of your Telco?")
Variable 3: Satisfaction with dial-up internet ("How satisfied are you with the dial-up internet provided by your Telco?")
Variable 4: Satisfaction with billing ("How satisfied are you with the billing provided by your Telco?")
Variable 5: Satisfaction with the relationship with your Telco ("How satisfied are you with the overall relationship you have with your Telco?")
She has also collected a business outcome for each Telco Company comprising:
Variable 6: Retention ("How likely would you, the customer, be to give your Telco company at least that same level of business next year?")
Defining the Data
Variables 1 - 5 are referred to as the 'satisfaction transactionals' since they provide the customer satisfaction ratings in their most actionable form. Variable 6 (Retention), the business outcome question, is the dependent variable which is hypothesised to be explained by these transactionals.
For all of the satisfaction transactional questions, the respondent provided a satisfaction rating using a "11 point Likert scale1", where 0 indicates extremely dissatisfied and 10 indicates extremely satisfied. For the business outcome question, (Retention) the respondent rated how likely they would be to give their Telco at least the same level of business next year, again using a "11 point Likert" scale. However here, 0 indicates 'no likelihood' and 10 indicates 'extremely likely'.
This data was collected from New South Wales and Victoria and is captured in the variable labelled 'STATE'. The following coding applies:
STATE = 0 New South Wales data
STATE = 1 Victoria data
Finally, the variable labelled 'TELCO' has been used to identify which Telco is being rated. The coding is as follows:
TELCO = 0 Capital City Telco
TELCO = 1 High Street Telco
The data file for this report is labelled Predicting Business Outcomes_1_2018_STUDENT.xlsx and has 500 observations in it (n=500). This file can be found in the ASSIGNMENTS section on Moodle.
Required: The question you must answer is:
Is it a waste of time and resources for Captial City Telco to measure customer satisfaction? That is, once collected, does the analysis of this data provide any value to management?
ASSIGNMENT TWO BEGINS HERE:
Now that you are familiar with the data, their distribution and relationships, you can now move to a more sophisticated approach in identifying relationships - linear regression analysis.
Whilst the ability of multiple regression modelling to analyse the relationship between a dependent variable and numerous independent variables, makes it the perfect tool to apply here, it is beyond the scope of this unit (worth doing a second year Stats unit just for this!). Instead, we will use simple linear regression, which restricts the modelling to just one independent variable at a time.
We will be working from an 'overall' perspective, without segmenting the data based on Telco Company or Location (State). This means that our analysis will be conducted on the sample size of 500.
Since the regression analysis procedure in Excel does not handle "blanks" in the data very well, I have created a 'customised for regression' version of the data by replacing all the missing values with randomly generated observations.
You will find this data file in the ASSIGNMENTS section on Moodle. It is labelled as follows: Assignment Two DATA-Blanks Replaced for Regression_1_2018_STUDENT.xlsx and is to be used to answer the following questions.
PART I -
QUESTION ONE: Produce five simple linear regression models [using the Regression procedure in Excel NOT via a scatterplot], one for each of the transactional drivers (Q1 - Q5) against the dependent variable, Retention (Q6). Provide the Summary Output for each of these regression models as Exhibits 1-5, being careful to label them appropriately.
PART II -
Analyse these models to determine which relationship will be the most useful/powerful in explaining retention and write a short report to management.
There are two parts to this analysis:
1. Determining which models are significant. (Question Two below)
2. Determining which of the significant models is the most useful. (Question Three below)
QUESTION TWO: Hypothesis Test of Significance
Using the p-value approach, and a 5% level of significance, determine which of the transactional variables has a statistically significant, linear relationship with Retention, Q6.
TIP: Present a full hypothesis test for the first variable/model, including the 5 steps, and then provide a summarised set of results for the remaining four variables, which includes the key components and conclusions.
QUESTION THREE: Explanatory Power of the Model (R-Squared)
Of the significant models, select the 'winning' model, based on its explanatory power. State the equation of this regression model (correct to 4 decimal places), remembering to define the variables, and state its R-squared.
QUESTION FOUR: Report to Management
Now that you have identified the winning model, provide the following in a short report to management:
i) Interpretation of R squared
ii) Interpretation of the slope coefficient
PRESENTATION: Deductions may be made for poor presentation. If your presentation is easy to read, you will avoid deductions. Ease of reading is assisted by appropriate font size, borders, colour choice and labelling of exhibits, and some care in spelling, grammar and punctuation.
Attachment:- Assignment Files.rar