PPPCasewithFormatting

Investigating the Payment Protection Plan (PPP) Loan Data

Contents

Case Brief 2

Background 2

Accounting Analytics 3

Data and Additional Resources 5

Case Brief

The accounting analyst is expected to be able to ask the right questions of the data. To do so, they should seek to identify if a problem currently exists (perhaps as directed by the client) or identify whether a problem could exist. This case will focus on developing investigative data analysis to help identify whether a problem could exist. Specifically, whereas it is anticipated that most loans being made under the PPP program will be used honestly to retain employees and remain operational, there is always a chance that fraud could exist. This case includes a lab session and discussion of your findings. Your lab session task is to investigate the PPP loan database, and to identify suspicious loans.

Background

In response to the economic fall-out from the Coronavirus and COVID-19, the United States Government enacted the Paycheck Protection Plan (PPP). The PPP was aimed at supporting small businesses offering loans of up to $10 Million to keep workers on the payroll. The PPP loans are potentially forgivable if they are used to cover payroll expenses and are guaranteed by the Small Business Administration (SBA). Loans granted through the PPP were implemented at the bank/financial institution level. In Washington State alone, over 100,000 loans were made under the PPP program.

Recent media articles have identified cases of fraudulent behavior relating to the PPP loan program. For example, a 29-year-old Florida man was arrested and charged with fraudulently obtaining $3.9 million of PPP loans, which he allegedly spent in part on a 2020 Lamborghini Huracan, dating websites, Miami Beach Resorts, and luxury jewelry. In addition, of the four companies he used to apply for the PPP loans through, none had websites and at least one was listed as inactive on the Florida Department of State website.

Accounting Analytics

There are many ways to undertake this investigative analysis, at a high level you will undertake the following broad steps: (1) Extract, Transform and Load the PPP data so that it can be analyzed, (2) calculate descriptive statistics for key features of the data to better understand the nature of the loans, (3) use visual analytics to further understand the nature of the loans and potentially highlight outliers, (4) use text-based analytics to further identify potentially problematic loans within the data.

Extract, Transform, and Load: On their website, the SBA website provides two types of files relating to the PPP loans: (1) a single dataset containing all PPP loans for amounts more than $150,000 across the country; and (2) one dataset per state and each US Territory containing PPP loans for amounts under $150,000.¹ The files contain different information, for the large loans, the SBA provides the names and addresses of the loan recipient, but for the smaller loans they only include the zip code. The smaller loans have the exact amount, whereas the larger loans are reported in bands (e.g., between $1-2 million dollars). In addition, the file contains North American Industry Classification (NAICs) codes but does not provide labels for the NAICs codes making them difficult to interpret without joining data from NAICs. In both cases, however, they do provide the estimated number of jobs retained by these loans and the bank or financial institution that made the loan. The data is interesting, but, like most real-world data, messy, making it a multi-step ETL task.

Descriptive Analytics: Once the data ETL has been completed, the next step is to investigate the key features of the data. We can do this by exploring descriptive, or summary statistics about the data. As an exploratory case, no matter where you start, you can not go wrong! To get started, we may want to know how many loans were made under this program, or how many per state. Other accounting analytic questions we could ask include, what is the average size of the small loans and how many jobs were estimated to be retained on average per loan? Which bank made the most loans? You may want to get a sense for how many dollars were received on average to retain a single job. Or perhaps, which state had the largest big loans? Note that in the dataset, the loans between $5-10 million are the biggest, so we can figure out which state received the most of these largest loans, but we can not determine which state received the most actual loan dollars from this dataset. After undertaking these descriptive steps, you will have a good idea about the average, or normal, features of the data, and you can begin to explore the data for possible anomalous, or abnormal, loans.

Visual Analytics: With a good sense for the data we can begin to look for anomalies by investigating the patterns in the data. Visualization provides one approach to aid the data exploration at this point, as we can see from the patterns in the loan data the common patterns as well as the loans that look different, often referred to as outliers. Suppose our descriptive analysis suggests some of the states with the largest populations received the most loans, a pattern we would expect, we could calculate the number of loans per capita, and investigate which states were above the average and which below when controlling for population. For example, we may note that New York and California rank highly in the number of loans received, but did they receive abnormally high or low numbers of loans given their relative population? These kinds of visual analytics are nicely aided by software that can provide geographical visualizations and X-Y visualizations which look great on a dashboard. If we get ambitious, we could even create a dynamic, or interactive dashboard that allows us to examine many different loan features in the data, or we could drill-down into the data by examining hierarchies like moving from the State to Zip code level. There is certainly plenty to explore.

Investigating individual loans: Using modern software, visual analytics will generally allow for the identification of which loans deviate from the pattern. For example, suppose we created an X-Y figure which compares the dollar amounts of the loans to the number of jobs retained. We would anticipate that the largest loans and those that retained the most jobs are positively correlated, i.e., these loans generally appear in the top-right corner of the X-Y figure. Based on this expectation, we may wish to examine the loans in the top-left corner of the X-Y figure, as these are the largest loans with the smallest number of jobs retained. Can we explain these loans? Perhaps they are in the industries with the highest paying jobs and those in the top-right are those with the lowest paying jobs. Or perhaps there are errors, or even worse, fraud. We can further investigate these loans by simply searching for the business name on the web to see if it has an active website.

We can also use textual analytics to investigate the quality of the loan applications using the business name and address fields, there are many possibilities here. For example, we could see identify simple spelling mistakes by comparing these fields to a dictionary, we could examine whether multiple business names are associated with the same address, or the same address is associated with multiple businesses. If we get ambitious, we could try and match the business names and addresses to state business registries and flag those that do not match, or those that are deregistered, for further analysis. The loans associated with these anomalies flag potentially suspect loans and could either be mistakes or fraud, and clearly warrant further attention.

In our test run of this case, we found all sorts of interesting loans in this large dataset. But we only scratched the surface. There is plenty left to explore, and we look forward to discussing your findings!

Data and Additional Resources

The following data and resources are available in the case supplement:

1. The raw data downloaded from the SBA is provided in multiple spreadsheets.

2. As this case is split across courses in the MPAcc, each instructor will provide specific directions and hints to achieve each step above and explore the data.