tco

Building a Total Cost of Ownership (TCO) Workbook

This skill encodes the methodology for producing a defensible, customer-ready TCO across cloud platforms. It is process, not output — the same phases apply whether the engagement is AWS→GCP, Azure→GCP, on-prem→AWS, GCP modernization, or any other source/target combination.

When to use

A customer (or the sales lead representing one) hands over a bill, inventory dump, or VMware export and asks "what would this cost on Y, with discount mechanism Z?" The deliverable is typically a workbook (Excel / Google Sheets), but the methodology applies to any output format including presentation decks and markdown reports.

Core principles (apply to every phase)

These six rules apply to every TCO regardless of source/target. Violating any of them produces an output that won't survive an auditor.

1. Reconcile to the cent. Sum of source line items must equal the source bill total exactly. Build a reconciliation sheet that fails loudly if drift creeps in. If you can't reconcile, you cannot defend the output.

2. Every cost cell is a formula. No hardcoded numbers in customer-facing sheets. Rates live on a back-office sheet; quantities live on a back-office detail sheet; customer sheets pull both via SUMIFS / INDEX-MATCH. Change a rate once, the whole TCO updates. Hardcoded numbers rot the moment a rate changes or a fix lands.

3. Source-of-truth fields beat label parsing. If the bill has a column (e.g., rds_engine) with discrete values, route on that column. Never regex on a description string — hyphens vs spaces, casing, marketing-name drift will burn you. Prior engagements have wasted days on Aurora-PG vs Aurora PostgreSQL mismatches.

4. Sign convention, locked early, applied everywhere. Pick one and document it: typically NEGATIVE delta = target saves vs source, POSITIVE = target costs more. Verify it in the reconciliation audit. Mixing conventions across sheets is the single most common shipping bug.

5. Distinct-workload keying, not row duplication. A bill might have 5,000 EC2 line items but only 250 distinct (region × family × OS × tenancy) workloads. Roll up at the workload level before pricing; otherwise you map and price the same thing N times and reconciliation goes sideways.

6. Pricing scenarios are columns, not separate workbooks. On-Demand / 1Y / 3Y / Spot mix all sit side-by-side per workload, computed by formula from a single CUD/discount table. One source-of-truth, three output columns.

The phases

Phase 0 → Phase 10 are sequenced because each builds on the previous. Don't skip ahead; you'll backtrack. Stop and verify reconciliation between phases.

Phase 0 — Engagement scoping

Before touching the bill, get answers in writing from the customer or sales lead. Capture each in an Assumptions sheet with a named range.

Required:

• Source platform(s) and accounts in scope
• Target platform(s) and any region preferences (compliance, data residency, latency)
• Bill period (one month? Average across N months? Annualized?)
• Pre-discount or post-discount comparison? (List rates vs the customer's negotiated/EDP rates)
• Commitment scenarios to model (On-Demand, 1Y, 3Y, Spot/Preemptible mix, Savings Plans)
• Excluded categories (some customers exclude marketplace, support, taxes, certain accounts)
• Output format and audience (executive summary? engineering deep-dive? customer self-service workbook? all three?)

Useful but optional:

• Customer's existing commitments (RIs / Savings Plans / CUDs already purchased — affects post-discount comparison)
• Migration timeline (some optimizations only apply after a re-architect)
• Risk tolerance (does the customer want a conservative or optimistic optimization framing?)

Capture every parameter in a back-office Assumptions sheet with named ranges. Every later phase references these constants by name; if a parameter changes, you flip one cell and the whole workbook updates.

See: references/00-engagement-scoping.md

Phase 1 — Source inventory normalization

Load the bill into a canonical schema:

Field	Required	Example
entity / account	yes	prod-account
service	yes	Elastic Compute Cloud
sub-service / usage type	yes	BoxUsage:m6g.large
region	yes	ap-south-1
quantity	yes	730
unit	yes	Hrs
unit_price	yes	0.0768
charge_type	yes	charge / credit / refund / tax
period	yes	2026-04
engine / sku-group	when present	Aurora-PG, Aurora-Storage

Steps:

1. Load the bill (CSV, Parquet, BigQuery export, RVTools XLSX — depends on source).
2. Map to canonical schema. Preserve every column you might need later — drop nothing during normalization.
3. Filter to positive charges only (drop credits, refunds, tax) for the pre-discount baseline. Keep the unfiltered copy on disk for audit reference.
4. Validate: sum of line items must equal the bill total to the cent before proceeding.
5. Write to a _Source_Detail sheet (back-office, immutable raw layer — no formulas in this sheet).

Bill-loading specifics by source platform (load patterns, schema mapping, common gotchas) are in:

• references/01-inventory-normalization.md

Phase 2 — Pricing rates

Build a _Target_Rates back-office sheet with every rate the workbook will reference. Schema:

| rate_key | service | sku_description | region | unit | unit_price | source | source_url | retrieval_date | notes |

Rate sources, in priority order:

1. Official pricing API. GCP Cloud Catalog API, AWS Pricing API, Azure Retail Prices API. Pull all SKUs for services in scope.
2. Documented rates. For SKUs not surfaced by the API (network egress tiers, free-tier limits, marketplace passthroughs, premium add-ons), pull from the published pricing page and append with source = "documented" and the page URL.
3. Spot-checks. Verify 5–10 sample SKUs by hand against the pricing page. Catalog APIs occasionally return Confidential Mode / High-Availability / Storage-Pool variants instead of the base SKU.

Build a parallel _Commitment_Rates sheet for resource CUDs / Reserved Instances / Savings Plans, keyed by (service, sku_group, region, commit_term). Discount percentages live here, not the absolute rates — derive committed rates as =OD × (1 − discount_pct).

Catalog gotcha — exclusion list: When searching the catalog by description, exclude variants the customer doesn't want. Common exclusions for GCP: Confidential Mode, High Availability, Storage Pools, Sole Tenancy. The base SKU is usually the cheaper one; a variant SKU will silently 80%-discount or 5×-inflate your output without raising any error.

Full pricing-API patterns and the per-platform exclusion lists: references/02-pricing-rates.md

Phase 3 — Service mapping (concept-level)

For every distinct source service, decide the target service before pricing anything. Output: a customer-facing Service Mapping sheet.

| Source Service | Target Default | Target Secondary / Optimization | Classification | Parity Notes | Source URL |

Classifications:

• 1:1 — direct equivalent (e.g., RDS MySQL → Cloud SQL MySQL)
• Best-fit — close but not identical (e.g., Aurora-PG → AlloyDB; performance/feature differences worth flagging)
• Concept-only — no service equivalent, but the concept maps (e.g., AWS Savings Plans → GCP CUDs apply per resource not per spend)
• No-equivalent — no managed target service; customer self-hosts on compute or uses marketplace (e.g., Transfer Family → SFTP-on-VM)

Document parity gaps explicitly. The customer reads this sheet to understand "what's NOT a like-for-like comparison" — surprises here destroy trust later.

See: references/03-service-mapping.md

Phase 4 — Compute architectural-fit

Compute is its own phase because the family translation rules are non-trivial and customer-visible. The customer will scrutinize compute more than any other category.

Per-row outputs (one row per distinct compute workload):

• Source family + machine type + OS + tenancy
• Mapped target family + machine type
• Quantity (instance-hours) — preserved exactly from source
• Target rate (formula → _Target_Rates)
• OD / 1Y / 3Y costs as formulas
• OS license premium (Windows/RHEL premiums, Linux/SUSE = 0)
• Spot rows: 1Y/3Y CUD cells = =OD cell (no commitment discount on spot)
• Translation note column: human-readable per-row explanation

Family translation rules are platform-pair specific. For AWS→GCP they look roughly like:

• ARM Graviton → C4A (Axion ARM)
• AMD compute (c6a/c7a) → C2D
• AMD general/memory (m6a/r6a) → N2D
• Intel compute (c5/c6i/c7i) → C3
• Intel general/memory (m5/m6i/r5/r6i) → N2
• Burstable (t2/t3/t3a) → E2
• Storage-optimized → C3 + Hyperdisk or Z3 (dense)
• SAP-HANA-class memory (x1/x1e/x2idn/u-*) → M4 megamem
• GPU training (p3/p4/p5) → A2/A3
• GPU inference (g4/g5/g6) → G2

Common pitfall: Mapping all "memory-optimized" sources to M4. M4 is megamem-class (TB-scale RAM) — reserve it for SAP-HANA workloads only. Generic memory-heavy goes to N2 or N2D.

Regional caveats: Some target families aren't GA in every region. Document fallbacks in Assumptions (e.g., "C4A not yet GA in asia-south2; route Hyderabad C4A workloads to asia-south1 per customer override").

Full per-pair translation tables and per-OS premium rates: references/04-compute-architectural-fit.md

Phase 5 — Service translations (DB / Storage / Network / Ops / Security / Other)

One sheet per category. Each row = one distinct workload. Each cost cell = formula.

Two recurring traps that cost prior engagements days:

• Networking egress tiers. Internal/cross-zone/inter-region/internet-egress-intra-continent/internet-egress-inter-continent are all different rates. Route by source unit_price tier, not by description text.
• Database engine routing. Capture the engine column directly from the bill (Aurora-PG, RDS-SQL-Server-LI-Ent). Never regex on the SKU description. Hyphens-vs-spaces will silently route SQL-Server rows into MySQL pricing.

Per-category guidance:

• Database: references/05a-database.md
• Storage: references/05b-storage.md
• Networking: references/05c-networking.md
• Operations: references/05d-operations.md
• Security: references/05e-security.md
• Other / Marketplace: references/05f-other.md

Phase 6 — Unmapped + optimization opportunities

Unmapped sheet — informational only, NOT included in target totals. Lists source services with no target equivalent, source $/mo, suggested workaround.

Optimization Opportunities sheet — formula-driven, NOT in baseline totals. Each opportunity has:

• Current cost (SUMIFS against the relevant line-item sheet)
• Optimized cost (formula expressing the optimization, e.g., *0.5 for 50% of egress moved to a cheaper tier)
• Savings $/mo + % + annual
• Effort estimate, trade-offs, status (recommended / customer decision required / etc.)

Common opportunities (cross-platform):

• Re-architect ARM workloads to AMD where ARM ISA isn't strictly required
• Egress tier shift (Premium → Standard or via CDN cache offload)
• Storage tier-down (Standard → Nearline / Coldline / Archive based on access pattern)
• Right-sizing (utilization-driven; needs metrics, not just bill data)
• Free-on-target services (Asset Inventory, Billing Reports, etc.)

Frame opportunities against the appropriate baseline. ARM→AMD savings against On-Demand may shrink 50%+ when measured against 3Y commit. Be explicit which baseline.

Phase 7–8 — Rollups

Source rollups: by-service, by-region, by-category. Each must sum to the source bill total exactly.

Target rollups: same dimensions × (OD / 1Y / 3Y / etc.) scenarios. Cross-sheet drift check: target by-service total = target by-region total = target by-category total, for each scenario. If they drift by more than $0.01, find and fix before continuing.

Phase 9 — Comparison + executive summary

Comparison sheet — the customer-facing one-page table:

Timeframe	Source	Target OD	Target 1Y	Target 3Y
Monthly	$X	$Y_OD	$Y_1Y	$Y_3Y
Annual (×12)	=Mo×12	…	…	…
3-Year TCO (×36)	=Mo×36	…	…	…

Plus:

• A savings table (NEGATIVE = target saves; POSITIVE = target costs more)
• A category-level breakdown (Compute / DB / Storage / Network / Ops / Security / Other) referencing Phase 7–8 rollups

Cover sheet — headline numbers reference the Comparison sheet. Customer name, period, scope.

Overview sheet — 1-page executive narrative + recommendation. Don't bury prose paragraphs deep in line-item sheets.

Phase 10 — Reconciliation audit

A dedicated sheet with N formula-driven checks, each returning OK / FAIL:

• Source line-items sum = bill total
• Source by-service total = bill total
• Source by-region total = bill total
• Source by-category total = bill total
• Target OD / 1Y / 3Y monotonicity per category (3Y < 1Y < OD where commitments apply)
• Sign convention check (3Y delta is negative = savings)
• Cross-sheet drift checks (Service / Region / Category rollups all match)
• Comparison sheet headline = corresponding rollup totals
• Optimization sheet TOTAL = sum of opportunity rows
• Named ranges resolve (no #NAME? in the workbook)
• No #REF! / #VALUE! / #N/A / #DIV/0! cells anywhere

If any check fails, do not ship. Trace upstream.

See: references/10-reconciliation.md

Output formats

The methodology is format-agnostic. Common outputs and tooling:

• Excel workbook — openpyxl (Python) preserves formulas for auditability. Use named ranges, conditional formatting, freeze panes. Verify by converting via headless LibreOffice and re-loading with data_only=True.
• Google Sheets — gspread or Apps Script; same formula model.
• Presentation deck — Comparison + Cover + Overview as inputs; the rollups are appendix material.
• Markdown report — works for executive summary; less audit-friendly than the workbook.

For workbook generation in Python: references/workbook-generation.md

Common failure modes (and how to catch them early)

Symptom	Likely cause	Fix
Reconciliation off by 5–15%	Filtered out negative charges but kept refunds, or misclassified usage type	Re-filter on charge_type; audit the dropped rows
One category ~80% cheaper than expected	Catalog returned a Confidential Mode / HA SKU variant	Add SKU exclusions to the catalog lookup; spot-check against pricing page
Sign convention inverted on one sheet	=B-A on one sheet, =A-B on another	Standardize, audit every delta cell, add a recon check
CUD discount not applying	sku_group string mismatch (vCPU vs vCPU_standard)	Print the unique sku_group values on both sides; case-fix
Networking 4× more than expected	Lumped all egress tiers into "internet egress"	Split DT lines by source unit_price; route per tier
Compute family mapped wrong (M4 spam)	Generic "memory-optimized → M4" rule applied to non-SAP-HANA	Restrict M4 to x1/x2idn/u-*; everything else → N2 / N2D
Customer says "but we have a 30% EDP"	Modelled list rates without disclosing it	Re-run with post-discount as a separate scenario column

Process discipline

• Stop and report between phases. Don't run all 10 phases and hand the customer the result. Verify each phase's reconciliation before moving on.
• Lock decisions in the Assumptions sheet, not in code. Region preferences, family overrides, compute routing exceptions — every parameter that might change should be a named range with a citation column noting who decided it and when.
• Hide back-office sheets before shipping. _Source_Detail, _Target_Rates, _Commitment_Rates, _Region_Map should be visible during build (auditable) but can be hidden in the final deliverable based on customer preference. The Assumptions sheet is referenced via named ranges so can stay hidden permanently.
• No internal-build language in customer-facing sheets. Strip "Phase X", "TODO", "CLEANUP-A pending", flag IDs, internal jira references. Anything that wouldn't be defensible in a customer review.
• Caveats belong in the engagement letter or recommendation, not in cells. Don't pepper the workbook with italicized "Caveat: this number overstates by ~$60K" footnotes. Either fix the number or document the methodology limit upfront.

Slash-command shortcut

If you've configured this skill at the user level, invoke with the path to the bill:

/tco path/to/source-bill.csv

Without a path, the skill walks you through Phase 0 scoping interactively before any work begins.