Foundation Skills in Data Analysis: Annual Supermarket Report Analysis

This report analyzes a dataset of 150 supermarkets, examining their annual performance based on an annual survey. The analysis covers key aspects such as gross profit, with the mean being higher than the median, and identifies factors influencing sales, including advertising expenses and staff numbers. The report investigates the impact of Sunday openings on gross profit and compares online sales performance across different store locations. It also explores wastage levels and assesses the sample's representation of different states. Furthermore, the analysis includes the estimation of annual online sales revenue and the proportion of supermarkets with low wastage, compliance with ACCC norms, and the percentage of stores open on Sundays. The report concludes with an evaluation of the sample's representativeness and the need for a larger sample size to improve the margin of error. The report provides insights into the operational aspects of supermarkets, including bonus eligibility, sales drivers, and wastage management, offering valuable information for business decision-making.

FOUNDATION SKILLS IN DATA ANALYSIS
STUDENT ID:
[Pick the date]

Executive Summary
The given sample presents data of 150 supermarkets derived from the annual survey. These
selected supermarkets have a mean gross profit of $1.07 million which is higher than the
median value. The high positive value of measures of dispersion indicates the significant
dispersion of the annual gross profit of the selected stores. The store manager of FOODplus
supermarket would receive the yearly bonus when the annual gross profit is not lower than
$3.135 million. No outliers are present in the annual gross profit as the outliers are indicative
of the extremely high or/and low values which is not the case here. It is noticeable that the
mean annual gross profit derived by the supermarkets which remain open on Sunday is higher
than the supermarkets which remain closed on Sunday. It indicates that opening the stores on
Sunday creates positive impact on the total gross profit of the stores. Further, the leadership
in online stores tend to be high for strip-based stores rather than mall-based stores. Also, in
regards to the level of wastage, significant number of stores belongs to medium level of
wastage. Further, only few stores belong to the low level of wastage which implies that
enormous steps need to be taken to improve the wastage management by the stores so that
higher number of stores would fall in the low-level wastage category.
From the analysis, it has been found that advertising expense is that most dominating factor
that affects the sales and thereby offers explanation for change in this. Further, the number of
staff in the stores also creates significant impact on the sales. However, the influence of
number of staffs on the sales is lower than that created by advertising expenses. The sample
analysis indicates that the mean annual online sales revenue would fall within the range of
$0.30 million and $0.42 million. Further, the population proportion of 750 FOODplus
supermarkets expected to fall within the low wastage category is expected to lie in the
interval of 9.57% and 21.10%. The inflation over the last few years has been amplified so the
pricing of the FOODplus has increased but the company does not violate the ACCC norms
and hence average increase during 2017-2018 on selected food basket fails to exceed $ 6.85.
One of key concerns is that less than 70% FOODplus stores remain open on Sunday. Based
on the analysis of the sample data provided, It would be fair to conclude that Queensland is
over represented while, WA and Vic are under-represented. Moreover, for obtaining the
margin of error as 6% while predicting the high level of wastage store proportion for the
population, the selected sample size 150 is not enough and a large sample size is
recommended.
1

Introduction
FOODplus is an Australia supermarket chain with presence in 750 locations across Australia.
The company started operations 27 years ago and over the years have grown their presence
and operations. From the annual survey of the company, Paul Anderson (general manager)
has selected a sample data of 150 supermarkets which has been handed to a business analyst
named Emma Thomas so that statistical analysis may be performed with the objective of
providing reliable answers to the questions posed by Paul through a memorandum provided.
The main aim of the report is to present specific aspects of the annual performance of
FOODplus supermarkets through analysis the given sample of 150 supermarkets. The
descriptive and inferential statistical methods are taken into consideration to answer the
various essential research questions.
Discussion & Conclusion
The comprehensive discussion about the requisite queries of memorandum of FOODplus
supermarkets has been carried out which are represented below.
(1) A quick summary about the gross profit of the supermarkets is highlighted below.
(a) For the provided sample of 150 supermarkets, the annual mean gross profit comes out to
be $1.027 million. The median value of the gross profit comes out to be $0.972 million
which indicates that mean is little higher than the corresponding median amount. The
noncoincidence of mean and median is due to the presence of some of the supermarkets
which have significantly high annual gross profit and thereby skew the mean. It also
indicates high level of dispersion in the annual gross profit of the supermarkets which is
evident from the minimum and maximum values of gross profit that are $0.02 million and
$3.159 million respectively. The data is not normally distributed which is apparent from
the positive skew value in the annual gross profit. This is because some of the
supermarkets are showing extremely high value of gross profit as compared with mean.
(b) The management has decided to provide the bonus to the store managers based on the
derived annual gross profit. The store managers who belong to the top 45 supermarket
stores among the 750 supermarkets would be eligible to receive the bonus. It means only
the top most 0.06 percentile stores would qualify this criterion of the bonus will receive
the same. Taking this into cognisance, the threshold point of the annual gross profit based
on the 150 supermarkets sample data comes out as $3.135 million.
(c) The values which are extremely high or extremely low would be categorised as unusual
as the probability associated with these values are very low. Even though, the gross profit
of the supermarkets is positively skew but no outliers are present in the sample which has
been confirmed through the relevant plots.
(2) (a) The gross profits that are derived from the two groups of stores i.e. which are opened
and closed on Sunday would be taken into account in order to analyse the effect of store
opening on Sunday. In this context, the mean gross profit of the store which are opened
on Sunday comes out to be higher than those which are closed on Sunday. This is evident
from the respective average values of $1.097 million and $0.913 million for store opened
and closed on Sunday. Based on this outcome, it can be concluded that in order to derive
higher gross profit the FOODplus supermarket must remain open on Sunday.
2

(b) Mall, strip and country are the three main locations of the FOODplus supermarkets which
have been analysed in order to draw a comparison of the existence of the online stores. This
would be further taken into analysis so as to comment with regards to the leader in the online
channel of the sales in these locations. Nearly 67.74% of the supermarkets which are located
in malls are using online stores so as to enhance the sales. This corresponding percentage is
60.42% and 77.50% for country and strip-based location respectively. Based on this, the
conclusion can be derived that strip-based location supermarkets are using maximum
percentage of online stores for the sale which are followed by the mall-based location stores
and then by country-based location stores.
(c) There are three main level of wastage highlighted in the given sample which are high,
medium and low. The majority of the sample supermarket stores are falling in the medium
level of wastage. Further, there are 30.67% of the supermarket stores which belong to the
high level of wastage category which reflects a serious concern that needs to be addressed.
Also, there are only 15.30% of the stores in the sample which are classified under low level
wastage category. It indicates that significant proportion of the supermarket stores in sample
are classified in either medium or high wastage category which can be a serious issue and
hence, prudent measures need to be taken into account so as to minimize this number to a
significant extend. Further, the maximum level of contribution in the high level of wastage
category comes from states of Queensland, SA and Vic in descending order. Moreover,
around 50% of the supermarkets with high wastage level belong to these states only. One
potential reason behind it is that these three states may have maximum number of
supermarket stores in the sample data.
(3) The main objective of this analysis is to ascertain the qualitative variables which can
provide satisfactory explanation for the variation of the sales. Also, the key variable that most
significantly impacts the sale would also be determined based on the relative significance of
the four variables under consideration. Advertising expenses and number of staff in the
supermarket store are the two potential variables which significantly affect the sales. Further,
the remaining two variables, car space and trading hours are not showing any significant
association with the sales. Nearly 70.60% of variation in the sales can be explained by
variation in the advertising expenses. The percentage was expected to be quite high because
as per the supporting literature, the products with high expenditure on the advertisement
would increase the sale. Further, 54.1% of change in the sales can be determined by the
change in the number of staff and hence, it is also taken as part of the significant explanation
variable. Similarly, change in the variables car space and trading hours can define only 32%
and 0.6% of variation in the sales respectively and hence, cannot be considered as significant
variables. Therefore, it can be concluded that from the statistical analysis of the four variables
provided, the most significant variable with regards to accounting for sales variation is
expenses on advertisement.
(4) (a) In accordance with the relevant statistical computation, it may be estimates that annual
online sales of a randomly chosen FOODplus supermarket would lie between $ 0.3 million
and $ 0.42 million. Taking the annual sales for the supermarkets into consideration, it can be
concluded that the online sales are currently at a very low level which provides an exciting
opportunity to the company for enhancing future sales.
3

(b) The analysis of the sample data highlights that 9.57% to 21.1% of 750 FOODplus
supermarkets will be classified as low wastage. This value is clearly low and hence provides
opportunity for improvement in the sphere to waste management in near future. Also, in the
context of NSW based FOODplus supermarkets, 5.90% to 29.99% will be classified as low
wastage. Further, in context of Victoria based FOODplus supermarkets, 3.18% to 29.08%
will be classified as low wastage.
(5)(a) A key requirement of ACCC is that the increase in food price during 2017-2018 should
not be greater than the underlying inflation. In order to fulfil the same, the average price
annual increase should not be higher than $ 6.85. The statistical testing of the sample data
indicates that FOODplus supermarkets are in compliance with ACCC as the average annual
increase does not cross $ 6.85.
(b) For long term sustainability, a key requirement is that atleast 70% of the FOODplus
supermarkets must be working on Sunday. Based on the statistical testing conducted taking
the sample data into consideration, it becomes evident that less than 70% of FOODplus
supermarkets are working on Sunday which is a cause for concern and hence company needs
to take steps to address the same in a prompt manner.
(6)(a)(i) Reliable conclusions can be drawn based on an unbiased sample. In the context of
the survey data presented, it would be expected that the various states are fairly represented.
In the absence of additional information about the supermarket distribution, it is fair to
assume that uniform distribution of FOODplus supermarkets exists across the various states.
Since there are eight states included in the survey data, hence 12.5% of the total sample size
should belong to each state. But this is not true as is apparent from Queensland which
contributes 18% supermarkets and hence has higher representation than desired. On the other
end, there are states namely Tasmania and Western Australia where the supermarket
contribution is 3% and 11% respectively leading to lower representation in the sample. Thus,
the given assertion about states being misrepresented in the survey data seems true.
(ii) The maximum age in the sample being 24 years should not be treated as anomaly as the
expansion of supermarkets is gradual only and even though the company started operations
27 years ago, the presence in the initial few years would be thin. As a result, during the
selection of survey sample, it is highly likely that 150 supermarkets have been randomly
selected and do not constitute any supermarket with presence for greater 24 years.
(b) The key objective here is to ascertain whether the current sample size would be sufficient
with regards to specific objectives for future surveys. In this regards, the existing sample size
is found enough for estimation of gross profit with a margin of error of $ 120,000 as only 148
sample values are required. However, in the context of estimation of high wastage proportion
with a margin of error of 6%, 150 sample size is not acceptable as minimum observations is
227 and hence a larger sample would be deemed necessary.
4