From wohlig-tco
Build a defensible Total Cost of Ownership comparison from a source-platform bill (AWS, Azure, on-prem VMware, or current GCP spend) to a target platform across multiple commitment scenarios. Use when a customer hands over a billing export and wants a customer-ready workbook with reconciled rollups, headline numbers, scenario pricing, and optimization opportunities. Covers engagement scoping, inventory normalization, service mapping, pricing-API integration, regional/architectural-fit translation, commitment discounts, rollups, reconciliation audit, and optimization sheets. Output format-agnostic (Excel, Google Sheets, presentation deck).
How this skill is triggered — by the user, by Claude, or both
Slash command
/wohlig-tco:tcoThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
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.
references/00-engagement-scoping.mdreferences/01-inventory-normalization.mdreferences/02-pricing-rates.mdreferences/03-service-mapping.mdreferences/04-compute-architectural-fit.mdreferences/05a-database.mdreferences/05b-storage.mdreferences/05c-networking.mdreferences/05d-operations.mdreferences/05e-security.mdreferences/05f-other.mdreferences/10-reconciliation.mdreferences/workbook-generation.mdThis 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.
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.
These six rules apply to every TCO regardless of source/target. Violating any of them produces an output that won't survive an auditor.
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.
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.
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.
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.
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.
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.
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.
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:
Useful but optional:
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
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:
_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:
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:
source = "documented" and the page URL.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
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:
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
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):
_Target_Rates)=OD cell (no commitment discount on spot)Family translation rules are platform-pair specific. For AWS→GCP they look roughly like:
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
One sheet per category. Each row = one distinct workload. Each cost cell = formula.
Two recurring traps that cost prior engagements days:
unit_price tier, not by description text.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:
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:
*0.5 for 50% of egress moved to a cheaper tier)Common opportunities (cross-platform):
Frame opportunities against the appropriate baseline. ARM→AMD savings against On-Demand may shrink 50%+ when measured against 3Y commit. Be explicit which baseline.
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.
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:
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.
A dedicated sheet with N formula-driven checks, each returning OK / FAIL:
#NAME? in the workbook)#REF! / #VALUE! / #N/A / #DIV/0! cells anywhereIf any check fails, do not ship. Trace upstream.
See: references/10-reconciliation.md
The methodology is format-agnostic. Common outputs and tooling:
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.gspread or Apps Script; same formula model.For workbook generation in Python: references/workbook-generation.md
| 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 |
_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.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.
Provides UI/UX resources: 50+ styles, color palettes, font pairings, guidelines, charts for web/mobile across React, Next.js, Vue, Svelte, Tailwind, React Native, Flutter. Aids planning, building, reviewing interfaces.
Fetches up-to-date documentation from Context7 for libraries and frameworks like React, Next.js, Prisma. Use for setup questions, API references, and code examples.
npx claudepluginhub samayyy/wohlig-tco-plugin --plugin wohlig-tco