https://www.americancityandcounty.com/wp-content/themes/acc_child/assets/images/logo/footer-logo.png
  • Home
  • Co-op Solutions
  • Hybrid Work
  • Commentaries
  • News
  • In-Depth
  • Multimedia
    • Back
    • Podcast
    • Latest videos
    • Product Guides
  • Resources & Events
    • Back
    • Resources
    • Webinars
    • White Papers
    • IWCE 2022
    • How to Contribute
    • Municipal Cost Index – Archive
    • Equipment Watch Page
    • American City & County Awards
  • About Us
    • Back
    • About Us
    • Contact Us
    • Advertise
    • Cookie Policy
    • Privacy Statement
    • Terms of Service
American City and County
  • NEWSLETTER
  • Home
  • Co-op Solutions
  • Hybrid Work
  • Commentaries
  • News
  • In-Depth
  • Multimedia
    • Back
    • Podcasts
    • Latest videos
    • Product Guides
  • Resources/Events
    • Back
    • Webinars
    • White Papers/eBooks
    • IWCE Expo
    • Calendar of Events
    • How to Contribute
    • American City & County Awards
    • Municipal Cost Index
    • Equipment Watch Page
  • About Us
    • Back
    • About Us
    • Contact Us
    • Advertise
    • Cookie Policy
    • Privacy Stament
    • Terms of Service
  • newsletter
  • Administration
  • Economy & Finance
  • Procurement
  • Public Safety
  • Public Works & Utilities
  • Smart Cities & Technology
  • Magazine
acc.com

Procurement


Article

Garbage in, garbage out does not have to be your reality

Garbage in, garbage out does not have to be your reality

Procurement teams need good-quality spend analytics. They need to know how much their agency spends, where, on what and with whom.
  • Written by Jonathan White
  • 20th June 2017

Very simply, “spend analytics is easier than you think and can deliver results very quickly” was the core message of my previous article in this series on spend analytics. It was a bit of a rant and a soapbox moment, but in this day and age, there is no reasonable excuse for a procurement team in an organization – whether private, not-for-profit or public in nature, small, medium or large in size – not to know how much it spends, where, on what and with whom.

In my previous article, I alluded to the fourth excuse we often hear an organization give for why they can’t do spend analysis – their data is so bad that no one, anywhere, could fix it. In any system or organization where hundreds, if not thousands of people can buy goods and services, there will inevitable data quality issues that make spend visibility more difficult, but certainly not impossible.

Some of the key data culprits are: errors in the data extraction, duplicate suppliers (a problem which increases as purchasing card spend increases), inconsistent, incorrect or missing classifications and lack of additional information about suppliers (size, diversity and location). Here, I want to provide a process which you or your chosen partner can take when you have decided that you are ready to overcome those data challenges, and you are no longer willing to live with poor quality spend data.

Overcoming the Data Deficit — Collect and Normalize

If you are part of a small organization, you may very well be able to do all of this on spreadsheets with your existing team. If you are a medium-to-large organization, the process becomes more difficult, but the same basic theory still applies.

First, collect the data from each of your transaction systems. At a minimum, this will be accounts payable data for every organization. Layered on top of that may be pCard data, eProcurement data and/or contract data. At a bare minimum, you need to know who was paid, at least one date (invoice, payment or accounting date typically), how much was paid, an invoice number and, ideally, the part of the organization which spent the money (department, business unit etc.)

Second, put the data you have into standardized columns. In other words, you may see “transaction date” in your pCard spend file and “invoice date” in your AP file. One source may say “supplier name” and another may say “vendor name”. The purpose of this step is to take each heading and match them between files. This could be done in Excel or Access, but there are more robust tools used by data scientists to complete this step as the dataset gets larger.

 

Spend Analytics, Jonathan White

Third, check the fitness/accuracy of the data exports. This is critically important because there is always something wrong with the initial data extract. It could be minor and quickly fixable – but it could also be major and skipping this step would mean that all of the rest of the work you do on your data in later steps is pointless because the base data was wrong. This step can be achieved by doing some simple tests on the data, like assessing whether or not the total spend is what you expect it to be. Look at the data by supplier – are your top suppliers those you expect to see? Sort by department by total spend and again, do they look right? Additionally, you should be checking for duplicated transactions in the file, missing supplier names or addresses, blank department descriptions and out of scope spend to name a few. Some problems can be fixed in place while others will require that the data be re-extracted.

Overcoming the Data Deficit — Turn Garbage Into Gold

The fourth step is to de-duplicate suppliers. Do you know how many ways there are to spell FedEx or AT&T? Software can help at this stage, but human verification is also important to ensure that suppliers are not rolled together incorrectly. This step is important so that you don’t spend more time than necessary in the later steps.

The fifth step is to classify your suppliers or lines to a common taxonomy. Why a common taxonomy? Because if everyone uses common taxonomies, it makes it possible to compare and look for collaborative opportunities more easily. When working with a third-party provider, this step is often completed using a software-based classification tool.

The sixth step is to manually validate and review the classifications applied if classification wasn’t done manually in the first place. Software-based classification tools can only take you so far, and there is no magic system that can correctly classify data to the level of quality and consistency that you need. Whether it is your team or a third-party, human eyes on the data make a fundamental difference to the output quality.

The seventh step is optional, and it’s to enrich your data with third-party data sources. This includes data such as the size of the supplier, the diversity ownership status or an environmental friendliness ranking, but could also extend to information about the credit scores or supplier satisfaction scores from external sources.

Visualizing and Analysing your Transformed Data

The eighth step — and it may come as a surprise to many people that it comes this late in the process – is to decide on a business intelligence toolset to use. This might be as simple as Excel or Microsoft PowerBI (which many organizations already have access to through their Microsoft agreements) or to look at one of the many other visualization tools available, which are too numerous to include here. While the choice of analytical tool is important in getting people to use the data to drive change, it ultimately doesn’t matter if you haven’t gone through the data transformation steps above first.

Wrapping it all up

If you got through the steps above, whether on your own or with help, you will have turned your garbage into gold and be ready to accelerate your ability to identify cost savings, improve efficiency and reduce risk on bought-in goods and services at your organization. You will be amazed how quickly opportunities to do all of those things jump out once you have the visibility in place with data you trust.

Jonathan White, Director, Business Development (Americas) Applied Analytics, Spikes Cavell, part of CSC; 1775 Tysons Blvd., Suite 900 Tysons, VA 22102; (571) 313-5257 ext. 102; [email protected] Go here or here for information.

_____________

To get connected and stay up-to-date with similar content from American City & County:
Like us on Facebook

Follow us on Twitter
Watch us on Youtube

Tags: Administration Economy Procurement Smart Cities & Technology Article

Most Recent


  • crisis
    Navigating crises with confidence: Five ways strategic plans support crisis response
    Some crises are short-lived, barging through our lives and routines, and before we can get a sense of what’s happened, we’re left dealing with the aftermath. But many crises build slowly, with many early warning signs, and once they’vehit their breaking point, panic and uncertainty overwhelm the ability of leaders to think clearly and mitigate […]
  • cities
    Gallery: Annual index ranks America's top performing cities; most are in the West
    Starting with the pandemic and the economic uncertainty that came along with it, it’s been a difficult few years for cities across the United States. As a testament to the resiliency and commitment of public administrators, American cities continue to thrive—with many of the fastest growing communities located in the West, according to the Milken […]
  • 2022 Crown Communities Award winner: Miami-Dade County Clerk of Courts' jury selection system
    The Miami-Dade County Clerk of Courts is revolutionizing the jury selection process.  In many jurisdictions, jury duty is perceived as an obligatory nuisance. On their appointed day, potential jurors arrive early and stay late. They read books or watch television to pass the time as judges and attorneys make in-person selections from the pool of […]
  • Orlando, FL
    Gallery: Top 10 American cities for seasonal and summer jobs
    With Memorial Day weekend upcoming, Americans everywhere are gearing up for longer days, warmer nights and seasonal summer jobs to begin. A new ranking from WalletHub lists the top cities for summer work based on criteria like employment outlook, availability of summer jobs, median income for part-time workers, affordability, and downtown-friendlinesss. “For some people, especially […]

Leave a comment Cancel reply

-or-

Log in with your American City and County account

Alternatively, post a comment by completing the form below:

Your email address will not be published. Required fields are marked *

Related Content

  • Deltek releases 12th annual Clarity Government Contracting Report
  • Amazon Business releases first B2B ecommerce report, highlighting e-procurement
  • Public procurement can be transformative for stakeholders in a community
  • The pandemic has led to big adjustments in procurement staffing in governments

White papers


5 reasons why Plan Examiners need Objective Trapeze

30th May 2023

7 Permitting & Licensing Fails Slowing Community Growth

24th May 2023

The Secret Ingredient to Local Government Employee Retention

23rd May 2023
view all

Webinars


How to Centralize and Build a Grants Management Process at your Organization

24th May 2023

Making Permitting Easier: What We’ve Learned Helping America’s Largest Cities Improve Their Permitting Process

16th May 2023

Digital Property Tax Collection: Tales from the Trenches of Modernization

16th May 2023
view all

PODCAST


Young Leaders Episode 4 – Cyril Jefferson – City Councilman, High Point, North Carolina

13th October 2020

Young Leaders Episode 3 – Shannon Hardin – City Council President, Columbus, Ohio

27th July 2020

Young Leaders Episode 2 – Christian Williams – Development Services Planner, Goodyear, Ariz.

1st July 2020
view all

GALLERIES


Gallery: Annual index ranks America’s top performing cities; most are in the West

30th May 2023

Gallery: Top 10 American cities for seasonal and summer jobs

25th May 2023

Gallery: 10 of America’s most affordable cities

9th May 2023
view all

Twitter


AmerCityCounty

5 reasons why Plan Examiners need Objective Trapeze dlvr.it/Sptl5z

30th May 2023
AmerCityCounty

Navigating crises with confidence: Five ways strategic plans support crisis response dlvr.it/SptVKN

30th May 2023
AmerCityCounty

Gallery: Annual index ranks America’s top performing cities; most are in the West dlvr.it/SpszdK

30th May 2023
AmerCityCounty

2022 Crown Communities Award winner: Miami-Dade County Clerk of Courts’ jury selection system dlvr.it/SphCBk

26th May 2023
AmerCityCounty

Gallery: Top 10 American cities for seasonal and summer jobs dlvr.it/SpdFWy

25th May 2023
AmerCityCounty

How to leverage digital tools to drive innovation in government dlvr.it/Spcktb

25th May 2023
AmerCityCounty

With many cities facing a fiscal cliff as ARPA funding ends, debt ceiling debate continues on Capitol Hill dlvr.it/SpZLph

24th May 2023
AmerCityCounty

7 Permitting & Licensing Fails Slowing Community Growth dlvr.it/SpYqBS

24th May 2023

Newsletters

Sign up for American City & County’s newsletters to receive regular news and information updates about local governments.

Resale Insights Dashboard

The Resale Insights Dashboard provides model-level data for the entire used equipment market to help you save time and money.

Municipal Cost Index

Updated monthly since 1978, our exclusive Municipal Cost Index shows the effects of inflation on the cost of providing municipal services

Media Kit and Advertising

Want to reach our digital audience? Learn more here.

DISCOVER MORE FROM INFORMA TECH

  • IWCE’s Urgent Communications
  • IWCE Expo

WORKING WITH US

  • About Us
  • Contact Us

FOLLOW American City and County ON SOCIAL

  • Privacy
  • CCPA: “Do Not Sell My Data”
  • Cookie Policy
  • Terms
Copyright © 2023 Informa PLC. Informa PLC is registered in England and Wales with company number 8860726 whose registered and Head office is 5 Howick Place, London, SW1P 1WG.