# 3|EXCEL – Advisory Revenue Pivot Tables

BYU Student Author: @Andrew
Reviewers: @Parker_Sherwood, @Mark
Estimated Time to Solve: 20 Minutes

We provide the solution to this challenge using:

• Excel

Overview
Every business will require large datasets to be summarized. Summarization can be done using several different tools. In Excel, pivot tables are great summarization tools to generate a non-visual report.

Instructions
You work for the advisory board at a public accounting firm. They have asked you to compile several reports for their review regarding revenue across the firm.

First, create a pivot table containing the total revenue for each practice by year. The pivot table should be in a new sheet called “Total Revenue”.

Second, create a pivot table displaying the total revenue for each region for the advisory practice in 2022. Sort the table by total revenue largest to smallest. The pivot table should be in a new sheet called “Advisory Revenue 2022”.

Third, create a pivot table containing the total revenue, total hours, and average rate billed per hour for each client serviced by the South region Advisory practice in 2022. Sort the table to show clients billed the highest average rate in descending order. The pivot table should be in a new sheet called “South region advisory detail”.

Fourth, create a pivot table displaying the top 10 professionals who billed the most hours in 2022, the total hours they worked and the average rate they billed. Sort by total hours works from largest to smallest. Format the table, so average rate is rounded to the nearest cent. The pivot table should be in a new sheet called “Top 10 professionals”.

Data Files

Suggestions and Hints

To filter the top 10 professionals by their hours, click the dropdown labeled “Row Labels” at the top of your pivot table, select “Value filters”, then select “Top 10”.

Solution

Nice reminders of some of the pivot table functions. I had forgotten how to do the top 10 filter.

One trick I learned that can be very useful is the toggling the “Generate GetPivotData” function. In the image below it shows where the toggle is. This allows you to reference the cell (if turned off) or the data (if turned on). The image shows the formula and what it looks like when you have it turned on in cells d56 and d57. This can be useful if you ever want to reference a PivotTable in a formula. With it turned off, you can copy and paste formulas and they will move like when you copy and paste any other cells. With it turned off, when you copy and paste, the cell value stays the same and doesn’t “move.” Try it out and see if helps.

2 Likes

Here is my solution!
I added some conditional formatting as well to highlight the highest revenues or highest average rates.

#1

#2

#3

#4

4 Likes

Awesome challenge. In addition to the the analysis you asked for, I want to see how the average rate has changed over time for the 5 different practices. I calculated the average rate by using a calculated field using this calculation: Sum(Revenue)/Sum(hours).

This was a solid challenge to practice some basic pivot table skills. For the third and fourth tables, it took me a minute to figure out how to sort based on values that weren’t in column A. I’ve listed the steps that I followed in case anyone has the same question but doesn’t have time to watch the video!

1. Click on this dropdown arrow next to “Row Labels.”
2. Select “More sort options…”
3. Select “Descending.”
4. Select “Average of rate.”

Good practice with some relatively simple Pivot tables for analysis. One thing that made it go faster for me was to make one Pivot table and then duplicate the tab (Right click the tab->Move or Copy->select Copy) rather than having to reselect the data to create several Pivot tables.

Here’s my solution to this problem!
Challenge3_Data.xlsx (146.3 KB)

I had gotten a little Rusty on my Pivot table and this was a good reminder of the basics. It was interesting testing out the top 10 filter and trying out some other filters as well. One filter I used was the bottom 10 filter which I could see being very useful in seeing where you can improve in different areas. Heres the Table using the bottom 10 filter.

Pivot Table Challenge.xlsx (137.9 KB)

This was a great reminder on how to create pivot tables. I forgot about this helpful feature! You can learn so much by focusing the data this way!

2 Likes

This was a great refresher to me on the power of using Pivot Tables within Excel. It really makes it so much easier to sort through large piles of data and find answers to specific questions.
Challenge3_Data_Spencer.xlsx (137.1 KB)

1 Like

Challenge3_Solution_Edington.xlsx (137.3 KB)
Awesome beginner exercise to figure out pivot tables!

Here is part of the solution! I definitely learned more about tables and how to filter by category. Thanks for the challenge and walkthrough!

Challenge3_Data.xlsx (146.8 KB)

This one was fun to brush up on how to create pivot tables. I’m starting a new job soon that will require using these, so hopefully this comes in handy!

This was a really good addition to my knowledge of pivot tables! I had some issues arranging the totals in the order that was required, but I found out that I simply had to order the filtering criteria correctly in order for it to sort the table by the correct field. For example, I had to sort the advisory practices by region, before the practice, in order for the table to sort from highest value to lowest.
*
Challenge3_Data.xlsx (145.8 KB)
*

It’s great finding different data you can mess around with in pivot tables. Here is my solution:

Challenge3_Data.xlsx (145.6 KB)

Here’s my solution!
Challenge3_Data.xlsx (136.5 KB)

1 Like

Great reminder about how to do pivot tables. I really liked all the formatting you did in the solution video.
Challenge3_Data.xlsx (145.8 KB)

Was a great review on pivot tables. Here is my solution.
Challenge3_Data.xlsx (146.0 KB)

Challenge3_Data.xlsx (144.9 KB)