All using excel

1. Using the Sample sheet, the marketing company wants to answer various questions regarding the relationship between the age groups and cities.

(a) Obtain a contingency table (using the PivotTable feature) of household age group by city. Paste the table into your report.

(b) Refer to the output in part (a) to answer the following questions:

i) What percentage of all people are between 15 and 64 years old in Edmonton?

ii) What percentage of people in Edmonton are between 15 and 64 years old?

iii) What percentage of the group with age between 15 and 64 years old are in Edmonton?

(c) Using = 0.05, is there any evidence of a difference in the proportion of seniors (ages 65 and over) between Calgary and Edmonton in 2021? Carry out the appropriate two-sample proportion test to answer the question.

State the null and alternative hypotheses. Calculate the value of the appropriate test statistic (show calculations), state the distribution of the test statistic under the null hypothesis, and the P-value (use the "NORM.S.DIST" function in Excel) of the test to answer the question. State your conclusion.

(d) Calculate a 95% confidence interval for the difference in the proportion of seniors between Calgary and Edmonton in 2021. State clearly all components (estimate, standard error, critical value) and show calculations. State your conclusion.

Comment on how your 95% confidence interval is consistent with the results of your hypothesis test in part (c).