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 Catherines 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 information she needs.
The directions are in the screenshots! PLEASE HELP and let me know how you did it, THANK YOU
A B C D E F G G H I I J K L L M NO P Q Q R 1 Chapter 10 2 Using Excel 3 P10-24 Using Excel to prepare balance sheet presentation of debt securities 4 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 5 second list contains the security ID and balance sheet amount(s). Catherine will have to manipulate the data to obtain the 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 to number 18 3. SORT 19 4. SUBTOTAL 20 21 Excel Help 22 23 Function Description Example Result Formula Displayed LEFT LEFT (Cell reference, 3) Displays 3 characters of the cell referenced 24 beginning with the leftmost character. APPLE APP =LEFT(C24,3) RIGHT RIGHT (Cell reference, 4) Displays 4 characters of the cell 25 referenced beginning with the rightmost character 123456 3456 =RIGHT(C25,4) The text functions return text. Combine the text function with 26 VALUE to return a number 27 VALUE VALUE (cell reference or formula) 123456 3456 =VALUE(RIGHT(C25,4)) 28 MID MID(cell reference, starting position, number of characters) FLOWERS LOWER =MID(C28,2,5) 29 MID combined with FIND becomes very powerful. MID(cell reference, FIND (specified character within double quotes, cell reference), number of characters) If you are trying to find a character that is not included in the output (such as a "/", you will need to add a number to the results a a of the FIND (before the comma in the formula) in order to obtain ( 30 the correct data. 123/456\789 456 =MID(C30,FIND("/",C30)+1,3) 31 FIND Instructions Securities + Ready + 100% A B C D E F L M N 0 P Q R First item 56 -51 Second Item Result 38 -32 Formula Displayed 38 EMIN(C33,D33) 51 MIN(C34:D34) Note: Values are separated by a comma in the formula Note: Values are separated by a colon in the formula ? My dia has beaders Then by Category OK Cancel 31 32 Function Description 33 MIN(cell ref1, cell ref 2) returns the smaller of the two. MIN 34 MIN(X:Y) can also refer to a range of cell references 35 36 37 38 SORT Sort 39 40 ed Cevel X Delitele Copy Level Options 41 Colum Sort On Order 42 Sort by Short-term or Long-term V ZA 43 20 44 45 46 47 48 49 50 51 52 53 54 SUBTOTAL Subtotal ? X 55 56 Beach change in Shorter Langerm 57 Use function 58 Sum 59 Add sobtotaltex 60 Security 61 Description Category 62 Stort term or long term were City 63 64 Replace current stor's 65 age break between groups 66 Summary below date 67 Bemove All OK Cancel 68 69 70 71 Subtotal 7 X 72 At each change in 73 74 Category Instructions Securities + A B C D E F G G H I J K L M N 0 P P Q Q R LE Low Cost for Value Replace gurren bil Page break between groups Summary below deta Bemove All OK Cancel Subtotal ? X At each change in Category Use function Sum Add subtotal to Security Description Category Short-term or long term Lower of Cost of fair Value 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 Replace current subtotals Page break between groups Summary below data Remove All OK Cancel Instructions Securities + B31 A B K N O 0 P a R R S fx 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. C D E F G H I J L M 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. Upon examination of the two lists of securities, Catherine noticed the following keys to the data: T T 1 OWN List 1 Security ID Security Description Category (TRA, HTM, AVS) Time (ST or LT) Characters 1-6 Characters 7-10 Characters 11-13 Characters 14-15 List 2 10 11 Security ID 12 Cost Characters 1-6 Variable number, follows the hyphen Variable number, follows the asterisk Fair Value 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 LIST 1 100110MSFTTRAST 100130ORCLAFSLT 100140INTUTRALT 100150ADBEAFSST 100160CSCOAFSST 100170AMZNTRAST 100190AAPLTRAST 100210PANWAFSLT 100240CHKPTRAST 100250TBILHTMST 100260TBILHTMST 100270TNOTHTMLT LIST 2 100110-46500*51000 100130-17630*16810 100140-61420*63600 100150-23500*28200 100160-59400*57400 100170-35000*35500 100190-42500*47500 100210-78000*80600 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. 31 32 33 List 1 List 2 Lower of Cost or Fair Value Short-term or Long-term Security ID Description Category Security ID Cost Fair Value 34 35 36 37 Instructions Securities + . B C D E F F G H I J K L M N N 0 P Q Q R R S T 30 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. 31 32 33 List 1 List 2 2 Lower of Cost or Fair Value Short-term or Long-term Security ID Description Category Security ID Cost Fair Value 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 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. 50 51 52 Combined Lists Short-term or Long-term Lower of Cost or Fair Value Security ID Description Category 53 54 55 56 57 58 59 60 61 62 63 64 65 66 Instructions Securities + A B D E F G H I J J K L M N N 0 P Q Q R S T 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 help. 69 70 71 Requirement 6: Compress the information so that only the subtotals appear (display level 3). 72 73 Short-term or Lower of Cost or Long-term Fair Value Security ID Description Category 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 102 1 Instructions Securities +
