#8 USAspending.gov Analytics-Which Contractors Performed the Best in Capturing Information Technology Spending Increases from GFY16-GFY19?

Gaining an Analytics Edge Using Federal Spending Open Data Series

Context

Purpose: Demonstrate to executives how easy it is to access and analyze free, open-data sources and motivate them to expect more insight and context from their team when making crucial go/no-go investment decisions on bids and new offerings.

Scope: US Federal Contracting Market.

TL;DR — Analyze GB’s of US Federal spending data with a few lines of Python code. Uncover competitive insights from information and analytics.

Introduction

This blog uses Federal contractor spending from Government Fiscal Year (GFY) 2010 through GFY2019 and covers these topics:

(1) Relative and absolute changes in expenditures by Federal “Product and Services Code” categories (PSC_Cat).

(2) Analysis of contractor spending on work designated by Federal procurement officers as “IT and Telecommunications” using the criteria “predominant product or service that is being purchased.” Exploring the expansion and contraction of contractor spending at the Department, Agency, and contractor levels.

The jupyter notebook and Python code that accompanies this analysis is located at this GitHub link within this repository.

Background

“The Product and Service Codes (PSC) Manual provides codes to describe products, services, and research and development (R&D) purchased by the federal government. These codes indicate “WHAT” was bought for each contract action reported in the Federal Procurement Data System (FPDS). “

“These product/service codes are used to record the products and services being purchased by the Federal Government. In many cases, a given contract/task order/purchase order will include more than one product and/or service. In such cases, the “Product or Service Code” data element code should be selected based on the predominant product or service that is being purchased.

For example, a contract for $1000 of lumber and $500 of pipe would be coded under 5510, “Lumber & Related Wood Materials.”

This coding of obligations with the “Product and Service Codes” (“PSC_Cat”) gives an analyst a good starting point to develop market context using the USAspending.gov obligation transaction data. Every obligation transaction is coded with one of the designators from the scheme described here:

https://www.acquisition.gov/sites/default/files/manual/PSC_Data_March_2020_Edition.xls

Based on guidance from this link.

Here is a sample of the major category codes:

Image for post
Image for post

Here is a detailed breakdown of the detailed codes under the PSC_Cat “D” category for “IT and Telecommunications”:

Image for post
Image for post

Caveats: When analyzing obligations for a specific product or service, this coding structure creates a broad picture of the spending. To get a detailed and more fine-grained picture, you may need to study the original RFP and Award documents posted on beta.sam.gov (former FBO.gov). Also, note that IT-related spending may be coded in the ‘D’ codes for integrated services and products, while independent hardware and software purchases can usually be found in PSC_Cat ‘70’. This document shows an even more complex hierarchy of the relationship of the detailed PSC_Categories and topics such as “information technology,” “medical”, “human capital”, etc. For Information Technology (IT), it is not unusual to find significant content in programs coded in the ‘R’ family of services — “SUPPORT SVCS (PROF, ADMIN, MGMT).” Depending on the fidelity needed and your analysis’s purpose, you may need to go beyond the depth of the examples I show below.

Big Picture Context

For instance, I usually calculate GFY annual totals and then reference those later in the form of checksums to confirm that I have not lost something in translation. When I first started working with the USAspending data and the Python pandas tools, I was not familiar with how pandas computes sums when it encounters NaN (blank values). I discovered that it stops counting when it encounters a NaN instead of treating them as 0.0. You will see in my data preparation the uses of the pandas fillna(0.0) and fillna(“UNSPECIFIED”) to address this. It is not unusual in formulating pandas selections, slices, and other translations to mistakenly create the wrong calculation, so liberal use of Python assert statements to check values can be valuable as you refine your code.

For this analysis, I start with a quick calculation and graph plotting the total obligations for each GFY from 2010–2019:

Image for post
Image for post
Image for post
Image for post
Note: GFY2020 is YTD through September 2020 with 3-month lag for DoD obligations

Next, I load the reference “Product and Service Code” Excel sheet and use that for subsequent calculations and reports.

Image for post
Image for post

A fast and straightforward (the source data has ~46 million records) pandas groupby([‘action_date_fiscal_year’, ‘PSC_Cat’]) and summation of federal obligations reveal the distribution of spending on the major categories of work:

Image for post
Image for post
Image for post
Image for post

A translation to a pivot_table structure and sorting with a plot shows the relative spending by major PSC_Category and the fraction of total spending for FY19, FY15 (market bottom), and FY10. One can see that the product PSC_Category “1” (10–19) that includes everything from Aircraft and Ships to Ammunition and Weapons has grown from ~14% of contractor obligations in GFY10 to ~18% in GFY19 while R&D has dropped from ~10.5% to ~8%.

The next plot shows the top six (6) categories over time with a thick black line that shows the relationship to total contractor spending bottomed in GFY2015 and recovered in GFY2018 to GFY2010 levels (this graph does not consider the inflationary differences between GFY10 and GFY19):

Image for post
Image for post
Image for post
Image for post

Since I am personally most familiar with the work in PSC_Cat codes ‘D’ (IT) and ‘R’ (Contract Services), I narrow the analysis to those categories and plot that relationship:

Image for post
Image for post

We now have context as we dive further into the obligation transactions.

Exploring PSC_Cat “D” — IT and Telecommunications Spending

Image for post
Image for post

Next, I use the pandas pivot_table (similar to Excel or Google Sheets Pivot Table functionality) to reorganize the view of the data.

Image for post
Image for post

I then calculate the three (3) year Compound-Annual-Growth-Rate (CAGR) (GFY2019 vs. GFY2016). Here are the top ten (10) largest Departments:

Image for post
Image for post

The GFY16 to GFY19 overall Federal CAGR was ~7.5%, while the growth for PSC_Cat “D” was:

  • overall market: 8.9%
  • DOD: 10.3%
  • Civilian Departments: 7.8%

The Departments with PSC_Cat D spending greater than $50 million/year in GFY19 and higher than the 8.9% CAGR:

Image for post
Image for post

The lowest growth Departments:

Image for post
Image for post

We can take this analysis to a lower level and inspect where the dollar volume of growth was by Agency inside Departments:

Image for post
Image for post

Outside of DoD, the biggest dollar growth from GFY16 to GFY19 was the VA at #1, the US Census Bureau preparing for the 2020 Decennial, HHS/CMS, and Commerce’s NOAA. The VA PSC_Cat D spending grew from ~$2.7B to ~$4.1B => ~47% increase in spending. The increased VA spending is larger than the GFY19 annual budgets for every Department except for DoD, HHS, DHS, DOC (due to US Census Bureau Decennial spending), and Treasury!

Some Agencies inside Departments saw significant reductions in PSC_Cat D spending too:

Image for post
Image for post

Analysis of Contractors that Benefited from the Increased Spending

Image for post
Image for post

Note that Perspecta emerged from M&A, and Cerner jumped into this list very recently. Understanding the baseline for the Perspecta requires more analysis. Cerner experienced impressive GFY19/GFY18 growth of ~$723M/~$490M => ~48% YoY.

How much of the growth was due to M&A, and how much from ‘organic’ expansion of the legacy business? Given the size of these businesses and the amount of M&A over the last several years, answering that requires a deeper dive into the obligation transactions that I won’t do here. It is possible to assess that by looking at the additional fields in the USAspending.gov obligation records.

Which contractors with at least $50M/year (GFY19) in obligations in PSC_Cat D work had the highest PSC_Cat D CAGR?

Image for post
Image for post

Which contractors increased their annual prime obligations for PSC_Cat D work the most?

Image for post
Image for post

Which contractors with at least $350M/year (GFY19) in PSC_Cat D work obligations had PSC_Cat D CAGR’s that exceeded the 8.9% CAGR benchmark (sorted from high to low)?

Image for post
Image for post

Exporting Data for Data Sharing with Colleagues

The Excel Pivot Table functionality is powerful, easy to use, and fast. It is often an excellent choice for quick analysis and sharing with others. You have lots of options once you get the data into thousands of rows and tens of columns from tens of millions of rows and ~300 columns.

You can import the data into other popular business intelligence (BI) tools such as Tableau, Microsoft Power BI, Microstrategy, Google Data Studio, etc.

There are other tools emerging that will host interactive analytics on a cloud service using Python open-source tools that are options also. This enables you to both share the data but also build your analytics tools using the power of the Python open-source ecosystem. Some of these are:

Closing

Previous post: #7 USAspending.gov Analytics — Time Series Forecasting with Facebook’s Open Sou

Previous Blog Post in this series: #7 USAspending.gov Analytics — Time Series Forecasting with Facebook’s Open Source Prophet Package

Coming Attractions

The code listed in the examples above and more can be found here:

https://github.com/leifulstrup/USAspending_Medium_Blog_Analytics_Series

MIT Open Source License Copyright 2020 Leif C Ulstrup

Written by

Strategy, emerging technology, innovation, and management advisor https://www.primehookllc.com/about-us.html, https://www.american.edu/kogod/faculty/ulstrup.cfm

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store