Financial Analyst Skill

Overview

Production-ready financial analysis toolkit providing ratio analysis, DCF valuation, budget variance analysis, and rolling forecast construction. Designed for financial modeling, forecasting & budgeting, management reporting, business performance analysis, and investment analysis.

5-Phase Workflow

Phase 1: Scoping

• Define analysis objectives and stakeholder requirements
• Identify data sources and time periods
• Establish materiality thresholds and accuracy targets
• Select appropriate analytical frameworks

Phase 2: Data Analysis & Modeling

• Collect and validate financial data (income statement, balance sheet, cash flow)
• Validate input data completeness before running ratio calculations (check for missing fields, nulls, or implausible values)
• Calculate financial ratios across 5 categories (profitability, liquidity, leverage, efficiency, valuation)
• Build DCF models with WACC and terminal value calculations; cross-check DCF outputs against sanity bounds (e.g., implied multiples vs. comparables)
• Construct budget variance analyses with favorable/unfavorable classification
• Develop driver-based forecasts with scenario modeling

Phase 3: Insight Generation

• Interpret ratio trends and benchmark against industry standards
• Identify material variances and root causes
• Assess valuation ranges through sensitivity analysis
• Evaluate forecast scenarios (base/bull/bear) for decision support

Phase 4: Reporting

• Generate executive summaries with key findings
• Produce detailed variance reports by department and category
• Deliver DCF valuation reports with sensitivity tables
• Present rolling forecasts with trend analysis

Phase 5: Follow-up

• Track forecast accuracy (target: +/-5% revenue, +/-3% expenses)
• Monitor report delivery timeliness (target: 100% on time)
• Update models with actuals as they become available
• Refine assumptions based on variance analysis

Tools

1. Ratio Calculator (scripts/ratio_calculator.py)

Calculate and interpret financial ratios from financial statement data.

Ratio Categories:

• Profitability: ROE, ROA, Gross Margin, Operating Margin, Net Margin
• Liquidity: Current Ratio, Quick Ratio, Cash Ratio
• Leverage: Debt-to-Equity, Interest Coverage, DSCR
• Efficiency: Asset Turnover, Inventory Turnover, Receivables Turnover, DSO
• Valuation: P/E, P/B, P/S, EV/EBITDA, PEG Ratio

python scripts/ratio_calculator.py sample_financial_data.json
python scripts/ratio_calculator.py sample_financial_data.json --format json
python scripts/ratio_calculator.py sample_financial_data.json --category profitability

2. DCF Valuation (scripts/dcf_valuation.py)

Discounted Cash Flow enterprise and equity valuation with sensitivity analysis.

Features:

• WACC calculation via CAPM
• Revenue and free cash flow projections (5-year default)
• Terminal value via perpetuity growth and exit multiple methods
• Enterprise value and equity value derivation
• Two-way sensitivity analysis (discount rate vs growth rate)

python scripts/dcf_valuation.py valuation_data.json
python scripts/dcf_valuation.py valuation_data.json --format json
python scripts/dcf_valuation.py valuation_data.json --projection-years 7

3. Budget Variance Analyzer (scripts/budget_variance_analyzer.py)

Analyze actual vs budget vs prior year performance with materiality filtering.

Features:

• Dollar and percentage variance calculation
• Materiality threshold filtering (default: 10% or $50K)
• Favorable/unfavorable classification with revenue/expense logic
• Department and category breakdown
• Executive summary generation

python scripts/budget_variance_analyzer.py budget_data.json
python scripts/budget_variance_analyzer.py budget_data.json --format json
python scripts/budget_variance_analyzer.py budget_data.json --threshold-pct 5 --threshold-amt 25000

4. Forecast Builder (scripts/forecast_builder.py)

Driver-based revenue forecasting with rolling cash flow projection and scenario modeling.

Features:

• Driver-based revenue forecast model
• 13-week rolling cash flow projection
• Scenario modeling (base/bull/bear cases)
• Trend analysis using simple linear regression (standard library)

python scripts/forecast_builder.py forecast_data.json
python scripts/forecast_builder.py forecast_data.json --format json
python scripts/forecast_builder.py forecast_data.json --scenarios base,bull,bear

5. Monte Carlo Simulator (scripts/monte_carlo_simulator.py)

Run N iterations (default 1000) with randomized assumptions to produce probability distributions for NPV, IRR, and payback period.

Features:

• Triangular distribution sampling (min/base/max for each assumption)
• NPV distribution with percentiles (P5, P10, P25, P50, P75, P90, P95)
• IRR distribution with annualized rates
• Payback period distribution
• Probability of break-even and positive NPV
• Value at Risk (5th percentile)
• Built-in Go/No-Go decision signal (Green/Yellow/Red)

python scripts/monte_carlo_simulator.py simulation_data.json
python scripts/monte_carlo_simulator.py simulation_data.json --iterations 5000
python scripts/monte_carlo_simulator.py simulation_data.json --format json
python scripts/monte_carlo_simulator.py simulation_data.json --seed 42

7. Project Payback Model (scripts/project_payback.py)

单项目投资回收分析——回答"投多少、多久回本、之后赚多少"。

Features:

• 月度现金流逐期计算（含ramp-up爬坡期）
• Break-even月份精确定位
• 项目级IRR和NPV（按月折现）
• 累计回报曲线（每6月一个数据点）
• 内置Go/No-Go信号灯（NPV/IRR/回收期三重判断）
• 自动前置"一句话版本"叙事总结

python scripts/project_payback.py project_data.json
python scripts/project_payback.py project_data.json --format json
python scripts/project_payback.py project_data.json --months 60

Go/No-Go信号灯逻辑：

• 🟢 GREEN：NPV > 0 且 IRR > hurdle rate 且 回收期 < 设备寿命50%
• 🟡 YELLOW：IRR接近但低于hurdle rate，或回收期 > 寿命50%
• 🔴 RED：NPV < 0，或回收期接近/超过设备寿命，或IRR远低于hurdle

8. Assumption Flip Test (scripts/assumption_flip_test.py)

假设翻转测试——逐一压力测试每个假设，找到"致命假设"（恶化≤30%就翻转结论的那个）。

Features:

• 逐个假设独立压力测试（10%/20%/30%/40%/50%）
• 自动识别收入侧（做减法）vs 成本侧（做加法）
• 找到每个假设的"翻转阈值"
• 标注致命假设（翻转阈值≤30%）
• 生成投委会建议（0个致命→稳健 / 1个→重点验证 / 多个→缩小试点）

python scripts/assumption_flip_test.py project_data.json
python scripts/assumption_flip_test.py project_data.json --format json
python scripts/assumption_flip_test.py project_data.json --stress 0.3

Input format:

{
    "initial_investment": 2000000,
    "projection_months": 36,
    "assumptions": {
        "monthly_revenue": {"min": 30000, "base": 50000, "max": 80000},
        "monthly_cost": {"min": 15000, "base": 25000, "max": 40000},
        "growth_rate_monthly": {"min": 0.01, "base": 0.03, "max": 0.06},
        "churn_rate_monthly": {"min": 0.01, "base": 0.03, "max": 0.05}
    },
    "discount_rate_annual": 0.10
}

6. Excel Report Exporter (scripts/excel_exporter.py)

Export any analysis result to a structured, McKinsey-styled Excel workbook (.xlsx) for investment committee review.

Features:

• McKinsey color scheme (deep blue headers, white text)
• Auto-adjusted column widths
• Supports multiple report types: DCF, Monte Carlo, Forecast, Ratios
• Sensitivity tables with proper formatting
• Decision signal color coding (Green/Yellow/Red)
• CSV fallback if openpyxl is not installed

Dependency: openpyxl (optional — falls back to CSV if not available)

# Install openpyxl for full Excel support
pip install openpyxl

# Export DCF results
python scripts/excel_exporter.py dcf_results.json --output valuation_report.xlsx --type dcf

# Export Monte Carlo results
python scripts/excel_exporter.py mc_results.json --output risk_analysis.xlsx --type montecarlo

# Export forecast
python scripts/excel_exporter.py forecast_results.json --output forecast.xlsx --type forecast

# Fallback to CSV (no openpyxl needed)
python scripts/excel_exporter.py results.json --output report.csv --fallback-csv

Knowledge Bases

中国市场模式（默认启用）

当分析对象为中国企业/项目时，自动加载 references/china-market-guide.md，并应用以下默认行为：

1. 参数替换：无风险利率 = 1.77%（中国10年国债），ERP = 5.0%，税率 = 25%
2. 叙事层输出：每次计算结果前置一段"人话总结"（投X万，Y月回本，赚Z万）
3. 竞品对标：计算完IRR后自动与行业基准对比，给出"偏高/持平/偏低"判断
4. Go/No-Go信号：IRR < hurdle rate (15%) 自动标红；P(NPV>0) < 60% 标红

Templates

Key Metrics & Targets

Input Data Format

All scripts accept JSON input files. See assets/sample_financial_data.json for the complete input schema covering all four tools.

Dependencies

Core tools (Tools 1-5): Python standard library only (math, statistics, json, argparse, datetime, random). No numpy, pandas, or scipy required.

Excel Exporter (Tool 6): Requires openpyxl for .xlsx output. Falls back to CSV export if openpyxl is not installed.

# Optional: install for Excel export
pip install openpyxl