Financial Model Formatting

Contract

When you produce or edit a financial-model spreadsheet (openpyxl/xlsxwriter code, a cell-by-cell layout, or a description of the formatting), apply the investment-banking modeling house style: cells are font-colored by their ROLE, numbers use the fixed finance format codes, assumptions live in referenced cells, and hardcodes carry a source tag. Use this whenever the deliverable is a model (DCF, LBO, three-statement, comps, budget, schedule, cap table) — not for a one-off data dump.

Rules

Rule 1 — Font color by cell role (the blue/black/green/red convention)

Color the FONT of every value cell by what kind of value it holds. This is the single most load-bearing rule; never leave every cell the default black.

| Cell role | Font color | Hex | RGB | |---|---|---|---| | Hardcoded input / assumption (a number a user changes for scenarios) | Blue | 0000FF | 0,0,255 | | Formula or calculation | Black | 000000 | 0,0,0 | | Link to a cell on ANOTHER sheet in the SAME workbook | Green | 008000 | 0,128,0 | | Link to a cell in a DIFFERENT (external) workbook file | Red | FF0000 | 255,0,0 |

In addition, fill (background) key assumptions a reviewer must revisit with yellow (FFFF00). Yellow is a fill, not a font color; it stacks on top of the blue font.

Rule 2 — Number format codes (the finance presentation set)

Apply these exact Excel number-format strings. The defaults a general model reaches for (two decimals, leading-minus negatives, "0" for zero) are wrong here.

• Currency / dollars: $#,##0 — whole units, no cents. State the unit in the column

header instead of in the cells, e.g. a header reading Revenue ($mm).

• Negatives in parentheses and zeros shown as a dash, combined into one code:

$#,##0;($#,##0);- for currency, or #,##0;(#,##0);- for plain counts.

• Negatives are ALWAYS parenthesized — (1,250), never a leading-minus -1,250.
• Percentages: 0.0% — exactly one decimal place.
• Valuation multiples (EV/EBITDA, P/E, EV/Revenue): 0.0x — one decimal and a literal

trailing x, e.g. 8.5x.

• Years used as labels: store/format as TEXT 2024, never the General/number format

that renders a thousands separator (2,024).

Rule 3 — Assumptions are referenced cells, never inline constants

Every driver (growth rate, margin, tax rate, multiple) gets its OWN labeled cell, and formulas REFERENCE that cell. Never bake a driver into a formula as a literal.

• Use =B5*(1+$B$6) where $B$6 holds the growth assumption.
• Never =B5*1.08.
• Likewise, totals and ratios are Excel formulas (=SUM(...), =AVERAGE(...),

=C9/C8), never a value you computed yourself and pasted as a literal.

Rule 4 — Tag every hardcode with its source

Each hardcoded input carries a brief cell note recording where the number came from — the source system or document, the date, and the specific reference it was taken from.

Rule 5 — Deliver with zero formula errors

No #REF!, #DIV/0!, #VALUE!, #N/A, or #NAME? may survive in the delivered file. Guard division denominators and check references before delivering.

Worked examples

Rule 1 — color by role

A monthly SaaS build hardcodes starting ARR and a churn rate, computes net ARR, and pulls headcount from an Operating sheet.

BEFORE (base default — everything plain black):

python
ws["B2"] = 1200000          # starting ARR (input)
ws["B3"] = 0.03             # monthly churn (input)
ws["B4"] = "=B2*(1-B3)"     # net ARR (formula)
ws["B5"] = "=Operating!C10" # headcount, same workbook other sheet
# no font colors set -> all black

AFTER (conforming — font color by role):

python
ws["B2"].font = Font(color="0000FF")  # input -> blue
ws["B3"].font = Font(color="0000FF")  # input -> blue
ws["B4"].font = Font(color="000000")  # formula -> black
ws["B5"].font = Font(color="008000")  # intra-workbook link -> green

If B5 instead pulled from a separate file, its font would be red (FF0000).

Rule 2 — number formats

A manufacturing margin line shows revenue, a loss period, a zero, a margin, and a trading multiple.

BEFORE (base default):

Revenue:   $4,200,000.00
EBIT:      -$85,000.00
Backlog:   0
Margin:    0.182
EV/EBITDA: 9.4
Year:      2,025

AFTER (conforming format codes):

Revenue ($):   4,200,000        -> number_format "$#,##0"
EBIT ($):      (85,000)         -> "$#,##0;($#,##0);-"
Backlog ($):   -                -> "$#,##0;($#,##0);-"
Margin:        18.2%            -> "0.0%"
EV/EBITDA:     9.4x             -> "0.0x"
Year:          2025  (text)     -> stored as text, no separator

Rule 3 — referenced assumptions

BEFORE: ws["C5"] = "=C4*1.08" (8% growth baked in) AFTER: ws["C2"] = 0.08 (blue input, labeled "Growth") and ws["C5"] = "=C4*(1+$C$2)"

Rule 4 — source tag

BEFORE: a bare 42.5 typed into the WACC cell. AFTER: 42.5 with a note Source: Company 10-K, FY2024, Page 45, Revenue Note, [SEC EDGAR URL].

Edge cases & exceptions

• An existing template ALWAYS wins. If you are editing a file that already has its own

color scheme or number formats, match it exactly; do not impose this house style on top of an established one.

• A cell that is part input, part formula does not exist — split it: the assumption is

a blue input cell, the calculation that uses it is a separate black formula cell.

• A hardcoded number that is genuinely a constant of nature (days in a year = 365) does

not need a source tag, but still gets blue font as an input.

• Percentages that are themselves multiples-style (a 1.0x = 100% coverage ratio) follow

the 0.0x rule, not 0.0%, when labeled as a multiple/ratio "times" figure.

Do / Don't

• DO color hardcoded inputs blue (0000FF) and leave formulas black (000000).
• DON'T leave the whole model in one default font color.
• DO render negatives as (1,250) and zeros as -.
• DON'T render negatives with a leading minus or zeros as 0.
• DO write multiples as 9.4x and percentages as 18.2%.
• DON'T write a multiple as 9.4 or 9.4% or 940%.
• DO reference assumption cells in formulas (=B5*(1+$B$6)).
• DON'T inline the assumption as a literal (=B5*1.08).
• DO state currency units in the header (Revenue ($mm)).
• DON'T append "mm"/"$" inside every value cell.

Common mistakes (base defaults to avoid)

• Setting no font colors at all, so inputs and formulas are indistinguishable.
• Using green/red interchangeably or skipping them — green is strictly same-workbook

links, red is strictly external-file links.

• Two-decimal currency ($4,200,000.00) instead of whole-dollar $#,##0.
• Leading-minus negatives instead of parentheses.
• Showing 0 instead of a dash for zero values.
• Writing a year as a number so Excel adds a thousands separator (2,025).
• Computing a sum/growth in code and pasting the literal instead of an Excel formula.

Quick checklist

• ] Inputs blue, formulas black, same-workbook links green, external links red
• ] Key assumptions filled yellow
• ] Currency $#,##0; units in headers, not cells
• ] Negatives (x), zeros -
• ] Percentages 0.0%, multiples 0.0x
• ] Years as text, no separator
• ] Assumptions referenced, not inlined; totals are formulas
• ] Hardcodes carry Source: ... tags
• ] Zero formula errors