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.

Leave a Reply

Your email address will not be published.