# Data Understanding — Invictus Programme Spreadsheets

This repository is the data layer for a £295M capital construction programme at a steel manufacturing facility. Think of it as a loosely normalised relational database spread across ~90 Excel files: budgets, contracts, purchase orders, invoices, risks, and schedule milestones. Every file is a table. Two identifiers — **CBS Code** and **WBS Element** — act as foreign keys that join most of them together. If you can hold those two concepts in your head, the rest of the schema falls into place quickly.

---

## The Two Global Identifiers

Before looking at any individual file, understand these two keys. They appear in almost every table.

### CBS Code — *what kind of work*

CBS stands for Cost Breakdown Structure. It is a 4-level hierarchical number that classifies spend by work type, not by location or contractor:

```
1               → Site Establishment & Preparation          (L1)
1.1             → Diversions                                 (L2)
1.1.1           → Electrical Equipment & Cable Diversions   (L3)
1.1.1.1         → Electrical Labour & Materials 1           (L4, leaf — carries the £ value)
```

L4 nodes are leaves. L1–L3 are rollup buckets with no direct spend against them. Every purchase order and every actual payment is stamped with the L4 CBS code it belongs to.

### WBS Element — *which SAP activity*

WBS stands for Work Breakdown Structure. It is a SAP project code formatted as `3701.00025-C-20-101`. It identifies the specific activity and plant area within SAP. Where CBS answers "what type of work", WBS answers "where in the project hierarchy does this sit". The two together give the full financial address of any transaction.

---

## File Groups

### 1. FID Budgets — `Cost Data/CBS Master/`

Four files, one per plant area:

| File | Area | Total FID Budget |
|---|---|---|
| `Controlled Meltshop FID Budget V10.xlsx` | Meltshop | £835M (programme-wide basis) |
| `Controlled Casters FID Budget V1.xlsx` | Casters | ~£51M |
| `Controlled Pickleline FID Budget V1.xlsx` | Pickleline | ~£200M |
| `Controlled Hot Rolled Products Life Extension FID Budget V1.xlsx` | HRP | ~£31.5M |

Each file has one main sheet. The schema is the same across all four:

| Column | Type | Description |
|---|---|---|
| `CBS - OLD` | float | Legacy CBS number (L1 rollup, e.g. `1`, `1.1`) |
| `Column1` | string | WBS suffix (e.g. `C-20-101`) — joins to Act & Commit |
| `CBS - New` | string | Current CBS code (e.g. `1.1.1.1`) — the join key to actuals |
| `L4 Description` | string | Human-readable name for the budget line |
| `L1 Totals` | float | Rollup at level 1 — only populated on L1 rows |
| `L2 Totals` | float | Rollup at level 2 — only populated on L2 rows |
| `L3 Totals` | float | Rollup at level 3 — only populated on L3 rows |
| `L4 Totals` | float | Leaf budget value in £ — only populated on L4 rows |
| `PM` | string | Project manager initials (e.g. `ML`, `IE`) |

> [!note] Reading this schema in pandas
> The header row is row 4 (`header=3`). Rows 1–3 contain dynamic/static totals used for Excel validation. Filter to rows where `L4 Totals` is not null to get only leaf budget lines.

**How this joins outward:** `CBS - New` on a leaf row matches the `CBS` column in `Invictus Actuals` and the `CBS` column in Cost Tool `POAccruals`. This is how you know whether actual spend on a PO is within or over the FID budget for that line.

---

### 2. Cost Tracking — `Cost Data/Act & Commit Master.xlsx`

This is the closest thing to a transactions table. It is SAP-sourced — populated by exports from the project's ERP system — so it is the authoritative record of money committed and money paid.

#### Sheet: `Invictus Commitments`

A commitment is a Purchase Order that has been raised but not yet fully invoiced. Think of it as money ring-fenced.

| Column | Type | Description |
|---|---|---|
| `WBS Element` | string | e.g. `3701.00025-C-20-101` — primary join key to CBS Master via the suffix |
| `Ref. Purchase Req.` | string | Requisition number (internal approval that precedes a PO) |
| `Ref Document Number` | string | SAP document ID |
| `Name` | string | Line item description (e.g. `CAPEX Welfare Facilities Cleaning BOS Q2`) |
| `Name 1` | string | Supplier legal name (e.g. `COMPASS CONTRACT SERVS (U.K.) LTD`) |
| `Val/COArea Crcy` | float | Committed value in GBP |
| `Document Date` | date | Date the PO was created |
| `Debit date` | date | Expected payment date |
| `Payment Terms` | string | Net 30, etc. |
| `Req` | string | Requisition number (short form) |
| `ReqUnique` | string | Concatenated req + line item (unique key) |
| `PO` | string | Purchase order number |
| `POUnique` | string | Concatenated PO + line item (unique key) |

#### Sheet: `Invictus Actuals`

An actual is an invoice that has been posted and paid. These are the real cash outflows.

| Column | Type | Description |
|---|---|---|
| `Fiscal Year` | string | SAP fiscal year (e.g. `2025`) |
| `Period` | string | SAP accounting period (1–12) |
| `WBS Element` | string | Same format as commitments — join key |
| `Ref Document Number` | string | SAP document ID |
| `Purchasing Document` | string | PO number — joins to commitments via `PO` |
| `Name` | string | PO line description |
| `Value in Obj. Crcy` | float | Invoice value in GBP (can be negative for reversals) |
| `Object Currency` | string | Always `GBP` |
| `Posting Date` | date | Date the invoice was posted in SAP |
| `CBS` | string | CBS L4 code — joins to FID Budget `CBS - New` |
| `PM` | string | Project manager initials |
| `Supplier Name` | string | Full SAP legal name (e.g. `TENOVA S.P.A.`) |

> [!warning] Name vs Name 1 vs Supplier Name
> `Name` in both sheets is the *PO line description* (what was ordered). The actual *vendor* is `Name 1` in Commitments and `Supplier Name` in Actuals. These use different naming conventions — `Name 1` = short form, `Supplier Name` = full SAP legal name. They are not directly comparable as strings without a mapping.

---

### 3. Cost Tool — `Cost Data/New Commitments/`

Two files: `Cost Tool Meltshop New_Dec 25.xlsx` and `Cost Tool Casters New - Live.xlsx`. These are working files maintained by the project team — they import from SAP and layer additional analysis on top. Each has 15 sheets. The analytically relevant ones:

| Sheet | Header row | Key columns |
|---|---|---|
| `SAP Commitments` | 1 | Same as `Invictus Commitments` — area-filtered copy |
| `SAP Actuals` | 1 | Same as `Invictus Actuals` — area-filtered copy |
| `POAccruals` | 4 | `CBS`, `PM`, `Req Number`, `PO Number`, `PO Unique`, `Supplier`, `Description`, `Accrual (GBP)`, `Accrual Date` |
| `Budget Accruals` | 4 | `SAP Project Code`, `CBS`, `FID Budget Description`, `L4 Totals`, `PM`, `Accrual (GBP)`, `Accrual Date` |
| `Detailed Summary` | 2 | Rollup totals only — no row-level data |
| `PO Summary` | 1 | PO-level summary with committed vs actual |

An **accrual** is an estimated cost that has been incurred but not yet invoiced — it bridges the gap between what SAP shows as paid and what the project team believes has been spent. Accruals are the team's manual correction layer on top of SAP data.

---

### 4. Commercial Management — `Commercial Management/Early Warning _ Quotations and NCE Data.xlsx`

This file tracks the contractual lifecycle: who was awarded what, how contracts are evolving, and what formal change events have been raised.

#### Sheet: `Contract Value and Owners List`

| Column | Type | Description |
|---|---|---|
| `Vendor` | string | Contractor name (short form, e.g. `ABB (Power Distribution System)`) |
| `Contract Number` | string | Full SAP contract reference (e.g. `ABB Ltd PEL2001 3701.00025.ABB.200000...`) |
| `Contract Owner` | string | Named individuals responsible (e.g. `Stephen Dawson - Dave Murray`) |
| `Contract Value` | float | Base awarded value in £ |

#### Sheet: `Weekly Report` (header row 2)

The live commercial dashboard. One row per contract.

| Column | Type | Description |
|---|---|---|
| `Contract` | string | Short contract label (e.g. `Tenova`, `SRM`) |
| `Contractor` | string | Full contractor name (e.g. `TALLERES JASO INDUSTRIAL SL`) |
| `Base Contract Value` | float | Original awarded value in £ |
| `Implimented Variations (£)` | float | Approved and enacted contract changes |
| `Unimplimented Variations (£)` | float | Approved but not yet enacted |
| `Total (£)` | float | Base + both variation columns |
| `Total Commitment (%)` | float | Total / Base — variation multiplier (1.0 = no change) |
| `Contract Manager` | string | Named person accountable |
| `Remark` | string | Free-text notes |

#### Sheet: `Q and NCE Data` (header row 5, **use this not `Quotation DB`**)

Every individual change quotation or NCE raised on any contract. `Quotation DB` is a filtered Excel dashboard view of this sheet — do not read `Quotation DB` programmatically.

| Column | Type | Description |
|---|---|---|
| `Area` | string | Plant area (Meltshop / Pickle Line / Programme / etc.) |
| `Contractor` | string | Contractor name — joins to `Weekly Report`.`Contractor` |
| `Contract Number` | string | Joins to `Contract Value and Owners List` |
| `Record Number` | string | e.g. `QUOTE-000001` — unique change quote ID |
| `Notification of Compensation Event` | string | NCE reference if this is a formal NCE |
| `Instruction` | string | Project instruction reference triggering the change |
| `Status` | string | `Submitted`, `Accepted`, `Rejected`, etc. |
| `Quotation Date` | date | When contractor submitted the price |
| `Quotation By` | string | Named person at the project who requested it |
| `Title` | string | Description of the change scope |
| `Change to the prices` | float | £ change (positive = cost increase) |
| `Change to Days` | int | Programme days affected |
| `Quotation Decision` | mixed | `Accepted` / `0` / blank |

> [!note] These are post-award variations, not original tender bids
> Every record in this sheet is a change to an existing contract — not the original competitive bid. Original tender submissions are not in this repository.

#### Sheet: `EW all Data`

Early Warnings — formal notices that a potential scope/cost/programme change may be coming. These are upstream of NCEs in the change management process. The schema is similar to Q and NCE Data but includes an EW-specific category column pulled from the `Data List` sheet.

---

### 5. Risk Management — `Risk Management/[Area]/[Area]_[Risk|Issue]/`

64 files total. Two types per area: **Risk** (something that *might* happen) and **Issue** (something that *has* happened). Nine monthly snapshots of each from June 2025 to February 2026.

Filename pattern: `YYYYMMDD [Area] [Risk|Issue]_MainForm_LineItems.xlsx`

Each file has four data-bearing sheets:

#### Sheet: `Main Form`

| Column | Type | Description |
|---|---|---|
| `Record ID` | int | Internal numeric ID |
| `Record No.` | string | e.g. `RISK-00001` — human-readable unique key |
| `Title` | string | Short name for the risk |
| `Status` | string | `Open` / `Closed` |
| `Risk Type` | string | `Threat` or `Opportunity` |
| `Risk Priority` | string | `High` / `Medium` / `Low` |
| `Risk Category` | string | `Cost & Schedule`, `Cost`, `Schedule`, etc. |
| `Pre-Response Probability of Occurrence` | string | Enum: `Unlikely (21-40%)`, `Likely (61-80%)`, etc. |
| `Short Description` | string | One-line description |
| `Cost Impact` | string | `Yes` / `No` — whether a cost estimate exists |
| `Schedule Impact` | string | `Yes` / `No` |
| `Owner Cost Estimate` | float | £ estimate if `Cost Impact = Yes` |

#### Sheet: `Detailed Cost Estimate`

Line-item cost breakdown per risk. One risk can have multiple rows here.

| Column | Type | Description |
|---|---|---|
| `Record ID` | int | FK → `Main Form`.`Record ID` |
| `Line Item ID` | int | Unique ID for this cost line |
| `Record Number` | string | FK → `Main Form`.`Record No.` (e.g. `RISK-00001`) |
| `Cost Code` | string | Composite key: `Area~~WBS~~CBS` (e.g. `Meltshop~~3701.00025.C.90~~90`) |
| `Potential Cost Impact` | float | £ value for this line |
| `Pre-Response Probability of Occurrence` | string | Same enum as Main Form |
| `Detailed Line Item Information` | string | Free-text explanation of cost basis |

#### Sheet: `Response Actions`

Mitigations being taken against each risk.

| Column | Type | Description |
|---|---|---|
| `Record ID` | int | FK → `Main Form`.`Record ID` |
| `Record Number` | string | FK → `Main Form`.`Record No.` |
| `Short Description` | string | What the mitigation is |
| `Title` | string | Name of the action |
| `Assigned To` | string | Named person responsible |
| `Response Action Status` | string | `Proposed` / `In Progress` / `Completed` |
| `Planned Start` / `Planned Finish` | date | Action timeline |

#### Sheet: `Codes And Descriptions`

Lookup table for valid cost codes. Format: `Area~~WBS~~CBS~~CBS-Level~~Description`.

---

### 6. Milestone Data — `Milestone Data/GFA milestone data from P6.xlsx`

A Primavera P6 export. Primavera P6 is the scheduling tool used on major capital projects — think Jira, but for construction timelines.

| Column | Type | Description |
|---|---|---|
| `Activity ID` | string | P6 activity identifier (e.g. `INV0090`) |
| `Activity Name` | string | Human-readable milestone description |
| `Finish` | date | Actual or forecast completion date |
| `Target deliverable Dates` | date | Original planned date at baseline |
| `Material Long stop dates` | date | Latest acceptable date before the milestone is considered failed |

This file does not share a key with any other file. It joins conceptually by area name but has no programmatic foreign key. It provides the time axis the cost data lacks — if a WBS element is behind schedule, that shows up here as a slipped `Finish` vs `Target deliverable Dates`.

---

## Entity Relationship Map

The files form a star-like graph with CBS Code and WBS Element at the centre.

```mermaid
erDiagram
    FID_BUDGET {
        string CBS_New PK
        string WBS_suffix FK
        string L4_Description
        float L4_Totals
        string PM
    }
    INVICTUS_ACTUALS {
        string WBS_Element FK
        string CBS FK
        string Supplier_Name FK
        float Value_GBP
        date Posting_Date
        string PO_UniqueID
    }
    INVICTUS_COMMITMENTS {
        string WBS_Element FK
        string Name_1 FK
        float Val_GBP
        string PO
    }
    CONTRACTS {
        string Vendor FK
        string Contract_Number PK
        float Contract_Value
    }
    WEEKLY_REPORT {
        string Contractor FK
        float Base_Contract_Value
        float Total_GBP
        float Variation_pct
    }
    NCE_QUOTATIONS {
        string Record_Number PK
        string Contractor FK
        string Contract_Number FK
        float Change_to_prices
        string Status
    }
    RISK_MAIN_FORM {
        string Record_No PK
        string Area FK
        string Risk_Type
        float Owner_Cost_Estimate
    }
    RISK_COST_ESTIMATE {
        string Record_Number FK
        string Cost_Code
        float Potential_Cost_Impact
    }
    RISK_RESPONSE_ACTIONS {
        string Record_Number FK
        string Assigned_To
        string Status
    }
    P6_MILESTONES {
        string Activity_ID PK
        date Finish
        date Target_Date
    }

    FID_BUDGET ||--o{ INVICTUS_ACTUALS : "CBS_New = CBS"
    FID_BUDGET ||--o{ INVICTUS_COMMITMENTS : "WBS_suffix in WBS_Element"
    CONTRACTS ||--o{ WEEKLY_REPORT : "Vendor ~ Contractor"
    CONTRACTS ||--o{ NCE_QUOTATIONS : "Contract_Number"
    WEEKLY_REPORT ||--o{ NCE_QUOTATIONS : "Contractor"
    INVICTUS_ACTUALS ||--o{ WEEKLY_REPORT : "Supplier_Name ~ Contractor (mapped)"
    RISK_MAIN_FORM ||--o{ RISK_COST_ESTIMATE : "Record_No = Record_Number"
    RISK_MAIN_FORM ||--o{ RISK_RESPONSE_ACTIONS : "Record_No = Record_Number"
```

> [!warning] The Actuals → Contracts join requires a name map
> `Supplier Name` in Actuals uses full SAP legal names (`TENOVA S.P.A.`). `Contractor` in Weekly Report uses short names (`Tenova`). There is no shared key — you need an explicit mapping. See [bid_comparision_output.py](bid_comparision_output.py) lines 63–77 for the `VENDOR_MAP` dict that handles this.

---

## How to Read Each File in pandas

```python
import pandas as pd

# FID Budget (any area)
fid = pd.read_excel("Cost Data/CBS Master/Controlled Meltshop FID Budget V10.xlsx",
                    sheet_name="FID3 Budget ", header=3)
leaf_budgets = fid[fid["L4 Totals"].notna()]

# Actuals
actuals = pd.read_excel("Cost Data/Act & Commit Master.xlsx",
                        sheet_name="Invictus Actuals")

# Commitments
commits = pd.read_excel("Cost Data/Act & Commit Master.xlsx",
                        sheet_name="Invictus Commitments")

# Weekly Report (contracts + variations)
weekly = pd.read_excel(
    "Commercial Management/Early Warning _ Quotations and NCE Data.xlsx",
    sheet_name="Weekly Report", header=1)

# NCE / Quotations (raw — NOT Quotation DB)
nce = pd.read_excel(
    "Commercial Management/Early Warning _ Quotations and NCE Data.xlsx",
    sheet_name="Q and NCE Data", header=4)

# Risk Main Form (one file — repeat for all 64)
risk = pd.read_excel(
    "Risk Management/Meltshop/Meltshop_Risk/20250623 Meltshop Risk_MainForm_LineItems.xlsx",
    sheet_name="Main Form")

# P6 Milestones
p6 = pd.read_excel("Milestone Data/GFA milestone data from P6.xlsx")
```

---

## Open Questions

1. **The `Detailed Summary` sheet in Cost Tool has no row-level data** — it contains only rollup totals. The row-level equivalent is `SAP Actuals` and `SAP Commitments` within the same file. Unclear whether `Detailed Summary` is a live formula sheet or a periodically pasted snapshot.

2. **Risk `Cost Code` composite key is not a clean FK** — the format `Meltshop~~3701.00025.C.90~~90` bundles area, WBS, and CBS into one string. Splitting on `~~` gives three parts, but `90` is an abbreviated CBS code that does not directly match the L4 format in FID Budget without a lookup through `Codes And Descriptions`.

3. **P6 Milestones have no programmatic link to cost data** — joins can only be made by area name inference. If automation is needed, an Activity ID → WBS mapping would need to be created manually.