Audit Analytics & companies in trouble

One of the databases that allows you to investigate companies that are in trouble is Audit Analytics (AA). One specific variable in the databases of AA allows you to select companies that are bankrupt or in liquidation, etc. The variable is called “Termination Status“. According to the Data dictionary this variable: “Indicates whether the registrant has formally de-registered with the SEC. We indicate the cause when possible, 2 = Went Private, 3 = Bankruptcy, 4 = Merger/Acquisition, 5 = Termination, 6 = Liquidation“. The variable can be found in the Auditor Opinion part database at search Step 3 in Wharton under the first box with company information/variables.

If you use the Wharton version of AA you can find companies (that are in trouble) only through using the option at Step 2 “Search entire database“. Unlike many databases the AA database can not be searched using a conditional statement at step 2 when choosing this search option. This means that you will have to download some basic company data on all companies for a certain time period and include the variable “Temination Status” in the download (NB: do not choose the Wharton download option Excel/XLS/XML if you think you may get more then 65,000 rows in the output as the download will be cut short at this point. You can choose tab-delimited text because these text files can be opened in Excel).

Afterwards, when you have opened the text file in Excel you can use the filer option to make visible only companies with the status 3 and 6 (and maybe 5, if necessary). See below an example screenshot.

After using the filter to get only bankrupt companies you can use the codes in the column COMPANY_FKEY to do another search in the same database and then look up information on these companies only. The COMPANY_FKEY is the same code as mentioned at Wharton search Step 2: Company Key (CIK). NB: If you have many codes (and many duplicates) you can copy this column to a new Excel sheet and (in Excel 2007) go to the tab Data (or Gegevens) and use the option in the Excel ribbon to remove duplicates. Then copy the codes and create a text file which can be uploaded at search Step 2 (in Wharton): Upload a file containing company codes.

Next at step 3 you indicate which variables are of interest when investigating companies in trouble. For example: it may be intresting to know if there was a Going Concern issue for these companies.

Email

Data research using Microsoft Access

A short while ago I was asked how to get data from Audit Analytics on companies that did not have restatements. The idea of the student for the thesis was to compare Audit data from companies that had had restatements with companies that had not. The first part was not that hard: Audit Analytics has a specific restatements part database which you can search through for specific restatements.

The difficult bit was finding companies for the same period that did not have restatements. It was also important to find similar US companies that are active in the same industry (using SIC codes). My solution was using the Compustat North America database to get US companies that had had statements filed in fiscal year 2009.
Using the output from that database I used Microsoft Access to match data from Audit Analytics with output from Compustat North America. Based on the match I could find companies that did not match by looking at the CIK codes. I could then also find matching companies with similar SIC codes.
Using a list of CIK codes from that match and uploading this in Audit Analytics I could get Audit data on companies that had not filed restatements. Afterwards you can use the links within Audit Analytics to the public SEC database Edgar to see original filings.

Here you can view an example movie of this type of research. The subject of the example in the movie is: Find Audit data for US companies that have had restatements on their fiscal year 2009 and compare them with similar type US companies that have not had restatements over the fiscal year 2009.

Email

Audit Analytics and S&P indexes

The database with Audit information offers the option to use a list of tickers or CIK numbers and then (if available) audit information on these companies. Indexes of stock exchanges are very often used as lists to do research. The S&P indexes are the most often used indexes. In the past I have blogged on some of the most often used S&P indexes like the S&P 500, S&P 400 and S&P 600 (together they form the S&P1500). The older blog posts covered Datastream, Compustat North America and the S&P websites.

Audit Analytics (AA) also offers a feature to make it easier to do research on companies that are part of the abovementioned S&P indexes. The option is available (only) through the part database “Auditor – Engagements” in the IVES website version of AA (not available in the WRDS version of AA):

The composition of indexes like those of the S&P 500 do change over time. It is therefore important to know how recent these indexes are. The small I-icon next to the selection box of these (market) indexes leads to a help (pop-up) screen that explains more about this. The indexes on offer in AA are specific to a point in time. Example pop-up:

If you want to use the last composition or historical indexes it may be a good idea to use a database like Compustat North America  or Datastream to get these. Just remember that, when using historical indexes, you may not always recognize the companies in the lists as company names and/or Tickers change!

NB: In addition to the three S&P indexes it is also possible to make use of the Russell 1000, Russell 2000 and Russell 3000 indexes.

Email

Audit Analytics & company changes

The database with Audit information offers two options to search for information on companies, You can search by name, or you can search by company identifier. The company identifiers that can be used to search are Ticker (symbol) and CIK numbers. The CIK numbers remain unique for companies. Tickers may change over time because they identify different companies over longer periods. Companies may also change Tickers depending on how companies evolve over time.

Some of my last posts here I checked in a few database how they deal with company changes and I used as an example FORTUNE BRANDS Inc. This company was recently split into BEAM Inc and the demerged FORTUNE BRANDS HOME & SECUR Inc. I also checked the Audit Analytics database and found that:

1) Name changes are handled fine: searches on old names will find you the company under the new name. Example looking up Fortune Brands will find you BEAM :

2) Tickers changes are tricky: old tickers can no longer be used to search for a company. The old ticker FO of Fortune Brands will not lead to the company BEAM. This means that working with historical Ticker lists of indexes like the S&P 500 from databases or websites may prove tricky.

Email

Audit Analytics versions

The database Contains information on the accounting and financial institutions in the United States of America. The database is used by:

* 18 of the top 20 public accounting firms
* 4 of the top 5 Errors And Omissions insurers
* Over 200 universities
* 7 regulatory bodies.

Audit Analytics covers over 20,000 public companies and more than 1,500 accounting firms from filings at the Security and Exchange Commission. Two versions may be used of the Audit Analytics database: the version availabe through the IVES Group company website and (if you have access to it) the version available through the WRDS portal.

The main advantage of the WRDS version is, that it is easy to download data for many years in one go, using the easy 4 steps that are virtually exactly the same for each database that can be used through WRDS. You may need to study the Audit Analytics manual (= Data dictionary excel file) carefully to find out which data items are available in which part-databases.

The IVES website version, however, is more intuitive and allows for easy switching between the separate parts and clicking on to specific sections for more data. Also, it offers many links to the SEC database Edgar with its public SEC Filings (the full historical Edgar is available through LexisNexis).

Email