BYU Student Author: @Millie_K_B
Reviewers: @Sterling_Lane, @Dallin_Gardner
Estimated Time to Solve: 30 Minutes
This challenge was written in partnership with an accounting professional. The data, scenario, or both were inspired by real-world business scenarios.
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
Overview
Your work at a financial planning office has you feeling shaken, not stirred. With all the things to keep track of, from retirement accounts to bank balances, you decide that it’s time to start improving some of your processes. Today’s project: looking up bond amounts.
Clients often own multiple bonds and want to know when the bonds will be redeemed. That way, they can see how much money they’ll have “freed up” or no longer tied to a bond and when, thus enabling them to make better plans for their financial future. Looking up these bond values is a tedious task, and you feel that using Excel data tables or pivot tables would make the process much quicker.
There are three clients that you need to help today that you can use to test your new tables.
- Steven and Deborah Johnson, who are married, want to know what money will be free by 9-30-2040.
- Emily Johnson would like to know how much is freed up by 3-31-2065.
- William Brown needs to know what he’ll have available by 12-31-2060.
Instructions
First, download the data file provided below. This file already contains all the bonds owned by your firm’s clients, along with their current value in dollars and maturity date. You can do all your work in this file.
For an extra challenge, import the raw .csv data file provided into a blank Excel workbook and format the bond values yourself. This will require creating new columns to calculate dollars and cents separately, using the NUMBERVALUE and TEXTSPLIT functions, and then summing your results. To see a solution for this extra portion, examine the formulas for the DOLLARS and CENTS columns in the provided xlsx data file.
Next, create a data table or pivot table that will allow you to filter the data by client. This will let you pull up each client’s bond portfolio.
Finally, you will need to add up the total amount that the client has tied up in bonds that will be released by the date given above. Essentially, if Jane Doe has seven total bonds, but only three of them will mature by the date she’s interested in, you only need to provide the total for those three bonds.
Data Files
- Challenge224_Bonds_James_Bonds.xlsx
- Challenge224_Bonds,_James_Bonds.csv
- Challenge224_Data_Dictionary
Suggestions and Hints
Bonds are traditionally reported in 32nds. To convert 32nds into cents, simply resolve the fraction, i.e., divide the numerator by the denominator. If you are using the .csv file and struggling to convert 32nds to dollars and cents, try the following formula:
Formula
=NUMBERVALUE(LEFT(TEXTAFTER([@PV]," ",),FIND("/",TEXTAFTER([@PV]," ",))-1))/NUMBERVALUE(RIGHT(TEXTAFTER([@PV]," ",),(LEN(TEXTAFTER([@PV]," ",)) - FIND("/",TEXTAFTER([@PV]," ",)))))
Make sure the client names are spelled correctly. There’s a Debra Johnson and a Stephen Johnson in the data set who are NOT the clients you’re looking for.
Remember to use the TOTAL_PV column for your sums.
Your company only recommends 10 unique bonds but serves 320 clients. This means you should use the ticker symbol as your pivot table row index for easier viewing.
Solution
Challenge224_Solution.xlsx
Solution Video: Challenge 224|EXCEL – Bonds, James Bonds
