Reference no: EM131663012

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:

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.

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