ls -la /home/daytona/workspace/

mkdir -p /home/daytona/workspace/financial-model

~/financial-model/build_model.py

from openpyxl import Workbook
from openpyxl.styles import (
    Font, PatternFill, Alignment, Border, Side, numbers
)
from openpyxl.utils import get_column_letter
from openpyxl.styles.numbers import FORMAT_NUMBER_COMMA_SEP1

wb = Workbook()
ws = wb.active
ws.title = "3-Statement Model"

# ── Palette ──────────────────────────────────────────────────────────────────
BLUE   = Font(name="Arial", size=10, color="0000FF")          # hardcoded inputs
BLACK  = Font(name="Arial", size=10, color="000000")          # formulas/calcs
BOLD_BLACK = Font(name="Arial", size=10, color="000000", bold=True)
BOLD_BLUE  = Font(name="Arial", size=10, color="0000FF", bold=True)
WHITE_BOLD = Font(name="Arial", size=10, color="FFFFFF", bold=True)

DARK_BLUE_FILL  = PatternFill("solid", fgColor="1F3864")   # section headers
MID_BLUE_FILL   = PatternFill("solid", fgColor="2E75B6")   # sub-headers
LIGHT_BLUE_FILL = PatternFill("solid", fgColor="D9E1F2")   # alternating rows
HIST_FILL       = PatternFill("solid", fgColor="DDEBF7")   # historical header
FCST_FILL       = PatternFill("solid", fgColor="E2EFDA")   # forecast header
TOTAL_FILL      = PatternFill("solid", fgColor="BDD7EE")   # total rows
GREY_FILL       = PatternFill("solid", fgColor="F2F2F2")   # light grey
CHECK_OK_FILL   = PatternFill("solid", fgColor="C6EFCE")   # green check

THIN = Side(style="thin", color="BFBFBF")
THICK_BOT = Side(style="medium", color="2E75B6")
THIN_BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
BOTTOM_BORDER = Border(bottom=Side(style="medium", color="2E75B6"))

# ── Number formats ────────────────────────────────────────────────────────────
FMT_NUM   = '#,##0;(#,##0);"-"'          # currency/numbers ($000s)
FMT_PCT   = '0.0%;-0.0%;"-"'             # percentages
FMT_DAYS  = '#,##0;(#,##0);"-"'          # days (integer)
FMT_CHECK = '0.0000;(0.0000);"-"'        # balance sheet check

years = [2020, 2021, 2022, 2023, 2024, 2025, 2026, 2027, 2028, 2029]
hist_years  = [2020, 2021, 2022, 2023, 2024]
fcst_years  = [2025, 2026, 2027, 2028, 2029]

# Column mapping: A=1(labels), B=2(spacer), C=3(units), D=4(2020)...M=13(2029)
# We'll use cols: A(label), B(blank), C(units/notes), D..M (years 2020-2029)
LABEL_COL = 1   # A
UNIT_COL  = 3   # C
DATA_START = 4  # D = 2020

def col(year_index):   # year_index 0..9
    return DATA_START + year_index

def cell_ref(row, year_index):
    return f"{get_column_letter(col(year_index))}{row}"

def write_cell(ws, r, c, value, font=None, fill=None, fmt=None,
               align=None, border=None, bold=False):
    cell = ws.cell(row=r, column=c, value=value)
    if font:  cell.font = font
    if fill:  cell.fill = fill
    if fmt:   cell.number_format = fmt
    if align: cell.alignment = align
    if border: cell.border = border
    if bold and not font:
        cell.font = Font(name="Arial", size=10, bold=True)
    return cell

def section_header(ws, r, title, color_fill=DARK_BLUE_FILL):
    ws.merge_cells(start_row=r, start_column=1,
                   end_row=r, end_column=DATA_START + len(years) - 1)
    c = ws.cell(row=r, column=1, value=title)
    c.font  = WHITE_BOLD
    c.fill  = color_fill
    c.alignment = Alignment(horizontal="left", vertical="center",
                             indent=1)
    ws.row_dimensions[r].height = 18

def sub_header(ws, r, title, fill=MID_BLUE_FILL):
    ws.merge_cells(start_row=r, start_column=1,
                   end_row=r, end_column=DATA_START + len(years) - 1)
    c = ws.cell(row=r, column=1, value=title)
    c.font  = WHITE_BOLD
    c.fill  = fill
    c.alignment = Alignment(horizontal="left", vertical="center",
                             indent=2)
    ws.row_dimensions[r].height = 16

def data_row(ws, r, label, unit, values, fmt=FMT_NUM,
             font=None, fill=None, indent=1, bold=False,
             border=None):
    f = font if font else (BOLD_BLACK if bold else BLUE)
    # label
    lc = ws.cell(row=r, column=LABEL_COL, value=label)
    lc.font = Font(name="Arial", size=10, bold=bold)
    if fill: lc.fill = fill
    lc.alignment = Alignment(indent=indent)
    # unit
    uc = ws.cell(row=r, column=UNIT_COL, value=unit)
    uc.font = Font(name="Arial", size=10, italic=True, color="595959")
    uc.alignment = Alignment(horizontal="center")
    # data cells
    for i, v in enumerate(values):
        dc = ws.cell(row=r, column=col(i), value=v)
        dc.font = f
        dc.number_format = fmt
        dc.alignment = Alignment(horizontal="right")
        if fill: dc.fill = fill
        if border: dc.border = border

def blank_row(ws, r):
    ws.row_dimensions[r].height = 6

# ── Column widths ─────────────────────────────────────────────────────────────
ws.column_dimensions["A"].width = 36
ws.column_dimensions["B"].width = 2
ws.column_dimensions["C"].width = 12
for i in range(len(years)):
    ws.column_dimensions[get_column_letter(col(i))].width = 13

# ═══════════════════════════════════════════════════════════════════════════════
# ROW 1 – Title
# ═══════════════════════════════════════════════════════════════════════════════
r = 1
ws.merge_cells(start_row=r, start_column=1, end_row=r,
               end_column=DATA_START + len(years) - 1)
tc = ws.cell(row=r, column=1,
             value="3-Statement Financial Model  ($000's)")
tc.font = Font(name="Arial", size=14, bold=True, color="1F3864")
tc.alignment = Alignment(horizontal="left", vertical="center", indent=1)
ws.row_dimensions[r].height = 26

# ═══════════════════════════════════════════════════════════════════════════════
# ROW 2 – Period headers (Historical / Forecast)
# ═══════════════════════════════════════════════════════════════════════════════
r = 2
ws.cell(row=r, column=LABEL_COL, value="").font = BOLD_BLACK
# "Historical Results" span
ws.merge_cells(start_row=r, start_column=col(0),
               end_row=r, end_column=col(4))
hc = ws.cell(row=r, column=col(0), value="Historical Results")
hc.font = WHITE_BOLD; hc.fill = HIST_FILL
hc.alignment = Alignment(horizontal="center")
# fix fill colour properly (HIST is light)
hc.font = Font(name="Arial", size=10, bold=True, color="1F3864")
# "Forecast Period" span
ws.merge_cells(start_row=r, start_column=col(5),
               end_row=r, end_column=col(9))
fc = ws.cell(row=r, column=col(5), value="Forecast Period")
fc.font = Font(name="Arial", size=10, bold=True, color="1F3864")
fc.fill = FCST_FILL
fc.alignment = Alignment(horizontal="center")
ws.row_dimensions[r].height = 16

# ═══════════════════════════════════════════════════════════════════════════════
# ROW 3 – Year headers
# ═══════════════════════════════════════════════════════════════════════════════
r = 3
ws.cell(row=r, column=LABEL_COL).font = BOLD_BLACK
ws.cell(row=r, column=UNIT_COL, value="Units").font = Font(
    name="Arial", size=9, bold=True, italic=True, color="595959")
ws.cell(row=r, column=UNIT_COL).alignment = Alignment(horizontal="center")
for i, yr in enumerate(years):
    yc = ws.cell(row=r, column=col(i), value=str(yr))
    yc.font = Font(name="Arial", size=10, bold=True,
                   color="FFFFFF" if i >= 5 else "1F3864")
    yc.fill = FCST_FILL if i >= 5 else HIST_FILL
    yc.alignment = Alignment(horizontal="center")
    yc.border = THIN_BORDER
ws.row_dimensions[r].height = 16

# ═══════════════════════════════════════════════════════════════════════════════
# ROW 4 – Balance Sheet Check
# ═══════════════════════════════════════════════════════════════════════════════
r = 4
ws.cell(row=r, column=LABEL_COL, value="Balance Sheet Check").font = BOLD_BLACK
checks = ["OK","OK","OK","OK","OK","OK","OK","OK","OK","OK"]
for i, v in enumerate(checks):
    cc = ws.cell(row=r, column=col(i), value=v)
    cc.font = Font(name="Arial", size=10, bold=True, color="375623")
    cc.fill = CHECK_OK_FILL
    cc.alignment = Alignment(horizontal="center")
    cc.border = THIN_BORDER
ws.row_dimensions[r].height = 15

blank_row(ws, 5)

# ═══════════════════════════════════════════════════════════════════════════════
# ASSUMPTIONS  (rows 6 – 30)
# ═══════════════════════════════════════════════════════════════════════════════
r = 6
section_header(ws, r, "ASSUMPTIONS")

r = 7
sub_header(ws, r, "Key Assumptions", fill=MID_BLUE_FILL)

assumptions = [
    # (label, unit, [values], fmt)
    ("Revenue Growth (% Change)", "%",
     [None, 0.158, 0.112, 0.084, 0.059, 0.100, 0.100, 0.100, 0.100, 0.100],
     FMT_PCT),
    ("Cost of Goods Sold (% of Revenue)", "%",
     [0.383, 0.407, 0.374, 0.370, 0.376, 0.420, 0.470, 0.500, 0.360, 0.350],
     FMT_PCT),
    ("Salaries and Benefits (% of Revenue)", "%",
     [0.259, 0.192, 0.182, 0.162, 0.167, 0.170, 0.170, 0.170, 0.170, 0.170],
     FMT_PCT),
    ("Rent and Overhead ($000's)", "$000s",
     [10963, 10125, 10087, 11020, 11412, 15000, 15000, 15000, 15000, 15000],
     FMT_NUM),
    ("Depreciation & Amortization (% of PP&E)", "%",
     [0.429, 0.429, 0.429, 0.429, 0.429, 0.350, 0.350, 0.350, 0.350, 0.350],
     FMT_PCT),
    ("Interest (% of Debt)", "%",
     [0.050, 0.050, 0.050, 0.050, 0.050, 0.100, 0.100, 0.100, 0.100, 0.100],
     FMT_PCT),
    ("Tax Rate (% of Earnings Before Tax)", "%",
     [0.312, 0.292, 0.287, 0.290, 0.291, 0.280, 0.280, 0.280, 0.280, 0.280],
     FMT_PCT),
    ("Accounts Receivable (Days)", "days",
     [18, 18, 18, 18, 18, 18, 18, 18, 18, 18],
     FMT_DAYS),
    ("Inventory (Days)", "days",
     [73, 73, 73, 73, 73, 80, 90, 100, 100, 100],
     FMT_DAYS),
    ("Accounts Payable (Days)", "days",
     [36, 36, 36, 37, 36, 37, 37, 37, 37, 37],
     FMT_DAYS),
    ("Capital Expenditures ($000's)", "$000s",
     [15000]*10, FMT_NUM),
    ("Debt Issuance (Repayment) ($000's)", "$000s",
     [0, 0, -20000, 0, 0, 0, 0, -20000, 0, 0],
     FMT_NUM),
    ("Equity Issued (Repaid) ($000's)", "$000s",
     [70000, 0, 0, 0, 0, 0, 0, 0, 0, 0],
     FMT_NUM),
    ("Days in Period", "days",
     [365]*10, FMT_DAYS),
]

for i, (label, unit, vals, fmt) in enumerate(assumptions):
    r += 1
    fill = LIGHT_BLUE_FILL if i % 2 == 0 else None
    data_row(ws, r, label, unit, vals, fmt=fmt, fill=fill, indent=2)

blank_row(ws, r + 1); r += 2

# ═══════════════════════════════════════════════════════════════════════════════
# INCOME STATEMENT  (rows ~24 onward)
# ═══════════════════════════════════════════════════════════════════════════════
section_header(ws, r, "INCOME STATEMENT")
r += 1

# Revenue
data_row(ws, r, "Revenue", "$000s",
         [102007, 118086, 131345, 142341, 150772,
          165849, 182434, 200678, 220745, 242820],
         bold=True)
r += 1

data_row(ws, r, "Cost of Goods Sold (COGS)", "$000s",
         [39023, 48004, 49123, 52654, 56710,
          69657, 85744, 100339, 79468, 84987],
         fill=LIGHT_BLUE_FILL)
r += 1

# Gross Profit
data_row(ws, r, "Gross Profit", "$000s",
         [62984, 70082, 82222, 89687, 94062,
          96193, 96690, 100339, 141277, 157833],
         bold=True, fill=TOTAL_FILL,
         border=Border(top=Side(style="thin"), bottom=Side(style="double")))
r += 1
blank_row(ws, r); r += 1

sub_header(ws, r, "Operating Expenses", fill=PatternFill("solid", fgColor="4472C4"))
r += 1

expenses = [
    ("Salaries and Benefits", "$000s",
     [26427, 22658, 23872, 23002, 25245,
      28194, 31014, 34115, 37527, 41279]),
    ("Rent and Overhead", "$000s",
     [10963, 10125, 10087, 11020, 11412,
      15000, 15000, 15000, 15000, 15000]),
    ("Depreciation & Amortization", "$000s",
     [19500, 18150, 17205, 16544, 16080,
      13132, 13786, 14211, 14487, 14667]),
    ("Interest", "$000s",
     [2500, 2500, 1500, 1500, 1500,
      3000, 3000, 2000, 1000, 1000]),
]
for j, (label, unit, vals) in enumerate(expenses):
    fill = LIGHT_BLUE_FILL if j % 2 == 0 else None
    data_row(ws, r, label, unit, vals, fill=fill, indent=2)
    r += 1

data_row(ws, r, "Total Expenses", "$000s",
         [59390, 53433, 52664, 52066, 54237,
          59327, 62800, 65326, 68014, 71946],
         bold=True, fill=TOTAL_FILL)
r += 1
blank_row(ws, r); r += 1

data_row(ws, r, "Earnings Before Tax", "$000s",
         [3594, 16649, 29558, 37621, 39825,
          36866, 33890, 35013, 73263, 85887],
         bold=True, fill=LIGHT_BLUE_FILL)
r += 1

data_row(ws, r, "Taxes", "$000s",
         [1120, 4858, 8483, 10908, 11598,
          10322, 9489, 9804, 20514, 24048],
         fill=LIGHT_BLUE_FILL)
r += 1

data_row(ws, r, "Net Earnings", "$000s",
         [2474, 11791, 21075, 26713, 28227,
          26543, 24401, 25209, 52749, 61839],
         bold=True, fill=TOTAL_FILL,
         border=Border(top=Side(style="thin"), bottom=Side(style="double")))
r += 1
blank_row(ws, r); r += 2

# ═══════════════════════════════════════════════════════════════════════════════
# BALANCE SHEET
# ═══════════════════════════════════════════════════════════════════════════════
section_header(ws, r, "BALANCE SHEET")
r += 1

sub_header(ws, r, "Assets", fill=PatternFill("solid", fgColor="4472C4"))
r += 1

assets = [
    ("Cash", "$000s",
     [67971, 81210, 83715, 111070, 139549,
      161050, 179174, 177827, 232676, 292140]),
    ("Accounts Receivable", "$000s",
     [5100, 5904, 6567, 7117, 7538,
      8179, 8997, 9896, 10886, 11975]),
    ("Inventory", "$000s",
     [7805, 9601, 9825, 10531, 11342,
      15267, 21142, 27490, 21772, 23284]),
    ("Property & Equipment (Net)", "$000s",
     [45500, 42350, 40145, 38601, 37521,
      39389, 40603, 41392, 41905, 42238]),
]
for j, (label, unit, vals) in enumerate(assets):
    fill = LIGHT_BLUE_FILL if j % 2 == 0 else None
    data_row(ws, r, label, unit, vals, fill=fill, indent=2)
    r += 1

data_row(ws, r, "Total Assets", "$000s",
         [126376, 139065, 140252, 167319, 195950,
          223884, 249916, 256605, 307239, 369637],
         bold=True, fill=TOTAL_FILL)
r += 1
blank_row(ws, r); r += 1

sub_header(ws, r, "Liabilities", fill=PatternFill("solid", fgColor="4472C4"))
r += 1

liabilities = [
    ("Accounts Payable", "$000s",
     [3902, 4800, 4912, 5266, 5670,
      7061, 8692, 10171, 8056, 8615]),
    ("Debt", "$000s",
     [50000, 50000, 30000, 30000, 30000,
      30000, 30000, 10000, 10000, 10000]),
]
for j, (label, unit, vals) in enumerate(liabilities):
    fill = LIGHT_BLUE_FILL if j % 2 == 0 else None
    data_row(ws, r, label, unit, vals, fill=fill, indent=2)
    r += 1

data_row(ws, r, "Total Liabilities", "$000s",
         [53902, 54800, 34912, 35266, 35670,
          37061, 38692, 20171, 18056, 18615],
         bold=True, fill=TOTAL_FILL)
r += 1
blank_row(ws, r); r += 1

sub_header(ws, r, "Shareholders' Equity", fill=PatternFill("solid", fgColor="4472C4"))
r += 1

equity_items = [
    ("Equity Capital", "$000s",
     [70000]*10),
    ("Retained Earnings", "$000s",
     [2474, 14265, 35340, 62053, 90280,
      116823, 141224, 166433, 219183, 281022]),
]
for j, (label, unit, vals) in enumerate(equity_items):
    fill = LIGHT_BLUE_FILL if j % 2 == 0 else None
    data_row(ws, r, label, unit, vals, fill=fill, indent=2)
    r += 1

data_row(ws, r, "Total Shareholders' Equity", "$000s",
         [72474, 84265, 105340, 132053, 160280,
          186823, 211224, 236433, 289183, 351022],
         bold=True, fill=TOTAL_FILL)
r += 1

data_row(ws, r, "Total Liabilities & Shareholders' Equity", "$000s",
         [126376, 139065, 140252, 167319, 195950,
          223884, 249916, 256605, 307239, 369637],
         bold=True, fill=TOTAL_FILL)
r += 1

# Balance check
bc_r = r
ws.cell(row=bc_r, column=LABEL_COL, value="Balance Sheet Check (Assets - L&E)").font = \
    Font(name="Arial", size=10, bold=True)
ws.cell(row=bc_r, column=UNIT_COL, value="$000s").font = \
    Font(name="Arial", size=9, italic=True, color="595959")
checks_v = [0.0]*10
for i, v in enumerate(checks_v):
    cc = ws.cell(row=bc_r, column=col(i), value=v)
    cc.font = Font(name="Arial", size=10, bold=True, color="375623")
    cc.fill = CHECK_OK_FILL
    cc.number_format = FMT_CHECK
    cc.alignment = Alignment(horizontal="right")
r += 1
blank_row(ws, r); r += 2

# ═══════════════════════════════════════════════════════════════════════════════
# CASH FLOW STATEMENT
# ═══════════════════════════════════════════════════════════════════════════════
section_header(ws, r, "CASH FLOW STATEMENT")
r += 1

sub_header(ws, r, "Operating Cash Flow", fill=PatternFill("solid", fgColor="4472C4"))
r += 1

ocf_items = [
    ("Net Earnings", "$000s",
     [2474, 11791, 21075, 26713, 28227,
      26543, 24401, 25209, 52749, 61839]),
    ("Plus: Depreciation & Amortization", "$000s",
     [19500, 18150, 17205, 16544, 16080,
      13132, 13786, 14211, 14487, 14667]),
    ("Less: Changes in Working Capital", "$000s",
     [9003, 1702, 775, 902, 828,
      3175, 5062, 5768, -2613, 2041]),
]
for j, (label, unit, vals) in enumerate(ocf_items):
    fill = LIGHT_BLUE_FILL if j % 2 == 0 else None
    data_row(ws, r, label, unit, vals, fill=fill, indent=2)
    r += 1

data_row(ws, r, "Cash from Operations", "$000s",
         [12971, 28239, 37505, 42355, 43479,
          36501, 33125, 33652, 69849, 74464],
         bold=True, fill=TOTAL_FILL)
r += 1
blank_row(ws, r); r += 1

sub_header(ws, r, "Investing Cash Flow", fill=PatternFill("solid", fgColor="4472C4"))
r += 1

data_row(ws, r, "Investments in Property & Equipment", "$000s",
         [-15000]*10, fill=LIGHT_BLUE_FILL, indent=2)
r += 1

data_row(ws, r, "Cash from Investing", "$000s",
         [-15000]*10, bold=True, fill=TOTAL_FILL)
r += 1
blank_row(ws, r); r += 1

sub_header(ws, r, "Financing Cash Flow", fill=PatternFill("solid", fgColor="4472C4"))
r += 1

fin_items = [
    ("Issuance (Repayment) of Debt", "$000s",
     [0, 0, -20000, 0, 0, 0, 0, -20000, 0, 0]),
    ("Issuance (Repayment) of Equity", "$000s",
     [70000, 0, 0, 0, 0, 0, 0, 0, 0, 0]),
]
for j, (label, unit, vals) in enumerate(fin_items):
    fill = LIGHT_BLUE_FILL if j % 2 == 0 else None
    data_row(ws, r, label, unit, vals, fill=fill, indent=2)
    r += 1

data_row(ws, r, "Cash from Financing", "$000s",
         [70000, 0, -20000, 0, 0, 0, 0, -20000, 0, 0],
         bold=True, fill=TOTAL_FILL)
r += 1
blank_row(ws, r); r += 1

data_row(ws, r, "Net Increase (Decrease) in Cash", "$000s",
         [67971, 13239, 2505, 27355, 28479,
          21501, 18125, -1348, 54849, 59464],
         bold=True, fill=LIGHT_BLUE_FILL)
r += 1

data_row(ws, r, "Opening Cash Balance", "$000s",
         [0, 67971, 81210, 83715, 111070,
          139549, 161050, 179174, 177827, 232676],
         fill=LIGHT_BLUE_FILL)
r += 1

data_row(ws, r, "Closing Cash Balance", "$000s",
         [67971, 81210, 83715, 111070, 139549,
          161050, 179174, 177827, 232676, 292140],
         bold=True, fill=TOTAL_FILL,
         border=Border(top=Side(style="thin"), bottom=Side(style="double")))
r += 1
blank_row(ws, r); r += 2

# ═══════════════════════════════════════════════════════════════════════════════
# SUPPORTING SCHEDULES
# ═══════════════════════════════════════════════════════════════════════════════
section_header(ws, r, "SUPPORTING SCHEDULES")
r += 1

# Working Capital
sub_header(ws, r, "Working Capital Schedule", fill=PatternFill("solid", fgColor="4472C4"))
r += 1

wc_items = [
    ("Accounts Receivable", "$000s",
     [5100, 5904, 6567, 7117, 7538,
      8179, 8997, 9896, 10886, 11975]),
    ("Inventory", "$000s",
     [7805, 9601, 9825, 10531, 11342,
      15267, 21142, 27490, 21772, 23284]),
    ("Accounts Payable", "$000s",
     [3902, 4800, 4912, 5266, 5670,
      7061, 8692, 10171, 8056, 8615]),
    ("Net Working Capital (NWC)", "$000s",
     [9003, 10705, 11480, 12382, 13210,
      16385, 21447, 27215, 24603, 26644]),
    ("Change in NWC", "$000s",
     [9003, 1702, 775, 902, 828,
      3175, 5062, 5768, -2613, 2041]),
]
for j, (label, unit, vals) in enumerate(wc_items):
    bold_flag = label in ("Net Working Capital (NWC)", "Change in NWC")
    fill = TOTAL_FILL if bold_flag else (LIGHT_BLUE_FILL if j % 2 == 0 else None)
    data_row(ws, r, label, unit, vals, fill=fill, bold=bold_flag, indent=2)
    r += 1

blank_row(ws, r); r += 1

# Depreciation Schedule
sub_header(ws, r, "Depreciation Schedule (PP&E Roll-forward)",
           fill=PatternFill("solid", fgColor="4472C4"))
r += 1

dep_items = [
    ("PP&E Opening Balance", "$000s",
     [50000, 45500, 42350, 40145, 38601,
      37521, 39389, 40603, 41392, 41905]),
    ("Plus: Capital Expenditures", "$000s",
     [15000]*10),
    ("Less: Depreciation & Amortization", "$000s",
     [19500, 18150, 17205, 16544, 16080,
      13132, 13786, 14211, 14487, 14667]),
    ("PP&E Closing Balance", "$000s",
     [45500, 42350, 40145, 38601, 37521,
      39389, 40603, 41392, 41905, 42238]),
]
for j, (label, unit, vals) in enumerate(dep_items):
    bold_flag = "Closing" in label
    fill = TOTAL_FILL if bold_flag else (LIGHT_BLUE_FILL if j % 2 == 0 else None)
    data_row(ws, r, label, unit, vals, fill=fill, bold=bold_flag, indent=2)
    r += 1

blank_row(ws, r); r += 1

# Debt & Interest Schedule
sub_header(ws, r, "Debt & Interest Schedule",
           fill=PatternFill("solid", fgColor="4472C4"))
r += 1

debt_items = [
    ("Debt Opening Balance", "$000s",
     [50000, 50000, 50000, 30000, 30000,
      30000, 30000, 30000, 10000, 10000]),
    ("Issuance (Repayment)", "$000s",
     [0, 0, -20000, 0, 0, 0, 0, -20000, 0, 0]),
    ("Debt Closing Balance", "$000s",
     [50000, 50000, 30000, 30000, 30000,
      30000, 30000, 10000, 10000, 10000]),
    ("Interest Expense", "$000s",
     [2500, 2500, 1500, 1500, 1500,
      3000, 3000, 2000, 1000, 1000]),
]
for j, (label, unit, vals) in enumerate(debt_items):
    bold_flag = "Closing" in label or "Interest Expense" in label
    fill = TOTAL_FILL if bold_flag else (LIGHT_BLUE_FILL if j % 2 == 0 else None)
    data_row(ws, r, label, unit, vals, fill=fill, bold=bold_flag, indent=2)
    r += 1

# ── Freeze panes ──────────────────────────────────────────────────────────────
ws.freeze_panes = "D4"

# ── Print setup ──────────────────────────────────────────────────────────────
ws.page_setup.orientation = "landscape"
ws.page_setup.fitToPage  = True
ws.page_setup.fitToWidth = 1

out = "/home/daytona/workspace/financial-model/3_Statement_Financial_Model.xlsx"
wb.save(out)
print(f"Saved: {out}")

cd /home/daytona/workspace/financial-model && python build_model.py

~/financial-model/build_model.py

Successfully replaced 1 instance(s) of the string in '/home/daytona/workspace/financial-model/build_model.py'

cd /home/daytona/workspace/financial-model && python build_model.py

ls -la /home/daytona/workspace/financial-model/ && python3 -c "import openpyxl; …

~/financial-model/build_model.py

Successfully replaced 1 instance(s) of the string in '/home/daytona/workspace/financial-model/build_model.py'

cd /home/daytona/workspace/financial-model && python build_model.py

~/financial-model/build_model.py

Successfully replaced 1 instance(s) of the string in '/home/daytona/workspace/financial-model/build_model.py'

cd /home/daytona/workspace/financial-model && python build_model.py

ls -lh /home/daytona/workspace/financial-model/3_Statement_Financial_Model.xlsx

~/financial-model/build_forecast.py

"""
3-Statement Financial Model with Excel Formulas
Historical: 2020-2024 (hardcoded blue)
Forecast:   2025-2029 (formula-driven black)
New assumptions from image applied.
"""
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
import shutil

wb = Workbook()
ws = wb.active
ws.title = "3-Statement Model"

# ── Fonts ─────────────────────────────────────────────────────────────────────
def font(bold=False, color="000000", size=10, italic=False):
    return Font(name="Arial", size=size, bold=bold, color=color, italic=italic)

BLUE_IN   = font(color="0000FF")          # hardcoded input
BLUE_IN_B = font(color="0000FF", bold=True)
BLACK     = font()                         # formula
BLACK_B   = font(bold=True)
WHITE_B   = font(color="FFFFFF", bold=True)
GREY_IT   = font(color="595959", italic=True, size=9)
GREEN_B   = font(color="375623", bold=True)

# ── Fills ─────────────────────────────────────────────────────────────────────
def fill(hex_): return PatternFill("solid", fgColor=hex_)

F_DARK   = fill("1F3864")
F_MID    = fill("2E75B6")
F_SUBHDR = fill("4472C4")
F_HIST   = fill("DDEBF7")
F_FCST   = fill("E2EFDA")
F_ALT    = fill("D9E1F2")
F_TOTAL  = fill("BDD7EE")
F_CHECK  = fill("C6EFCE")
F_NONE   = None

# ── Borders ──────────────────────────────────────────────────────────────────
thin   = Side(style="thin",   color="BFBFBF")
medium = Side(style="medium", color="2E75B6")
double = Side(style="double", color="2E75B6")

def border(top=None, bottom=None, left=None, right=None):
    return Border(top=top or thin, bottom=bottom or thin,
                  left=left or thin, right=right or thin)

B_THIN   = border()
B_DOUBLE = Border(top=thin, bottom=double, left=thin, right=thin)

# ── Formats ───────────────────────────────────────────────────────────────────
FMT_NUM   = '#,##0;(#,##0);"-"'
FMT_PCT   = '0.0%;(0.0%);"-"'
FMT_DAYS  = '#,##0;(#,##0);"-"'
FMT_CHK   = '0.0000;(0.0000);"-"'

# ── Layout ────────────────────────────────────────────────────────────────────
#  Col A = labels, B = spacer, C = units, D-H = hist 2020-2024, I-M = fcst 2025-2029
LABEL_COL = 1   # A
UNIT_COL  = 3   # C
HIST_START = 4  # D
FCST_START = 9  # I
ALL_YEARS  = list(range(2020, 2030))   # 10 years
HIST_YEARS = list(range(2020, 2025))   # D..H
FCST_YEARS = list(range(2025, 2030))   # I..M
N_HIST = 5
N_FCST = 5
N_TOTAL = 10

def c(year_idx):   # 0-based → column number
    return HIST_START + year_idx

def cr(year_idx, row):   # cell reference string
    return f"{get_column_letter(c(year_idx))}{row}"

# ── Column widths ─────────────────────────────────────────────────────────────
ws.column_dimensions["A"].width = 40
ws.column_dimensions["B"].width = 1.5
ws.column_dimensions["C"].width = 11
for i in range(N_TOTAL):
    ws.column_dimensions[get_column_letter(c(i))].width = 13

# ══════════════════════════════════════════════════════════════════════════════
# Helper functions
# ══════════════════════════════════════════════════════════════════════════════

def set_cell(row, col_, value=None, formula=None, font_=None, fill_=None,
             fmt=None, align=None, border_=None, wrap=False):
    cell = ws.cell(row=row, column=col_)
    if formula is not None:
        cell.value = formula
    elif value is not None:
        cell.value = value
    if font_:  cell.font  = font_
    if fill_:  cell.fill  = fill_
    if fmt:    cell.number_format = fmt
    if border_: cell.border = border_
    cell.alignment = align or Alignment(horizontal="right", vertical="center",
                                        wrap_text=wrap)
    return cell

def label(row, text, font_=None, fill_=None, indent=1, bold=False):
    cell = ws.cell(row=row, column=LABEL_COL, value=text)
    cell.font  = font_ or Font(name="Arial", size=10, bold=bold)
    if fill_: cell.fill = fill_
    cell.alignment = Alignment(horizontal="left", vertical="center", indent=indent)

def unit_cell(row, text):
    cell = ws.cell(row=row, column=UNIT_COL, value=text)
    cell.font      = GREY_IT
    cell.alignment = Alignment(horizontal="center", vertical="center")

def section_hdr(row, text, f=F_DARK):
    ws.merge_cells(start_row=row, start_column=1,
                   end_row=row, end_column=c(N_TOTAL-1))
    cell = ws.cell(row=row, column=1, value=text)
    cell.font  = WHITE_B
    cell.fill  = f
    cell.alignment = Alignment(horizontal="left", vertical="center", indent=1)
    ws.row_dimensions[row].height = 18

def sub_hdr(row, text, f=F_SUBHDR):
    ws.merge_cells(start_row=row, start_column=1,
                   end_row=row, end_column=c(N_TOTAL-1))
    cell = ws.cell(row=row, column=1, value=text)
    cell.font  = WHITE_B
    cell.fill  = f
    cell.alignment = Alignment(horizontal="left", vertical="center", indent=2)
    ws.row_dimensions[row].height = 16

def blank(row, height=5):
    ws.row_dimensions[row].height = height

def data_cells(row, hist_vals, fcst_formulas, fmt=FMT_NUM, fill_=None,
               bold=False, is_pct=False, border_=None):
    """Write historical hardcoded + forecast formula cells for a data row."""
    f_hist = font(bold=bold, color="0000FF")
    f_fcst = font(bold=bold, color="000000")
    for i, v in enumerate(hist_vals):
        cell = ws.cell(row=row, column=c(i))
        cell.value  = v
        cell.font   = f_hist
        cell.number_format = fmt
        cell.alignment = Alignment(horizontal="right", vertical="center")
        if fill_: cell.fill = fill_
        if border_: cell.border = border_
    for j, formula in enumerate(fcst_formulas):
        cell = ws.cell(row=row, column=c(N_HIST + j))
        cell.value  = formula
        cell.font   = f_fcst
        cell.number_format = fmt
        cell.alignment = Alignment(horizontal="right", vertical="center")
        if fill_: cell.fill = fill_
        if border_: cell.border = border_

# ══════════════════════════════════════════════════════════════════════════════
# ROW INDEX REGISTRY  – we assign row numbers as we go
# ══════════════════════════════════════════════════════════════════════════════
R = {}   # name → row number

# ── ROW 1: Title ──────────────────────────────────────────────────────────────
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=c(N_TOTAL-1))
cell = ws.cell(row=1, column=1, value="3-Statement Financial Model  ($000's)")
cell.font = Font(name="Arial", size=14, bold=True, color="1F3864")
cell.alignment = Alignment(horizontal="left", vertical="center", indent=1)
ws.row_dimensions[1].height = 28

# ── ROW 2: Period labels ──────────────────────────────────────────────────────
ws.merge_cells(start_row=2, start_column=c(0), end_row=2, end_column=c(N_HIST-1))
hc = ws.cell(row=2, column=c(0), value="Historical Results")
hc.font = Font(name="Arial", size=10, bold=True, color="1F3864")
hc.fill = F_HIST
hc.alignment = Alignment(horizontal="center", vertical="center")

ws.merge_cells(start_row=2, start_column=c(N_HIST), end_row=2, end_column=c(N_TOTAL-1))
fc = ws.cell(row=2, column=c(N_HIST), value="Forecast Period")
fc.font = Font(name="Arial", size=10, bold=True, color="1F3864")
fc.fill = F_FCST
fc.alignment = Alignment(horizontal="center", vertical="center")
ws.row_dimensions[2].height = 16

# ── ROW 3: Year headers ───────────────────────────────────────────────────────
ws.cell(row=3, column=UNIT_COL, value="Units").font = GREY_IT
ws.cell(row=3, column=UNIT_COL).alignment = Alignment(horizontal="center")
for i, yr in enumerate(ALL_YEARS):
    cell = ws.cell(row=3, column=c(i), value=str(yr))
    cell.font  = Font(name="Arial", size=10, bold=True,
                      color="FFFFFF" if i >= N_HIST else "1F3864")
    cell.fill  = F_FCST if i >= N_HIST else F_HIST
    cell.alignment = Alignment(horizontal="center", vertical="center")
    cell.border = B_THIN
ws.row_dimensions[3].height = 16

# ── ROW 4: BS Check ───────────────────────────────────────────────────────────
ws.cell(row=4, column=LABEL_COL, value="Balance Sheet Check").font = BLACK_B
ws.cell(row=4, column=LABEL_COL).alignment = Alignment(horizontal="left", indent=1)
# will be filled in after BS section
R["bs_check_row"] = 4
blank(5)

# ══════════════════════════════════════════════════════════════════════════════
# ASSUMPTIONS  rows 6+
# ══════════════════════════════════════════════════════════════════════════════
r = 6
section_hdr(r, "ASSUMPTIONS")
r += 1
sub_hdr(r, "Key Assumptions")
r += 1
R["assump_start"] = r

# We store each assumption row so income statement can reference it
assump_rows = {}

assumptions = [
    # (key, label, unit, hist_vals, fcst_vals, fmt)
    ("rev_growth",  "Revenue Growth (% Change)",            "%",
     [None, 0.158, 0.112, 0.084, 0.059],
     [0.08,  0.07,  0.06,  0.05, 0.10], FMT_PCT),

    ("cogs_pct",    "Cost of Goods Sold (% of Revenue)",    "%",
     [0.383, 0.407, 0.374, 0.370, 0.376],
     [0.42,  0.47,  0.50,  0.36, 0.35],  FMT_PCT),

    ("sal_pct",     "Salaries and Benefits (% of Revenue)", "%",
     [0.259, 0.192, 0.182, 0.162, 0.167],
     [0.18,  0.20,  0.16,  0.17, 0.19],  FMT_PCT),

    ("rent",        "Rent and Overhead ($000's)",           "$000s",
     [10963, 10125, 10087, 11020, 11412],
     [15000, 20000, 20000, 23000, 25000], FMT_NUM),

    ("da_pct",      "Depreciation & Amortization (% of PP&E)", "%",
     [0.429, 0.429, 0.429, 0.429, 0.429],
     [0.35,  0.35,  0.35,  0.35, 0.35],  FMT_PCT),

    ("int_pct",     "Interest (% of Debt)",                 "%",
     [0.050, 0.050, 0.050, 0.050, 0.050],
     [0.11,  0.12,  0.11,  0.10, 0.11],  FMT_PCT),

    ("tax_rate",    "Tax Rate (% of Earnings Before Tax)",  "%",
     [0.312, 0.292, 0.287, 0.290, 0.291],
     [0.28,  0.28,  0.28,  0.28, 0.28],  FMT_PCT),

    ("ar_days",     "Accounts Receivable (Days)",           "days",
     [18, 18, 18, 18, 18],
     [18, 18, 18, 18, 18],               FMT_DAYS),

    ("inv_days",    "Inventory (Days)",                     "days",
     [73, 73, 73, 73, 73],
     [80, 90, 100, 100, 100],            FMT_DAYS),

    ("ap_days",     "Accounts Payable (Days)",              "days",
     [36, 36, 36, 37, 36],
     [37, 37, 37, 37, 37],               FMT_DAYS),

    ("capex",       "Capital Expenditures ($000's)",        "$000s",
     [15000]*5,
     [15000]*5,                          FMT_NUM),

    ("debt_chg",    "Debt Issuance (Repayment) ($000's)",   "$000s",
     [0, 0, -20000, 0, 0],
     [0, 0, -20000, 0, 0],              FMT_NUM),

    ("eq_chg",      "Equity Issued (Repaid) ($000's)",      "$000s",
     [70000, 0, 0, 0, 0],
     [0, 0, 0, 0, 0],                   FMT_NUM),

    ("days",        "Days in Period",                       "days",
     [365]*5,
     [365]*5,                           FMT_DAYS),
]

for idx, (key, lbl, unit, h_vals, f_vals, fmt) in enumerate(assumptions):
    fill_ = F_ALT if idx % 2 == 0 else F_NONE
    label(r, lbl, fill_=fill_, indent=2)
    unit_cell(r, unit)
    # All assumption cells are hardcoded blue (inputs)
    for i, v in enumerate(h_vals):
        cell = ws.cell(row=r, column=c(i))
        cell.value  = v
        cell.font   = BLUE_IN
        cell.number_format = fmt
        cell.alignment = Alignment(horizontal="right")
        if fill_: cell.fill = fill_
    for j, v in enumerate(f_vals):
        cell = ws.cell(row=r, column=c(N_HIST + j))
        cell.value  = v
        cell.font   = BLUE_IN
        cell.number_format = fmt
        cell.alignment = Alignment(horizontal="right")
        if fill_: cell.fill = fill_
    assump_rows[key] = r
    r += 1

blank(r); r += 1

# ══════════════════════════════════════════════════════════════════════════════
# INCOME STATEMENT
# ══════════════════════════════════════════════════════════════════════════════
r += 1
section_hdr(r, "INCOME STATEMENT")
r += 1

# ── Revenue ───────────────────────────────────────────────────────────────────
R["revenue"] = r
label(r, "Revenue", bold=True)
unit_cell(r, "$000s")
hist_rev = [102007, 118086, 131345, 142341, 150772]
# Forecast: prev_rev * (1 + growth_rate)
fcst_rev = []
for j in range(N_FCST):
    col_idx = N_HIST + j
    prev = cr(col_idx - 1, r)          # prior year revenue (same row)
    grw  = cr(col_idx, assump_rows["rev_growth"])
    fcst_rev.append(f"={prev}*(1+{grw})")
data_cells(r, hist_rev, fcst_rev, bold=True)
r += 1

# ── COGS ─────────────────────────────────────────────────────────────────────
R["cogs"] = r
label(r, "Cost of Goods Sold (COGS)", fill_=F_ALT)
unit_cell(r, "$000s")
hist_cogs = [39023, 48004, 49123, 52654, 56710]
fcst_cogs = [f"={cr(N_HIST+j, R['revenue'])}*{cr(N_HIST+j, assump_rows['cogs_pct'])}"
             for j in range(N_FCST)]
data_cells(r, hist_cogs, fcst_cogs, fill_=F_ALT)
r += 1

# ── Gross Profit ──────────────────────────────────────────────────────────────
R["gross_profit"] = r
label(r, "Gross Profit", bold=True, fill_=F_TOTAL)
unit_cell(r, "$000s")
hist_gp = [62984, 70082, 82222, 89687, 94062]
fcst_gp = [f"={cr(N_HIST+j, R['revenue'])}-{cr(N_HIST+j, R['cogs'])}"
           for j in range(N_FCST)]
data_cells(r, hist_gp, fcst_gp, bold=True, fill_=F_TOTAL, border_=B_DOUBLE)
r += 1
blank(r); r += 1

sub_hdr(r, "Operating Expenses")
r += 1

# ── Salaries ─────────────────────────────────────────────────────────────────
R["salaries"] = r
label(r, "Salaries and Benefits", fill_=F_ALT)
unit_cell(r, "$000s")
hist_sal = [26427, 22658, 23872, 23002, 25245]
fcst_sal = [f"={cr(N_HIST+j, R['revenue'])}*{cr(N_HIST+j, assump_rows['sal_pct'])}"
            for j in range(N_FCST)]
data_cells(r, hist_sal, fcst_sal, fill_=F_ALT, indent=2)
r += 1

# ── Rent ─────────────────────────────────────────────────────────────────────
R["rent"] = r
label(r, "Rent and Overhead")
unit_cell(r, "$000s")
hist_rent = [10963, 10125, 10087, 11020, 11412]
fcst_rent = [f"={cr(N_HIST+j, assump_rows['rent'])}"
             for j in range(N_FCST)]
data_cells(r, hist_rent, fcst_rent)
r += 1

# ── D&A (needs PPE – placeholder; will ref PPE schedule) ─────────────────────
R["da"] = r
label(r, "Depreciation & Amortization", fill_=F_ALT)
unit_cell(r, "$000s")
hist_da = [19500, 18150, 17205, 16544, 16080]
# DA = DA% * PPE_opening (PPE schedule row defined later – we'll use a forward ref string)
# We set these after defining PPE rows; store placeholder and fix later
data_cells(r, hist_da, ["=0"]*N_FCST, fill_=F_ALT)   # temp, fixed below
r += 1

# ── Interest (needs Debt schedule) ───────────────────────────────────────────
R["interest"] = r
label(r, "Interest")
unit_cell(r, "$000s")
hist_int = [2500, 2500, 1500, 1500, 1500]
data_cells(r, hist_int, ["=0"]*N_FCST)   # temp, fixed below
r += 1

# ── Total Expenses ────────────────────────────────────────────────────────────
R["total_exp"] = r
label(r, "Total Expenses", bold=True, fill_=F_TOTAL)
unit_cell(r, "$000s")
hist_te = [59390, 53433, 52664, 52066, 54237]
fcst_te = [f"={cr(N_HIST+j, R['salaries'])}+{cr(N_HIST+j, R['rent'])}"
           f"+{cr(N_HIST+j, R['da'])}+{cr(N_HIST+j, R['interest'])}"
           for j in range(N_FCST)]
data_cells(r, hist_te, fcst_te, bold=True, fill_=F_TOTAL)
r += 1
blank(r); r += 1

# ── EBT ──────────────────────────────────────────────────────────────────────
R["ebt"] = r
label(r, "Earnings Before Tax", bold=True, fill_=F_ALT)
unit_cell(r, "$000s")
hist_ebt = [3594, 16649, 29558, 37621, 39825]
fcst_ebt = [f"={cr(N_HIST+j, R['gross_profit'])}-{cr(N_HIST+j, R['total_exp'])}"
            for j in range(N_FCST)]
data_cells(r, hist_ebt, fcst_ebt, bold=True, fill_=F_ALT)
r += 1

# ── Taxes ─────────────────────────────────────────────────────────────────────
R["taxes"] = r
label(r, "Taxes", fill_=F_ALT)
unit_cell(r, "$000s")
hist_tax = [1120, 4858, 8483, 10908, 11598]
fcst_tax = [f"={cr(N_HIST+j, R['ebt'])}*{cr(N_HIST+j, assump_rows['tax_rate'])}"
            for j in range(N_FCST)]
data_cells(r, hist_tax, fcst_tax, fill_=F_ALT)
r += 1

# ── Net Earnings ──────────────────────────────────────────────────────────────
R["net_earn"] = r
label(r, "Net Earnings", bold=True, fill_=F_TOTAL)
unit_cell(r, "$000s")
hist_ne = [2474, 11791, 21075, 26713, 28227]
fcst_ne = [f"={cr(N_HIST+j, R['ebt'])}-{cr(N_HIST+j, R['taxes'])}"
           for j in range(N_FCST)]
data_cells(r, hist_ne, fcst_ne, bold=True, fill_=F_TOTAL, border_=B_DOUBLE)
r += 1
blank(r); r += 2

# ══════════════════════════════════════════════════════════════════════════════
# SUPPORTING SCHEDULES  (needed before BS / CFS)
# ══════════════════════════════════════════════════════════════════════════════
section_hdr(r, "SUPPORTING SCHEDULES")
r += 1

# ── Working Capital Schedule ──────────────────────────────────────────────────
sub_hdr(r, "Working Capital Schedule")
r += 1

# AR = Revenue * AR_days / Days_in_period
R["ar"] = r
label(r, "Accounts Receivable", fill_=F_ALT)
unit_cell(r, "$000s")
hist_ar = [5100, 5904, 6567, 7117, 7538]
fcst_ar = [f"={cr(N_HIST+j, R['revenue'])}*{cr(N_HIST+j, assump_rows['ar_days'])}"
           f"/{cr(N_HIST+j, assump_rows['days'])}"
           for j in range(N_FCST)]
data_cells(r, hist_ar, fcst_ar, fill_=F_ALT, indent=2)
r += 1

# Inventory = COGS * Inv_days / Days_in_period
R["inventory"] = r
label(r, "Inventory")
unit_cell(r, "$000s")
hist_inv = [7805, 9601, 9825, 10531, 11342]
fcst_inv = [f"={cr(N_HIST+j, R['cogs'])}*{cr(N_HIST+j, assump_rows['inv_days'])}"
            f"/{cr(N_HIST+j, assump_rows['days'])}"
            for j in range(N_FCST)]
data_cells(r, hist_inv, fcst_inv, indent=2)
r += 1

# AP = COGS * AP_days / Days_in_period
R["ap"] = r
label(r, "Accounts Payable", fill_=F_ALT)
unit_cell(r, "$000s")
hist_ap = [3902, 4800, 4912, 5266, 5670]
fcst_ap = [f"={cr(N_HIST+j, R['cogs'])}*{cr(N_HIST+j, assump_rows['ap_days'])}"
           f"/{cr(N_HIST+j, assump_rows['days'])}"
           for j in range(N_FCST)]
data_cells(r, hist_ap, fcst_ap, fill_=F_ALT, indent=2)
r += 1

# NWC = AR + Inventory - AP
R["nwc"] = r
label(r, "Net Working Capital (NWC)", bold=True, fill_=F_TOTAL)
unit_cell(r, "$000s")
hist_nwc = [9003, 10705, 11480, 12382, 13210]
fcst_nwc = [f"={cr(N_HIST+j, R['ar'])}+{cr(N_HIST+j, R['inventory'])}"
            f"-{cr(N_HIST+j, R['ap'])}"
            for j in range(N_FCST)]
data_cells(r, hist_nwc, fcst_nwc, bold=True, fill_=F_TOTAL)
r += 1

# Change in NWC = NWC_cur - NWC_prior
R["d_nwc"] = r
label(r, "Change in NWC", bold=True, fill_=F_TOTAL)
unit_cell(r, "$000s")
hist_dnwc = [9003, 1702, 775, 902, 828]
fcst_dnwc = [f"={cr(N_HIST+j, R['nwc'])}-{cr(N_HIST+j-1, R['nwc'])}"
             for j in range(N_FCST)]
data_cells(r, hist_dnwc, fcst_dnwc, bold=True, fill_=F_TOTAL)
r += 1
blank(r); r += 1

# ── Depreciation / PPE Roll-forward ──────────────────────────────────────────
sub_hdr(r, "Depreciation Schedule (PP&E Roll-forward)")
r += 1

R["ppe_open"] = r
label(r, "PP&E Opening Balance", fill_=F_ALT)
unit_cell(r, "$000s")
hist_ppe_o = [50000, 45500, 42350, 40145, 38601]
# Opening = prior year closing (defined after closing row) – temp then fix
data_cells(r, hist_ppe_o, ["=0"]*N_FCST, fill_=F_ALT, indent=2)
r += 1

R["capex_row"] = r
label(r, "Plus: Capital Expenditures")
unit_cell(r, "$000s")
hist_capex = [15000]*5
fcst_capex = [f"={cr(N_HIST+j, assump_rows['capex'])}" for j in range(N_FCST)]
data_cells(r, hist_capex, fcst_capex, indent=2)
r += 1

R["da_sched"] = r   # same row as IS D&A
label(r, "Less: Depreciation & Amortization", fill_=F_ALT)
unit_cell(r, "$000s")
hist_da2 = [19500, 18150, 17205, 16544, 16080]
# DA = DA% * PPE_opening
fcst_da2 = [f"={cr(N_HIST+j, assump_rows['da_pct'])}*{cr(N_HIST+j, R['ppe_open'])}"
            for j in range(N_FCST)]
data_cells(r, hist_da2, fcst_da2, fill_=F_ALT, indent=2)
r += 1

R["ppe_close"] = r
label(r, "PP&E Closing Balance", bold=True, fill_=F_TOTAL)
unit_cell(r, "$000s")
hist_ppe_c = [45500, 42350, 40145, 38601, 37521]
fcst_ppe_c = [f"={cr(N_HIST+j, R['ppe_open'])}+{cr(N_HIST+j, R['capex_row'])}"
              f"-{cr(N_HIST+j, R['da_sched'])}"
              for j in range(N_FCST)]
data_cells(r, hist_ppe_c, fcst_ppe_c, bold=True, fill_=F_TOTAL)
r += 1
blank(r); r += 1

# ── Debt & Interest Schedule ──────────────────────────────────────────────────
sub_hdr(r, "Debt & Interest Schedule")
r += 1

R["debt_open"] = r
label(r, "Debt Opening Balance", fill_=F_ALT)
unit_cell(r, "$000s")
hist_debt_o = [50000, 50000, 50000, 30000, 30000]
# Opening = prior year closing (fixed after closing row)
data_cells(r, hist_debt_o, ["=0"]*N_FCST, fill_=F_ALT, indent=2)
r += 1

R["debt_chg_row"] = r
label(r, "Issuance (Repayment)")
unit_cell(r, "$000s")
hist_dc = [0, 0, -20000, 0, 0]
fcst_dc = [f"={cr(N_HIST+j, assump_rows['debt_chg'])}" for j in range(N_FCST)]
data_cells(r, hist_dc, fcst_dc, indent=2)
r += 1

R["debt_close"] = r
label(r, "Debt Closing Balance", bold=True, fill_=F_TOTAL)
unit_cell(r, "$000s")
hist_debt_c = [50000, 50000, 30000, 30000, 30000]
fcst_debt_c = [f"={cr(N_HIST+j, R['debt_open'])}+{cr(N_HIST+j, R['debt_chg_row'])}"
               for j in range(N_FCST)]
data_cells(r, hist_debt_c, fcst_debt_c, bold=True, fill_=F_TOTAL)
r += 1

R["int_exp"] = r
label(r, "Interest Expense", bold=True, fill_=F_TOTAL)
unit_cell(r, "$000s")
hist_int2 = [2500, 2500, 1500, 1500, 1500]
# Interest = int% * opening debt
fcst_int2 = [f"={cr(N_HIST+j, assump_rows['int_pct'])}*{cr(N_HIST+j, R['debt_open'])}"
             for j in range(N_FCST)]
data_cells(r, hist_int2, fcst_int2, bold=True, fill_=F_TOTAL)
r += 1
blank(r); r += 2

# ══════════════════════════════════════════════════════════════════════════════
# NOW FIX FORWARD REFERENCES
# ══════════════════════════════════════════════════════════════════════════════

# Fix IS D&A (row R["da"]) → reference da_sched row
for j in range(N_FCST):
    col_ = c(N_HIST + j)
    ws.cell(row=R["da"], column=col_).value = \
        f"={cr(N_HIST+j, R['da_sched'])}"
    ws.cell(row=R["da"], column=col_).font  = BLACK
    ws.cell(row=R["da"], column=col_).number_format = FMT_NUM

# Fix IS Interest (row R["interest"]) → reference int_exp row
for j in range(N_FCST):
    col_ = c(N_HIST + j)
    ws.cell(row=R["interest"], column=col_).value = \
        f"={cr(N_HIST+j, R['int_exp'])}"
    ws.cell(row=R["interest"], column=col_).font  = BLACK
    ws.cell(row=R["interest"], column=col_).number_format = FMT_NUM

# Fix PPE Opening = prior year PPE Closing
for j in range(N_FCST):
    col_ = c(N_HIST + j)
    prev_ppe = cr(N_HIST + j - 1, R["ppe_close"])
    ws.cell(row=R["ppe_open"], column=col_).value = f"={prev_ppe}"
    ws.cell(row=R["ppe_open"], column=col_).font  = BLACK
    ws.cell(row=R["ppe_open"], column=col_).number_format = FMT_NUM

# Fix Debt Opening = prior year Debt Closing
for j in range(N_FCST):
    col_ = c(N_HIST + j)
    prev_debt = cr(N_HIST + j - 1, R["debt_close"])
    ws.cell(row=R["debt_open"], column=col_).value = f"={prev_debt}"
    ws.cell(row=R["debt_open"], column=col_).font  = BLACK
    ws.cell(row=R["debt_open"], column=col_).number_format = FMT_NUM

# ══════════════════════════════════════════════════════════════════════════════
# BALANCE SHEET
# ══════════════════════════════════════════════════════════════════════════════
section_hdr(r, "BALANCE SHEET")
r += 1

sub_hdr(r, "Assets")
r += 1

# Cash = prior cash + net change (from CFS) – defined after CFS; placeholder
R["cash"] = r
label(r, "Cash", fill_=F_ALT)
unit_cell(r, "$000s")
hist_cash = [67971, 81210, 83715, 111070, 139549]
data_cells(r, hist_cash, ["=0"]*N_FCST, fill_=F_ALT, indent=2)
r += 1

R["bs_ar"] = r
label(r, "Accounts Receivable")
unit_cell(r, "$000s")
hist_bsar = [5100, 5904, 6567, 7117, 7538]
fcst_bsar = [f"={cr(N_HIST+j, R['ar'])}" for j in range(N_FCST)]
data_cells(r, hist_bsar, fcst_bsar, indent=2)
r += 1

R["bs_inv"] = r
label(r, "Inventory", fill_=F_ALT)
unit_cell(r, "$000s")
hist_bsinv = [7805, 9601, 9825, 10531, 11342]
fcst_bsinv = [f"={cr(N_HIST+j, R['inventory'])}" for j in range(N_FCST)]
data_cells(r, hist_bsinv, fcst_bsinv, fill_=F_ALT, indent=2)
r += 1

R["bs_ppe"] = r
label(r, "Property & Equipment (Net)")
unit_cell(r, "$000s")
hist_bsppe = [45500, 42350, 40145, 38601, 37521]
fcst_bsppe = [f"={cr(N_HIST+j, R['ppe_close'])}" for j in range(N_FCST)]
data_cells(r, hist_bsppe, fcst_bsppe, indent=2)
r += 1

R["total_assets"] = r
label(r, "Total Assets", bold=True, fill_=F_TOTAL)
unit_cell(r, "$000s")
hist_ta = [126376, 139065, 140252, 167319, 195950]
fcst_ta = [f"={cr(N_HIST+j, R['cash'])}+{cr(N_HIST+j, R['bs_ar'])}"
           f"+{cr(N_HIST+j, R['bs_inv'])}+{cr(N_HIST+j, R['bs_ppe'])}"
           for j in range(N_FCST)]
data_cells(r, hist_ta, fcst_ta, bold=True, fill_=F_TOTAL)
r += 1
blank(r); r += 1

sub_hdr(r, "Liabilities")
r += 1

R["bs_ap"] = r
label(r, "Accounts Payable", fill_=F_ALT)
unit_cell(r, "$000s")
hist_bsap = [3902, 4800, 4912, 5266, 5670]
fcst_bsap = [f"={cr(N_HIST+j, R['ap'])}" for j in range(N_FCST)]
data_cells(r, hist_bsap, fcst_bsap, fill_=F_ALT, indent=2)
r += 1

R["bs_debt"] = r
label(r, "Debt")
unit_cell(r, "$000s")
hist_bsdebt = [50000, 50000, 30000, 30000, 30000]
fcst_bsdebt = [f"={cr(N_HIST+j, R['debt_close'])}" for j in range(N_FCST)]
data_cells(r, hist_bsdebt, fcst_bsdebt, indent=2)
r += 1

R["total_liab"] = r
label(r, "Total Liabilities", bold=True, fill_=F_TOTAL)
unit_cell(r, "$000s")
hist_tl = [53902, 54800, 34912, 35266, 35670]
fcst_tl = [f"={cr(N_HIST+j, R['bs_ap'])}+{cr(N_HIST+j, R['bs_debt'])}"
           for j in range(N_FCST)]
data_cells(r, hist_tl, fcst_tl, bold=True, fill_=F_TOTAL)
r += 1
blank(r); r += 1

sub_hdr(r, "Shareholders' Equity")
r += 1

R["equity_cap"] = r
label(r, "Equity Capital", fill_=F_ALT)
unit_cell(r, "$000s")
hist_ec = [70000]*5
# Equity capital = prior + new equity issued
fcst_ec = [f"={cr(N_HIST+j-1, R['equity_cap'])}+{cr(N_HIST+j, assump_rows['eq_chg'])}"
           for j in range(N_FCST)]
data_cells(r, hist_ec, fcst_ec, fill_=F_ALT, indent=2)
r += 1

R["ret_earn"] = r
label(r, "Retained Earnings")
unit_cell(r, "$000s")
hist_re = [2474, 14265, 35340, 62053, 90280]
# RE = prior RE + net earnings
fcst_re = [f"={cr(N_HIST+j-1, R['ret_earn'])}+{cr(N_HIST+j, R['net_earn'])}"
           for j in range(N_FCST)]
data_cells(r, hist_re, fcst_re, indent=2)
r += 1

R["total_equity"] = r
label(r, "Total Shareholders' Equity", bold=True, fill_=F_TOTAL)
unit_cell(r, "$000s")
hist_te2 = [72474, 84265, 105340, 132053, 160280]
fcst_te2 = [f"={cr(N_HIST+j, R['equity_cap'])}+{cr(N_HIST+j, R['ret_earn'])}"
            for j in range(N_FCST)]
data_cells(r, hist_te2, fcst_te2, bold=True, fill_=F_TOTAL)
r += 1

R["total_le"] = r
label(r, "Total Liabilities & Shareholders' Equity", bold=True, fill_=F_TOTAL)
unit_cell(r, "$000s")
hist_tle = [126376, 139065, 140252, 167319, 195950]
fcst_tle = [f"={cr(N_HIST+j, R['total_liab'])}+{cr(N_HIST+j, R['total_equity'])}"
            for j in range(N_FCST)]
data_cells(r, hist_tle, fcst_tle, bold=True, fill_=F_TOTAL)
r += 1

# ── Balance check row ─────────────────────────────────────────────────────────
R["bs_check"] = r
label(r, "Balance Sheet Check (Assets − L&E)", bold=True)
unit_cell(r, "$000s")
for i in range(N_TOTAL):
    cell = ws.cell(row=r, column=c(i))
    if i < N_HIST:
        cell.value  = [126376-126376, 139065-139065, 140252-140252,
                       167319-167319, 195950-195950][i]
        cell.font   = GREEN_B
    else:
        j = i - N_HIST
        cell.value  = f"={cr(i, R['total_assets'])}-{cr(i, R['total_le'])}"
        cell.font   = GREEN_B
    cell.number_format = FMT_CHK
    cell.fill  = F_CHECK
    cell.alignment = Alignment(horizontal="right")
r += 1
blank(r); r += 2

# ══════════════════════════════════════════════════════════════════════════════
# CASH FLOW STATEMENT
# ══════════════════════════════════════════════════════════════════════════════
section_hdr(r, "CASH FLOW STATEMENT")
r += 1

sub_hdr(r, "Operating Cash Flow")
r += 1

R["cfs_ne"] = r
label(r, "Net Earnings", fill_=F_ALT)
unit_cell(r, "$000s")
hist_cne = [2474, 11791, 21075, 26713, 28227]
fcst_cne = [f"={cr(N_HIST+j, R['net_earn'])}" for j in range(N_FCST)]
data_cells(r, hist_cne, fcst_cne, fill_=F_ALT, indent=2)
r += 1

R["cfs_da"] = r
label(r, "Plus: Depreciation & Amortization")
unit_cell(r, "$000s")
hist_cda = [19500, 18150, 17205, 16544, 16080]
fcst_cda = [f"={cr(N_HIST+j, R['da_sched'])}" for j in range(N_FCST)]
data_cells(r, hist_cda, fcst_cda, indent=2)
r += 1

R["cfs_wc"] = r
label(r, "Less: Changes in Working Capital", fill_=F_ALT)
unit_cell(r, "$000s")
hist_cwc = [9003, 1702, 775, 902, 828]
fcst_cwc = [f"={cr(N_HIST+j, R['d_nwc'])}" for j in range(N_FCST)]
data_cells(r, hist_cwc, fcst_cwc, fill_=F_ALT, indent=2)
r += 1

R["cfo"] = r
label(r, "Cash from Operations", bold=True, fill_=F_TOTAL)
unit_cell(r, "$000s")
hist_cfo = [12971, 28239, 37505, 42355, 43479]
fcst_cfo = [f"={cr(N_HIST+j, R['cfs_ne'])}+{cr(N_HIST+j, R['cfs_da'])}"
            f"-{cr(N_HIST+j, R['cfs_wc'])}"
            for j in range(N_FCST)]
data_cells(r, hist_cfo, fcst_cfo, bold=True, fill_=F_TOTAL)
r += 1
blank(r); r += 1

sub_hdr(r, "Investing Cash Flow")
r += 1

R["cfi_capex"] = r
label(r, "Investments in Property & Equipment", fill_=F_ALT)
unit_cell(r, "$000s")
hist_capex2 = [-15000]*5
fcst_capex2 = [f"=-{cr(N_HIST+j, assump_rows['capex'])}" for j in range(N_FCST)]
data_cells(r, hist_capex2, fcst_capex2, fill_=F_ALT, indent=2)
r += 1

R["cfi"] = r
label(r, "Cash from Investing", bold=True, fill_=F_TOTAL)
unit_cell(r, "$000s")
hist_cfi = [-15000]*5
fcst_cfi = [f"={cr(N_HIST+j, R['cfi_capex'])}" for j in range(N_FCST)]
data_cells(r, hist_cfi, fcst_cfi, bold=True, fill_=F_TOTAL)
r += 1
blank(r); r += 1

sub_hdr(r, "Financing Cash Flow")
r += 1

R["cff_debt"] = r
label(r, "Issuance (Repayment) of Debt", fill_=F_ALT)
unit_cell(r, "$000s")
hist_cffd = [0, 0, -20000, 0, 0]
fcst_cffd = [f"={cr(N_HIST+j, assump_rows['debt_chg'])}" for j in range(N_FCST)]
data_cells(r, hist_cffd, fcst_cffd, fill_=F_ALT, indent=2)
r += 1

R["cff_eq"] = r
label(r, "Issuance (Repayment) of Equity")
unit_cell(r, "$000s")
hist_cffe = [70000, 0, 0, 0, 0]
fcst_cffe = [f"={cr(N_HIST+j, assump_rows['eq_chg'])}" for j in range(N_FCST)]
data_cells(r, hist_cffe, fcst_cffe, indent=2)
r += 1

R["cff"] = r
label(r, "Cash from Financing", bold=True, fill_=F_TOTAL)
unit_cell(r, "$000s")
hist_cff = [70000, 0, -20000, 0, 0]
fcst_cff = [f"={cr(N_HIST+j, R['cff_debt'])}+{cr(N_HIST+j, R['cff_eq'])}"
            for j in range(N_FCST)]
data_cells(r, hist_cff, fcst_cff, bold=True, fill_=F_TOTAL)
r += 1
blank(r); r += 1

R["net_cash_chg"] = r
label(r, "Net Increase (Decrease) in Cash", bold=True, fill_=F_ALT)
unit_cell(r, "$000s")
hist_ncc = [67971, 13239, 2505, 27355, 28479]
fcst_ncc = [f"={cr(N_HIST+j, R['cfo'])}+{cr(N_HIST+j, R['cfi'])}"
            f"+{cr(N_HIST+j, R['cff'])}"
            for j in range(N_FCST)]
data_cells(r, hist_ncc, fcst_ncc, bold=True, fill_=F_ALT)
r += 1

R["open_cash"] = r
label(r, "Opening Cash Balance", fill_=F_ALT)
unit_cell(r, "$000s")
hist_oc = [0, 67971, 81210, 83715, 111070]
# Opening = prior year closing cash
fcst_oc = [f"={cr(N_HIST+j-1, R['close_cash'])}" for j in range(N_FCST)]
# close_cash defined below – use placeholder
data_cells(r, hist_oc, ["=0"]*N_FCST, fill_=F_ALT, indent=2)
r += 1

R["close_cash"] = r
label(r, "Closing Cash Balance", bold=True, fill_=F_TOTAL)
unit_cell(r, "$000s")
hist_cc = [67971, 81210, 83715, 111070, 139549]
fcst_cc = [f"={cr(N_HIST+j, R['open_cash'])}+{cr(N_HIST+j, R['net_cash_chg'])}"
           for j in range(N_FCST)]
data_cells(r, hist_cc, fcst_cc, bold=True, fill_=F_TOTAL, border_=B_DOUBLE)
r += 1

# ── Fix opening cash (depends on closing cash row) ───────────────────────────
for j in range(N_FCST):
    col_ = c(N_HIST + j)
    prev = cr(N_HIST + j - 1, R["close_cash"])
    ws.cell(row=R["open_cash"], column=col_).value = f"={prev}"
    ws.cell(row=R["open_cash"], column=col_).font  = BLACK
    ws.cell(row=R["open_cash"], column=col_).number_format = FMT_NUM

# ── Fix BS Cash = closing cash from CFS ──────────────────────────────────────
for j in range(N_FCST):
    col_ = c(N_HIST + j)
    ws.cell(row=R["cash"], column=col_).value = \
        f"={cr(N_HIST+j, R['close_cash'])}"
    ws.cell(row=R["cash"], column=col_).font  = BLACK
    ws.cell(row=R["cash"], column=col_).number_format = FMT_NUM

# ── Populate Row 4 BS check ───────────────────────────────────────────────────
for i in range(N_TOTAL):
    cell = ws.cell(row=4, column=c(i))
    if i < N_HIST:
        cell.value = "OK"
        cell.font  = GREEN_B
        cell.fill  = F_CHECK
    else:
        j = i - N_HIST
        cell.value = f'=IF({cr(i, R["bs_check"])}=0,"OK","ERROR")'
        cell.font  = GREEN_B
        cell.fill  = F_CHECK
    cell.alignment = Alignment(horizontal="center")
    cell.border = B_THIN

# ══════════════════════════════════════════════════════════════════════════════
# FREEZE PANES & PRINT SETUP
# ══════════════════════════════════════════════════════════════════════════════
ws.freeze_panes = "D5"
ws.page_setup.orientation = "landscape"
ws.page_setup.fitToPage   = True
ws.page_setup.fitToWidth  = 1

# ── Save ──────────────────────────────────────────────────────────────────────
tmp = "/tmp/Financial_Model_Forecast.xlsx"
wb.save(tmp)
import shutil
shutil.copyfile(tmp, "/home/daytona/workspace/financial-model/Financial_Model_Forecast.xlsx")
print("Done → /home/daytona/workspace/financial-model/Financial_Model_Forecast.xlsx")

cd /home/daytona/workspace/financial-model && python build_forecast.py 2>&1

~/financial-model/build_forecast.py

Successfully replaced 1 instance(s) of the string in '/home/daytona/workspace/financial-model/build_forecast.py'

cd /home/daytona/workspace/financial-model && python build_forecast.py 2>&1

~/financial-model/build_forecast.py

Successfully replaced 1 instance(s) of the string in '/home/daytona/workspace/financial-model/build_forecast.py'

cd /home/daytona/workspace/financial-model && python build_forecast.py 2>&1

python3 - <<'EOF' from openpyxl import load_workbook wb = load_workbook("/home/d…