1. Remember, how you present your answers is very important. Convey the findings in the
1. Remember, how you present your answers is very important. Convey the findings in the
clearest, simplest way.
2. Always provide evidence and make appropriate references to tables and charts
produced.
3. You must also draw borders and label all graphs appropriately.
4. You must use an appropriate font size. Font size 12 – 14 is desirable. Any appropriate
font type is acceptable.
5. Please spell-check your answers before submitting.
6. You will lose marks if charts/tables are not appropriately labelled.
7. Please note that marks will be deducted for poor presentation
Question 1: Storytelling with data (20 marks)
Statistics is the science of learning from data. Being able to turn data into information is a
critical aspect of decision making in business. In this world of big data, storytelling through
data has emerged as an important aspect of all data analysis. Complex ideas can be
understood easily though storytelling. In this question, we try and build your skills in
storytelling by improving your ability to visualise and communicate findings.
In this task, you are required to choose a data set from any source (e.g. internet) on a subject
of your choice (e.g. marketing data, finance data, etc.). When selecting a data set, please keep
in mind that in the later part of this question, you will be expected to create a graph using
Microsoft Excel. You may select a data set from any website you choose. Some suggestions
are.
• http://www.abs.gov.au/ (Links to an external site.)
• https://www.data.vic.gov.au/ (Links to an external site.)
• http://www.bom.gov.au/ (Links to an external site.)
Using your data, answer the following questions.
a. In not more than 2 sentences, provide a brief description of the data selected, e.g. what
is the data about and what information does it contain. You may also include
information such as the time period.
(1 mark)
b. Select two/three variables from your data set. Provide full data classification for each
variable you selected. In one sentence, explain your choice of classification for each
variable.
(3 marks)
c. Construct a pivot table using two/three variables you selected above. You are required
to construct a pivot table showing grand total of frequencies (not percentage).
(1 mark)
d. Based on your pivot table, provide one example of a marginal, conditional and joint
probability. Write the probability statement, workings and answers for each of the three 3
probabilities. Then provide a contextual interpretation of each of the three probability
values.
(6 marks)
e. Select two or three appropriate variables and provide a suitable visualisation (graph). It
must be appropriately labelled. Please remember to reference your data source. You
will need to indicate the URL for the actual data source.
(2 marks)
f. Briefly explain why you have chosen this type of visualisation (graph).
(1 mark)
g. Using the visualisation (graph) you provided in your answer above, write a summary
describing the main findings and patterns visible from the visualisation. Word limit here
is 100 words.
(6 marks)
Question 2: Producing, interpreting and communicating results using Excel (30 marks)
A large investment company is interested in analysing the efficacy of launching a brand of
frozen food products. As part of their feasibility study, they obtain data on a sample of 856
potential customers. This data is provided in A1.xlsx. Based on the data provided, answer the
following questions:
a. A recent report states that the average income of customers is highest for the oldest age
groups and this is true for both genders. Do you agree with the report?
In order to answer this, you will need to construct a pivot table that provides the average
income of the two genders according to different age groups.
Hint: Create class intervals starting from 20 years and using interval widths of 10 years,
e.g. your first-class interval should read 20-29, 30-39 and so on.
(3 marks)4
b. Provide a brief comparison of the proportion of male and female customers who have
and have not tried the product. Include a relevant table to support your answer.
(3 marks)
c. Compare the distribution of the variable “Annual Income” for the two genders, discussing
typical values of
i. central tendency measures such as mean and median values
ii. variability measures (how spread out the values are)
iii. shape of the distributions.
Explain which of the genders shows more variability in income? Explain using appropriate
evidence. (Hint: Wherever possible provide contextual interpretation of all measures
discussed)
(10 marks)
d. The company obtains information on average weekly expenditure on all frozen food
products, homeownership and gender. Using the suggested approach to pivot table
analysis, analyse how gender and home ownership influence the average weekly
expenditure. Be sure to include a table and quote relevant figures.
(7 marks)
e. The spending habits of customers can be divided into “High” and “Low”. “High” refers to
those who spend more than $30 per week on average. “Low” refers to those who spend
at most $30 per week on average. Based on this information, we wish to tabulate
information on customers gender and spending habits.
i. Briefly discuss two ways in which we can obtain this information by using Excel.
ii. Construct a table to depict this information.
(2 marks each)
f. Based on the table you provided above, does it seem that spending habits are
influenced by gender? Provide relevant evidence to support your answ
