You are an accountant working for a company called Global Bike Inc. You have been assigned to assist the strategic planning team with profitability analysis in the wholesale division of the company. (The internet sales will be handled by a different team.) Your IT team has pulled transactional data from 2011 through 2014 for you to analyze.

all files needed ar attached

Requirements: as required

Chapter 5: exercise 1

USING excel pivot tables for analytics

Nitin Kalé, University of Southern California

Nancy Jones, San Diego State University

Objective

To derive business intelligence using Microsoft Excel Pivot Tables

Activities

Create a pivot table

Create crosstabs

Filter, sort, rank

Aggregate

Create calculated fields

Create charts

Software Prerequisites

Microsoft Excel 2010 or higher

UCC Products Required

None

Data Required

GBI dataset is available in GBI_E5_1.xlsx

Scenario

You are an accountant working for a company called Global Bike Inc. You have been assigned to assist the strategic planning team with profitability analysis in the wholesale division of the company. (The internet sales will be handled by a different team.) Your IT team has pulled transactional data from 2011 through 2014 for you to analyze. The data are stored in an Excel file called GBI_E5_1.xlsx.

Everyone on the team agrees that you are the best person to do the initial exploration of the data set. Although you are free to do your own analysis, at the very least, you need to answer the questions in the following section so that you can report back to the team.

UsIng Excel Pivot Tables

The most common tool used to slice and dice data is a spreadsheet. Typically slicing and dicing is done by creating a pivot table which creates what is called a cross tabulated structure or crosstab. The crosstab displays aggregated and summarized data based on which way you sort the columns and rows. The pivot table can be easily transposed by swapping the axes; hence the name, pivot.

We will now use Microsoft Excel pivot tables to answer some business questions related to your assigned profitability analysis. The step-by-step instructions will guide you through the use and manipulation of pivot tables.

Open the data file GBI_E5_1.xlsx in Microsoft Excel

You should see the spreadsheet in Figure 1

Figure 1: GBI spreadsheet

Click on Insert Pivot Table. You should get the screen in Figure 2. Notice that Excel suggests the entire table as the range of cells for your pivot table.

Figure 2: Creating a Pivot Table

Click OK.

This inserts a pivot table in a new spreadsheet.

Figure 3 shows the layout of the pivot table and associated menus. Explore the menus and tool bar.

Figure 3: Pivot table layout

Drag Revenue to Values, Calendar Year to Rows. Alternatively, you may simply check the selection box to the right of Revenue and Calendar Year and Excel will suggest the placement of the fields in the pivot table. If you choose to select rather than drag and drop fields, you will need to double check to be sure they are placed in the appropriate location within your analysis.

Format the Revenue as Currency, $

Now we would like to sort the revenue to find out which year has the highest revenue. Click on the drop arrow (see Figure 4) next to Row Labels. Then choose More Sort Options. Then Descending by Sum of Revenue. Be sure that you click on the relevant field in the crosstab before clicking on the Row Labels drop arrow. In this case it would be Year.

`

Figure 4: Row Labels

Question 1: What was the overall revenue? Which year had the highest revenue and what was the revenue during that year?

Add Material Desc to the Rows.

Using the drop arrow next to Row Labels, Select Field Material Desc and filter the material to E-Bike.

Question 2: What is the Revenue for E-Bike in 2010?

Question 3: What is the Revenue for E-Bike in 2009?

Remove the filter on Material Desc.

Remove Revenue, add Sales Quantity. Format the Sales Quantity column to Number, zero decimals.

Add Customer Desc to Rows.

Question 4: What is the Sales Quantity of T-shirts for Airport Bikes in 2007? (Hint: filter Material Desc and Year to eliminate unwanted rows of data.)

Remove Material Desc. Remove Customer Desc. In the Fields panel you may notice that if you applied filters, a filter icon will still show next to the filtered fields (probably Material Desc and Calendar Year). Click on the icon next to each filtered field and remove the filter(s).

Sort the Calendar Year in descending order of Sales Quantity using the drop down arrow next to Row Labels. (Hint: Choose More Sort Options from the drop down list.)

Figure 5: Sort Descending by Sales Quantity

Question 5: What year had the highest Sales Quantity?

Remove Sales Quantity. Add Revenue. Sort Calendar Year in ascending order of Revenue.

Question 6: What year had the lowest Revenue?

Add Material Desc.

Sort Calendar Year in ascending order of Revenue. Then Sort Material Desc in ascending order of Revenue.

Question 7: In the year with the lowest Revenue, which Material had the lowest revenue?

Question 8: Which material had the lowest revenue overall? (Hint: You may wish to remove a field to get the answer to this question.)

Remove Material Desc. Remove Revenue. Add Calendar Year. Add Net Sales to Values. Add Division to Rows

Sort Calendar Year in descending order of Net Sales. Sort Division in descending order of Net Sales.

Question 9: In the year that had the highest Net Sales, what division had the highest Net Sales?

Question 10: For the division in Question 9, which customer had the highest Net Sales? (Hint: You will have to add a field and perhaps a filter and then sort.)

Question 11: In the year with the lowest Revenue, what division had the highest sales revenue? (Hint: Remove any filters you may have applied to answer the previous question.)

Question 12: For the division in Question 11, which customer had the highest Net Sales for all years?

Question 13: What customer provided the highest revenue for Accessories (Division AS) in 2009?

Hints regarding removal of filters will no longer be given. Therefore you will want to watch carefully and be sure to remove filters after they are no longer needed.

Include only Calendar Year, Division and Customer Desc in rows. Include only Revenue in Values. Filter Year to 2011, Division to BI. Click on any customer and then Row Labels Value Filters Top 10 Enter 3

Question 14: In 2011, for Bicycles (Division is BI), what is the total revenue for the top three customers?

Remove the Top 3 filter. Remove the Calendar Year filter. Remove the Division Filter.

Remove Customer Desc.

Right click Sum of Revenue in the crosstab. Change the Summarize by to average.

Remove Year and Division.

Question 15: What is the average revenue per customer?

Add Year

Question 16: What is the average revenue per customer per year?

Add Division

Question 17: What is the average revenue per customer per year per division?

Remove Division. Sort Row Labels by Average of Revenue.

Question 18: What is the average revenue per customer for each of the years?

Which year has the highest average revenue per customer? How much is that amount?

Add Customer Desc.

Within the year with the highest average sales per customer, sort descending the customers by Average of Revenue.

Question 19: Which customer has the highest average revenue during the year?

Include only Calendar Year in Rows. Country Desc in Columns. Revenue in Values. Insert a Pivot Chart. Choose a Line chart. Change the aggregation of Revenue to Sum.

Question 20: Are the historical (year by year) revenue trends for the US and DE similar or dissimilar?

Include only Customer Desc in Rows. Calendar Year in Columns. Revenue in Values.

Question 21: Did GBI ever gain or lose a customer (based on revenue)? Explain

Include only Calendar Month in Rows. Calendar Year in Columns. Revenue in Values. Notice that the Months may be sorted as text (rather than numerically). Resort them manually (if needed) in the correct order (1, 2, 3 …12). You can do this by selecting a cell, then hover over it until you see a four headed arrow. Then drag and drop to order correctly.

Insert a Pivot Chart. Choose Line chart.

Question 22: Is there seasonality in revenue during the year? If so, what month has the highest revenue? Is the seasonality similar from year to year?

Remove the Chart. Include only Material Desc in Rows. Calendar Year in Columns. Revenue in Values.

Question 23: Which product shows dramatic change in revenue over time (years)? Does it have the same change by country? (Hint: You will have to add a field to your analysis to answer the second part of this question.)

Include only Material Desc in Columns. Calendar Month in Rows. Revenue in Values Insert a Pivot Chart. Choose Line chart.

Select the material that shows the highest peak (in the chart). Right click Filter Hide Selected Item. Continue removing the next highest material until you see a low seasonality material (relatively flat line).

Question 24: Which material does not display significant seasonality?

Remove the chart. Include only Customer Desc in Rows. Revenue in Values. Right click Revenue in the crosstab and Show Values as % of Column Total. Sort Customers in descending order of Revenue.

Question 25: What customer has the highest percentage contribution to revenue? What has been the trend of that customer’s percentage contribution over the years? (Hint: You will need to add a field to your analysis to answer this question.)

Now create a new Calculated field called Gross Margin. Fields, Items, & Sets Calculated Field.

Figure 6: Navigation Ribbon for Adding a Calculated Field

Name: Gross Margin, Formula = ‘Net Sales’ – ‘Cost of Goods M’. OK

Figure 7: Create a Calculated Field

Include only Gross Margin in Values. Calendar Year in Rows. Sort Calendar Year in descending order of Gross Margin

Question 26: What year had the highest overall gross margin? What was the gross margin during that year?

Question 27: What was the gross margin (as a percentage of sales) for Germany for the year with the highest gross margin? What was the gross margin (as a percentage of sales) for US during the same year? (Hint: You will need to add a calculated field to your analysis to answer this question.)

—————————————- This is the end of the exercise. —————————————–

Collepals.com Plagiarism Free Papers

Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS

Why Hire Collepals.com writers to do your paper?

Quality- We are experienced and have access to ample research materials.

We write plagiarism Free Content

Confidential- We never share or sell your personal information to third parties.

Support-Chat with us today! We are always waiting to answer all your questions.