Using Excel
P10-24 Using Excel to prepare balance sheet presentation of equity and 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 information she needs.
Requirements
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), and then sort by category (in reverse alphabetical order).
5. Subtotal the securities first by time (short-term or long-term), and then by category (trading,
held-to-maturity, or available-for-sale). On the second subtotal, do not remove the previous
subtotals.
6. Compress the information so that only the subtotals appear (display level 3).
SOLUTION
The student templates for Using Excel are available online in MyAccountingLab in the Multimedia