Back to Playbooks

Enterprise ROI Calculator Rescue

Build
~3 days (audit through documentation)

A VP of Sales hands you a broken Excel ROI calculator — 22 tabs, 9,414 formulas, half of them dead. Nobody can maintain it. Reps need it next quarter. You reverse-engineer the business logic, rebuild it in Google Sheets with Apps Script automation, add a guided wizard, validate every calculation, and ship full documentation.

The Slack Message

VP
VP Sales9:14 AM

Our ROI calculator is broken. It's an Excel file with thousands of formulas and nobody can maintain it. Half the numbers are wrong. The reps are asking for it in every deal and we need it working by next quarter. Can you fix this?

The Prompt

The file landed in your inbox. Here is the plan you write after opening it.

VP Sales: "Our ROI calculator is broken. It's an Excel file with thousands of formulas.
Nobody can maintain it. Half the numbers are wrong. We need reps using it next quarter."

The file is a 22-tab Excel workbook with 9,414 formulas — most of them broken.
The business logic is buried inside wrapper functions that stopped working when
the file was converted from .xlsm to .xlsx.

Goal: reverse-engineer the business logic, rebuild it in Google Sheets with
Apps Script, add a guided wizard for reps, validate every calculation, and
ship full documentation — sales guide, admin guide, and quick reference.

Phase 1 — Audit
  - Scan the workbook for every broken formula
  - Extract the inner logic from 1,620 dummy function wrappers
  - Catalog the 189 ISBETWEEN patterns and 262 ampersand artifacts
  - Preserve everything in CSV for audit trail

Phase 2 — Extract core business logic
  - Identify that the 9,414-formula model reduces to one equation:
    Savings = Volume × Rate × AHT × (Agent $/min − Platform $/min) − Fee
  - Map the 6 value drivers, 3 scenarios, and pricing tiers
  - Document the architectural decisions

Phase 3 — Rebuild in Google Sheets
  - Build 10 clean tabs with ~200 formulas (vs 9,414 broken ones)
  - Write Apps Script: main engine, wizard backend, wizard UI
  - Add 4 presets, 3 commercial paths, product selector

Phase 4 — QA and documentation
  - Run 8 validation suites, fix the sheet-name reference bug
  - Generate sales guide, admin guide, quick reference, and technical docs
  - Ship to reps

The insight that changed the project

9,414 formulas sounds unsalvageable. But systematic audit revealed the core business logic was one equation: Savings = Volume × Rate × AHT × (Agent $/min − Platform $/min) − Fee The thousands of formulas were over-engineered 36-month projections with broken wrappers. Extracting the real logic and rebuilding with ~200 clean formulas plus Apps Script was faster and more maintainable than trying to patch the original.

How This Was Built

1

Audit the wreckage

Scan all 22 tabs. Find 9,414 broken formulas. Extract 1,620 dummy function wrappers into CSV with full cell references.

2

Catalog the damage

Identify 189 ISBETWEEN patterns and 262 ampersand artifacts. Build a transformation ruleset for each class of breakage.

3

Extract core business logic

Discover that 9,414 formulas reduce to one core equation plus 6 value drivers, 3 scenarios, and a tiered pricing engine.

4

Architect the rebuild

10 tabs instead of 22. Apps Script instead of nested formulas. Wizard sidebar instead of manual entry. Separate CI and CA modules with shared inputs.

5

Build the Apps Script engine

Write the main calculator engine, wizard backend, and wizard HTML frontend. Implement presets, scenario modeling, and pricing tiers.

6

Push and integrate

Deploy to Google Sheets via Gremlin. Wire up the menu system, product selector, tab visibility, and named ranges.

7

Validate everything

Run 8 QA suites: formula validation, architecture, product selection logic, pricing, ramp calculations, named ranges, formula count, ROI clarity.

8

Ship documentation

Generate sales user guide, admin guide, quick reference card, and full technical documentation. Publish to knowledge base.

After you paste this prompt, Claude Code:

"Opening the Excel workbook. 22 tabs. Let me scan for broken formulas first — anything with __xludf.DUMMYFUNCTION is a dead formula."

Running
$ python audit_excel.py "Business Case Model v3.2.xlsx"
Scanned 22 tabs. Found 9,414 broken formulas. 1,620 use __xludf.DUMMYFUNCTION wrapper. 189 unique ISBETWEEN patterns. 262 ampersand concatenation artifacts.

"The formulas are not random garbage — they are valid logic wrapped in a broken container. The DUMMYFUNCTION wrapper was added when .xlsm was saved as .xlsx and the UDFs stopped resolving. I need to strip the wrapper, fix the ampersand artifacts, and convert ISBETWEEN to AND()."

Building transformation rules for each class of breakage

Rule 1: Strip =IFERROR(__xludf.DUMMYFUNCTION("..."),fallback) wrapper → extract inner logic
Rule 2: Fix X"&"Y artifacts → XY (regex: /([A-Za-z0-9$]+)"&"([A-Za-z0-9$]+)/)
Rule 3: Convert ISBETWEEN(val,lo,hi) → AND(val>=lo,val<=hi)
Running
$ python transform_formulas.py --input audit/dummy_functions_backup.csv --output audit/conversions_full.csv
Transformed 1,620 formulas. All 189 ISBETWEEN patterns converted. All 262 ampersand artifacts resolved. Conversion log: 1,621 rows.
After you paste this prompt, Claude Code:

"Looking at the converted formulas... the 3-year projection tab has 900 formulas that model 36 months of driver roadmaps with ISBETWEEN-gated tier transitions. This is massively over-engineered. The actual business logic is simple."

"The entire CA model reduces to: Savings = Volume × Resolution Rate × AHT × (Agent $/min − Platform $/min) − Platform Fee Three scenarios just vary the resolution rate (40% / 50% / 60%). The CI model has 6 value drivers but each one is a straightforward calculation. I don't need 9,414 formulas. I need ~200."

Designing the rebuild architecture: 10 tabs, Apps Script engine, wizard sidebar

Architecture:
- Quick Calculator (primary input, 8 fields)
- Scenario Comparison (Bear/Base/Bull)
- Executive Summary (PDF-ready)
- Customer Snapshot (copy/paste for emails)
- 6 hidden support tabs (drivers, pricing, advanced, assumptions, glossary, CA containment)
- Product selector: CI Only / CA Only / Both
- 4 presets: Mid-market, Enterprise, High Compliance, Chat-Heavy
- 3 commercial paths: Annual, 30-Day Pilot, Competitive Trial
After you paste this prompt, Claude Code:

Writing the Apps Script engine — main calculator, wizard, and UI

CICalculator.gs: 4,719 lines — menu system, tab setup, formula engine, pricing calculator, scenario runner, preset handler, product selector
Wizard.gs: 444 lines — 5-step wizard backend with preset loading and result computation
wizard.html: sidebar UI with step navigation, input validation, and result cards
Running
$ g-gremlin push SHEET_ID --script gsheets/CICalculator.gs --script gsheets/Wizard.gs --html gsheets/wizard.html
Deployed 3 files to Google Sheets. Menu registered. Wizard accessible via CI Calculator > Launch ROI Wizard.
Running
$ g-gremlin lint SHEET_ID --rules "all" --output lint_report.json
Found 7 #REF! errors in Executive Summary. Root cause: sheet name mismatch — formulas reference "Pricing Engine" but tab is named "Pricing Engine (CI)".
Running
$ g-gremlin lint SHEET_ID --rules "dead-refs" --fix
Fixed 7 references: "Pricing Engine" → "Pricing Engine (CI)" in CICalculator.gs lines 525-561 and 1640.
After you paste this prompt, Claude Code:
Running
$ python qa_validate.py --suite all --output qa-checks/QA-REPORT.md
8/8 suites passed: ✓ Formula validation — no #REF!, #VALUE!, #NAME? errors ✓ Architecture — clean data flow ✓ Product selection — CI/CA/Both modes correct ✓ CA pricing — all 4 commitment bands operative ✓ Ramp calculation — Y1 ramped, Y2+ steady state ✓ Named ranges — all critical outputs labeled ✓ Formula count — 200 formulas (vs 9,414 broken) ✓ ROI clarity — labeled and calculated correctly

Generating documentation suite

4 documents published:
- CI-ROI-Calculator-Documentation.md (660 lines) — full technical reference
- sales-guide.md — 2-minute quick start + wizard walkthrough
- admin-docs.md — maintenance and configuration guide
- CI-ROI-Quick-Reference.md — one-page cheat sheet for reps

Done. 9,414 broken formulas → 200 clean formulas. 22 messy tabs → 10 organized tabs. Zero documentation → 4 complete guides. Wizard, presets, scenarios, and pricing engine all operational.

Output Artifacts

Google Sheet: ROI Calculator

  • Quick Calculator — 8 core inputs with instant results
  • Wizard sidebar — 5-step guided flow with 4 presets
  • Scenario Comparison — Bear / Base / Bull side-by-side
  • Executive Summary — PDF-ready one-page output
  • Customer Snapshot — Copy/paste block for emails
  • Product selector — CI Only / CA Only / Both

Audit Trail and Documentation

  • Formula audit CSVs — Every broken formula cataloged with cell references
  • Transformation log — Before/after for all 1,620 converted formulas
  • QA report — 8 validation suites, all passed
  • Sales user guide — 2-minute quick start + wizard walkthrough
  • Admin guide — Maintenance and configuration reference
  • Technical docs — 660-line complete system reference

Requirements

The broken Excel workbook

The source file with the formulas that need reverse-engineering.

Python for audit scripts

Used for formula extraction, pattern cataloging, and transformation.

Google Sheets access

Target environment for the rebuild with Apps Script support.

Gremlin CLI

Deploys Apps Script, lints formulas, fixes references, snapshots.

Claude Code or Codex

Architects the rebuild, writes Apps Script, generates documentation.

Domain knowledge from stakeholders

Value driver definitions, pricing tiers, and scenario assumptions.

Results

9,414
Broken formulas eliminated
~200
Clean formulas in rebuild
5,163
Lines of Apps Script
8/8
QA suites passed
22 → 10
Tabs (before → after)
7
Value drivers
4
Documentation pages
4
Presets for reps

From 9,414 formulas to ~200

97.9% reduction in formula complexity. The remaining formulas are clean, documented, and maintainable.

Reps get ROI in 2 minutes

Open wizard, pick a preset, adjust numbers, view results. No formula knowledge needed. Executive Summary is PDF-ready.

Based on a real enterprise ROI calculator rescue. Client details redacted; architecture, methodology, and metrics are the reusable pattern.

Related

Claude Code for RevOps

How AI coding agents work in RevOps: planner plus governed executor.

Read guide

Spreadsheet Rescue

The general pattern for diagnosing, repairing, and documenting broken sheets.

See use case

Broken Sheet Playbook

The lighter version: inherit a sheet with #REF! errors and no docs.

See playbook

B2B ROI Calculator Guide

The architecture guide for rebuilding ROI tools with explicit drivers, Apps Script, scenarios, and QA.

Read guide

Got a broken calculator that reps are waiting on?

The pattern is audit, extract, rebuild, validate, document. Gremlin and Claude Code handle the heavy lifting.

View Quickstart GuideMore Playbooks