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 information she needs.

 

Transcribed Image Text:

N O A D F G K L M P Q 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. 2. Add a column to List 2 that calculates the lower of cost or fair value by security using the function MIN. 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). 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). 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. 9 10 11 12 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 24 LEFT (Cell reference, 3) Displays 3 characters of the cell referenced beginning with the leftmost character. APPLE АPP =LEFT(C24,3) RIGHT 25 RIGHT (Cell reference, 4) Displays 4 characters of the cell referenced beginning with the rightmost character. 123456 3456 =RIGHT(C25,4) The text functions return text. Combine the text function with VALUE to return a number 26 3456 =VALUE(RIGHT(C25,4)) =MID(C28,2,5) 27 VALUE VALUE (cell reference or formula) 123456 28 MID LOWER MID(cell reference, starting position, number of characters) MID combined with FIND becomes very powerful. FLOWERS 29 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 of the FIND (before the comma in the formula) in order to obtain the correct data. FIND 30 123/456\789 456 =MID(C30,FIND("/",C30)+1,3) 31 Instructions Securities + Ready + 100% N O A D F G K L M P Q 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. 2. Add a column to List 2 that calculates the lower of cost or fair value by security using the function MIN. 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). 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). 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. 9 10 11 12 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 24 LEFT (Cell reference, 3) Displays 3 characters of the cell referenced beginning with the leftmost character. APPLE АPP =LEFT(C24,3) RIGHT 25 RIGHT (Cell reference, 4) Displays 4 characters of the cell referenced beginning with the rightmost character. 123456 3456 =RIGHT(C25,4) The text functions return text. Combine the text function with VALUE to return a number 26 3456 =VALUE(RIGHT(C25,4)) =MID(C28,2,5) 27 VALUE VALUE (cell reference or formula) 123456 28 MID LOWER MID(cell reference, starting position, number of characters) MID combined with FIND becomes very powerful. FLOWERS 29 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 of the FIND (before the comma in the formula) in order to obtain the correct data. FIND 30 123/456\789 456 =MID(C30,FIND("/",C30)+1,3) 31 Instructions Securities + Ready + 100%

Answer rating: 100% (QA)

List 1 List 2 100110MSFTTRAST 1001104650051000 100130ORCLAFSLT 1001301763016810 100140INTUTRALT 1001... View the full answer