Three-Statement Financial Model Builder

Prompt

Create a comprehensive three-statement financial model integrating the income statement, balance sheet, and cash flow statement. Include historical data inputs and future projections.

Prepare historical data for 2020 and 2021, ensuring all line items across income statement, balance sheet, and cash flow are aligned and linked to model assumptions.

Build the income statement with operating revenue, COGS as % of revenue, gross profit, operating/investing/financing activities, depreciation and amortization, pre-tax income, taxes, net income, and EPS.

Build the cash flow statement with operating, investing, and financing sections using both direct and indirect methods, ensuring reconciliation between net income and the balance sheet cash position.

Create projection assumptions including operating growth rate, COGS %, CAPEX %, working capital %, debt schedule, and dividend payout schedule.

Implement scenario analysis with toggle-based Base, Optimistic, and Conservative cases.

Add dynamic drivers with sensitivity tables and dropdowns that automatically update all statements.

Implement circular accounting equations and validation checks including balance sheet equality, cash flow reconciliation, retained earnings roll-forward, and depreciation consistency.

Link income statement to balance sheet via retained earnings and dividends, and ensure ending cash from the cash flow statement matches the balance sheet.

Align projections across all statements, aggregate totals, and reconcile projected balance sheet items.

Add worksheets for assumptions, historical data, scenario switches, and error checks.

Manage circularity with proper iteration settings and error highlighting.

Include a model explanation tab with key assumptions and methodology.

Use advanced Excel capabilities including data validation, conditional formatting, dynamic ranges, scenario toggles, error checks, circularity management, labeled assumption tables, and consistent formatting.

Final deliverables: integrated three-statement model with 2 historical and 3 projected years, assumptions dashboard with scenario toggles, supporting schedules (working capital, debt, depreciation), and validation tabs.