# -*- coding: utf-8 -*-
"""
build_financial_model.py — C6 FINANCIAL MODEL as a live workbook.

Every number is either an INPUT cell (blue = sourced, yellow = assumption) or a FORMULA.
Nothing is a hard-coded result. Change a driver and the whole model recomputes.
Serves REQ-SYS-05, REQ-SYS-07, REQ-SYS-10, REQ-SYS-11.
"""
import os
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
import project_data as P
import model_params as M
import model_agedcare as AC
import trade_study as TS

ROOT = os.path.abspath(os.path.join(os.path.dirname(__file__), ".."))
OUT  = os.path.join(ROOT, "05_Outputs", "NDIS_and_Aged_Care_Financial_Model_v2.0.xlsx")
os.makedirs(os.path.dirname(OUT), exist_ok=True)

A       = "Arial"
INK     = "1B2A38"
BLUE_T  = Font(name=A, size=10, color="0000FF")                 # sourced hard input
YEL     = PatternFill("solid", fgColor="FFFF00")                # assumption to review
BLK     = Font(name=A, size=10)
BOLD    = Font(name=A, size=10, bold=True)
H1      = Font(name=A, size=14, bold=True, color=INK)
H2      = Font(name=A, size=11, bold=True, color="FFFFFF")
NOTE    = Font(name=A, size=10, italic=True, color="55636E")   # REQ-CON-04: nothing below 10pt
WARN    = Font(name=A, size=10, italic=True, bold=True, color="B03A2E")  # REQ-CON-04
HFILL   = PatternFill("solid", fgColor="1B2A38")
SUBFILL = PatternFill("solid", fgColor="EDF2F5")
THIN    = Side(style="thin", color="D5DEE4")
BOX     = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
MONEY   = '$#,##0.00;($#,##0.00);-'
MONEY0  = '$#,##0;($#,##0);-'
PCT     = '0.0%'
NUM1    = '#,##0.0'

wb = Workbook(); wb.remove(wb.active)


def _currency_sentence():
    """DEF-030. This sentence used to say every external figure was verified on the currency date.
    Fifty-three of the sources were verified eighteen days earlier, and CR-007 amended REQ-CON-03
    from an equality test to a staleness window precisely so the project would stop claiming
    otherwise — but the claim survived inside the delivered workbook, where independent
    verification V6 found it. It is now generated from the register's actual accessed dates."""
    from collections import Counter
    c = Counter(x[6] for x in P.SRC)
    parts = ", ".join("%d verified %s" % (n, d) for d, n in sorted(c.items()))
    oldest = min(c)
    import datetime as _dt
    stale = (_dt.date.fromisoformat(oldest) + _dt.timedelta(days=365)).isoformat()
    return ("This model rests on %d sources across %d research waves: %s. The currency date is %s and "
            "every source sits inside the ninety-day staleness window REQ-CON-03 sets. Under GOV-F1.16 a "
            "citation is stale twelve months after it was verified, so the FIRST source to expire does so "
            "on %s (BKL-006). Re-verify before then, not after."
            % (len(P.SRC), len(c), parts, P.CURRENCY_DATE, stale))

def sheet(name, widths):
    ws = wb.create_sheet(name)
    for i, w in enumerate(widths, start=1):
        ws.column_dimensions[get_column_letter(i)].width = w
    # REQ-CON-04, as amended. A spreadsheet is scrolled by nature; what GOV-F4.5a asks of one is
    # that scrolling does not lose the labels. Freezing column A and the rows above the first data
    # row keeps the row label and the column heading on screen on a phone. Sheets narrower than
    # seven columns do not need it, but it costs nothing and is applied uniformly.
    ws.freeze_panes = "B5"
    return ws

def title(ws, text, span, sub=None):
    ws["A1"] = text; ws["A1"].font = H1
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=span)
    if sub:
        ws["A2"] = sub; ws["A2"].font = NOTE
        ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=span)

def header(ws, row, labels, start=1):
    for i, l in enumerate(labels):
        c = ws.cell(row=row, column=start + i, value=l)
        c.font = H2; c.fill = HFILL
        c.alignment = Alignment(wrap_text=True, vertical="center")
    ws.row_dimensions[row].height = 28

def put(ws, addr, value, font=BLK, fmt=None, fill=None, wrap=False):
    c = ws[addr]; c.value = value; c.font = font
    if fmt: c.number_format = fmt
    if fill: c.fill = fill
    c.alignment = Alignment(wrap_text=wrap, vertical="top")
    c.border = BOX
    return c


# =========================================================================
# 1. README
# =========================================================================
ws = sheet("README", [30, 108])
title(ws, "%s — Financial Model  %s" % (P.PROJECT_NAME, P.BASELINE_VERSION),
      2, "OWNER: 01_System/build_financial_model.py. REVIEW: regenerate whenever model_params.py or "
         "model_agedcare.py changes. Generated %s. Do not hand-edit (GOV-D4.11)." % P.CURRENCY_DATE)
rows = [
 ("WHAT THIS MODEL DOES", "Turns the sourced NDIS price limits and the sourced SCHADS wage into a margin per billable "
  "hour, then into a break-even volume, a twelve-month cash curve and a runway figure."),
 ("HOW TO USE IT", "Change a value in the Drivers sheet. Every downstream number recomputes. Nothing in this workbook "
  "is a typed-in result EXCEPT the two comparison sheets named below."),
 ("WHERE THE FORMULAS STOP", "Structure B5:C6 and Combined B5:C9 hold values projected from the two Python models "
  "rather than cell formulas, because they compare figures whose components live in model_agedcare.py and "
  "model_params.py rather than on any sheet here. Their DIFFERENCE and TOTAL columns ARE formulas, so the "
  "comparisons recompute. Checker C37 reconciles the projected values against the models to the cent on every "
  "build, and independent verification V6 raised the earlier blanket claim that nothing here is typed in."),
 ("COLOUR LEGEND", "BLUE text  = a hard input taken from a source. The SRC-### beside it names that source.\n"
  "YELLOW fill = an assumption, not a fact. The ASM-### beside it says what breaks if it is wrong. "
  "These are the cells to challenge first.\nBLACK text = a formula. Do not overwrite it."),
 ("THE ONE THING TO READ FIRST", "UnitEconomics, row 'Contribution if administration is PAID'. If that number is "
  "small, the business only works while you personally do the rostering, notes and invoicing for nothing. "
  "That is the finding, not the margin."),
 ("WHAT THIS MODEL DOES NOT DO", "It does not model weekend or public holiday work: no arithmetically consistent "
  "SCHADS casual penalty rate could be sourced (DEF-003, BKL-002). It does not model SIL or SDA: no property "
  "parameters were supplied (BKL-005). It excludes payroll tax on the assumption the business stays below the "
  "Victorian threshold (ASM-003). It excludes income tax and GST entirely."),
 ("CURRENCY", _currency_sentence()),
 ("NOT ADVICE", "This is a decision-support model, not financial, tax or legal advice."),
]
header(ws, 4, ["", "How this workbook works"])
r = 5
for k, v in rows:
    put(ws, "A%d" % r, k, BOLD, wrap=True)
    put(ws, "B%d" % r, v, BLK, wrap=True)
    ws.row_dimensions[r].height = 15 + 13 * (v.count("\n") + len(v) // 110)
    r += 1


# =========================================================================
# 2. INPUTS — sourced rates
# =========================================================================
ws = sheet("Inputs", [46, 14, 12, 14, 14, 62])
title(ws, "Sourced rate inputs", 6,
      "BLUE = a hard number taken from a named source. Change one only if you have a better source, and update the "
      "Source column when you do.")
header(ws, 4, ["Input", "Value", "Unit", "Source", "Confidence", "Why the confidence is not High"])
INPUT_ROWS = [
 ("NDIS price limit — self-care, weekday daytime (01_011_0107_1_1)", 73.58, "AUD/hr", "SRC-002", "Medium",
  "Read from a 65-page PDF through a summarising fetch, not a raw table. RSK-007. BKL-001 closes this."),
 ("NDIS price limit — community participation, weekday (04_104_0125_6_1)", 73.58, "AUD/hr", "SRC-004", "Medium",
  "Same extraction caveat."),
 ("NDIS price limit — support coordination Level 2 (07_002_0106_8_3)", 100.14, "AUD/hr", "SRC-005", "Medium",
  "Same extraction caveat."),
 ("SCHADS SACS Level 2 casual hourly rate", 45.28, "AUD/hr", "SRC-009", "Medium",
  "fairwork.gov.au blocks automated fetching. Two independent payroll calculators agree exactly, but no first-party "
  "read was achieved. ISS-001."),
 ("SCHADS SACS Level 3 casual hourly rate (used for administration)", 50.61, "AUD/hr", "SRC-009", "Medium",
  "Same. ISS-001."),
 ("Superannuation guarantee, FY2026-27", 0.12, "rate", "SRC-011", "High", "-"),
 ("WorkCover premium, Victorian state average 2026-27", 0.018, "rate", "SRC-012", "Medium",
  "State average across all industries. The disability-sector rate sits in a Special Gazette that could not be read. ASM-004."),
 ("Portable long service leave levy, Victorian community services", 0.0165, "rate", "SRC-013", "High", "-"),
 ("Payroll tax, Victoria — assumed not payable below threshold", 0.0, "rate", "ASM-003", "Medium",
  "The threshold itself could not be confirmed from a primary source. Set this to 0.0485 to test crossing it."),
]
r = 5
for label, val, unit, src, conf, why in INPUT_ROWS:
    put(ws, "A%d" % r, label, BLK, wrap=True)
    c = put(ws, "B%d" % r, val, BLUE_T, PCT if unit == "rate" else MONEY)
    put(ws, "C%d" % r, unit)
    put(ws, "D%d" % r, src, BOLD)
    put(ws, "E%d" % r, conf, BOLD)
    put(ws, "F%d" % r, why, NOTE, wrap=True)
    ws.row_dimensions[r].height = 30
    r += 1
PRICE_CORE = "Inputs!$B$5"; PRICE_SC = "Inputs!$B$7"
WAGE_L2 = "Inputs!$B$8"; WAGE_L3 = "Inputs!$B$9"
SUPER = "Inputs!$B$10"; WC = "Inputs!$B$11"; LSL = "Inputs!$B$12"; PTAX = "Inputs!$B$13"


# =========================================================================
# 3. DRIVERS — assumptions
# =========================================================================
ws = sheet("Drivers", [50, 14, 12, 12, 12, 66])
title(ws, "Drivers — these are ASSUMPTIONS, not facts", 6,
      "YELLOW cells are the ones to argue with. Each names the ASM-### that states what breaks if it is wrong. "
      "Change one and every sheet downstream recomputes.")
header(ws, 4, ["Driver", "Value", "Unit", "ASM", "Confidence", "What happens if this is wrong"])
DRIVER_ROWS = [
 ("Administration hours required per billable hour", 0.25, "hr", "ASM-008", "Low",
  "The single most important number in this model. At 0.40 the core-supports margin turns NEGATIVE. No published "
  "benchmark exists."),
 ("Payment lag from invoice to cash", 14, "days", "ASM-007", "Low",
  "Sets the working capital figure. At 30 days it roughly doubles."),
 ("Bookkeeping and accounting", 300, "AUD/mo", "ASM-005", "Low",
  "No vendor publishes a price. Every $100 of error moves break-even by about five billable hours a month."),
 ("General operating overhead — phone, internet, memberships, travel", 200, "AUD/mo", "ASM-014", "Low",
  "Same sensitivity as bookkeeping."),
 ("Website and brand, one-off", 2500, "AUD", "ASM-006", "Low",
  "The published Australian range is $3,500 to $30,000. A lean single-operator build is assumed."),
 ("Support coordination billable utilisation", 0.55, "rate", "ASM-013", "Low",
  "Drives the entire support coordination result. At 0.35 annual net falls from about $99,000 to about $59,000."),
 ("Hours available per week if you work the business full time", 38, "hr", "ASM-011", "Low",
  "Assumes owner-operator. ACT-001 must confirm this."),
 ("Support workers screened and trained before the first shift", 3, "count", "-", "Low",
  "Scales the per-worker setup cost of about $429 each."),
 ("Owner drawings per month, base case", 0, "AUD/mo", "ASM-012", "Low",
  "Set to zero deliberately. ACT-002 must supply the real number. Every scenario in this model assumes you work for "
  "nothing until you change this."),
 ("Target billable hours per month at steady state", 300, "hr", "-", "Low",
  "Used for the working capital calculation. About 4.6 participants at 15 hours a week each."),
]
r = 5
for label, val, unit, asm, conf, effect in DRIVER_ROWS:
    put(ws, "A%d" % r, label, BLK, wrap=True)
    c = put(ws, "B%d" % r, val, BLUE_T, PCT if unit == "rate" else (MONEY0 if "AUD" in unit else NUM1), YEL)
    put(ws, "C%d" % r, unit)
    put(ws, "D%d" % r, asm, BOLD)
    put(ws, "E%d" % r, conf, BOLD)
    put(ws, "F%d" % r, effect, NOTE, wrap=True)
    ws.row_dimensions[r].height = 30
    r += 1
D_ADMIN = "Drivers!$B$5"; D_LAG = "Drivers!$B$6"; D_BOOK = "Drivers!$B$7"
D_GEN = "Drivers!$B$8"; D_WEB = "Drivers!$B$9"; D_UTIL = "Drivers!$B$10"
D_HRSWK = "Drivers!$B$11"; D_WORKERS = "Drivers!$B$12"; D_DRAW = "Drivers!$B$13"; D_TARGET = "Drivers!$B$14"


# =========================================================================
# 4. COSTS
# =========================================================================
ws = sheet("Costs", [52, 14, 14, 14, 12, 12, 50])
title(ws, "Cost stack", 7,
      "One-off, recurring monthly and recurring annual. Every line names its source. Where no vendor publishes a "
      "price, the line names the ASM instead and the confidence is Low.")
header(ws, 4, ["Cost line", "Low", "Base", "High", "Source", "Confidence", "Note"])
COST_ONEOFF = [
 ("ASIC Pty Ltd company registration", 636, 636, 636, "SRC-025", "Medium", "Secondary summary of ASIC's fee."),
 ("ASIC business name, three years", 108, 108, 108, "SRC-027", "Medium", "Secondary summary."),
 ("ABN, TFN, GST and PAYG registration", 0, 0, 0, "SRC-028", "High", "Free. Instant, or up to 20 business days if referred."),
 ("myID Strong and RAM setup", 0, 0, 0, "SRC-030", "High", "Free. PRODA closes for Commission portals 30 Sep 2026."),
 ("Policy and procedure manual, verification pack", 649, 1080, 1825, "SRC-036", "High", "Two vendors publish prices."),
 ("Verification audit fee", 3500, 4750, 6000, "SRC-034", "Medium",
  "A consultancy's estimate. NO Approved Quality Auditor publishes a rate card. ACT-007 replaces this with two real quotes."),
]
r = 5
put(ws, "A%d" % r, "ONE-OFF SETUP", BOLD, fill=SUBFILL); r += 1
first_oneoff = r
for label, lo, ba, hi, src, conf, note in COST_ONEOFF:
    put(ws, "A%d" % r, label, BLK, wrap=True)
    for col, val in zip("BCD", (lo, ba, hi)):
        put(ws, "%s%d" % (col, r), val, BLUE_T, MONEY0)
    put(ws, "E%d" % r, src, BOLD); put(ws, "F%d" % r, conf, BOLD)
    put(ws, "G%d" % r, note, NOTE, wrap=True); ws.row_dimensions[r].height = 26
    r += 1
put(ws, "A%d" % r, "Website and brand (from Drivers)", BLK)
for col in "BCD":
    put(ws, "%s%d" % (col, r), "=%s" % D_WEB, BLK, MONEY0)
put(ws, "E%d" % r, "ASM-006", BOLD); put(ws, "F%d" % r, "Low", BOLD)
put(ws, "G%d" % r, "Published Australian range is $3,500 to $30,000. This model assumes a lean build.", NOTE, wrap=True)
r += 1
put(ws, "A%d" % r, "Per-worker setup: screening $139.20 + first aid and CPR $290", BLK, wrap=True)
for col in "BCD":
    put(ws, "%s%d" % (col, r), "=(139.2+290)*(%s+1)" % D_WORKERS, BLK, MONEY0)
put(ws, "E%d" % r, "SRC-031, SRC-041", BOLD); put(ws, "F%d" % r, "High", BOLD)
put(ws, "G%d" % r, "Scales with the worker count in Drivers, plus you. Orientation module is free (SRC-042).", NOTE, wrap=True)
r += 1
last_oneoff = r - 1
put(ws, "A%d" % r, "TOTAL ONE-OFF SETUP", BOLD, fill=SUBFILL)
for col in "BCD":
    put(ws, "%s%d" % (col, r), "=SUM(%s%d:%s%d)" % (col, first_oneoff, col, last_oneoff), BOLD, MONEY0, SUBFILL)
put(ws, "E%d" % r, "", fill=SUBFILL); put(ws, "F%d" % r, "", fill=SUBFILL); put(ws, "G%d" % r, "", fill=SUBFILL)
ONEOFF_ROW = r
r += 2

put(ws, "A%d" % r, "RECURRING MONTHLY", BOLD, fill=SUBFILL); r += 1
first_mo = r
MO = [("CRM and rostering (ShiftCare Essentials, 5-staff minimum)", 45, 45, 125, "SRC-037", "High", "Vendor's published pricing."),
      ("Accounting and payroll (Xero)", 78, 78, 143, "SRC-038", "High", "Vendor's published pricing.")]
for label, lo, ba, hi, src, conf, note in MO:
    put(ws, "A%d" % r, label, BLK, wrap=True)
    for col, val in zip("BCD", (lo, ba, hi)):
        put(ws, "%s%d" % (col, r), val, BLUE_T, MONEY0)
    put(ws, "E%d" % r, src, BOLD); put(ws, "F%d" % r, conf, BOLD)
    put(ws, "G%d" % r, note, NOTE, wrap=True); r += 1
for label, ref, lo, hi, asm, note in [
        ("Bookkeeping  (base cell is the Drivers value)", D_BOOK, 200, 500, "ASM-005",
         "No vendor publishes a price. Low and high are the plausible band; the base column is wired to the "
         "Drivers sheet so changing the driver moves the base case."),
        ("General operating overhead  (base cell is the Drivers value)", D_GEN, 150, 350, "ASM-014",
         "No vendor publishes a price. Same structure: low and high bound it, the driver sets the base.")]:
    put(ws, "A%d" % r, label, BLK, wrap=True)
    put(ws, "B%d" % r, lo, BLUE_T, MONEY0)
    put(ws, "C%d" % r, "=%s" % ref, BLK, MONEY0)
    put(ws, "D%d" % r, hi, BLUE_T, MONEY0)
    put(ws, "E%d" % r, asm, BOLD); put(ws, "F%d" % r, "Low", BOLD)
    put(ws, "G%d" % r, note, NOTE, wrap=True); ws.row_dimensions[r].height = 26; r += 1
last_mo = r - 1
put(ws, "A%d" % r, "SUBTOTAL MONTHLY", BOLD, fill=SUBFILL)
for col in "BCD":
    put(ws, "%s%d" % (col, r), "=SUM(%s%d:%s%d)" % (col, first_mo, col, last_mo), BOLD, MONEY0, SUBFILL)
for col in "EFG": put(ws, "%s%d" % (col, r), "", fill=SUBFILL)
MO_ROW = r
r += 2

put(ws, "A%d" % r, "RECURRING ANNUAL", BOLD, fill=SUBFILL); r += 1
first_an = r
AN = [("ASIC company annual review", 342, 342, 342, "SRC-026", "Medium", "Secondary summary of ASIC's fee."),
      ("Insurance: public liability, professional indemnity, personal accident", 1200, 2350, 3500, "SRC-040", "Low",
       "One broker's marketing page, no named underwriter. Management liability has NO published price anywhere. ACT-008.")]
for label, lo, ba, hi, src, conf, note in AN:
    put(ws, "A%d" % r, label, BLK, wrap=True)
    for col, val in zip("BCD", (lo, ba, hi)):
        put(ws, "%s%d" % (col, r), val, BLUE_T, MONEY0)
    put(ws, "E%d" % r, src, BOLD); put(ws, "F%d" % r, conf, BOLD)
    put(ws, "G%d" % r, note, NOTE, wrap=True); ws.row_dimensions[r].height = 26; r += 1
last_an = r - 1
put(ws, "A%d" % r, "SUBTOTAL ANNUAL", BOLD, fill=SUBFILL)
for col in "BCD":
    put(ws, "%s%d" % (col, r), "=SUM(%s%d:%s%d)" % (col, first_an, col, last_an), BOLD, MONEY0, SUBFILL)
for col in "EFG": put(ws, "%s%d" % (col, r), "", fill=SUBFILL)
AN_ROW = r
r += 2
put(ws, "A%d" % r, "TOTAL FIXED COST PER MONTH  (monthly subtotal + annual subtotal / 12)", BOLD, fill=SUBFILL)
for col in "BCD":
    put(ws, "%s%d" % (col, r), "=%s%d+%s%d/12" % (col, MO_ROW, col, AN_ROW), BOLD, MONEY, SUBFILL)
for col in "EFG": put(ws, "%s%d" % (col, r), "", fill=SUBFILL)
FIXED_ROW = r
r += 2
put(ws, "A%d" % r, "NOT INCLUDED — no published price could be found for any of these", WARN, wrap=True)
ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=7); r += 1
for t in ["NDIS Commission's own application or certificate fee, if one exists — ACT-006 resolves this",
          "Management liability insurance — no published figure exists for a small Australian disability business",
          "Mid-term surveillance audit at about 18 months, estimated at 60-70% of the initial audit fee (SRC-034)",
          "Payroll tax, if the business crosses the Victorian threshold (ASM-003)",
          "Income tax, GST remittance, and any consultant engaged to run the registration"]:
    put(ws, "A%d" % r, "  .  " + t, NOTE, wrap=True)
    ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=7); r += 1
FIXED_BASE = "Costs!$C$%d" % FIXED_ROW
ONEOFF_BASE = "Costs!$C$%d" % ONEOFF_ROW


# =========================================================================
# 5. UNIT ECONOMICS
# =========================================================================
ws = sheet("UnitEconomics", [56, 16, 14, 66])
title(ws, "Unit economics — one billable hour of core support", 4,
      "This is the whole business in eight rows. Read the last two together.")
header(ws, 4, ["Line", "AUD per hour", "% of price", "What it means"])
UE = [
 ("NDIS price limit, weekday daytime", "=%s" % PRICE_CORE, "SRC-002",
  "The most you may charge for one hour. It is a ceiling, not a target."),
 ("Casual support worker wage, SACS Level 2", "=-%s" % WAGE_L2, "SRC-009",
  "Includes the 25% casual loading paid in lieu of leave."),
 ("Superannuation", "=-%s*%s" % (WAGE_L2, SUPER), "SRC-011", "12.00% for FY2026-27."),
 ("WorkCover premium", "=-%s*%s" % (WAGE_L2, WC), "SRC-012", "Victorian state average. The sector rate may be higher."),
 ("Portable long service leave levy", "=-%s*%s" % (WAGE_L2, LSL), "SRC-013",
  "1.65%. Victoria-specific, covers NDIS-funded work, and it is the on-cost most often forgotten."),
 ("Payroll tax", "=-%s*%s" % (WAGE_L2, PTAX), "ASM-003", "Zero while below the Victorian threshold."),
]
r = 5
first_ue = r
for label, formula, src, note in UE:
    put(ws, "A%d" % r, label, BLK, wrap=True)
    put(ws, "B%d" % r, formula, BLK, MONEY)
    put(ws, "C%d" % r, "=B%d/%s" % (r, PRICE_CORE), BLK, PCT)
    put(ws, "D%d" % r, "%s — %s" % (src, note), NOTE, wrap=True)
    ws.row_dimensions[r].height = 26; r += 1
last_ue = r - 1
put(ws, "A%d" % r, "GROSS MARGIN PER BILLABLE HOUR", BOLD, fill=SUBFILL)
put(ws, "B%d" % r, "=SUM(B%d:B%d)" % (first_ue, last_ue), BOLD, MONEY, SUBFILL)
put(ws, "C%d" % r, "=B%d/%s" % (r, PRICE_CORE), BOLD, PCT, SUBFILL)
put(ws, "D%d" % r, "Before ANY overhead. This is not profit.", BOLD, fill=SUBFILL, wrap=True)
GM_ROW = r; r += 2
put(ws, "A%d" % r, "Administration, if you pay someone to do it", BLK, wrap=True)
put(ws, "B%d" % r, "=-%s*(1+%s+%s+%s+%s)*%s" % (WAGE_L3, SUPER, WC, LSL, PTAX, D_ADMIN), BLK, MONEY)
put(ws, "C%d" % r, "=B%d/%s" % (r, PRICE_CORE), BLK, PCT)
put(ws, "D%d" % r, "A coordinator at SACS Level 3, fully loaded, for %s of an hour per billable hour (ASM-008)."
    % "0.25", NOTE, wrap=True)
ADMIN_ROW = r; r += 1
put(ws, "A%d" % r, "CONTRIBUTION IF ADMINISTRATION IS PAID", BOLD, fill=SUBFILL)
put(ws, "B%d" % r, "=B%d+B%d" % (GM_ROW, ADMIN_ROW), BOLD, MONEY, SUBFILL)
put(ws, "C%d" % r, "=B%d/%s" % (r, PRICE_CORE), BOLD, PCT, SUBFILL)
put(ws, "D%d" % r, "READ THIS ROW FIRST. If it is small or negative, the business only works while you do the "
                   "administration yourself for nothing.", BOLD, fill=SUBFILL, wrap=True)
CONTRIB_PAID_ROW = r
ws.row_dimensions[r].height = 30
r += 2
put(ws, "A%d" % r, "CONTRIBUTION IF YOU DO THE ADMINISTRATION", BOLD, fill=SUBFILL)
put(ws, "B%d" % r, "=B%d" % GM_ROW, BOLD, MONEY, SUBFILL)
put(ws, "C%d" % r, "=B%d/%s" % (r, PRICE_CORE), BOLD, PCT, SUBFILL)
put(ws, "D%d" % r, "Costs no cash, but caps how many participants one person can carry.", BOLD, fill=SUBFILL, wrap=True)
CONTRIB_OWNER_ROW = r
r += 2
put(ws, "A%d" % r,
    "Sector reality check: National Disability Services reported about half of NDIS providers were loss-making in "
    "2024-25 and 81% said current pricing is unsustainable (SRC-046). The rows above are the arithmetic behind that "
    "finding. The price is not the problem; the administration load is.", WARN, wrap=True)
ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=4)
ws.row_dimensions[r].height = 44
CONTRIB_OWNER = "UnitEconomics!$B$%d" % CONTRIB_OWNER_ROW
CONTRIB_PAID  = "UnitEconomics!$B$%d" % CONTRIB_PAID_ROW
LOADED_WAGE   = "(%s*(1+%s+%s+%s+%s))" % (WAGE_L2, SUPER, WC, LSL, PTAX)


# =========================================================================
# 6. BREAK-EVEN
# =========================================================================
ws = sheet("BreakEven", [58, 18, 66])
title(ws, "Break-even and working capital", 3,
      "Break-even is fixed cost divided by contribution per hour. Working capital is the cash that must sit behind "
      "the wage cycle permanently.")
header(ws, 4, ["Measure", "Value", "How it is calculated and what it means"])
BE = [
 ("Fixed cost per month (base case)", "=%s" % FIXED_BASE, MONEY,
  "From the Costs sheet: monthly subtotal plus one twelfth of the annual subtotal."),
 ("Break-even billable hours per month — you do the administration", "=%s/%s" % (FIXED_BASE, CONTRIB_OWNER), NUM1,
  "Fixed cost divided by contribution. About 0.6 of one participant using 15 hours a week."),
 ("Break-even billable hours per month — administration is paid", "=%s/%s" % (FIXED_BASE, CONTRIB_PAID), NUM1,
  "The same fixed cost against a much thinner contribution. About two participants."),
 ("Billable hours to draw $5,000 a month — you do the administration",
  "=(%s+5000)/%s" % (FIXED_BASE, CONTRIB_OWNER), NUM1,
  "About four participants at 15 hours a week, and roughly four to six support workers to deliver it."),
 ("Billable hours to draw $5,000 a month — administration is paid",
  "=(%s+5000)/%s" % (FIXED_BASE, CONTRIB_PAID), NUM1,
  "If this number looks impossible for a new business, that is the finding."),
 ("Target billable hours per month (from Drivers)", "=%s" % D_TARGET, NUM1,
  "About 4.6 participants at 15 hours a week."),
 ("Monthly wage bill at target volume", "=%s*%s" % (D_TARGET, LOADED_WAGE), MONEY,
  "Fully loaded. This leaves your account before any invoice is paid."),
 ("WORKING CAPITAL REQUIRED at the assumed payment lag",
  "=%s*%s*(%s/30)" % (D_TARGET, LOADED_WAGE, D_LAG), MONEY,
  "Wages multiplied by the payment lag as a fraction of a month. Ring-fence this. It is SEPARATE from setup capital "
  "and it grows every time you win a participant."),
 ("Working capital at a 30-day lag instead", "=%s*%s" % (D_TARGET, LOADED_WAGE), MONEY,
  "The prior working files omitted working capital entirely (DEF-001). This is the line that omission hid."),
]
r = 5
for label, formula, fmt, note in BE:
    put(ws, "A%d" % r, label, BOLD if "WORKING CAPITAL" in label else BLK, wrap=True)
    put(ws, "B%d" % r, formula, BOLD if "WORKING CAPITAL" in label else BLK, fmt,
        SUBFILL if "WORKING CAPITAL" in label else None)
    put(ws, "C%d" % r, note, NOTE, wrap=True)
    ws.row_dimensions[r].height = 30; r += 1


# =========================================================================
# 7. SCENARIOS — 12-month cash curves
# =========================================================================
ws = sheet("Scenarios", [30] + [11] * 12 + [14])
title(ws, "Twelve-month cash, three scenarios", 14,
      "All three assume you draw the amount set in Drivers (currently zero). Change that driver and every scenario "
      "recomputes.")
scen = [("BASE: first client month 4, you do the administration", M.RAMP_BASE, CONTRIB_OWNER),
        ("SLOW: first client month 7, you do the administration", M.RAMP_SLOW, CONTRIB_OWNER),
        ("PAID ADMIN: base ramp, but administration is paid", M.RAMP_BASE, CONTRIB_PAID),
        ("DOWNSIDE: no participant at all for twelve months", M.RAMP_ZERO, CONTRIB_OWNER)]
r = 4
for name, ramp, contrib in scen:
    put(ws, "A%d" % r, name, BOLD, fill=SUBFILL)
    ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=14)
    r += 1
    header(ws, r, ["Month"] + [str(i) for i in range(1, 13)] + ["Year 1"])
    hdr = r; r += 1
    put(ws, "A%d" % r, "Billable hours", BLK)
    for i, h in enumerate(ramp):
        put(ws, "%s%d" % (get_column_letter(2 + i), r), h, BLUE_T, NUM1)
    put(ws, "N%d" % r, "=SUM(B%d:M%d)" % (r, r), BOLD, NUM1)
    hrs_row = r; r += 1
    put(ws, "A%d" % r, "Revenue", BLK)
    for i in range(12):
        cl = get_column_letter(2 + i)
        put(ws, "%s%d" % (cl, r), "=%s%d*%s" % (cl, hrs_row, PRICE_CORE), BLK, MONEY0)
    put(ws, "N%d" % r, "=SUM(B%d:M%d)" % (r, r), BOLD, MONEY0)
    r += 1
    put(ws, "A%d" % r, "Contribution", BLK)
    for i in range(12):
        cl = get_column_letter(2 + i)
        put(ws, "%s%d" % (cl, r), "=%s%d*%s" % (cl, hrs_row, contrib), BLK, MONEY0)
    put(ws, "N%d" % r, "=SUM(B%d:M%d)" % (r, r), BOLD, MONEY0)
    contrib_row = r; r += 1
    put(ws, "A%d" % r, "Fixed costs", BLK)
    for i in range(12):
        put(ws, "%s%d" % (get_column_letter(2 + i), r), "=-%s" % FIXED_BASE, BLK, MONEY0)
    put(ws, "N%d" % r, "=SUM(B%d:M%d)" % (r, r), BOLD, MONEY0)
    fixed_row = r; r += 1
    put(ws, "A%d" % r, "Owner drawings", BLK)
    for i in range(12):
        put(ws, "%s%d" % (get_column_letter(2 + i), r), "=-%s" % D_DRAW, BLK, MONEY0)
    put(ws, "N%d" % r, "=SUM(B%d:M%d)" % (r, r), BOLD, MONEY0)
    draw_row = r; r += 1
    put(ws, "A%d" % r, "Net cash in the month", BOLD)
    for i in range(12):
        cl = get_column_letter(2 + i)
        put(ws, "%s%d" % (cl, r), "=%s%d+%s%d+%s%d" % (cl, contrib_row, cl, fixed_row, cl, draw_row), BOLD, MONEY0)
    net_row = r; r += 1
    put(ws, "A%d" % r, "CUMULATIVE CASH", BOLD, fill=SUBFILL)
    put(ws, "B%d" % r, "=B%d-%s" % (net_row, ONEOFF_BASE), BOLD, MONEY0, SUBFILL)
    for i in range(1, 12):
        cl, pv = get_column_letter(2 + i), get_column_letter(1 + i)
        put(ws, "%s%d" % (cl, r), "=%s%d+%s%d" % (pv, r, cl, net_row), BOLD, MONEY0, SUBFILL)
    put(ws, "N%d" % r, "=MIN(B%d:M%d)" % (r, r), BOLD, MONEY0, SUBFILL)
    cum_row = r; r += 1
    put(ws, "A%d" % r, "Deepest cash hole in the year (column N above)", NOTE, wrap=True)
    ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=14)
    r += 2
put(ws, "A%d" % r,
    "Month 1 already carries the full one-off setup cost, which is why every cumulative row starts negative. The "
    "DOWNSIDE row is REQ-SYS-07: it is the cash you must be willing to lose if no participant ever arrives.",
    WARN, wrap=True)
ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=14)
ws.row_dimensions[r].height = 32


# =========================================================================
# 8. SUPPORT COORDINATION
# =========================================================================
ws = sheet("SupportCoordination", [52, 18, 66])
title(ws, "Support coordination delivered by you personally", 3,
      "No support workers, no wage cycle, no working capital gap. The only driver that matters is billable utilisation.")
header(ws, 4, ["Measure", "Value", "Note"])
SC = [
 ("Price limit, Support Coordination Level 2", "=%s" % PRICE_SC, MONEY, "SRC-005."),
 ("Hours available per week", "=%s" % D_HRSWK, NUM1, "ASM-011. Assumes owner-operator. ACT-001 must confirm."),
 ("Billable utilisation", "=%s" % D_UTIL, PCT, "ASM-013. Low confidence, no published benchmark."),
 ("Billable hours per month", "=%s*%s*52/12" % (D_HRSWK, D_UTIL), NUM1, "Weekly billable hours annualised then monthly."),
 ("Revenue per month", "=B8*%s" % PRICE_SC, MONEY, "Hours multiplied by the price limit."),
 ("Fixed costs per month", "=%s" % FIXED_BASE, MONEY, "Same fixed base as core supports."),
 ("NET CASH PER MONTH", "=B9-B10", MONEY, "Before tax and before any drawing."),
 ("NET CASH PER YEAR", "=B11*12", MONEY, "This is what the owner earns. Compare it to the core-supports scenarios."),
 ("Break-even billable hours per month", "=%s/%s" % (FIXED_BASE, PRICE_SC), NUM1,
  "Very low, because there is no direct labour cost. Utilisation, not volume, is the constraint."),
 ("One-off setup, no registration and no workers",
  "=636+108+%s+139.2+290" % D_WEB, MONEY,
  "No audit and no policy manual, because mandatory registration for support coordination is currently PAUSED (SRC-018)."),
]
r = 5
for label, formula, fmt, note in SC:
    put(ws, "A%d" % r, label, BOLD if label.startswith("NET") else BLK, wrap=True)
    put(ws, "B%d" % r, formula, BOLD if label.startswith("NET") else BLK, fmt,
        SUBFILL if label.startswith("NET") else None)
    put(ws, "C%d" % r, note, NOTE, wrap=True)
    ws.row_dimensions[r].height = 26; r += 1
r += 1
put(ws, "A%d" % r, "UTILISATION SENSITIVITY", BOLD, fill=SUBFILL)
ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=3); r += 1
header(ws, r, ["Utilisation", "Net cash per year", "Billable hours per month"]); r += 1
for u in (0.25, 0.35, 0.45, 0.55, 0.65, 0.75):
    put(ws, "A%d" % r, u, BLUE_T, PCT)
    put(ws, "B%d" % r, "=(%s*A%d*52/12*%s-%s)*12" % (D_HRSWK, r, PRICE_SC, FIXED_BASE), BLK, MONEY0)
    put(ws, "C%d" % r, "=%s*A%d*52/12" % (D_HRSWK, r), BLK, NUM1)
    r += 1
r += 1
put(ws, "A%d" % r,
    "THE TRADE. This model earns more, on less capital, with no staff. Against that: the NDIS Review recommended "
    "restructuring support coordination into a navigator model, and that design and pilot work runs through 2026-2028 "
    "with the funding mechanism intended to change (SRC-049, RSK-003). You would be building on the one service line "
    "government has said it intends to redesign.", WARN, wrap=True)
ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=3)
ws.row_dimensions[r].height = 56


# =========================================================================
# 9. SENSITIVITY
# =========================================================================
ws = sheet("Sensitivity", [64, 18, 16, 56])
title(ws, "What breaks the margin", 4,
      "Each row changes ONE assumption and leaves everything else alone. Ranked by how much damage it does.")
header(ws, 4, ["If this assumption is wrong", "Gross margin per hour", "Change", "Which assumption"])
SENS = [
 ("Nothing is wrong — base case as sourced", "=%s" % CONTRIB_OWNER, "-", "Base"),
 ("Administration is paid and runs at 0.40 hr per billable hour",
  "=%s-%s*(1+%s+%s+%s)*0.4" % (CONTRIB_OWNER, WAGE_L3, SUPER, WC, LSL), None,
  "ASM-008 — the model's most dangerous assumption. This turns the margin NEGATIVE."),
 ("Administration is paid at the assumed 0.25 hr per billable hour", "=%s" % CONTRIB_PAID, None, "ASM-008"),
 ("Workers sit at SACS Level 3, not Level 2",
  "=%s-%s*(1+%s+%s+%s+%s)" % (PRICE_CORE, WAGE_L3, SUPER, WC, LSL, PTAX), None,
  "ASM-002 — the award schedule could not be read; fairwork.gov.au is robots-blocked."),
 ("The business crosses the Victorian payroll tax threshold",
  "=%s-%s*(1+%s+%s+%s+0.0485)" % (PRICE_CORE, WAGE_L2, SUPER, WC, LSL), None,
  "ASM-003 — the threshold itself could not be confirmed."),
 ("The disability-sector WorkCover rate is 3.0%, not the 1.8% state average",
  "=%s-%s*(1+%s+0.03+%s+%s)" % (PRICE_CORE, WAGE_L2, SUPER, LSL, PTAX), None,
  "ASM-004 — the industry rate sits in a Special Gazette that could not be read."),
]
r = 5
for label, formula, ch, asm in SENS:
    put(ws, "A%d" % r, label, BLK, wrap=True)
    put(ws, "B%d" % r, formula, BOLD, MONEY)
    put(ws, "C%d" % r, "-" if r == 5 else "=B%d-$B$5" % r, BLK, MONEY)
    put(ws, "D%d" % r, asm, NOTE, wrap=True)
    ws.row_dimensions[r].height = 28; r += 1
r += 1
put(ws, "A%d" % r,
    "Read the second row. There is a plausible, unfalsified version of this business in which every hour of core "
    "support you sell LOSES money. Nothing in the sourced evidence rules it out, because no source publishes an "
    "administration-hours benchmark. Closing ASM-008 with real data from an operating provider is the single "
    "highest-value thing you can do before committing capital.", WARN, wrap=True)
ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=4)
ws.row_dimensions[r].height = 56


# =========================================================================
# 10. AGED CARE — INPUTS
# =========================================================================
ws = sheet("AC_Inputs", [56, 16, 12, 14, 14, 62])
title(ws, "Aged care inputs — Support at Home", 6,
      "Blue = sourced hard input. Yellow = assumption to review. Nothing here is a result; every result is a "
      "formula somewhere else in this workbook. Change a cell here and the whole aged care model moves.")
header(ws, 4, ["Input", "Value", "Unit", "Source", "Confidence", "Why this value, and what it does"])
AC_NOTES = {
 "price_personal_care":     "IHACPA ADVISORY reference price, not a legal cap. Price caps were legislated for 1 July 2026 and deferred indefinitely on 19 May 2026 with no replacement date. You may charge more or less.",
 "price_domestic_assist":   "Same IHACPA advice. Not used in the base model; held here so a domestic-assistance line can be priced without re-researching it.",
 "price_nursing":           "Same IHACPA advice, registered nurse. Not used in the base model.",
 "wage_homecare_l2_casual": "SCHADS Schedule F Home Care, Level 2 casual, from 1 September 2026. A DIFFERENT schedule from the Social and Community Services stream the NDIS model uses.",
 "wage_homecare_l3_casual": "Schedule F Level 3 casual, used for care management and administration time.",
 "care_mgmt_share":         "Ten per cent is DEDUCTED from each participant's quarterly budget to fund care management. It is revenue to you and it has no NDIS equivalent — it is the single structural advantage aged care holds.",
 "budget_level_1":          "The bottom ongoing classification. At the advisory price this buys about 1.8 service hours a week.",
 "budget_level_8":          "The top ongoing classification. Even this buys only about 13 hours a week. THIS is the constraint, not the price.",
}
r = 5
AC_CELL = {}
for k, (val, unit, src_id, conf) in AC.INPUTS.items():
    put(ws, "A%d" % r, k.replace("_", " "), BLK, wrap=True)
    c = put(ws, "B%d" % r, val, BLUE_T, PCT if unit == "rate" else MONEY)
    AC_CELL[k] = "AC_Inputs!$B$%d" % r
    put(ws, "C%d" % r, unit, BLK)
    put(ws, "D%d" % r, src_id, BLK)
    put(ws, "E%d" % r, conf, BLK)
    put(ws, "F%d" % r, AC_NOTES.get(k, ""), NOTE, wrap=True)
    ws.row_dimensions[r].height = 30
    r += 1

r += 1
put(ws, "A%d" % r, "DRIVERS — assumptions, not sources", BOLD)
r += 1
header(ws, r, ["Driver", "Value", "Unit", "Source", "Confidence", "What breaks if it is wrong"])
r += 1
AC_DRIVER_BREAK = {
 "avg_client_budget":             "The single largest lever in the whole aged care case. At $20,000 the contribution per client falls about 36 per cent; at $12,000 about 65 per cent. Close it by recording the assessed classification of your first five referrals (ACT-010).",
 "care_mgmt_hours_per_client_mo": "At 3 hours the care-management line roughly breaks even instead of contributing. It does not change the sign of the result.",
 "admin_hours_per_billable_hour": "The same assumption that decides the NDIS business. Shared deliberately: one definition, one place to correct it.",
 "wage_coordinator_base":         "ASM-031. Care management and administration are costed at the Social and Community Services Level 3 base rate, NOT the Schedule F Home Care rate, deliberately. IHACPA prices care management at $116 to $127 an hour, implying a role well above a home carer. The Schedule F rate would RAISE the contribution by about $43 a client a month, so this is the conservative choice.",
 "payment_lag_days":              "At a thirty-day lag aged care working capital rises from about $4,200 to about $17,900. That is a material addition to the capital requirement.",
 "participant_contribution_share":"Small exposure. The entire bad-debt allowance is $7.50 a client a month.",
 "contribution_bad_debt":         "At 10 per cent bad debt the allowance rises to $37.50 a client a month, still immaterial against a $1,002 contribution.",
 "clients_target":                "Sets the working-capital figure and the revenue projection. It is a target, not a forecast, and the plan says so.",
}
for k, (val, unit, src_id, conf) in AC.DRIVERS.items():
    put(ws, "A%d" % r, k.replace("_", " "), BLK, wrap=True)
    fmt = PCT if unit == "rate" else (MONEY0 if unit == "AUD/yr" else NUM1)
    c = put(ws, "B%d" % r, val, BLK, fmt, fill=YEL)
    AC_CELL[k] = "AC_Inputs!$B$%d" % r
    put(ws, "C%d" % r, unit, BLK)
    put(ws, "D%d" % r, src_id, BLK)
    put(ws, "E%d" % r, conf, BLK)
    put(ws, "F%d" % r, AC_DRIVER_BREAK.get(k, ""), NOTE, wrap=True)
    ws.row_dimensions[r].height = 34
    r += 1

PRICE_AC   = AC_CELL["price_personal_care"]
WAGE_AC_L2 = AC_CELL["wage_homecare_l2_casual"]
WAGE_AC_L3 = AC_CELL["wage_homecare_l3_casual"]
CM_SHARE   = AC_CELL["care_mgmt_share"]
BUDGET     = AC_CELL["avg_client_budget"]
CM_HOURS   = AC_CELL["care_mgmt_hours_per_client_mo"]
ADMIN_R    = AC_CELL["admin_hours_per_billable_hour"]
LAG        = AC_CELL["payment_lag_days"]
CONTRIB_SH = AC_CELL["participant_contribution_share"]
BADDEBT    = AC_CELL["contribution_bad_debt"]
CLIENTS    = AC_CELL["clients_target"]
COORD_AC   = AC_CELL["wage_coordinator_base"]
ONCOST     = "(1+%s+%s+%s)" % (SUPER, WC, LSL)

# =========================================================================
# 11. AGED CARE — THE UNIT IS THE CLIENT
# =========================================================================
ws = sheet("AC_ClientEconomics", [58, 18, 18, 62])
title(ws, "Contribution per client per month — aged care against NDIS", 4,
      "In the NDIS the price is a hard ceiling and the volume is open. In aged care the price is not capped but the "
      "participant's budget is. The two businesses have different binding constraints, so comparing them per HOUR "
      "misleads. This sheet compares them per CLIENT PER MONTH, which is the unit both businesses actually acquire.")
header(ws, 4, ["", "Aged care", "NDIS", "What this line is"])
AC_ROWS = [
 ("Loaded cost of a support worker hour",
  "=%s*%s" % (WAGE_AC_L2, ONCOST), "=%s*%s" % (WAGE_L2, ONCOST), MONEY,
  "Same on-cost stack applied to two different award schedules. Aged care labour costs within seven per cent of NDIS labour."),
 ("Published hourly price",
  "=%s" % PRICE_AC, "=%s" % PRICE_CORE, MONEY,
  "Aged care advisory, NDIS a hard cap. Aged care pays about forty per cent more for near-identical labour."),
 ("Gross margin an hour",
  "=AC_ClientEconomics!B6-AC_ClientEconomics!B5", "=AC_ClientEconomics!C6-AC_ClientEconomics!C5", MONEY,
  "The headline number, and the misleading one — aged care cannot sell you the hours."),
 ("Revenue a month per client",
  "=%s/12" % BUDGET, "=%d*52/12*%s" % (AC.NDIS_HOURS_PER_CLIENT_WEEK[0], PRICE_CORE), MONEY,
  "Aged care: one twelfth of the assessed budget. NDIS: %d hours a week at the price limit (ASM-025)."
  % AC.NDIS_HOURS_PER_CLIENT_WEEK[0]),
 ("Care management revenue (the 10% pool)",
  "=AC_ClientEconomics!B8*%s" % CM_SHARE, "=0", MONEY,
  "Aged care only. The NDIS has no equivalent revenue line."),
 ("Service revenue",
  "=AC_ClientEconomics!B8-AC_ClientEconomics!B9", "=AC_ClientEconomics!C8", MONEY,
  "What is left to buy actual service hours with."),
 ("Billable hours a month",
  "=AC_ClientEconomics!B10/%s" % PRICE_AC, "=%d*52/12" % AC.NDIS_HOURS_PER_CLIENT_WEEK[0], NUM1,
  "THE CONSTRAINT. In aged care this is an output of the budget; in the NDIS it is an input you choose."),
 ("Billable hours a week",
  "=AC_ClientEconomics!B11*12/52", "=AC_ClientEconomics!C11*12/52", NUM1,
  "Aged care buys about a third of the NDIS hours per client."),
 ("Direct labour",
  "=AC_ClientEconomics!B11*AC_ClientEconomics!B5", "=AC_ClientEconomics!C11*AC_ClientEconomics!C5", MONEY,
  "Hours times the loaded wage."),
 ("Care management cost",
  "=%s*%s*%s" % (CM_HOURS, COORD_AC, ONCOST), "=0", MONEY,
  "Coordinator time against the 10% pool (ASM-021)."),
 ("Service administration cost",
  "=AC_ClientEconomics!B11*%s*%s*%s" % (ADMIN_R, COORD_AC, ONCOST),
  "=AC_ClientEconomics!C11*%s*%s*%s" % (ADMIN_R, WAGE_L3, ONCOST), MONEY,
  "Rostering, notes and claiming, PAID rather than absorbed by you (ASM-008)."),
 ("Bad debt allowance",
  "=AC_ClientEconomics!B8*%s*%s" % (CONTRIB_SH, BADDEBT), "=0", MONEY,
  "A participant co-contribution that can go unpaid (ASM-022, ASM-023)."),
 ("CONTRIBUTION PER CLIENT PER MONTH",
  "=AC_ClientEconomics!B10-AC_ClientEconomics!B13+AC_ClientEconomics!B9-AC_ClientEconomics!B14-AC_ClientEconomics!B15-AC_ClientEconomics!B16",
  "=AC_ClientEconomics!C10-AC_ClientEconomics!C13-AC_ClientEconomics!C15", MONEY,
  "THE COMPARISON THAT DECIDES IT."),
]
r = 5
for label, fa, fn, fmt, note in AC_ROWS:
    bold = label.isupper()
    put(ws, "A%d" % r, label, BOLD if bold else BLK, wrap=True)
    put(ws, "B%d" % r, fa, BOLD if bold else BLK, fmt)
    put(ws, "C%d" % r, fn, BOLD if bold else BLK, fmt)
    put(ws, "D%d" % r, note, NOTE, wrap=True)
    ws.row_dimensions[r].height = 30
    r += 1
put(ws, "A%d" % r, "Ratio, aged care to NDIS", BOLD)
put(ws, "B%d" % r, "=AC_ClientEconomics!B17/AC_ClientEconomics!C17", BOLD, NUM1)
put(ws, "D%d" % r, "Aged care contributes this many times the NDIS figure, on a third of the service hours, "
                   "because it pays about forty per cent more an hour for labour of nearly the same cost AND adds "
                   "a ten per cent care-management revenue line the NDIS does not have.", NOTE, wrap=True)
ws.row_dimensions[r].height = 44
r += 2
put(ws, "A%d" % r, "WHAT THE FIXED BUDGET BUYS ACROSS THE WHOLE CLASSIFICATION RANGE", BOLD)
r += 1
header(ws, r, ["Classification budget a year", "Service hours a week", "Revenue a month", "Contribution a month"])
r += 1
for b, hrs, rev, con in AC.budget_sensitivity():
    put(ws, "A%d" % r, b, BLK, MONEY0)
    put(ws, "B%d" % r, "=A%d*(1-%s)/12/%s*12/52" % (r, CM_SHARE, PRICE_AC), BLK, NUM1)
    put(ws, "C%d" % r, "=A%d/12" % r, BLK, MONEY)
    put(ws, "D%d" % r, "=C%d-C%d*%s-(C%d*(1-%s)/%s)*(AC_ClientEconomics!$B$5+%s*%s*%s)"
                       "+C%d*%s-%s*%s*%s-C%d*%s*%s"
                       % (r, r, CM_SHARE, r, CM_SHARE, PRICE_AC, ADMIN_R, COORD_AC, ONCOST,
                          r, CM_SHARE, CM_HOURS, COORD_AC, ONCOST, r, CONTRIB_SH, BADDEBT), BLK, MONEY)
    r += 1
r += 1
put(ws, "A%d" % r,
    "Read the hours column. Even the TOP classification buys about thirteen service hours a week and most buy under "
    "five. You cannot sell more hours to a willing client — you can only acquire more clients. That makes this an "
    "acquisition business, not a rostering one, and it is the single most important structural difference between "
    "the two ventures.", WARN, wrap=True)
ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=4)
ws.row_dimensions[r].height = 56

# =========================================================================
# 12. STRUCTURE — TWO ENTITIES OR ONE
# =========================================================================
ws = sheet("Structure", [56, 18, 18, 18, 56])
title(ws, "Two separate companies, or one — what the choice costs", 5,
      "Zaid chose two separate entities (DEC-006). This sheet prices that choice so the number sits in front of the "
      "decision rather than behind it. It is a liability decision, and it is his to make.")
header(ws, 4, ["", "Two entities", "One entity", "Difference", "What is and is not duplicated"])
S = AC.structure_comparison()
rows = [
 ("Fixed cost a month", S["two_entities_fixed_month"], S["one_entity_fixed_month"],
  "Accounting and payroll, bookkeeping, general overhead and the ASIC annual review are paid twice. "
  "Software licensed per client and insurance priced on turnover are not."),
 ("One-off setup", S["two_entities_oneoff"], S["one_entity_oneoff"],
  "One extra company registration and one extra business name."),
]
r = 5
for label, two, one, note in rows:
    put(ws, "A%d" % r, label, BLK, wrap=True)
    put(ws, "B%d" % r, two, BLK, MONEY)
    put(ws, "C%d" % r, one, BLK, MONEY)
    put(ws, "D%d" % r, "=B%d-C%d" % (r, r), BOLD, MONEY)
    put(ws, "E%d" % r, note, NOTE, wrap=True)
    ws.row_dimensions[r].height = 34
    r += 1
put(ws, "A%d" % r, "Cost of separation, a year", BOLD)
put(ws, "D%d" % r, "=D5*12", BOLD, MONEY)
put(ws, "E%d" % r, "The monthly difference, annualised.", NOTE, wrap=True)
r += 1
put(ws, "A%d" % r, "COST OF SEPARATION OVER THREE YEARS", BOLD)
put(ws, "D%d" % r, "=D5*36+D6", BOLD, MONEY)
put(ws, "E%d" % r, "This is the number to hold against the benefit.", NOTE, wrap=True)
r += 2
put(ws, "A%d" % r,
    "What the separation BUYS: a compliance failure in one sector cannot reach the other's assets, and a suspension "
    "by one regulator does not stop all revenue. For two separately regulated care businesses those are real. "
    "What it DOES NOT buy: independence from the owner. Both plans depend on the owner doing administration unpaid "
    "in year one, so illness stops both revenue lines on the same day. Two entities separate legal liability, not "
    "operational dependency (RSK-016). The test at month twelve: if either entity has not reached break-even, the "
    "separation is being paid for out of a business that cannot afford it.", WARN, wrap=True)
ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=5)
ws.row_dimensions[r].height = 72

# =========================================================================
# 13. TRADE STUDY 3 — LIVE WEIGHTS
# =========================================================================
ws = sheet("TradeStudy3", [40, 12] + [13] * 4 + [56])
title(ws, "Trade study 3 — which business to start first", 7,
      "Weights fixed and timestamped BEFORE any option was scored: %s. The weight column is LIVE — change a weight "
      "and every score recomputes, so you can find your own answer rather than take mine." % TS.WEIGHTS_RECORD)
header(ws, 4, ["Criterion", "Weight"] + [o[0] for o in TS.OPTIONS] + ["Why this criterion carries this weight"])
WHY = {
 "capital":   "You are the sole capital provider. It is the only criterion where being wrong is unrecoverable.",
 "contrib":   "The unit both businesses acquire. Equal to capital, because a business that cannot fund you is not a business however little it cost to start.",
 "time":      "Every month before revenue is a month of fixed cost against a finite runway. It compounds with capital.",
 "stability": "Both sectors are mid-reform. A model whose funding mechanism is being redesigned is a model whose numbers expire.",
 "owner":     "A line that only works while you personally deliver it is a job, not a business, and it stops when you do.",
}
r = 5
first = r
for key, label, wt in TS.CRITERIA:
    put(ws, "A%d" % r, label, BLK, wrap=True)
    put(ws, "B%d" % r, wt, BLK, '0.00', fill=YEL)
    for i, (ok_, _) in enumerate(TS.OPTIONS):
        put(ws, "%s%d" % (get_column_letter(3 + i), r), TS.SCORES[ok_][key][0], BLK, '0')
    put(ws, "G%d" % r, WHY[key], NOTE, wrap=True)
    ws.row_dimensions[r].height = 32
    r += 1
last = r - 1
put(ws, "A%d" % r, "Weights must total", BOLD)
put(ws, "B%d" % r, "=SUM(B%d:B%d)" % (first, last), BOLD, '0.00')
put(ws, "G%d" % r, "If this is not 1.00 the scores below are meaningless.", WARN, wrap=True)
r += 1
put(ws, "A%d" % r, "WEIGHTED SCORE", BOLD)
for i in range(len(TS.OPTIONS)):
    col = get_column_letter(3 + i)
    put(ws, "%s%d" % (col, r), "=SUMPRODUCT($B$%d:$B$%d,%s%d:%s%d)" % (first, last, col, first, col, last),
        BOLD, '0.00')
put(ws, "G%d" % r, "Live. Change a weight above and this moves.", NOTE, wrap=True)
r += 2
put(ws, "A%d" % r, "THE OPTIONS", BOLD)
r += 1
for key, label in TS.OPTIONS:
    put(ws, "A%d" % r, key, BOLD)
    put(ws, "B%d" % r, label, BLK, wrap=True)
    ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=7)
    ws.row_dimensions[r].height = 20
    r += 1
r += 1
put(ws, "A%d" % r,
    "The margin between first and second is %.2f, and four of the five criteria change the winner within a weight "
    "movement you could reasonably make. The specific fork: raise capital preservation from 0.25 to 0.31 and support "
    "coordination wins instead. If the most you are willing to lose entirely is a small number, option C is the right "
    "answer and the recommendation is the wrong one. GOV-B6.4 requires that a result this close is escalated, not "
    "taken — recorded as DEC-007, parked with Zaid." % TS.margin(), WARN, wrap=True)
ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=7)
ws.row_dimensions[r].height = 60

# =========================================================================
# 14. COMBINED — WHAT MUST BE IN THE BANK
# =========================================================================
ws = sheet("Combined", [52, 18, 18, 20, 56])
title(ws, "Both entities together — what must be in the bank before the first client", 5,
      "Base case, owner drawing nothing for six months. Add a $6,000 a month draw and the runway lines roughly triple.")
header(ws, 4, ["", "NDIS entity", "Aged care entity", "Combined", "What this line is"])
Ares = AC.results(); Mres = M.results()
crows = [
 ("One-off setup, base case", Mres["one_off_core"][1], Ares["one_off"][1],
  "Registration, screening, first aid, policy manuals, brand, and the aged care registration fee band."),
 ("Fixed cost a month, base case", Mres["monthly_fixed"][1], Ares["monthly_fixed"][1],
  "Software, accounting, bookkeeping, overhead, insurance and company fees, held separately for each entity."),
 ("Six-month runway, no owner draw", Mres["runway_6mo_core_no_draw"], Ares["runway_6mo_no_draw"],
  "Setup plus six months of fixed cost with no revenue at all."),
 ("Working capital, low case", Mres["working_capital_at_300h"], Ares["working_capital"],
  "Wages fall due before invoices are paid. NDIS at a fourteen-day lag; aged care at seven (ASM-030)."),
 ("Working capital, high case", Mres["working_capital_at_300h_30day"], Ares["working_capital"],
  "NDIS at a thirty-day plan-manager lag. This is the figure that was absent from the earlier plan entirely."),
]
r = 5
for label, n, a, note in crows:
    put(ws, "A%d" % r, label, BLK, wrap=True)
    put(ws, "B%d" % r, n, BLK, MONEY)
    put(ws, "C%d" % r, a, BLK, MONEY)
    put(ws, "D%d" % r, "=B%d+C%d" % (r, r), BOLD, MONEY)
    put(ws, "E%d" % r, note, NOTE, wrap=True)
    ws.row_dimensions[r].height = 32
    r += 1
put(ws, "A%d" % r, "TOTAL CAPITAL BEFORE FIRST REVENUE, low", BOLD)
put(ws, "D%d" % r, "=D7+D8", BOLD, MONEY0)
put(ws, "E%d" % r, "Six-month runway plus low-case working capital, both entities.", NOTE, wrap=True)
r += 1
put(ws, "A%d" % r, "TOTAL CAPITAL BEFORE FIRST REVENUE, high", BOLD)
put(ws, "D%d" % r, "=D7+D9", BOLD, MONEY0)
put(ws, "E%d" % r, "The same with the thirty-day NDIS payment lag.", NOTE, wrap=True)
r += 2
put(ws, "A%d" % r,
    "Your earlier files put the whole startup at $15,000 to $30,000 for ONE business and carried no working-capital "
    "row at all. The gap between that and this is not a difference of opinion — it is the working capital and the "
    "second entity.", WARN, wrap=True)
ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=5)
ws.row_dimensions[r].height = 44

wb.save(OUT)
print("wrote", OUT)