tutorialsmartechscenario-planning

Step-by-Step: Build a ‘Sprint vs Marathon’ Scenario Planner with Goal Seek and What-If

UUnknown

2026-03-06

10 min read

Build a Sprint vs Marathon planner with Goal Seek and What-If in Excel & Google Sheets — quantify staffing, time, and cost tradeoffs fast.

Hook: Stop guessing how many heads you need — model sprint vs marathon tradeoffs in minutes

Wasting weeks building spreadsheets from scratch, then second-guessing whether a fast rollout needs 7 people or 10? If your martech projects keep slipping because resource trade-offs are unclear, you need a repeatable scenario planner that answers: "What if we accelerate? What if we stretch?" This guide shows how to build a Sprint vs Marathon Scenario Planner using Excel's What-If tools and Goal Seek — plus Google Sheets equivalents — so you can quantify trade-offs, optimize staffing, and present clear recommendations to stakeholders.

Why Sprint vs Marathon modeling matters in 2026

In late 2025 and into 2026, two trends changed how teams decide rollout speed:

Martech consolidation and cost pressure: Platforms are being rationalized, so teams must prove time-to-value before approving new integrations.
AI-assisted execution and automation: Copilot-style features in Excel and Google Sheets speed model-building but also raise expectations for faster planning and scenario delivery.

That means business buyers and ops leaders need models that answer high-stakes questions fast: Can we hit the quarter with a quick MVP? What resource level keeps quality tolerable? Which option gives the best ROI? The tools covered here turn those questions into repeatable, defensible answers.

The outcome: What this planner gives you

Quantified time, cost, and quality for sprint vs marathon rollouts
Scenario sweeps across team sizes, budgets, or feature scope
Optimized resource suggestions using Goal Seek and Solver
Reproducible dashboards (pivot tables, charts) to present to stakeholders

Quick model overview: Inputs, outputs, and formulas

Before building, define the core elements. Keep inputs separated in an Assumptions block so What-If tools can run cleanly.

Key inputs (examples)

Workload (Story Points or Hours) — e.g., 1,200 hours
Individual velocity (hours/week or points/week)
Team size (FTEs)
Ramp-up weeks per new hire
Quality factor (defect rework % by speed)
Cost per FTE/week
Target deadline (weeks) or target budget ($)

Core outputs

Expected completion time (weeks)
Total cost to delivery
Estimated rework hours and impact on time
Throughput per week (adjusted for overhead)

Step-by-step: Build the planner (Excel first, then Google Sheets equivalents)

Step 1 — Lay out the Assumptions table

Create one sheet named Assumptions. Use named ranges for clarity (Excel: Formulas > Define Name; Sheets: Data > Named ranges).

Example cells:

A2: Workload_hours = 1200
A3: Base_velocity_per_person_per_week = 30
A4: Team_size = 5
A5: Ramp_up_weeks = 2
A6: Quality_penalty_pct_per_extra_speed = 0.10 (10% more defects if delivery speed > baseline)
A7: Cost_per_FTE_per_week = 3000
A8: Target_weeks = 8

Step 2 — Baseline calculations

On a second sheet Model, compute throughput and time. Example formulas (Excel/Sheets):

Throughput_per_week = Team_size * Base_velocity_per_person_per_week * (1 - Communication_overhead)
Communication_overhead (basic heuristic) = MAX(0, (Team_size - 5) * 0.05) — increases overhead when teams grow beyond 5
Estimated_weeks = (Workload_hours / Throughput_per_week) + Ramp_up_weeks + Rework_weeks

Concrete Excel formula examples assuming named ranges:

Cell B2 (CommOverhead) = =MAX(0,(Team_size-5)*0.05)
Cell B3 (Throughput) = =Team_size*Base_velocity_per_person_per_week*(1-B2)
Cell B4 (Base_weeks) = =Workload_hours/B3
Cell B5 (Rework_hours) = =Workload_hours * Quality_penalty_pct_per_extra_speed * IF(B4<Target_weeks,1.2,1) — simple heuristic to increase rework when sprinting
Cell B6 (Estimated_weeks) = =B4 + Ramp_up_weeks + (B5/ B3)
Cell B7 (Total_cost) = =Team_size * Cost_per_FTE_per_week * B6

Step 3 — Use What-If Analysis: Data Tables for sweeps (Excel)

One-variable table: Evaluate Estimated_weeks across team sizes 2..12.

Create a vertical list of Team_size candidates in a new sheet (e.g., column A: 2,3,4...12).
In column B1, reference the Estimated_weeks cell (e.g., =Model!B6).
Select the area and go to Data > What-If Analysis > Data Table.
For Column input cell, point to the Team_size cell in Assumptions (or the named range).

Excel will fill the table with estimated weeks for each team size. Repeat for Total_cost (new reference) or create a two-variable table with team size vs target weeks.

Google Sheets equivalent for sweeps

Google Sheets doesn’t have the classic Data Table UI. Use array formulas or Apps Script:

Option A — Array formula grid: build a grid where each cell points to a formula that references the input candidate (team size cell in that row). Fill down using =ARRAYFORMULA() combined with MAP() in 2026-enabled Sheets (MAP is widely available).
Option B — Apps Script sweep: programmatically change the Team_size named range, recalculate, and write results to a sheet. This is closer to Excel Data Table behavior.

Step 4 — Use Goal Seek to solve for required resources

Goal Seek is the simplest optimizer: set an output to a target by changing one input.

Go to Data > What-If Analysis > Goal Seek.
Set cell: Model!B6 (Estimated_weeks).
To value: enter your target (e.g., 8).
By changing cell: Assumptions!Team_size.
Run. Excel will find the team size (may be fractional — round up) needed to meet target weeks.

Google Sheets — Goal Seek via Apps Script

Google Sheets lacks a built-in Goal Seek menu in some deployments, but you can add it via Apps Script. Paste the script below into Extensions > Apps Script and run the function:

function goalSeek(targetRangeA1, changingRangeA1, targetValue, tolerance) {
  tolerance = tolerance || 0.01;
  var ss = SpreadsheetApp.getActive();
  var targetRange = ss.getRange(targetRangeA1);
  var changingRange = ss.getRange(changingRangeA1);
  var low = 1; var high = 100; // sensible bounds for team size
  for (var i=0;i<40;i++){
    var mid = (low+high)/2;
    changingRange.setValue(mid);
    SpreadsheetApp.flush();
    var val = targetRange.getValue();
    if (Math.abs(val - targetValue) <= tolerance) return mid;
    if (val > targetValue) { low = mid; } else { high = mid; }
  }
  return changingRange.getValue();
}

Call it with e.g., goalSeek('Model!B6','Assumptions!B4',8,0.01). The script uses a simple binary search loop to find a changing value that makes the target close to your goal.

Step 5 — Scenario Manager (Excel) and named-scenarios in Sheets

Excel's Scenario Manager (Data > What-If Analysis > Scenario Manager) stores named input sets (e.g., Sprint-MVP, Marathon-Full). Create scenarios with different Team_size, Cost_per_FTE, and Quality_penalty values and show a summary report comparing Estimated_weeks and Total_cost.

In Google Sheets, mimic scenarios by creating separate small sheets named Sprint, Marathon, etc., or by storing scenario rows in a table and using LOOKUP to choose the active scenario via a selector cell (Data > Data validation drop-down).

Step 6 — Optimization with Solver

If you need to optimize multiple inputs (team size, contractor mix, overtime) to minimize cost while meeting deadline and quality constraints, use Solver.

Excel: Install Solver Add-in (File > Options > Add-ins). Then Data > Solver: set Objective (Total_cost) to Min by changing variables (Team_size, Overtime_pct) subject to constraints (Estimated_weeks <= Target_weeks, Quality_penalty <= 0.15).
Google Sheets: Use the Solver add-on or the OpenSolver web service. Alternatively, use linear programming libraries through Apps Script for small problems.

Step 7 — Add sensitivity analysis and Monte Carlo (advanced)

For risk-aware planning, add probabilistic inputs. Use Excel's RAND()/NORM.INV() or Google Sheets' RAND() and the new NORM.INV alternatives. Run 1,000 simulations via VBA (Excel) or Apps Script (Sheets), capturing distributions for Estimated_weeks and Total_cost.

Key takeaway: present percentiles — P50, P75, P90 — not just expected values. Stakeholders prefer “90% confidence we deliver in X weeks” over single-point estimates.

Step 8 — Present results: dashboard and pivot table tips

Create a sheet Dashboard with:

KPIs: Estimated_weeks, Total_cost, Rework_hours
Scenario selector (drop-down) linked to Scenario table
Charts: Team size vs Weeks (line chart), Cost vs Weeks (scatter), Probability distribution histogram
Pivot table: aggregate scenarios by strategy (Sprint/Marathon) and show averages

Use conditional formatting to flag scenarios that breach target weeks or budget.

Practical example: small martech rollout (numbers you can paste)

Example assumptions (paste into Assumptions): Workload_hours=1200, Base_velocity_per_person_per_week=30, Team_size=4, Ramp_up_weeks=2, Quality_penalty_pct_per_extra_speed=0.12, Cost_per_FTE_per_week=3200, Target_weeks=10.

With those values the model might show:

Throughput_per_week = 4 * 30 * (1 - 0) = 120 hours/week
Base weeks = 1200 / 120 = 10 weeks
Rework_hours (if sprinting) = 1200 * 0.12 = 144 hours (adds 1.2 weeks)
Estimated_weeks = 10 + 2(ramp) + 1.2 = 13.2 weeks
Total_cost = 4 * 3200 * 13.2 = $168,960

Run Goal Seek to find Team_size that makes Estimated_weeks <= 10. Excel returns ~6.5 → round up to 7 FTEs. Compare costs: 7 FTEs reduce rework and weeks but increase weekly cost; present both to leadership as a sprint-with-cost trade-off vs a marathon-with-lower-weekly-cost trade-off.

Automation & integrations (2026 best practices)

Automate scenario updates and integrate with upstream sources:

Connect HR / Resource management via Zapier or Make to pull current bench and contractor rates into Assumptions.
Use Power Query (Excel) or BigQuery connectors (Sheets) to refresh workload data from ticketing systems (Jira) and time tracking.
Embed scenario dashboards into Looker Studio or Power BI for executive sharing. In 2026, generative features let you produce summary insights automatically (e.g., "Sprint saves X weeks but costs Y").

Macros and Apps Script examples to run sweeps

Excel VBA: run team-size sweep and log

Sub SweepTeamSize()
  Dim i As Integer
  Dim wsModel As Worksheet, wsLog As Worksheet
  Set wsModel = ThisWorkbook.Sheets("Model")
  Set wsLog = ThisWorkbook.Sheets("SweepLog")
  wsLog.Range("A2:C100").ClearContents
  For i = 2 To 12
    ThisWorkbook.Sheets("Assumptions").Range("B4").Value = i 'Team_size
    Application.Calculate
    wsLog.Cells(i - 1, 1).Value = i
    wsLog.Cells(i - 1, 2).Value = wsModel.Range("B6").Value 'Estimated_weeks
    wsLog.Cells(i - 1, 3).Value = wsModel.Range("B7").Value 'Total_cost
  Next i
  End Sub

Apps Script: sweep example (Sheets)

function sweepTeamSize() {
  var ss = SpreadsheetApp.getActive();
  var assumptions = ss.getSheetByName('Assumptions');
  var model = ss.getSheetByName('Model');
  var log = ss.getSheetByName('SweepLog');
  log.getRange('A2:C100').clearContent();
  var row = 2;
  for (var t=2; t<=12; t++){
    assumptions.getRange('B4').setValue(t);
    SpreadsheetApp.flush();
    var weeks = model.getRange('B6').getValue();
    var cost = model.getRange('B7').getValue();
    log.getRange(row,1,1,3).setValues([[t,weeks,cost]]);
    row++;
  }
}

Common pitfalls and how to avoid them

Over-precision: Do not pretend the model predicts exact days. Use ranges and percentiles.
Hidden links: Keep assumptions in a single sheet and use named ranges — this prevents Goal Seek or Solver from changing the wrong cell.
Ineffective quality modeling: Avoid a single fixed "defect" number. Make defect rates a function of acceleration and team experience.
Forgetting ramp-up: New hires reduce initial throughput — model ramp explicitly.

Advanced recommendations for 2026

Use Excel's LET and LAMBDA functions to encapsulate repeatable calculations (cleaner formulas and easier reviews).
Leverage AI-assisted formula suggestions in Google Sheets and Microsoft Copilot in Excel to generate alternate heuristics faster, but always validate with domain judgment.
Store scenario runs in a database (BigQuery or Delta) for historical comparison — useful as organizations assess estimator accuracy over time.

"Not all progress starts with action" — decide when you need a sprint or a marathon. Use data to back that choice.

Final checklist before presenting scenarios

Validate assumptions with at least one SME (engineering lead or delivery manager).
Run sensitivity: change velocity ±20% and observe impact on weeks and cost.
Produce P50 and P90 estimates from Monte Carlo runs.
Create short one-slide summary: recommended option, cost delta, time delta, key risks.

Wrap-up: Use the planner to reduce debate and speed decisions

With this Sprint vs Marathon Scenario Planner you’ll move discussions from opinions to numbers. Use Goal Seek for quick one-input solves, Data Tables for fast sweeps, and Solver when constraints are complex. In Google Sheets, Apps Script fills gaps and enables the same repeatable workflows. In 2026, combine these spreadsheet techniques with connector automation and AI-driven insights to deliver scenario analysis to stakeholders faster than ever.

Action: Get the template and accelerate your planning

Ready to skip spreadsheet setup? Download our ready-to-use Sprint vs Marathon Scenario Planner template (Excel + Google Sheets) with pre-built Goal Seek scripts, Solver-ready models, and dashboards. Use it to run a decision-ready analysis in under an hour.

Next step: Download the template, paste your workload and cost assumptions, run the sweep, then run Goal Seek to produce a recommended team size. If you want a custom version (contractor mix, hybrid teams, or ROI NPV calculations), request a tailored template and we'll build it for your org.

Unknown

Contributor

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.