Task 1: Ungrouped Descriptive Statistics [10 marks]
To gain some insights into the typical price consumers are willing to pay for a stand mixer, a retailer of kitchen
appliances randomly selected 130 relevant invoices for transactions completed in October 2020. Descriptive
statistics, computed to summarise the data, are presented in Table 1 below. NB: Some values are missing. This
table has been made available in the Excel file named “MN4063QA July21CWK 1 Data” under the tab labelled
Task 1.
Table 1 – Ungrouped Descriptive Statistics
Required for Task 1 (Comments for task 1 should not exceed 50 words in total.)
a. With reference to Table 1 above and using a calculator, find the Sum and Variance. Insert your values
on the table and copy into your Word document.
b. Interpret the mean and standard deviation in the context of the scenario provided. NB: You
should not describe calculation methods.
Page 2 of 8
MN4063QA Understanding and Managing Data July21 Intake
Task 2: Grouped Descriptive Statistics [14 marks]
It was suggested to the retailer that the 130 transaction values could also be summarised in a grouped table (see
Table 2 below). Three table templates based on Table 2 have been made available in the Excel file named
“MN4063QA July21 CWK 1 Data” under the tab labelled Task 2.
Table 2 – Grouped Descriptive Statistics
Required for Task 2 (Comments for task 2 should not exceed 50 words in total.)
a. Using the three table templates provided in the Excel data file as a starting point, construct three tables in
Excel.
The first table should show the frequencies and percentage frequencies.
The second table should show the ‘less than’ cumulative frequencies and cumulative percentage
frequencies.
Complete the third table to estimate the mean and standard deviation. You need to copy the
Excel output (3 tables) into your Word document.
b. Compare the grouped data mean and standard deviation values to the ungrouped data mean and standard
deviation under task 1.
Why are the values different and which findings are more accurate; the ungrouped or grouped
statistics? Justify your answer.
c. With reference to fig. 1 below (ogive), comment on the estimated minimum amount a consumer needs to
spend on a stand mixer to belong to the top 25% in terms of expenditure.
Fig. 1 – Ogive Showing Cumulative Percentage Frequencies Expenditure (£) Frequency
Under 100 14
100 and under 200 35
200 and under 300 65
300 and over 16
Total: 130
Page 3 of 8
MN4063QA Understanding and Managing Data July21 Intake
Task 3: Data Types [8 marks]
The kitchen appliances retailer seeks to survey its customers to gain insights into key consumer characteristics,
including demographic profiles and lifestyle preferences. It is believed this will help the company better
understand its target consumers and strengthen its position in the market. However, before outsourcing the
data collection fieldwork, the retailer seeks clarification on data types, as outlined in the requirements below.
Required for Task 3 (Comments for task 3 should not exceed 100 words in total.)
a. With reference to examples relevant to the retailer’s information needs, discuss the differences
between cross-sectional data and time series data and clearly state whether cross-sectional data, time
series data or both types of data should be collected to gain insights into the demographic profile and
lifestyle characteristics of its customers.
Use and credit one source. The citation and reference must be credited using the Harvard method. The
reference is excluded from the 100-word limit.
Task 4: Networking [12 marks]
The retailer has seen a shift in the way in which consumers purchase items, such as kitchen appliances. Online
retailing now accounts for over 80% of their sales and, to reflect this change, the retailer has decided to
convert some of its retail space into warehouse space. This conversion project consists of 10 activities. For
example, Activity C takes 3 weeks to complete and can only be started after Activity A has been completed.
Information on the 10 activities is outlined in Table 3 below.
Table 3 – Precedence Table
Required for Task 4 (Comments for task 4 should not exceed 50 words in total.)
a. With reference to Table 3, construct a network diagram by going through the forward and backward
passes. You may draw your network diagram in Excel or Word. The network diagram needs to be copied
into your Word file.
b. Identify the critical path and duration of the project.
c. Comment on the distinction between critical and non-critical activities. Activity Preceding Activity Duration (Weeks)
A ……… 6
B ……… 4
C A 4
D B 2
E C 6
F C,D 5
G D 3
H …….. 8
I E,F,G 5
J H,I 2
Page 4 of 8
MN4063QA Understanding and Managing Data July21 Intake
Task 5: Relationships [12 marks]
To operate more effectively, the retailer wants to better understand the relationship between sales revenue,
total costs, average order value and gross profit. To this end, data for the period 2014, quarter 1 to 2020,
quarter 3 were made available. To complete task 5, refer to the data provided under the tab named “Task 5” in
the “MN4063QA July21 CWK 1 Data” Excel file.
Required for Task 5 (Comments for task 5 should not exceed 150 words in total.)
a. Construct a correlation matrix in Excel to show the correlation coefficients between quarterly Sales
Revenue (£’000) and total costs (£’000), average order value (£) and gross profit (£’000). The correlation
matrix needs to be copied into your Word file.
b. With reference to the correlation matrix, identify the ‘best’ predictor of quarterly Sales Revenue (£’000)
and briefly explain the reason for your choice.
c. Create a scatter graph, plotting quarterly Sales Revenue (£’000) against the independent variable selected
under point 2 above. The trend, regression equation and coefficient of determination should be shown
on the graph. Copy the correctly labelled graph, with the correct units of measurement, into your Word
file.
d. Interpret the relevant correlation coefficient and the coefficient of determination in context.
e. State the regression equation in context and interpret the values of the intercept and gradient in the
context of the scenario.
Task 6: Expected Values [8 marks]
The retailer has the option to invest in either venture A or venture B. Both ventures require an investment
of £39,000.
The net expected values and standard deviations for the two ventures are stated in Table 4 below. Table 4 –
Outcomes
Required for Task 6 (Comments for task 6 should not exceed 50 words in total.)
1. Calculate the coefficients of variation for venture A and venture B.
2. Assuming the retailer is risk averse, which project should be chosen?
Venture A Net EV = £47.0 Standard Deviation = £30.08
Venture B Net EV = £41.2 Standard Deviation = £13.32
Page 5 of 8
MN4063QA Understanding and Managing Data July21 Intake
Task 7 [6 marks]
This task requires reflection to provide insights into the process and quality of personal learning. Your reflection
should focus on your actions, motivations and emotions and should analyse what changes will be made to
practice in light of experiences, feedback and reflection. A good reflection will help you understand what, why
and how you learn.
NB: You should not comment on the theories of reflection. You should also not evaluate teaching or the module
for this exercise.
Required for Task 7 (Comments for task 7 should not exceed 150 words in total.)
What do you think you did particularly well in this assignment?
What was the most challenging aspect of this coursework and why was this the case?
What would you do differently next time?
Part 2 starts on page 6.
Page 6 of 8
MN4063QA Understanding and Managing Data July21 Intake
PART 2
Breakeven Analysis [30 marks]
Andreea Ltd. manufactures t-shirts, which sell at £15 each. Table 5 shows the sales and total costs for the
period July 2020 to December 2020. Monthly fixed costs have been the same each month and it is expected
they will remain constant for the remainder of the financial year (up to and including March 2021).
Table 5 – Sales Volume and Total Costs
Required for this part (The report should not exceed 400 words in total.)
1. With reference to Table 5 and using the high-low method, estimate the variable cost per t-shirt produced
and sold. Show your calculations.
2. Using the high-low method, estimate the monthly fixed cost. Show your calculations.
3. Using the information in (1) and (2) above, how many t-shirts should Andreea Ltd. produce and sell in
January 2021 to break even? Show your calculations.
4. How many t-shirts does the company need to produce and sell to achieve a profit target of
£200,000 in January 2021? Show your calculations.
5. What is the margin of safety for January 2021, assuming the profit target for that month has been achieved?
Show your calculations.
6. Write a 400-word report, addressed to the sales manager of Andreea Ltd. The report should:
a. Explain, with reference to the scenario and the table provided, why the high-low method had to
be used in this case;
b. State and interpret the fixed and variable costs;
c. Advise on the number of t-shirts Andreea Ltd. need to produce and sell in January 2021 to
breakeven;
d. Advise on the number of t-shirts Andreea Ltd. need to produce and sell to achieve the target profit
for January 2021;
e. Interpret the margin of safety for January 2021;
f. Briefly discuss the limitations of breakeven analysis in this situatio
