Looking at data, big picture and little picture

I posted my thoughts on the future of expenditure analysis, moving from historical to real-time analysis and intervention into a transaction. But I also said I respect the typical data mining approach. Here’s an example of data mining that can really help to bring understanding to your clients, executives running other business units. Hat tip to David Bush, at E-sourcing forum, yesterday he led me to an article on micro and macro expenditure analysis from November on his site. Well worth reading for anyone thinking about how to understand buying patterns.

The article reminded me of a clever chart a team looking at PC spend put together to help client executives understand the strategy they were proposing. They had gathered data from about 50 departments, which let us understand a high level view of purchasing patterns.

(All data presented has been changed and “anonymized”)

I like to start to understand expenditure using a “macro” approach I call “5X4” reporting. 5 axis of data reported against each other:

1. Category of Spend
2. Supplier
3. General Ledger (GL) Code
4. Business Unit
5. Geographic Location

Here are two generic examples of 5X4 reports, the first has Category as the pivot and the second shows Supplier as the pivot. The others follow the same pattern, and you can sort the charts alphabetically, by $ amount, etc.

5X4 Category Spend

5X4 Supplier Spend

The quality of data will vary be organization and axis. I often find Geographic Location and Business Unit data are pretty sketchy.

When we looked at 5X4 data for PC spend we made some observations:

1. It was a big category of spend, tens of millions for PC’s alone
2. Spend was well consolidated to a handful of major suppliers.
3. There were noticeable differences in the macro benchmarks; Spend per employee, Square footage, and % of SG&A costs varied by department in ways that we couldn’t understand based on the type of work performed by those departments
4. There was the usual mis-coding of GL spend.

Nothing that jumped out at us to say we should make this a high priority category for sourcing.

Next we looked at the data (hundreds of thousands of lines of transaction data) using a standard stock chart from excel, “Volume:High:Low:Close”.
By department expenditure data these adjustments were made to the input definitions:

Volume = Total dollars spent
High = the highest price paid for a single PC
Low = the lowest price for a single PC
Close = the mean price for all PC’s purchased.

Here are a few of the questions we expected the chart would lead us to investigate:

1. Are large variances in low to high price due to a wide range in quality and specifications being purchased or a wide range in price being paid for the same quality and specification?
2. Does the departmental pricing look similar for departments with similar work and labour forces?
3. Is the volume of purchasing for similar departments also similar?
4. Would we see variances in the lowest and highest prices across departments? A large variance in the high price would be easier to understand than a large variance in the low price.

And some other observations we expected to make:

1. The lower the mean price was to the low price, the more units were being purchased at the low end of the price scale
2. A mean price in the middle of the price range indicated just as many high price units being purchased as low price units

Again, with the data changed, here what the chart looks like: The chart led us to look for answers in the full set of transaction data:

1. We found the wide price variance at “A” was equally due to a same unit price variances and to a wide variety of specifications being purchased. Given the size of the spend and what we also knew about the department, we were confident that PC buying was basically a “Free-for-all”.
2. Departments “B” and “C” were virtual clones. Same city, same type of work, same type of labour force. We expected similar purchasing behaviour and this data confirmed it. By cross-checking the macro data (5X4)’s though we observed that price per employee was more varied than we expected. It turned out that “B” generally kept PC in service for 4 years, and “C” for five years.
3. “D”, “E”, and “F” were a surprise. Again, pretty much clone departments with pretty different purchasing behaviours.
4. “I” was a revelation to the executive who ran the department, he had been in charge for over 10 years, ran an “analytical” department and confidently told us he had standardized PC purchases and had tight internal controls “best in the business”. One look at his chart and he immediately understood the data had proved otherwise. He was a good enough executive that he accepted and appreciated the data.

This analysis and the discussions that ensued with the business units were then directed into crafting a market strategy for purchasing PCs across the organization.

Cheers,

David Rotor