Blog · Procurement and supply chain · Procurement
Build a supplier-category-unit-period spend view from AP lines in Excel, retaining credit notes, unmapped rows and control totals.
A spend cube is useful when a category manager can move from total purchases to the suppliers, units and months behind the total without changing the underlying number. In Excel, that can be a controlled line table feeding PivotTables rather than a complex new database.
This guide owns that multidimensional AP view. The spend-under-management guide retains the selected managed-spend measure, and the supplier-tail guide owns rationalization decisions. A cube provides evidence for those questions; it does not answer them merely by grouping rows.
Use one row per posted purchase invoice or credit-note line. Retain source company, document ID, line ID, supplier ID, category code, business unit, posting date, currency and signed net-spend amount. Keep tax and reporting-currency policies explicit.
A supplier invoice with three categories needs three lines. A single document-header total joined to each line repeats the invoice value three times. Conversely, grouping to one row per supplier too early loses the category and period evidence a reviewer needs.
Agree the time window, date basis, companies, included document types and treatment of reversals before loading. Purchase orders, invoices and payments describe different events; do not append them as additional spend. The purchase-order/invoice/payment comparison shows the distinction.
Create separate approved mappings for supplier, category and unit. Test that each source key has one applicable mapping for the relevant date. Keep unmatched keys in an explicit Unknown or Unmapped bucket until reviewed, so their amount stays in the control total.
Power Query documents multi-column joins and grouping by dimensions. Check compatible types and join grain. Use approved crosswalks; a similar supplier name does not prove that two businesses are the same. Merge queries, Group rows.
The following synthetic rows already use the same approved USD net-spend basis. The final row is a credit note, not an extra purchase.
| Line | Supplier | Category | Unit | Period | Net amount |
|---|---|---|---|---|---|
| A-1 | S-01 | Hardware | North | January | $6,000 |
| B-1 | S-01 | Hardware | South | January | $4,000 |
| C-1 | S-02 | Facilities | North | January | $3,000 |
| D-1 | S-02 | Facilities | South | February | $2,000 |
| E-1 | S-01 | Hardware | North | February | $4,000 |
| F-1 | S-01 | Hardware | North | February | -$1,000 |
| Total | $18,000 |
The category totals are Hardware $13,000 and Facilities $5,000. Unit totals are North $12,000 and South $6,000. January totals $13,000 and February $5,000. Each view sums to $18,000.
A hardware-by-unit pivot gives North $9,000 and South $4,000. That is a useful drill-down, but it is not a new $13,000 to add to the category total. Multiple views summarize the same rows.
Import the line table with explicit types. Validate unique document-line keys, normalize credit signs, join reviewed dimensions, and add a period field derived from the selected date basis. Load the result to an Excel table or Data Model, then create a PivotTable with signed amount in Values.
Begin with category in Rows, unit in Columns and period as a filter. Duplicate that view for supplier-by-category and category-by-period comparisons. Keep a source-line drill-through available, including the original identifiers and mapping versions.
If using measures in a Data Model, name each by basis: Net Invoiced Spend, for example. A count of invoice lines is not an invoice count; use distinct document identifiers where that distinction matters. Microsoft's PivotTable guidance describes the Excel aggregation workflow.
Record source rows, distinct documents, gross positive charges, negative credits, signed total, unmapped amount and post-join row count. In the example, positive charges total $19,000 and credits total -$1,000, reconciling to $18,000.
A merge that raises the signed total signals duplicated matches, not rising purchases. An empty category should stay in an unmapped row rather than be filtered away. Reconcile filtered pivots to their stated subset, and show whether slicers are active when exporting a review.
Category coding, supplier crosswalks and currencies determine whether comparisons are meaningful. A cube does not prove savings, contractual compliance or supplier replaceability. Credits arriving after the period can change a later view; preserve extract dates and report versions.
For practical acceptance checks, use the procurement pilot specification. Bring an authorized AP sample and independent control total to discuss a reporting scope. This file-based method does not promise an ERP connector or an already-configured live cube.
No. They represent different stages of purchases. Keep their event populations separate and reconcile them rather than adding their amounts.
Keep their amounts in a disclosed Unmapped bucket and source control total until an approved mapping is available.