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 suchclassifications ofinvestment 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-data-sets.htmlHowever, the dataset is too large to open in Excel. So I have selected about 30 banksand constructed a sample of all their“NUM” datasetsfor 2011-2019.
You can download from here:https://www.dropbox.com/s/wvftfjxmuymjeic/xbrl_dataset.xlsx?dl=0
2.The “NUM” dataset includes nine fields of information. See: https://www.sec.gov/files/aqfs.pdfThe 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/excel-functions/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, thenclicking 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-with-multiple-criteria) and create a sheet thatcalculates your ratios.
5.Answer the questions below on a Word document.The Word document should be no longer than three pages.A one-paragraphanswerfor 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 yoursources.
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?
