Reference no: EM131192094 , Length:

Assignment

Learning Outcome

L1: Create solutions by applying data analysis and statistical techniques and skills to practical business, economic and personal decision-making and research.

L2: Use computer software to analyse data and understand the limitations of such software.

L3: Deduce clear and unambiguous solutions in a form that they useful for decision making and research purposes and for communication to the wider public.

L4: Appraise the accuracy, sources and relevance of statistical data and models.

L5: Create and appraise solutions, reports and arguments based on statistical data and models.

Case Study

Supermart is one of Australia's leading supermarket chains. There are 700 stores in the chain. Originating from a family based chain of general stores, Supermart now has stores all over Australia, with the first one being established 27 years ago. In 2015 the company launched its online store to enable customers, in selected suburbs, to make their purchase online. In terms of operation, each state capital has a company office and they have significant autonomy in the individual state's operations. Further, individual store management has wide-ranging powers about day-to-day operations of their stores. However, broad company planning and direction take place in the company Head Office in Melbourne. Included in the Head Office, is the Research and Analysis Department.

Having compared the company's position in the market and against the competitors, the General Manager - Michael Braithwaite has asked you, Ruby Rose, to conduct exploratory and descriptive analysis to gain a better understanding of Sales of Supermart.

The Data

The data relates to a random sample of 150 stores in the Supermart chain. The survey is conducted every year to gather information that is not readily available from the central sources. The variables in the data table are described below:

Variable Name	Description
Store No.	Unique ID of the store
Sales $m	Total Sales revenue for each store for the financial year ($  million)
Wages $m	Total Wage and salary bill for the financial year  ($million)
No. Staff	The number of effective full-time staff employed on a weekly  basis
Av. Wage	The average annual wage/salary per effective full-time staff   member.
GrossProfit $m	Gross Profit for each store for the financial year ($  million)
Adv.$'000	Advertising and promotional expenses for the financial year  ($'000)
Competitors	The number of competing stores in the consumer catchment  area
HrsTrading	The total number of hours open for trading per  week
Sundays	Open on Sundays; Close on  Sunday
Mng-Gender	Male store manager; Female store  manager
Mng-Age	Age of the store manager, years
Mng-Exp	No. of years of experience in some form of junior/senior management at   Supermart
Mng-Train	No. of management training courses taken while employed at  Supermart
Union%	The proportion of the staff at the store which belongs to a  union
Car Spaces	The number of parking spaces available to the  store
Online Channel	Whether or not the store has an online  channel
OnlineSales$m	Sales revenue from Online Sales Channel ($  million)
Basket:2015	Cost ($) of the basket of food items in each store at 1 June  2015
Basket:2016	Cost ($) of the basket of food items in each store at 1 June  2016
%Change	Annual percentage increase of the cost of the basket of food items in each  store

Time Period	Time Period Index
Quarter	Quarter Description
Total Sales($m)	Total Sales of Supermart in $m

Task One - Summary of Sales

Only analyse Sales by itself. The importance of other variables is considered in other tasks. You should, at the very least, thoroughly investigate relevant summary measures (and their reliability) for this variable. Also, there may well be suitable tables and graphs that will illustrate, further and more clearly, other important features of sales.

In your report you should comment, where relevant, on data location, central tendency, variability, shape and outliers for this variable.

Reference: Module 1 - Topic 2

Task Two - Factors influencing Sales

Analyse Sales of stores against other variables included in the data set. Use appropriate descriptive techniques such as cross-tabulations, comparative summary measures, scatter diagrams to explore key relationships.

In your report you should only include the most important factors that impact Sales.

Reference: Module 1 - Topic 3

Task Three - Development of a multiple regression model

You should follow the model building process outlined in topic 5. You are only required to consider linear relationships in the model. Each stage of developing your model should be included in your analysis. You will notice in the Supermart Excel file that there are separate worksheets (tabs) named Q3-1, Q3-2, etc. These are where you place each version of your model. Note that if you have undertaken more iterations of the model then add more worksheets as required. Minimum requirements for the final model:

- At least two independent variables
- One categorical independent variable

In your written report, please provide an interpretation of all elements of the final model - (for e.g. a practical interpretation of the regression equation, description of the explanatory / predictive power/ overall strength of the models as well as the significance of each individual independent variable.)

Task Four - Time Series analysis

Quarterly Total Sales for Supermart from Q2, 2012 to Q1, 2016 are given in the QtrSales worksheet. Develop a multiplicative time series model to forecast Sales for the next 4 quarters (Q2, 2016 to Q1, 2017).

If the observed values for those 4 quarters are as below, calculate the MAPE of the forecast.

Time Period	Quarter	Observed
17	2016-Q2	95
18	2016-Q3	132
19	2016-Q4	150
20	2017-Q1	109

In your written report, please provide an interpretation of the final model - (for e.g. a practical interpretation of the time series model, measures of forecast accuracy, choices about exponential smoothing constant).

Task Five - Critique the Business Research Approach

Discuss the suitability of the general business research approach taken. In your response, include possible alternative approaches and other sources of (secondary) data. If the analysis was to be repeated in the future, would you recommend a different approach?