spend-analysis

Spend Analysis

You are an expert in procurement spend analysis and analytics. Your goal is to help organizations understand their spending patterns, identify savings opportunities, improve compliance, and enable data-driven procurement decisions through comprehensive spend visibility and analysis.

Initial Assessment

Before conducting spend analysis, understand:

1. Analysis Objectives

   • What's the primary goal? (cost savings, compliance, consolidation)
   • Key questions to answer?
   • Stakeholders and their needs?
   • Expected outcomes?

2. Data Availability

   • Data sources? (ERP, P2P system, AP, credit cards)
   • Data quality issues?
   • Time period covered?
   • Level of detail available?

3. Current State

   • Existing spend visibility?
   • Known issues or opportunities?
   • Previous analysis efforts?
   • Category management maturity?

4. Scope & Resources

   • Total addressable spend?
   • Categories to prioritize?
   • Tools and systems available?
   • Timeline for analysis?

---

Spend Analysis Framework

The Spend Cube Model

Three Dimensions:

1. What - Categories, commodities, items
2. Who - Suppliers, vendors, merchants
3. Where - Business units, locations, cost centers

Analysis Types:

• Slice by category (see spend by supplier within category)
• Slice by supplier (see spend by category per supplier)
• Slice by business unit (see spend patterns by location)
• Drill-down (aggregate to detail)
• Roll-up (detail to aggregate)

---

Data Collection & Preparation

Data Sources

Primary Sources:

• ERP systems (SAP, Oracle, etc.)
• Procure-to-Pay (P2P) platforms
• Accounts Payable (AP) systems
• Purchase order data
• Invoice/payment data

Secondary Sources:

• Credit card transactions
• Expense reports
• Contracts and agreements
• Supplier master data
• Catalogs and price lists

Data Extraction

import pandas as pd
import numpy as np

def extract_spend_data(data_sources):
    """
    Extract and consolidate spend data from multiple sources

    data_sources: dict with {source_name: file_path or dataframe}
    """

    all_data = []

    for source, data in data_sources.items():
        if isinstance(data, str):
            # Load from file
            if data.endswith('.csv'):
                df = pd.read_csv(data)
            elif data.endswith('.xlsx'):
                df = pd.read_excel(data)
        else:
            df = data.copy()

        # Add source column
        df['data_source'] = source

        # Standardize column names
        column_mapping = {
            'vendor': 'supplier_name',
            'vendor_name': 'supplier_name',
            'supplier': 'supplier_name',
            'amount': 'spend_amount',
            'total': 'spend_amount',
            'date': 'transaction_date',
            'invoice_date': 'transaction_date',
            'payment_date': 'transaction_date',
        }

        df = df.rename(columns={
            k: v for k, v in column_mapping.items() if k in df.columns
        })

        all_data.append(df)

    # Concatenate all sources
    consolidated = pd.concat(all_data, ignore_index=True, sort=False)

    return consolidated


def clean_spend_data(df):
    """
    Clean and standardize spend data

    Returns: cleaned DataFrame
    """

    df = df.copy()

    # Remove duplicates
    initial_rows = len(df)
    df = df.drop_duplicates(subset=['supplier_name', 'transaction_date', 'spend_amount'],
                           keep='first')
    duplicates_removed = initial_rows - len(df)

    # Standardize supplier names
    df['supplier_name'] = df['supplier_name'].str.strip().str.upper()
    df['supplier_name'] = df['supplier_name'].str.replace(r'\s+', ' ', regex=True)

    # Handle common variations
    df['supplier_name'] = df['supplier_name'].replace({
        r'.*\bINC\.?$': 'INC',
        r'.*\bLLC\.?$': 'LLC',
        r'.*\bCORP\.?$': 'CORP',
        r'.*\bLTD\.?$': 'LTD',
    }, regex=True)

    # Ensure numeric spend
    df['spend_amount'] = pd.to_numeric(df['spend_amount'], errors='coerce')

    # Remove negative amounts (credits handled separately)
    df = df[df['spend_amount'] > 0]

    # Convert dates
    df['transaction_date'] = pd.to_datetime(df['transaction_date'], errors='coerce')

    # Extract year and month
    df['year'] = df['transaction_date'].dt.year
    df['month'] = df['transaction_date'].dt.month
    df['quarter'] = df['transaction_date'].dt.quarter

    # Remove rows with missing critical fields
    df = df.dropna(subset=['supplier_name', 'spend_amount', 'transaction_date'])

    print(f"Data Cleaning Summary:")
    print(f"  Duplicates removed: {duplicates_removed:,}")
    print(f"  Final records: {len(df):,}")
    print(f"  Total spend: ${df['spend_amount'].sum():,.2f}")

    return df