The ABCs of spend analysis: Bring the data together
First, get corporate-management support; then decide just what data you need.
By Maria Varmazis -- Purchasing, 5/3/2007 6:00:00 AM
It's all well and good to try implementing a spend analysis program, but the very first steps—getting support, deciding what data you need, then finding it—can be daunting. The process known as ETL—extract, transform, and load—means pulling every morsel of data together and cleaning it up before funneling it through a spend analysis program.
"There's a little bit of an art to defining what you want to report on and analyze before you begin," says Chris Haydon, vice president of global solutions and services for Amsterdam-based software provider Quadrem. Purchasers can get overwhelmed by the amount of data they must sift through, with little direction on how to make sense of it, but there are several pointers to guide you through the very first steps.
Most spend analysis practitioners mention how crucial it was to their project to get high-level support. Gathering spend data is much easier if there's early, upfront commitment from all the departments you need. The more disparate the ERP systems you're trying to pull together, the more resistance you may encounter from systems administrators, financial staff or even other buyers. Strong backing from the board of directors or the CEO will help alleviate pushback—not to mention that small detail of securing a budget.
Your data-gathering team shouldn't be just procurement—depending on your company's structure, you might want to bring in your IT and finance departments as well to sell your overall goal and get their support.
Before sorting one spreadsheet or pulling information from a database, sit down and plan concretely the data you want, at both the macro and micro level. Companies about to undergo spend analysis often seek a complete and detailed understanding of their spend habits, while others have vendor consolidation more specifically in mind. On the micro level, specifically map what needs to be tracked in the final spend reports—which items, which categories, how much, where, and who?
Some ERP systems might track this information; others might only have bits and pieces. Haydon says the important thing is to have "placeholders" for the fields you want, even if they're empty, and fill them in later. Pool the spend expertise you have on-hand to form the structure that makes the most sense for storing and organizing your data. Everyone needs to have the big picture in mind, says Haydon: "You should start with the end in mind, map that territory and think of how the data needs to mesh together."
Timetable for refreshing
 There’s an art to defining what you want to report on and analyze,” says Chris Haydon. |
When mapping out your strategy, determine how often you need to pull the data from the ERP systems for the most recent data, or "refresh." Some companies prefer quarterly refreshes, while others go monthly or weekly. What may decide your refresh rate is your end goal and how long the refresh process might take.
Phil Mingin, director of materials management for the Memorial Sloan-Kettering Cancer Center in New York, says before the hospital revamped its spend management system, it took months just to get spend data from the previous year. "We had an ERP system where by the time we loaded everything and made sure it didn't include extra information, everything was a few months old," he says. Mingin's colleague, Assistant Director of Corporate Procurement Bill Abeltin, adds that the hospital's goal is to get to up-to-date monthly refreshes via SciQuest: "We want to be able to look at last year's data at the beginning of January to make strategic decisions, especially in contract negotiations."
Another consideration for faster refreshes for Abeltin and Mingin is the just-in-time environment under which the hospital operates. Many items are ordered just one day before the lab researchers need them. Buyers need timely data refreshes that reflect their spend habits.
 Related articles: The ABCs of spend analysis The ABCs of spend analysis: Acquire the data skills The ABCs of spend analysis: Change the way you source |
One aspect of spend-data categorization worth scrutiny is the classification code set. While many companies use UNSPSC codes as a standard, depending on your industry, it might not be the best fit. Greg Potapenko, strategic sourcing analyst at Toronto-based mining company Barrick Gold, which uses software from Ketera, says UNSPSC wasn't the best fit for categorizing mining-related materials. Potapenko goes back to GIGO—garbage in, garbage out—and says the only way to get meaningful classification was to devise a new system. Instead of going it alone, Barrick Gold teamed up with several other mining companies and established their own codes for spend data.
Get finance on the team
From this point, the data collection process begins. A best practice that Arun Prakash, senior director of products at Santa Clara, Calif.-based Ketera, recommends is to team up with finance and go through the accounts payable or invoice systems data. This spend information is usually the easiest to obtain, and by grabbing requisition, purchase order, invoice, or Pcard data, you gain fast results to demonstrate the worth of the project.
 “Getting information organized and quickly is really the key,” say Bill Abelton and Phil Mingin. |
Having finance representatives on your team provides a big advantage. Finance can verify if the easy-target spend data is correct or pinpoint any other easy spend targets you might have missed. Starting small with the finance data serves as a base for the rest of the data-extraction process.
It also can uncover problems with the data structure. Edgar Heitmann, senior project manager at Oslo, Norway-based Orkla ASA, which uses software from Zycus, says that on the first try to collect spend (across 40 different ERP systems and 13 different languages), there were some snags. "Some data structures didn't fit at local companies, so we had to make revisions, which was a huge effort because all the other companies' data extractions also had to be modified," he says.
One tactic Heitmann used to keep the data-gathering process organized, despite any minor issues, was to effectively outline and communicate the goals of the program and the requirements for the process itself. He put together a communications package, which he shared with procurement, finance and IT representatives. The first 50% of the companies within Orkla had three to four weeks to deliver the necessary extracted data, and Heitmann says they managed to do it within just two.
Orkla also tested the data with a tool it devised in Microsoft Access and Excel to make sure the spend data was correct and categorized as needed. "It saved a lot of time and we were able to automate file analysis. And after getting files from the companies we could give feedback and report back in one hour," he says.
Now Playing:
