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
Continuing Problem
P10-25 Accounting for debt and equity investments