Draft a cohort-based financial model
Build a spreadsheet model that connects historical customer cohorts to longer-term revenue assumptions.
How this works
Build a spreadsheet model that connects historical customer cohorts to longer-term revenue assumptions. Use this to connect customer acquisition cohorts with revenue assumptions over time. The model should make retention, expansion and acquisition drivers inspectable rather than hide them inside a single growth percentage.
Separate observed cohort history from projected cells, document each assumption and calculate revenue through explicit formulas. Reconcile historical totals before extending the horizon. The finance owner reviews scenarios and missing observations; the assistant cannot establish future growth from a plausible-looking curve.
Before you start
Cohort definition
Agree acquisition event, time grain and treatment of reactivated customers.
Historical records
Obtain customer start dates and revenue by period with stable identifiers.
Model assumptions
Confirm retention, expansion, pricing and acquisition scenarios with the owner.
Prepare the inputs
- customer_history
- Required
customer ID, cohort date, period and revenue
Missing observations remain distinct from zero revenue.
- cohort_rules
- Required
grouping, reactivation and revenue definitions
Use one consistent definition across history and forecast.
- assumptions
- Required
driver, scenario, value and evidence
Label owner estimates and historical estimates separately.
- horizon
- Required
forecast periods and reporting currency
Do not combine currencies without an approved basis.
Implementation, step by step
Validate the history
- Reconcile customer-period revenue to the approved financial totals.
- Check duplicate customers, refunds and incomplete periods.
Checkpoint: Historical input is trustworthy for modeling.
Build the cohort table
- Assign customers to cohorts and calculate observed revenue by cohort age.
- Distinguish unobserved future cells from genuine zero activity.
Checkpoint: The cohort matrix preserves data availability.
Define driver formulas
- Connect acquisition, retention, expansion and pricing to revenue using explicit formulas.
- Keep assumptions in separate editable cells with source notes.
Checkpoint: The model is understandable and reproducible.
Extend scenarios
- Project beyond observed history using the approved assumption sets.
- Show sensitivities and avoid fitting an unsupported long-term curve to a short history.
Checkpoint: Scenarios reveal their drivers.
Review the workbook
- Inspect formulas, reconciliations and boundary cases with finance.
- Record owner changes and keep actuals visually separate from projections.
Checkpoint: The workbook is ready for planning review.
Decisions along the way
Young cohort lacks later observations
Leave those historical cells unavailable and forecast separately.
Customer reactivates
Apply the agreed cohort rule rather than count a new acquisition by default.
Revenue is negative from refunds
Retain the adjustment and explain its effect on retention measures.
What you will produce
cohort-financial-model.xlsx
SpreadsheetA formula-based model separating observed and projected values.
Include: cohort · age · actual_revenue · forecast_revenue · drivers · scenario
model-assumptions.md
MarkdownA review record for financial drivers.
Include: definition · assumption · evidence · owner · sensitivity
Check the result
- Reconcile historical revenue with approved source totals.
- Inspect formulas across new cohorts and forecast boundaries.
- Check missing observations are not converted to zero.
- Test scenario changes for expected directional effects.
When something goes wrong
History does not reconcile
Resolve the data gap before fitting assumptions.
Formula ranges omit new cohorts
Extend references and recalculate the workbook.
Insufficient history for retention
Use an explicitly owner-supplied scenario and state the evidence limit.
A worked example
SYNTHETIC EXAMPLE · NOT A REPORTED BUSINESS RESULT
Request
Synthetic: model two monthly cohorts.
Inputs
January cohort revenue is 1,000 in January and 900 in February. February cohort revenue is 800 in February; March data is not yet available.
Expected result
Synthetic result: record January age-one retention at 90%, leave February age-one history unavailable, and show any March projection in a separate forecast cell with its assumption.
Run a small pilot
Start small
Model a small product with several completed cohorts and one recent cohort.
Record the baseline
Record manual model-building time and reconciliation corrections.
Agree success criteria
Agree formula accuracy and assumption transparency before planning use.
Stop or revise
Stop forecast interpretation when historical totals or cohort definitions are unresolved.
Keep the scope clear
- A cohort projection is a scenario, not a promised financial outcome.
- The model does not authorize budgets, fundraising claims or accounting treatment.
Check before using
Check every assumption and formula. Missing historical observations must not silently become zero revenue; projections require financial review.
Business activities
- Manage finances and accounting
FROM A RECIPE TO YOUR WORKFLOW
Bring the company context together.
This workflow needs reliable inputs before it needs a better prompt. Moat8 Vault brings working information into a readable archive for the AI tools you use.
For this recipe: Cohort history · Revenue drivers · Planning horizon and business assumptions
Start with one recurring task, agree which sources to use, and review the first result with your team.
Discuss this workflow