Excel

Build a Financial Model in Excel (with Python Automation)

Three-statement financial models link the income statement, balance sheet, and cash flow statement. They are the backbone of investment banking, equity research, and corporate finance. In this tutorial, we build one from scratch in Excel, then automate the data population using Python.

Prerequisites

✓Intermediate Excel skills
✓Basic Python
✓Understanding of financial statements

Step 1.Install openpyxl

openpyxl lets Python read and write Excel files programmatically.

pip install openpyxl pandas

Step 2.Create the model structure

We start with the income statement structure, using professional formatting.

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

wb = Workbook()

# Income Statement
ws_is = wb.active
ws_is.title = "Income Statement"
headers = ["Item", "2021", "2022", "2023", "2024E", "2025E"]
for col, header in enumerate(headers, 1):
    cell = ws_is.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="1a1a2e")

line_items = [
    "Revenue", "COGS", "Gross Profit", "Operating Expenses",
    "EBITDA", "D&A", "EBIT", "Interest Expense",
    "EBT", "Tax", "Net Income"
]
for row, item in enumerate(line_items, 2):
    ws_is.cell(row=row, column=1, value=item)

print("Income Statement sheet created")

Step 3.Add Balance Sheet and Cash Flow

All three statements use the same year headers for consistency.

# Balance Sheet
ws_bs = wb.create_sheet("Balance Sheet")
bs_items = [
    "Cash", "Accounts Receivable", "Inventory", "Total Current Assets",
    "PP&E", "Goodwill", "Total Assets",
    "Accounts Payable", "Short-term Debt", "Total Current Liabilities",
    "Long-term Debt", "Total Liabilities",
    "Shareholders Equity", "Total Liabilities & Equity"
]
for col, header in enumerate(headers, 1):
    cell = ws_bs.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="1a1a2e")
for row, item in enumerate(bs_items, 2):
    ws_bs.cell(row=row, column=1, value=item)

# Cash Flow Statement
ws_cf = wb.create_sheet("Cash Flow")
cf_items = [
    "Net Income", "D&A", "Changes in Working Capital", "Operating Cash Flow",
    "CapEx", "Acquisitions", "Investing Cash Flow",
    "Debt Issued", "Debt Repaid", "Dividends", "Financing Cash Flow",
    "Net Change in Cash"
]
for col, header in enumerate(headers, 1):
    cell = ws_cf.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="1a1a2e")
for row, item in enumerate(cf_items, 2):
    ws_cf.cell(row=row, column=1, value=item)

print("Balance Sheet and Cash Flow sheets created")

Step 4.Populate with sample data

Historical data is hardcoded, projections use formulas that you can adjust.

# Sample revenue data (in millions)
revenue_data = {2021: 365817, 2022: 394328, 2023: 383285}
cogs_pct = 0.57
opex_pct = 0.14

for col, year in enumerate([2021, 2022, 2023], 2):
    rev = revenue_data[year]
    ws_is.cell(row=2, column=col, value=rev)
    ws_is.cell(row=3, column=col, value=-int(rev * cogs_pct))
    ws_is.cell(row=4, column=col, value=int(rev * (1 - cogs_pct)))
    ws_is.cell(row=5, column=col, value=-int(rev * opex_pct))
    ws_is.cell(row=6, column=col, value=int(rev * (1 - cogs_pct - opex_pct)))

# Add growth formulas for projections
ws_is.cell(row=2, column=5, value="=D2*1.05")  # 5% growth
ws_is.cell(row=2, column=6, value="=E2*1.05")

print("Data populated with formulas")

Step 5.Save and format

The finished model has three linked sheets ready for analysis.

# Auto-width columns
for ws in [ws_is, ws_bs, ws_cf]:
    ws.column_dimensions["A"].width = 25
    for col_num in range(2, 7):
        ws.column_dimensions[chr(64 + col_num)].width = 15

wb.save("financial_model.xlsx")
print("Saved financial_model.xlsx")
print(f"Sheets: {wb.sheetnames}")

Expected Output

Income Statement sheet created
Balance Sheet and Cash Flow sheets created
Data populated with formulas
Saved financial_model.xlsx
Sheets: ['Income Statement', 'Balance Sheet', 'Cash Flow']

Next Steps

→Add Excel formulas to link all three statements
→Build sensitivity tables for key assumptions
→Create charts that update automatically