Business Case Development and ROI Analysis Questions

Start with the ASC 606 / IFRS 15 five-step framework: (1) identify the contract, (2) identify distinct performance obligations (POs), (3) determine the transaction price, (4) allocate the price to POs based on standalone selling prices (SSP), and (5) recognize revenue when (or as) each PO is satisfied.

Example assumptions (to make this concrete):

• Total contract price (upfront): $120,000 for a 3‑year agreement.
• Distinct POs: Implementation (one‑time), Software subscription (access for 36 months), Ongoing support (24 months starting after implementation).
• Determined SSPs: Implementation $20,000, Subscription $30,000/year, Support $5,000/year.
• SSP totals: Implementation $20k + Subscription $90k + Support $10k = $120k (so no residual allocation adjustments needed).

Allocation and recognition:

• Allocate transaction price to POs by SSP:
  • Implementation: $20,000
  • Subscription: $90,000 (recognized ratably over 36 months = $2,500/month)
  • Support: $10,000 (recognized ratably over 24 months = $416.67/month), beginning when support period starts
• Implementation revenue: recognize at the point in time when control transfers (often on delivery/completion) — record $20,000 immediately.
• Subscription and support: recognized over time on a straight‑line basis (unless usage patterns indicate another measure of progress).

Balance sheet and income statement effects:

• On receipt of the upfront payment: debit Cash $120,000; credit Contract Liability (Deferred Revenue) $120,000.
• As POs are satisfied:
  • On implementation completion: debit Contract Liability $20,000; credit Revenue — Implementation $20,000.
  • Monthly subscription recognition: debit Contract Liability $2,500; credit Revenue — Subscription $2,500.
  • Monthly support recognition (for 24 months once started): debit Contract Liability $416.67; credit Revenue — Support $416.67.
• After subscription/support periods end, contract liability balance will be zero (assuming no future obligations).

Additional considerations and judgment areas:

• SSP determination: use observable standalone prices when available; otherwise estimate (adjusted market assessment, expected cost plus margin, residual).
• Significant financing component: if upfront payment and the timing of transfer of goods/services span >12 months, evaluate whether to present interest income/expense (allocate portion of transaction price for financing).
• Contract costs: capitalize incremental costs to obtain (e.g., sales commissions) and amortize consistent with revenue recognition period.
• Metrics impact: ARR and deferred revenue balances will be affected; product managers should coordinate with finance to reflect revenue patterns in pricing/packaging decisions.

This approach ensures revenue aligns with delivery of promised goods/services and that the balance sheet reflects obligations until performance is complete.

Approach: group by cohort (acquisition_month) and month_offset, sum revenue, pivot to months 0–11 filling missing with zeros, compute cumulative revenue across months for each cohort, then divide by cohort user count to get average LTV per user. Use vectorized pandas/numpy operations and avoid Python loops for performance.

import pandas as pd
import numpy as np

def cohort_ltv(df: pd.DataFrame) -> pd.DataFrame:
    """
    Build cohort LTV table.
    Input df columns: ['user_id','acquisition_month','month_offset','revenue'].
    Returns DataFrame indexed by acquisition_month with columns:
      month_0 ... month_11 (cumulative revenue per cohort) and avg_ltv_per_user.
    Missing month data treated as zero. Optimized for large DataFrames using groupby/pivot.
    """
    # Ensure expected columns exist
    required = {'user_id','acquisition_month','month_offset','revenue'}
    if not required.issubset(df.columns):
        raise ValueError(f"DataFrame must contain columns: {required}")

    # Normalize types for performance
    df = df.loc[:, ['user_id','acquisition_month','month_offset','revenue']].copy()
    df['month_offset'] = df['month_offset'].astype(int)
    df['acquisition_month'] = df['acquisition_month'].astype(str)  # keep as string for index

    # Cohort sizes: unique users per acquisition_month
    cohort_sizes = df.groupby('acquisition_month')['user_id'].nunique()

    # Aggregate revenue by cohort and month_offset
    agg = df.groupby(['acquisition_month','month_offset'], observed=True)['revenue'].sum().reset_index()

    # Pivot to wide format with month columns 0..11, fill missing with 0
    months = list(range(12))
    pivot = agg.pivot_table(index='acquisition_month', columns='month_offset', values='revenue', aggfunc='sum', fill_value=0)
    # Reindex columns to ensure 0..11 present
    pivot = pivot.reindex(columns=months, fill_value=0).sort_index()

    # Cumulative revenue across months (per cohort)
    cum = pivot.cumsum(axis=1)
    # Rename columns to month_0 ... month_11
    cum.columns = [f'month_{m}' for m in cum.columns]

    # Average LTV per user = final cumulative (month_11) / cohort_size
    final_col = f'month_{months[-1]}'
    cohort_sizes = cohort_sizes.reindex(cum.index).fillna(0).astype(int)
    avg_ltv = cum[final_col].div(cohort_sizes.replace({0: np.nan}))  # avoid divide-by-zero

    result = cum.copy()
    result['avg_ltv_per_user'] = avg_ltv

    # Replace NaN avg_ltv (from zero-size cohorts) with 0
    result['avg_ltv_per_user'] = result['avg_ltv_per_user'].fillna(0)

    return result.sort_index()

Key points:

• Time complexity dominated by groupby/pivot: roughly O(n) for scans and aggregation.
• Memory: pivot creates (#cohorts × 12) table — small relative to raw data, good for large n.
• Handles missing months by fill_value=0.
  Edge cases:
• Users with multiple rows same month counted once in cohort_sizes (nunique).
• month_offset outside 0–11 ignored (you can clamp/filter if needed).
  Alternatives:
• For extremely large cardinality of cohorts, compute incrementally or use Dask/SQL for aggregation.

Approach: compute cohort size (distinct users by acquisition_month) and sum each cohort’s revenue in the first 6 months (month_offset 0..5). LTV per user = total 6-month revenue for cohort / cohort_size. Treat NULL monthly_revenue as 0 and assume missing rows for some month_offsets imply zero revenue for that month.

SQL:

WITH cohort_users AS (
  -- cohort size: distinct users per acquisition month
  SELECT
    acquisition_month,
    COUNT(DISTINCT user_id) AS cohort_size
  FROM subscriptions
  GROUP BY acquisition_month
),
cohort_revenue_6m AS (
  -- sum revenue for month_offset 0..5 (treat NULL as 0)
  SELECT
    acquisition_month,
    SUM(COALESCE(monthly_revenue, 0)) AS total_revenue_6m
  FROM subscriptions
  WHERE month_offset BETWEEN 0 AND 5
  GROUP BY acquisition_month
)
SELECT
  c.acquisition_month,
  c.cohort_size,
  COALESCE(r.total_revenue_6m, 0) / NULLIF(c.cohort_size, 0) AS ltv_6m
FROM cohort_users c
LEFT JOIN cohort_revenue_6m r
  ON c.acquisition_month = r.acquisition_month
ORDER BY c.acquisition_month;

Key points and assumptions:

• NULL monthly_revenue is treated as 0 via COALESCE.
• Missing month rows for a user are treated as zero revenue (no imputation).
• month_offset is relative to acquisition_month; we use 0..5 for the first 6 months.
• If cohort_size = 0 (unlikely), NULLIF prevents division-by-zero.
• If you want median LTV or per-user variability, compute per-user 6-month sums first and then aggregate (AVG, MEDIAN).

Clarifying assumptions (explicit):

• Offered TCV = $2,000,000 over 3 years (baseline). Prospect requests 20% discount → TCV = $1,600,000.
• Implementation services required (one-time cost to us) = $300,000 (assume cost to deliver; if it's revenue, clarify: here “provide $300k in implementation services” treated as cost).
• Additional expected services revenue (upsell/renewal professional services) = $200,000 over life (assume recognized when delivered).
• SaaS gross margin on subscription revenue = 80% (adjustable).
• Recognize subscription revenue ratably over contract term (36 months). Services revenue recognized on delivery (implementation upfront; additional services when delivered).
• Cash timing: subscription billed annually in advance unless otherwise specified. Assume customer pays annual installments equal to 1/3 of TCV up front each year; implementation invoiced and collected month 0; additional services invoiced when delivered (Year 2).
• Discount requested applies to subscription revenue and any bundled items; implementation still required.

Model (numbers):

Baseline (no 20% discount)

• TCV = $2,000,000 → annual subscription = $666,667
• Implementation cost (one-time cost) = $300,000 (cash outflow month 0). If implementation is revenue to us separate from TCV, treat as +$300k revenue month0; here treated as cost to deliver included in margin.
• Additional services revenue = +$200,000 delivered Year2.

With 20% discount

• TCV_net = $1,600,000 → annual subscription = $533,333
• Implementation cost still $300,000
• Additional services revenue +$200,000 Year2

Revenue recognition & cash flow (Discounted case, simple yearly view):
Year0 (signing / month 0):

• Cash in: Implementation (if invoiced) — assume customer pays implementation separately: +$300,000
• Cash out: implementation delivery cost = -$300,000 → net 0 if cost equals invoice (but margin may be zero). If implementation is not profitable, adjust.
  Year1:
• Cash in: subscription year1 = $533,333
• Recognize revenue ratably; but cash collected = billed.
• Gross margin on subscription: 80% → gross profit = $426,667
  Year2:
• Cash in: subscription year2 = $533,333 + additional services billed $200,000 = $733,333
• Gross profit = 0.8*533,333 + services margin (assume 60%) of 200,000 = 426,667 + 120,000 = 546,667
  Year3:
• Cash in: subscription year3 = $533,333 → gross profit = 426,667

Cumulative cash (discounted-case, ignoring tax & working capital):

• Year0: 0
• End Y1 cumulative cash in = 533,333
• End Y2 cumulative = 1,266,666
• End Y3 cumulative = 1,800,000

Payback (time to recover implementation cost and CAC):

• If implementation cost was internal investment (300k) and CAC assumed included, payback = time to reach cumulative gross profit >= 300k.
• Y1 gross profit = 426,667 → payback < 12 months (within Year1) in discounted case (because subscription margin high). If you include implementation cost as unrecovered until gross profit accumulated, payback approx 0.7 years.

Sensitivity to churn after Year1

• Key risk: customer could churn after Year1 causing you to lose Year2/3 subscription + expected services. Evaluate NPV of retention scenarios:

Scenario A: churn after Year1 (no Year2/3)

• Lost cash: 1,066,666 (two remaining subscription years + services)
• Realized cash = Year0 + Year1 = 533,333
• If churn probability p, expected value EV = 533,333*(1) + (1-p)*1,266,667 additional expected from future years.

Compare with baseline (no discount) numbers:

• Baseline cumulative cash end Y3 = 2,000,000 (plus services if outside TCV)
• Discount reduces lifetime cash by $400k and proportionally reduces margins by 320k (80% of 400k).

Decision framework and recommendation (product manager view)

• If churn risk after Year1 is >20% (i.e., substantial), the 20% discount materially reduces expected LTV and increases risk that CAC+implementation won’t be recovered when factoring churn and cost of capital. Run sensitivity: with 30% churn after Y1, expected remaining value = 0.7*(1,266,667) = 886,667; plus Year1 533,333 = 1,419,999 < baseline 2M — discount is unattractive.
• If retention is high (churn <10%) and you can invoice implementation as separate, profitable service or upsell $200k is likely, accept only if:
  • implementation is profitable (charge more or limit scope), or
  • require annual prepayment and contractually protect you (early termination fees, minimum commitment), or
  • tie discount to increased volume/longer term (commitment to auto-renew, add SLAs).
• Negotiate: offer 10% discount with implementation billed separately at margin, or accept 20% only if customer commits to non-cancellable annual prepay or pays implementation as professional services at market rate.

Final recommendation: Do not accept a flat 20% discount without stronger retention guarantees or separate profitable services billing. Require either (a) implementation invoiced and margin-protected, (b) annual prepayment or termination penalties, or (c) onboarding/expansion KPIs that trigger the discount. If retention probability after Year1 >90% and implementation is break-even or profitable, the 20% discount can be acceptable; otherwise counteroffer.

Situation: We need to know when giving a 10% price concession (to move a prospect into the 10% tier) is value-accretive given: 2-year commitment, gross margin = 65%, implementation cost = 10% of TCV.

Approach: compare post-discount net profit (gross profit minus implementation) for the deal when the discount induces an uplift in TCV versus the baseline deal without discount. Solve for minimum uplift u (percentage increase in TCV) needed so the discounted deal produces at least the same profit as the undiscounted deal.

Symbols:

• Baseline TCV = X
• Uplift from discount = u (so discounted TCV = (1+u)X)
• Discount = 10% → revenue = 0.9*(1+u)X
• Gross profit = 65% of revenue
• Implementation cost = 10% of TCV

Compute:

• Profit_baseline = 0.65X - 0.10X = 0.55X
• Profit_discounted = 0.650.9(1+u)X - 0.10*(1+u)X
  = (0.585 - 0.10)(1+u)X = 0.485(1+u)X

Set Profit_discounted >= Profit_baseline:
0.485*(1+u)X >= 0.55X → 1+u >= 0.55 / 0.485 ≈ 1.1340 → u >= 0.1340

Result: You need ≈13.4% or greater uplift in TCV from offering the 10% discount for the deal to be value-accretive compared to closing it at full price.

Numeric example: baseline deal X = $700,000.

• Required new TCV = 700k * 1.134 ≈ $793.8k.
• If discount only preserves the same $700k (no uplift), discounted profit = 0.485*700k = $339.5k vs baseline $385k → loss of $45.5k.

Negotiation levers to achieve the required uplift or mitigate margin loss:

• Convert discount into multi-year commitment or upfront payment to reduce churn and improve NPV.
• Tie discounts to upsells (add seats/modules) or higher commitments (volume bands) to reach the 13.4% uplift.
• Offer one-time onboarding credits (reduce recurring discount) so implementation cost treated separately.
• Shorten discount duration (first year only) and step-up pricing in year 2.
• Require minimum term, prepayment, or non-refundable deposit to lower effective implementation cost.
• Increase perceived value (bundle premium features) instead of pure price cuts.

This simple model ignores time-value of money and retention effects; include discounting and post-contract expansion in a refined NPV model for final decisions.