Before diving into this assignment, I very strongly recommend that you watch the videos on Panopto.
Once again, Angelo Cappellini has asked you to look at the pizza sales for Pop’s Pizza. This time, he’s
given you the first three months of data from 2020. He’s interested in gathering some insight about his
business based on its past sales. Please answer each question to the best of your ability, using functions
in Excel as backing evidence for each problem. After you’ve used Excel to find the answer to Angelo’s
questions please fill in the appropriate areas on you “Answer” sheet in the workbook.
The workbook associated with this homework contains several worksheets:
• An ‘Answers’ sheet that you will fill in with the answers and explanations for the problems
below in this document.
• An ‘Order Details’ sheet that contains the complete details of all pizza orders made at Pop’s
Pizza during the first three months of 2020. This sheet contains one row per line-item on the
order’s receipt, so there may be multiple rows associated with an order.
o A blank month column, that you’ll fill in while completing this homework
o The date an order was placed
o The Order ID
o The Storefront Location that an order was made it (Wilkes-Barre, Scranton, or Pittston)
o The itemized Pizza # that the particular record belongs to. EX: Order ID 2020-00002-D
has a Medium Pizza with anchovies and sausage as toppings, which can be understood
by looking at rows 3-5. Both items are part of Pizza #1 for that order. The order also had
a second, Large Pizza with Sausage, Onions, and Bacon as toppings on rows 6-9 and it is
Pizza #2. You will NOT be using this column for anything on this or any other
homework this semester.
o The Product name
o The Product category, which will be either Pizza or Topping
o The Product’s base sale price
o What type of order this was (dine-in, take-out, or online)
o The payment method for the order (cash, credit, paypal, or online credit)
o Any discount that was applied to the order (none, military, senior, or student)
o A blank discount amount column that you’ll fill in while completing this homework
o A blank final price column that you’ll fill in while completing this workbook
• A blank ‘Order Summary’ sheet that you will fill in while completing this homework
• A blank ‘Problem 3’ sheet that you will fill in while completing this homework
• A blank ‘Problem 4’ sheet that you will fill in while completing this homework
Your task is to apply what you’ve learned in Part 1 of our lab to authoritatively answer the questions
below. You will be free to lay out your resulting work/spreadsheets in any manner you see fit so long as
they clearly answer the questions.
Please note: I will be grading your ability to not only convincingly answer the questions I ask, but also
your ability to convey and show your work in a clear, easy to understand manner. If you provide me
answers for a problem but don’t have a worksheet where I can go and find the work you used to get
your answers, you will receive no credit for that problem. Part of learning how to use Excel is learning
to make elegant spreadsheets. I want you to put thought into the appearance of your work, knowing
that you will often need to share your Excel workbooks with others.
Each question will be graded as follows:
• 50% – Does your written explanation / justification for the answer accurately describe the
answer in a way a layperson can understand?
• 50% – Is there work done in Excel that can justify your written answer? Work is considered to be
the formulas/functions/worksheets used to answer the problem. Do any worksheets or
functions made to answer the problem contain the necessary Excel work needed to answer the
question posed in the problem, laid out in a manner that’s clear to understand?
Reminder: This is an Excel lab. If you provide me an answer to a calculation but don’t have that
calculation in Excel, you will receive no credit for the problem. When you are asked to use a function to
solve something use a function. Not doing so will ensure you receive no credit.
I’m happy to give partial credit. If you’ve come to the wrong answer but have work that shows me how
you got to that answer, you will receive partial credit for the problem.
Problem 1
Start this homework assignment by completing the Order Details sheet. To do this, you will need to
perform the following actions in Excel:
• Fill in the Month column using one of the functions we used in Part 2 of our data analytics lab.
• Fill in the DiscountAmount column by using the IF() function we learned in class. Your IF function
will use the following arguments:
o Logical_test: The value in column K = “None” (hint: @K:K)
o Value_if_true: 0
o Value_if_false: 10% * The value in column H (hint @H:H)
Row 2 should output a value of 1.25
Row 3 should output a value of 0 (it will appear as – in Accounting format )
• Fill in the FinalPrice column using the following formula:
o Price – Discount Amount
In order to receive credit for this problem, Order Details sheet must be filled in with the correct content.
You won’t be able to proceed further in the assignment without doing this.
Problem 2
For this problem, your task is to fill in the blank Order Summary sheet. Your sheet will be broken up into
a Reference area and a Data area. The reference area is the portions of the spreadsheet where you’ll
calculate out totals, and the data area will be filled in with data that’s been gathered using functions to
grab values from the Order Details sheet using Excel functions.
Your Order Summary Sheet will contain one record (row) for each distinct order that was made, as well
as the final price of the order. You’ll fill in the Data area of the sheet with two functions to help you find
these values.
To fill in your Data area:
• Cell A10 will contain a function you learned in Part 2 of our Excel lab to help you get all of the
unique values from OrderID column of the Order Details sheet. If you do this correctly you’ll see
the following output:
o Cell A10: OrderId
o Cell A11: 2020-00001-D
o Cell A12: 2020-00002-D
o Cell A13: 2020-00003-D
o Cell A14: 2020-00004-O
o Etc
• Cell B10 will contain the text Final Subtotal
• Cell B11 will contain a SUMIF() function to generate the final subtotal of the corresponding
OrderId on the same row. Here are some tips:
o You want to filter rows on your Order Details sheet that meet the criteria of the OrderId
column matching the OrderID in your Data area. When you’ve filtered the records on
your Order Details sheet, you should have it return the sum of the Final Price column.
We did a whole bunch of examples of this in class, and in the Online videos discussing
the SUMIF function
After filling in your Data area, you can fill in your Reference area
• Cell A2 will contain the text Total Revenue
• Cell B2 will contain the total revenue generated, based on the Final Subtotal values in your Data
area. Use an Excel function to return this value
After solving the bullet points above, go to your Answers sheet and fill in the total revenue value that
you found in cell B2.
Problem 3
Go to your Problem 3 sheet. On this sheet you will use the SUMIFS() function to help Angelo break
down his revenue on a monthly basis at each storefront location. In class I showed you two ways you can
set up your SUMIFS() functions to give you results like these: the compact cross-tables, and the long
lists. For this problem you may use either method to return the results you need.
Notes:
• There are a total of 3 storefront locations: Wilkes-Barre, Scranton, and Pittston
• There are 3 months of data in this dataset: January, February, and March
Set up your SUMIFS table/list however you deem appropriate.
After you’ve gotten your data filled in please Let Angelo know the following information:
• During which month did Angelo have the highest sales revenue?
• Which of Angelo’s three storefront locations brough in the highest total revenue?
You will be able to find the above answers by examining the output of your SUMIFS() table/list.
After solving the bullet points above, go to your Answers sheet and fill in your answers.
Problem 4
Go to your Problem 4 sheet. On this sheet you will use the database functions to help Angelo out. He
would like to understand a bit more about his sales as it relates to whether a discount was applied to an
order or not.
When using the database functions you need to set up your spreadsheet with a Field area and a Criteria
area. I have your Problem 4 sheet set up with these areas partially set up to help you along. You’ll need
to fill in the actual field names and criteria setup yourself.
There’s also a Database Function Area on the sheet. This is where you’ll actually use the database
functions to come up with answers for Angelo.
Feel free to modify the sheet as you see fit to suit your needs. You may also delete it and start from a
truly blank copy if you prefer.
Angelo would like to know the following information:
• What was the total revenue generated by all sales where a discount was applied?
o Notes:
▪ You will be able to find revenue by using the FinalPrice field on your Order
Details sheet
▪ The Order Details sheet has the following Discount types
• Military
• Student
• Senior
• What was the total revenue generated in January for the Storefront Locations Scranton OR
Pittston
o Notes:
▪ You will be able to find revenue by using the FinalPrice field on your Order
Details sheet
▪ The Location column of the Order Details sheet lists the storefront location that
a sale was made at
▪ The Month column of the Order Details sheet lists the month that an order was
placed
After solving the bullet points above, go to your Answers sheet and fill in your answers.
