Purchase Cards – Visualize

Tableau Public Visualizations

This case study uses Tableau Public for visualizations. Tableau Public can be used to quickly create data visualizations and then pull them together in a dashboard. Another tool that is commonly included in organizations’ current collection of software is MS PowerBI which may be a part of your current Microsoft Office 365 license.

Initial Visualizations: Quick Start – Purchase Card Transactions

You can interact with this Tableau Public Dashboard. It selects and shows two example agencies, the Metropolitan Police Department and the Fire & Emergency Medical Services.  The Agency Filter is on the right and shows checkboxes next to the two selected Agencies. 

You can select other agencies of interest.  The dashboard has been designed to show one or more agencies.  If you are interested in all agencies, the dashboard is not going to help you because it is difficult to visualize all 89 agencies at once.  However, if you go to an individual visualization worksheet you can choose “(All)” in the Agency filter and compare the information.

Question: What is that giant spike?!

If you select the second tab in the visualization, “PCards by Month” and select “(All)” in the “Agency” filter on the right, you will see all Purchase Cards totals per month graphed across 2015 to 2022. 

If you mouse over the giant spike in 2016, you can see the description of this purchase card activity.  It seems that this anomalous purchase card spending occurred in February 2016 by the Office of Contracting & Procurement in the amount of $13,412,015.  Now, this is quite out of the ordinary as you can tell from the graph.  Would you see this if you were simply looking at a spreadsheet, or worse, one credit card statement?  Unlikely.  But when you accumulate and look at a month-to-month comparison by agency, it is clear.  The highest peak of spending for all agencies from 2015 to 2022 is always under $6 million during a month.  February 2016’s total was over $14 million.

Perhaps you already know what this spike represents?  Let me know in the comments if you guessed it before the continued investigation below.

You can delve deeper into this information.  Next, let’s find out two pieces of information about February 2016, the MCCs and Vendors. 

MCC Investigation

First, what were the Merchant Category Codes (MCCs) for the data spike?  MCCs represent the primary code assigned to a vendor’s business.  If you go to the third tab, “Total by Agency and MCC – Scatter Plot,” you can limit by both Agency and Month.  Under “Agency” uncheck “(All).”  Now, scroll down or click on the magnifying glass in the “Agency” filter and find Office of Contracting & Procurement.  Using the magnifying glass to find “procurement” is a great way to find this.  Under the “Month, Year of Transaction Date” filter uncheck “(All).”  Now, select February 2016.  This produces a “Total by Agency and MCC – Scatter Plot” with useful information. 

The horizontal axis shows the Transaction Amount total.  The vertical axis shows the purchase card transaction count.  As we are looking at a spike in dollars, we will pay attention to values that are further to the right, rather than higher. 

We can see the highest dollars are the dot “1” showing $5,634,116 spent on the MCC of “GENL CONTRACTORS-RESIDENTIAL, AND COMMERCIAL.”  Dot “2” is not far behind at $5,428,495 for an MCC of “RENTALS-TOOLS, EQUIPMENT AND FURNITURE.”  But who is being paid by purchase card for these particular MCCs: General Contracting and Tool Rental & Equipment? 

Vendor Investigation

We can look at the vendors by going to the next tab, “Total by Agency and Vendor – Scatter Plot.”  These visualizations have been configured to apply the previous filters of “Office of Contracting & Procurement” and “February 2016.“

The top vendor is Total Civil Construction.  The second one is CW Strittmatter, another construction company.  The other two vendors that have over a million dollars are JJ Prime Services (construction) and Hurb Landscaping.  So why would the city pay for Tools and Equipment rental from construction and landscape companies in February 2016 in Washington, DC?  Have you figured it out yet?

Of course, if you had wanted, you could have googled “Washington DC February 2016” at the beginning of this process and seen the top results were weather-related, specifically “snowmageddon.”  While the big snowstorm actually happened on January 23, the city was paying for the snow removal services continuing into February.  In fact, if you go to the Hurb Landscaping website, one of their featured services happens to be Snow Removal.  They have pictures of the snowmageddon removal with the Washington monument in the background.

The giant spike in the data was an emergency situation where there wasn’t time to negotiate purchase orders and contracts with snow removal companies.  The City Office of Contracting & Procurement used their purchase cards to spend millions to deal with the emergency.  Perhaps in the future, The City of Washington, DC can negotiate a better rate with its vendors ahead of time to plan for emergency situations. 

Question: Purchase Card Payments vs Negotiated Contracts?

Can we identify any other instances where better rates might be negotiated for purchases?  That is, when someone is making purchases, using a credit card is great and can expedite a transaction. But if you can plan your purchases and negotiate with a vendor to provide what you need by way of a contract and associated purchase orders, you can often negotiate discounts. 

In order to determine which Merchant Categories might benefit from the negotiation of a contract, we can start on the visualization tab called “Vendor Outliers within MCC.” Be sure that “(All)” is selected under “Agency.” This chart is filtered by the top ten MCCs.

This graph shows the top 10 highest MCC categories with exceptional spending for outlier vendors.  If all Agencies are selected, you can see three of the top ten MCC Codes are related to different versions of “Stationery and Office Supplies.”  We can see the top vendors for these categories are The Hamilton Group, Standard Office Supply, and Capital Services and Supplies.

• http://www.thehamiltongroupllc.net/
• https://mdmstandardofficesolutions.com/
• https://www.capitalservices.net/content/About-Us

A quick visit to these vendors’ websites reveals that they are minority or female-owned or small businesses.  They are all located in Washington, DC.  The City of DC encourages business development within its borders with underrepresented businesses.  Excellent!  This is in line with The City’s purchasing objectives.  However, as a small women-owned business myself, I know that there are significant fees associated with purchases on credit cards.  It could benefit the City AND the vendors to negotiate long-term, steady contracts for the purchases of office supplies. 

Question: Where can purchasing transparency be improved?

Vague MCCs

A few of the MCCs are a bit vague. This makes sense given that a vendor’s complete identity is trying to be summarized in a single Merchant Category Code. However, it can lead to a lack of transparency. For this example, I’m going to focus on “Other Services – Not Elsewhere Classified” and “Professional Services – Not Elsewhere Classified.” Both of these can indicate a lack of diligence in identifying the purchase.  There are approximately 500 MCC codes to choose from.  Please try not to pick “Not Elsewhere Classified.” 

PayPal

I love PayPal and have been using it since 2000. However, they are always struggling to keep scammers off their platform. Additionally, a vendor name may show up simply as “PayPal” instead of their actual vendor name. When this happens, the purchase card transaction may by default fall under “Not Elsewhere Classified” categories.

Report Filtered on MCC and Vendor

Let’s see which agencies are using their purchase cards with PayPal.  To do this, go back to “PCards By Month.” 

1. For “Agency” select “(All).”
2. Under “Merchant Cat Code Desc,” deselect “(All)”. Then perform a search using the tiny magnifying glass and type “not elsewhere classified.” While there are many options, limit the search by checking only “Professional Services – Not Elsewhere Classified.” and “Other Services – Not Elsewhere Classified.” Because of data cleanliness issues, you will check 4 boxes.
3. Unde “Vendor Name,” deselect “(All).” Then use the tiny magnifying glass to type “PayPal.”  You will notice some variations on the PayPal vendor name for the vendors that have their store name included as part of the “PayPal” transaction. For this example, you can simply choose the first “PayPal” on its own.

For the variations on the PayPal vendor name, there are some interesting vendors such as Adult Tutus, 1800LOVECO, 1LUVLYLADY, or AbsoluteDat. They may be valid vendors that the City of DC uses. You would likely need some additional information such as receipts for validating these transactions if they appeared material. Typically, though, it is better to investigate long-term, high-dollar trends rather than getting lost in the tiny details of the data.

One of the highest spending agencies for PayPal is the DC Public Schools, which makes sense as they are one of the highest spending agencies overall.  They are shown at the top of the graph in blue.

The more interesting spending is actually the one that threads its way through the graph in green.  It is identified as “Unknown New Agency.” Surprise! It’s a third generic identifier that reduces spending transparency.  Perhaps this agency is a placeholder as new agencies are created in Washington, DC. 

For better control of spending, the City might consider fast-tracking the set-up of new agencies. This would cause the agency to be able to spend under its own name, thereby better showing the spending that is occurring.  Or, alternatively, they could leave the purchasing for these agencies up to the Office of Contract and Procurement until they are set up. Or perhaps restrict these agencies to a few approved standard vendors and not allow them to make purchases from PayPal.

Another helpful control around the “Unknown New Agency” would mandate that the ability to bill to this agency be turned off after the buyer has their New Agency credentials. This would prevent new transactions from inadvertently or accidentally falling under this agency. It appears that this “Unknown New Agency” purchase card spending may contain values from other agencies when you look at all of its spending on purchase cards for all vendors.

Most of the significant spikes in the “Unknown New Agency” data fall in the month of September. One possible explanation may be because the budgets for newly established agencies do not start until the beginning of the fiscal year, October. End-of-year spending may need somewhere to be categorized in the meantime. Another possible explanation may be that some spending for the highest spending agency, the District of Columbia Public Schools, was necessary during September when the school year started but was not available as part of the end of fiscal year budget. Instead, perhaps the spending was categorized as “Unknown New Agency.”
Questions about the spending in the “Unknown New Agency” can only be answered by reviewing purchase card spending policy documents and receipts. Consideration about how this agency’s spending is tracked may need some additional oversight to provide better transparency of spending.

Data Visualizations – More Information Needed

These are just a few examples you can look at with seven fields of purchase card data.  Imagine if you worked for The City of Washington, DC, and could find out which employees were making and approving these purchases. 

Visualization is not the final step, but simply a QuickStart.

Once you have Initial Visualizations >> Plan >>