Homework answers / question archive /  Illustrated Excel 2019 | Module 5: End of Module Project 1 City Sports MANAGE AND ANALYZE TABLE DATA GETTING STARTED Open the file IL_EX19_EOM5-1_FirstLastName_1

 Illustrated Excel 2019 | Module 5: End of Module Project 1 City Sports MANAGE AND ANALYZE TABLE DATA GETTING STARTED Open the file IL_EX19_EOM5-1_FirstLastName_1

MS Excel

Share With

---

 Illustrated Excel 2019 | Module 5: End of Module Project 1

City Sports

MANAGE AND ANALYZE TABLE DATA

• GETTING STARTED

• Open the file IL_EX19_EOM5-1_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX19_EOM5-1_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file IL_EX19_EOM5-1_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. Felipe Tejada is the director of online sales for City Sports, a company that manufactures scooters, skateboards, and hoverboards in Tacoma, Washington. In an Excel workbook, Felipe has been tracking online sales of scooters each month, and asks for your help in managing and analyzing the data.
   Go to the April Orders worksheet. Format the range A11:F73 as a table with headers using Orange, Table Style Medium 4. Name the table Orders so that you can reference it in calculations.
2. City Sports sells five categories of scooters, which are listed in the range H5:H9. Felipe wants to make it easy to enter a valid category when he adds records to the table. Create an in-cell dropdown data validation rule for the Category column that accepts only entries from the range H5:H9. Show an input message when the cell is selected using Scooter Category as the title. Use the following sentence as the input message:
   Click the arrow to select a category.
   Apply an Information style error alert that also uses Scooter Category as the title and the following sentence as the error message:
   Please select a category from the list.
3. Felipe needs to show the total billed amount in the Orders table. Insert a table column to the right of the Shipping column. Type Total Billed as the column heading. Resize columns A, F, and G to their best fit to display the full text of the header cells in those columns.
4. In cell G12, enter a formula without a function that uses structured references to add the Price and Shipping values for the first record. If necessary, fill the range G13:G73 with the formula in cell G12.
5. Felipe received an online order and needs to add it to the Orders table. Add a new record to the end of the table and then insert the data shown in Table 1, using the in-cell dropdown list to enter the Category value.

 

Order Date	Order ID	Category	Product Name	Price	Shipping	Total Billed
4/30/2021	M-8045	Micro	Zip Lux Scooter	79.00	8.49	(calculated)

 

6. The table is not currently sorted, but Felipe wants to sort it by the Order Date, with the oldest records listed first, which will help him find orders quickly. For orders received on the same date, he wants to list the orders by Product Name. Sort the table in ascending order first by Order Date and then by Product Name.
7. Felipe also wants to track the total billing amount, the average price, and the number of orders. Add a Total row to the Orders table, which automatically sums the amounts in the Total Billed column. In cell B75, use the total row to display the count of the Order IDs. In cell E75, use the total row to display the average Price.
8. Felipe has created a lookup area in the range A4:B7. First, he wants to find the product name associated with the Order ID entered in cell B5. In cell B6, enter a formula using the INDEX and MATCH functions and structured references to the columns in the Orders table. Return a value from the Product Name column that exactly matches the value in cell B5, which is listed in the Order ID column of the Orders table.
9. Next, Felipe wants to find the amount billed for the order entered in cell B5. In cell B7, enter a formula using the VLOOKUP function and structured references to the columns in the Orders table. Look up the value in cell B5, refer to the six columns in the Orders table beginning with Order ID and ending with Total Billed (range B12:G74), and return a value in column 6 that exactly matches the value in cell B5.
10. Felipe also wants to find the number of scooters sold in the Mini category (cell D6) along with the total amount of revenue generated from those scooters. In cell E6, enter a formula using the DCOUNTA function and structured references to the Orders table. Use the [#Headers] and [#Data] in the Orders table (range A11:G74) as the formula database. Use the Category field header (cell C11) as the field to count. Use the values Felipe set up in the range D5:D6 as the criteria.
11. In cell F6, enter a formula using the DSUM function and structured references to the Orders table. Use the [#Headers] and [#Data] in the Orders table (range A11:G74) as the formula database. Use the Price field header (cell E11) as the field to summarize. Use the values Felipe set up in the range D5:D6 as the criteria.
12. Felipe wants to examine the orders for scooters in the Mini category more closely. Filter the Orders table to display orders in the Mini category only.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.