Cost Structure Analysis

Python
# Basic cost categorization and analysis
import pandas as pd
import numpy as np

# Load P&L data
df = pd.read_csv('pl_data.csv')

# Step 1: Categorize costs
cost_categories = {
    'fixed': ['rent', 'insurance', 'salaries_base', 'depreciation'],
    'variable': ['materials', 'commissions', 'shipping', 'utilities_variable'],
    'semi_variable': ['utilities', 'maintenance', 'overtime', 'consulting']
}

# Step 2: Calculate unit economics
total_revenue = df['revenue'].sum()
total_costs = df['total_costs'].sum()
units_sold = df['units_sold'].sum()

cost_per_unit = total_costs / units_sold
cost_per_revenue_dollar = total_costs / total_revenue

print(f"Cost per unit: ${cost_per_unit:.2f}")
print(f"Cost ratio: {cost_per_revenue_dollar:.1%}")

Progress:

• Data preparation and validation
• Cost categorization (fixed/variable/semi-variable)
• Unit economics calculation
• Outlier and anomaly identification
• Industry benchmarking
• Pareto analysis (80/20 rule)
• Cost driver tree creation
• Optimization recommendations with ROI

Step 1: Data Preparation

Python
# Validate and clean P&L data
required_columns = ['cost_center', 'amount', 'cost_type', 'period']
df = df.dropna(subset=required_columns)
df['amount'] = pd.to_numeric(df['amount'], errors='coerce')

Step 2: Cost Categorization

Python
def categorize_costs(df):
    df['category'] = df['cost_type'].map({
        'rent': 'fixed', 'salaries': 'fixed', 'insurance': 'fixed',
        'materials': 'variable', 'shipping': 'variable', 'commissions': 'variable',
        'utilities': 'semi_variable', 'maintenance': 'semi_variable'
    })
    
    # Calculate percentages
    category_summary = df.groupby('category')['amount'].sum()
    category_pct = (category_summary / category_summary.sum() * 100).round(1)
    
    return category_summary, category_pct

Step 3: Unit Economics

Python
def calculate_unit_economics(df, volume_metric):
    metrics = {
        'cost_per_unit': df['total_costs'].sum() / df[volume_metric].sum(),
        'cost_per_employee': df['total_costs'].sum() / df['employee_count'].mean(),
        'cost_per_transaction': df['total_costs'].sum() / df['transactions'].sum()
    }
    return metrics

Step 4: Outlier Detection

Python
def identify_outliers(df, column='amount'):
    Q1 = df[column].quantile(0.25)
    Q3 = df[column].quantile(0.75)
    IQR = Q3 - Q1
    
    outliers = df[(df[column] < Q1 - 1.5*IQR) | (df[column] > Q3 + 1.5*IQR)]
    return outliers[['cost_center', column, 'cost_type']]

Step 5: Pareto Analysis

Python
def create_pareto_analysis(df):
    # Sort costs by amount descending
    cost_summary = df.groupby('cost_center')['amount'].sum().sort_values(ascending=False)
    
    # Calculate cumulative percentage
    cumulative_pct = (cost_summary.cumsum() / cost_summary.sum() * 100)
    
    # Identify 80% of costs
    pareto_80 = cost_summary[cumulative_pct <= 80]
    
    return {
        'top_20_pct_cost_centers': len(pareto_80),
        'represents_pct_of_total': (pareto_80.sum() / cost_summary.sum() * 100).round(1),
        'cost_centers': pareto_80.index.tolist()
    }

Step 6: Cost Driver Tree

Python
def build_cost_driver_tree(df):
    tree = {
        'total_costs': df['amount'].sum(),
        'by_category': {
            'fixed': df[df['category']=='fixed']['amount'].sum(),
            'variable': df[df['category']=='variable']['amount'].sum(),
            'semi_variable': df[df['category']=='semi_variable']['amount'].sum()
        },
        'top_drivers': df.groupby('cost_center')['amount'].sum().nlargest(5).to_dict()
    }
    return tree

Example 1: Manufacturing Company Input: P&L with $2M costs, 100K units produced Output:

• Cost per unit: $20.00
• Fixed costs: 40% ($800K)
• Variable costs: 45% ($900K)
• Semi-variable: 15% ($300K)
• Top 3 cost centers represent 65% of total costs

Example 2: SaaS Company Input: Monthly costs $500K, 10K customers, 50 employees Output:

• Cost per customer: $50/month
• Cost per employee: $10K/month
• Customer acquisition costs: 25% of total
• Pareto: 20% of cost centers = 78% of expenses

1. Standardize chart of accounts before analysis
2. Use rolling 12-month averages for seasonal businesses
3. Include fully-loaded costs (benefits, overhead allocation)
4. Benchmark against 3-5 comparable companies minimum
5. Calculate ROI for initiatives >$50K investment
6. Update analysis monthly for operational costs

Python
def calculate_optimization_roi(current_cost, proposed_cost, implementation_cost, annual_volume):
    annual_savings = (current_cost - proposed_cost) * annual_volume
    roi = (annual_savings - implementation_cost) / implementation_cost * 100
    payback_months = implementation_cost / (annual_savings / 12)
    
    return {
        'annual_savings': annual_savings,
        'roi_percent': roi,
        'payback_months': payback_months
    }

• Mixing accrual vs cash accounting - Use consistent basis
• Ignoring allocation methods - Document overhead allocation rules
• Static benchmarks - Update industry comparisons quarterly
• Missing implementation costs - Include training, system changes
• Over-optimizing small costs - Focus on material impact (>2% of total)
• Forgetting cross-functional impact - Consider downstream effects of cuts