Скачать или смотреть Illustrated Excel 2019 | Module 5: SAM Project 1b

Please contact us if you need this project or any other project in any subject to be completed.
Whatsapp: +1-343-321-3276
Email: [email protected]
WhatsApp Link: https://wa.me/13433213276
#IllustratedExcel2019 #Module5SAMProject1b
#AmariFamily
Illustrated Excel 2019 | Module 5: SAM Project 1b
Amari Family
MANAGE AND ANALYZE TABLE DATAPROJECT 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.