Data Analytics Project XBRL (eXtensible Business Reporting Language) was adopted by the SEC to make financial statements easier to use. This project requires your understanding of the material covered in “Chapter 12 Investments”, basic knowledge of Excel, and XBRL.
Goal of the project:
Identify which banks had the most decrease in available-for-sale securities and most increase in held-to-maturity securities using XBRL. Then, explain why these banks experienced an increase in such classifications of investment securities by searching
financial statements, news articles, or other sources.
Instructions:
1. The raw data comes from: https://www.sec.gov/dera/data/financial-statement-datasets.html However, the dataset is too large to open in Excel. So I have selected about 30 banks and constructed a sample of all their “NUM” datasets for 2011-2019. You can
download from here: https://www.dropbox.com/s/wvftfjxmuymjeic/xbrl_dataset.xlsx?dl=0
2. The“NUM”datasetincludesninefieldsofinformation.See:
https://www.sec.gov/files/aqfs.pdf The first ten digits of the “adsh” field is a unique identifier for SEC filing entities. We call them CIK (central index key); they can be used to search companies on SEC EDGAR. Use the LEFT function (https://exceljet.net/excelfunctions/excel-left-function) to generate a field for CIKs. Select 10 banks.
3. Decide how you want to calculate ratios. For example, for available-for-sale securities, you can calculate available-for-sale securities divided by total assets or available-for-sale securities divided by total investment securities. Find the appropriate tags required to calculate these ratios by going to SEC EDGAR and finding the company’s 10-K, then clicking on “Interactive Data” > “Financial Statements” > “Consolidated Balance Sheets”
and clicking on the balance sheet item then clicking on “Details”.
4. Generate a new spreadsheet with columns for CIKs and dates. Use the INDEX and MATCH function to look up values (https://exceljet.net/formula/index-and-match-withmultiple-criteria) and create a sheet that calculates your ratios.
5. Answer the questions below on a Word document. The Word document should be no longer than three pages. A one-paragraph answer for each question is sufficient. Submit both the Word document and your Excel spreadsheet to Blackboard. Do not attach the original XBRL dataset.
Questions.
1. List the names of the 10 banks you selected.
2. Which bank has the most decrease in available-for-sale securities? Draw a graph that shows the trend over the sample period.
3. Why did this bank experience the decrease in available-for-sale securities? Find sources that support your answer from financial statements, news articles, and other sources.
Provide links to your sources.
4. Which bank has the most increase in held-to-maturity securities? Draw a graph that shows the trend over the sample period.
5. Why did this bank experience the increase in held-to-maturity securities? Find sources that support your answer from financial statements, news articles, and other sources. Provide
links to your sources.
6. What did you find most useful about using XBRL? As a professional accountant, how would you use XBRL?
7. What did you find most troubling about using XBRL?
