Background Information on the Assignment: You are required to answer all. In order to answer the questions, you will need to refer to the Excel data file provided.
You will need to communicate your findings accordingly. Remember, how you present your answers is very important. Convey the findings in the clearest, simplest way. Always provide evidence and make appropriate references to tables and charts produced.
Presentation Your assignment must be clearly set out, well labelled and easy to follow. You must use an appropriate font size. Font size 12 is desirable. Any appropriate font type is acceptable. You must also draw borders and label all graphs appropriately. Please spell-check your answers before submitting. Please note that three (3) marks will be deducted for poor presentation.
Telecommunications service 100 marks
A consulting firm is investigating the level of service provided by a telecommunication company in resolving customer issues. Data was obtained from a sample of 999 customer reviews which included variables such as age, income, number of enquiries made to resolve issue, etc. This information is available in the file A1_Data.xlsx,which has columns containing the following variables.
- Customer_ID: customer’s ID
- Age: age = 1
- Gender: 1 = female, 0 =male
- Married: marital status, Y = married, N = otherwise
- Postcode: postcode
- Employment: 1 = Employed, 0 = Unemployed
- Time Spent: time spent to resolve the issue (minutes)
- Rating: rating for the service from 1 to 5 (1=the lowest, 5=the highest)
- Enquiries: number of enquiries made to resolve the issue
Based on the data provided, answer the following questions:
- Select two different kinds of qualitative variables and two different kinds of quantitative variables. State whether the variable is nominal, ordinal, discrete or continuous. Explain your reasoning in each case. 5 marks
- Is the data “cross-sectional” or “time-series” data? Explain your answer.
- A recent report states the following: i. there is an even spread of the number of enquiries made to resolve the issue. ii. ratings are quite evenly distributed. Do you agree with these two statements? Justify your answers for both statements by providing relevant tables for each statement. 8 marks
- Construct two Pivot Tables using the number of enquiries and age groups, one using percentage column total and the other row total. (Hint: Group age using interval widths of 15 years, beginning with 16 years of age). 6 marks
- For each level of enquiry,
- which is the dominant age group? Are they younger or older customers?
- why is this the case?
Support your discussion with the aid of a table. 5 marks
- Are male customers making more enquiries than female customers? Provide a brief explanation. Support your answer with the aid of a table. 6 marks
- Complete the following tables of summary statistics of time spent for each of the following two variables:
Hint: sort the data by Gender, then calculate the summary statistics of time spent for the two genders separately.
- Rating (in two groups 1-3 and 4-5)
Hint: sort the data by Rating, then calculate the summary statistics of time spent for the rating from 1 to 3 group and rating from 4 to 5 group separately.
Give your answers in the following tables to two decimal places and make sure you indicate units. 10 marks
|Coefficient of Variation|
|Rating 1-3||Rating 4-5|
|Coefficient of Variation|
- Construct percentage frequency histograms of the Time Spent for the two rating groups:
1) Rating 1-3
2) Rating 4-5
Hint: You may need to sort the data by Rating, then copy the Time Spent data with rating 1-3 to the column K (under the variable name “Time (Rating 1-3)”) and that with rating 45 to the column M (under the variable name “Time (Rating 4-5)”) separately. In the histogram, group Time Spent using interval widths of 8 minutes, beginning with 0 minutes. 8 marks
- Based on your answers obtained in parts g and h, compare the distribution of the variable Time Spent for the two rating groups (Low: rating 1-3 and High: rating 4-5), discussing typical values (i.e. “central tendency”), how spread out the values are (“variability”), and the shape of the distributions. Which of the genders shows more variability in Time Spent? Explain using appropriate evidence. Quote the values to two decimal places and make sure you indicate units. 15 marks