Select Page

# Case 3 – Extreme Mountain Bikes Data Analysis

Subject: General Questions    / General General Questions
Question

Q

Case 3 – Extreme Mountain Bikes Data Analysis with Pivot Tables

Introduction

After completing your initial analysis of the data mart using SQL, Bob would you like you to Perform a more detailed analysis using Microsoft Excel Pivot Tables. Pivot Tables are used to summarize large, complex data sets efficiently.

The Problem

The initial analysis performed using SQL was a good start, but it did not provide the level of detail Bob needs to make strategic and managerial decisions to improve the performance of his organization. He would like for you to conduct a more detailed analysis of EMBs (1) customers, (2) products, and (3) salespeople using Microsoft Excel Pivot Tables. Bob and EMB have requested your assistance with analyzing the data to identify patterns and trends and make recommendations that can be used to improve the overall performance of EMB.

EMB would like for you to perform a detailed analysis of the data contained in SAP using Microsoft Excel Pivot Tables. Before creating the Pivot Tables, you must export the result set of query 6 in Case 2 to a Microsoft Excel Workbook. To export the query 6 result set, run query 6 by double clicking on it and export the results to Excel using the “Export to Excel” button on the “External Data” tab of Access. Save the exported data as “EMB Pivot Tables.xlsx”. The pivot tables described below should be created from the data you export and all pivot tables should be contained in the same workbook (i.e. EMB Pivot Tables.xlsx”) with each pivot table listed as a separate tab.

Creating Pivot Tables using Excel

Pivot Table 1 – Customer Sales by Product

EMB would like to analyze the purchase patterns of customers. Specifically, EMB would like to know how much money each customer spent on various products. Create a pivot table to show the total sales dollar amount for each customer in descending order from high to low. The pivot table should list purchases by product type so that the columns display the amount of each product purchased along with the total amount each customer purchased. Which customer purchased the greatest dollar amount of products for the quarter? How much money did he or she spend? What product did he or she spend the most money on? What recommendations would you make to management based on this report? Save the pivot table as worksheet “Customers”. (Hint: Customers should be displayed in rows and products should be displayed in columns. You should create a new column in the raw data set to compute the total sales amount prior to creating the pivot table. In addition, you should create a new column in the raw data set to display the customer’s full name).

Pivot Table 2 – Products by Region

EMB would like to analyze the sales of products by geographic region. Create a pivot table to show the total sales dollar amount of each product in order from high to low with the product with the greatest amount listed first. The pivot table should list purchases by region name so that the column display the dollar amount of each product purchased by region name along with the total amount of each product sold. Which product had the highest sales dollar amount for the quarter? How much did the east region sell? What recommendations would you make to management based on this report? Save the pivot table as worksheet “Products by Region”.

Pivot Table 3 – Sales by Rep

EMB would like to analyze the sales of products by sales representative. Create a pivot chart to show the total sales by sales representative from high to low with the person selling the greatest dollar amount listed first. The pivot table should list sales representatives’ names as rows and total amount sold as the only column and display the corresponding bar chart. Who sold the greatest dollar amount for the quarter? Who sold the least? What recommendations would you make to management based on this report? Save the pivot table as worksheet “Sales by Rep’.

Pivot Table 4 — Product by Month

EMB would like to analyze the sales of products over time. Create a pivot chart to show the total sales by month for the Extreme Mountain Bike, The Extreme Plus Mountain Bike and the Extreme Ultra Mountain Bike. The Pivot Chart should display bar for each month indicating the dollar amount sold of each of the three products for a given month. Which month had the highest total bike sales? Which month had the lowest total bike sales? What recommendations would you make to management based on this report? Save the pivot chart as worksheet “Products by Month”. (Hint: Create a new column in the raw data set to compute the month prior to creating the pivot table.)

The Deliverables

Excel Workbook

Please attach a copy of your Excel workbook containing pivot tables 1 through 4. Answers to the discussion questions for each pivot table should be included directly in the Excel workbook.

Order Now