Check whether each sale really improves your business

Methodology and author

Define context, complete inputs, produce, review and adapt. Designed and signed by Julio Alonzo. Designed and signed by Julio Alonzo.

Requirements and limitations

Five independent cases in USD, with no currency conversion. Recurring: revenue and service cost per active customer-month. Transactional: one completed sale per customer, without repeat purchases. CAC is paid once at the start. Do not add monthly and per-project amounts as if they shared one unit. In Inputs, mode 1 uses churn; mode 2 uses retention. Only the selected input is authoritative. The build derives the other rate as 1 minus the active rate. Values in the inactive column are retained but unused. Both rates are ignored for one-off sales. Active rates must be between 0% and 100%. Sensitivity contains the only scenario selector. It multiplies price and CAC and adds the churn change in percentage points. Service cost does not change. A 1% shift means one percentage point: 3% becomes 4%. Invalid rates are not clipped; they are marked REVIEW. Switch scenarios to compare recalculated results; six simultaneous models are not displayed. Gross contribution = active revenue − service cost. Gross margin = gross contribution / revenue; zero revenue returns n.a. Month-1 or sale contribution = gross contribution − CAC, which is not deducted again in later months. Net customer value = LTV − CAC. These are not net business profits. Recurring LTV = monthly gross contribution / constant churn. A full first month and cancellation at each month end are assumed. Zero churn produces no finite LTV, except zero gross contribution, whose LTV is zero; other zero-churn cases show n.a. Transactional LTV = gross contribution from one sale. LTV/CAC returns n.a. when CAC is zero. Cash flows are not discounted for time value. Payback: the visible schedule calculates 120 months per initial customer. Month 0 pays CAC; month 1 receives full gross contribution. Thereafter, recurring contribution = previous contribution × retention; a one-off sale does not repeat. Cumulative contribution less CAC gives the first whole month with a nonnegative balance (USD0.00000001 tolerance). Zero CAC returns month 0. Nonpositive contribution with positive CAC: unrecovered; recovery beyond the horizon or never: >120 months. With churn strictly between 0% and 100%, finite LTV at or below CAC never recovers CAC in a finite month; rounding does not change this. With 100% churn, payback occurs in month 1 if that contribution covers CAC. The traffic light uses editable illustrative targets, initially LTV/CAC ≥3 and payback ≤12 months. Red: nonpositive gross contribution, negative finite net value or no recovery within 120 months. Amber: no finite LTV or outside targets. Green: positive contribution, recovery within target and sufficient finite ratio; zero CAC is handled separately. These are not universal criteria or a viability certification. The five original scenarios retain their relative changes. A sixth fictional stress case is added: price ×0.30, CAC ×2 and churn +15 percentage points. With original data, base case 1 has USD75 gross contribution, USD2,500 LTV and 5-month payback. Under stress, gross contribution is −USD9 and LTV −USD50. Project A changes from USD2,300 net sale contribution to −USD1,800. Select 1 and then 6 to reproduce; return to 1 when done. Revenue is collected and service cost paid at the relevant month end, without delays. Expansion, reactivation, temporary discounts, taxes, financing, overhead and capacity constraints are not modeled unless included in service cost. The cohort is a mathematical expectation; it does not predict a specific customer’s survival or replace a cash-flow forecast. Enter zero for a known zero amount; blank or numeric text is invalid in an active field. Checks observe defined integrity rules and do not feed the model. They do not guarantee input accuracy or constitute advice. The historical copy and August reports are retained separately; this edition documents October repairs and tests. Microsoft Excel UI was not tested. The native Microsoft Excel interface was not tested.

Cutoff date: 2026-10-12