Revenue Build (Driver-Based Forecast)

When to use

Use when revenue should be built bottom-up from real operating drivers rather than a single growth percentage: customers from marketing spend or a funnel, churn, ARPU, or volume times price by product line. Good for operating plans, ARR forecasts, and fundraising models that need a defensible bridge from drivers to the top line. The skill builds a downloadable .xlsx in the Claude app with openpyxl; it does not use any Excel add-in or Microsoft 365 connection.

What it builds

A workbook with six tabs: - Cover: title, color legend, scenario selector, headline revenue and growth. - Drivers: all blue inputs (starting customers, marketing spend, CAC or funnel rates, ARPU, churn, price, scenario multipliers). - Revenue Build: the period-by-period customer or units schedule and revenue. - Summary: annual revenue, growth, ending customers, and the scenario in force. - Sensitivity: new-adds by churn Data Table driving ending ARR or revenue. - Checks: roll-forward identity and sanity flags.

Build workflow

Create the workbook and the six tabs in the order above.
On Drivers, lay out every input in blue with units; add a scenario cell (1=Base, 2=Bull, 3=Bear) named scn.
Set up the period header row (months or quarters) once and reference it everywhere.
Build the customer or units roll-forward on Revenue Build: beginning, adds, churn, ending.
Compute revenue each period from the schedule (customers times ARPU, or volume times price).
Build the Summary by aggregating periods to years.
Build the Sensitivity tab as a native two-variable Data Table.
Build the Checks tab with the roll-forward identity and growth sanity tests.
Recalculate the workbook headless (LibreOffice) so all formulas compute.
Verify zero formula errors (#REF!, #DIV/0!, #VALUE!, #NAME?); fix and re-recalculate in a loop, then deliver.

Tab-by-tab spec

Cover

Title cell, model purpose line, and a build date.
Color legend block: a blue swatch labelled "Input", black labelled "Formula", green labelled "Cross-tab link".
Scenario echo: =CHOOSE(scn,"Base","Bull","Bear") pulling the active scenario from Drivers.
Headline outputs as green cross-tab links: latest-year revenue =Summary!<latest revenue cell>, ending customers, and final-year YoY growth.
A one-line read-me noting the workbook recalculates live in Excel and was verified error-free at build.

Drivers

B2 Scenario selector (blue, 1/2/3), named scn.
Starting customers B5; ARPU per period B6; monthly churn % B7.
Acquisition: marketing spend per period B8; CAC B9; new adds from spend =B8/B9 (computed on Build, not here).
Optional funnel: leads B10, lead-to-customer % B11.
Scenario multipliers in a 3-column block (Base/Bull/Bear): adds multiplier row C14:E14, churn multiplier row C15:E15. Active values: B14 =CHOOSE(scn,C14,D14,E14), B15 =CHOOSE(scn,C15,D15,E15).
Optional product-line block: price per line and volume per line for a volume times price build.

Revenue Build

Row 3 period headers 1..N (single series, referenced by all rows below).
Beginning customers: period 1 =Drivers!B5; later periods =<prior ending cell>.
New adds: =Drivers!$B$8/Drivers!$B$9*Drivers!$B$14 (spend over CAC times active adds multiplier), or funnel =Drivers!$B$10*Drivers!$B$11*Drivers!$B$14. Use one consistent formula across the row.
Churned: =Beginning*Drivers!$B$7*Drivers!$B$15 (active churn multiplier).
Ending customers: =Beginning+Adds-Churned.
Revenue: =Ending*Drivers!$B$6 (customers times ARPU), or for product lines =SUMPRODUCT(price_range,volume_range).
ARR (if subscription): =Revenue*12 when periods are months, or annualize as appropriate.

Summary

Annual revenue: =SUM(<the 12 monthly revenue cells for that year>).
YoY growth: =ThisYear/PriorYear-1.
Ending customers per year: last period ending of the year via a cross-tab link.
Active scenario label: =CHOOSE(scn,"Base","Bull","Bear").

Sensitivity

Top-left corner references ending ARR or final-period revenue ='Revenue Build'!<ending ARR cell>.
Column input: new-adds per period values. Row input: churn % values.
Native two-variable Data Table with row input cell = Drivers churn and column input cell = a Drivers adds driver.

Checks

Roll-forward each period: =IF(Ending=Beginning+Adds-Churned,"PASS","FAIL") across all periods, then =AND(...).
Continuity: =IF(Beginning_period_t=Ending_period_t-1,"PASS","FAIL").
Growth sanity: flag if any YoY growth exceeds a blue cap input or is below a floor.
Non-negative customers and revenue every period.

Formula and formatting conventions

Blue font for inputs (Drivers only). Black for in-tab formulas. Green for cross-tab links.
No hardcoded numbers in formulas; constants live on Drivers as blue cells. Use absolute refs ($B$7) to driver cells so a row fills across periods cleanly.
One consistent formula per row so it copies horizontally without edits.
Customers as whole numbers with thousands separators; ARPU and revenue as currency; churn and growth as percent.
Name scn and the ending-ARR cell so Checks and Sensitivity read clearly.

Checks

Ending = beginning + adds - churn every period (PASS/FAIL).
Beginning of each period equals prior ending.
YoY growth within a stated sane band.
Customers and revenue never negative; churn between 0 and 1.

Recalculate and verify

After writing, recalculate headless with LibreOffice so openpyxl formula text becomes computed values. Scan every sheet for #REF!, #DIV/0!, #VALUE!, #NAME?. Fix any offending formula or missing named range and recalculate again in a loop. Deliver only when the Checks tab shows all PASS and no error strings remain.

Inputs to gather

Starting customers or units and ARPU or price per line.
Acquisition mechanism: marketing spend and CAC, or a funnel (leads and conversion).
Churn rate per period and the period length (month or quarter).
Scenario multipliers for adds and churn (Base/Bull/Bear).
Forecast horizon (number of periods).

Example

Hypothetical: start 1,000 customers, ARPU 50 per month, churn 3% monthly. Marketing spend 100,000 per month, CAC 500, so 200 new adds per month. Month-1 churn about 30 customers, ending about 1,170. Over 36 months under Base, customers ramp and monthly revenue grows; Bull applies a 1.2x adds and 0.8x churn multiplier. Sensitivity flexes adds from 150 to 300 against churn from 2% to 5% to show ending ARR.