--- name: financial-modeling description: Build integrated three-statement financial models with income statement, balance sheet, and cash flow projections. Use when creating financial models for fundraising, M&A analysis, strategic planning, valuation, scenario analysis, or investment decision-making. Triggers on phrases like "financial model", "three-statement model", "integrated model", "build model", "pro forma", "DCF model", "valuation model", "model assumptions", "base case model". --- # Financial Modeling Build rigorous, integrated financial models for strategic decision-making, valuation, and scenario analysis. ## Workflow ### Model Development Process Trigger when a new modeling engagement begins (fundraising, M&A, strategic planning, valuation): 1. **Define scope and purpose**: Use case, time horizon (3/5/10-year), required outputs (IRR, NPV, equity value, cash projections), audience sophistication. 2. **Gather historical data**: 3–5 years of financials, operational metrics, capital structure, working capital trends, tax history. 3. **Build income statement**: Revenue by product/segment, price × volume, COGS, operating expenses (fixed vs. variable), non-operating items. 4. **Build balance sheet**: Assets (cash, AR, inventory, FA), liabilities (AP, accruals, debt, deferred revenue), equity roll-forward. 5. **Build cash flow**: Operating (indirect method), investing (capex, M&A), financing (debt, equity, dividends); cash tie-out. 6. **Link and balance**: Verify IS → BS equity, CF → BS cash, BS balances (A = L + E), cross-check formulas, stress-test. 7. **Sensitivity/scenario**: Data tables, scenario switch, tornado charts, Monte Carlo if applicable. 8. **Validate and audit**: Balance checks, formula audit, extreme value testing, peer review, documentation. ### Model Architecture Standards ``` FINANCIAL MODEL ARCHITECTURE STANDARDS ======================================== Color Coding (Industry Standard): Blue text: Input cells (user-modifiable assumptions) Black text: Formula/calculated cells (protected) Green text: Output cells (results, read-only) Red text: Error flags / balance checks Purple text: Links to other worksheets / workbooks Sheet Structure: Sheet 1: COVER — Model name, date, version, author, key outputs summary Sheet 2: ASSUMPTIONS — All input assumptions organized by category Sheet 3: INCOME STATEMENT — Detailed P&L build (monthly for Year 1, annual thereafter) Sheet 4: BALANCE SHEET — Full BS with roll-forwards Sheet 5: CASH FLOW — Operating, investing, financing sections Sheet 6: WORKING CAPITAL — DSO, DIO, DPO calculations and schedules Sheet 7: FIXED ASSETS — FA register, depreciation schedule, capex plan Sheet 8: DEBT SCHEDULE — All debt instruments with amortization Sheet 9: EQUITY — Cap table, raises, buybacks, dividends Sheet 10: SCENARIOS — Base/upside/downside with toggle switch Sheet 11: SENSITIVITY — Data tables, tornado charts, Monte Carlo Sheet 12: VALUATION — DCF, comparables, precedent transactions Sheet 13: CHECKS — Balance checks, formula validation, error tests Tab Order: Summary → Assumptions → Detailed Builds → Supporting Schedules → Outputs Never place inputs in calculation sheets (clean separation) Navigation: Hyperlinked table of contents on Cover sheet "Back to Summary" button on every sheet Conditional formatting for balance checks (green = balanced, red = error) Protection: Input cells: Unlocked (blue) Formula cells: Locked and protected (password-protected if shared) Sheets: Protected to prevent accidental formula deletion Workbook: Structure protected to prevent sheet deletion/renaming ``` ### Revenue Modeling Approaches ``` REVENUE MODELING BY BUSINESS TYPE =================================== SaaS Revenue Model: Beginning ARR + New Logo ARR (Pipeline × Win Rate × ACV × Ramp Factor) + Expansion ARR (Existing ARR × Net Expansion Rate) − Gross Churn ARR (Existing ARR × Gross Churn Rate) = Ending ARR Monthly Revenue = Ending ARR / 12 Assumptions to model: - Pipeline coverage ratio: 3–4x quarterly quota - Win rate by stage: Discovery 10%, Demo 30%, Proposal 60%, Negotiation 85% - Sales cycle: 3–6 months (SMB), 6–12 months (Enterprise) - Ramp factor: New reps at 50% productivity Month 1–3, 75% Month 4–6, 100% Month 7+ - Gross churn: 5–10% annual (B2B SaaS) - Net expansion: 10–30% annual (land-and-expand) Transaction Revenue Model: Revenue = Units × Price × (1 − Discount Rate) Units = Leads × Conversion Rate × (1 + Repeat Rate) Price = List Price × (1 − Average Discount %) Assumptions: - Conversion rate by channel: Organic 3–5%, Paid 1–3%, Referral 5–8% - Average order value: $50–$5,000 (varies by product) - Repeat purchase rate: 20–40% within 12 months - Seasonality: Monthly indices (e.g., Dec = 2.5× baseline for retail) - Discount rate: 5–20% (trade promotions, volume discounts) Services Revenue Model: Revenue = Billable Hours × Utilization Rate × Blended Rate Billable Hours = FTE Count × Available Hours × Target Utilization Available Hours = 2,080 annual − PTO − Holidays − Training ≈ 1,700 hours Assumptions: - Utilization rate: 70–85% (consultants), 60–75% (technical staff) - Blended rate: $150–$500/hour (by consultant level and specialty) - Project margin: 25–45% gross - Sell-through: Committed revenue / available capacity (target > 1.2×) - Ramp: New hires at 40% Month 1, 60% Month 2, 80% Month 3, 100% Month 4 ``` ## Three-Statement Integration ### Balance Sheet Build ``` BALANCE SHEET MODELING DETAIL =============================== Assets: Cash: From cash flow statement (ending balance) Accounts Receivable: Revenue × DSO / 365 - DSO assumption: 30–60 days (varies by industry and customer mix) - Trend: DSO may improve with collections process or deteriorate with economic conditions Inventory: COGS × DIO / 365 - DIO assumption: 15–90 days (varies by product type) - Safety stock: 10–20% above forecasted demand Prepaid Expenses: 1–2 months of operating expenses (insurance, rent) Fixed Assets (Net): Opening Net FA + Capex − Depreciation − Disposals = Closing Net FA Depreciation: Straight-line over useful life (3–7 years for equipment, 15–39 years for buildings) Capex: % of revenue (2–10% for SaaS, 5–15% for manufacturing) Deferred Tax Assets: Net operating loss carryforwards × tax rate Other Assets: Deposits, lease right-of-use assets (ASC 842) Liabilities: Accounts Payable: COGS + Opex × DPO / 365 - DPO assumption: 30–60 days (varies by bargaining power) - Trend: DPO may extend with vendor negotiations Accrued Expenses: 1–2 months of operating expenses (payroll, benefits, bonuses) Deferred Revenue: Advance payments × months remaining / total contract months - SaaS: 12 months of pre-collected annual contracts Debt (Current): Principal payments due within 12 months Debt (Long-term): Remaining principal after current portion Lease Liabilities: Present value of remaining lease payments (ASC 842) Other Liabilities: Tax payables, employee benefits obligations Equity: Common Stock: Par value × shares outstanding Additional Paid-in Capital: Cumulative equity raises − par value Retained Earnings: Opening RE + Net Income − Dividends Treasury Stock: Cost of share buybacks (negative equity) Accumulated Other Comprehensive Income: FX translation adjustments, unrealized gains/losses ``` ### Cash Flow Modeling ``` CASH FLOW STATEMENT MODELING ============================== Operating Activities (Indirect Method): Net Income $XX.XM + Depreciation & Amortization $X.XM + Stock-based compensation $X.XM + Deferred taxes $X.XM − Changes in working capital: Accounts receivable ($X.XM) (increase = cash outflow) Inventory ($X.XM) (increase = cash outflow) Accounts payable $X.XM (increase = cash inflow) Accrued expenses $X.XM (increase = cash inflow) Deferred revenue $X.XM (increase = cash inflow) = Cash from Operations $XX.XM Investing Activities: Capital expenditures ($X.XM) Acquisitions ($XX.XM) Purchase of investments ($X.XM) Sale of investments $X.XM Proceeds from asset sales $X.XM = Cash from Investing ($XX.XM) Financing Activities: Proceeds from debt $XX.XM Debt repayments ($X.XM) Proceeds from equity $XX.XM Share buybacks ($X.XM) Dividends paid ($X.XM) Debt issuance costs ($X.XM) = Cash from Financing ($XX.XM) Net Change in Cash $X.XM Cash, Beginning of Period $XX.XM Cash, End of Period $XX.XM Key tie-outs: - End of period cash MUST equal cash on balance sheet - Net income MUST equal bottom line of income statement - Capex MUST equal change in gross fixed assets + disposals - Deferred revenue change MUST reconcile to subscription model ``` ## Scenario and Sensitivity Analysis ### Multi-Scenario Framework ``` SCENARIO MODELING FRAMEWORK ============================= Scenario Toggle (single cell switch): 0 = Base Case 1 = Upside Case 2 = Downside Case Revenue Assumptions by Scenario: Growth Rate: Base 20% | Upside 28% | Downside 12% Gross Margin: Base 67% | Upside 70% | Downside 64% Operating Margin: Base 10% | Upside 14% | Downside 6% Expense Assumptions by Scenario: S&M as % of Revenue: Base 30% | Upside 25% | Downside 35% R&D as % of Revenue: Base 15% | Upside 14% | Downside 16% G&A as % of Revenue: Base 12% | Upside 10% | Downside 14% Working Capital by Scenario: DSO: Base 45 days | Upside 38 days | Downside 55 days DIO: Base 30 days | Upside 25 days | Downside 40 days DPO: Base 45 days | Upside 50 days | Downside 35 days Sensitivity Data Tables: Revenue CAGR × Gross Margin: 65% 67% 69% 15% $XM $XM $XM (Equity Value) 20% $XM $XM $XM (Base case = 20% × 67%) 25% $XM $XM $XM WACC × Terminal Growth: 2.0% 2.5% 3.0% 9.0% $XM $XM $XM (Enterprise Value) 10.5% $XM $XM $XM (Base case = 10.5% × 2.5%) 12.0% $XM $XM $XM ``` ## Edge Cases - **Hypergrowth companies** (>50% YoY): - Non-linear growth: Model distinct phases (ramp-up, steady-state, plateau) - Margin normalization: Early margins compressed; improve with scale (model margin expansion path) - Working capital: Negative working capital common (deferred revenue > AR) - Funding: Model equity raises as cash infusions (trigger at 12-month runway) - Risk: Single-point failure in growth assumption → stress-test with 50% growth reduction - **Negative cash flow / pre-profitability**: - Burn rate: Monthly net cash outflow (fixed + variable) - Runway: Current cash / monthly burn (model funding triggers) - Funding schedule: Series A at Month X, Series B at Month Y (with dilution impact) - Path to profitability: Model inflection point (Month X, Revenue $XM) - Breakeven analysis: Revenue needed to cover total costs (fixed + variable) - **Complex capital structures**: - Convertible debt: Model conversion trigger (price, time, redemption) - Options/warrants: Treasury stock method for diluted share count - Preferred shares: Liquidation preferences, dividends, conversion rights - Multiple equity classes: Common A, Common B, Preferred A, Preferred B - Pro-forma cap table: Post-money ownership by round (founder, employees, investors) - Fully diluted: Include all options, warrants, convertibles in share count - **Cyclical industries** (commodities, construction, semiconductors): - Economic cycle: Model boom/bust with capacity utilization swings - Inventory: Build-up in upcycle, write-down in downcycle - Capex: Counter-cyclical investment (buy in downcycle for upcycle demand) - Stress-test: 2008 financial crisis analog, 2020 pandemic shock - Hedging: Commodity hedges, interest rate swaps, FX forwards - **Distressed situations / turnaround**: - Liquidity analysis: Current assets / current liabilities (target > 1.5×) - Debt covenant tracking: Leverage ratio, interest coverage, fixed charge coverage - Restructuring: Chapter 11 scenarios, DIP financing, creditor negotiations - Asset liquidation: Fire-sale values vs. going-concern values - Employment: Severance costs, retention bonuses for key talent ## Integration Points - **Excel / Google Sheets**: Primary modeling platform; macros, data tables, scenario manager, Solver add-in - **ERP systems**: NetSuite, SAP, Oracle — historical financial data import, actuals comparison - **Market data APIs**: Bloomberg, S&P Capital IQ, FactSet — comparable company data, macro assumptions, industry benchmarks - **Valuation tools**: Wall Street Prep, Breaking Into Wall Street — pre-built DCF and LBO templates - **Planning platforms**: Anaplan, Adaptive Insights — scenario modeling, collaborative input - **BI tools**: Tableau, Power BI — model output visualization, dashboard creation - **Presentation tools**: PowerPoint, Google Slides — model output to executive summary charts - **Data rooms**: Intralinks, Firmex — model delivery for fundraising/M&A due diligence - **Python/R**: Advanced statistical modeling, Monte Carlo simulation, ML-based forecasting - **Collaboration**: Smartsheet, Airtable — assumption collection from multiple stakeholders