2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Data Driven Strategies in Procurement

Tom Roberts Jason StantonDirector Systemwide Program Analyst

Contract Services & ProcurementOffice of the Chancellor

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Data to Management Information

What is dataData can be any level of information detail

from our systems (or elsewhere)What is management information?

Management information is gleaned from data and used to make decisions. Business Intelligence (BI)

There is a place in business for both

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Mining DataChoices in data to use

Raw dataCleansed dataEnriched data

Choices of accessing dataPeopleSoft financial systemDownloading into applications then manipulating it

Data Warehouse Spreadsheets or databases Oracle (or other) tools Hire a third party

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

CSU Hired a Third PartySpikes Cavell

Spend analytics company specializing in public sector

We now have:Three years of data (refreshed every four

months)All campuses / business unitsOver 100 user accounts systemwideCan see our data and data from other

institutions

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

What Does Spikes Do?

ENRICH

4

CLASSIFY

3

DATA

COLLECT

1

CLEANSE

2

REDACT

5

COLLATE

6

Accurate extraction of raw financial data from PeopleSoft and US Bank.

Data standardization, removal of duplicates and identification of errors.

Classification of vendor and purchase card data into NAICS, NIGP, and vCode categories

Appending of supplemental attributes from Spikes Cavell and licensed 3rd party reference datasets to each of the core trade vendor records.

Identification and redaction of payments made to individuals or where security might be compromised.

Aggregation of all of the spend and related data into a single, unified and consistent master database.

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

CSU_VENDOR_PYMT 22745Business Unit = COCSUPayment Dt From = 2012-07-01Payment Dt To = 2013-06-30Unit Remit Vndr Tax ID Vendor Classification Name Address 1 City St Postal Payment Ref Check AmtCOCSU 0000000021 ****427007 Outside Party FEDERAL EXPRESS PO BOX 7221 PASADENA CA 91109-7321 347270 24.210COCSU 0000000021 ****427007 Outside Party FEDERAL EXPRESS PO BOX 7221 PASADENA CA 91109-7321 347270 24.210COCSU 0000000021 ****427007 Outside Party FEDERAL EXPRESS PO BOX 7221 PASADENA CA 91109-7321 347315 8.070COCSU 0000000026 ****745535 Outside Party AT&T DATACOMM PO BOX 8104 AURORA IL 60507-8104 342756 79500.000COCSU 0000000026 ****745535 Outside Party AT&T DATACOMM PO BOX 8104 AURORA IL 60507-8104 343061 195986.370COCSU 0000000026 ****745535 Outside Party AT&T DATACOMM PO BOX 8104 AURORA IL 60507-8104 343061 195986.370COCSU 0000000026 ****745535 Outside Party AT&T DATACOMM PO BOX 8104 AURORA IL 60507-8104 343061 195986.370COCSU 0000000026 ****745535 Outside Party AT&T DATACOMM PO BOX 8104 AURORA IL 60507-8104 343061 195986.370COCSU 0000000026 ****745535 Outside Party AT&T DATACOMM PO BOX 8104 AURORA IL 60507-8104 343061 195986.370

Payment Dt Creation Dt PymntAcctg Date Payment Method Pay Status Voucher Vchr Ln Descr Vchr Ln Vchr Dist ln No Vchr GL Unit Vchr Account6/18/2013 6/18/2013 6/18/2013 System Check Paid 00158989 W/E 05/26/13 1 1 COCSU 6608036/18/2013 6/18/2013 6/18/2013 System Check Paid 00158991 W/E 06/02/13 1 1 COCSU 6608036/21/2013 6/21/2013 6/21/2013 System Check Paid 00158992 W/E 06/09/13 1 1 COCSU 6608037/23/2012 7/23/2012 7/23/2012 System Check Paid 00150742 FY 11/12 CNI campus implementa1 1 COCSU 6168168/15/2012 8/15/2012 8/15/2012 System Check Paid 00151334 Equipment, including staging a1 1 COCSU 6160028/15/2012 8/15/2012 8/15/2012 System Check Paid 00151335 Equipment, including staging a1 1 COCSU 6160028/15/2012 8/15/2012 8/15/2012 System Check Paid 00151336 Equipment, including staging a1 1 COCSU 6160028/15/2012 8/15/2012 8/15/2012 System Check Paid 00151337 FY 11/12 - Equipment for 10 Gi1 1 COCSU 6160028/15/2012 8/15/2012 8/15/2012 System Check Paid 00151338 Equipment, staging and install1 1 COCSU 616002

Descr FIRMS Object CDFirms Obj Cd DescrVchr Fund Vchr Dept PO No. PO Ln PO Category CodeVchr Ln Dist AmtEXPRESS MAIL CHARGES660001 Postage and Freight 48501 1057 0 16.140EXPRESS MAIL CHARGES660001 Postage and Freight 48501 1057 0 8.070EXPRESS MAIL CHARGES660001 Postage and Freight 48501 1057 0 8.070IT COMMS PROJ ECT MGMT COST616001 I/T Communications 48503 1101 0000003182 1 00067 79500.000IT HARDWARE OVER $5,000616002 I/T Hardware 48501 1101 0000003358 1 00010 6000.000IT HARDWARE OVER $5,000616002 I/T Hardware 48501 1101 0000003358 1 00010 412.500IT HARDWARE OVER $5,000616002 I/T Hardware 48501 1101 0000003358 1 00010 5738.000IT HARDWARE OVER $5,000616002 I/T Hardware 48502 1101 0000003463 1 00010 699.920IT HARDWARE OVER $5,000616002 I/T Hardware 48501 1101 0000003386 1 00010 8219.370

A/P Voucher Level Transaction TableRaw Data

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Sample of a few lines from the PeopleSoft report

PeopleSoft Open PO Report

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Commodity Codes ProjectRecommend an approach to change the use

of the current NIGP commodity codes from three to five digits to achieve a more granular reporting of expenditures with in the CSU.

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

CSU Current Use of CodesStarted out 12 years ago with 270 NIGP

codesNow over 1,000 codes across all campusesCreated by campuses for their individual

needs

9

8820096185DEFAULT962561502742500BT

84066C42554N95403LAMPSTRVL

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e 10

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Commodity Codes ProjectRecommend an approach to change the use

of the current NIGP commodity codes from three to five digits to achieve a more granular reporting of expenditures with in the CSU.

FindingsThe CSU does not use commodity codes in a

uniform manner across campusesLarge jump from 270 select codes to the full

five digit NIGP seriesUNSPSC or NIGP are both used in online

catalogues

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

CSU Data by the Numbers

2012/13 Fiscal Year Transaction Count1,028,000 AP voucher lines (313,000

vouchers)242,000 pCard transactions91 tables

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

pCard $127m

Non-pCard $1.3b

pCard 242,000

Non-pCard 313,000

High Level View of DataTransaction Volume In Dollars

95% Accuracy Target

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Low Level Data Rolled Up

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Spikes Chart here – Whole SlideInvoice Distribution by Range of Invoice

Values (Summary) all campuses – non p-card filter

Low Level Data Charted

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Spikes chart here -Whole SlideInvoice Distribution by Range of Invoice

Values (Low End) non-pcard

Low Level Data Drilled

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Spikes chart here -Whole SlideInvoice Distribution by Range of Invoice

Values (Low End) non-pcard

Conclusions?

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Progressive Value-add to the CSU over Three YearsYear One (the learning year)

Perfected data collection methodologiesCustom face-to-face visits/demos/training for

many campuses and groups (FOA, PSSOA)CSP Data Analyst serves as first response to

end user support.Spend Analytics used in several CO projects

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Progressive Value-add to the CSU over Three YearsYear Two (guiding campuses to savings,

creating awareness)Delivered 23 campus-tailored Savings

Opportunity Assessments and tutorials in:Invoice Consolidation (reduce invoice

volume) Category Rationalization (reduce vendors in

highly populated commodity or service categories)

Compliance with Contracted Vendors (confirming campus is purchasing from vendors under contract)

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Invoice Consolidation From 2011/12 to 2012/13 – Reduction of AP invoices in almost all campuses (2%

systemwide, over 5,300 invoices) During the same period – overall increase of 1% in total spend Increase of 2% in pCard usage According to RPMG Research Corp, each invoice can cost up to $71 from receipt to

payment. Overall value-add would then exceed $376,000 (2010 Purchasing Card Benchmark Survey)

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Progressive Value-add to the CSU over Three Years Year Three in progress (“spend under

management”) Development of project program to define how much

CSU total spend is being managed (under contract). This is a necessary metric in measuring strategic

sourcing and contract compliance success. Campuses will be presented with a specific action

plan to bring greater spend under contract Joint development (at no cost to CSU) of custom

dashboard views of spend for executive review.

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Spend Under Management849 contracts uploaded to the Spikes

Observatory from:CSU systemwide contractsUC contractsDGS contractsE&I contracts

One-stop place to search for available contracts

Ability to track monthly spend with contracted vendors

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Spend Under Management

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Spend Under ManagementMonthly spend with contracted vendors

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

Example ofCampus Use of Data:

San Luis Obispo

Dru Zachmeyer, Director

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

San Luis Obispo

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

San Luis Obispo

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

San Luis Obispo

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

San Luis Obispo

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

San Luis Obispo

2 0 1 4 F O A / P SS O A C S U B u s i n e s s C o n f e r e n c e

San Luis Obispo