need help with this Excel sheet following requirements listed in first pic

F G H K L M N Chapter 10 Using Excel P10-24 Using Excel to prepare balance sheet presentation of debt securities The financial statement presentation of debt securities categorized as trading, held-to-maturity, or available-for-sale as of March 31, 2018, is due shortly to Catherine's supervisor. She has received lists of the securities and balance sheet amounts from the IT department. However, one list of securities links together the security ID, the security name, the security category, and whether it is short-term or long-term. A second list contains the security ID and balance sheet amount(s). Catherine will have to manipulate the data to obtain the 5 information she needs. 6 7 Requirements 8 1. Split the contents of the two lists into separate cells. Use functions LEFT, MID, RIGHT, FIND and VALUE as needed. 9 2. Add a column to List 2 that calculates the lower of cost or fair value by security using the function MIN. 10 3. Create a new list of securities that has Description. Category, Short-term or Long-term, and Lower of Cost or Fair Value. This new list should contain values (not formulas). 11 4. Copy the new list to a new location. Sort securities as to whether they are short-term or long-term (in reverse alphabetical order), then sort by category (in reverse alphabetical order). 12 5. Subtotal the lower of cost or market of the securities first by time (short-term or long-term), then by category (trading, held-to-maturity, or available-for-sale). On the second subtotal, do not remove the previous subtotals. 13 6. Compress the information so that only the subtotals appear (display level 3). 14 15 Excel Skills 16 1. LEFT, MID, RIGHT, FIND, VALUE, and MIN functions 17 2. Copy, paste as value; Convert number 18 3. SORT 19 4. SUBTOTAL B C F G Variable number, follows the asterisk Fair Value LIST 1 100110MSFTTRAST 100130ORCLAFSLT 100140INTUTRALT 100150ADBEAFSST 100160CSCOAFSST 100170AMZNTRAST 100190AAPLTRAST 100210PANWAFSLT 100240CHKPTRAST 100250TBILHTMST 100260TBILHTMIST 100270TNOTHTMLT LIST 2. 100110-46500*51000 100130-1763016810 100140-61420*63600 100150-23500*28200 100160-59400*57400 100170-35000*35500 100190-42500*47500 100210-78000*80500 100240-17510*16686 100250-9950*9960 100260-19900*19920 100270-49750-49770 Requirement 1: Split the contents of the two lists into separate cells (use List 1 and List 2 below). Use functions LEFT, MID, RIGHT, FIND and VALUE as needed Requirement 2: Add a column to list 2 that calculates the lower of cost or fair value by security using the function MIN. List 1 List 2 Security ID Lower of Cost or Fair Value Fair Value Description Short-term or Long-term Category Security ID Cost Instructions Securities G H Requirement 3: Create a new list of securities that has Description, Category, Short-term or Long-term, and Lower of Cost or Fair Value. Use VLOOKUP (with an exact match) to create the list using both List 1 and List 2 Combined Lists Security ID Short-term or Long-term Lower of Cost or Fair Value Description Category 3 2 3 4 -5 -6 Requirement 4: Copy the new list and paste values to a new location (below). Sort securities aqiy time (short-term or long-term) in reverse alphabetical order, then sort by category (in reverse alphabetical order). 57 58 Requirement 5: Subtotal the lower of cost or market of the securities, first by time (short-term or long-term), then by category (trading, HTM, or AFS). On the second subtotal, do not remove the previous subtotals. See the instructions for 69 70 71 Requirement 6: Compress the information so that only the subtotals appear (display level 3). 72 73 Security ID Description Short-term Lower of Cost or Long-term or Fair Value Category 74 75 76 77 78 Instructions Securities D E F G H K 64 65 66 Requirement 4: Copy the new list and paste values to a new location (below). Sort securities as to time (short-term or long-term) in reverse alphabetical order, then sort by category (in reverse alphabetical order). 67 68 Requirement 5: Subtotal the lower of cost or market of the securities, first by time (short-term or long-term), then by category (trading, HTM, or AFS). On the second subtotal, do not remove the previous subtotals. See the instructions for 69 70 71 Requirement 6: Compress the information so that only the subtotals appear (display level 3). 72 73 Security ID Description Category Short-term Lower of Cost or Long-term or Fair Value 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 196 Instructions Securities