## Core Modeling Framework ### Phase 1: Model Specification ``` I need to build a financial model for: **Purpose:** [DCF valuation, budget forecast, scenario analysis, etc.] **Company/Project:** [Brief description, industry, stage] **Time Horizon:** [Forecast period: monthly, quarterly, annually] **Key Assumptions:** [Revenue growth, margins, discount rate, etc.] **Outputs Needed:** [NPV, IRR, sensitivity tables, charts, etc.] Please design the model structure: 1. **Tab Architecture** - List all worksheets needed (Assumptions, Income Statement, Balance Sheet, etc.) - Define data flow between tabs - Identify calculation dependencies 2. **Input Parameters** - Define all assumption cells with clear labels - Specify units, ranges, and validation rules - Recommend industry benchmarks if applicable 3. **Calculation Logic** - Outline key formulas for each section - Identify circular references to resolve - Suggest helper columns or intermediate calculations 4. **Outputs & Visualizations** - Define key metrics and KPIs - Recommend charts and dashboards - Specify conditional formatting rules ``` ### Phase 2: Formula Generation #### Three-Statement Model ``` Build a three-statement financial model with the following specifications: **Revenue Model:** - Multiple revenue streams: [List streams] - Growth rates: [By stream or blended] - Seasonality: [Monthly patterns if applicable] **Operating Expenses:** - COGS: [% of revenue or fixed] - SG&A: [Fixed costs + variable] - R&D: [Investment schedule] **Balance Sheet:** - Working capital assumptions (DSO, DIO, DPO) - Capex schedule and depreciation method - Debt schedule with interest rates and amortization **Cash Flow:** - Link to income statement and balance sheet - Calculate free cash flow to firm (FCFF) - Model cash sweeps and debt paydowns Generate Excel formulas for: 1. Revenue build-up (by year/quarter) 2. COGS and gross profit calculations 3. Operating expense schedule 4. Depreciation and amortization 5. Interest expense (average debt method) 6. Tax calculations 7. Working capital changes 8. Capex and asset roll-forward 9. Debt and equity schedule 10. Cash flow waterfall ``` #### DCF Valuation Model ``` Create a DCF valuation model with: **Projection Period:** [5-10 years] **Terminal Value Method:** [Gordon Growth or Exit Multiple] **WACC Components:** - Risk-free rate: [Current rate] - Equity risk premium: [Typical 5-7%] - Beta: [Industry or company-specific] - Cost of debt: [Current borrowing rate] - Tax rate: [Statutory rate] - Capital structure: [Target D/E ratio] Provide formulas for: 1. WACC calculation with components broken out 2. Free cash flow to firm (FCFF) for each year 3. Terminal value calculation 4. Present value of cash flows 5. Enterprise value 6. Equity value bridge (EV - Net Debt + Non-op assets) 7. Per-share value 8. Sensitivity table (WACC vs. growth rate) ``` #### Scenario Analysis & Sensitivity ``` Set up scenario analysis for: **Base Case Assumptions:** [List key assumptions] **Bear Case (-20% revenue, +10% costs):** [Adjusted assumptions] **Bull Case (+30% revenue, -5% costs):** [Adjusted assumptions] Create: 1. Scenario manager setup with named ranges 2. Data table for one-variable sensitivity (e.g., revenue growth) 3. Two-variable sensitivity table (e.g., revenue growth × margin) 4. Tornado chart to show impact of each assumption 5. Conditional formatting to highlight scenario outcomes ``` ### Phase 3: Automation & Error Checking #### Formula Audit ``` Review my financial model for common errors: [Paste formulas or upload Excel file] Check for: 1. **Circular References**: Identify and suggest resolution 2. **Hard-Coded Values**: Flag numbers that should be cell references 3. **Broken Links**: Find references to deleted cells or external files 4. **Inconsistent Formulas**: Detect formulas that differ across a row/column 5. **Division by Zero**: Add IFERROR() or IF() protections 6. **Missing Cell References**: Verify all inputs link to assumption cells 7. **Date Mismatches**: Check date calculations and fiscal period logic 8. **Unit Consistency**: Ensure all calculations use same units (thousands, millions) Generate a prioritized error report with fixes. ``` #### Excel VBA/Macros ``` Create a VBA macro to automate: **Task:** [Describe repetitive task] **Inputs:** [Where data comes from] **Process:** [Steps to automate] **Outputs:** [Where results go] Example tasks: - Consolidate data from multiple sheets/files - Generate monthly variance reports - Update dashboard charts automatically - Export specific sheets as PDF - Run scenario analysis and save results - Refresh pivot tables and external data connections Provide: 1. Complete VBA code with comments 2. Installation instructions 3. Error handling for edge cases 4. User instructions and documentation ``` #### Power Query Integration ``` Build a Power Query solution to: **Data Source:** [CSV, database, API, web scraping] **Transformation Steps:** 1. [Clean data, remove duplicates, etc.] 2. [Join/merge with other tables] 3. [Pivot or unpivot data] 4. [Calculate new columns] 5. [Filter and aggregate] **Output:** [Final table structure] Generate the M code for Power Query, including: - Connection setup - Step-by-step transformations - Parameterization for recurring refreshes - Error handling for missing data ``` ## Advanced Techniques ### Monte Carlo Simulation ``` Set up a Monte Carlo simulation to model uncertainty: **Variables to Randomize:** 1. Revenue growth: [Distribution: Normal, mean=X%, std dev=Y%] 2. COGS margin: [Distribution: Triangular, min/mode/max] 3. Capex: [Distribution: Uniform, range] **Simulation Parameters:** - Number of iterations: 10,000 - Random seed: [For reproducibility] - Correlation matrix: [If variables are correlated] **Output:** - Probability distribution of NPV/IRR - Percentile table (10th, 25th, 50th, 75th, 90th) - Risk metrics (Value at Risk, probability of negative return) - Histogram and cumulative distribution charts Provide Excel formulas using RAND(), NORM.INV(), or @RISK/Crystal Ball if available. ``` ### Cap Table & Waterfall Modeling ``` Build a capitalization table and waterfall analysis for: **Current Cap Table:** [List shareholders, share counts, preferences] **Financing Events:** - Series A: [$X million at $Y pre-money, Z% option pool] - Series B: [Subsequent rounds] **Exit Scenario:** [Exit valuation or range of valuations] Model: 1. Fully diluted share count at each round 2. Ownership % at each round (with anti-dilution adjustments) 3. Liquidation preferences and participation rights 4. Waterfall distribution by exit value 5. Investor returns (cash-on-cash, IRR) 6. Founder dilution tracker 7. Sensitivity: exit value vs. founder proceeds Provide formulas for each calculation step. ``` ### Budget vs. Actual Variance Analysis ``` Create an automated variance analysis report: **Budget Data:** [Link to budget file/sheet] **Actual Data:** [Link to actuals from accounting system] **Reporting Period:** [Monthly, Quarterly, YTD] Generate: 1. Variance table: Budget | Actual | $ Variance | % Variance 2. Conditional formatting: Red for unfavorable, green for favorable 3. Variance commentary template (populate for >10% variances) 4. Rolling forecast: Update budget based on actuals + revised assumptions 5. Charts: Budget vs Actual by category over time 6. Executive summary with top 5 variances Automate refresh when new actuals are imported. ``` ## Best Practices 1. **Separation of Inputs, Calculations, and Outputs**: Use dedicated tabs for each 2. **Named Ranges**: Define names for key cells to improve formula readability 3. **Color Coding**: Blue for inputs, black for formulas, green for links 4. **Documentation**: Include an instructions tab explaining model logic 5. **Version Control**: Date-stamp file names, maintain change log 6. **Scenario Manager**: Use Excel's built-in scenario tool for what-if analysis 7. **Data Validation**: Restrict input cells to valid ranges (no negative prices) 8. **Protect Worksheets**: Lock formula cells to prevent accidental changes ## Example Session **User**: "Build me a DCF model for a SaaS company with ARR of $10M growing at 30% YoY." **Opus Response**: > I'll build a comprehensive DCF model. Let's structure it: > > **Tab 1: Assumptions** > | Assumption | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | > |-----------|--------|--------|--------|--------|--------| > | ARR Growth Rate | 30% | 28% | 25% | 22% | 20% | > | Gross Margin | 75% | 76% | 77% | 78% | 80% | > | S&M (% of Revenue) | 45% | 42% | 40% | 38% | 35% | > | R&D (% of Revenue) | 20% | 18% | 17% | 16% | 15% | > | G&A (% of Revenue) | 15% | 13% | 12% | 11% | 10% | > > **Tab 2: Income Statement** > > ``` > A1: Year > B1: 0 (Current) > C1: 1 > ... > > A2: Revenue > B2: 10,000,000 [Input] > C2: =B2 * (1 + Assumptions!C2) [Links to growth rate] > > A3: COGS > C3: =C2 * (1 - Assumptions!C3) [1 minus gross margin] > > A4: Gross Profit > C4: =C2 - C3 > ... > ``` > > **WACC Calculation:** > | Component | Value | Formula | > |-----------|-------|---------| > | Risk-Free Rate | 4.0% | [10-yr Treasury] | > | Equity Risk Premium | 6.0% | [Historical avg] | > | Beta | 1.2 | [SaaS industry avg] | > | Cost of Equity | 11.2% | =RF + Beta × ERP | > | Cost of Debt | 6.0% | [Pre-tax] | > | Tax Rate | 25% | [Statutory] | > | After-Tax Cost of Debt | 4.5% | =CD × (1 - Tax) | > | Target D/E | 0% | [Typical for SaaS] | > | **WACC** | **11.2%** | =E% × Weight + D% × Weight | > > Shall I continue with the full model or would you like me to adjust any assumptions? ## Related Resources - [FAST Modeling Standard](https://www.fast-standard.org/) for best practices - [Macabacus](https://www.macabacus.com/) for Excel add-ins - [CFI Financial Modeling](https://corporatefinanceinstitute.com/resources/financial-modeling/) for templates - [Aswath Damodaran Data](https://pages.stern.nyu.edu/~adamodar/) for valuation inputs