The financial statement presentation of debt securities categorized as trading, held-to-maturity, or available-for-sale as of March 31,
Fantastic news! We've Found the answer you've been seeking!
Question:
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%
Expert Answer:
Answer rating: 100% (QA)
List 1 List 2 100110MSFTTRAST 1001104650051000 100130ORCLAFSLT 1001301763016810 100140INTUTRALT 1001... View the full answer
Posted Date:
Students also viewed these accounting questions
-
For the data presented in BE16-4, show the financial statement presentation of the trading securities and related accounts. Discuss.
-
For the data presented in BE16-6, show the financial statement presentation of the available-for-sale securities and related accounts. Assume the available-for-sale securities are noncurrent. Discuss.
-
Financial Statement Presentation of Receivables Patriot Company shows a balance of $241,140 in the Accounts Receivable account on December 31, 2010. The balance consists of the following. Illustrate...
-
Quality Air Conditioning manufactures three home air conditioners: an economy model, a standard model, and a deluxe model. The profits per unit are $63, $95, and $135, respectively. The production...
-
What volatilities were used to construct each tree? (You computed zero-coupon bond prices in the previous problem; now you have to compute the year-1 yield volatility for 1-, 2-, 3-, and 4-year...
-
Refer to the Lopez Plastics Company illustration on pages 136 140 and to Exhibit 4-7. Also see the table in the solution to the first Summary Problem for Your Review on page 140. Based on new...
-
Eighteen plots of land in Indiana were given different applications of nitrogen. In each case, the previous crop had been soybeans and all other factors were roughly comparable. Use the 18...
-
Cook Corporation manufactures plastic garbage cans. In a typical year, the firm produces between 40,000 and 50,000 cans. At this level of production, fixed costs are $10,000 and variable costs are $2...
-
Pastina Company sells various types of pasta to grocery chains as private label brands. The company's reporting year - end is December 3 1 . The unadjusted trial balance as of December 3 1 , 2 0 2 4...
-
f. If there were $800 of dividends declared but not yet received by Maloney Co's from their investments, what adjustment would need to be made to Maloney's revenue?
-
What are the most serious climate risks the company faces and how will these risks impact global production and distribution of core products? Can these climate risks be mitigated and transformed...
-
Given the original array, how many of the following for statements enter an infinite loop at runtime, assuming each is executed independently? A. Zero. B. One. C. Two. D. Three. E. Four. F. The code...
-
What is the result of compiling and executing the following program? A. The code does not compile. B. The code compiles and prints a NullPointerException at runtime. C. The code compiles but does not...
-
Suppose we want to have two modules: com.ny and com.sf. Which is true about the placement of the module-info.java file(s)? A. One module-info.java file is required in position Z. B. Two...
-
Assuming the absolute path referenced in the code exists and its contents are accessible, which statement about the following code snippet is correct? A. The first stream statement does not compile....
-
Which of the following methods is not available on an ExecutorService instance? (Choose two.) A. execute(Callable) B. shutdownNow() C. submit(Runnable) D. exit() E. submit(Callable) F....
-
Andrew died in April of this year at the age of 74 and is survived by his wife Kim who is 69 years old. Among Andrew's assets is a RRIF that had a fair market value of $495,000 at the time of his...
-
The first national bank pays a 4% interest rate compound continuously. The effective annual rate paid by the bank is __________. a. 4.16% b. 4.20% c. 4.08% d. 4.12%
-
In early 2021, an article in the New York Times quoted Fed Chair Jerome Powell as saying that a robust job market can be sustained without causing an outbreak of inflation. a. What did Powell mean by...
-
Over the years, the Federal Reserve Board of Governors has changed its estimate of the NAIRU. The following table shows the boards NAIRU estimates for selected years. a. Why would the Board of...
-
In July 2022, in a press conference after a meeting of the FOMC, a reporter asked Chair Powell the following question: Within the Fed, there are staff economists whove argued that NAIRU, the...
Study smarter with the SolutionInn App