Final Project IST 310: Introduction to Information and Management Productivity Systems Page 1
IST 310
Final Project
40 Points
1. Download and save the Final Project Template.xlsx file as
LastNameFirstNameFP.xlsx (e.g. BestMarkFP.xlsx).
IMPORTANT:
• Data is often messy. Don’t forget about our “Text Functions” lecture!
Functions like LEFT, RIGHT, MID, PROPER, TRIM, etc. can make your
life a lot easier when working with messy data in Excel.
• The States worksheet has a table in it named StateTable. The States
worksheet is protected with a password (i.e. you can’t modify it).
• The Prices worksheet has a table in it named PriceTable. The Prices
worksheet is protected with a password (i.e. you can’t modify it).
• The range B2:F2 in the Discounts worksheet has been named Regions. The
range B3:F12 in the Discounts worksheet has been named Discounts. The
Discounts worksheet is protected with a password (i.e. you can’t modify it).
2. Use the IFS function in the Salesperson column of the Orders worksheet to
return the name of the salesperson based on the first two characters of the Order
ID. You may NOT add to, delete, or modify any of the Order ID data in the
Orders worksheet. Additionally, you may NOT add any additional columns to the
data. MG is Michelle Grant, SP is Susan Peters, TJ is Timothy James, and AG is
Adam Grossman.
Figure Sense: How should you use the IFS function to complete the task for the first cell? Can
you check to make sure that you did this correctly?
3. Use the VLOOKUP function in the State Name column of the Orders worksheet
to return the name of the state based on the two-character state abbreviation that
is part of the code in the Ship To column of the Orders worksheet. Use the
StateTable in the States worksheet. You may NOT add to, delete, or modify any
of the Ship To data in the Orders worksheet. Additionally, you may NOT add any
additional columns to the data.
Figure Sense: How should you use the VLOOKUP function to complete the task for the first
cell? Can you check to make sure that you did this correctly?
4. Use the VLOOKUP function in the Region column of the Orders worksheet to
return the region based on the two-character state abbreviation that is part of the
code in the Ship To column of the Orders worksheet. Use the StateTable in the
States worksheet. You may NOT add to, delete, or modify any of the Ship To
data in the Orders worksheet. Additionally, you may NOT add any additional
columns to the data.
Final Project IST 310: Introduction to Information and Management Productivity Systems Page 2
Figure Sense: How should you use the VLOOKUP function to complete the task for the first
cell? Can you check to make sure that you did this correctly?
5. Use the XLOOKUP function in the Price column of the Orders worksheet to
return the price based on the Product column in the Orders worksheet. Use the
PriceTable in the Prices worksheet. You MUST use structured references to the
PriceTable fields in your XLOOKUP function. You may NOT add to, delete, or
modify any of the Product data in the Orders worksheet. Additionally, you may
NOT add any additional columns to the data.
Figure Sense: How should you use the XLOOKUP function to complete the task for the first
cell? Can you check to make sure that you did this correctly?
6. Use the INDEX/MATCH functions in the Discount % column of the Orders
worksheet to return the discount percentage based on the Discounts and
Regions named ranges in the Discounts worksheet.
Figure Sense: How should you use the INDEX and MATCH functions to complete the task for
the first cell? Can you check to make sure that you did this correctly?
7. Use a formula to complete the Total column of the Orders worksheet. Make sure
that the totals include any discount. Check Figure: Total for Order ID SP1001 =
$719.64
Figure Sense: If you know the quantity (q), the price of the product (p), and the discount (d),
what is an algebraic expression for the total cost of the order (tc)? What is an Excel formula for
this algebraic expression? Can you check to make sure that you did this correctly?
8. Use the Flash Fill tool in the Team column of the Orders worksheet to return just
the team name based on the Product column of the Orders worksheet. Make
sure that there are no blank spaces. For example, Buccaneers (without any
blank spaces) should be the team listed for Order ID SP1000.
Figure Sense: Can you check to make sure that you dd this correctly?
9. There are 22 duplicate records in the Order worksheet. Remove them.
Figure Sense: Can you check to make sure that you did this correctly?
10. Use conditional formatting in the Order ID column of the Orders worksheet to
format the Order IDs that have more than one type of jersey (i.e. Product) on that
order with a Green Fill with Dark Green Text. For example, Order ID MG1008
had Panthers, Patriots, Titans, Vikings, and Texans jerseys purchased on it.
Therefore, all five cells with Order ID = MG1008 should be formatted with a
Green Fill with Dark Green Text.
Figure Sense: Can you check to make sure that you did this correctly?
Final Project IST 310: Introduction to Information and Management Productivity Systems Page 3
IMPORTANT: You may NOT use PivotTables to answer Questions 11 – 16.
11. Use a function in cell B1 of the Questions 11 – 16 worksheet to answer the
following question: What is the average number of Chiefs jerseys sold? Use
another function in the same formula to round the results to two decimal places.
Figure Sense: What function will computer the average of only the Chiefs jerseys? What
function will round the results to two decimal places? How can you use these functions to
compute the result for the first cell? Can you check to make sure that you did this correctly?
12. Use a function in cell B3 of the Questions 11 – 16 worksheet to answer the
following question: How many total jerseys were shipped to the Central region?
Figure Sense: What function will compute the total number of jerseys shipped to the Central
region? How can you use this function to compute the result for the first cell? Can you check to
make sure that you did this correctly?
13. Use a function in cell B5 of the Questions 11 – 16 worksheet to answer the
following question: How many orders that were shipped to the West region
included a Washington Football Team jersey?
Figure Sense: What function will compute the total number of orders shipped to the West
region and have a Washington Football Team jersey? How can you use this function to
computer the result for the first cell? Can you check to make sure that you did this correctly?
14. Use a function in cell B7 of the Questions 11 – 16 worksheet to answer the
following question. You may only use the Order ID, Region, and Quantity
columns. How many total jerseys did Timothy James sell to customers in the
Northeast region?
Figure Sense: What function will compute the total number of jerseys that Timothy James sold
in the Northeast region? How can you use this function to compute the result for the first cell?
Can you check to make sure that you did this correctly?
15. Use a function in cell B9 of the Questions 11 – 16 worksheet to answer the
following question: How many states are in the South region?
Figure Sense: What function will computer the total number of states in the South region? How
can you use this function to compute the results for the first cell? Can you check to make sure
that you did this correctly?
Final Project IST 310: Introduction to Information and Management Productivity Systems Page 4
16. Use a function in cell B11 of the Questions 11 – 16 worksheet to answer the
following question: How many total Chiefs jerseys were sold in the Central
region plus total Packers jerseys sold in the West region?
Figure Sense: What function will compute the total number of Chiefs jerseys sold in the Central
region? What function will compute the total number of Packers jerseys sold in the West
region? How can you use these functions to compute the result for the first cell? Can you
check to make sure that you did this correctly?
17. Add the label Free Gift to cell M1 of the Orders worksheet. Then write a formula
that returns TRUE if the order included more than one Broncos or Patriots jersey
being shipped to the Northeast, West, or South region.
Figure Sense: What Excel functions need to be used? How can you use these functions to
compute the result for the first cell? Can you check to make sure that you did this correctly?
18. Make the Orders data an actual Excel table. Name the table OrderTable.
Figure Sense: One way to check your answers for Questions 11 – 16 is to use the table
functions (e.g. filters) to verify that what you have is correct.
19. Insert a PivotTable based on the OrderTable into a new worksheet named PT1.
Move the PT1 worksheet so that it is directly to the right of the Questions 11 – 16
worksheet. Create a PivotTable that shows the total dollar amount of jerseys
sold by team. Make sure to format the amounts as Currency with two decimal
places. Insert a slicer for Region. Use the slicer to filter the PivotTable so that
only data for the East and Northeast regions is displayed. Remove the gridlines
from the PT1 worksheet.
Final Project IST 310: Introduction to Information and Management Productivity Systems Page 5
20. Insert a new PivotTable based on the OrderTable into a new worksheet named
PT2. Move the PT2 worksheet so that it is directly to the right of the PT1
worksheet. Remove the gridlines from the PT2 worksheet. Replicate this
PivotTable:
21. Insert a new PivotTable based on the OrderTable into a new worksheet named
PT3. Move the PT3 worksheet so that it is directly to the right of the PT2
worksheet. Remove the gridlines from the PT3 worksheet. Create a PivotTable
that shows the total dollar amount for each unique order. For example, Order ID
SP1004 consists of multiple jerseys. The PivotTable should show just one row
for Order ID SP1004 with a total dollar amount of $848.10. Make sure to format
the amounts as Currency with two decimal places.
Final Project IST 310: Introduction to Information and Management Productivity Systems Page 6
22. Insert a new PivotTable based on the OrderTable into a new worksheet named
PT4. Move the PT4 worksheet so that it is directly to the right of the PT3
worksheet. Remove the gridlines from the PT4 worksheet. Replicate this
PivotTable:
Final Project IST 310: Introduction to Information and Management Productivity Systems Page 7
23. Insert a new PivotTable based on the OrderTable into a new worksheet named
PT5. Move the PT5 worksheet so that it is directly to the right of the PT4
worksheet. Remove the gridlines from the PT5 worksheet. Replicate this
PivotTable and PivotChart (Mac Users: Your PivotChart might not have the
filter buttons on it. That is okay!):
24. Type your first and last name into the center section of the custom header of the
Orders worksheet.
25. Make the Orders tab the active worksheet, save and close your file, and submit
your Excel file on Canvas. Here is a sample of what the Orders worksheet
should look like: