Solve a statistical case study using Excel file contains data needed.
tony201
case_study.rtfplease solve the Case study and prepare a short report (up to two pages, including tables and graphs) which describes the obtained results, provides answers to the questions and interpretations. Please send the report (pos sible formats: doc, docx, ) together with the solutions in the Excel file .
A health insurance company offers the basic health insurance for the same price (the same contribution) for all persons. However the experience of other companies shows that the costs of health care use increase with age. For that reason the company is considering to increase the annual contribution rate for persons aged 60+ as it suspects that the average cost of services provided to these persons is substantially higher than the average cost of services provided to the younger. The company obtained data on total cost of health services for a sample of 500 persons.
These data is available in the HEALTH.xls file. Using these data please answer the following questions:
a. Compare the sample mean and the sample median total cost of services provided to persons aged 59 or less and persons aged 60+. Do these sample statistics indeed suggest that health care costs on the elderly are higher?
b. Investigate the variation and the skewness measures. Are there any problems with the data (ex. outliers)? c. Locate outliers in the data if there are any. What could be the reason for the presence of outliers in the data?
d. Clean the data from the outliers and compute the sample means and sample medians again. Have the results changed? How?
e. Test whether the mean total cost of services provided to persons aged 59 or less is significantly lower than the mean total costs of services provided to persons aged 60+.
f. Do your results provide any rationale for the company to increase the contribution rate for the older persons?
g. The insurance company is also going to check the probability that the portfolio of insurances to persons aged 60+ will generate more costs than revenues in the next year if the annual contribution is equal to 1400. Let’s assume that the cost of health services per person is an unknown random variable with mean and standard deviation equal to those in the sample of persons aged 60+ (after removing outliers). Please calculate the probability that the average expenditure in the portfolio of observed 200 insurances next year will be higher than the contribution. Comment the result.
