Name:
WPC 300: Problem Solving and Actionable Analytics
Assignment 1: Analyzing Data Cubes using Pivot Tables
You’ve been asked to help better understand sales patterns in DS’s Fine . Foods Emporium
And you will use the dimensional data set “ ” They sell a variety of fine Food Emporium
products, such as Gustaf's Knäckebröd, Pâté Chinois, and Maxilaku. Their motto is “Food so
good even we don’t know what it is!”
Specifically, you’ve been asked to answer a series of questions, which are listed in the table
below. You will perform the quantitative analysis to answer each of these questions. They can
all be answered by constructing pivot tables and pivot charts in Excel. The Excel spreadsheet
“Food Emporium.xlsx” has already been created for you. This is the single-table, joined data
cube and is ready for analysis.
Here are some tips:
1) You won’t necessarily be able to answer more than one question using the same pivot table.
2) You won’t need to create additional measures. The data in the table is all the data you’ll
need. But you may apply formulas to the results of your Pivot Table analysis (for example,
see problem #4).
3) To answer some of these questions, you may need to perform several steps. In other words,
you may need to create one pivot table, and then use that information to filter data in a
second pivot table.
4) In the spreadsheet, “Full Price” is the base price of the line item without a discount. The
“Extended Price” is the discounted price offered to the customer (i.e., what they actually
pay). The “Total Discount” is the difference between the Full Price and the Extended Price.
1. What are the five best-selling products based on quantity sold?
•Camembert Pierrot - 1577
•Raclette Courdavault - 1496
•Gorgonzola Telino - 1397
•Gnocchi di nonna Alice - 1263
•Pavlova - 1158
2. What are the five worst selling products based on quantity sold?
•Mishi Kobe Niku - 95
•Genen Shouyu - 122
•Gravad lax - 125
•Chocolade - 138
•Laughing Lumberjack Lager - 184
Name:
3. What’s the average order price?
(HINT: Look at the extended price by order and then use the Excel AVERAGE function)
•1525.052
4. What is the average quantity sold per order?
(HINT: Look at the total quantity by order and then use the Excel AVERAGE function)
•61.828
5. Are our sales seasonal (look at total sales in dollars)?
•The sales are seasonal
6. If so, what is the nature of the seasonality?
•When do sales (in dollars) go up/go down?
i. Sales go up during the months of March to August and then again November
and December. They are down January, February, September, & October.
•What is changing – number of orders or the size of the orders?
i. The number of orders and order size increases with the sales volume goes
up and decrease when it goes down.
7. Based on the data we have, does our discount policy appear to help sales?
•Yes, the total discount increases with sales volume and decreases when sales
are down
8. What are the most heavily discounted products (by percentage discount)?
•Chocolade has an average discount of 10.83%
•Chang has an average discount of 10.23%
•Mishi & Escargots have an average discount of 10.0%
9. Who are our five best customers based on total sales (i.e., Extended Price)?
•Cathrine Ecton $42,264.89
•Shayla Mcbath $40,318.77
•Ellie Sittig $32,297.05
•Kurt Bissonette $29,614.38
•Alvina Bollig $29,384.43
10. What are the five most popular (quantity sold) products among our five best customers?
(HINT: Apply a filter to include only the five best customers found in #9)
•Camembert Pierrot
•Chartreuse verte
•Côte de Blaye
•Gnocchi di nonna Alice
•Guaraná Fantástica
