Startup Budget Tool: Architecture, Cohort Modeling, and Runway Control

What a 'startup budget tool' must do

The purpose is not bookkeeping but survival and scale. A startup budget tool predicts runway and funding needs, allocates capital to goals (MRR, NRR, payback), and adapts plans with rolling forecasts. For a beginner-friendly overview of budgeting fundamentals, see this foundational piece and then return to implement the advanced stack described here: intro to budget tools.

The architecture: nine tabs that power the model

Organize the workbook into: 1) Assumptions, 2) Pipeline & Conversion, 3) Revenue (Cohorts), 4) Costs (COGS/OPEX), 5) Hiring Plan, 6) Cash & Runway, 7) Scenario Engine, 8) KPI Dashboard, 9) Reporting Pack. Each tab is described with fields, formulas, and workflows to enable investor-grade outputs.

1) Assumptions

Centralize pricing, ARPA per segment, churn/expansion, discounts, payment terms, fees, CAC by channel, sales cycle, implementation effort, support workload, engineering velocity, credit interest. All downstream sheets reference these named ranges to make scenarios switchable.

2) Pipeline & Conversion

Model monthly buckets Leads → MQL → SQL → Opp → Won with conversion rates and cycle days per stage and per channel (Paid Search, Organic, Partnerships, Outbound, PLG). Example formula: Won_t = SQL_{t - sales_cycle} * WinRate. For PLG, include activation and PQL stages to estimate self-serve revenue.

3) Revenue (cohort-driven)

Every month's new customers form a cohort with its own ARPA, discounts, churn, and expansion. Revenue per cohort-month = Subscribers_{c,m} * ARPA_{c,m}. Track logo churn and revenue churn separately, plus add-on seats/features with lags. Respect billing cycle (monthly/annual) and cash timing (deferred revenue vs. cash received). This is the core differentiator from generic budgets.

4) Cost Model

COGS: hosting, payment fees (% of revenue), support cost per ticket, implementation hours, third-party APIs. OPEX: marketing by channel, sales (commissions, tools), product/engineering (comp bands, stack), G&A. Drivers: marketing spend tied to CAC targets; commissions as % of new MRR; hosting scaled by active users/requests.

5) Hiring Plan

Headcount by month with role, seniority, start date, base comp, payroll taxes, equipment, onboarding, recruiting fee, productivity ramp. Tie AEs to quotas, support to tickets/MAU, engineering to velocity targets. Add vacancy buffer (e.g., 45-day time-to-hire) so burn reflects reality.

6) Cash & Runway

Separate P&L from cash. Book revenue when earned but model cash when invoiced/paid. Expenses split into immediate (payroll), net-14/30 (vendors), and quarterly/annual (software). Cash balance = Opening + In - Out; Runway = Cash / Next month's net burn. Layer in credit lines with interest and covenants.

7) Scenario Engine

Define named profiles: price +X/−Y%, CAC ±, churn ±, hiring freeze from month T, fundraising delayed N months, enterprise deal closes in Q3. A drop-down scenario selector propagates through named ranges. Tactical toggles: 'Freeze hiring', 'Cut paid 20%', 'Extend payment terms'.

8) KPI Dashboard

Show MRR/ARR, Net New MRR, CAC by channel, blended CAC, LTV, LTV/CAC, Gross Margin, Magic Number, Payback, Logo Churn, NRR, Burn Multiple, Rule of 40. Traffic-light goals and warnings when runway < 9 months.

9) Reporting Pack

Auto-generate: 12-month P&L, burn/runway curve, hiring Gantt, CAC/Payback by channel, sensitivity plots. Export to PDF/Slides for board and investor updates.

Ready-to-use formulas

Logo churn: Subs_t = Subs_{t-1} * (1 - churn)
MRR by cohort: MRR_{t} = Subs_{t} * ARPA_{t} with ARPA_{t} = ARPA_{t-1} * (1 + expansion) * (1 - discount_fade)
CAC (channel): CAC_k = Spend_k / NewCustomers_k
Payback: Payback_k = CAC_k / (GrossMargin * ARPA)
LTV: LTV = (ARPA * GrossMargin) / Churn
Burn Multiple: BurnMultiple = NetBurn / NetNewARR

Data model

Dims: Month, Channel, Segment, Plan, CohortMonth, Country. Facts: Leads, SQL, Won, NewMRR, ExpansionMRR, ChurnedMRR, Spend, Hosting, SupportHours, Headcount, Salaries, Software, CashIn, CashOut. Enables pivotable investor-grade analytics.

10-step implementation in 48 hours

1) Gather 6–12 months of actuals (bank/GL, CRM, billing). 2) Fill Assumptions from actuals. 3) Build channel funnel with stage conversion and cycle time. 4) Create cohort revenue sheet with churn/expansion logic. 5) Input driver-based COGS and OPEX. 6) Build hiring plan tied to quotas and workloads. 7) Implement cash timing (AR/AP aging, prepayments, annual contracts). 8) Wire the scenario engine and dashboards. 9) Reconcile against accounts. 10) Publish the Reporting Pack and schedule monthly reviews.

Operating cadence

Weekly: Update funnel, signups, paid spend, new hires. Monthly: Close prior month, review KPIs, switch scenario, approve hiring and paid changes. Quarterly: Review pricing, packaging, segments, CAC/LTV.

Three archetypes

PLG SaaS: Emphasize activation and PQL conversion; self-serve annual prepay improves payback and cash.
Enterprise SaaS: Probability-weighted opportunities, milestone billing, services margin.
Marketplace: Separate supply/demand CAC and payback; take-rate model for revenue; escrow/fee timing in cash flow.

Controls and red flags

Burn Multiple > 2 without ARR acceleration, LTV/CAC < 3 on core segment, NRR < 100% for SaaS, runway < 9 months with no active raise, >60% new customers from a single channel.

Tooling and integration

Start in Google Sheets, then connect accounting actuals and CRM exports. Add BI when the team needs self-serve analytics. Keep monthly snapshots of Assumptions for auditability.

Column templates

Revenue (cohort): CohortMonth, Month, NewSubs, ActiveSubs, ARPA, NewMRR, ExpansionMRR, ChurnedMRR, EndMRR.
Marketing: Month, Channel, Spend, Leads, MQL, SQL, Won, CAC, Payback.
Hiring: Month, Role, Seniority, StartDate, Salary, Ramp%, Tools, RecruitFee.
Cash: Month, OpeningCash, CashIn, CashOut, NetBurn, ClosingCash, Runway.

Investor communication

Present base and worst side-by-side. Highlight payback improvements, NRR trend, and path to break-even. For stakeholders new to budgeting fundamentals, share the introductory resource: budget tool basics.

Next steps

Implement the architecture, run a base case, then add two tactical scenarios: 'Enterprise deal closes Q3' and 'Bootstrap 12 months'. Use the tool to govern runway and hiring in monthly board meetings. Explore further resources at Foundbase.io.