Financial Model Builder

You are a financial analyst. Build a comprehensive financial model.

**Business:** {business_description}
**Model Purpose:** {purpose}
**Time Horizon:** {years} years

Build a three-statement financial model:

**1. Model Assumptions:**

**Revenue Assumptions:**
- Starting revenue: ${starting_revenue}
- Growth rate: {growth_rate}% annually
- Revenue streams:
  - {stream_1}: {percentage_1}% of total
  - {stream_2}: {percentage_2}% of total
  - {stream_3}: {percentage_3}% of total

**Cost Assumptions:**
- COGS as % of revenue: {cogs_percentage}%
- Operating expenses growth: {opex_growth}%
- Fixed costs: ${fixed_costs}/year
- Variable costs: {variable_percentage}% of revenue

**Capital Structure:**
- Initial capital: ${initial_capital}
- Debt: ${debt_amount} at {interest_rate}% interest
- Depreciation: {depreciation_method} over {depreciation_years} years

**2. Income Statement Projection:**

| Year | Y1 | Y2 | Y3 | Y4 | Y5 |
|------|----|----|----|----|-----|
| **Revenue** | | | | | |
| {stream_1} | =Y1_rev*{pct_1} | =Y2_rev*{pct_1} | | | |
| {stream_2} | =Y1_rev*{pct_2} | =Y2_rev*{pct_2} | | | |
| {stream_3} | =Y1_rev*{pct_3} | =Y2_rev*{pct_3} | | | |
| **Total Revenue** | ={starting_revenue} | =Y1*(1+{growth_rate}) | | | |
| | | | | | |
| **Cost of Goods Sold** | =Revenue*{cogs_percentage} | | | | |
| **Gross Profit** | =Revenue-COGS | | | | |
| **Gross Margin %** | =Gross Profit/Revenue | | | | |
| | | | | | |
| **Operating Expenses** | | | | | |
| Salaries | ={salaries} | =Y1*(1+{growth}) | | | |
| Marketing | ={marketing} | =Y1*(1+{growth}) | | | |
| R&D | ={rd} | =Y1*(1+{growth}) | | | |
| G&A | ={ga} | =Y1*(1+{growth}) | | | |
| **Total OpEx** | =SUM(above) | | | | |
| | | | | | |
| **EBITDA** | =Gross Profit-OpEx | | | | |
| Depreciation | ={depreciation} | | | | |
| **EBIT** | =EBITDA-Depreciation | | | | |
| Interest Expense | =Debt*{interest_rate} | | | | |
| **EBT** | =EBIT-Interest | | | | |
| Taxes | =EBT*{tax_rate} | | | | |
| **Net Income** | =EBT-Taxes | | | | |
| | | | | | |
| **Net Margin %** | =Net Income/Revenue | | | | |

**3. Balance Sheet Projection:**

| Year | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 |
|------|----|----|----|----|-----|-----|
| **Assets** | | | | | | |
| Cash | ={starting_cash} | =Y0+CF_from_ops | | | | |
| Accounts Receivable | =Revenue/12 | | | | | |
| Inventory | =COGS*{inventory_days}/365 | | | | | |
| **Current Assets** | =SUM(above) | | | | | |
| | | | | | | |
| PP&E Gross | ={ppe_gross} | =Y0+CapEx | | | | |
| Accumulated Depreciation | =0 | =Y0+Depreciation | | | | |
| PP&E Net | =Gross-Accum_Dep | | | | | |
| **Total Assets** | =Current+PPE | | | | | |
| | | | | | | |
| **Liabilities** | | | | | | |
| Accounts Payable | =COGS*{payable_days}/365 | | | | | |
| Current Debt | ={current_debt} | | | | | |
| **Current Liabilities** | =SUM(above) | | | | | |
| | | | | | | |
| Long-term Debt | ={lt_debt} | =Y0-Debt_payments | | | | |
| **Total Liabilities** | =Current+LT | | | | | |
| | | | | | | |
| **Equity** | | | | | | |
| Common Stock | ={common_stock} | | | | | |
| Retained Earnings | =0 | =Y0+Net_Income-Dividends | | | | |
| **Total Equity** | =Stock+RE | | | | | |
| | | | | | | |
| **Total Liab + Equity** | =Liab+Equity | | | | | |

**Check:** Total Assets = Total Liab + Equity

**4. Cash Flow Statement Projection:**

| Year | Y1 | Y2 | Y3 | Y4 | Y5 |
|------|----|----|----|----|-----|
| **Operating Activities** | | | | | |
| Net Income | =From_IS | | | | |
| Add: Depreciation | =From_IS | | | | |
| Change in AR | =-(Y1_AR-Y0_AR) | | | | |
| Change in Inventory | =-(Y1_Inv-Y0_Inv) | | | | |
| Change in AP | =(Y1_AP-Y0_AP) | | | | |
| **Cash from Operations** | =SUM(above) | | | | |
| | | | | | |
| **Investing Activities** | | | | | |
| CapEx | =-{capex} | | | | |
| **Cash from Investing** | =CapEx | | | | |
| | | | | | |
| **Financing Activities** | | | | | |
| Debt Issuance/(Repayment) | ={debt_change} | | | | |
| Equity Issuance | ={equity_issuance} | | | | |
| Dividends | =-{dividends} | | | | |
| **Cash from Financing** | =SUM(above) | | | | |
| | | | | | |
| **Net Change in Cash** | =Ops+Inv+Fin | | | | |
| Beginning Cash | =From_BS_Y0 | | | | |
| **Ending Cash** | =Beginning+Change | | | | |

**5. Key Metrics & Ratios:**

| Metric | Y1 | Y2 | Y3 | Y4 | Y5 |
|--------|----|----|----|----|-----|
| **Profitability** | | | | | |
| Gross Margin | =Gross_Profit/Revenue | | | | |
| EBITDA Margin | =EBITDA/Revenue | | | | |
| Net Margin | =Net_Income/Revenue | | | | |
| ROE | =Net_Income/Avg_Equity | | | | |
| ROA | =Net_Income/Avg_Assets | | | | |
| | | | | | |
| **Liquidity** | | | | | |
| Current Ratio | =Current_Assets/Current_Liab | | | | |
| Quick Ratio | =(Current_Assets-Inventory)/Current_Liab | | | | |
| Cash Ratio | =Cash/Current_Liab | | | | |
| | | | | | |
| **Leverage** | | | | | |
| Debt-to-Equity | =Total_Debt/Total_Equity | | | | |
| Debt-to-Assets | =Total_Debt/Total_Assets | | | | |
| Interest Coverage | =EBIT/Interest_Expense | | | | |
| | | | | | |
| **Efficiency** | | | | | |
| Asset Turnover | =Revenue/Avg_Assets | | | | |
| Days Sales Outstanding | =AR/(Revenue/365) | | | | |
| Days Inventory Outstanding | =Inventory/(COGS/365) | | | | |
| Days Payable Outstanding | =AP/(COGS/365) | | | | |
| Cash Conversion Cycle | =DSO+DIO-DPO | | | | |

**6. Scenario Analysis:**

| Scenario | Revenue Growth | COGS % | OpEx Growth | Net Income Y5 |
|----------|---------------|--------|-------------|---------------|
| **Base Case** | {base_growth}% | {base_cogs}% | {base_opex}% | ${base_ni} |
| **Optimistic** | {opt_growth}% | {opt_cogs}% | {opt_opex}% | ${opt_ni} |
| **Pessimistic** | {pess_growth}% | {pess_cogs}% | {pess_opex}% | ${pess_ni} |

**7. Valuation (DCF Method):**

**Free Cash Flow Calculation:**
```
FCF = EBIT * (1 - Tax Rate)
      + Depreciation
      - CapEx
      - Change in NWC
```

| Year | Y1 | Y2 | Y3 | Y4 | Y5 | Terminal |
|------|----|----|----|----|-----|----------|
| FCF | ${fcf_1} | ${fcf_2} | ${fcf_3} | ${fcf_4} | ${fcf_5} | ${terminal_value} |
| Discount Factor | =1/(1+WACC)^1 | | | | | |
| PV of FCF | =FCF*DF | | | | | |

**WACC Calculation:**
- Cost of Equity (CAPM): {cost_of_equity}%
- Cost of Debt (after-tax): {cost_of_debt}%
- WACC: {wacc}%

**Terminal Value:**
- Method: Perpetuity Growth
- Terminal Growth Rate: {terminal_growth}%
- Terminal Value = FCF_Y5 * (1 + g) / (WACC - g)

**Enterprise Value:** =SUM(PV of FCFs) + PV(Terminal Value)
**Equity Value:** =EV - Net Debt
**Equity Value per Share:** =Equity Value / Shares Outstanding

**8. Sensitivity Analysis:**

**NPV Sensitivity to WACC and Growth:**

| WACC →<br>Growth ↓ | 8% | 10% | 12% | 14% | 16% |
|---------------------|-----|-----|-----|-----|-----|
| 2% | ${npv_8_2} | ${npv_10_2} | ${npv_12_2} | ${npv_14_2} | ${npv_16_2} |
| 3% | ${npv_8_3} | ${npv_10_3} | ${npv_12_3} | ${npv_14_3} | ${npv_16_3} |
| 4% | ${npv_8_4} | ${npv_10_4} | ${npv_12_4} | ${npv_14_4} | ${npv_16_4} |
| 5% | ${npv_8_5} | ${npv_10_5} | ${npv_12_5} | ${npv_14_5} | ${npv_16_5} |

**9. Charts & Visualizations:**

Create:
- Revenue & Net Income over time (line chart)
- Gross Margin trend (line chart)
- Cash flow waterfall (waterfall chart)
- Balance sheet composition (stacked bar)
- Scenario comparison (clustered bar)

**10. Model Documentation:**

**Key Assumptions:**
- {assumption_1}
- {assumption_2}
- {assumption_3}

**Limitations:**
- {limitation_1}
- {limitation_2}

**Recommendations:**
- {recommendation_1}
- {recommendation_2}

Provide: Complete Excel model + scenarios + valuation + documentation.