Business Analytics
Unit Learning Outcome (ULO)
ULO 2: Apply quantitative reasoning skills to analyse business performance.
This assignment assesses the ability to use the appropriate technique to analyse the data, correctly interpret the analysis output and draw appropriate conclusions.
ULO3: Create data driven/fact based solutions to complex business problems.
This assignment assesses the ability to use the appropriate technique to analyse the data, correctly interpret the analysis output and draw appropriate conclusions.
ULO 4: Use contemporary data analysis tools
to analyse business performance.
This assignment assesses proficiency in the use of data analysis tools within Microsoft Excel (one of the most widely used data
analysis tools).
Graduate Learning Outcome
GLO 1: Discipline-specific knowledge and capabilities: appropriate to the level of study related to a discipline or profession.
GLO 4: Critical thinking: evaluating information using critical and analytical thinking and judgement.
GLO5: Problem Solving: creating solutions to authentic (real world and ill defined) problems.
Scenario
You work as a junior analyst for a large consultancy company. You have been asked to complete some of the unfinished data analysis work of your senior colleagues.
Email from Duncan Brown
To: Maria Woodman
From: Duncan Brown - Advance Analytics Team Leader Subject: Analysis of Sales and Customers
Dear Maria,
As all of our clients are urgently awaiting reports, thank you for helping us finalise these two projects. I particularly need your expertise on the following:
1. Project A: Supermart sales prediction:
Please build a model to predict sales. Supermart management is very keen to understand what factors influence their sales. Your model should provide management with an ability to predict sales for various scenarios.
2. Project B: Bilka direct email marketing campaign
Please model the Bilka customer behaviour using RFM analysis. The Bilka management team is interested in the top three customer segments with the highest net revenue and their corresponding response rate to the direct email offer.
For the next direct email marketing campaign, our client would like to generate as much revenue as possible. Roughly what percentage of customers do they need to target under RFM scheme to achieve this goal?
I look forward to reading your report.
Sincerely
Duncan Brown
Task One - Development of a multiple regression model
Case Study A: Supermart
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 an online store to enable customers, in selected suburbs, to make their purchases. The data relates to a random sample of 150 stores in the Supermart chain. The survey is conducted every year.
The variables in the data set are described in 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
|
|
Car Spaces
|
The number of parking spaces available to the store
|
For this analysis, you will need to build a multiple regression model using sales as your dependent measure. You should begin by including all variables in your model, assessing the model for overall significance, then if found to be significant, removing variables that are not contributing (if there are any) to a change in the dependent measure one at a time by conducting a series of t-tests with alpha set at 0.05.
In particular, you should at least consider following questions:
a) Which independent variable has the strongest linear relationship with sales
b) Is your multiple regression model overall significant?
c) If so, which variables do not help you in modelling the dependent measure?
d) Once you've built your final model, are there any potential multi-collinearity problems? If so, which variables are they? (If there are collinearity problems between the independent measures, you should firstly remove the variable that has the "least correlation" with the dependent measure, then run the model and assess again).
e) How well does the model explain sales (use R2 in your explanation)?
f) What would be the sales for an 8 year old store with 60 staff and 80 car spaces that is open for 100 hours per week including Sunday, managed by a 37 year old male manager with seven years of experience, that pays $2.6 million on wages, spends $150,000 on advertising, reports $1 million gross profit, with three competitor stores?
[Note, only use the values that you have found to be significant (α set at 0.05) contributors to the behaviour of the dependent measure].
Task Two - Development of an RFM model
Case Study B: Bilka
Bilka is an online retailer providing a wide range of products (from big name brands to exclusive products) to consumers all around Australia. Bilka encourage customers to register their email to receive regular sales and special offers. The retailer has a very large customer base and for this study a random sample of 4,338 customers has been selected.
|
Variable Name
|
Description
|
|
Customer ID
|
Unique ID of the customer
|
|
Elapsed Time (in Days)
|
Elapsed time since a customer last placed an order with the
company
|
|
Transaction Count
|
Number of times a customer orders from the company in the defined period
|
|
Monetary Value ($)
|
Amount a customer spends on average per transaction
|
|
Responded to last campaign
|
0 = Customer did not responded to the direct email marketing campaign; 1 = Customer responded to the direct email marketing campaign
|
|
Cost per email
|
Cost per each direct email to the customer
|
|
Recency Score
|
Coded value for elapsed time
|
|
Frequency Score
|
Coded value for number of customer orders in the given period
|
|
Monetary Score
|
Coded value for the average customer spend
|
|
FRM Score
|
Final synthesised score of the RFM analysis
|
|
Net Revenue (campaign)
|
The monetary amount if the customer responded to the previous campaign less the cost of the direct email marketing per customer ($1). If the customer did not respond then the net revenue would be the direct email marketing cost ($1).
|
Here, you will need to create three new measures that will contribute to the creation of a single new measure called the "RFM" (Recency, Frequency, Money) coded sequence.
- For each measure (for example, recency measure) divide the customers into three equal groups and assign a numerical code (1 to 3) for each group.
- Repeat the coding process for Frequency and Monetary measures.
- After coding is complete, combine the three measures to derive the RFM score for each customer.
For example, a customer who has not shopped Recently (lowest 1/3 of observations), shops with the lowest Frequency (lowest 1/3 of observations), spending the least amount of Money (lowest 1/3 of observations), will have an RFM score of 111.
You should consider following questions:
- What is the total net revenue attributable to the campaign of all customers for the period the data covers
Based on the net revenue generated from the campaign:
- What is the net revenue generated by the various RFM segments.
- What are the 5 top total revenue generating RFM segments that we should target in our next email sales campaign?
- What is the response rate of each RFM customer segment (Hint: You could use a pivot table to summarise customer segments)
Attachment:- Data.xlsx