TBM Budget Variance Analyst

Session Setup (Cowork / Cloud Environments)

If running in Cowork or any environment without local filesystem access, upload these files to the session before starting:

• references/Variance_Analysis_Framework.md — variance driver codes, decision tree, materiality guidelines
• references/variance-analysis-Anthropic.md — general variance methodology and formulas
• scripts/generate_excel.py — 5-tab Excel workbook generator (primary deliverable)
• scripts/generate_dashboard.py — interactive HTML dashboard generator (optional)
• scripts/requirements.txt — Python dependencies (pandas + openpyxl)

If these files are not available, Claude will generate equivalent logic, but outputs may vary between sessions. The shipped scripts and reference files are the authoritative versions — always prefer them over generated alternatives.

Before You Begin

Read both reference files before processing any data:

1. references/Variance_Analysis_Framework.md — TBM-specific variance driver codes (VD.1–VD.11), decision tree for driver attribution, materiality guidelines by cost pool, and 8 recurring TBM variance patterns. Read this first — it is the primary reference.

2. references/variance-analysis-Anthropic.md — General variance methodology including price/volume decomposition formulas, headcount/compensation decomposition, materiality threshold frameworks, narrative quality standards, and waterfall chart format. This is an external reference that may be updated independently. Use it for methodology and formulas; use the TBM-specific framework for driver codes and cost pool patterns.

Finding these files: The references/ and scripts/ folders are in the user's local working directory — the folder where the user is running this analysis. Do not look for these files in the skill's source or installation directory. Check these locations in order and use the first match found:

1. {workspace_dir}/references/ and {workspace_dir}/scripts/ — the user's current working directory
2. {skill_base_dir}/references/ and {skill_base_dir}/scripts/ — the skill pack's own installation directory
3. If neither location yields the files, ask the user to confirm where their reference files are located or to upload them directly into the session.

These references are your source of truth. When in doubt about a driver attribution, defer to the definitions in these files rather than general knowledge.

Step 1: Understand and Validate Input

Expected Input

This skill expects A1 output with a budget column added. The input should be a table (CSV, XLSX, or pasted data) with these columns:

Required columns:

Helpful but optional columns:

Data Alignment Prerequisite

Before validating columns, confirm that budget and actuals data share a common GL structure. This skill assumes both datasets use the same GL account codes and cost center codes — if they don't, variance calculations will produce false driver attributions.

Signs of GL misalignment that require A0 first:

• High counts of VD.4 (New-Item) or VD.5 (Cancelled) items that seem implausible as genuine business events
• Budget and actuals from different source systems (e.g., budget from Adaptive/Anaplan, actuals from SAP/Oracle ERP)
• COA changes between the budget period and the actuals period (renumbers, consolidations, splits)
• Actuals at GL + cost center level, budget at department summary level

If any of these signs are present, recommend running the TBM Data Reconciliation Mapper (A0) before this skill. A0 produces a reconciled dataset with aligned GL structures and a unified Budget_Amount + Actual_Amount column — that output is the ideal input for this skill. When data has been pre-processed by A0, note it in the output header.

Validation Checks

Before proceeding, validate the input:

1. Budget and Actual columns exist — if either is missing, ask the user to provide both
2. Cost Pool column exists — if missing, recommend running the TBM Cost Pool Mapper (A1) first
3. Check for blanks — flag any rows with missing Cost_Pool or missing amounts
4. Identify budget-only rows — items with a budget amount but zero or no actual (candidates for VD.5 Cancelled)
5. Identify actual-only rows — items with actual spend but zero or no budget (candidates for VD.4 New-Item)
6. Verify totals — sum budget and actual columns; report the top-line variance immediately so the user has context

Optional: A2 Tower Data

If the user provides A2 (Resource Tower Analyzer) output for both budget and actual periods, you can produce an additional tower-level variance tier (Tier 1b). This requires tower assignments for each line item in both the budget and actual datasets. If only one period has tower data, skip the tower tier and note why.

Step 2: Establish Materiality Threshold

Ask the user for their materiality threshold. Provide guidance from the framework:

"What materiality threshold should I use? This determines which variances get flagged for driver analysis. Common defaults:

• 5% is typical for most IT organizations
• 3% for highly predictable pools (Telecom, Data Center Facilities)
• 10–15% for consumption-based pools (Cloud Services, Misc Costs)

Some organizations use a dual test — e.g., flag if variance > 5% AND > $50,000. Would you like to use a single threshold or a dual test?"

If the user does not specify, use 5% as the default and state this in the output header.

Step 3: Calculate Variances

For every line item:

$ Variance = Actual_Amount - Budget_Amount
% Variance = (Actual_Amount - Budget_Amount) / Budget_Amount × 100

Special cases:

• New items (Budget = $0): $ Variance = Actual_Amount. % Variance = "N/A (new)" — do not divide by zero. Assign VD.4.
• Cancelled items (Actual = $0 or near-zero): $ Variance = -Budget_Amount. % Variance = -100%. Assign VD.5.

Aggregate to Sub-Pool and Cost Pool levels:

1. Sum line-item budgets and actuals within each sub-pool → compute sub-pool variance
2. Sum sub-pool budgets and actuals within each cost pool → compute pool variance
3. Sum pool budgets and actuals → compute total variance

Validation: The sum of all line-item variances must equal the total variance. If it does not, there is a calculation error — stop and fix before proceeding.

Step 4: Attribute Variance Drivers

For every line item where the variance exceeds the materiality threshold, apply the decision tree from Variance_Analysis_Framework.md to assign a primary VD code.

Key Attribution Logic by Cost Pool

Staffing:

• Check headcount change → VD.1 (Headcount)
• Check rate/salary change → VD.2 (Rate-Price)
• Both changed? Decompose: (ΔHC × Budget Rate) + (ΔRate × Actual HC). Assign dominant factor as primary; note secondary.
• Contractor-to-FTE swap → VD.8 (Mix-Shift) at pool level; look for offsetting Internal Labor over / Staff Aug under.

Cloud Services:

• Check usage volume change → VD.3 (Volume-Qty)
• Check unit pricing change → VD.2 (Rate-Price)
• Check service mix (e.g., Azure down, AWS up) → VD.8 (Mix-Shift)
• New cloud services not in budget → VD.4 (New-Item)
• Reserved instance coverage change → may be VD.2 (unit cost) or VD.8 (commitment mix)

Hardware:

• Purchase timing shifted → VD.6 (Timing). Distinguish from VD.5: will the spend happen next period?
• New purchase not in budget → VD.4 (New-Item)
• Price change on same hardware → VD.2 (Rate-Price)

Software & SaaS:

• License/seat count change → VD.3 (Volume-Qty)
• Price increase at renewal → VD.2 (Rate-Price)
• New SaaS adoption → VD.4 (New-Item)
• Annual true-up spike → VD.3; flag if consistently over-budget

Outside Services:

• SOW amendment / scope expansion → VD.7 (Scope-Change)
• T&M overruns (more hours, same rate) → VD.3 (Volume-Qty)
• Rate card change → VD.2 (Rate-Price)
• Cancelled project → VD.5 (Cancelled)

Data Center Facilities / Telecom:

• Contract rate changed → VD.2 (Rate-Price)
• Usage/capacity changed → VD.3 (Volume-Qty)
• These pools are highly predictable — any material variance warrants investigation

Cross Charges:

• Allocation method changed → VD.10 (Allocation)
• Flag that cross-charge variances are usually "structural" (accounting movement) not "real" (economic change)

Offsetting Variance Detection

Actively look for the 8 TBM-specific patterns in Variance_Analysis_Framework.md, especially:

• Pattern 8 (Contractor-to-FTE): Internal Labor over + Staff Aug under within Staffing pool
• Pattern 1 (Cloud migration): One cloud provider under + another over within Cloud Services pool
• Pattern 5 (Managed services): Outside Services over + corresponding Internal Labor under (outsourcing shift)

When offsetting variances are found, note them explicitly and report the net effect at the pool level.

Step 5: Assign Confidence Levels

Every material variance gets a confidence level. In this skill, confidence reflects driver attribution accuracy — how certain you are about why the variance occurred.

High Confidence

The driver is unambiguous. The budget assumption is known, the actual is clear, and a single verifiable factor explains the variance.

• Example: "GL 7810 GPU Cluster — Budget $0, Actual $350K. VD.4 (New-Item). High confidence: no budget existed; this is an unplanned CapEx purchase."

Medium Confidence

The most defensible interpretation, but relies on an assumption. For every Medium item, state:

1. The driver code assigned and why
2. The assumption you are making
3. What data would confirm or refute the assumption
4. The specific action to take (who to ask, what document to pull)

• Example: "GL 7100 AWS Cloud Services — VD.3 (Volume-Qty). Medium confidence: variance attributed to consumption growth, but workload-level data is not available. Verify with AWS Cost Explorer. If Azure-to-AWS migration contributed, reclassify portion as VD.8."

Low Confidence

Insufficient information. State:

1. All plausible drivers (with VD codes)
2. Why none can be confirmed
3. Exactly what information is needed to resolve

• Example: "GL 7820 Data Labeling — VD.3 or VD.7. Low confidence: unclear whether volume increased on the same SOW or scope was formally expanded. Need: SOW/change order from Scale AI."

Step 6: Produce the Output

Header Block

Always start the output with a header block:

Budget total: $[total]
Actual total: $[total]
Net variance: [+/-]$[amount] ([%] [over/under] budget)
Materiality threshold applied: [%] (user-defined / default)
Data source: [period description] | Cost Pool classification complete (A1 output)
TBM taxonomy version: v5.0.1
Variance driver codes: All line-item rows cite the source rule from Variance_Analysis_Framework.md

Tier 1: Variance Summary Table (by Cost Pool)

9 pools sorted by absolute dollar variance (largest first). Columns:

| Cost Pool | Budget | Actual | $ Variance | % Variance | Status |

Status flags:

• Within — variance within materiality threshold
• Over — unfavorable variance exceeds threshold (with warning indicator)
• Under — favorable variance exceeds threshold (with warning indicator)

Tier 1b (Optional): Tower Variance Summary

Only produce this tier if the user provides A2 tower data for both budget and actual periods. Same format as Tier 1 but with 12 towers × 4 domains instead of 9 cost pools.

Tier 2: Sub-Pool Variance Detail

All sub-pools within each cost pool. Columns:

| Cost Pool | Sub-Pool | Budget | Actual | $ Variance | % Variance | Primary Driver |

Primary Driver = the dominant VD code for that sub-pool, or "Within" if below threshold.

Tier 3: Line-Item Variance Detail

One row per GL line item. Columns:

| GL | Description | Cost Pool | Budget | Actual | $ Variance | % Variance | Driver Code | Confidence | Notes |

• Within-threshold items get "—" for Driver Code and Confidence
• Every material item gets exactly one VD code (dominant driver)
• Notes column captures secondary drivers, assumptions, and cross-references to related items

Include a math reconciliation note: "Sum of line-item variances = $[X] = Tier 1 total variance. Verified."

Tier 4: Review Items

All Medium and Low confidence items. For each:

• Item description with GL account, cost pool, sub-pool
• Budget, actual, and variance amounts
• Driver code applied
• Confidence level and why
• Assumption made
• What to verify (specific data source, person, or document)
• Impact if wrong (dollar amount that would shift between driver categories)

Tier 5: Narrative Summary

5–7 sentences, CFO/CIO-ready. Must cover:

1. Net position (total over/under budget and percentage)
2. Top 2–3 unfavorable variances with their drivers
3. Top 1–2 favorable variances
4. Any emergent themes (e.g., unbudgeted AI/ML spend, cloud migration signals)
5. Offsetting variance patterns detected
6. 2–3 recommended actions

Use the narrative templates from Variance_Analysis_Framework.md Section 7. Follow the narrative quality checklist from variance-analysis-Anthropic.md — be specific, quantified, causal, forward-looking, and actionable. Avoid the anti-patterns (circular explanations, vague "timing" without specifics, "various small items" for material variances).

Step 7: Validate and Summarize

Before delivering the output, run these validation checks:

Math Reconciliation

• Sum of line-item variances = sum of sub-pool variances = sum of pool variances = total variance
• Check for sign errors (favorable variances should be negative for cost items)
• Verify no double-counting in offsetting variance pairs

Distribution Sanity Checks

• Total variance within +/- 5% → normal operating range
• Single pool > +/- 15% → flag for executive attention
• More than 20% of items coded VD.11 (Unknown) → dataset needs enrichment; recommend additional data sources

Benchmark Comparison

Compare pool-level variance percentages against the typical ranges in Variance_Analysis_Framework.md Section 4. Flag any pool where the variance significantly exceeds its expected range (e.g., Data Center Facilities at +12% when the typical range is 3–5%).

Step 8: Generate Interactive Visualization (Optional)

After producing the 5-tier analysis, offer to generate an interactive variance dashboard as a self-contained HTML file. This dashboard runs in any browser (including Claude Cowork) and provides:

• KPI cards — Total Budget, Total Actual, Net Variance, Variance %
• Budget vs. Actual bar chart — grouped or variance view, by cost pool (clickable to drill into sub-pools)
• Variance waterfall — aggregated by driver code, showing how budget flows to actual through each driver
• Variance driver distribution — doughnut chart showing absolute dollar variance by VD code
• Material items table — filterable by Over / Under / Review (Medium confidence), sortable, with driver badges and confidence indicators
• Expandable cost pool detail — click any pool to reveal sub-pool breakdown with status flags

How to Generate

1. Save the Tier 3 line-item detail as a CSV with these columns (at minimum): GL_Account, Description, Cost_Pool, Sub_Pool, Budget_Amount, Actual_Amount, Driver_Code, Confidence, Notes

2. Run the dashboard generator:

   pip install -r scripts/requirements.txt   # pandas only — first time
   python scripts/generate_dashboard.py <tier3_output.csv> --threshold 5 --output variance_dashboard.html
   

3. Open variance_dashboard.html in a browser or upload to Claude Cowork.

Interactive Features

• Threshold slider — adjust the materiality threshold (1–20%) and watch status flags, material items, and chart colors update in real time
• Chart toggle — switch the bar chart between "Grouped" (budget vs. actual) and "Variance" (net variance per pool)
• Filter tabs — filter material items by All / Over / Under / Review (Medium confidence items)
• Pool drill-down — click any cost pool row or bar chart segment to expand sub-pool detail
• Hover tooltips — all charts show dollar values on hover

Architecture

The scripts/generate_dashboard.py script is fully self-contained — the HTML template is embedded inside the Python file. No external template files are needed. The script reads the Tier 3 CSV, builds a DASHBOARD_DATA JSON object, injects it into the template, and writes a single standalone HTML file. The output uses Tailwind CSS (CDN) and Chart.js — no build step or dependencies beyond a browser.

Output Format Guidelines

Default Output: Excel Workbook

The primary deliverable is a formatted Excel workbook (.xlsx) generated by scripts/generate_excel.py. Finance users expect Excel — it is their native working format for review, annotation, and forwarding to stakeholders.

Workflow:

1. Save the Tier 3 line-item detail as a CSV with these columns (at minimum): GL_Account, Description, Cost_Pool, Sub_Pool, Budget_Amount, Actual_Amount, Driver_Code, Confidence, Notes Include optional columns (Vendor, CapEx_OpEx) when available — they enrich the workbook.

2. Install dependencies and run the Excel generator:

   pip install -r scripts/requirements.txt   # pandas + openpyxl — first time only
   python scripts/generate_excel.py <tier3_output.csv> --threshold 5 --output variance_analysis.xlsx
   

   Cowork environment note: Always run pip install -r scripts/requirements.txt at the start of the session before invoking the script — Cowork sessions do not persist installed packages across runs. If pip is unavailable, try python -m pip install -r scripts/requirements.txt. The script validates its own imports and prints a clear error message if either pandas or openpyxl is missing.

3. The workbook contains 5 tabs:

   • Summary — Header block (totals, threshold, taxonomy version) + Tier 1 cost pool variance table with status flags
   • Sub-Pool Detail — Tier 2 sub-pool variance grouped by cost pool with primary driver codes
   • Line-Item Detail — Tier 3 one row per GL item, color-coded by materiality (red = over, green = under), with variance driver codes, confidence levels, and notes
   • Review Items — Tier 4 Medium and Low confidence items sorted by absolute dollar impact, highlighted for attention
   • Narrative — Tier 5 metadata, confidence distribution, driver code legend, and a placeholder for the narrative summary

Workbook formatting:

• Currency columns use #,##0 format; variance columns show +/- signs
• Percentage columns use 0.0% with signed format
• Header rows are frozen for scrolling
• Status cells are color-filled (red = over threshold, green = under threshold, gray = within)
• Confidence cells are highlighted (yellow = Medium, red = Low)
• Tab colors indicate tier hierarchy

Conversation Output

In addition to the Excel workbook, always produce these in the conversation:

• Tier 1 summary table — so the user sees the top-line picture immediately
• Tier 5 narrative — the CFO/CIO-ready narrative (also paste into the Narrative tab of the workbook)
• Review Items count — number of Medium and Low confidence items, with a note to check the Review Items tab

For inputs with fewer than 20 items, you may also show Tier 2 and Tier 3 as markdown in the conversation for convenience, but the Excel workbook remains the primary deliverable.

Interactive Dashboard (Optional)

After delivering the Excel workbook, offer the interactive HTML dashboard as an additional visualization. If the user wants it, run scripts/generate_dashboard.py on the same Tier 3 CSV.

What This Skill Does NOT Do

• It does not reconcile GL structures across budget and actuals source systems — that is the TBM Data Reconciliation Mapper (A0). If budget and actuals use different GL account codes, cost center codes, or levels of granularity, run A0 first. Skipping A0 when GL structures are misaligned causes this skill to produce false VD.4 (New-Item) and VD.5 (Cancelled) codes for items that are actually GL key mismatches, not genuine business events.
• It does not classify items into cost pools — that is the TBM Cost Pool Mapper skill (A1). Run A1 first to get cost pool assignments before running this skill.
• It does not perform tower-level allocation — that is the Resource Tower Analyzer skill (A2). Tower-level variance requires running A2 on both budget and actual datasets, then providing both to this skill for the optional Tier 1b output.
• It does not build chargeback or allocation models — that is the TBM Chargeback & Showback Builder skill (B1).
• It does not generate executive dashboards or reports beyond the interactive variance dashboard (Step 8) — full executive reporting is the TBM Executive Report Generator skill (C1).
• It does not perform cloud cost optimization or FinOps analysis — it identifies cloud variances and flags them, but detailed cloud cost optimization is out of scope.
• It does not produce budget forecasts or headcount plans — it analyzes historical budget-vs-actual performance, not future projections.

If the user asks for any of these activities, complete the variance analysis first, then direct them to the appropriate skill.

Practitioner Notes

Variance Sign Convention

Positive variance = over budget = unfavorable for cost items. This is the standard IT cost management convention. Always state the convention explicitly in the output header so readers are not confused.

CapEx Timing

Hardware CapEx frequently shifts quarters. VD.6 (Timing) is common and expected for hardware. The critical question is whether the spend is truly deferred (will appear next period) or cancelled (will not occur). If the item does not appear in the next period's forecast, consider reclassifying as VD.5.

Cloud Variance Tolerance

Consumption-based cloud spend inherently has higher variance. A 10–15% variance on Cloud Services is normal operating range. Only flag cloud variance as a problem if it exceeds 15% or shows a trend of growing over-spend. Reference FinOps commitment coverage metrics when cloud variance is material.

Offsetting Variances

Always look for pairs before flagging individual items. Azure under + AWS over = migration signal, not a problem. Staffing Internal Labor over + Staff Aug under = workforce mix shift. Report the net effect at the pool level and explain the underlying movement.

YTD vs. Full-Year

This skill analyzes point-in-time budget vs. actuals (typically annual or YTD). Full-year forecast variance is a separate workflow that incorporates run-rate projections and known future changes. Do not conflate the two.

Structural vs. Real Variance

If cost pool assignments changed since the budget was built (e.g., items reclassified from Outside Services to Software & SaaS under v5.0.1 rules), some variances are "structural" (reclassification, VD.10) rather than "real" (economic change). Flag these explicitly — they distort pool-level comparisons unless the budget is recast.

Running in Cowork

This skill is designed to run in Claude Cowork. Key considerations:

• Package installation: Run pip install -r scripts/requirements.txt at the start of every Cowork session. Installed packages do not persist across sessions. The script will exit with a clear error if dependencies are missing — do not attempt to generate Excel formatting inline as a fallback.
• File output: Write the Excel workbook and any intermediate CSVs to the workspace folder. Cowork makes workspace files available for user download automatically.
• Token budget: The Excel workbook offloads all 5 tiers to Python, keeping the LLM output to ~2K tokens (Tier 1 table + narrative). This avoids the 32K output token limit that affects large datasets. Do not reproduce the full Tier 3 line-item detail as markdown in the conversation — always point the user to the workbook.
• Script location: The scripts/generate_excel.py and scripts/generate_dashboard.py files ship with the skill pack. They are pre-built, deterministic CLI tools — do not regenerate or modify them during a run.

Connection to A1 and A2

This skill sits downstream of A1 and optionally uses A2 output. The cost pool and sub-pool assignments in the input should already be validated through A1. If the user reports that many items seem miscategorized, recommend re-running A1 before completing the variance analysis — bad classifications produce misleading variance attributions.