OpenTable was the first mover in the world of booking orders for restaurants around the globehttps://www.vox.com/2018/10/19/17698428/resy-restaurant-reservations-startup-opentable. After obtaining notoriety, they issued a massive IPOhttps://www.reuters.com/article/us-opentable-idUSTRE5464CJ20090507 andhttps://press.opentable.com/news-releases/news-release-details/opentable-inc-prices-initial-public-offering/ resulting in a stock price….
Management of Information Systems
ISM6026 – Management of Information Systems
Class Assignment 2
Data Analysis using Pivot Tables
Given: Customer survey data of a credit card company (Excel dataset provided). The credit
card company is working towards ensuring that it remains profitable, successfully collects its
debts and attracts new customers.
Task: You are manager of the CRM (customer relationship management) division. Perform data
analysis on the given data. You need to determine the type of customers to focus on in order to
fulfill the company’s goal of ‘strategically attracting new customers’.
Perform data analysis, determine the following by creating their visualizations (plots/graphs):
Give appropriate ‘Chart Title’ and the x and y axis titles. Points will be deducted for missing
1. How many customers based on their marital status have
a. Good and Bad credit standing (2 points)
2. How many Male and Female customers have
a. Good and Bad credit standing (2 points)
3. What results can you see on the credit standing (good and bad) based on:
a. Employment and Housing criteria (2 points)
4. Provide another visualization plot using any other criteria (not used in the above plots)
you would like to analyze on this data and add 2 useful ‘Pivot Slicers’ (Remember Pivot
Slicers can be fields other than the ones on the plot axis)? (3 points)
5. Provide ‘conclusion’ on which type of customers would you like to focus on attracting in
future, to fulfill the company’s goal. Explain in at least 5 to 6 sentences based on the
findings from all the plots above (1 point)
Upload the Excel spreadsheet with the visualizations (plots). Provide the ‘conclusion’ on a
separate tab in the same spreadsheet.
TUTORIAL (if you need help)
Tutorial: Analysis Steps to presenting visualizations (plots/graphs) using Excel:
a) For Task 1 you can follow these steps. Download the excel spreadsheet from Canvas
b) Open the spreadsheet and select all the data (CTRL+A) as shown and select Pivot
Table from Insert tab on the ribbon as shown
c) Click ok here
d) This creates a Pivot table on a new spreadsheet. Now select the following 2 items
(Personal Status and Credit Standing) to build the Pivot Table and drag both the fields
as shown to prepare the table
e) Your spreadsheet will then show a pivot table on left side. Note: If the Pivot table
fields menu on the right side disappear then you can click on the Pivot table heading
‘Count of Credit Standing’ in this case – for it to appear again.
f) Now click on Pivot chart and select the ‘Column’ plot
g) Pivot chart is created
FORMATING – Now to add custom and meaningful titles. Click on the chart and then click on
‘Design’ tab then Add Chart Element as shown that lets you edit the Titles.
Add an appropriate Title
Follow similar steps as shown above to create Pivot charts for other questions.
Pivot Slicers for Question 4
Pivot Slicers – are a visual filter in form of a button for quick and easy filtration of Pivot data.
Click on the Pivot chart first and then click on Pivot Slicer which gives you the option of
selecting the filter field.
JUST AN EXAMPLE… shows how to add slicers. DO NOT add it on the chart shown. You
have to create your own chart as mentioned in question 4 and then add.
Ignore the numbers you are seeing here – you might get different total values .. that’s ok!
Once field is selected it creates the Slicer and the Pivot table data can be filtered using that
Slicer. In the screenshot below 2 slicers were created (because 2 were selected from above ‘insert
FYI only – Question 1 plots – Incase you need to create separate plots for Bad/Good credit
then you can filter the ‘credit standing as shown.
On the filter select Credit Standing of ‘Bad’
This filters the pivot table and now shows only the people with ‘Bad’ Credit standing. Note the
total number went down compared from before table