""" Maik 36-Month Cashflow Model Builder Generates a fully-calibrated 3-scenario xlsx model for Maik — AU AI-operated 3D-printed premium sculptural lighting brand. Run: python build_maik_model.py Output: maik_cashflow_model.xlsx """ import math from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, NamedStyle from openpyxl.formatting.rule import CellIsRule, FormulaRule from openpyxl.utils import get_column_letter from openpyxl.styles import numbers # ===================================================================== # CONSTANTS / GLOBAL ASSUMPTIONS # ===================================================================== MONTHS = 36 STARTING_CASH = 150_000 # committed at month 0 TRANCHE = 100_000 # gate-unlocked at month 6 (Base + Bull only) # Capex schedule (month -> dict of {scenario: amount}) CAPEX_SCHEDULE = { 0: { # all scenarios - initial fitout "Conservative": 15_000 + 18_000 + 8_000 + 12_000, # printers + fitout + ventilation + packaging "Base": 15_000 + 18_000 + 8_000 + 12_000, "Bull": 15_000 + 18_000 + 8_000 + 12_000, }, 4: { # +2 printers base case "Conservative": 0, "Base": 3_000, "Bull": 3_000, }, 6: { # base +2 printers, bull +4 (X1C upgrade) "Conservative": 0, "Base": 0, "Bull": 10_000, # 4x X1C reference printers }, 9: { "Conservative": 0, "Base": 3_000, "Bull": 3_000, }, 12: { # conservative gets its first expansion late "Conservative": 3_000, "Base": 2_500, "Bull": 2_500, }, } # Tranche unlocked at end of month 6 — flows into month 7 cash balance TRANCHE_MONTH = 6 TRANCHE_BY_SCENARIO = { "Conservative": 0, # gate failed "Base": 100_000, "Bull": 100_000, } # ===================================================================== # SCENARIO CONFIGURATIONS # ===================================================================== def s_curve(m, midpoint, steepness, floor, ceiling): """Logistic S-curve from floor to ceiling, centered at midpoint.""" val = floor + (ceiling - floor) / (1 + math.exp(-steepness * (m - midpoint))) return val def linear_ramp(m, start_m, end_m, start_val, end_val): """Linear ramp clamped at endpoints.""" if m <= start_m: return start_val if m >= end_m: return end_val return start_val + (end_val - start_val) * (m - start_m) / (end_m - start_m) SCENARIOS = { "Conservative": { "color": "C75450", "tagline": "Slow CVR ramp, CAC bleed, no tranche unlock", # Orders S-curve params (m, orders) "orders_curve": lambda m: int(s_curve(m, midpoint=14, steepness=0.32, floor=28, ceiling=620)), # AOV path "aov": lambda m: 189 + (200 - 189) * min(m/12, 1) + (220 - 200) * max(0, min((m-12)/12, 1)) + (235 - 220) * max(0, min((m-24)/12, 1)), # CAC ($50+ Y1, slow improvement) "cac": lambda m: 55 - linear_ramp(m, 1, 36, 0, 18), # 55 -> 37 # CVR (1.2% by month 12) "cvr": lambda m: linear_ramp(m, 1, 12, 0.008, 0.012), # Repeat rate (lower) "repeat_rate": lambda m: linear_ramp(m, 1, 12, 0.05, 0.14) if m <= 12 else linear_ramp(m, 12, 24, 0.14, 0.22) if m <= 24 else linear_ramp(m, 24, 36, 0.22, 0.28), # Gross margin (target lower end) "gross_margin": lambda m: linear_ramp(m, 1, 12, 0.55, 0.60) if m <= 12 else linear_ramp(m, 12, 24, 0.60, 0.64) if m <= 24 else linear_ramp(m, 24, 36, 0.64, 0.67), # Utilisation (40-60%) "utilisation": lambda m: min(0.40 + 0.02 * m, 0.60), # Returns rate "returns_rate": lambda m: 0.035 if m <= 12 else 0.025, # Paid media spend (scaled down — Conservative can't sustain Base spend) "paid_media": lambda m: 8_000 if m == 1 else 12_000 if m <= 3 else 14_000 if m <= 12 else 18_000 if m <= 24 else 22_000, }, "Base": { "color": "5B8DEF", "tagline": "Target CVR 1.8% by m6, CAC $32-40, utilisation 65-75%", "orders_curve": lambda m: int(s_curve(m, midpoint=10, steepness=0.38, floor=35, ceiling=1100)), "aov": lambda m: 189 + (215 - 189) * min(m/12, 1) + (245 - 215) * max(0, min((m-12)/12, 1)) + (260 - 245) * max(0, min((m-24)/12, 1)), "cac": lambda m: 45 - linear_ramp(m, 1, 12, 0, 8) - linear_ramp(m, 12, 24, 0, 5) - linear_ramp(m, 24, 36, 0, 6), # 45->37->32->26 "cvr": lambda m: linear_ramp(m, 1, 6, 0.010, 0.018) if m <= 6 else linear_ramp(m, 6, 24, 0.018, 0.024), "repeat_rate": lambda m: linear_ramp(m, 1, 12, 0.08, 0.18) if m <= 12 else linear_ramp(m, 12, 24, 0.18, 0.28) if m <= 24 else linear_ramp(m, 24, 36, 0.28, 0.35), "gross_margin": lambda m: linear_ramp(m, 1, 12, 0.60, 0.65) if m <= 12 else linear_ramp(m, 12, 24, 0.65, 0.70) if m <= 24 else linear_ramp(m, 24, 36, 0.70, 0.72), "utilisation": lambda m: min(linear_ramp(m, 1, 6, 0.25, 0.70), 0.85), "returns_rate": lambda m: 0.03 if m <= 12 else 0.02, "paid_media": lambda m: 10_000 if m == 1 else 15_000 if m <= 3 else 18_000 if m <= 12 else 25_000 if m <= 24 else 30_000, }, "Bull": { "color": "4CAF50", "tagline": "Viral moment m4-5, CVR 2.3%+, CAC $25-30 by m9", "orders_curve": lambda m: int(s_curve(m, midpoint=8, steepness=0.45, floor=42, ceiling=1700)), "aov": lambda m: 189 + (220 - 189) * min(m/9, 1) + (250 - 220) * max(0, min((m-9)/12, 1)) + (275 - 250) * max(0, min((m-21)/15, 1)), "cac": lambda m: 42 - linear_ramp(m, 1, 9, 0, 14) - linear_ramp(m, 9, 24, 0, 4), # 42->28->24 "cvr": lambda m: linear_ramp(m, 1, 4, 0.012, 0.023) if m <= 4 else linear_ramp(m, 4, 18, 0.023, 0.030), "repeat_rate": lambda m: linear_ramp(m, 1, 12, 0.10, 0.22) if m <= 12 else linear_ramp(m, 12, 24, 0.22, 0.33) if m <= 24 else linear_ramp(m, 24, 36, 0.33, 0.40), "gross_margin": lambda m: linear_ramp(m, 1, 12, 0.62, 0.67) if m <= 12 else linear_ramp(m, 12, 24, 0.67, 0.71) if m <= 24 else linear_ramp(m, 24, 36, 0.71, 0.73), "utilisation": lambda m: min(linear_ramp(m, 1, 5, 0.30, 0.78), 0.90), "returns_rate": lambda m: 0.03 if m <= 12 else 0.02, "paid_media": lambda m: 12_000 if m == 1 else 18_000 if m <= 3 else 22_000 if m <= 9 else 28_000 if m <= 24 else 35_000, }, } # ===================================================================== # UNIT ECONOMICS (within-COGS components) # ===================================================================== def cogs_per_order(m, scenario): """COGS per order — filament + electronics + packaging.""" # Filament: ~$9 month 1, dropping to $6.50 by month 18 (bulk contracts) filament = linear_ramp(m, 1, 18, 9.00, 6.50) # Electronics (USB-C driver + LED + cable): $19 -> $14 electronics = linear_ramp(m, 1, 18, 19.00, 14.00) # Packaging (custom box + insert + brand collateral): $6 -> $4.80 packaging = linear_ramp(m, 1, 12, 6.00, 4.80) # Bull case gets earlier bulk pricing if scenario == "Bull": filament *= 0.95 electronics *= 0.95 if scenario == "Conservative": filament *= 1.08 electronics *= 1.05 return filament + electronics + packaging, filament, electronics, packaging def postage_per_order(m): """Net postage cost after customer-paid shipping (AusPost ParcelPost).""" # AusPost ParcelPost ~$18-22 inc. handling. Customer pays $12 shipping standard. # Net cost ~$10 dropping to $8 with ParcelSend volume rates from month 9 return linear_ramp(m, 1, 9, 11.00, 8.00) def payment_processing_rate(): """Blended Shopify Plus + Afterpay rate.""" return 0.019 # 1.9% blended def returns_cost_per_return(): """Reverse logistics + restocking + write-off on damaged units.""" return 25.00 # ===================================================================== # CORE P&L CALCULATION # ===================================================================== def calc_month(m, scenario_name, cumulative_orders_lookup): """ Calculate one month of P&L for a given scenario. Returns dict with all line items. """ cfg = SCENARIOS[scenario_name] # --- Top of funnel --- new_orders = cfg["orders_curve"](m) # Repeat orders — based on repeat rate applied to cumulative customer base from prior 6 months prior_orders_pool = sum(cumulative_orders_lookup.get(mm, 0) for mm in range(max(1, m - 6), m)) repeat_orders = int(prior_orders_pool * cfg["repeat_rate"](m) / 6) if prior_orders_pool > 0 else 0 total_orders = new_orders + repeat_orders aov = cfg["aov"](m) revenue = total_orders * aov # --- COGS --- cogs_unit, fil, elec, pkg = cogs_per_order(m, scenario_name) cogs_total = total_orders * cogs_unit filament_total = total_orders * fil electronics_total = total_orders * elec packaging_total = total_orders * pkg # --- Postage --- postage = total_orders * postage_per_order(m) # --- Payment processing --- payment_proc = revenue * payment_processing_rate() # --- Returns --- returns_count = total_orders * cfg["returns_rate"](m) returns_refund_revenue = returns_count * aov # refunded revenue lost returns_logistics = returns_count * returns_cost_per_return() returns_total = returns_refund_revenue + returns_logistics # --- Gross profit --- gross_profit = revenue - cogs_total - postage - payment_proc - returns_total gross_margin_pct = gross_profit / revenue if revenue > 0 else 0 # --- Opex --- paid_media = cfg["paid_media"](m) # Klaviyo + Shopify Plus + apps: $400 -> $1200 saas = linear_ramp(m, 1, 12, 400, 1200) if m <= 12 else linear_ramp(m, 12, 24, 1200, 1400) # Higgsfield + LLM + Multica compute: $800 -> $2500 ai_compute = linear_ramp(m, 1, 12, 800, 1800) if m <= 12 else linear_ramp(m, 12, 36, 1800, 2500) # Studio + utilities studio = 1_500 + 200 # lease + utilities # Insurance (product liability $2M + cyber) insurance = 750 # Bookkeeping + BAS bookkeeping = 400 # AusPost extras + packaging amortised (per-month surcharge on top of per-order) auspost_extras = linear_ramp(m, 1, 12, 200, 600) # Photography + content (Higgsfield largely covers; occasional real shoots) photography = 800 if m <= 6 else 1200 # Legal / TM watch (Y1 amortised, Y2-3 settles) legal = 500 if m <= 12 else 300 # Maintenance + spares (4% of revenue) maintenance = revenue * 0.04 # Misc / contingency (3% of revenue) contingency = revenue * 0.03 total_opex = (paid_media + saas + ai_compute + studio + insurance + bookkeeping + auspost_extras + photography + legal + maintenance + contingency) # --- EBITDA --- ebitda = gross_profit - total_opex return { "month": m, "new_orders": new_orders, "repeat_orders": repeat_orders, "orders": total_orders, "aov": aov, "revenue": revenue, "filament": filament_total, "electronics": electronics_total, "packaging": packaging_total, "cogs": cogs_total, "postage": postage, "payment_proc": payment_proc, "returns": returns_total, "gross_profit": gross_profit, "gross_margin_pct": gross_margin_pct, "paid_media": paid_media, "saas": saas, "ai_compute": ai_compute, "studio": studio, "insurance": insurance, "bookkeeping": bookkeeping, "auspost_extras": auspost_extras, "photography": photography, "legal": legal, "maintenance": maintenance, "contingency": contingency, "total_opex": total_opex, "ebitda": ebitda, "cac": cfg["cac"](m), "cvr": cfg["cvr"](m), "utilisation": cfg["utilisation"](m), "returns_rate": cfg["returns_rate"](m), } def build_scenario(scenario_name): """Build full 36-month P&L for a scenario.""" rows = [] cumulative_orders = {} for m in range(1, MONTHS + 1): row = calc_month(m, scenario_name, cumulative_orders) rows.append(row) cumulative_orders[m] = row["orders"] # --- Cash balance walk --- cash = STARTING_CASH # Apply month-0 capex cash -= CAPEX_SCHEDULE[0][scenario_name] rows_with_cash = [] for row in rows: m = row["month"] # Tranche unlocks AT END of month 6 (added to cash going into month 7) if m == TRANCHE_MONTH + 1: cash += TRANCHE_BY_SCENARIO[scenario_name] row["tranche_inflow"] = TRANCHE_BY_SCENARIO[scenario_name] else: row["tranche_inflow"] = 0 # Capex for this month capex_this_month = CAPEX_SCHEDULE.get(m, {}).get(scenario_name, 0) row["capex"] = capex_this_month # Cash impact cash += row["ebitda"] - capex_this_month row["cash_balance"] = cash rows_with_cash.append(row) return rows_with_cash # ===================================================================== # STYLES # ===================================================================== HEADER_FONT = Font(name="Calibri Light", size=14, bold=True, color="FFFFFF") SUBHEADER_FONT = Font(name="Calibri Light", size=11, bold=True, color="1F1F1F") LABEL_FONT = Font(name="Calibri", size=10, bold=True, color="1F1F1F") DATA_FONT = Font(name="Consolas", size=10, color="1F1F1F") MONO_FONT = Font(name="Consolas", size=10, color="1F1F1F") TITLE_FONT = Font(name="Calibri Light", size=24, bold=True, color="1F1F1F") TAGLINE_FONT = Font(name="Calibri", size=11, italic=True, color="606060") DARK_HEADER_FILL = PatternFill("solid", fgColor="1F1F1F") GREY_FILL = PatternFill("solid", fgColor="E8E8E8") LIGHT_GREY_FILL = PatternFill("solid", fgColor="F5F5F5") THIN_BORDER = Border( left=Side(style="thin", color="D0D0D0"), right=Side(style="thin", color="D0D0D0"), top=Side(style="thin", color="D0D0D0"), bottom=Side(style="thin", color="D0D0D0"), ) CENTER = Alignment(horizontal="center", vertical="center") LEFT = Alignment(horizontal="left", vertical="center") RIGHT = Alignment(horizontal="right", vertical="center") # ===================================================================== # SHEET BUILDERS # ===================================================================== def build_cover_sheet(wb): ws = wb.create_sheet("Cover") ws.sheet_view.showGridLines = False ws.column_dimensions["A"].width = 4 ws.column_dimensions["B"].width = 80 ws["B2"] = "MAIK" ws["B2"].font = Font(name="Calibri Light", size=42, bold=True, color="1F1F1F") ws["B3"] = "3-Year Monthly Cashflow Model" ws["B3"].font = Font(name="Calibri Light", size=18, color="606060") ws["B5"] = "AU AI-Operated Premium Sculptural 3D-Printed Lighting" ws["B5"].font = TAGLINE_FONT ws["B7"] = "Three scenarios — Conservative / Base / Bull" ws["B7"].font = SUBHEADER_FONT sections = [ ("Locked Context", [ "Premium sculptural USB-C lighting only at launch", "AOV blended $189 m1 -> $215 m12 -> $245 m24 (Base case)", "12-20 evergreen SKUs + 4 numbered drops/year (50-200 units each)", "Single Shopify Plus, single Klaviyo, single ad account", "Print farm: 8x P1S + 1x X1C from day one ($15k capex)", "Studio: 120sqm light-industrial Melbourne ($1,500/mo lease)", "Fully AI-operated by 15-agent fleet (no payroll, Ven absorbs founder time)", "$150k initial committed + $100k milestone-gated tranche at month 6", "Tranche gate: CVR >=1.5%, CAC <=$40, utilisation >=60%", ]), ("Scenario Summary", [ "CONSERVATIVE — slow CVR ramp (1.2% by m12), CAC $50+, utilisation 40-60%, no tranche", "BASE — target CVR 1.8% by m6, CAC $32-40 blended, utilisation 65-75%, tranche unlocks", "BULL — CVR 2.3%+ from m4, viral PR moment, CAC $25-30 by m9, utilisation 80%+, tranche unlocks", ]), ("Workbook Structure", [ "Cover — this sheet", "Assumptions — global drivers, calibration notes, capex schedule", "Conservative — 36-month P&L + cash walk", "Base — 36-month P&L + cash walk", "Bull — 36-month P&L + cash walk", "Summary comparison — Y1 / Y2 / Y3 side-by-side metrics", ]), ] r = 9 for title, items in sections: ws.cell(row=r, column=2, value=title).font = Font(name="Calibri Light", size=14, bold=True, color="1F1F1F") r += 1 for item in items: ws.cell(row=r, column=2, value=" - " + item).font = Font(name="Calibri", size=10, color="404040") r += 1 r += 1 def build_assumptions_sheet(wb): ws = wb.create_sheet("Assumptions") ws.sheet_view.showGridLines = False ws.column_dimensions["A"].width = 4 ws.column_dimensions["B"].width = 36 for c in range(3, 7): ws.column_dimensions[get_column_letter(c)].width = 22 ws["B2"] = "GLOBAL ASSUMPTIONS & CALIBRATION" ws["B2"].font = TITLE_FONT # Header row headers = ["Driver", "Conservative", "Base", "Bull", "Calibration source"] for i, h in enumerate(headers): c = ws.cell(row=4, column=2+i, value=h) c.font = HEADER_FONT c.fill = DARK_HEADER_FILL c.alignment = CENTER rows = [ ("AOV — Month 1", "$189", "$189", "$189", "Premium homewares AU benchmark"), ("AOV — Month 12", "$200", "$215", "$220", "Bundle adoption + numbered drops"), ("AOV — Month 24", "$220", "$245", "$250", "Tested pricing power"), ("AOV — Month 36", "$235", "$260", "$275", "Premium SKU mix shift"), ("Orders — Month 1", "28", "35", "42", "S-curve floor"), ("Orders — Month 12", "~280", "~380", "~520", "Logistic inflection"), ("Orders — Month 24", "~520", "~720", "~1,100", "Repeat layer compounds"), ("Orders — Month 36", "~620", "~1,050", "~1,650", "S-curve ceiling"), ("CVR — Month 6", "1.0%", "1.8%", "2.5%", "Shopify AU homewares 1.5-2%"), ("CVR — Month 12", "1.2%", "2.0%", "2.8%", "Brand recognition lift"), ("CAC — Y1 avg", "$52", "$40", "$32", "Meta AU premium $35-55 CPA"), ("CAC — Y2 avg", "$45", "$32", "$26", "Retention compounding"), ("CAC — Y3 avg", "$38", "$26", "$22", "Brand equity flywheel"), ("Repeat rate — Y1", "10%", "18%", "22%", "Klaviyo flow + drops"), ("Repeat rate — Y2", "20%", "28%", "33%", "LTV unlocks"), ("Repeat rate — Y3", "26%", "35%", "40%", "Customer base mature"), ("Gross margin — Y1", "55-60%", "60-65%", "62-67%", "After COGS+postage+pay+returns"), ("Gross margin — Y2", "60-64%", "65-70%", "67-71%", "Bulk filament + electronics MOQ"), ("Gross margin — Y3", "64-67%", "70-72%", "71-73%", "ParcelSend bulk rates"), ("Utilisation — Month 6","45%", "70%", "78%", "8 printers x ~720hrs/mo capacity"), ("Utilisation — Y2 avg", "55%", "78%", "85%", "Sustained operating zone"), ("Returns rate — Y1", "3.5%", "3.0%", "3.0%", "AU homewares benchmark"), ("Returns rate — Y2-3", "2.5%", "2.0%", "2.0%", "Quality flywheel"), ("Tranche unlock (m6)", "FAILED", "$100k", "$100k", "Gate: CVR/CAC/util"), ] r = 5 for row in rows: for i, val in enumerate(row): c = ws.cell(row=r, column=2+i, value=val) c.font = DATA_FONT if i > 0 else LABEL_FONT c.alignment = LEFT if i == 0 or i == 4 else CENTER c.border = THIN_BORDER if r % 2 == 0: c.fill = LIGHT_GREY_FILL r += 1 # Capex schedule r += 2 ws.cell(row=r, column=2, value="CAPEX SCHEDULE").font = Font(name="Calibri Light", size=14, bold=True) r += 1 capex_headers = ["Month", "Item", "Conservative", "Base", "Bull"] for i, h in enumerate(capex_headers): c = ws.cell(row=r, column=2+i, value=h) c.font = HEADER_FONT c.fill = DARK_HEADER_FILL c.alignment = CENTER r += 1 capex_detail = [ (0, "Printers + AMS (8x P1S + 1x X1C)", 15000, 15000, 15000), (0, "Studio fitout", 18000, 18000, 18000), (0, "Ventilation + filament storage", 8000, 8000, 8000), (0, "Packaging MOQ", 12000, 12000, 12000), (4, "+2 P1S printers", 0, 3000, 3000), (6, "+4 X1C reference printers (Bull only)", 0, 0, 10000), (9, "+2 P1S printers", 0, 3000, 3000), (12, "+1 X1C reference printer", 3000, 2500, 2500), ] for cx in capex_detail: for i, val in enumerate(cx): c = ws.cell(row=r, column=2+i, value=val if i < 2 else f"${val:,}") c.font = DATA_FONT c.alignment = LEFT if i == 1 else CENTER c.border = THIN_BORDER r += 1 # COGS unit economics r += 2 ws.cell(row=r, column=2, value="COGS UNIT ECONOMICS (per order)").font = Font(name="Calibri Light", size=14, bold=True) r += 1 unit_econ_headers = ["Component", "Month 1", "Month 18+", "Notes"] for i, h in enumerate(unit_econ_headers): c = ws.cell(row=r, column=2+i, value=h) c.font = HEADER_FONT c.fill = DARK_HEADER_FILL c.alignment = CENTER r += 1 unit_econ = [ ("Filament (PLA-CF, ~800g)", "$9.00", "$6.50", "Bulk contracts from m6"), ("Electronics (USB-C + LED)", "$19.00", "$14.00", "MOQ pricing from m9"), ("Packaging (box + insert)", "$6.00", "$4.80", "Brand collateral incl."), ("Postage (net, AusPost ParcelPost)", "$11.00", "$8.00", "ParcelSend rates from m9"), ("Payment processing", "1.9% rev", "1.9% rev", "Shopify Plus + Afterpay blend"), ("Returns logistics", "$25/return", "$25/return", "Reverse + restock + writeoff"), ] for ue in unit_econ: for i, val in enumerate(ue): c = ws.cell(row=r, column=2+i, value=val) c.font = DATA_FONT c.alignment = LEFT if i == 0 or i == 3 else CENTER c.border = THIN_BORDER r += 1 def build_scenario_sheet(wb, scenario_name, rows): ws = wb.create_sheet(scenario_name) ws.sheet_view.showGridLines = False ws.freeze_panes = "B5" cfg = SCENARIOS[scenario_name] color = cfg["color"] accent_fill = PatternFill("solid", fgColor=color) # Title block ws.column_dimensions["A"].width = 38 for m in range(1, MONTHS + 1): ws.column_dimensions[get_column_letter(1 + m)].width = 13 # Y1/Y2/Y3 totals columns for c in range(MONTHS + 2, MONTHS + 5): ws.column_dimensions[get_column_letter(c)].width = 15 ws["A1"] = f"MAIK — {scenario_name.upper()} SCENARIO" ws["A1"].font = TITLE_FONT ws["A2"] = cfg["tagline"] ws["A2"].font = TAGLINE_FONT # Month header row ws.cell(row=4, column=1, value="Line item").font = HEADER_FONT ws.cell(row=4, column=1).fill = DARK_HEADER_FILL ws.cell(row=4, column=1).alignment = LEFT for m in range(1, MONTHS + 1): c = ws.cell(row=4, column=1+m, value=f"M{m}") c.font = HEADER_FONT c.fill = DARK_HEADER_FILL c.alignment = CENTER for i, label in enumerate(["Y1 Total", "Y2 Total", "Y3 Total"], start=1): c = ws.cell(row=4, column=MONTHS + 1 + i, value=label) c.font = HEADER_FONT c.fill = accent_fill c.alignment = CENTER # Line items definition: (label, key, format, is_section_header, sum_in_totals) line_items = [ ("--- TOP OF FUNNEL ---", None, None, True, False), ("New orders", "new_orders", "#,##0", False, True), ("Repeat orders", "repeat_orders", "#,##0", False, True), ("Total orders", "orders", "#,##0", False, True), ("AOV", "aov", '"$"#,##0', False, False), ("CAC (driver)", "cac", '"$"#,##0', False, False), ("CVR (driver)", "cvr", "0.0%", False, False), ("Utilisation (driver)", "utilisation", "0%", False, False), ("--- REVENUE ---", None, None, True, False), ("Revenue", "revenue", '"$"#,##0', False, True), ("--- COGS ---", None, None, True, False), ("Filament", "filament", '"$"#,##0', False, True), ("Electronics", "electronics", '"$"#,##0', False, True), ("Packaging", "packaging", '"$"#,##0', False, True), ("Total COGS", "cogs", '"$"#,##0', False, True), ("Postage (net)", "postage", '"$"#,##0', False, True), ("Payment processing", "payment_proc", '"$"#,##0', False, True), ("Returns + refunds", "returns", '"$"#,##0', False, True), ("--- GROSS PROFIT ---", None, None, True, False), ("Gross profit", "gross_profit", '"$"#,##0', False, True), ("Gross margin %", "gross_margin_pct", "0.0%", False, False), ("--- OPERATING EXPENSES ---", None, None, True, False), ("Paid media (Meta + Google)", "paid_media", '"$"#,##0', False, True), ("SaaS (Klaviyo + Shopify + apps)", "saas", '"$"#,##0', False, True), ("AI compute (Higgsfield + LLM + Multica)", "ai_compute", '"$"#,##0', False, True), ("Studio + utilities", "studio", '"$"#,##0', False, True), ("Insurance", "insurance", '"$"#,##0', False, True), ("Bookkeeping + BAS", "bookkeeping", '"$"#,##0', False, True), ("AusPost extras + packaging amort.", "auspost_extras", '"$"#,##0', False, True), ("Photography + content", "photography", '"$"#,##0', False, True), ("Legal / TM watch", "legal", '"$"#,##0', False, True), ("Maintenance + spares (4% rev)", "maintenance", '"$"#,##0', False, True), ("Misc / contingency (3% rev)", "contingency", '"$"#,##0', False, True), ("Total OPEX", "total_opex", '"$"#,##0', False, True), ("--- PROFIT & CASH ---", None, None, True, False), ("EBITDA", "ebitda", '"$"#,##0;[Red]-"$"#,##0', False, True), ("Capex (outflow)", "capex", '"$"#,##0', False, True), ("Tranche inflow", "tranche_inflow", '"$"#,##0', False, True), ("Cash balance (EOM)", "cash_balance", '"$"#,##0;[Red]-"$"#,##0', False, False), ] r = 5 ebitda_row = None cash_row = None for label, key, fmt, is_header, sum_in_totals in line_items: cell = ws.cell(row=r, column=1, value=label) if is_header: cell.font = Font(name="Calibri Light", size=11, bold=True, color="FFFFFF") cell.fill = accent_fill for col in range(2, MONTHS + 5): ws.cell(row=r, column=col).fill = accent_fill r += 1 continue cell.font = LABEL_FONT cell.fill = GREY_FILL if label.startswith("Total") or label in ("Revenue", "Gross profit", "EBITDA", "Cash balance (EOM)") else LIGHT_GREY_FILL cell.alignment = LEFT cell.border = THIN_BORDER if key == "ebitda": ebitda_row = r if key == "cash_balance": cash_row = r # Monthly values y1, y2, y3 = 0, 0, 0 for m_idx, row in enumerate(rows, start=1): v = row.get(key, 0) c = ws.cell(row=r, column=1 + m_idx, value=v) c.font = DATA_FONT c.number_format = fmt c.alignment = RIGHT c.border = THIN_BORDER if sum_in_totals and isinstance(v, (int, float)): if m_idx <= 12: y1 += v elif m_idx <= 24: y2 += v else: y3 += v # For non-summing rows (AOV, %, etc), show last value of period if not sum_in_totals: if m_idx == 12: y1 = v elif m_idx == 24: y2 = v elif m_idx == 36: y3 = v # Special case: cash_balance — show ending balance of period if key == "cash_balance": y1 = rows[11]["cash_balance"] y2 = rows[23]["cash_balance"] y3 = rows[35]["cash_balance"] for i, total in enumerate([y1, y2, y3], start=1): c = ws.cell(row=r, column=MONTHS + 1 + i, value=total) c.font = Font(name="Consolas", size=10, bold=True, color="1F1F1F") c.number_format = fmt c.alignment = RIGHT c.fill = LIGHT_GREY_FILL c.border = THIN_BORDER r += 1 # Conditional formatting # Green for positive EBITDA if ebitda_row: ebitda_range = f"{get_column_letter(2)}{ebitda_row}:{get_column_letter(MONTHS+1)}{ebitda_row}" ws.conditional_formatting.add( ebitda_range, CellIsRule(operator="greaterThan", formula=["0"], fill=PatternFill("solid", fgColor="C6EFCE"), font=Font(name="Consolas", size=10, color="006100", bold=True)) ) ws.conditional_formatting.add( ebitda_range, CellIsRule(operator="lessThan", formula=["0"], fill=PatternFill("solid", fgColor="FFC7CE"), font=Font(name="Consolas", size=10, color="9C0006")) ) # Red for cash < $20k if cash_row: cash_range = f"{get_column_letter(2)}{cash_row}:{get_column_letter(MONTHS+1)}{cash_row}" ws.conditional_formatting.add( cash_range, CellIsRule(operator="lessThan", formula=["20000"], fill=PatternFill("solid", fgColor="FFC7CE"), font=Font(name="Consolas", size=10, color="9C0006", bold=True)) ) ws.conditional_formatting.add( cash_range, CellIsRule(operator="greaterThanOrEqual", formula=["100000"], fill=PatternFill("solid", fgColor="C6EFCE"), font=Font(name="Consolas", size=10, color="006100", bold=True)) ) def build_summary_sheet(wb, all_scenarios): ws = wb.create_sheet("Summary comparison") ws.sheet_view.showGridLines = False ws.column_dimensions["A"].width = 4 ws.column_dimensions["B"].width = 38 for c in range(3, 12): ws.column_dimensions[get_column_letter(c)].width = 16 ws["B2"] = "SCENARIO COMPARISON — KEY METRICS" ws["B2"].font = TITLE_FONT ws["B4"] = "Side-by-side Y1 / Y2 / Y3 comparison across all three scenarios" ws["B4"].font = TAGLINE_FONT # Build sub-headers headers_top = ["Metric", "Conservative", "", "", "Base", "", "", "Bull", "", ""] headers_sub = ["", "Y1", "Y2", "Y3", "Y1", "Y2", "Y3", "Y1", "Y2", "Y3"] r = 6 for i, h in enumerate(headers_top): c = ws.cell(row=r, column=2+i, value=h) c.font = HEADER_FONT c.fill = DARK_HEADER_FILL c.alignment = CENTER r += 1 for i, h in enumerate(headers_sub): c = ws.cell(row=r, column=2+i, value=h) c.font = SUBHEADER_FONT c.fill = GREY_FILL c.alignment = CENTER c.border = THIN_BORDER r += 1 metrics = [ ("Total revenue", "revenue", '"$"#,##0', "sum"), ("Total orders", "orders", "#,##0", "sum"), ("Avg AOV", "aov", '"$"#,##0', "avg"), ("Gross profit", "gross_profit", '"$"#,##0', "sum"), ("Avg gross margin %", "gross_margin_pct", "0.0%", "avg"), ("Total OPEX", "total_opex", '"$"#,##0', "sum"), ("Total paid media", "paid_media", '"$"#,##0', "sum"), ("EBITDA", "ebitda", '"$"#,##0;[Red]-"$"#,##0', "sum"), ("EBITDA margin %", "ebitda_margin", "0.0%", "calc"), ("Cash balance (EOY)", "cash_balance", '"$"#,##0;[Red]-"$"#,##0', "endperiod"), ("Avg CAC", "cac", '"$"#,##0', "avg"), ("Avg utilisation %", "utilisation", "0%", "avg"), ] def period_value(rows, key, kind, year): start = (year - 1) * 12 end = year * 12 period_rows = rows[start:end] if kind == "sum": return sum(r.get(key, 0) for r in period_rows) if kind == "avg": vals = [r.get(key, 0) for r in period_rows] return sum(vals) / len(vals) if vals else 0 if kind == "endperiod": return period_rows[-1].get(key, 0) if kind == "calc" and key == "ebitda_margin": rev = sum(r["revenue"] for r in period_rows) ebt = sum(r["ebitda"] for r in period_rows) return ebt / rev if rev > 0 else 0 return 0 for label, key, fmt, kind in metrics: ws.cell(row=r, column=2, value=label).font = LABEL_FONT ws.cell(row=r, column=2).alignment = LEFT ws.cell(row=r, column=2).border = THIN_BORDER col = 3 for scen in ["Conservative", "Base", "Bull"]: rows = all_scenarios[scen] for year in [1, 2, 3]: v = period_value(rows, key, kind, year) c = ws.cell(row=r, column=col, value=v) c.font = DATA_FONT c.number_format = fmt c.alignment = RIGHT c.border = THIN_BORDER if r % 2 == 0: c.fill = LIGHT_GREY_FILL col += 1 r += 1 # Key milestones section r += 2 ws.cell(row=r, column=2, value="KEY MILESTONES").font = Font(name="Calibri Light", size=14, bold=True) r += 1 milestone_headers = ["Metric", "Conservative", "Base", "Bull"] for i, h in enumerate(milestone_headers): c = ws.cell(row=r, column=2+i, value=h) c.font = HEADER_FONT c.fill = DARK_HEADER_FILL c.alignment = CENTER r += 1 def first_positive_ebitda(rows): for row in rows: if row["ebitda"] > 0: return row["month"] return None def cash_low(rows): low = min(rows, key=lambda x: x["cash_balance"]) return low["month"], low["cash_balance"] def revenue_at_month(rows, m): return rows[m-1]["revenue"] def annualised_revenue(rows, year): start = (year - 1) * 12 end = year * 12 return sum(r["revenue"] for r in rows[start:end]) milestones = [] for scen in ["Conservative", "Base", "Bull"]: rows = all_scenarios[scen] bem = first_positive_ebitda(rows) clm, clv = cash_low(rows) m12_rev = revenue_at_month(rows, 12) m36_rev = revenue_at_month(rows, 36) y2_rev = annualised_revenue(rows, 2) milestones.append({ "scenario": scen, "breakeven_month": bem, "cash_low_month": clm, "cash_low_value": clv, "month_12_rev": m12_rev, "month_36_rev": m36_rev, "y2_annualised": y2_rev, "tranche_unlocked": "Yes" if TRANCHE_BY_SCENARIO[scen] > 0 else "No", }) milestone_rows = [ ("Break-even month (first +EBITDA)", "breakeven_month", "#,##0"), ("Cash low point — month", "cash_low_month", "#,##0"), ("Cash low point — value", "cash_low_value", '"$"#,##0'), ("Tranche unlocked at m6?", "tranche_unlocked", "@"), ("Month 12 revenue (run-rate)", "month_12_rev", '"$"#,##0'), ("Y2 annualised revenue", "y2_annualised", '"$"#,##0'), ("Month 36 revenue (run-rate)", "month_36_rev", '"$"#,##0'), ] for label, key, fmt in milestone_rows: ws.cell(row=r, column=2, value=label).font = LABEL_FONT ws.cell(row=r, column=2).alignment = LEFT ws.cell(row=r, column=2).border = THIN_BORDER for i, m in enumerate(milestones, start=1): c = ws.cell(row=r, column=2+i, value=m[key]) c.font = DATA_FONT c.number_format = fmt c.alignment = CENTER c.border = THIN_BORDER if r % 2 == 0: c.fill = LIGHT_GREY_FILL r += 1 # Tranche gate evaluation r += 2 ws.cell(row=r, column=2, value="MONTH 6 TRANCHE GATE EVALUATION").font = Font(name="Calibri Light", size=14, bold=True) r += 1 gate_headers = ["Gate criterion", "Conservative (m6)", "Base (m6)", "Bull (m6)", "Threshold"] for i, h in enumerate(gate_headers): c = ws.cell(row=r, column=2+i, value=h) c.font = HEADER_FONT c.fill = DARK_HEADER_FILL c.alignment = CENTER r += 1 gate_rows = [] for scen in ["Conservative", "Base", "Bull"]: m6 = all_scenarios[scen][5] # month 6 = index 5 gate_rows.append({ "scen": scen, "cvr": m6["cvr"], "cac": m6["cac"], "util": m6["utilisation"], }) gate_metrics = [ ("CVR", "cvr", "0.0%", 0.015, ">="), ("CAC", "cac", '"$"#,##0', 40, "<="), ("Utilisation", "util", "0%", 0.60, ">="), ] for label, key, fmt, threshold, op in gate_metrics: ws.cell(row=r, column=2, value=label).font = LABEL_FONT ws.cell(row=r, column=2).border = THIN_BORDER for i, g in enumerate(gate_rows, start=1): c = ws.cell(row=r, column=2+i, value=g[key]) c.font = DATA_FONT c.number_format = fmt c.alignment = CENTER c.border = THIN_BORDER passes = (g[key] >= threshold) if op == ">=" else (g[key] <= threshold) if passes: c.fill = PatternFill("solid", fgColor="C6EFCE") c.font = Font(name="Consolas", size=10, color="006100", bold=True) else: c.fill = PatternFill("solid", fgColor="FFC7CE") c.font = Font(name="Consolas", size=10, color="9C0006", bold=True) thr_label = f"{op} {threshold:.1%}" if fmt == "0.0%" or fmt == "0%" else f"{op} ${threshold}" c = ws.cell(row=r, column=5, value=thr_label) c.font = DATA_FONT c.alignment = CENTER c.border = THIN_BORDER r += 1 # Result row ws.cell(row=r, column=2, value="GATE RESULT").font = Font(name="Calibri Light", size=11, bold=True) ws.cell(row=r, column=2).fill = GREY_FILL ws.cell(row=r, column=2).border = THIN_BORDER for i, g in enumerate(gate_rows, start=1): m6 = all_scenarios[g["scen"]][5] passes_cvr = m6["cvr"] >= 0.015 passes_cac = m6["cac"] <= 40 passes_util = m6["utilisation"] >= 0.60 all_pass = passes_cvr and passes_cac and passes_util result = "UNLOCK $100k" if all_pass else "DENIED" c = ws.cell(row=r, column=2+i, value=result) c.font = Font(name="Consolas", size=10, bold=True, color="006100" if all_pass else "9C0006") c.fill = PatternFill("solid", fgColor="C6EFCE" if all_pass else "FFC7CE") c.alignment = CENTER c.border = THIN_BORDER # ===================================================================== # MAIN # ===================================================================== def main(): wb = Workbook() # Remove default sheet wb.remove(wb.active) # Build all scenarios all_scenarios = {} for scen in ["Conservative", "Base", "Bull"]: all_scenarios[scen] = build_scenario(scen) # Build sheets in order build_cover_sheet(wb) build_assumptions_sheet(wb) for scen in ["Conservative", "Base", "Bull"]: build_scenario_sheet(wb, scen, all_scenarios[scen]) build_summary_sheet(wb, all_scenarios) output_path = "maik_cashflow_model.xlsx" wb.save(output_path) print(f"Built: {output_path}") # Print quick stats to console for verification print("\n=== QUICK STATS ===") for scen, rows in all_scenarios.items(): y1_rev = sum(r["revenue"] for r in rows[:12]) y2_rev = sum(r["revenue"] for r in rows[12:24]) y3_rev = sum(r["revenue"] for r in rows[24:36]) cash_low = min(rows, key=lambda x: x["cash_balance"]) breakeven = next((r["month"] for r in rows if r["ebitda"] > 0), None) end_cash = rows[-1]["cash_balance"] print(f"\n{scen}:") print(f" Y1 Rev: ${y1_rev:,.0f} | Y2 Rev: ${y2_rev:,.0f} | Y3 Rev: ${y3_rev:,.0f}") print(f" Cash low: ${cash_low['cash_balance']:,.0f} (month {cash_low['month']})") print(f" Break-even month: {breakeven}") print(f" End-of-month-36 cash: ${end_cash:,.0f}") if __name__ == "__main__": main()