Descriptive Analytics And Visualisation docx.

In this assignment 1 pdf file and 4 excel file available. All analysis answer should be in A1 excel sheet. and other 3 excel sheet for analysis and calculation. Read pdf file very carefully and especially attention guidelines which are given by the professor. I need all question solution very precisely with perfection. I need the solution excel file with all the workings. In excel file all formulas and all should be clearly mentioned. Word file at least 1500 words which following professor guideline. So read carefully everything.

DESCRIPTIVE ANALYTICS AND VISUALISATION
ASSIGNMENT ONE
STUDENT ID:
[Pick the date]

Introduction
The given report aims to outline the findings from the statistical analysis of the sample data
provided by Edmond Kendrick. The data was accompanied by an email which raised certain
questions which have been addressed as part of the given report. The statistical analysis of the
sample data has been facilitated through the use of both descriptive and inferential statistical
techniques so that requisite information about the various claims can be made. With regards
to the given data analysis and interpretation, it has been considered that the sample provided
was collected using a random sampling technique and presented a faithful presentation of the
population of interest.
Discussion
The results derived from the sample data analysis are discussed below.
Question 1
a) The two sample mean proportion test has been conducted in order to estimate is there is
any difference in the proportion of “MILD”or “MEDIUM” for males and females. Based
on the sample data, test has been performed using Excel and output has been shared in
Table 1 in Appendix 1. The test used a z statistics and is two tail. The results indicate
rejection of null hypothesis and acceptance of alternative hypothesis. Hence, for the two
genders, we can conclude with 95% confidence that proportion of of “MILD” or
“MEDIUM” claims do differ significantly.
b) The two sample independent t test is the appropriate test to be conducted in order to test
claim whether average claim amount is higher when claimant is represented by private
attorney. It is noteworthy that t based test has been chosen over a z based test since the
population standard deviation for the two samples is not known. Based on the alternative
hypothesis, the appropriate test is right tail and it has been assumed that variances for the
two sample data sets are unequal. The output from excel contained in Table 2 in Appendix
1 highlights that rejection of null hypothesis would happen. This would indicate that on
average higher claim amount is obtained when representation by private attorney takes
place.

c) A two sample proportion test has been carried out to check the claim if there is higher
representation on average by private attorney for “SEVERE” cases when compared with
“MEDIUM” cases. The appropriate hypothesis test would be based on z statistic and is
one tail (right). Based on the result pasted in Table 3 of Appendix 1, it can be inferred that
null hypothesis cannot be rejected. This would imply that the industry claim is not
supported by the given sample data.
Question 2
a) A two sample proportion test has been carried out to determine if the % of “SEVERE”
claims is lower for orthopaedic surgeons when compared with other specialists combined.
The appropriate test would use a z statistic and is left tail. Based on the output presented in
Table 1 of Appendix 2, it can be inferred that no rejection of null hypothesis takes place.
Thus, one can infer that the sample data does not lend support to the claim that
orthopaedic surgeons have lower representation of “SEVERE” claims when compared
with all other specialists combined.
b) A two sample independent t test has been carried out to determine if the claim amount on
average is higher for orthopaedic surgeons when compared with other specialists
combined. It is noteworthy that t based test has been chosen over a z based test since the
population standard deviation for the two samples is not known. Based on the alternative
hypothesis, the appropriate test is right tail and it has been assumed that variances for the
two sample data sets are unequal. The output from excel contained in Table 2 of Appendix
2 highlights that rejection of null hypothesis would not happen. As a result, the claim is
wrong.
Question 3
a) For the given claim, it needs to be determined if there is any significant difference in the
average claim amount based on the claimants marital status. A t test would not be suitable
here since there are 4 categories of marital status. The Tukey-Kramer Multiple
Comparisons test has been carried out in this case. Based on the output pasted in Table 1
of Appendix 3, it can be inferred that rejection of null hypothesis would not happen. This
implies that average claim amount is not significantly different across marital status.

b) For the given claim, it needs to be determined if there is any significant difference in the
average claim amount based on the different surgeon specialities. A t test would not be
suitable here since there are 4 categories of marital status. The Tukey-Kramer Multiple
Comparisons test has been carried out in this case. Based on the output pasted in Table 2
of Appendix 3, it can be inferred that rejection of null hypothesis would not happen. This
implies that average claim amount is not significantly different across different surgeon
specialities.
c) For the given claim, it needs to be determined if there is difference with regards to
proportion of representation by private attorneys for claimant having different marital
status. Considering that the relevant hypothesis requires to compare proportion instead of
numbers, hence confidence interval based approach is preferred. The decision rule is that
if there would not be any overlapping of atleast one confidence interval with any of the
other three, then it would be concluded that there is significant difference in proportion.
The requisite result has been pasted in Table 3 of Appendix 3 and highlights that all the
four confidence intervals tend to overlap with each other. The conclusion thereby can be
derived is that private attorney representation shows no significant variation as per marital
status of claimants.
Question 4
For considering the private attorney representation impact and insurance on the claim
amount, the data in the experiment tab has been analysed using pivot table. This has resulted
in a graph labelled as Graph 1 in Appendix 4. This graph highlights that average claim
amount tends to higher when there is a private attorney representation irrespective of the type
of insurance. Further the average claim amount varies in accordance with insurance when
private attorney representation is not involved. The Table 1 of Appendix 4 indicates that
highest average claim can be expected from Medicare/Medicaid claimants while lowest
insurance claim can be expected from private insurance.
Question 5
Meeting deadlines is a critical objectives with regards to work of data analyst since if the
requisite analysis is not performed in a timely manner, then it is quite likely that the relevance
of same could be lost. Considering the fast based business environment which is prevalent,

accurate and reliable information needs to be available on time or else competitive advantage
may be lost. For delivery of my work on time, I rely heavily on planning and tend to break
down the complex tasks into smaller tasks where the scope of work is easier to determine. A
time frame is estimated for each of these and an estimated time is worked out for the task at
hand. Usually some buffer is always kept as there are certain tasks on priority which cannot
wait. Additionally, at times the task may turn out to be more complex than I originally
thought which results in overshooting of the anticipated time. However, to cope up with this,
I would out in extra time so that the future schedule of the work remains the same and there is
buffer left. I am not a believer of sitting beyond office hours unless necessary and if I have to
sit beyond my normal timings, I see that as a mark of inefficiency. Considering this, I am
always able to motivate myself so as to ensure that work is finished on time and quality is not
compromised.
Conclusion
The above analysis indicates that the proportion of “MILD” or “SEVERE” claims does differ
by gender i.e. male and female. Further, the claim amount on average is higher when private
attorney representation is there. In relation to the representation of private attorney being
higher in “SEVERE” claims in comparison to “MEDIUM” claims, the sample data does not
provide any support. The claim in relation to claim amount on average being higher in case
of orthopaedic surgeon does not find support in the sample data. Also, the claim regarding
the proportion of “SEVERE” claims being lower for orthopaedic surgeon is found to be
untrue based on sample data. The average claim amount is not influenced by the underlying
surgeon specialist but is impact by the marital status of the underlying claimant.

Appendices
Appendix 1
Table 1

Table 2

Table 3

Appendix 2
Table 1

Table 2

Appendix 3
Table 1
Table 2
Table 3

Appendix 4
Graph 1
Table 1