Illustrated Excel 2019 | Module 5: SAM Project 1b Amari Family

GETTING STARTED

1. Open the file IL_EX19_5b_FirstLastName_1.xlsx, available for download from the SAM website.

2. Save the file as IL_EX19_5b_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_5b_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. Michael Amari works as an independent financial advisor and is developing an Excel workbook to track his home and office inventory. If the property in his family home or office is damaged or stolen, he can use the inventory to replace furniture, equipment, and other items. He asks for your help in managing and analyzing the inventory data. Go to the Home worksheet. Michael wants to sort and filter the data on the worksheet, which lists his home inventory data. Format the range A3:F42 as a table with headers using the Orange, Table Style Medium 2 table style.

2. Michael and his family purchase items online or in physical stores. He wants to make sure that he and other family members enter only "Online" or "Store" in the Where Purchased column for new items added to the inventory list. Create a data validation rule for the Where Purchased column as follows:

a. For all cells in the Where Purchased column, create an in-cell dropdown data validation rule that accepts only entries from a list.

b. Use Online, Store as the Source values.

c. Provide an input message that uses Where Purchased as the title and the following sentence as the input message: Select Online or Store.

d. Set a Stop style error alert that uses Invalid entry as the title and the following sentence as the error message: You can enter only Online or Store.

3. Michael and his wife recently purchased a new chair, and he wants to add it to the home inventory. He also wants to delete any duplicate records from the table. Add and remove records as follows:

a. Find and remove a record with a duplicate Item ID number.

b. 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 Where Purchased value.

Table 1: Data for the New Record

Item ID | Room | Description | Make/Model | Purchase Price | Where Purchased 156 | Bedroom 2 | Desk chair | Bayshore Designs | 150 | Online

4. The table is currently sorted by room name, but Michael prefers to have it sorted by item ID so he can find items quickly. Sort the table in ascending order (smallest to largest) by the values in the Item ID column.

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

6. Michael and his wife are thinking of moving in the next two years and want to refurnish the living room to increase its appeal to potential buyers. Michael wants to list the living room items separate from the table so that he can calculate replacement costs. Use an advanced filter to provide this list for Michael as follows:

a. In cell H4, enter Living room as the condition to use for filtering the criteria range.

b. Create an advanced filter that copies the results to another location.

c. Use the table of home inventory data (range A3:F42) as the List range.

d. Use the information you set for filtering (range H3:H4) as the Criteria range.

e. Copy the results to the range starting in cell H6.

f. Resize columns H:M to their best fit.

7. Go to the Office worksheet, which includes a filtered table named Office. Remove the filter from the Office table to display all of the records.

8. Apply the Orange, Table Style Medium 2 table style to the Office table to use a format similar to other tables in the workbook.

9. Michael notices that the record for Item ID 331 is incomplete. Remove the record for Item ID 331 from the table.

10. The Purchase Price column lists the price Michael paid for each office item, and the Loss of Value column lists how much value each item has lost depending on the purchase data and condition. Michael wants to display the current value for each item, the total current value for all items, and the number of items in the inventory. Provide this information for Michael as follows:

a. Insert a new column in the table to the right of the Loss of Value column.

b. Use Current Value as the column heading of the new column.

c. In cell H8, enter a formula without using a function that uses structured references to subtract the loss of value amount for the first item ([@[Loss of Value]]) from the purchase price ([@[Purchase Price]]).

d. Apply the Accounting number format with zero decimal places to the newly created column (if necessary).

e. Add a Total Row to the Office table, which automatically totals the amounts in the Current Value column.

f. In cell C30, use the total row to display the count of the items.

11. Michael has created an area in the range J1:L4 for looking up data in the Office table. First, he wants to find the purchase price for a specific item. He has already entered the item ID in cell J2. Look up the price of the item with Item ID 332 as follows:

a. In cell J4, begin to enter a formula using the VLOOKUP function.

b. Use the Item ID in cell J2 as the value to look up.

c. Use a structured reference to the Office table as the table to search.

d. Display the value in the Purchase Price column (column 6) of the table.

e. Specify an exact match (FALSE) for the range_lookup argument.

12. Michael also wants to calculate the total cost of replacing items in a specific category. He has already entered the category number in cell K2. Calculate the total cost of replacing items in Category 2 (office equipment) as follows:

a. In cell K4, begin to enter a formula using the DSUM function.

b. Use a structured reference to the Office table (beginning in cell A7) including the headers and the data as the formula database.

c. Use a structured reference to the Purchase Price field header as the field to summarize.

d. Use the values Michael set up in the range K1:K2 as the criteria.

13. The third calculation Michael wants to make is to identify the number of replacement items, which are items in poor condition. Calculate the number of items in poor condition as follows:

a. In cell L4, begin to enter a formula using the DCOUNTA function.

b. Use a structured reference to the Office table (beginning in cell A7) including the headers and the data as the formula database.

c. Use a structured reference to the Condition field header as the field to count.

d. Use the values Michael set up in the range L1:L2 as the criteria.

14. Michael wants to list the items in each category that have a current value of $250 or more to identify items that are worth more than his current homeowners insurance deductible amount. Filter and sort the Office table as follows:

a. Use a number filter in the Current Value column to display courses with values greater than 250. (Hint: Click the Filter button in the Sort & Filter group on the Data tab to display the filter arrows in the table.)

b. Sort the filtered table in ascending order first by Category and then by Current Value.

15. Go to the Complete Inventory worksheet, which includes a table named Inventory that lists all the home and office personal property. Michael wants to identify items from the bedrooms.

a. Apply banded rows to the Inventory table to make it easier to read.

b. Filter the Inventory table using a custom AutoFilter to display only items from room names beginning with "Bedroom".

16. Prepare the Complete Inventory worksheet for printing by using the data in row 6 as the print titles.

17. Go to the Insurance worksheet, which includes information about the Amari's current and potential insurance policies. Michael included a table named Coverage in the range A3:C9 but does not plan to filter or sort the data or use any other table features with it. Convert the Coverage table to a range.

18. Michael wants to insert a table listing the coverage and premium amounts for condo and renter's insurance since he and his wife are considering condos and apartments for their next move. Insert the new table as follows:

a. Beginning in cell A11, enter the data shown in Table 2.

b. Format the range A11:C13 as a table using the Orange, Table Style Medium 2 table style, specifying that the table has headers.

c. Use CondoRenters as the name of the new table.

Table 2: Data for the New Table

| A | B | C 11 | Type | Coverage | Premium 12 | Condo | $60,000 | $389 13 | Renters | $40,000 | $207

19. The Companies table in the range E3:K7 lists information about the Amari's current insurance company (Highland) and five other companies they are considering, including the score assigned by an independent insurance evaluation agency. Michael has created an area in the range E11:F14 for looking up data in the Companies table. Look up the score assigned to the Highland insurance company:

a. In cell E14, begin to enter a formula using the HLOOKUP function.

b. Use the value in cell E12 as the value to look up.

c. Use a structured reference to the entire Companies table as the table to search.

d. Display the value in row 4 of the table.

e. Specify an exact match (FALSE) for the range lookup.

20. Find the ranking of a company listed in the Companies table as follows:

a. In cell F14, begin to enter a formula using the MATCH function.

b. Use the company name in cell E12 as the value to look up.

c. Use the range F3:K3 as the range to search.

d. 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: Home Worksheet

Final Figure 2: Office Worksheet

Final Figure 3: Complete Inventory Worksheet

Final Figure 4: Insurance Worksheet