Business Analytics MIS17 1Development of an RFM model代写
100%原创包过,高质代写&免费提供Turnitin报告--24小时客服QQ&微信：120591129
Business Analytics MIS17 1Development of an RFM model代写
Deakin's Bachelor of Commerce and MBA are internationally EPAS accredited.
Deakin Business School is accredited by AACSB.
Business Analytics – MIS171
Trimester 2 2017
Assignment 2
QUIZ DUE DATE AND TIME: Quiz opens at the completion of Week 11, on Thursday the 28 th
of September at 10AM, and closes on Sunday the 1 st of October
11:59PM
QUIZ WINDOW: The Quiz Window is 2 hours. Once you start the quiz, you will
have 2 hours to complete it.
PERCENTAGE OF FINAL GRADE: 20% of final grade
Learning Outcome Details
Unit Learning Outcome (ULO) Graduate Learning Outcome (GLO)
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.
GLO 1: Discipline‐specific knowledge and
capabilities: appropriate to the level of study
related to a discipline or profession.
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.
GLO 4: Critical thinking: evaluating information using
critical and analytical thinking and judgement.
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).
GLO5: Problem Solving: creating solutions to
authentic (real world and ill defined) problems.
Page 2 of 7
Feedback prior to submission
Students are able to seek assistance from the teaching staff to ascertain whether the assignment
conforms to submission guidelines.
Feedback after submission
In order to understand areas where improvement is required, students are expected to refer, and
compare, their answers to the suggested solutions.
General Description / Requirements / Scenario
This is an individual assignment and it focuses on materials presented up to and including Week 10.
To complete the assignment you should first analyse the dataset to answer the specific questions
that are contained in an email to you (see below). Secondly, you should interpret the results, and be
able to draw conclusions. Once you have done this, you will have the necessary output (i.e. Data
Analysis) to complete the online quiz. The quiz contains twenty (20) randomly allocated questions
that relate to the data analysis, interpretation, and drawing conclusions.
The assignment again uses the file MIS171 A2 2017 T2.xlsx which can be downloaded from
CloudDeakin. The assignment must be completed individually.
Note:
1. You do NOT need to submit any data analysis or any written work. However, you will need
to refer to your data analysis and written work in order to answer the multiple‐choice
questions successfully.
2. The multiple choice online quiz will be open for 4 days.
3. Once you start the quiz, you have a 2‐hour window to complete it.
4. No time extension is possible. That is, as this assignment is due one week before the end of
term, and as students require timely feedback, the solutions will be released very soon after
the quiz closes. If there are extenuating circumstances beyond your control, you can apply
for a special consideration. See http://www.deakin.edu.au/students/assessments/special‐
consideration
5. Assignment 2 requires that you analyse a data set, interpret and draw conclusions from your
analysis, and then convey your conclusions in a written email.
Page 3 of 7
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
Business Analytics MIS17 1Development of an RFM model代写
Duncan Brown
Page 4 of 7
ASSIGNMENT INSTRUCTIONS and NOTES
In order to prepare a reply to Duncan’s email, you will need to examine and analyse the datasets,
thoroughly. The following are some guidelines to follow.
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
Business Analytics MIS17 1Development of an RFM model代写
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
Page 5 of 7
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 R 2 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.
Page 6 of 7
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)
Guidelines for your Online Quiz
Once you have completed your data analysis you should summarise the key findings for each
question and write a response to Duncan’s questions. Before you attempt the on‐line quiz, make sure
you have a print out of your data analysis, your summaries and your responses so that you can refer
to them as required. Ideally, you should be familiar with all aspects of your assignment.
The quiz contains 20 multiple‐choice questions. Some questions focus on the appropriateness of the
technique, steps in the analysis, model coefficients, and the theoretical assumptions you may have
had to make. The rest of the questions relate to interpreting and drawing conclusions from your
analysis.
The following two sample questions are indicative of the type of multiple‐choice questions you will
receive in the quiz.
Q1. When testing the contribution of all independent variables included in a multiple linear
regression model.
a. The more independent variables that are included in the model, the need to consider multi‐
collinearity reduces.
b. The more independent variables there are, the need to consider multi‐collinearity increases.
c. Limiting the number of independent variables reduces the need to consider multi
collinearity.
d. None of the above are correct.
Q2. When testing if an independent measure should be included in a regression model, which of
the following statements is correct?
a. The larger the independent coefficient, the more likely it is to have a significant contribution
on the dependent measure.
b. If an independent measure has a non‐zero effect on the predicted variable, the p‐value will
be greater than alpha
Page 7 of 7
c. If an independent measure has a non‐zero effect on the predicted variable, the p‐value will
be less than alpha
d. None of the above are correct
Good luck everyone.
Please ask questions if you have them.
All the best,
The MIS171 Business Analytics Team
Business Analytics MIS17 1Development of an RFM model代写
