Statistical Analysis and Hypothesis Testing in Excel

· All numerical calculations and graphs/plots should be done using EXCEL. In your worksheet there should be formula’s and/or calculations and not just the value results. If there are no calculations showing how you obtained the result, 0 marks will be given even if the result is correct.

· Please DO NOT use the formulas embedded in excel (unless explicitly specified otherwise!) but the ones we discussed on the slides in class to calculate the parameters (you can of course check your result with the excel functions):

· Your assignment must be done in Excel and for every question you will make a separate worksheet! There are 5 questions so there will be 5 worksheets in your excel file, not more not less!

· A copy of your completed assignment, so your EXCEL sheets, must be submitted electronically.

· You are required to keep a hard copy and an electronic copy of your submitted assignment to re-submit, in case the original submission is lost for some reason

Attached you will find an Excel sheet named “financial_markets_data.xlsx” in which you will find the daily closing prices of both the S&P500 (USA benchmark index) and of the DJ Eurostoxx 50 (European benchmark index) over the last 5 years.

a. Calculate the daily returns for both indices and then the mean and the median of these daily returns for both indices

b. Calculate the standard deviations, variances and coefficient of variances of the daily returns for both indices and also for the daily closing prices of both indices.

c. Calculate the following percentiles for the daily returns for both indices: 10th, 25th and 40th .

d. What is the correlation between the closing prices of the S&P500 and of the DJ Eurostoxx 50?

e. Divide the daily returns from both indices into a number of different classes you deem appropriate. Explain why and how you did this. Give per class the frequency, relative frequency and cumulative relative frequency.

f. Plot a histogram of the daily returns of both indices and comment on the shape of it.

In the attached spreadsheet named “Accidental_Drug_Related_Deaths_2012-2018.xlsx” you will find data on the all the deaths in the state of Connecticut (USA) during the period 2012- 2018. You found on internet that the population in this American state is composed of 69.3% Whites, 16.1% Hispanics, 10.1% Black or Afro-Americans and 4.5% Asians. Further you assume that the population is exactly 50% male and 50% female.

a. What is the average and median age of the deaths per race and per gender? What is the standard devation of the age per race and per gender?

b. You want to know whether there are significantly (95% confidence level) more malesn in Connecticut dying from drug abuse than females following the Hypothesis testing we learned in class. What would be your null hypothesis and what your alternative hypothesis?

c. Verify for b above wether your nul hypothesis should be withheld or rejected.

d. Perform the same analysis whether there are significantly (95% confidence level) more Blacks dying from drug abuse than their share in the population would suggest. Specify clearly your null hypothesis and your alternative hypothesis.

e. Perform the same analysis whether there are significantly (95% confidence level) more Hispanics dying from drug abuse than their share in the population would suggest. Specify clearly your null hypothesis and your alternative hypothesis.

f. Perform the same analysis whether there are significantly (95% confidence level) more Whites dying from drug abuse than their share in the population would suggest. Specify clearly your null hypothesis and your alternative hypothesis.

Life insurance experts have been claiming that the average worker in the city of Cincinnati has no more than $25,000 of personal life insurance. An insurance researcher believes that this is not true and sets out to prove that the average worker in Cincinnati has more than  $25,000 of personal life insurance. To test this claim, she randomly samples 100 workers in Cincinnati and interviews them about their personal life insurance coverage. She discovers that the average amount of personal life insurance coverage for this sample group is $26,650. The population standard deviation is $12,000.

a. Determine whether the test shows enough evidence to reject the null hypothesis posed by the salesperson. Assume the probability of committing a Type I error is .05.

b. If the actual average for this population is $30,000, what is the probability of committing a Type II error?

c. If instead you were testing a null hypothesis stating that the average of the population is $25,000 against an alternative hypothesis that it is not equal to $25,000, where would the critical values lie assuming an 10% ?

d. Explain the meaning of the critical values obtained under c. above and how you would use these in hypothesis testing.

a. What is the covariance between dependent variable Y and the independent variable X? What are the standard deviations of X and Y in this sample?

b. What is the correlation between X and Y and give your interpretation of this figure.

c. Find the equation of the regression line of X and Y and interpret the values you found for the intercept and the slope.