90.2K

Verified Solution

Link Copied!

PLEASE HELP! I NEED HELP WITH REQUIREMENTS 3,4,&5. IF YOU COULD PLEASE INCLUDE FORMULAS FOR EACH COLUMN IN EACH INDIVIDUAL LIST IT WOULD BE GREATLY APPRECIATED!!!

F G H H B C D D E Requirement 1: Split the contents of LIST 1 into separate cells. Use functions LEFT, MID, RIGHT, FIND and VALUE as needed. (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab, you will be marked wrong.) 31 Requirement 2: Split the contents of LIST 2 into separate cells. Add a column to List 2 that calculates the lower of cost or fair value by security using the function MIN. (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab, you will be marked wrong.) 32 33 34 List 1 List 2 Cost Short-term or Long-term ST LT LT - ST 35 36 37 38 39 40 41 42 43 44 45 Security ID 100210 100230 100240 -24 100250 100260 way 100270 100290 100310 100340 100350 100360 100370 Description NSFT PROL FACE JNTU BDBE DSCO BMZN BAPL QANW DHKP UBIL UBIL UNOT Category TRA AFS TRA AFS AFS TRA TRA AFS ST ST ST LT ST ST ST LT Security ID 100210 100230 2002 100240 100250 100260 ave 100270 100290 100310 100340 100 100350 100360 100370 56,500 19,630 61,420 23,500 ware 58,400 35,250 45,500 82,000 15,510 19,950 18,900 49.790 Fair Value 51,000 10,810 16,810 64,600 25,200 57,400 35,500 47,500 80,600 16,686 19,960 18,920 49,770 Lower of Cost or Fair Value 51,000 16,810 10,010 01420 61,420 23,500 w 57,400 35,250 A 45,500 CO 80,600 15,510 19,950 18,900 49,770 TRA HTM HTM HTM 46 47 48 49 Requirement 3: Create a new list of securities that has Description. Category, Short-term or Long-term, and Lower of 50 Cost or Fair Value. Use VLOOKUP (with an exact match) to create the list using both List 1 and List 2. (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the 51 Instructions tab, you will be marked wrong.) , . 52 53 Combined Lists Short-term or Lower of Cost or 54 Security ID Description Category Long-term Fair Value 55 56 57 58 59 60 61 62 63 64 65 66 bb 67 68 Instructions ENTERANSWERS + Ready 2 IT + 90% B C D E G H 68 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). 69 70 Short-term or Lower of Cost or Fair Value Security ID Description Category Long-term 71 72 73 74 75 13 76 77 " 78 79 80 81 82 83 84 85 Requirement 5: Copy the sorted list and paste values to a new location (below). 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 help. 86 87 88 89 90 Short-term or Long-term Lower of Cost or Fair Value Security ID Description Category 91 92 93 94 95 96 30 97 98 99 100 101 102 103 104 105 106 Instructions ENTERANSWERS + Ready + 90% F G H H B C D D E Requirement 1: Split the contents of LIST 1 into separate cells. Use functions LEFT, MID, RIGHT, FIND and VALUE as needed. (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab, you will be marked wrong.) 31 Requirement 2: Split the contents of LIST 2 into separate cells. Add a column to List 2 that calculates the lower of cost or fair value by security using the function MIN. (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab, you will be marked wrong.) 32 33 34 List 1 List 2 Cost Short-term or Long-term ST LT LT - ST 35 36 37 38 39 40 41 42 43 44 45 Security ID 100210 100230 100240 -24 100250 100260 way 100270 100290 100310 100340 100350 100360 100370 Description NSFT PROL FACE JNTU BDBE DSCO BMZN BAPL QANW DHKP UBIL UBIL UNOT Category TRA AFS TRA AFS AFS TRA TRA AFS ST ST ST LT ST ST ST LT Security ID 100210 100230 2002 100240 100250 100260 ave 100270 100290 100310 100340 100 100350 100360 100370 56,500 19,630 61,420 23,500 ware 58,400 35,250 45,500 82,000 15,510 19,950 18,900 49.790 Fair Value 51,000 10,810 16,810 64,600 25,200 57,400 35,500 47,500 80,600 16,686 19,960 18,920 49,770 Lower of Cost or Fair Value 51,000 16,810 10,010 01420 61,420 23,500 w 57,400 35,250 A 45,500 CO 80,600 15,510 19,950 18,900 49,770 TRA HTM HTM HTM 46 47 48 49 Requirement 3: Create a new list of securities that has Description. Category, Short-term or Long-term, and Lower of 50 Cost or Fair Value. Use VLOOKUP (with an exact match) to create the list using both List 1 and List 2. (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the 51 Instructions tab, you will be marked wrong.) , . 52 53 Combined Lists Short-term or Lower of Cost or 54 Security ID Description Category Long-term Fair Value 55 56 57 58 59 60 61 62 63 64 65 66 bb 67 68 Instructions ENTERANSWERS + Ready 2 IT + 90% B C D E G H 68 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). 69 70 Short-term or Lower of Cost or Fair Value Security ID Description Category Long-term 71 72 73 74 75 13 76 77 " 78 79 80 81 82 83 84 85 Requirement 5: Copy the sorted list and paste values to a new location (below). 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 help. 86 87 88 89 90 Short-term or Long-term Lower of Cost or Fair Value Security ID Description Category 91 92 93 94 95 96 30 97 98 99 100 101 102 103 104 105 106 Instructions ENTERANSWERS + Ready + 90%

Answer & Explanation Solved by verified expert

See Answer