Monday, July 19, 2021

New Oregon Excel Workbook: July 2021 Data for $10.5 Billion in PPP Loans and RR Grants, and a Breakout of Double Dippers.

Last week I posted HERE a first look at Oregon restaurants ("Double Dippers") who had received both SBA Restaurant Relief grants and at least one SBA Payroll Protection Program (forgivable) loan. [It's possible that some RR grantees could move into the triple dipper category if they had more than one PPP loan, but I did not focus on that]. 

I noted that SBA did not make it easy to identify those RR recipient restaurants in PPP loan data. It has taken me some time and a lot of effort to assemble a new comprehensive Excel workbook that includes and consolidates that Oregon information.  

The 50 MB Excel workbook HERE includes 21 worksheets with ALL Oregon July 2021 data for PPP loans, RR grants, and a mash up of PPP loans that went to RR grantees (Double Dippers).

Content Overview

I explain the organization of the workbook in the default opening READ ME worksheet that you may also view as a PDF document HERE. I strongly encourage you to read this document before you begin digging into the Excel file.

After the READ me file the first worksheet is likely the most important as it a PIVOT worksheet that focuses on RR grantees who also got PPP loans--the Double Dippers. 

Similar PIVOT worksheets are available for the RR grantees and PPP recipients. 

This first pivot table shows by county the amounts for 

  • RR grants
  • Currently approved PPP loans
  • PPP loan forgiveness, 
  • The combined of RR grants and the forgiven amount of currently approved PPP loans
  • The percentage of PPP current approved PPP that has been forgiven, 

Note: In the Pivot Tables users can switch from the "summarize values by sum" default to a "summarize values by count" using the Excel pull down.  This will count the number of grants/loans etc. instead of totaling them.  

Oregon Statewide Summary

  • $10 Billion in PPP loans and, so far, $5.3 billion in PPP loan forgiveness. 
  • $111 million in PPP loans and $45 million in PPP loan forgiveness went to applicants who got got both RR grants and PPP loans. (Double Dippers) 
  • $530 million in RR grants. $325 million of that went to Double Dippers.
  • Double Dippers so far have received total federal subsidy of $370 million--$325 million from RR grants and $45 million from PPP loan forgiveness. 
  • Total federal subsidy so far for both PPP and RR is $5.87 billion. 
  • The difference between currently approved PPP loans and forgiven loans is $4.7 billion. IF all of that were forgiven then total federal subsidy for PPP and RR would be $10.6 billion. 
  • In comparison the most recent Oregon Employment Department dashboard indicates a total of $9.9 billion has been paid in Oregon for unemployment insurance from March 15, 2020 to July 14, 2021. (That includes both federal funds and employer paid payroll taxes into the UI fund).

Organization of the Workbook

The workbook has 4 primary sections: Double Dippers, RR grantees, PPP loans, and a utilities section. The workbook has some summary worksheets that provide county or city level summaries. ALL three program sections (Double dippers, RR grantees and PPP loans) also include Pivot tables to allow users to fully explore the data within each category. 

Race Ethnicity Breakouts

I have also added a "DD Race Ethnicity Summary" worksheet for the Double Dippers that shows a breakout by race and ethnicity. Similar data can be extracted for PPP loans, but not for RR grants alone. 

The following worksheet "PPP DD Race Ethnicity Summary"  shows the share of Double Dipper PPP loans for minority groups vs their share for all PPP loans. ( Percentages are calculated ONLY on the counts of loans where race and ethnicity data is known--a large share of the PPP loans do not have race or ethnic data).

Likely reflecting the current racial and ethnic mix of restaurants this worksheet shows that some minority groups who had both DD grants and PPP loans were represented at higher rates than their share of all PPP loans alone. Conversely, both whites and blacks who were double dippers were represented at a lower rate than their share of all PPP loans alone.  Note: The share differences may not have been as significant if the PPP only loans universe used for comparison was reduced to only restaurant NAICS codes instead of all PPP loans. While I did not complete this detailed further analysis all the available data needed is in this workbook. It is also possible that unreported  PPP race  data contains a higher percentage of white and black businesses, but it is unreported. 

Within the data worksheets users can also use filters at the top of the columns to isolate loans or grants of interest, including top 10, bottom 10 and so on. 

SBA Data Issues: 

There are a number of quirks in the SBA provided data; some examples in the RR grant data

  • There is no race or ethic data, instead a socially disadvantaged data field in the RR grant data. If a RR grantee also has a PPP loan, race and ethic data IS available from the PPP loan data. 
  • There is no county data field. If a RR grantee also has a PPP loan race and ethic data IS available from the PPP loan data. 
  • The restaurant type field in the RR grant data has text strings but no NAICS code. A "Restaurant" text string in this RR data field finds 37 different NAICS codes from the PPP database if the RR grantee also received a PPP loan.  

Originally created and posted on the Oregon Housing Blog.

No comments:

Post a Comment