Google Financial Analyst Interview Preparation Guide (Junior Level)

What is sensitivity analysis & why it matters
Sensitivity analysis tests how a model’s output (e.g., NPV) changes when a key input varies. It helps identify which assumptions drive value, quantify downside/upside, prioritize due diligence, and communicate risk to stakeholders.

Assumptions (stated clearly)

• Starting revenue = $10.0M (end of year 0)
• Project horizon = 5 years (revenues at year 1–5)
• Discount rate = 10% (WACC)
• For simplicity we treat revenue as cash flow (or note you can apply margin assumptions)

Approach
Compute NPV = sum_{t=1..5} 10*(1+g)^t / (1+0.10)^t for g = 3%, 5%, 8%.

Worked numbers (NPV over 5 years)

• g = 3% → NPV ≈ $41.3M
• g = 5% → NPV ≈ $43.7M
• g = 8% → NPV ≈ $47.2M

(Show calculation in model: use factor r = (1+g)/(1+0.10), PV = 10 * r*(1-r^5)/(1-r).)

Interpretation

• Moving growth from 3% → 8% increases 5-year NPV by ≈ $5.9M (14%).
• Sensitivity is material but not explosive; growth is an important lever.

How I’d present results to stakeholders

• Table: side-by-side NPVs and % change for each growth rate with stated assumptions.
• Line chart: NPV vs. growth rate (continuous curve) to show slope.
• Tornado chart: rank multiple drivers (growth, margin, discount rate, capex) to show biggest impacts.
• One-slide summary: key assumption, best/worst-case NPVs, break-even growth rate, recommendation and next steps (validate market assumptions, scenario testing).

This structure makes assumptions transparent, quantifies risk, and supports a clear investment recommendation.

Definition — Monthly Churn Rate
Monthly churn rate measures the proportion of customers or revenue lost in a month. It's used to gauge retention health and feed ARR forecasting.

Customer churn (by count)
Formula:

Customer Churn Rate = (Customers lost during month) / (Customers at start of month)

Explanation: counts canceled accounts regardless of contract size. Example: start 1,000 customers, 20 churn → 2% monthly churn. For ARR forecasting, high customer churn signals unit-level retention problems and impacts longtime customer lifetime value (LTV) assumptions.

Revenue churn (by MRR)
Gross Revenue Churn:

Gross Revenue Churn = (MRR lost from downgrades + MRR lost from cancellations during month) / (MRR at start of month)

Net Revenue Churn (accounts for expansions):

Net Revenue Churn = (MRR lost - MRR gained from expansions) / (MRR at start of month)

Explanation: works on dollar-weighted MRR so a few large customers matter more. Example: start MRR $200k, lost $8k, expansions $2k → gross 4%, net 3%.

Implications on ARR forecasting

• Customer churn affects headcount- and acquisition-driven unit economics; useful for volume-based forecasting and cohort LTV.
• Revenue churn directly moves ARR and is more predictive of near-term revenue runs; critical for modeling ARR growth, cash flow, and valuation.

Which to prioritize (enterprise product)
Prioritize revenue (MRR) churn because enterprise accounts are high-dollar and uneven; a small number of large losses materially change ARR. Continue tracking customer churn for product/CS insights and acquisition efficiency modeling.

Difference (concise)

• NPV = present value of cash flows discounted at a chosen cost of capital; gives value added in currency.
• IRR = discount rate that makes NPV = 0; gives implied rate of return.
• Key conceptual differences: NPV uses an external discount rate (consistent with WACC/objective value), IRR is internal and can be ambiguous with non‑conventional flows or multiple sign changes.

When NPV is preferred

• Use NPV for project ranking and mutually exclusive investments because it measures absolute value added and uses the firm’s required return.
• Prefer NPV when cash flows are irregular, projects differ in scale, or multiple IRRs exist.

Excel implementation (irregular timing)

• Use XNPV and XIRR which accept dates:

=XNPV(discount_rate, values_range, dates_range)
=XIRR(values_range, dates_range, [guess])

• Steps: list each cash flow with its actual date, set discount = WACC, compute XNPV to get PV. Use XIRR to find project IRR. Validate XNPV>0 and compare to other projects by NPV per capital if scale differs.

Practical note

• Remember IRR assumes reinvestment at IRR; NPV assumes reinvestment at discount rate — another reason to prefer NPV for firm decisions.

Approach
Build a driver-based CAPEX model linking capacity targets to physical and cost drivers, phased over three years.

Key drivers to include

• Capacity target (units/year)
• Equipment cost per unit of capacity
• Installation/commissioning % of equipment cost
• Civil/site prep costs (area m2) tied to expansion sq ft
• Utility upgrades (kW, M3) and permitting timelines
• Contingency % and inflation rate

Phasing spend

• Year 0 (planning/engineering): 10–20% for design, long-lead orders
• Year 1 (major procurement & civil): 50–70% as equipment delivered and installed
• Year 2 (commissioning & ramp): 10–30% final payments, spares, contingency

Implementation notes

• Tie monthly cashflows to milestone triggers (PO, ship, install, test)
• Sensitivity scenarios on capacity outcome, supplier lead times, inflation
• Validate with PM/engineering and include staged capital approvals.

Model structure (driver-based, 12-month rolling)

• Build a monthly worksheet with rows: Active Users, Conversion Rate, Paying Users, ARPU, Seasonality Index, Promotions uplift, Net Revenue.
• Formula: Paying Users = Active Users * Conversion Rate; Revenue = Paying Users * ARPU * Seasonality Index * (1 + Promotion Uplift) +/- Upgrades/Churn adjustments.
• Rolling logic: every month append actuals, drop month 13, project next month using updated drivers.

Source & validate inputs

• Sources: product analytics (DAU/MAU), CRM for conversion, billing system for ARPU, marketing calendar for promotions.
• Validation: cross-check DAU/MAU with product team, reconcile billed subscribers to ARPU, use historical correlation tests (R²) between season index and revenue.

Seasonality & promotions

• Derive seasonality index from 3-year monthly moving average of normalized revenue. Apply multiplicative factor.
• Model promotions as temporary uplift on conversion or ARPU with start/end dates; test lift using A/B results or historical promo cohorts.

Churn & upgrades

• Model churn as monthly retention rate applied to paying users; include cohort-based churn for accuracy.
• Model upgrades/downgrades as ARPU migration matrix or expected % of users upgrading; capture timing and one-time vs recurring effects.

Monthly update checks

• Reconcile model revenue to actual billing within tolerance.
• Variance analysis: driver-level (Active Users, Conversion, ARPU) vs prior forecast.
• Sanity checks: year-over-year seasonality, month-to-month growth caps, correlation breakdowns.
• Sensitivity: run +/−10% on key drivers and document assumptions for stakeholder review.

Definition & intuition
Weighted Average Cost of Capital (WACC) is the after-tax average rate a company is expected to pay to finance its assets. It’s used as the discount rate for DCF valuation and to evaluate investment returns.

Formula

WACC = (E / V) * Re  +  (D / V) * Rd * (1 - Tc)

Where V = E + D.

Components explained

• Re (cost of equity): required return by equity investors reflecting risk (dividend/price expectations, growth, risk premium).
• Rd (cost of debt): effective interest rate on debt (market yield or borrowing rate); use pre-tax figure in formula.
• E/V and D/V (weights): proportion of equity and debt in capital structure. Prefer market values (market cap, market value of debt) because they reflect current investor pricing; book values can bias WACC especially when markets are volatile.
• Tc (tax rate): corporate tax rate; (1 - Tc) captures the interest tax shield that lowers after-tax cost of debt.

Common methods to estimate Re

• CAPM: Re = Rf + Beta * (Rm - Rf) — most common for public firms.
• Build-up model: Re = Rf + Equity Risk Premium + Size Premium + Industry/Company-specific Premium — used when beta is unreliable.
• Dividend Discount Model or multi-stage DCF as alternatives.

Practical adjustments for non-public or thinly traded firms

• Use industry comparables for beta (unlever and relever using target leverage).
• Apply size and illiquidity premiums via build-up model.
• Use private-company risk premium (additional percentage).
• Estimate Rd using company’s actual borrowing rates or spreads over comparable-rated public debt.
• Prefer implied equity cost from transaction multiples or analyst consensus when available.

As a financial analyst I prioritize market-based weights, document assumptions (tax rate, target leverage), test sensitivity, and add illiquidity/small‑company premiums where appropriate.

Approach (summary)
Model each deal’s timeline as: expected sales-cycle + expected procurement delay (probabilistic) + onboarding lag + payment terms. Use scenario-weighting (no delay vs delay) and close probability to produce probability-weighted revenue recognition and cash collections for reporting and cash planning.

Key calculation (example)

• Sales cycle = 6 months
• Procurement delay: 30% chance of 1–3 months → model mean delay = 0.3 * 2 = 0.6 months (use distribution for scenarios)
• Onboarding = 1 month (4 weeks)
• Payment terms = Net30 → 1 month to cash after billing

Expected time to first bill (months):

T_bill = 6 + (0.3 * 2) + 1 = 7.6

Expected time to cash:

T_cash = T_bill + 1 = 8.6

How to model probability-weighted recognition & cashflow

1. Deal-level inputs: contract value, close probability, expected close date, procurement-delay probability & distribution, onboarding lag, billing schedule, payment terms.
2. Scenario generation: for each deal create at least two scenarios: no delay (70%) and delay (30%) with sampled delay (1–3 months or expected 2).
3. For each scenario compute billing start date = close date + sales-cycle + delay + onboarding. Then map billing schedule to revenue recognition rules (ASC 606 or company policy) and cash collections by adding payment terms.
4. Weight each scenario by (close probability * scenario probability) to get probability-weighted revenue and cash dates. Sum across deals to produce monthly probability-weighted revenue and cashflow forecasts.

Outputs for reporting & cash planning

• Probability-weighted P&L timing: monthly recognized revenue distribution (used for best-estimate reporting).
• Cash runway / liquidity: probability-weighted monthly cash collections plus scenario percentiles (P50, P75, P90) for stress testing.
• Sensitivity table: show impact of ±1 month change in procurement delay or onboarding on timing and cash.

Practical notes & best practices

• Use Monte Carlo if portfolio is large to capture variability.
• Reconcile to closed-won buckets and update actuals as procurement/onboarding events occur.
• Present both expected (mean) timeline and percentile scenarios for treasury planning.

Overview / goal
Explain a reproducible Excel process to reconcile a detailed ledger export (GL) to a summarized P&L when totals differ, isolating missing, mis-mapped, currency or timing issues.

Preparation

• Import both files into Power Query as tables (GL and P&L summary). Keep original files read-only and timestamped.
• Standardize columns: AccountCode, AccountName, Department, Date, Amount, Currency, MappingKey.
• Create a mapping table (AccountCode → P&L Line) and a FX rates table by date.

Helper tables & pivots

• Build a Pivot Table from GL grouped by MappingKey, P&L Line, Department, and Currency (sum Amount).
• Build a Pivot Table from P&L summary by P&L Line, Department, Currency.
• Create a reconciliation sheet showing GL total, P&L total, and variance by P&L Line and Department.

Power Query anti-joins to find missing items

• In Power Query, left anti-join GL to P&L summary on MappingKey + Department + Currency to find GL items not in P&L.
• Right anti-join to find P&L lines without corresponding GL transactions.
• Use an anti-join between GL and mapping table to find unmapped account codes.

Checks for common issues

• Mapping differences: flag account codes present in GL but mapped to multiple P&L lines; use a distinct count pivot on MappingKey.
• Currency conversion: apply FX rates in Power Query to normalize amounts to reporting currency, then compare totals.
• Cut-off / timing: filter GL by date ranges; create running totals and compare month-end balances; identify transactions on boundary dates.
• Rounding / aggregation: compare sums at the transaction level vs. aggregated level to catch rounding.

Resolution workflow

• Prioritize variance by size; export anti-join results for investigation, add comment/owner columns, correct mapping or reclassify adjustments, re-run queries and pivots until variance is explained.

Approach (brief)

Load transactions and budget (target) tables, aggregate monthly per SKU, compute variances decomposed into price, volume, and mix vs. budget/plan.

High-level pseudo-code (SQL-style + Python orchestration)

-- 1. normalize & aggregate transactions monthly per sku
CREATE TABLE txn_monthly AS
SELECT sku_id, month,
       SUM(quantity) AS qty,
       SUM(revenue)  AS revenue,
       SUM(cost)     AS cost,
       AVG(price)    AS avg_price
FROM transactions
WHERE date BETWEEN @start AND @end
GROUP BY sku_id, month;

-- 2. budget aggregated per sku-month and total category
CREATE TABLE budget_monthly AS
SELECT sku_id, month, budget_qty, budget_price, budget_revenue
FROM budgets;

-- 3. join and compute variances
SELECT t.sku_id, t.month,
       t.qty         AS actual_qty,
       b.budget_qty,
       t.avg_price   AS actual_price,
       b.budget_price,
       -- volume variance
       (t.qty - b.budget_qty) * b.budget_price AS volume_variance,
       -- price variance
       (t.avg_price - b.budget_price) * t.qty       AS price_variance,
       -- mix variance (if category-level budget exists)
       (t.share_of_category - b.planned_share) *
         category_total_qty * b.budget_price        AS mix_variance,
       (t.revenue - b.budget_revenue) AS total_revenue_variance
FROM txn_monthly t
JOIN budget_monthly b USING (sku_id, month)
LEFT JOIN (
  -- compute category totals and shares
) cat ON ...
;

Performance optimizations for 200M rows

• Pre-partition by month and sku hash; store as Parquet on object store.
• Use distributed engine (Spark/Presto) with predicate pushdown and vectorized I/O.
• Push aggregation to cluster (map-side combine, reduce by key).
• Column pruning: only load date, sku, qty, price, revenue.
• Use approximate distinct only where acceptable; sample-test before full run.
• Cache intermediate month-level aggregates; avoid repeating full-scan joins.

Incremental processing

• Maintain watermark per source date; process only new/changed partitions.
• Produce delta aggregates and merge into monthly materialized table (UPSERT).
• Use CDC (change data capture) or snapshot diff to capture updates to transactions/budgets.

Testing plan (scale & correctness)

• Unit tests on small synthetic datasets with known decomposition.
• Property tests: conservation of variance (price+volume+mix ~= total variance).
• Regression tests: compare incremental vs full-batch outputs on rolling windows.
• Sampling-based checks: compute full-run on 1% stratified sample vs incremental results.
• Performance tests: run on representative partition (one month) with production cluster; assert SLA.
• Monitor row counts, nulls, and reconciliation dashboards; alert on drift.

This design balances financial correctness (explicit variance formulas) with scalable engineering practices for large-scale monthly SKU variance analysis.

Calculation

DSO = (Accounts Receivable / Revenue) × Days
DSO = (350,000 / 4,000,000) × 365
DSO = 0.0875 × 365 = 31.94 days ≈ 32 days

Interpretation

• A DSO of ~32 days means on average it takes ~32 days to collect receivables. That’s reasonable for many B2B firms but depends on industry benchmarks.

Two policy changes to reduce DSO (with trade-offs)

1. Tighten credit terms (e.g., move from Net 45 to Net 30)

• Impact: Shorter payment window directly lowers AR and DSO; improves cash flow and reduces working capital needs.
• Trade-off: May reduce sales or win-rate if customers prefer longer terms; higher sales effort or discounting may be required to retain clients.

2. Introduce early-pay discounts and automated invoicing/collections

• Impact: Incentivizes faster payment and reduces billing/payment friction; automation reduces days outstanding and AR admin costs.
• Trade-off: Discounts lower margin on sales collected early; automation requires upfront investment and change management.

I would quantify expected DSO reduction and P&L impact before recommending implementation.