In a multicultural country like Singapore, coffee plays an important role in social interactions: James has been operating a premium coffee shop in the country: Descriptive Analytics Assignment, NUS, Singapore

In a multicultural country like Singapore, coffee plays an important role in social interactions. People enjoy having a cup of coffee with friends or even business negotiations. James has been operating a premium coffee shop in the country. He has an intention to expand the shop into a coffee shop chain within the country. In order to understand the coffee culture in near future, he collected some information from a random sample of 150 coffee drinkers. The gender and annual coffee consumption (in kg) data are stored in
the Excel data file Assignment Data T2’24 (Re-assessment).

Question 1 

(a) Identify the data type and the level of measurement for each of the two variables in the data file – Gender and Coffee Consumption.
(b) Use Excel to find the mean, median, standard deviation and interquartile range for Coffee Consumption (in kg). You may use Excel Data Analysis Tool to generate the descriptive statistics, or individual Excel function for each of the measures. Do not simply paste the raw output as your answers.
(c) Generate a histogram for the coffee consumption using 9 bins to clearly show the shape of the distribution. Include appropriate label to the axes. You may use either of the Histogram function – Data Analysis Tool or INSERT function.
(d) Using the answers in part (b) and part (c), describe the distribution of the coffee consumption in terms of shape, center and spread using appropriate measures.

Question 2

James had a perception that the annual coffee consumption is most likely to be more than 3.2kg. A friend commented to James that there is a high percentage of heavy drinker in the country (consumption more
than 4.5kg).

Referring to the Excel output generated for Question 1 part (b), use the mean and standard deviation of coffee consumption to calculate the following probabilities by hand. Show all your workings.

Assume that the coffee consumption is normally distributed.

NOTE: Round the mean and standard deviation you obtained in Q1(b) to 2 decimal places before using them to do the calculation here.
(a) Calculate the probability that a randomly selected coffee drinker has coffee consumption of more than 3.2kg.
(b) What is the percentage of the coffee drinker that is considered heavy drinker according to the comment made by James’ friend?
(c) Are James’ perception and his friend’s comment well supported by the study?
(d) Use Excel function to find out the probability for part (b). Show the function used and the answer obtained.

Question 3

In order to further understand the study, James determines to estimate the population mean consumption of coffee using the data obtained.

NOTE: Round the mean and standard deviation you obtained in Q1(b) to 2 decimal places before using them to do the calculation here.
(a) Using Excel Data Analysis Tool, construct a 95% confidence interval estimate for the population mean coffee consumption. Show all your workings.(b) Interpret the interval obtained in part (a).
(c) Explain whether it is reasonable to say that the population mean coffee consumption is 2.6 kg.
(d) Using a significance level of 5%, test whether there is evidence to conclude that the population mean coffee consumption differ from 2.6 kg. Show all the steps in your manual working. No Excel is required.

Question 4

Reports show that there is an increase in the consumption of coffee in 2021 compared to 2019. Using the data stored in Worksheet Q4, perform a hypothesis test at 5% level of significance that the increase in population mean coffee consumption is significant. Assume that both the populations are normally distributed and the variances are equal.

Use the appropriate test in Data Analysis Tool to generate the output. Show all the steps in your working, supported by Excel output generated.