The Spreadsheet Problem

Every finance team we’ve worked with has the same origin story. Margin analysis started in a single Excel workbook. Someone built a clever set of formulas. It worked for a while. Then the business grew, and that workbook became a monster — 47 tabs, circular references, manual data entry from three different systems, and a weekly ritual where an analyst spends an entire Friday copying numbers from the ERP into a pivot table.

The real cost isn’t the analyst’s time. It’s the decisions that get delayed because the numbers aren’t ready, the errors that slip through because nobody audits a spreadsheet with 12,000 formulas, and the institutional knowledge trapped in one person’s head about which cells to update and which to never touch.

At Harbor Software, we’ve built margin analysis automation systems for companies ranging from a 50-person e-commerce operation to a multi-division manufacturing firm. The technical details vary, but the trajectory is always the same: extract the logic from spreadsheets, encode it in software, and layer intelligence on top. This post walks through how we approach that transformation.

Understanding What the Spreadsheet Actually Does

Before writing any code, you have to reverse-engineer the spreadsheet. This is the most important and most underestimated phase of the project. Spreadsheets are deceptive — they look simple but encode years of accumulated business logic in formulas, conditional formatting, and hidden columns.

We start with what we call a “spreadsheet audit.” An engineer sits with the finance team and traces every calculation from input to output. We document:

• Data sources: Where does each input number come from? ERP export? Manual entry? Another spreadsheet?
• Calculation chains: What formulas transform inputs into outputs? What are the intermediate values?
• Business rules: Which calculations change based on product category, customer tier, region, or time period?
• Exceptions: Which cells have hardcoded overrides? Why?
• Outputs: What reports, charts, or summaries does the spreadsheet produce? Who reads them?

In one project for a distribution company, this audit revealed that the spreadsheet contained 14 different margin calculation methods depending on the product category, but only 3 were documented. The other 11 were embedded in nested IF statements that even the analyst who maintained the spreadsheet couldn’t fully explain. If we’d skipped the audit and built a system based on the documented business rules alone, the numbers would have been wrong for 70% of their SKUs.

The Data Pipeline Architecture

Once you understand the calculations, you need clean, reliable data flowing into the system. This is where most margin analysis automation projects succeed or fail. The calculations themselves are straightforward once you understand them. Getting the data right is the hard part.

Our standard architecture uses a three-layer pipeline:

┌─────────────────────────────────────────────────┐
│  Layer 1: Ingestion                              │
│  ┌──────┐  ┌──────┐  ┌──────┐  ┌──────────┐    │
│  │ ERP  │  │ CRM  │  │ POS  │  │ Shipping │    │
│  │ API  │  │ API  │  │ Feed │  │ Carrier  │    │
│  └──┬───┘  └──┬───┘  └──┬───┘  └────┬─────┘    │
│     └─────────┴─────────┴───────────┘           │
│                    │                             │
│  Layer 2: Transformation & Storage               │
│  ┌─────────────────▼──────────────────────┐     │
│  │  PostgreSQL (normalized fact tables)    │     │
│  │  + dbt models for margin calculations  │     │
│  └─────────────────┬──────────────────────┘     │
│                    │                             │
│  Layer 3: Intelligence & Presentation            │
│  ┌────────┐  ┌────────┐  ┌────────────┐        │
│  │ Alerts │  │ Dashbd │  │ AI Anomaly │        │
│  │ Engine │  │  (viz) │  │ Detection  │        │
│  └────────┘  └────────┘  └────────────┘        │
└─────────────────────────────────────────────────┘

Layer 1: Ingestion

Most ERP systems (NetSuite, SAP, Dynamics) have APIs, but they’re often poorly documented, rate-limited, and return data in formats that require significant transformation. We build dedicated connectors for each source system.

A connector is a small service responsible for one thing: pulling data from a source system and writing it to a staging table in its raw form. No transformation at this stage. If the ERP returns cost data in a weird nested XML structure, we store the weird nested XML. Transformation comes later.

# connectors/erp_connector.py
import asyncio
from datetime import datetime, timedelta
from typing import AsyncGenerator

class ERPConnector:
    def __init__(self, config: ERPConfig):
        self.client = ERPClient(
            base_url=config.base_url,
            api_key=config.api_key,
            rate_limit=config.rate_limit_per_minute
        )
        self.db = get_database()
    
    async def sync_transactions(
        self, since: datetime
    ) -> AsyncGenerator[dict, None]:
        """Pull transactions modified since the given timestamp."""
        cursor = None
        while True:
            response = await self.client.get_transactions(
                modified_after=since,
                cursor=cursor,
                page_size=500
            )
            
            for txn in response.data:
                yield txn
            
            if not response.has_more:
                break
            cursor = response.next_cursor
    
    async def run_incremental_sync(self):
        """Sync only changes since last successful run."""
        last_sync = await self.db.get_last_sync_timestamp('erp_transactions')
        since = last_sync or datetime(2020, 1, 1)
        
        batch = []
        count = 0
        async for txn in self.sync_transactions(since):
            batch.append({
                'source': 'erp',
                'source_id': txn['id'],
                'raw_data': txn,
                'ingested_at': datetime.utcnow()
            })
            
            if len(batch) >= 100:
                await self.db.upsert_staging('erp_transactions_raw', batch)
                count += len(batch)
                batch = []
        
        if batch:
            await self.db.upsert_staging('erp_transactions_raw', batch)
            count += len(batch)
        
        await self.db.set_last_sync_timestamp('erp_transactions', datetime.utcnow())
        return count

The key design decision here is incremental sync. We track the last successful sync timestamp and only pull changes since then. This keeps sync times short (minutes instead of hours) and reduces load on the source system. For the initial backfill, we run a one-time full sync.

Layer 2: Transformation with dbt

Raw data from connectors is messy and denormalized. We use dbt (data build tool) to transform it into clean, analysis-ready tables. dbt is perfect for this because it lets you write transformations as SQL, version-control them, and test them automatically.

Here’s a simplified dbt model that calculates product-level gross margin:

-- models/marts/product_margin.sql
WITH sales AS (
    SELECT
        product_id,
        order_date,
        SUM(quantity) AS units_sold,
        SUM(line_total) AS gross_revenue,
        SUM(discount_amount) AS total_discounts
    FROM {{ ref('stg_orders') }}
    WHERE order_status NOT IN ('canceled', 'returned')
    GROUP BY product_id, order_date
),

costs AS (
    SELECT
        product_id,
        effective_date,
        unit_cost,
        freight_per_unit,
        ROW_NUMBER() OVER (
            PARTITION BY product_id
            ORDER BY effective_date DESC
        ) AS cost_recency
    FROM {{ ref('stg_product_costs') }}
),

current_costs AS (
    SELECT product_id, unit_cost, freight_per_unit
    FROM costs
    WHERE cost_recency = 1
),

margin_calc AS (
    SELECT
        s.product_id,
        s.order_date,
        s.units_sold,
        s.gross_revenue,
        s.total_discounts,
        s.gross_revenue - s.total_discounts AS net_revenue,
        c.unit_cost * s.units_sold AS total_cogs,
        c.freight_per_unit * s.units_sold AS total_freight,
        (s.gross_revenue - s.total_discounts) -
            (c.unit_cost * s.units_sold) -
            (c.freight_per_unit * s.units_sold) AS gross_margin,
        CASE
            WHEN (s.gross_revenue - s.total_discounts) > 0 THEN
                ((s.gross_revenue - s.total_discounts) -
                 (c.unit_cost * s.units_sold) -
                 (c.freight_per_unit * s.units_sold)) /
                (s.gross_revenue - s.total_discounts) * 100
            ELSE 0
        END AS gross_margin_pct
    FROM sales s
    LEFT JOIN current_costs c ON s.product_id = c.product_id
)

SELECT * FROM margin_calc

We add dbt tests to validate the transformation pipeline. These catch issues like negative margins (which might indicate a cost data problem), missing cost records, and revenue amounts that don’t reconcile with the source system:

# models/marts/product_margin.yml
version: 2
models:
  - name: product_margin
    description: "Daily product-level margin calculations"
    columns:
      - name: product_id
        tests:
          - not_null
      - name: gross_margin_pct
        tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: -100
              max_value: 100
      - name: net_revenue
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0"

Layer 3: Intelligence

This is where the system becomes more than a faster spreadsheet. Once you have clean, reliable margin data flowing through a pipeline, you can layer on intelligence that would be impossible in Excel.

Anomaly Detection: Catching Margin Erosion Early

One of the highest-value features we build is automated anomaly detection. Margins erode slowly — a supplier raises prices by 2%, a sales rep starts discounting more aggressively, a freight surcharge gets applied inconsistently. In a spreadsheet world, these problems are invisible until someone notices the quarterly numbers look bad.

We use a combination of statistical methods and simple rules to flag anomalies in real-time:

import numpy as np
from scipy import stats
from dataclasses import dataclass
from typing import Optional

@dataclass
class MarginAnomaly:
    product_id: str
    metric: str
    current_value: float
    expected_range: tuple[float, float]
    severity: str  # 'info', 'warning', 'critical'
    description: str

def detect_margin_anomalies(
    product_id: str,
    current_margin: float,
    historical_margins: list[float],
    lookback_days: int = 90
) -> list[MarginAnomaly]:
    anomalies = []
    
    if len(historical_margins) < 10:
        return anomalies  # Not enough data
    
    arr = np.array(historical_margins)
    mean = np.mean(arr)
    std = np.std(arr)
    
    # Z-score based detection
    if std > 0:
        z_score = (current_margin - mean) / std
        
        if abs(z_score) > 3:
            anomalies.append(MarginAnomaly(
                product_id=product_id,
                metric='gross_margin_pct',
                current_value=current_margin,
                expected_range=(mean - 2 * std, mean + 2 * std),
                severity='critical',
                description=(
                    f'Margin of {current_margin:.1f}% is {abs(z_score):.1f} '
                    f'standard deviations from the {lookback_days}-day mean '
                    f'of {mean:.1f}%'
                )
            ))
        elif abs(z_score) > 2:
            anomalies.append(MarginAnomaly(
                product_id=product_id,
                metric='gross_margin_pct',
                current_value=current_margin,
                expected_range=(mean - 2 * std, mean + 2 * std),
                severity='warning',
                description=(
                    f'Margin of {current_margin:.1f}% deviates significantly '
                    f'from the {lookback_days}-day mean of {mean:.1f}%'
                )
            ))
    
    # Trend detection: is margin consistently declining?
    if len(historical_margins) >= 30:
        recent_30 = arr[-30:]
        x = np.arange(len(recent_30))
        slope, _, r_value, p_value, _ = stats.linregress(x, recent_30)
        
        if slope < -0.05 and p_value < 0.05 and r_value ** 2 > 0.3:
            daily_decline = abs(slope)
            monthly_decline = daily_decline * 30
            anomalies.append(MarginAnomaly(
                product_id=product_id,
                metric='margin_trend',
                current_value=monthly_decline,
                expected_range=(0, 0),
                severity='warning',
                description=(
                    f'Margin is declining at {monthly_decline:.1f} percentage '
                    f'points per month over the last 30 days '
                    f'(R-squared: {r_value**2:.2f})'
                )
            ))
    
    return anomalies

This catches two kinds of problems: sudden spikes or drops (z-score detection) and slow erosion over time (trend detection). The trend detection is particularly valuable because it flags problems that humans miss — a 0.1% daily margin decline doesn’t feel alarming on any given day, but it’s a 3-point erosion over a month.

AI-Powered Root Cause Analysis

Detecting an anomaly is step one. Explaining it is step two. When the system flags that Product X’s margin dropped 5 points this week, the finance team’s first question is “why?”

We use an LLM-based analysis layer that examines the data surrounding an anomaly and generates a hypothesis. This isn’t magic — it’s structured prompting with real data:

async def generate_root_cause_analysis(
    anomaly: MarginAnomaly,
    context: dict
) -> str:
    """
    Generate a plain-language explanation for a margin anomaly
    by examining surrounding data points.
    """
    prompt = f"""You are a financial analyst examining a margin anomaly.

Product: {context['product_name']} (ID: {anomaly.product_id})
Anomaly: {anomaly.description}

Recent changes detected:
- Unit cost: {context['cost_change_pct']:+.1f}% vs last period
- Average selling price: {context['asp_change_pct']:+.1f}% vs last period
- Discount rate: {context['discount_rate_current']:.1f}% (was {context['discount_rate_previous']:.1f}%)
- Freight cost per unit: {context['freight_change_pct']:+.1f}% vs last period
- Volume: {context['volume_change_pct']:+.1f}% vs last period
- Channel mix: {context['channel_mix_summary']}

Based on these data points, provide a concise root cause analysis.
Identify the primary driver and any contributing factors.
Include the approximate dollar impact where possible."""

    response = await llm_client.generate(
        model='gpt-4o',
        messages=[{'role': 'user', 'content': prompt}],
        temperature=0.2,
        max_tokens=500
    )
    
    return response.content

The LLM doesn’t invent data — it synthesizes the structured data points we feed it into a readable narrative. In practice, the output looks something like: “The primary driver of the 5.2-point margin decline on Product X is a 12% increase in unit cost from Supplier A, effective March 1. This accounts for approximately $18,400 of the $23,100 total margin reduction. A secondary factor is a shift in channel mix — direct-to-consumer sales (which carry higher margins) dropped from 40% to 28% of volume, with the remainder moving to wholesale channels at lower price points.”

This is the kind of analysis that used to take a finance analyst half a day. The system produces it in seconds, and the analyst can spend their time validating the hypothesis and deciding on action rather than hunting through data.

The Reporting Layer

Dashboards are necessary but not sufficient. We’ve learned that the most impactful output of a margin analysis system isn’t a beautiful dashboard — it’s a weekly email that arrives in the CFO’s inbox with exactly three things: what changed, why it changed, and what to do about it.

We generate these summaries automatically from the anomaly detection and root cause analysis pipeline. The email is structured, scannable, and actionable. No one has to log into a dashboard to know something needs attention.

For the dashboard itself, we typically use Metabase or a custom React application connected to the PostgreSQL warehouse. The choice depends on the client’s technical sophistication and customization needs. Metabase is faster to deploy and lets the finance team build their own views. A custom dashboard gives you more control over the user experience and can integrate tightly with the alerting system.

Migration Strategy: Don’t Rip and Replace

The biggest mistake we see in these projects is trying to replace the spreadsheet on day one. Finance teams are deeply attached to their spreadsheets for good reason — they trust the numbers because they can trace every calculation by clicking on a cell.

Our migration strategy runs the new system in parallel with the spreadsheet for at least two full reporting cycles. The finance team continues to use the spreadsheet for actual decisions while we compare the system’s output against the spreadsheet’s output. Every discrepancy is investigated and resolved.

This parallel run builds trust. By the time we turn off the spreadsheet, the finance team has seen months of the system producing identical numbers (or catching errors the spreadsheet missed). The transition becomes a relief rather than a risk.

We also make sure the system can export to Excel. It sounds counterintuitive, but the ability to download a margin report as a spreadsheet and poke at the numbers is a critical trust-building feature. People need to verify, and meeting them where they are accelerates adoption.

What Changes After Automation

When the system is running, the transformation in the finance team’s work is tangible. The analyst who spent every Friday copying data now spends that time investigating anomalies the system flagged. The CFO gets margin visibility daily instead of monthly. Pricing decisions are made with same-week data instead of last-quarter data.

But the most valuable change is one that’s hard to quantify: confidence. When margin data is automated, tested, and reconciled, the leadership team trusts the numbers. They make faster decisions. They catch problems earlier. They stop sandbagging forecasts because they’re not sure the actuals are right.

That’s the real ROI of margin analysis automation. Not the analyst hours saved, but the quality of decisions made with reliable, timely data. Every project we’ve done in this space has paid for itself within two quarters — not from cost savings, but from margin improvement driven by visibility.