Illustrated Excel 2019 | Module 5: End of Module Project 2 Home Project Supply

GETTING STARTED

1. Open the file IL_EX19_EOM5-2_FirstLastName_1.xlsx, available for download from the SAM website.

2. Save the file as IL_EX19_EOM5-2_FirstLastName_2.xlsx by changing the “1” to a “2”.

a. 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.

3. With the file IL_EX19_EOM5-2_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

a. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1. Suong Nguyen works in the Accounting Department of Home Project Supply, a growing chain of hardware stores in the Midwest. She is developing an Excel workbook to track the company's inventory stored in warehouses in Chicago, Illinois and Toledo, Ohio. She asks for your help in managing and analyzing the inventory data. Go to the Chicago worksheet. Suong wants to review the data on the worksheet, which lists the Chicago warehouse inventory data. Format the range A3:I61 as a table with headers using the Orange, Table Style Medium 9 table style.

2. Apply a table style option to bold and shade the item IDs in the first column of the table to contrast them with the other data.

3. Sort the table in ascending order by the values in the Item ID column so that Suong can find items quickly.

4. Find and remove a record with a duplicate Item ID number to list only unique records in the table.

5. Suong wants to analyze the inventory data on power tools separate from the complete inventory list. In cell K4, she has already entered "Power" as the condition to use in an advanced filter. Create an advanced filter that copies the results to another location, using the table of inventory data (range A3:I60) as the List range. Use the information in the range K3:K4 as the Criteria range. Copy the results to the range starting in cell K6. Resize column L to a width of 21.00. Resize columns M:S to a width of 9.00.

6. Go to the Toledo worksheet, which includes a filtered table named Toledo. Remove the filter from the Toledo table to display all the records.

7. Apply the Orange, Table Style Medium 9 table style to the Toledo table to use a format similar to other tables in the workbook.

8. Remove the record for Item ID B428 from the table because the record is incomplete.

9. Suong wants to calculate the total value of a specific type of tool. She has already entered the tool type in cell A4. In cell A6, enter a formula using the DSUM function that specifies a structured reference to the entire Toledo table including the headers and the data (Toledo[#All]) as the formula database. Use the Value field header (Toledo[[#Headers],[Value]]) as the field to summarize. Use the values in the range A3:A4 as the criteria.

10. Suong also wants to identify the number of items that need to be reordered. In cell B6, enter a formula using the DCOUNTA function that specifies a structured reference to the entire Toledo table (Toledo[#All]) as the formula database. Use the Reorder? field header (Toledo[[#Headers],[Reorder?]]) as the field to count. Use the values in the range B3:B4 as the criteria.

11. Go to the Full Inventory worksheet, which includes a table named Full_Inventory. Apply banded rows to the table to make it easier to read. Add a total row to the table, and then use the total row to sum the values in the Unit Price and Value columns.

12. Suong wants to list the tools that have a unit price more than $70 to identify the most expensive tools. Use a number filter to show only tools with unit prices greater than 70.

13. Prepare the Full Inventory worksheet for printing by using the data in row 3 as the print titles. Change the top and bottom worksheet margins to 1".

14. Go to the Suppliers worksheet, which includes information about the company's preferred and new suppliers. Suong included a table named Preferred in the range A4:C9 but does not plan to filter or sort the data or use any other table features with it. Convert the Preferred table to a range.

15. She also wants to insert a table listing shipping options each supplier offers. In the range A11:C14, insert a new table, specifying that the table has headers, and then use Shipping as the name of table. Enter the data shown in Table 1.

Table 1: Data for the New Table

| A | B | C 11 | Option | Days | Surcharge 12 | Premium | 1 | $15 13 | Standard | 3 | $10 14 | Backup | 5 | $0

16. The New_Suppliers table in the range E4:H9 lists information about suppliers the company is considering contracting with. Suong has created an area in the range E11:F14 for looking up data in the New_Suppliers table. In cell E14, begin to enter a formula using the HLOOKUP function to look up the industry rating for Clara Industries. Use the value in cell E13 as the value to look up and a structured reference to the entire New_Suppliers (New_Suppliers[#All]) table as the table to search. Display the value in row 4 of the table. Specify an exact match (FALSE) for the range lookup.

17. Next, find the ranking of a manufacturer listed in the New_Suppliers table. In cell F14, enter a formula using the MATCH function that specifies the manufacturer name in cell E12 as the value to look up. Use the Manufacturer column in the New_Suppliers table (range E5:E9) as the column to search. Specify an exact match (0) for the match type.

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.

Final Figure 1: Chicago Worksheet

Final Figure 2: Toledo Worksheet

Final Figure 3: Full Inventory Worksheet

Final Figure 4: Suppliers Worksheet