3 Statement Model

3-Statement Financial Model Template Completion

Complete and populate integrated financial model templates with proper linkages between Income Statement, Balance Sheet, and Cash Flow Statement.

⚠️ CRITICAL PRINCIPLES — Read Before Populating Any Template

Environment — Office JS vs Python:

• If running inside Excel (Office Add-in / Office JS): Use Office JS directly. Write formulas via range.formulas = [["=D14*(1+Assumptions!$B$5)"]] — never range.values for derived cells. No separate recalc; Excel computes natively. Use context.workbook.worksheets.getItem(...) to navigate tabs.
• If generating a standalone .xlsx file: Use Python/openpyxl. Write ws["D15"] = "=D14*(1+Assumptions!$B$5)", then run recalc.py before delivery.
• Office JS merged cell pitfall: Do NOT call .merge() then set .values on the merged range — throws InvalidArgument because the range still reports its pre-merge dimensions. Instead write value to top-left cell alone, then merge + format the full range: ws.getRange("A1").values = [["INCOME STATEMENT"]]; const h = ws.getRange("A1:G1"); h.merge(); h.format.fill.color = "#1F4E79";
• All principles below apply identically in either environment.

Formulas over hardcodes (non-negotiable):

• Every projection cell, roll-forward, linkage, and subtotal MUST be an Excel formula — never a pre-computed value
• When using Python/openpyxl: write formula strings (ws["D15"] = "=D14*(1+Assumptions!$B$5)"), NOT computed results (ws["D15"] = 12500)
• The ONLY cells that should contain hardcoded numbers are: (1) historical actuals, (2) assumption drivers in the Assumptions tab
• If you find yourself computing a value in Python and writing the result to a cell — STOP. Write the formula instead.
• Why: the model must flex when scenarios toggle or assumptions change. Hardcodes break every downstream integrity check silently.

Verify step-by-step with the user:

1. After mapping the template → show the user which tabs/sections you've identified and confirm before touching any cells
2. After populating historicals → show the user the historical block and confirm values/periods match source data
3. After building IS projections → run the subtotal checks, show the user the projected IS, confirm before moving to BS
4. After building BS → show the user the balance check (Assets = L+E) for every period, confirm before moving to CF
5. After building CF → show the user the cash tie-out (CF ending cash = BS cash), confirm before finalizing
6. Do NOT populate the entire model end-to-end and present it complete — break at each statement, show the work, catch errors early

Formatting — Professional Blue/Grey Palette (Default unless template/user specifies otherwise)

Keep colors minimal. Use only blues and greys for cell fills. Do NOT introduce greens, yellows, oranges, or multiple accent colors — a clean model uses restraint.

That's 3 blues + 1 grey + white. If the template has its own color scheme, follow the template instead.

Font color signals what a cell is (input/formula/link). Fill color signals where you are (header/data/check).

Model Structure

Identifying Template Tab Organization

Templates vary in their tab naming conventions and organization. Before populating, review all tabs to understand the template's structure. Below are common tab names and their typical contents:

Template Review Checklist

• Identify which tabs exist in the template (not all templates include every schedule)
• Note any template-specific tabs not listed above
• Understand tab dependencies (e.g., which schedules feed into the main statements)
• Locate input cells vs. formula cells on each tab

Understanding Template Structure

Before populating a template, familiarize yourself with its existing layout to ensure data is entered in the correct locations and formulas remain intact.

Identifying Row Structure

• Locate the model title at top of each tab
• Identify section headers and their visual separation
• Find the units row indicating $ millions, %, x, etc.
• Note column headers distinguishing Actuals vs. Estimates periods
• Confirm period labels (e.g., FY2024A, FY2025E)
• Identify input cells vs. formula cells (typically distinguished by font color)

Identifying Column Structure

• Confirm line item labels in leftmost column
• Verify historical years precede projection years
• Note the visual border separating historical from projected periods
• Check for consistent column order across all tabs

Working with Named Ranges Templates often use named ranges for key inputs and outputs. Before entering data:

• Review existing named ranges in the template (Formulas → Name Manager in Excel)
• Common named ranges include: Revenue growth rates, cost percentages, key outputs (Net Income, EBITDA, Total Debt, Cash), scenario selector cell
• Ensure inputs are entered in cells that feed into these named ranges

Projection Period

• Templates typically project 5 years forward from last historical year
• Verify historical (A) vs. projected (E) columns are clearly separated
• Confirm columns use fiscal year notation (e.g., FY2024A, FY2025E)

Margin Analysis

Note: The following margin analysis should only be performed if prompted by the user or if the template explicitly requires it. If no prompt is given, skip this section.

Calculate and display profitability margins on the Income Statement (IS) tab to track operational efficiency and enable peer comparison.

Core Margins to Include

Income Statement Layout with Margins

Display margin percentages directly below each profit line item:

• Gross Margin % below Gross Profit
• EBIT Margin % below EBIT
• EBITDA Margin % below EBITDA
• Net Income Margin % below Net Income

Credit Metrics

Note: The following Credit analysis should only be performed if prompted by the user or if the template explicitly requires it. If no prompt is given, skip this section.

Calculate and display credit/leverage metrics on the Balance Sheet (BS) tab to assess financial health, debt capacity, and covenant compliance.

Core Credit Metrics to Include