Overview
For the Pizza Pivot Table Challenge, you’ll be assuming the role of a consultant who has been hired by a Greek-inspired pizza restaurant. The restaurant needs you to help them improve their operations, and you can do so using the transactional data they have collected over the past year. Your job is to put together a report that will help the restaurant improve sales and work more efficiently.
Specifically, the following questions need to be answered in your report.
What days and times is the pizza restaurant the busiest?
How many pizzas are they making during peak periods?
What are the best and worst selling pizzas?
What is the average order value?
Answer these questions and offer any other insights that you think would be valuable to the pizza restaurant. Pivot tables offer an efficient and effective way to report the answers to these questions.
Instructions
Download the excel data.
Create a pivot table on a new sheet that displays order quantity by time and day of the week. Time of the day should be the rows and day of the week should be the columns. Use conditional formatting color scales within the pivot table to easily highlight the busiest times of the week.
Create a pivot table on a new sheet that displays Total Price by time and day of the week. Time of the day should be the rows and day of the week should be the columns. Use conditional formatting color scales within the pivot table to easily highlight the most profitable times of the week.
Create a pivot table on a new sheet that displays each pizza and their total sales and quantity ordered for the year. Sort the data by total sales for each pizza. Use conditional formatting color scales within the pivot table to easily highlight the best and worst selling pizzas.
Create a pivot table to display the average order value in a single cell.
All pivot tables appear to be the same except for number 4. Usually, when an order is broken out by the items within, the total price will be the sum of each of the unit prices within the order. However, in this dataset, the total price columns seems to be the unit price multiplied by the quantity. In essence, it is a line total (ie, the total price for this individual item within the order). In my number 4 Pivot, I created a calculated field that multiplied the average unit price by the quantity, which should output the total revenue per pizza name. In the solution posted by the author, sum of the total price is used. I included the sum of the total price as a column in my number 4 for reference. In this case, I think the author is probably more accurate in using the sum of the total price column since total price is a line total. If the total price column had been the total price of the order, then my solution would be more accurate.
Hopefully this makes sense! To be clear, I think the author’s solution is correct given the dataset.
This challenge was really good for me because, despite my years spent honing my Excel skills, pivot tables are always something I need to keep relearning!
My solution really closely matches @Mike_Paulsin’s, so I’m not going to add an extra file here in the chat (though I am happy to share if anyone would find that useful–just let me know)!
A couple my own key takeaways from this training that I don’t think I had ever considered before:
Pivot tables combined with conditional formatting can be extremely powerful to quickly identify trends in large datasets. In my work, I’ve been inclined to take hours creating dashboards to visualize trends, which has a time and place, but I think for quick decision-making, this combo is something I plan to implement the next time I present to my managers!
Sorting has much stronger capabilities in pivot tables than it does in normal table views. I didn’t realize you could sort based on so many different criteria, but it’s definitely useful to know now!
Also @Mark, I’ve been thinking a lot about your comments (very insightful!) and want to make sure I’m understanding completely! It looks like, in this data set, even if two different pizzas were ordered at the exact same time on the same day, they would be recorded under two different order numbers. Are you saying that if the orders in this dataset contained different types of pizzas, then your calculated-field method would work well? Looking at your solution, it makes a lot of sense to me but seems like the calculated field really overestimates total revenue for the year for some reason. Do you have any insights why?
This challenge was super fun! I always seem to forget how powerful Pivot Tables are and how much I can do with them! I feel like after this challenge I’m just about ready to open my own pizza restaurant!
I found that I was able to get all of the information I needed for question two from the table I made for question one. I also found a lot of other useful ways to use the tables!
I tested out the first step to see how this site works. I am think this is a great tool to learn about picot tables and am excited to learn more. Challenge19_Dataset_PizzaPivotTablesChallenge.xlsx (6.8 MB)
Here is my solution: Pizza solution.xlsx (6.7 MB)
I had forgotten how powerful pivot tables can be. They help visualize data in great ways. Making it easier to consume and analyze the data.
I really enjoyed this challenge–I think pivot tables are very underrated tools and I’m glad that there are several pivot table challenges on this site. For my best and worst selling pizzas, I added the pizza_id which indicates the size. So the pivottable is still sorted by the pizza with the most total sales, but it also breaks the sales out by size. Take a look:
After completing what was asked in the instructions, I played around a bit with the data in another worksheet. Pivot tables are so helpful and show many important things, especially for a business such as this. Challenge19_Dataset_PizzaPivotTablesChallenge.xlsx (6.7 MB)
The power of pivot tables is really astounding! It’s always fun to do challenges with them. I thought it was fun to mess around with the pivot table after it was created. I idn’t see anyone post which times were the best, but the answer is Firdays, and 12PM:
Great fun challenge. I wanted to see what days of the week had the most order. I also wanted to see if the correlation between the number of of orders and total sales. They are strong correlated so the number of pizzas being ordered by order doesn’t change very much.
This was a great refresher on how to make pivot tables. I liked how the color scales emphasized the message of the pivot tables and made them more effective.
