Ask the question in any multi-hospital group: what did we spend on ceftriaxone last year, across all sites? The honest answer is usually a number from one site, a number from pharmacy that Finance does not recognise, or “let me come back to you”. The problem is not people. It is that nobody has put the purchasing data into one picture. That picture is a spend cube, and it takes an afternoon.
What a spend cube is
A spend cube is purchasing data arranged along three axes: what was bought (category and item), who it was bought from (supplier) and where it was bought (site, department). Every cell holds value, quantity and the price paid. Once the cube exists, most procurement questions become a pivot table.
Step 1 — Pull the lines (30 minutes)
From the ERP or purchase system, export purchase-order lines or invoice lines for the last 12 months, every site. You need: date, site, supplier name, supplier ID if one exists, item code, item description, unit, quantity, unit price, line value, and if available the contract or tender reference. Do not filter. If the export is 300,000 lines, that is fine; Excel handles a million.
Step 2 — Fix the supplier names (60 minutes)
This is where the afternoon is won or lost. “Medline”, “Medline LLC”, “MEDLINE-DXB” and “Medline (new)” are one supplier in four spellings. Build a mapping table: raw name → clean name. Start with the 50 largest by value; they usually cover 80% of spend. Use the Vendors sheet in our Spend Cube Template — it flags near-duplicates automatically.
Step 3 — Assign categories (60 minutes)
Map each item to a category tree that matches how you will act on the data: pharmaceuticals (by therapeutic class), medical consumables, implants, laboratory reagents, radiology, equipment, services, facilities. A two-level tree is enough to start. Map by keyword rules first (“catheter” → consumables/urology), then hand-map the top 200 items by value.
Step 4 — Build the cube (20 minutes)
A pivot table with category and supplier on rows, site on columns, value in the cells. Add a second pivot: item × site with average unit price. Add a third: supplier × category with value and line count. That is the cube.
The four findings it always produces
- Price variance across sites. The same item, same pack, bought at prices 10–25% apart because each site negotiated alone. This is the first and fastest saving: harmonise to the best price already achieved in the group.
- Supplier fragmentation. Forty suppliers for a category that three could serve. Consolidation is the second saving — and it improves service.
- Maverick spend. Purchases outside contract at list price. Usually 10–20% of category spend; a compliance fix, not a negotiation.
- Expiring contracts nobody is tracking. The cube with contract dates attached becomes a renewal calendar.
What to do with it
Put the one-page dashboard in front of the CFO and the medical director together. Agree three categories to act on this quarter. Then — and this is the step most groups skip — set up a savings tracker that reconciles every claimed saving to the ledger, so the number that reaches the P&L is the number you report. The templates are free; if you would rather we run it on your real data, that is the free executive assessment.