Netflix Financial Analyst (Mid-Level) Interview Preparation Guide

Total Variance = Actual Revenue − Budget Revenue

Volume = (Actual Total Units − Budget Total Units) × Budget Weighted Average Price

Mix = Actual Total Units × (Actual Mix Price − Budget Mix Price)

Price = Actual Revenue − Revenue attributable to Budget Price and Actual Units/Mix

CM_per_unit = Selling Price_per_unit − Variable Cost_per_unit

CM Volume = (Actual Total Units − Budget Total Units) × Budget Weighted Average CM
CM Mix = Actual Total Units × (Actual Mix CM − Budget Mix CM)
CM Price = Actual Units by SKU × (Actual Price_per_unit − Budget Price_per_unit) × 1  (then subtract variable cost impact if V costs changed)
Total CM Variance = CM Volume + CM Mix + CM Price + (Fixed/Other variances)

# inputs
leads = 10000                          # baseline monthly leads
conv = {'MQL': 0.20, 'SQL': 0.50, 'Opp': 0.30, 'Closed': 0.33}
avg_deal = 15000.0                     # average deal size
seasonality = 1.10                     # month multiplier

# cascade conversions
mql = leads * conv['MQL']
sql = mql * conv['SQL']
opp = sql * conv['Opp']
closed_won = opp * conv['Closed']

# revenue
revenue = closed_won * avg_deal * seasonality

H2 = A2 * B2            # MQL
I2 = H2 * C2            # SQL
J2 = I2 * D2            # Opportunity
K2 = J2 * E2            # Closed-Won
L2 = K2 * F2 * G2       # Revenue

Incremental Revenue per user = Effective Price * (1 - RevenueShare)
LTV = Incremental Revenue per user * Avg Tenure - Incremental CAC

-- 1. normalize invoices: sign, per-day rate, annualize
WITH inv_norm AS (
  SELECT
    i.invoice_id,
    i.subscription_id,
    s.customer_id,
    s.start_date,
    date_part('year', s.start_date)::int AS cohort_year,
    i.invoice_date,
    i.recognized_date,
    CASE WHEN i.invoice_type = 'credit' THEN -i.amount ELSE i.amount END AS amount,
    -- assume invoice covers period [recognized_date, recognized_end_date] if available; else treat as one-off
    i.recognized_end_date, 
    -- days covered (fallback 1)
    GREATEST(1, (i.recognized_end_date - i.recognized_date)) AS days_covered,
    -- daily revenue and annualized ARR contribution = daily * 365
    (CASE WHEN (i.recognized_end_date IS NOT NULL)
      THEN (CASE WHEN i.invoice_type='credit' THEN -i.amount ELSE i.amount END) / GREATEST(1, (i.recognized_end_date - i.recognized_date))
      ELSE (CASE WHEN i.invoice_type='credit' THEN -i.amount ELSE i.amount END)
    END) * 365.0 AS annualized_arr
  FROM invoices i
  JOIN subscriptions s USING (subscription_id)
),

-- 2. build monthly snapshots per customer for months 0..12 relative to cohort start
monthly AS (
  SELECT
    cohort_year,
    customer_id,
    date_trunc('month', start_date) + (n * interval '1 month') AS period_month,
    SUM(annualized_arr) FILTER (WHERE date_trunc('month', recognized_date) = date_trunc('month', start_date) + (n * interval '1 month')) AS delta_arr
  FROM inv_norm
  CROSS JOIN generate_series(0,12) n
  GROUP BY 1,2,3
),

-- 3. compute starting ARR and movement types by comparing customer ARR between months
cust_monthly AS (
  SELECT
    cohort_year,
    customer_id,
    period_month,
    SUM(delta_arr) OVER (PARTITION BY cohort_year, customer_id ORDER BY period_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_arr
  FROM monthly
),

-- 4. classify movements between month t-1 and t
movements AS (
  SELECT
    cohort_year,
    period_month,
    SUM(GREATEST(cum_arr - lag(cum_arr) OVER (PARTITION BY cohort_year, customer_id ORDER BY period_month), 0)) FILTER (WHERE lag(cum_arr) IS NOT NULL AND cum_arr > lag(cum_arr)) AS expansion,
    SUM(GREATEST(lag(cum_arr) - cum_arr, 0)) FILTER (WHERE lag(cum_arr) IS NOT NULL AND cum_arr < lag(cum_arr)) AS contraction,
    SUM(CASE WHEN cum_arr = 0 AND lag(cum_arr) > 0 THEN lag(cum_arr) ELSE 0 END) AS churned,
    SUM(CASE WHEN lag(cum_arr) IS NULL THEN cum_arr ELSE 0 END) AS new_arr,
    SUM(CASE WHEN lag(cum_arr) IS NULL THEN cum_arr ELSE 0 END) FILTER (WHERE period_month = date_trunc('month', start_date)) AS starting_arr -- adjust as needed
  FROM cust_monthly cm
  JOIN subscriptions s ON cm.customer_id = s.customer_id AND date_part('year', s.start_date)=cm.cohort_year
  GROUP BY cohort_year, period_month
)

-- final aggregation: per cohort totals over 12 months
SELECT
  cohort_year,
  SUM(starting_arr) AS starting_arr,
  SUM(new_arr) AS new_arr,
  SUM(expansion) AS expansion,
  SUM(contraction) AS contraction,
  SUM(churned) AS churn
FROM movements
WHERE period_month <= date_trunc('month', (make_date(cohort_year,1,1) + interval '12 months'))
GROUP BY cohort_year
ORDER BY cohort_year;

=IF(A2="Budget",0, G1)

=MAX(B2,0)    /* in E2 */
=ABS(MIN(B2,0)) /* in F2 */

=G1 + B2

wMAPE = ( sum_i |Forecast_i - Actual_i| ) / ( sum_i Actual_i )

Bias = ( sum_i (Forecast_i - Actual_i) ) / ( sum_i Actual_i )

Coverage = ( # SKUs with non-null forecast ) / ( total SKUs ) * 100%

Hit Rate = ( # observations where |Forecast - Actual| <= tol ) / ( total observations )

MAD = mean( |Forecast - Actual| )
RMSE = sqrt( mean( (Forecast - Actual)^2 ) )