1
Skills & Agents
Create Financial Models
---
SKILL.md
---
# Financial Modeling Suite
A comprehensive financial modeling toolkit for investment analysis, valuation, and risk assessment using industry-standard methodologies.
## Core Capabilities
### 1. Discounted Cash Flow (DCF) Analysis
- Build complete DCF models with multiple growth scenarios
- Calculate terminal values using perpetuity growth and exit multiple methods
- Determine weighted average cost of capital (WACC)
- Generate enterprise and equity valuations
### 2. Sensitivity Analysis
- Test key assumptions impact on valuation
- Create data tables for multiple variables
- Generate tornado charts for sensitivity ranking
- Identify critical value drivers
### 3. Monte Carlo Simulation
- Run thousands of scenarios with probability distributions
- Model uncertainty in key inputs
- Generate confidence intervals for valuations
- Calculate probability of achieving targets
### 4. Scenario Planning
- Build best/base/worst case scenarios
- Model different economic environments
- Test strategic alternatives
- Compare outcome probabilities
## Input Requirements
### For DCF Analysis
- Historical financial statements (3-5 years)
- Revenue growth assumptions
- Operating margin projections
- Capital expenditure forecasts
- Working capital requirements
- Terminal growth rate or exit multiple
- Discount rate components (risk-free rate, beta, market premium)
### For Sensitivity Analysis
- Base case model
- Variable ranges to test
- Key metrics to track
### For Monte Carlo Simulation
- Probability distributions for uncertain variables
- Correlation assumptions between variables
- Number of iterations (typically 1,000-10,000)
### For Scenario Planning
- Scenario definitions and assumptions
- Probability weights for scenarios
- Key performance indicators to track
## Output Formats
### DCF Model Output
- Complete financial projections
- Free cash flow calculations
- Terminal value computation
- Enterprise and equity value summary
- Valuation multiples implied
- Excel workbook with full model
### Sensitivity Analysis Output
- Sensitivity tables showing value ranges
- Tornado chart of key drivers
- Break-even analysis
- Charts showing relationships
### Monte Carlo Output
- Probability distribution of valuations
- Confidence intervals (e.g., 90%, 95%)
- Statistical summary (mean, median, std dev)
- Risk metrics (VaR, probability of loss)
### Scenario Planning Output
- Scenario comparison table
- Probability-weighted expected values
- Decision tree visualization
- Risk-return profiles
## Model Types Supported
1. **Corporate Valuation**
- Mature companies with stable cash flows
- Growth companies with J-curve projections
- Turnaround situations
2. **Project Finance**
- Infrastructure projects
- Real estate developments
- Energy projects
3. **M&A Analysis**
- Acquisition valuations
- Synergy modeling
- Accretion/dilution analysis
4. **LBO Models**
- Leveraged buyout analysis
- Returns analysis (IRR, MOIC)
- Debt capacity assessment
## Best Practices Applied
### Modeling Standards
- Consistent formatting and structure
- Clear assumption documentation
- Separation of inputs, calculations, outputs
- Error checking and validation
- Version control and change tracking
### Valuation Principles
- Use multiple valuation methods for triangulation
- Apply appropriate risk adjustments
- Consider market comparables
- Validate against trading multiples
- Document key assumptions clearly
### Risk Management
- Identify and quantify key risks
- Use probability-weighted scenarios
- Stress test extreme cases
- Consider correlation effects
- Provide confidence intervals
## Example Usage
"Build a DCF model for this technology company using the attached financials"
"Run a Monte Carlo simulation on this acquisition model with 5,000 iterations"
"Create sensitivity analysis showing impact of growth rate and WACC on valuation"
"Develop three scenarios for this expansion project with probability weights"
## Scripts Included
- `dcf_model.py`: Complete DCF valuation engine
- `sensitivity_analysis.py`: Sensitivity testing framework
## Limitations and Disclaimers
- Models are only as good as their assumptions
- Past performance doesn't guarantee future results
- Market conditions can change rapidly
- Regulatory and tax changes may impact results
- Professional judgment required for interpretation
- Not a substitute for professional financial advice
## Quality Checks
The model automatically performs:
1. Balance sheet balancing checks
2. Cash flow reconciliation
3. Circular reference resolution
4. Sensitivity bound checking
5. Statistical validation of Monte Carlo results
## Updates and Maintenance
- Models use latest financial theory and practices
- Regular updates for market parameter defaults
- Incorporation of regulatory changes
- Continuous improvement based on usage patterns
---
dcf_model.py
---
"""
Discounted Cash Flow (DCF) valuation model.
Implements enterprise valuation using free cash flow projections.
"""
import numpy as np
from typing import Dict, List, Any, Optional, Tuple
class DCFModel:
"""Build and calculate DCF valuation models."""
def __init__(self, company_name: str = "Company"):
"""
Initialize DCF model.
Args:
company_name: Name of the company being valued
"""
self.company_name = company_name
self.historical_financials = {}
self.projections = {}
self.assumptions = {}
self.wacc_components = {}
self.valuation_results = {}
def set_historical_financials(
self,
revenue: List[float],
ebitda: List[float],
capex: List[float],
nwc: List[float],
years: List[int]
):
"""
Set historical financial data.
Args:
revenue: Historical revenue
ebitda: Historical EBITDA
capex: Historical capital expenditure
nwc: Historical net working capital
years: Historical years
"""
self.historical_financials = {
'years': years,
'revenue': revenue,
'ebitda': ebitda,
'capex': capex,
'nwc': nwc,
'ebitda_margin': [ebitda[i]/revenue[i] for i in range(len(revenue))],
'capex_percent': [capex[i]/revenue[i] for i in range(len(revenue))]
}
def set_assumptions(
self,
projection_years: int = 5,
revenue_growth: List[float] = None,
ebitda_margin: List[float] = None,
tax_rate: float = 0.25,
capex_percent: List[float] = None,
nwc_percent: List[float] = None,
terminal_growth: float = 0.03
):
"""
Set projection assumptions.
Args:
projection_years: Number of years to project
revenue_growth: Annual revenue growth rates
ebitda_margin: EBITDA margins by year
tax_rate: Corporate tax rate
capex_percent: Capex as % of revenue
nwc_percent: NWC as % of revenue
terminal_growth: Terminal growth rate
"""
if revenue_growth is None:
revenue_growth = [0.10] * projection_years # Default 10% growth
if ebitda_margin is None:
# Use historical average if available
if self.historical_financials:
avg_margin = np.mean(self.historical_financials['ebitda_margin'])
ebitda_margin = [avg_margin] * projection_years
else:
ebitda_margin = [0.20] * projection_years # Default 20% margin
if capex_percent is None:
capex_percent = [0.05] * projection_years # Default 5% of revenue
if nwc_percent is None:
nwc_percent = [0.10] * projection_years # Default 10% of revenue
self.assumptions = {
'projection_years': projection_years,
'revenue_growth': revenue_growth,
'ebitda_margin': ebitda_margin,
'tax_rate': tax_rate,
'capex_percent': capex_percent,
'nwc_percent': nwc_percent,
'terminal_growth': terminal_growth
}
def calculate_wacc(
self,
risk_free_rate: float,
beta: float,
market_premium: float,
cost_of_debt: float,
debt_to_equity: float,
tax_rate: Optional[float] = None
) -> float:
"""
Calculate Weighted Average Cost of Capital (WACC).
Args:
risk_free_rate: Risk-free rate (e.g., 10-year treasury)
beta: Equity beta
market_premium: Equity market risk premium
cost_of_debt: Pre-tax cost of debt
debt_to_equity: Debt-to-equity ratio
tax_rate: Tax rate (uses assumption if not provided)
Returns:
WACC as decimal
"""
if tax_rate is None:
tax_rate = self.assumptions.get('tax_rate', 0.25)
# Calculate cost of equity using CAPM
cost_of_equity = risk_free_rate + beta * market_premium
# Calculate weights
equity_weight = 1 / (1 + debt_to_equity)
debt_weight = debt_to_equity / (1 + debt_to_equity)
# Calculate WACC
wacc = (equity_weight * cost_of_equity +
debt_weight * cost_of_debt * (1 - tax_rate))
self.wacc_components = {
'risk_free_rate': risk_free_rate,
'beta': beta,
'market_premium': market_premium,
'cost_of_equity': cost_of_equity,
'cost_of_debt': cost_of_debt,
'debt_to_equity': debt_to_equity,
'equity_weight': equity_weight,
'debt_weight': debt_weight,
'tax_rate': tax_rate,
'wacc': wacc
}
return wacc
def project_cash_flows(self) -> Dict[str, List[float]]:
"""
Project future cash flows based on assumptions.
Returns:
Dictionary with projected financials
"""
years = self.assumptions['projection_years']
# Start with last historical revenue if available
if self.historical_financials and 'revenue' in self.historical_financials:
base_revenue = self.historical_financials['revenue'][-1]
else:
base_revenue = 1000 # Default base
projections = {
'year': list(range(1, years + 1)),
'revenue': [],
'ebitda': [],
'ebit': [],
'tax': [],
'nopat': [],
'capex': [],
'nwc_change': [],
'fcf': []
}
prev_revenue = base_revenue
prev_nwc = base_revenue * 0.10 # Initial NWC assumption
for i in range(years):
# Revenue
revenue = prev_revenue * (1 + self.assumptions['revenue_growth'][i])
projections['revenue'].append(revenue)
# EBITDA
ebitda = revenue * self.assumptions['ebitda_margin'][i]
projections['ebitda'].append(ebitda)
# EBIT (assuming depreciation = capex for simplicity)
depreciation = revenue * self.assumptions['capex_percent'][i]
ebit = ebitda - depreciation
projections['ebit'].append(ebit)
# Tax
tax = ebit * self.assumptions['tax_rate']
projections['tax'].append(tax)
# NOPAT
nopat = ebit - tax
projections['nopat'].append(nopat)
# Capex
capex = revenue * self.assumptions['capex_percent'][i]
projections['capex'].append(capex)
# NWC change
nwc = revenue * self.assumptions['nwc_percent'][i]
nwc_change = nwc - prev_nwc
projections['nwc_change'].append(nwc_change)
# Free Cash Flow
fcf = nopat + depreciation - capex - nwc_change
projections['fcf'].append(fcf)
prev_revenue = revenue
prev_nwc = nwc
self.projections = projections
return projections
def calculate_terminal_value(
self,
method: str = 'growth',
exit_multiple: Optional[float] = None
) -> float:
"""
Calculate terminal value using perpetuity growth or exit multiple.
Args:
method: 'growth' for perpetuity growth, 'multiple' for exit multiple
exit_multiple: EV/EBITDA exit multiple (if using multiple method)
Returns:
Terminal value
"""
if not self.projections:
raise ValueError("Must project cash flows first")
if method == 'growth':
# Gordon growth model
final_fcf = self.projections['fcf'][-1]
terminal_growth = self.assumptions['terminal_growth']
wacc = self.wacc_components['wacc']
# FCF in terminal year
terminal_fcf = final_fcf * (1 + terminal_growth)
# Terminal value
terminal_value = terminal_fcf / (wacc - terminal_growth)
elif method == 'multiple':
if exit_multiple is None:
exit_multiple = 10 # Default EV/EBITDA multiple
final_ebitda = self.projections['ebitda'][-1]
terminal_value = final_ebitda * exit_multiple
else:
raise ValueError("Method must be 'growth' or 'multiple'")
return terminal_value
def calculate_enterprise_value(
self,
terminal_method: str = 'growth',
exit_multiple: Optional[float] = None
) -> Dict[str, Any]:
"""
Calculate enterprise value by discounting cash flows.
Args:
terminal_method: Method for terminal value calculation
exit_multiple: Exit multiple if using multiple method
Returns:
Valuation results dictionary
"""
if not self.projections:
self.project_cash_flows()
if 'wacc' not in self.wacc_components:
raise ValueError("Must calculate WACC first")
wacc = self.wacc_components['wacc']
years = self.assumptions['projection_years']
# Calculate PV of projected cash flows
pv_fcf = []
for i, fcf in enumerate(self.projections['fcf']):
discount_factor = (1 + wacc) ** (i + 1)
pv = fcf / discount_factor
pv_fcf.append(pv)
total_pv_fcf = sum(pv_fcf)
# Calculate terminal value
terminal_value = self.calculate_terminal_value(terminal_method, exit_multiple)
# Discount terminal value
terminal_discount = (1 + wacc) ** years
pv_terminal = terminal_value / terminal_discount
# Enterprise value
enterprise_value = total_pv_fcf + pv_terminal
self.valuation_results = {
'enterprise_value': enterprise_value,
'pv_fcf': total_pv_fcf,
'pv_terminal': pv_terminal,
'terminal_value': terminal_value,
'terminal_method': terminal_method,
'pv_fcf_detail': pv_fcf,
'terminal_percent': pv_terminal / enterprise_value * 100
}
return self.valuation_results
def calculate_equity_value(
self,
net_debt: float,
cash: float = 0,
shares_outstanding: float = 100
) -> Dict[str, Any]:
"""
Calculate equity value from enterprise value.
Args:
net_debt: Total debt minus cash
cash: Cash and equivalents (if not netted)
shares_outstanding: Number of shares (millions)
Returns:
Equity valuation metrics
"""
if 'enterprise_value' not in self.valuation_results:
raise ValueError("Must calculate enterprise value first")
ev = self.valuation_results['enterprise_value']
# Equity value = EV - Net Debt
equity_value = ev - net_debt + cash
# Per share value
value_per_share = equity_value / shares_outstanding if shares_outstanding > 0 else 0
equity_results = {
'equity_value': equity_value,
'shares_outstanding': shares_outstanding,
'value_per_share': value_per_share,
'net_debt': net_debt,
'cash': cash
}
self.valuation_results.update(equity_results)
return equity_results
def sensitivity_analysis(
self,
variable1: str,
range1: List[float],
variable2: str,
range2: List[float]
) -> np.ndarray:
"""
Perform two-way sensitivity analysis on valuation.
Args:
variable1: First variable to test ('wacc', 'growth', 'margin')
range1: Range of values for variable1
variable2: Second variable to test
range2: Range of values for variable2
Returns:
2D array of valuations
"""
results = np.zeros((len(range1), len(range2)))
# Store original values
orig_wacc = self.wacc_components.get('wacc', 0.10)
orig_growth = self.assumptions.get('terminal_growth', 0.03)
orig_margin = self.assumptions.get('ebitda_margin', [0.20] * 5)
for i, val1 in enumerate(range1):
for j, val2 in enumerate(range2):
# Update first variable
if variable1 == 'wacc':
self.wacc_components['wacc'] = val1
elif variable1 == 'growth':
self.assumptions['terminal_growth'] = val1
elif variable1 == 'margin':
self.assumptions['ebitda_margin'] = [val1] * len(orig_margin)
# Update second variable
if variable2 == 'wacc':
self.wacc_components['wacc'] = val2
elif variable2 == 'growth':
self.assumptions['terminal_growth'] = val2
elif variable2 == 'margin':
self.assumptions['ebitda_margin'] = [val2] * len(orig_margin)
# Recalculate
self.project_cash_flows()
valuation = self.calculate_enterprise_value()
results[i, j] = valuation['enterprise_value']
# Restore original values
self.wacc_components['wacc'] = orig_wacc
self.assumptions['terminal_growth'] = orig_growth
self.assumptions['ebitda_margin'] = orig_margin
return results
def generate_summary(self) -> str:
"""
Generate text summary of valuation results.
Returns:
Formatted summary string
"""
if not self.valuation_results:
return "No valuation results available. Run valuation first."
summary = [
f"DCF Valuation Summary - {self.company_name}",
"=" * 50,
"",
"Key Assumptions:",
f" Projection Period: {self.assumptions['projection_years']} years",
f" Revenue Growth: {np.mean(self.assumptions['revenue_growth'])*100:.1f}% avg",
f" EBITDA Margin: {np.mean(self.assumptions['ebitda_margin'])*100:.1f}% avg",
f" Terminal Growth: {self.assumptions['terminal_growth']*100:.1f}%",
f" WACC: {self.wacc_components['wacc']*100:.1f}%",
"",
"Valuation Results:",
f" Enterprise Value: ${self.valuation_results['enterprise_value']:,.0f}M",
f" PV of FCF: ${self.valuation_results['pv_fcf']:,.0f}M",
f" PV of Terminal: ${self.valuation_results['pv_terminal']:,.0f}M",
f" Terminal % of Value: {self.valuation_results['terminal_percent']:.1f}%",
""
]
if 'equity_value' in self.valuation_results:
summary.extend([
"Equity Valuation:",
f" Equity Value: ${self.valuation_results['equity_value']:,.0f}M",
f" Shares Outstanding: {self.valuation_results['shares_outstanding']:.0f}M",
f" Value per Share: ${self.valuation_results['value_per_share']:.2f}",
""
])
return "\n".join(summary)
# Helper functions for common calculations
def calculate_beta(
stock_returns: List[float],
market_returns: List[float]
) -> float:
"""
Calculate beta from return series.
Args:
stock_returns: Historical stock returns
market_returns: Historical market returns
Returns:
Beta coefficient
"""
covariance = np.cov(stock_returns, market_returns)[0, 1]
market_variance = np.var(market_returns)
beta = covariance / market_variance if market_variance != 0 else 1.0
return beta
def calculate_fcf_cagr(fcf_series: List[float]) -> float:
"""
Calculate compound annual growth rate of FCF.
Args:
fcf_series: Free cash flow time series
Returns:
CAGR as decimal
"""
if len(fcf_series) < 2:
return 0
years = len(fcf_series) - 1
if fcf_series[0] <= 0 or fcf_series[-1] <= 0:
return 0
cagr = (fcf_series[-1] / fcf_series[0]) ** (1 / years) - 1
return cagr
# Example usage
if __name__ == "__main__":
# Create model
model = DCFModel("TechCorp")
# Set historical data
model.set_historical_financials(
revenue=[800, 900, 1000],
ebitda=[160, 189, 220],
capex=[40, 45, 50],
nwc=[80, 90, 100],
years=[2022, 2023, 2024]
)
# Set assumptions
model.set_assumptions(
projection_years=5,
revenue_growth=[0.15, 0.12, 0.10, 0.08, 0.06],
ebitda_margin=[0.23, 0.24, 0.25, 0.25, 0.25],
tax_rate=0.25,
terminal_growth=0.03
)
# Calculate WACC
model.calculate_wacc(
risk_free_rate=0.04,
beta=1.2,
market_premium=0.07,
cost_of_debt=0.05,
debt_to_equity=0.5
)
# Project cash flows
model.project_cash_flows()
# Calculate valuation
model.calculate_enterprise_value()
# Calculate equity value
model.calculate_equity_value(net_debt=200, shares_outstanding=50)
# Print summary
print(model.generate_summary())
---
sensitivity_analysis.py
---
"""
Sensitivity analysis module for financial models.
Tests impact of variable changes on key outputs.
"""
import numpy as np
import pandas as pd
from typing import Dict, List, Any, Tuple, Callable
class SensitivityAnalyzer:
"""Perform sensitivity analysis on financial models."""
def __init__(self, base_model: Any):
"""
Initialize sensitivity analyzer.
Args:
base_model: Base financial model to analyze
"""
self.base_model = base_model
self.base_output = None
self.sensitivity_results = {}
def one_way_sensitivity(
self,
variable_name: str,
base_value: float,
range_pct: float,
steps: int,
output_func: Callable,
model_update_func: Callable
) -> pd.DataFrame:
"""
Perform one-way sensitivity analysis.
Args:
variable_name: Name of variable to test
base_value: Base case value
range_pct: +/- percentage range to test
steps: Number of steps in range
output_func: Function to calculate output metric
model_update_func: Function to update model with new value
Returns:
DataFrame with sensitivity results
"""
# Calculate range
min_val = base_value * (1 - range_pct)
max_val = base_value * (1 + range_pct)
test_values = np.linspace(min_val, max_val, steps)
results = []
for value in test_values:
# Update model
model_update_func(value)
# Calculate output
output = output_func()
results.append({
'variable': variable_name,
'value': value,
'pct_change': (value - base_value) / base_value * 100,
'output': output,
'output_change': output - self.base_output if self.base_output else 0
})
# Reset to base
model_update_func(base_value)
return pd.DataFrame(results)
def two_way_sensitivity(
self,
var1_name: str,
var1_base: float,
var1_range: List[float],
var2_name: str,
var2_base: float,
var2_range: List[float],
output_func: Callable,
model_update_func: Callable
) -> pd.DataFrame:
"""
Perform two-way sensitivity analysis.
Args:
var1_name: First variable name
var1_base: First variable base value
var1_range: Range of values for first variable
var2_name: Second variable name
var2_base: Second variable base value
var2_range: Range of values for second variable
output_func: Function to calculate output
model_update_func: Function to update model (takes var1, var2)
Returns:
DataFrame with two-way sensitivity table
"""
results = np.zeros((len(var1_range), len(var2_range)))
for i, val1 in enumerate(var1_range):
for j, val2 in enumerate(var2_range):
# Update both variables
model_update_func(val1, val2)
# Calculate output
results[i, j] = output_func()
# Reset to base
model_update_func(var1_base, var2_base)
# Create DataFrame
df = pd.DataFrame(
results,
index=[f"{var1_name}={v:.2%}" if v < 1 else f"{var1_name}={v:.1f}"
for v in var1_range],
columns=[f"{var2_name}={v:.2%}" if v < 1 else f"{var2_name}={v:.1f}"
for v in var2_range]
)
return df
def tornado_analysis(
self,
variables: Dict[str, Dict[str, Any]],
output_func: Callable
) -> pd.DataFrame:
"""
Create tornado diagram data showing relative impact of variables.
Args:
variables: Dictionary of variables with base, low, high values
output_func: Function to calculate output
Returns:
DataFrame sorted by impact magnitude
"""
# Store base output
self.base_output = output_func()
tornado_data = []
for var_name, var_info in variables.items():
# Test low value
var_info['update_func'](var_info['low'])
low_output = output_func()
# Test high value
var_info['update_func'](var_info['high'])
high_output = output_func()
# Reset to base
var_info['update_func'](var_info['base'])
# Calculate impact
impact = high_output - low_output
low_delta = low_output - self.base_output
high_delta = high_output - self.base_output
tornado_data.append({
'variable': var_name,
'base_value': var_info['base'],
'low_value': var_info['low'],
'high_value': var_info['high'],
'low_output': low_output,
'high_output': high_output,
'low_delta': low_delta,
'high_delta': high_delta,
'impact': abs(impact),
'impact_pct': abs(impact) / self.base_output * 100
})
# Sort by impact
df = pd.DataFrame(tornado_data)
df = df.sort_values('impact', ascending=False)
return df
def scenario_analysis(
self,
scenarios: Dict[str, Dict[str, float]],
variable_updates: Dict[str, Callable],
output_func: Callable,
probability_weights: Optional[Dict[str, float]] = None
) -> pd.DataFrame:
"""
Analyze multiple scenarios with different variable combinations.
Args:
scenarios: Dictionary of scenarios with variable values
variable_updates: Functions to update each variable
output_func: Function to calculate output
probability_weights: Optional probability for each scenario
Returns:
DataFrame with scenario results
"""
results = []
for scenario_name, variables in scenarios.items():
# Update all variables for this scenario
for var_name, value in variables.items():
if var_name in variable_updates:
variable_updates[var_name](value)
# Calculate output
output = output_func()
# Get probability if provided
prob = probability_weights.get(scenario_name, 1/len(scenarios)) \
if probability_weights else 1/len(scenarios)
results.append({
'scenario': scenario_name,
'probability': prob,
'output': output,
**variables # Include all variable values
})
# Reset model (simplified - should restore all base values)
df = pd.DataFrame(results)
# Calculate expected value
df['weighted_output'] = df['output'] * df['probability']
expected_value = df['weighted_output'].sum()
# Add summary row
summary = pd.DataFrame([{
'scenario': 'Expected Value',
'probability': 1.0,
'output': expected_value,
'weighted_output': expected_value
}])
df = pd.concat([df, summary], ignore_index=True)
return df
def breakeven_analysis(
self,
variable_name: str,
variable_update: Callable,
output_func: Callable,
target_value: float,
min_search: float,
max_search: float,
tolerance: float = 0.01
) -> float:
"""
Find breakeven point where output equals target.
Args:
variable_name: Variable to adjust
variable_update: Function to update variable
output_func: Function to calculate output
target_value: Target output value
min_search: Minimum search range
max_search: Maximum search range
tolerance: Convergence tolerance
Returns:
Breakeven value of variable
"""
# Binary search for breakeven
low = min_search
high = max_search
while (high - low) > tolerance:
mid = (low + high) / 2
variable_update(mid)
output = output_func()
if abs(output - target_value) < tolerance:
return mid
elif output < target_value:
low = mid
else:
high = mid
return (low + high) / 2
def create_data_table(
row_variable: Tuple[str, List[float], Callable],
col_variable: Tuple[str, List[float], Callable],
output_func: Callable
) -> pd.DataFrame:
"""
Create Excel-style data table for two variables.
Args:
row_variable: (name, values, update_function)
col_variable: (name, values, update_function)
output_func: Function to calculate output
Returns:
DataFrame formatted as data table
"""
row_name, row_values, row_update = row_variable
col_name, col_values, col_update = col_variable
results = np.zeros((len(row_values), len(col_values)))
for i, row_val in enumerate(row_values):
for j, col_val in enumerate(col_values):
row_update(row_val)
col_update(col_val)
results[i, j] = output_func()
df = pd.DataFrame(
results,
index=pd.Index(row_values, name=row_name),
columns=pd.Index(col_values, name=col_name)
)
return df
# Example usage
if __name__ == "__main__":
# Mock model for demonstration
class SimpleModel:
def __init__(self):
self.revenue = 1000
self.margin = 0.20
self.multiple = 10
def calculate_value(self):
ebitda = self.revenue * self.margin
return ebitda * self.multiple
# Create model and analyzer
model = SimpleModel()
analyzer = SensitivityAnalyzer(model)
# One-way sensitivity
results = analyzer.one_way_sensitivity(
variable_name="Revenue",
base_value=model.revenue,
range_pct=0.20,
steps=5,
output_func=model.calculate_value,
model_update_func=lambda x: setattr(model, 'revenue', x)
)
print("One-Way Sensitivity Analysis:")
print(results)
# Tornado analysis
variables = {
'Revenue': {
'base': 1000,
'low': 800,
'high': 1200,
'update_func': lambda x: setattr(model, 'revenue', x)
},
'Margin': {
'base': 0.20,
'low': 0.15,
'high': 0.25,
'update_func': lambda x: setattr(model, 'margin', x)
},
'Multiple': {
'base': 10,
'low': 8,
'high': 12,
'update_func': lambda x: setattr(model, 'multiple', x)
}
}
tornado = analyzer.tornado_analysis(variables, model.calculate_value)
print("\nTornado Analysis:")
print(tornado[['variable', 'impact', 'impact_pct']])