Who are Albany’s Vendors?

---

The data is straightforward to import and understand at a first glance.  There are nearly fourteen thousand vendor payments, with each payment associated with an account and category, and each payment has a description, a vendor and an amount. 

In 2017 these vendor payments represented $51.092 million of spend.  The City’s 2017 approved budget contained approximately $177 million of spend; vendor payments are 29% of that spend.   

We had certain expectations going in as we analyzed vendor payments.  We don’t want to get into an individual discussion of the merits of any single vendor and any single payment, rather we want to ask a few basic questions:

1. Are the vendor payments understandable?  As a citizen could we go into the system and understand what the payments are for?
2. Can we get an understanding on the categories or groups of spending that happens?
3. Can we identify which are the biggest vendors, by dollar amount?
4. Are there questionable or concerning practices that make analyzing vendor payments difficult?
5. Can we slice and dice the data enough that we can use the 2017 data for comparison when we analyze the 2024 data in the future?

All of this work was fairly straightforward Microsoft Excel work.  We imported the data and then created a number of pivot tables.  

We first looked at the spending categorization.  The image above shows the top spending categories.

There are a number of notable things.  Firstly, the 2017 accounting system and its categorization mixes spend types or uses (“Sewer”, “Motor Vehicles”, etc) with at least one category that is defined by the vendor relationship - “Contracted Services.”  Contracted Services is the largest category of spending; we will dig into this below to see if we can understand what this spending consists of.  

There are 136 unique spending categories from those listed above to the smallest categories of spending - "Last Run 5K"=$526; "Winter Activities"=$300; "Forms"=$127.

We wanted a high level view of where the money goes and creating a graph with 136 categories wouldn’t be very meaningful.  We went through the spending category data and attempted to come up with some higher-level meta categories.  As an illustrative example, we put the Paving projects in a high level category called “Sewer, Street, Sidewalks, Water & Buildings” and Playground equipment went into “Parks, Landfills, Recreation & Outdoors”.

Then we can roll up and graph these high level categories to identify where the City spends money on vendors.  Based on our categorization: 

• 30% of the vendor spending supported work on our infrastructure - Sewer, Streets, Sidewalks, Water and Buildings
• 12% of the vendor spending was for utilities - National Grid, Telecom and more
• 3% of the vendor spending was for legal - outside contractors and the cost of judgments

26% of the vendor spending was on “Contracted Services.”  First we looked at the larger ticket vendor spending in Contracted Services.  There were 452 invoices that were greater than $2,000.   

Examining a few random examples: 

• Motorola Solutions  - The City spent over $18k on a radio contract.  Note that the City already has a spending category called “Telephone Communication”
• Leica Geosystems - The City spent $5,000 to repair some technical engineering equipment.  Note that the City already has a spending category called “Laboratory Equipment” which contains similar equipment spending.
• DCK Services LLC - The City spent $2,480 which was one of 10 2017 invoices with DCK Services that collectively were over $32,061 in spending.  A quick investigation suggests that this spending is associated with Albany’s water system

The takeaway is that when Albany spends money and allocates it to the “Contracted Services” spending category it is very challenging to understand how that money rolls up into categories and is connected with City priorities.  

For example, if we unpack the Contracted Services category and reallocate the spending, we will find that other spending categories could go up dramatically.  For example, William Keller & Sons Construction provided $368k of services for the spending category “Skateboard Park” and $251k of services for “Sewer Rehabilitation”.  William Keller & Sons Construction also provided $1.5 million of services under “Contracted Services”.  Did those Contracted Services projects include Sewer or Skateboard Park work or were they for Water, Street, Sidewalk or other repair?   

Importantly we aren’t asking this question because we are questioning William Keller & Sons Contruction’s work; we are questioning our ability for a citizen or lay person to readily understand how the City of Albany spends its money.

Our goal in this analysis was not to be a forensic accountant and comprehensively comment on the City of Albany’s accounting practice.  We’re not interested in doing that nor is that our expertise.  However we can point out where the accounting practice and methods can obfuscate how the City of Albany spends its money.  

We believe that it is a reasonable goal that an interested citizen or lay person should be able to have visibility and an understanding of what medium and large ticket spending is happening.  A combination of an invoice’s spending category, vendor and invoice description.  For example, above is a filter of 16 random invoices greater than $2,000.    Our spending on rock salt, National Grid, and demoing properties on Alexander and Quail Streets makes sense.  

What was the purpose of spending $33k with “Albany County Convention and”, $17k with Rehfuss Law Firm, and $12k with “Rehabilitation Initiatives”?  We should start with a default position that the spending is necessary.  We do want detailed information on the spend to provide insight on the spending’s connection to the City’s priorities and operations, and whether the spend is required in the future.

We could not measure how much of the Vendor file does not provide enough information for a citizen to understand the spend.  Anecdotally we can say that it frequently happens.  Perhaps we would flip this around and say that Albany’s accounting and reporting goal should be that any spending over $1000 should be understandable - who, how much, what is it for.

Each vendor’s location is listed in the source data and we performed a quick study of where our vendor spending goes by city/state.  Below is a table with the top 15 vendor cities, sorted by spend.  40% of our vendor spending stays in Albany.  Another 35% flows to neighboring cities (Troy, Wilton, Selkirk, et al).  

Spending in distant cities is typically explained by a single vendor:

• Newark, NJ is National Grid
• Atlanta, GA is mostly Navistar which makes industrial and commercial vehicles
• Boston, MA is primarily the Global Montello Group Corp which is a fuel company

We don’t know if there is any significant or actionable value in analyzing the location of the vendors at this time.  It may in the future be a starting point for some other explorations such as why or when do we use remote vendors versus local vendors.

With this study of the City of Albany’s 2017 Vendor Spending we have identified a few themes:

1. The vendor spending categorization is helpful but requires improvement to fully draw insight on how the City spends its money.  
2. Specifically, the “Contracted Services” category’s organization does not provide required visibility for roughly 1/4th of the vendor spending
3. The ability to understand the detail of individual medium- to large-ticket items is variable due to missing or incomplete explanations. 

If we understand the vendor spending data we can also use this insight to inform other conversations.  For example, we were curious how much it costs to tear down buildings that are prohibitively expensive to rehabilitate.  We also want to use the Streets spending data which can help us analyze where streets are being repaved and reconstructed and what future rehabilitation costs and pace looks like.

1. We will FOIA the 2023 and 2024 vendor spending data and recreate the same analysis above using newer data
2. If we find that issues with categorization (specifically “Contracted Services”) haven’t been reconciled we will attempt to unpack, understand and do a test allocation to identify spending themes
3. We want to understand our spending versus peers.  For example, what is the rate of our spending on infrastructure rehabilitation relative to other cities of our age and size?

Questions or comments?  Drop us an email at AlbanyDataStories@gmail.com