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?