Data Analyst Report: Analysis of Car Insurance Brokerage Services

This report presents a comprehensive analysis of a car insurance brokerage service, utilizing a dataset of 25 variables to address nine specific questions. The study employs various statistical methods, including summary measures, regression analysis, confidence intervals, chi-square tests, ANOVA, and single-sample t-tests, all performed using MS Excel. The analysis explores relationships between factors such as broker performance, customer satisfaction, and insurance premiums, offering insights into the impact of car value, age, and other variables. Key findings include an assessment of broker performance, relationships between customer satisfaction and demographic factors, and the significance of car value in determining insurance premiums. The report also estimates average savings and confidence intervals, providing a detailed understanding of the dataset and the statistical conclusions derived from it.

Running Head: FOUNDATION SKILLS IN DATA ANALYST
Foundation Skills in Data Analyst
Name of the Student:
Name of the University:
Author Note:

1
FOUNDATION SKILLS IN DATA ANALYST
Executive Summary
This study is based on brokerage service of car insurance for the customer. An
insurance worker provides various policy scheme for the customers. In this study there are 25
variables provided. The Summary measure, regression, confidence interval, chi-square test,
ANOVA, single sample t-test has been shown in this study for this 25 variable. According to
email there are nine questions are provided and the solution has been done by using MS-
Excel.

2
FOUNDATION SKILLS IN DATA ANALYST
Table of Contents
Executive Summary...................................................................................................................1
Answer to the question no. 1......................................................................................................3
Answer to the question no. 2......................................................................................................3
Answer to the question no. 3......................................................................................................4
Answer to the question no .4......................................................................................................5
Answer to the question no. 5......................................................................................................6
Answer to the question no. 6......................................................................................................6
Answer to the question no. 7......................................................................................................7
Answer to the question.no. 8......................................................................................................8
Answer to the question no. 9......................................................................................................9
References................................................................................................................................10

3
FOUNDATION SKILLS IN DATA ANALYST
Answer to the question no. 1
In this study there are four brokers. The number of consumers who saved annual car
insurance premiums is categorised by four brokers. The first broker has been saved annual car
insurance premium is $19602, the second broker saved $49864, the third broker saved $16621 and the
fourth broker saved $6010. It has been seen that the second broker saved the highest and the fourth
broker saved the lowest. From the summary table that is the table number two provide the summary
statistics of number of consumers who saved annual car insurance premiums. The mean of the data
is 23024.25 and the median of the data is 18111.5. It has been seen that the mean is larger than the
median. So it is positively skewed. The value of the skewnes is 1.41.The mode does not exist, because
there is no repetition in the data. The standard deviation is 18819.94.
Answer to the question no. 2
H0: There is a differences between the individual broker performances.
I.e. at least one the of individual broker performance has different.
H0: There is no differences between the individual broker performances.
i.e. μ1=μ2=μ3=μ4
Using ANOVA test the results of the test as bellow
Test statistic = 1.90
P-value = 0.13
Since the P – value is larger than the alpha value. The result is insignificant. Therefore
there is a sufficient at 5% level of significance to conclude that there is a differences between
the individual broker performances.

4
FOUNDATION SKILLS IN DATA ANALYST
Answer to the question no. 3
It has been seen that the 35% of the customers with the brokering services are
satisfied. A very few customers are very dissatisfied and the value is 5.5%. The 24.75%
customers with the brokering services are neutral.
The expected frequencies of gender versus satisfaction and area versus satisfaction
has been shown in the table 3 and table 4. Using chi – square test the relationship has been
identified.
Where
oi , j= observed frequencies
Ei , j= expected frequencies
Degrees of freedom=(r-1) (c-1)
Where r= number of rows
C= number of columns
H0: Customers satisfaction with the brokering service does not depends on area and
gender.
H0: Customers satisfaction with the brokering service is depends on area and gender.
The test statistic for gender versus satisfaction = 0.0004
The test statistic for area versus satisfaction = 0.003
P- Value for gender versus satisfaction= 1.00

5
FOUNDATION SKILLS IN DATA ANALYST
P- Value for area versus satisfaction= 1.00
Since the P- Value for both the test is larger than the alpha value. Therefore the hypothesis is
not significant. Hence it has been conclude that the customers satisfaction with the brokering
service is depends on area and gender
Answer to the question no .4
H0: There is a relationship between current insurance premiums and car age, car value,
license held, excess.
H1: There is no relationship between current insurance premiums and car age, car value,
license held, excess.
The general equation of multiple regression is
Y= β0 +β1 X1 + β2 X2+ β3 X3 + β4 X 4 +error
Where X1 =Car age
X2= Car value
X3= License held
X 4= Excess
Y = Current insurance premium.
Using multiple regression
The value of R-square = 0.70
The P- Value for age of the car = 0.29
The P- Value for value of the car = 0.00
The P- Value for License held = 0.23

6
FOUNDATION SKILLS IN DATA ANALYST
The P- Value for excess = 0.74
Since the P- value for age of the car, license held and excess are larger than the alpha
value. So this factors are not significant. Hence there is a sufficient evidence at 5% level of
significance to conclude that there is no relationship between current insurance premiums and
car age, license held and excess.
But the P- value for car value is lesser than the alpha value. So this factors are
significant. Hence there is a sufficient evidence at 5% level of significance to conclude that
there is a relationship between current insurance premiums and value of the car.
Answer to the question no. 5
An estimate of the average saving of car insurance premiums for all customers is to be
estimated by using one sample t- test.
The calculation has been done by using MS- Excel and this calculation of the above test is as
bellow
t =
X−μ
S
√n
t- Statistic = 14.37
Degrees of freedom = 399
P- Value = 0.00
Therefore the required estimated t- statistic is 14.37
Answer to the question no. 6
The proportion of all customers who are worse off = 97
400
= 0.2425

7
FOUNDATION SKILLS IN DATA ANALYST
= 24%
Here the total sample size = 400
Count of success = 97
Confidence interval = 95%
Sample proportion (p) = 24%
Z- Value = 1.96
Standard error of the Proportion (SE) = √ p(1− p)
n
= 0.02143
Margin of error (ME) = SE * Z- value
= 0.0420
Lower limit of the confidence interval = Sample proportion – ME
= 20.05%
Upper limit of the confidence interval = Sample proportion + ME
= 28.45%
Answer to the question no. 7
Given that the two years ago stated that the average savings on car insurance premium is
around $260.
H0: The average savings on car insurance premiums has decreased.
I.e. μ < $260
H1: The average savings on car insurance premiums has not decreased.
I . e . μ ≥ $260

8
FOUNDATION SKILLS IN DATA ANALYST
The mean of the average savings car insurance premiums = $228.14
The variance of the average savings car insurance premiums = $101159.6
Using one sample t-test the it has been seen the following result
Degrees of freedom = 398
P – Value = 0.000
Since the P- Value is lesser than the alpha value. So that it is significant. Therefore
there is a sufficient evidence at 5% level significance to conclude that the average savings on
car insurance premiums has not decreased that means μ ≥ $260.
Answer to the question.no. 8
The proportion of males that use car insurance brokers = = 167
400
= 0.4175
= 42%
Here the total sample size = 400
Count of success = 167
Confidence interval = 95%
Sample proportion (p) = 42%
Z- Value = 1.96
Standard error of the Proportion (SE) = √ p(1− p)
n
= 0.0246
Margin of error (ME) = SE * Z- value

9
FOUNDATION SKILLS IN DATA ANALYST
= 0.0483
Lower limit of the confidence interval = Sample proportion – ME
= 36.92%
Upper limit of the confidence interval = Sample proportion + ME
= 46.58%
Answer to the question no. 9
The estimate of true proportion (P) = 142
500
= 0.355
= 35.50%
Margin of error (ME) = 0.05
Confidence interval = 95%
Z- Value = 1.96
Calculated sample size = P∗( 1− p )∗( Z−Value)2
( ME )2
= 351.85
Therefore minimum sample size needed = 352

10
FOUNDATION SKILLS IN DATA ANALYST
References
Altman, D., Machin, D., Bryant, T. and Gardner, M. eds., 2013. Statistics with confidence:
confidence intervals and statistical guidelines. John Wiley & Sons.
Cameron, A.C. and Trivedi, P.K., 2013. Regression analysis of count data (Vol. 53).
Cambridge university press.
Campbell, D. and Lele, S., 2014. An ANOVA test for parameter estimability using data
cloning with application to statistical inference for dynamic systems. Computational
Statistics & Data Analysis, 70, pp.257-267.
Chatterjee, S. and Hadi, A.S., 2015. Regression analysis by example. John Wiley & Sons.
Cuchel, M., Meagher, E.A., du Toit Theron, H., Blom, D.J., Marais, A.D., Hegele, R.A.,
Averna, M.R., Sirtori, C.R., Shah, P.K., Gaudet, D. and Stefanutti, C., 2013. Efficacy and
safety of a microsomal triglyceride transfer protein inhibitor in patients with homozygous
familial hypercholesterolaemia: a single-arm, open-label, phase 3 study. the Lancet,
381(9860), pp.40-46.
De Winter, J.C., 2013. Using the Student's t-test with extremely small sample sizes. Practical
Assessment, Research & Evaluation, 18(10).
De Winter, J.C., 2013. Using the Student's t-test with extremely small sample sizes. Practical
Assessment, Research & Evaluation, 18(10).
Duggan, M. and Brenner, J., 2013. The demographics of social media users, 2012 (Vol. 14).
Washington, DC: Pew Research Center's Internet & American Life Project.
Fox, J., 2015. Applied regression analysis and generalized linear models. Sage Publications.
Hinton, P.R., 2014. Statistics explained. Routledge.
Holcomb, Z.C., 2016. Fundamentals of descriptive statistics. Routledge.
Jan, S.L. and Shieh, G., 2014. Sample size determinations for Welch's test in one‐way
heteroscedastic ANOVA. British Journal of Mathematical and Statistical Psychology, 67(1),
pp.72-93.
McHugh, M.L., 2013. The chi-square test of independence. Biochemia medica: Biochemia
medica, 23(2), pp.143-149.

11
FOUNDATION SKILLS IN DATA ANALYST
Onchiri, S., 2013. Conceptual model on application of chi-square test in education and social
sciences. Educational Research and Reviews, 8(15), pp.1231-1241.
Vogt, A. and Barta, J., 2013. The making of tests for index numbers: Mathematical methods
of descriptive statistics. Springer Science & Business Media.