Executive Summary

A case study about YHG (Your Home Grocer) is presented, which in Australia is counted as one of the top supermarket chains. Today, all most all the businesses are influenced by online platforms and at a point they wish to expand their business and make it available online. By doing so, the business becomes open to all the consumers and increases the sales as it is more convenient to shop online. YHG has plans of expanding its business too and intends to conduct 5 years strategic planning by their executives, where they consider shopping behavior of their consumers, to come up with a better plan that can increase the profit. All this expansion is because during COVID-19 pandemic, the sales of online grocery doubled. So, YHG intends to expand further through online channel. The aim is to invest and develop data analytics capabilities to combine data analytics and retail capabilities in the organisation, and present a centralised business unit, which can help all the business units in the organisation i.e., from inventory to new product development. The objective is to find the advertising expenses and gross profit for online space/channel for YHG chain, and determine if the claim of having a proportion of high-level wastage when compared with the competitors in the sector are true or not. The results of this study showed that the claims of high-level wastage proportion of YHG is not true.

1.Background Business Problems

Based on the Finder Consumer Sentiment Tracker, on an average $157 is spent on groceries each week by the Australian household. But, when compared to women, men are seen to spend a bit more. For retaining the market power, the supermarkets have come up with innovative ideas that gives them market stability and gain market dominance when compared to the competitive local supermarkets.

In Australia, YHG is counted as one of the top supermarket chains, and it is estimated to have 100,000 staffs. YHG’s Head of Marketing, Katrina de Jong has contacted the newly created Data Lab, for analyzing the obtained data from its annual store survey and shared the department results. Here, a random sample of 350 stores will be extracted and analyzed as a data analyst named Misha Toutou, who works for Data Lab.

The problem is that during COVID-19 pandemic, the sales of the competing online grocery doubled, but the problem is that YHG does not have an online channel. And hence the issue is online surge. And various supermarket chains are seen to struggle for producing considerable profit (McMorrow & Eley, 2020). So, YHG intends to expand further through online channel.There are several questions related to the annual store survey which the head of the marketing need to know and they are about advertising expenses and gross profit for its online channel, and determine if the claim of having a proportion of high-level wastage when compared with the competitors in the sector are true or not. In addition, determine the difference in advertising expenses depending on the online channel and location. Moreover, all these determinations include various challenges, so it is not easy especially for the newly created Data Lab to effectively analyze the extracted data from its annual store survey and present correct results.

2. Methodology

The selected methods for this data analysis are explained as follows:

Descriptive Statistics

In the dataset, the statistical data is summarized with the help of descriptive statistic, which uses the available information. This technique helps to have different types of output as options. In the ‘Data Analysis’ group of ‘Data’ tab, Excel Descriptive Statistics function is an already present inbuilt tool that can be seen. The statistical output has numbers which explain the data properties. And, when they share beneficial information, the charts are always highly insightful. It is always a good practice to utilize the graphs and statistical results, as they are usually combined for maximizing the understanding. In this case, the advertising expenses at YHG will be summarized (Frost, 2022).

T Test

T-tests can be referred as the hypothesis tests, which can evaluate the means of one or two groups. Sample data is used by the hypothesis tests for infer properties of the entire populations. For using t-test, it is essential to have arandom samplefrom the target populations. Based on the t-test, it helps to configure and test them to determine the following (Frost, 2023):

• The paired means are different.
• The means of the two groups are different.
• One mean varies from the target value.

In this case, T test will be used to determine the true differences in advertising expenses with the location and online channel.

Multiple Linear Regression

In statistical modelling,regression analysisis utilized for evaluating the relationship between two or more variables, such as:

• Dependent variable or criterion variable: It can be referred as a key factor that is being tried to understand and predict.
• Independent variablesorpredictors, or explanatoryvariables:It can be referred as the factors which can influence the dependent variable.

Regression analysis is beneficial to make the researcher understand the change in the dependent variable when one of the independent variables differs and permits to mathematically identify the variables that actually poses an impact. In statistics, they are vary based on a simple linear regression and multiple linear regression. The simple linear regression models relationship between the dependent variable and an independent variable with the help of linear function.Whereas, when two or many explanatory variables are used for predicting the dependent variable, it denotes multiple linear regression.In case a dependent variable is modelled as a non-linear function, as the data relationships fails to follow the straight line, then it is required to utilize the nonlinear regression(Cheusheva, 2023).

In this case, multiple linear regression will be used, because it is required to determine the factors that are affecting the YHG sales. Here, the sale is a dependent variable and Number of staff, age of stores, competitors, manager experience, customer rating, store location, car spaces, and online channel are all the independent variables. Based on the selected variables, multiple linear regression must be done, which will help in finding out the factors that are affecting the YHG sales.

3. Findings

In this section, the following analysis is completed and interprets all the findings with the help of Microsoft Excel.

In this analysis, the advertising expenses at YHG company must be summarized and also determine the proportion of advertising expenses below $100,000. Begin with, summarizing the advertising expenses with the help of descriptive statistics on Microsoft Excel. The result of the Overall Summary Statistics of the Advertising Expenses is tabulated below.

According the above result, the average of advertising expenses is $147.99, minimum cost is 20, and maximum cost is 369. The total advertising cost for YHG is $51797.

Calculate the Proportion of Advertising Expenses

Total Advertising Expenses = $51978

Proportion of Advertising Expenses below $1,00,000 = 51978/1,00,000 = 0.51

In this analysis, any differences in advertising expenses will be determined based on the location and online channel. To do this analysis, T test hypothesis testing is used. Before conducting this test, it is necessary to change the online channel yes as 1, no as 0, at the same time location must be changed as follows:

• Country - 1
• shopping centre - 2
• Strip – 3

Because, the T-test requires numeric values determine the differences between the variables. The next step of this analysis is to formulate the hypothesis.

• Differences in advertising expenses based on the location
  • H₀: There are no true differences in advertising expenses and Location.
  • H₁: There are true differences in advertising expenses and Location.
• Differences in advertising expenses based on the Online channel
  • H₀: There are no true differences in advertising expenses and online channel.
  • H₁: There are true differences in advertising expenses and online channel.

The T test result for the differences in advertising expenses based on location is tabulated below (Frost, 2023).

The result shows the mean for advertising cost as 147.994 and for the location it shows 1.934285714. Based on the Variances row, it is observed that both the variables are not equal. The highly significant statistic is p-value, because when this value is lower than the significance level, then the difference between the means is said to be statistically significant. for both one-tailed and two-tailed t-tests, Microsoft Excel can give the p-values. One-tailed t-tests is capable of identifying the differences between the means in just one direction, for instance, the one-tailed test can just identify if the Location is larger than Advertising or not, whereas the two-tailed tests is capable of identifying the differences in direction i.e., is larger than or lesser than.

For this output, P(T<=t) two-tail will be used, and it resembles the p-value for the two-tailed form of t-test. This is due to the p-value i.e., 0.000, and it is lesser than the standard significance level (i.e., 0.05), and as a result reject the null hypothesis. Hence, it is stated that there are true differences in advertising expenses and Location.

The T-test result for the differences in advertising the expenses based on the online channel is tabulated below.

The result shows the mean of advertising cost as 147.994 and 0.7 for the online channel. Based on the Variances row, it is observed that both the variables are not equal. Thu, P(T<=t) two-tail can be used, which acts as p-value for the two-tailed form of t-test. Due to the p-value displayed as 0.000, which is lesser than the standard significance level (i.e., 0.05), the null hypothesis has to be rejected. Therefore, it is determined that, there are true differences in advertising expenses and online channel.

In this analysis, the executives are interested to determine the gross profit and advertise the expenses in online space for YHG chain, because, their focus is to further expand the online channel due to the impact of COVID-19. Before doing this, it is essential to determine the total count of online channel stores and offline channel stores, as tabulated below.

Based on the above table, the total number of online channels are 245.

a)

Now, the proportion of all the supermarkets that have an online channel is determined.

Proportion of Online Channel = Total online channel/ total supermarkets.

=245/350

= 0.7

b)

Next, the average gross profit all the supermarkets that have an online channel is determined as 0.900.

c)

Average of advertising expenses for all the supermarkets that have an online channel is found to be 152.0163.

In Australia, one of the leading sustainable organizations is the YHG brand, but the recent industry report has claimed that YHG has at least 40% of proportion of high-level wastage when compared with the other competitors in the industry. Thus, it is necessary to check if this claim is true for all the supermarkets or not. To determine this, begin with determining the total count of wastage for high, low, and medium as tabulated below.

The Proportion of Wastage is calculated as tabulated below.

The figure 1 depicts the visualization of YHG wastage proportion.

Figure 1

According to the result, the proportion of high-level wastage of YHG is 31%. The recent industry report is not true, because the high-level wastage of YHG for all the supermarket is 31%. Therefore, it is allowed to claim a high-level wastage of YHG, cannot not be true, which has less high-level wastages.

In this analysis, one of predictive analytics will be used to determine what the factors that are affecting the sales at YHG. The selected predictive analytics is multiple linear regression on Microsoft Excel. To do this, select the sale as a dependent variable and Number of staff, age of stores, competitors, manager experience, customer rating, store location, car spaces, and online channel as independent variables. The result of multiple linear regression is presented below.

Here, the result of the created linear regression model helps to interpret the results, where it shows the value of R2 as 0.73, and the value of adjustedR2 is shown as 0.73, and it clearly indicates independent variables to present 73% variability to dependent variable i.e., the YHG Sales. Next, from the linear regression model, statistical significance is identified. Accordingly, the F value is 120.93, and p-value is 0.00. It assists to show the linear regression model’s successful prediction of statistical significance for the dependent variable. And, 0.05 is the displayed statistical significance of this model. Thus, the null hypothesis is forcefully rejected and as a result the alternative hypothesis is accepted. Further, from the coefficient table, the number of staff, competitors, manager experience, store location, and online channel have p-value as 0.00, which indicate that these factors are affecting the YHG sales. At the same time store ages, customer rating and car spaces are not affecting the YHG sales.

4. Recommendations

It is recommended to improve its adaptation to the new online world, i.e., YHG must work on adopting to the new model of online channel and train its staff members to use the platform effectively.

Slowing the profit will improve if the marketing strategies are well analyzed and implemented strategically to attract the consumers.

YHG must also work on perfecting its online operations, while simultaneously working on meeting the ecommerce demands.

It must hire professionals who can guide them throughout all the online processes.

Effective marketing strategies are essential in online channels to increase the sales. Thus, the main focus must be on marketing, as it has high changes of increases the sales.

The discounts and prices must be planned effectively, as it also contributes a lot to the surge in sales.

5. Conclusion

The analysis is completed successfully, where the results are interpreted with the help of Microsoft Excel. The determined problem due to COVID-19 pandemic impacting the surge in the sales of online grocery is considered by YHG. And the interest of coming up with an online channel for YHG is observed in this case study. The issue of online surge is tackled with the online channel and aims to improve the profits of the supermarket chains. For this, YHG developed data analytics capabilities and combined data analytics and retail capabilities in the organisation, and a centralised business unit is developed, which can help all the business units in the organization.

This report has determined the answers related to advertising expenses and gross profit for its online channel, and determine if the claim of having a proportion of high-level wastage when compared with the competitors in the sector are true or not. And, the results of this study showed that the claims of high-level wastage proportion of YHG is not true.

Are you struggling to keep up with the demands of your academic journey? Don't worry, we've got your back! Exam Question Bank is your trusted partner in achieving academic excellence for all kind of technical and non-technical subjects.

Our comprehensive range of academic services is designed to cater to students at every level. Whether you're a high school student, a college undergraduate, or pursuing advanced studies, we have the expertise and resources to support you.

To connect with expert and ask your query click here Exam Question Bank