# Bid Comparison Plan — No LLM Required

This document is the execution plan for comparing contracts and bids across the Invictus programme. The data already exists in the Excel files in this repository. No AI is needed for the calculation — the comparison is straightforward arithmetic on three sheets. An LLM would only add value at the end, to narrate findings in plain English.

---

## The Core Question

For each contract/vendor: **how much did they originally agree to do the work for, and how much is the project now paying them?** The gap is the "variation creep" — the most important signal when evaluating whether a bid was genuinely competitive or just low to win and then expanded.

---

## The Three Source Sheets

All the data needed lives in `Commercial Management/Early Warning _ Quotations and NCE Data.xlsx`:

| Sheet | Key Columns | Role in comparison |
|---|---|---|
| **Contract Value and Owners List** | `Vendor`, `Contract Number`, `Contract Value` | The base — what each vendor was awarded |
| **Weekly Report** | `Contract`, `Contractor`, `Base Contract Value`, `Implimented Variations (£)`, `Unimplimented Variations (£)`, `Total (£)`, `Total Commitment (%)` | The current state — base + all variations to date |
| **Quotation DB** | `Contractor`, `Record Number`, `Status`, `Title`, `Change to the prices`, `Quotation Decision` | The change history — individual quotes that make up the variations |

> [!NOTE]
> The `Weekly Report` sheet is the most self-contained — it already has `Base Contract Value` and `Total (£)` on the same row per contractor. The comparison formula is already implicit in the data.

---

## The Comparison Formula

### In Excel (new sheet in the same workbook)

Add a new sheet called `Bid Comparison`. Pull from `Weekly Report`:

| Column | Formula (assuming Weekly Report data starts at row 2) |
|---|---|
| Vendor | `='Weekly Report'!B2` |
| Base Contract (£) | `='Weekly Report'!C2` |
| Variations Implemented (£) | `='Weekly Report'!D2` |
| Variations Pending (£) | `='Weekly Report'!E2` |
| Total Current Value (£) | `='Weekly Report'!F2` |
| Variation % | `=('Weekly Report'!F2 - 'Weekly Report'!C2) / 'Weekly Report'!C2` — format as % |
| Risk Flag | `=IF(('Weekly Report'!F2 - 'Weekly Report'!C2)/'Weekly Report'!C2 > 0.1, "⚠ >10%", "OK")` |

Sort descending by `Variation %` — the vendors at the top are where money is leaking.

### In Python (produces a CSV you can open in Excel)

```python
import pandas as pd

EXCEL_FILE = "Commercial Management/Early Warning _ Quotations and NCE Data.xlsx"

weekly = pd.read_excel(EXCEL_FILE, sheet_name="Weekly Report", header=1)

weekly = weekly.rename(columns={
    "Contractor": "Vendor",
    "Base Contract Value": "Base Contract (£)",
    "Implimented Variations (£)": "Variations Implemented (£)",
    "Unimplimented Variations (£)": "Variations Pending (£)",
    "Total (£)": "Total Current Value (£)",
})

weekly["Variation %"] = (
    (weekly["Total Current Value (£)"] - weekly["Base Contract (£)"])
    / weekly["Base Contract (£)"]
)

weekly["Risk Flag"] = weekly["Variation %"].apply(
    lambda x: "⚠ >10%" if x > 0.1 else "OK"
)

result = weekly[["Vendor", "Base Contract (£)", "Variations Implemented (£)",
                  "Variations Pending (£)", "Total Current Value (£)",
                  "Variation %", "Risk Flag"]]

result = result.sort_values("Variation %", ascending=False)
print(result.to_string(index=False))
```

Run with `/Users/yeshwanth/.venv/bin/python bid_comparision_output.py` and verify Phase 1 output before proceeding.

---

## Phase 2 — Drill Into a Specific Vendor's Variations

Once you have the ranked list, pick any high-variation vendor and pull their individual quotes from `Quotation DB`:

Change the `VENDOR` on line 40 to any contractor from the Phase 1 output. The script prints all their NCE/quotation records and the total accepted price change.

```python
VENDOR = "REPLACE WITH CONTRACTOR NAME"  # e.g. "SIR ROBERT MCALPINE LIMITED"

quotation_db = pd.read_excel(EXCEL_FILE, sheet_name="Quotation DB")

vendor_quotes = quotation_db[quotation_db["Contractor"].str.upper() == VENDOR.upper()]
print(vendor_quotes[["Record Number", "Status", "Title",
                      "Change to the prices", "Quotation Decision"]])
print("\nTotal accepted price change:",
      vendor_quotes.loc[vendor_quotes["Quotation Decision"] == "Accepted",
                        "Change to the prices"].sum())
```

> [!NOTE]
> `Quotation DB` is a filtered dashboard — the raw data is in the `Q and NCE Data` sheet with headers on row 5 (`header=4` in pandas).

---

## Phase 3 — Cross-Check Against Actual Spend

Join the contract comparison with `Invictus Actuals` from `Act & Commit Master.xlsx` to verify the contracted value matches what SAP has actually posted:

The actuals sheet uses full SAP legal names (e.g. `TENOVA S.P.A.`, `SIR ROBERT MCALPINE LIMITED`) which don't match the Weekly Report short names. The script uses an explicit `VENDOR_MAP` to bridge them. Two vendors — **Sarens** and **Churngold** — have no actuals postings in SAP yet and will show £0.

```python
ACTUALS_FILE = "Act & Commit Master.xlsx"

VENDOR_MAP = {
    "SRM": "SIR ROBERT MCALPINE LIMITED",
    "Tenova": "TENOVA S.P.A.",
    # extend as needed
}

actuals = pd.read_excel(ACTUALS_FILE, sheet_name="Invictus Actuals")
actuals_grouped = actuals.groupby("Vendor")["Amount"].sum().reset_index()
actuals_grouped.columns = ["SAP Vendor", "Actual Spend (£)"]

result["SAP Vendor"] = result["Vendor"].map(VENDOR_MAP).fillna(result["Vendor"])
result = result.merge(actuals_grouped, on="SAP Vendor", how="left")
result["Actual Spend (£)"] = result["Actual Spend (£)"].fillna(0)
result["Uncommitted (£)"] = result["Total Current Value (£)"] - result["Actual Spend (£)"]
result["Paid %"] = result["Actual Spend (£)"] / result["Total Current Value (£)"]

result.to_csv("bid_comparison_output.csv", index=False)
print("Written to bid_comparison_output.csv")
```

Phase 3 adds `Actual Spend (£)`, `Uncommitted (£)`, and `Paid %` to the Phase 1 table and writes everything to `bid_comparison_output.csv`.

This adds a column showing how much of the contract value is still unspent — useful for prioritising which contracts still have leverage.

---

## What an LLM Would Add (and What It Wouldn't)

| Task | LLM needed? |
|---|---|
| Calculate variation % per vendor | No — pure arithmetic |
| Rank vendors by variation creep | No — sort descending |
| Flag contracts over 10% variation | No — IF formula |
| Drill into what caused variations | No — filter Quotation DB by vendor |
| Cross-check actuals vs contracted | No — SUMIF / groupby |
| Write a plain-English summary of findings for the boss | Yes — this is where LLM earns its place |
| Spot non-obvious patterns across 30+ contracts | Maybe — worth trying once the data is clean |

The right workflow: **run the Python script → produce the ranked CSV → paste the top 10 rows into Claude and ask "what's the story here?"** That is the minimum viable version of what your boss is asking for.

---

## Open Questions

1. **Does `Quotation DB` track original bids or only post-award change quotations?** — From the sample data, all records have `QUOTE-000001` style IDs and a `Change to the prices` column, suggesting these are variation quotes not original competitive bids. If original tender bids exist, they may be in a separate document not yet in this repository.

2. **The `Weekly Report` row 1 is blank and headers are row 2** — the `header=1` in the Python script assumes this. Confirm before running.