Using the data provided, answer the following questions

Description

I have attached all the files needed.

MKT356Take Home MidTerm ExamSpring 2018sec1 is the main document you will need.

RFM_ExcelWay_Hints18 (1).docx is just a optional document just for hint.

MKT 356
Take Home Midterm Exam
Spring 2018
Instructions:
1. Open notes/book, but independent work is required. You may work with other
students on this exam. However, each individual must turn in their own exam.
Plagiarism will result in a mark of zero for the exam, and will be reported to the
department.
2. Please support your claims with proper charts or exhibits – remember you are
talking to a business audience. If you show a chart that is with too much
information or not readable, no marks can be given. One or two charts are
expected for each question, except for Part B q1 and q2 (For these two
questions, you may just say “Done. See sheet2 in the file xxx.xlsx.”).
3. An appendix of how you construct each exhibit is required. An example of such
appendix is on Canvas. Please put the appendix at the end of your word
document.
4. Your work is due 5pm 3/20/2018 on Canvas. Please submit a word document
(put the Appendix mentioned above at the end of the document – don’t put
them in the text), and other supporting twb files and/or excel files. Please save
your Tableau charts as images and paste them in the proper places in your word
file. I will be grading your word file ONLY.
5. No re-do is allowed for the Midterm Exam.
Using the global_superstore.xls data (the “Orders” sheet, unless instructed otherwise),
answer the following questions.
Part A [50 marks]:
Compare profitability for the Africa, LATAM, and the US market.
1. Describe what this dataset is all about. (It is about How many consumers’
purchasing records, where, when, and in what product categories?) (5 marks)
2. Comment on the overall profit of these markets. Together with their sales, what
would you say? (5 marks)
3. Now add product category information to the picture. Comment on profitability
again. (5 marks)
4. Take the US market as an example, see whether your comments in question 3
would apply on a year to year basis or not. What do you say now? (5 marks)
5. Bring variable “Discount” to the picture. Do you see discount done differently in
these markets, in each category? (5 marks)
6. Bring variable “Shipping Cost” to the picture. Please comment on how shipping
cost may affect profitability in each category. (5 marks)
7. Now Open the data in Excel. In the “Orders” sheet, create a new column, and call
it Returned. Use the vlookup function, and the OrderID as identifier, to bring in
information for “whether the order is returned or not” from the “Returns” sheet.
Then create a blank Tableau workbook and connect the data again. Compare two
economic aspects of the returned orders to those that are not returned. Comment
on the results. [Hint: you may consult hw3.] (15 marks)
8. Based on what you have observed so far, can you make some conjectures and
recommendations for each market, in terms of how we may improve its
profitability? Please support your claims with facts that you commented on
previously. (5 marks)
Part B [40 marks]:
RFM is commonly used in marketing, retail and professional services industries to assess
customer value. The general idea behind the analysis can be summarized as:
Recency: People who have purchased recently from you are much more likely to respond
to a new offer than someone who you haven’t sold to in a long time.
Frequency: People who shop frequently at your store are more likely to respond to new
offers than less frequent buyers.
Monetary: People who spend more money at your store are more likely to show interest
in new offers.
They are defined as:
Recency: How long ago did the customer make a purchase (usually measured by days)?
Frequency: How many purchases has the customer made (during the last year or other
time period)?
Monetary Value: How much has the customer spent in total (in given time period)?
1. Please set 2014 data aside, and use 2011-2013 data (the end of year 2013 as cutoff
point for recency), and compute the RFM for each customer. Your resulting
database is one with 795 customers and four variables: Customer Name, Recency,
Frequency, and Monetary. (5 marks)
2. For each of the R, F and M variable, create a code for each customer by first
sorting the variable, and then assigning group membership 3 (the most
recent/most frequent/spent the most), 2, or 1 to each customer. The three groups
are equal size (in this case, 1/3, 1/3, and 1/3). Now your RFM database has 6
variables (with customer names, it would be 7 columns). And you can bring total
profit for each customer in (the 8th column) to add additional information for the
analysis below. (5 marks)
3. For each of the R, F and M variable, analyze profit by groups. Did you see a
descending order of profit for group 3, 2 and 1 in year 2011-2013? (15 marks)
4. Repeat what you did in question 3 using profit of 2014. Now, out of the R, F and
M variable, which one do you think have the most importance in predicting outof-sample profitability? (5 marks)
5. Use the concatenate function in Excel or in Tableau to create a three digit code:
The first digit for R, The second digit for F, The last digit for M
Every customer is either a 333, 332, 331, …, down to 111—altogether 27 RFM
cells.
And comment on profitability across these 27 groups again. Do you see the
difference for in-sample and out-of-sample profitability? (10 marks)
Part C [10 marks]: Write the step-by-step appendix for each of the above exhibits or
tasks, for both Part A and Part B.
Appendix –– RFM part of the midterm
[note: This file is written by some past student of this class and I think it is
reasonably informative, and some of you may find it useful. It is natural that it
may be confusing for you, because it is written by someone else. You should
write your own version of the step-by-step Appendix to earn credit. You will
need to understand what is behind the RFM Analysis and create your own step
by step procedure to finish the analysis.]:
It is NOT an instruction. It is just a reference file for you that you may or may
not find useful –In this latter case, you should disregard this file and create
your own version.
Set data up to find RFM
1. Open up global superstore file on excel
2. Create a new sheet
3. Copy customer name, order date, profit and sales onto new excel sheet
4. Paste them
Separate 2014 data for later use
1. Sort by order date
2. Sort - order date  newest to oldest
3. Cut and paste onto new sheet all the 2014 data to a new sheet
4. Name new sheet order14, and rename the old sheet order1113
Find RFM using sheet order1113
1. Select all data
2. Insert pivot table
3. On the pop-up window, select new worksheet
a. Insert fields
i. Customer name— insert into rows
ii. Sales insert into values, default is sum
iii. Profits inset into values, default is sum
iv. Duplicate sales in value and change sum to count, it would
then be “count of sales2” – this is frequency
4. Creating the M and F: Create a new sheet call it M by copying the above
aggregate data from the pivot table, and Create another sheet call it F
a. Copy all data post it in M delete ‘count of sales2’ to avoid confusion
i. Data sort —-sum of sales—values—-largest to lowest
ii. Make a new column and call it Mcode
iii. Divide Rows into 3 groups of 265 each
iv. Highest sales is group 3, med is group 2, lowest is group 1
b. Copy all data post it in F
i. Data—–sort—–count of sales2——values largest to
smallest (highest is group 3)
ii. Make a new column and call it Fcode
iii. Delete sales column to avoid confusion
iv. In Fcode create the groups, divide the rows into 3 groups of
265 each
v. Highest count is group 3, med is group 2, lowest is group 1
5. Now open a new sheet call it R
i. Copy data from order1113
ii. Delete irrelevant info ( sales and profits, we only need
order date and customer name)
iii. Date—–sort—-customer name—-a-z and add a second
level sort of Order date- newest to oldest
iv. In a new column, type in Formula =if(A2A1,B2, “na”)
v. Double click formula
vi. Name column latest
vii. Copy the latest column to new column to get rid of formula,
use “paste special”, and select “values”. Format it back to
“Date”.
viii. Call this new column latest2.
ix. Sort by lastest2, and delete all the “na” rows.
x. Take a look to see now this sheet only has 795 rows of
data.
b. Create a new sheet and call it RFM
i. Sort all other sheets by customer names, including the pivot
table that has the sum of 2011-13 profit.
ii. Copy all R, F, and M data to new RFM sheet
iii. You can verify by using a formula =if(C2=A2, 0, 1), and
make sure the sum of this column is 0. [suppose C and A
column have the customer names from different sheets]
iv. Get rid of redundant information
v. You must concatenate to get the three-digit RFM code:
=concatenate(D2,E2,F2) [suppose column D E and F
are the Rcode, Fcode, and Mcode]
c. Take a break
d. Do a pivot table for 2014 order data to get sum of profit
i. Create a pivot table
ii. Put customer name and profit
iii. Copy pivot data to RFM sheet
iv. Verify that customers names are right from the copied data
by using formula =IF(a2=f2,0,1). Select this column to see
whether we have 795 cases matched or not.
v. You will probably be missing a customer called Nicole bre
vi. Manually insert 2014 profit for this person – zero.
vii. Now you can delete extra info and final sheet will have
profit for 2014
Using RFM to see group profits:
6. In Tableau, import new data sheet we just created: the RFM sheet.
7. Once data is in, convert rcode, mcode and fcode to Dimension, and create
the relevant charts.
8. Now comment on what you see, to get credits on correctly and
meaningfully commenting your charts in a business context.
a.pdf – Adobe Acrobat Professional
X

File
Edit
View
Document Comments Forms Tools Advanced Window Help
Exhibit
1. Drag Revenue Mesure to Rows (drag channel dimension to color)
2. Drag Revenue Measure to Rows (delete colored breakdown to second
option)
Drag Order Date Dimension to Column (Click pill and change to month)
4. Click Y axis and select dual axis, and then click right axis and select
synchornize axis.
Exhibit II
1. Click latitude and longitude measures.
2. Move Revenue measure to color shelf.
3. Move state dimension to detail shelf.
Exhibit III
1. Move date dimension to column.
2. Move Revenue measure to rows.
4. Move Channel Dimension to Column.
5. Move Revenue measure to label shelf.
5. Click Analysis > Forecast > Show Forecast
Exhibit IV
1. Move channel dimension to columns.
2. Move Revenue measure to rows.
3. Move channel dimension to color.
4. Go to “Show Me’ and click box plot
Right click axis and go to add reference line. Select appropriate labels.

Purchase answer to see full
attachment

We offer the bestcustom writing paper services. We have done this question before, we can also do it for you.

Why Choose Us

  • 100% non-plagiarized Papers
  • 24/7 /365 Service Available
  • Affordable Prices
  • Any Paper, Urgency, and Subject
  • Will complete your papers in 6 hours
  • On-time Delivery
  • Money-back and Privacy guarantees
  • Unlimited Amendments upon request
  • Satisfaction guarantee

How it Works

  • Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
  • Fill in your paper’s requirements in the "PAPER DETAILS" section.
  • Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
  • Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
  • From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.