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代写