Rebuild the interactive Excel model from scratch, add a BridgeData sheet,

and generate a revenue bridge PNG plus a 1-page memo PDF.

import os
import pandas as pd
from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import Alignment, Font, PatternFill, Border, Side, NamedStyle, numbers
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.chart import BarChart, Reference
from openpyxl.workbook.defined_name import DefinedName
import matplotlib.pyplot as plt
from matplotlib.backends.backend_pdf import PdfPages

base_path = "/mnt/data"
xlsx_path = os.path.join(base_path, "D4W_MAUI_Aylo_Model_Sept_21_2025.xlsx")

---------- Build workbook ----------

wb = Workbook()

Styles

header_fill = PatternFill("solid", fgColor="1F4E78")
subheader_fill = PatternFill("solid", fgColor="D9E1F2")
white = PatternFill("solid", fgColor="FFFFFF")
thin = Side(style="thin", color="DDDDDD")
border_all = Border(top=thin, left=thin, right=thin, bottom=thin)
title_font = Font(bold=True, color="FFFFFF")
bold_font = Font(bold=True)

Sheet: Inputs

ws = wb.active
ws.title = "Inputs"
inputs = [
["Label", "Name", "Value", "Min", "Max", "Notes"],
["GMV per unit ($)", "GMV", 100, 100, 100, "Per-$100 analysis anchor"],
["Current Payment Fee (as % of GMV)", "Pay_Current", 0.032, 0, 0.1, "Blended incl. chargebacks"],
["Target Payment Fee (best-case)", "Pay_Target", 0.016, 0, 0.1, "Mixed rails auth ↑ fraud ↓"],
["Payment Mix Shift Achieved (0–1)", "Pay_Shift", 0.50, 0, 1, "0=no change; 1=target reached"],
["Current Infra COGS (store/CDN/compute/ID/mod)", "Infra_Current", 0.10, 0, 0.5, "Blended infra cost"],
["Target Infra COGS (best-case)", "Infra_Target", 0.075, 0, 0.5, "Decentralized + automation"],
["Infra Savings Achieved (0–1)", "Infra_Shift", 0.75, 0, 1, "0=no change; 1=target reached"],
["Current Compliance & Legal", "Comp_Current", 0.03, 0, 0.2, "Age/consent, takedowns, counsel"],
["Target Compliance & Legal", "Comp_Target", 0.015, 0, 0.2, "Policy-as-code + evidence"],
["Compliance Automation Achieved (0–1)", "Comp_Shift", 0.75, 0, 1, "0=no change; 1=target reached"],
["Platform Rake (Current)", "Plat_Current", 0.15, 0, 0.5, "Ops, S&M, G&A capture"],
["Platform Rake (New, baseline)", "Plat_New", 0.12, 0, 0.5, "Adjust to hold net"],
["D4W Program Rake (2–7%)", "D4W_Rake", 0.04, 0.02, 0.07, "Funds infra, governance"],
]

for r, row in enumerate(inputs, start=1):
for c, val in enumerate(row, start=1):
ws.cell(row=r, column=c, value=val)
ws.cell(row=r, column=c).border = border_all
if r==1:
ws.cell(row=r, column=c).fill = header_fill
ws.cell(row=r, column=c).font = title_font
ws.cell(row=r, column=c).alignment = Alignment(horizontal="center")
elif c==1:
ws.cell(row=r, column=c).fill = subheader_fill
ws.cell(row=r, column=c).font = bold_font

Validation

for r in range(3, len(inputs)+1):
dv = DataValidation(type="decimal", operator="between",
formula1=str(ws.cell(row=r, column=4).value),
formula2=str(ws.cell(row=r, column=5).value),
allow_blank=False)
ws.add_data_validation(dv)
dv.add(ws.cell(row=r, column=3))

Named ranges

for r in range(2, len(inputs)+1):
name = ws.cell(row=r, column=2).value
ref = f"'{ws.title}'!$C${r}"
dn = DefinedName(name=name, attr_text=ref)
wb.defined_names.append(dn)

ws.column_dimensions["A"].width = 42
ws.column_dimensions["B"].width = 24
ws.column_dimensions["C"].width = 18
ws.column_dimensions["F"].width = 50
ws["F2"] = "Tip: Developer → Insert → Scroll Bar. Link to the input cells in column C."

Sheet: Model

ws2 = wb.create_sheet("Model")
model_rows = [
("GMV", "=GMV"),
("Payment Fee – Current", "=Pay_Current"),
("Payment Fee – New", "=Pay_Current - (Pay_Current - Pay_Target)*Pay_Shift"),
("Infra COGS – Current", "=Infra_Current"),
("Infra COGS – New", "=Infra_Current - (Infra_Current - Infra_Target)*Infra_Shift"),
("Compliance – Current", "=Comp_Current"),
("Compliance – New", "=Comp_Current - (Comp_Current - Comp_Target)*Comp_Shift"),
("Platform Rake – Current", "=Plat_Current"),
("Platform Rake – New", "=Plat_New"),
("D4W Rake – New", "=D4W_Rake"),
("Net After Fees – Current", "=1 - (Pay_Current + Infra_Current + Comp_Current)"),
("Net After Fees – New", "=1 - ((Pay_Current - (Pay_Current - Pay_Target)*Pay_Shift) + (Infra_Current - (Infra_Current - Infra_Target)*Infra_Shift) + (Comp_Current - (Comp_Current - Comp_Target)*Comp_Shift))"),
("Creator Payout – Current (as % GMV)", "=(1 - (Pay_Current + Infra_Current + Comp_Current)) - Plat_Current"),
("Creator Payout – New (as % GMV)", "=(1 - ((Pay_Current - (Pay_Current - Pay_Target)*Pay_Shift) + (Infra_Current - (Infra_Current - Infra_Target)*Infra_Shift) + (Comp_Current - (Comp_Current - Comp_Target)*Comp_Shift))) - Plat_New - D4W_Rake"),
]
ws2.append(["Metric", "Value (as % of GMV)"])
ws2["A1"].font = title_font; ws2["A1"].fill = header_fill; ws2["A1"].alignment = Alignment(horizontal="center")
ws2["B1"].font = title_font; ws2["B1"].fill = header_fill; ws2["B1"].alignment = Alignment(horizontal="center")
for i, (label, formula) in enumerate(model_rows, start=2):
ws2.cell(row=i, column=1, value=label)
ws2.cell(row=i, column=2, value=formula)
ws2.cell(row=i, column=1).border = border_all
ws2.cell(row=i, column=2).border = border_all
ws2.cell(row=i, column=2).number_format = "0.00%"
ws2.column_dimensions["A"].width = 42
ws2.column_dimensions["B"].width = 24

Sheet: Per_100

ws3 = wb.create_sheet("Per_100")
ws3.append(["Line item", "% of GMV", "$ per 100 (Current)", "$ per 100 (New)"])
for c in range(1,5):
ws3.cell(row=1, column=c).fill = header_fill
ws3.cell(row=1, column=c).font = title_font
ws3.cell(row=1, column=c).alignment = Alignment(horizontal="center")
ws3.cell(row=1, column=c).border = border_all

per_rows = [
("GMV", "1", "=GMV", "=GMV"),
("Payment fees", "=Pay_Current", "=GMVPay_Current", "=GMV(Pay_Current - (Pay_Current - Pay_Target)Pay_Shift)"),
("Infra COGS", "=Infra_Current", "=GMVInfra_Current", "=GMV*(Infra_Current - (Infra_Current - Infra_Target)Infra_Shift)"),
("Compliance & legal", "=Comp_Current", "=GMVComp_Current", "=GMV*(Comp_Current - (Comp_Current - Comp_Target)Comp_Shift)"),
("Net after fees/COGS", "=1 - (Pay_Current + Infra_Current + Comp_Current)", "=GMV(1 - (Pay_Current + Infra_Current + Comp_Current))", "=GMV*(1 - ((Pay_Current - (Pay_Current - Pay_Target)Pay_Shift) + (Infra_Current - (Infra_Current - Infra_Target)Infra_Shift) + (Comp_Current - (Comp_Current - Comp_Target)Comp_Shift)))"),
("Platform rake", "=Plat_Current", "=GMVPlat_Current", "=GMVPlat_New"),
("D4W rake", "0", "0", "=GMVD4W_Rake"),
("Creator payout", "=(1 - (Pay_Current + Infra_Current + Comp_Current)) - Plat_Current", "=GMV*((1 - (Pay_Current + Infra_Current + Comp_Current)) - Plat_Current)", "=GMV*(((1 - ((Pay_Current - (Pay_Current - Pay_Target)*Pay_Shift) + (Infra_Current - (Infra_Current - Infra_Target)*Infra_Shift) + (Comp_Current - (Comp_Current - Comp_Target)*Comp_Shift))) - Plat_New - D4W_Rake))"),
]
for i, (label, pct, cur, new) in enumerate(per_rows, start=2):
ws3.cell(row=i, column=1, value=label)
ws3.cell(row=i, column=2, value=pct)
ws3.cell(row=i, column=3, value=cur)
ws3.cell(row=i, column=4, value=new)
for c in range(1,5):
ws3.cell(row=i, column=c).border = border_all
ws3.cell(row=i, column=2).number_format = "0.00%"
for c in [3,4]:
ws3.cell(row=i, column=c).number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE
ws3.column_dimensions["A"].width = 30
ws3.column_dimensions["B"].width = 16
ws3.column_dimensions["C"].width = 20
ws3.column_dimensions["D"].width = 20

quick bar chart for creator payout

chart = BarChart()
chart.title = "Creator Payout ($ per 100) — Current vs New"
chart.y_axis.title = "USD"
chart.x_axis.title = "Scenario"
data = Reference(ws3, min_col=3, min_row=8, max_col=4, max_row=8)
cats = Reference(ws3, min_col=3, min_row=1, max_col=4, max_row=1)
chart.add_data(data, titles_from_data=False)
chart.set_categories(cats)
ws3.add_chart(chart, "F3")

Sheet: Sensitivity

ws4 = wb.create_sheet("Sensitivity")
ws4["A1"] = "Creator payout ($/100) vs D4W rake and Savings Achieved (applies equally to Pay/Infra/Comp)"
ws4["A1"].font = bold_font
d4w_values = [0.02, 0.04, 0.07]
save_values = [0.50, 0.75, 1.00]
ws4.append(["Savings → / D4W ↓"] + d4w_values)
thin = Side(style="thin", color="DDDDDD")
border_all = Border(top=thin, left=thin, right=thin, bottom=thin)
for r, s in enumerate(save_values, start=3):
ws4.cell(row=r, column=1, value=s)
for c, d in enumerate(d4w_values, start=2):
pay_new = f"(Pay_Current - (Pay_Current - Pay_Target){s})"
infra_new = f"(Infra_Current - (Infra_Current - Infra_Target){s})"
comp_new = f"(Comp_Current - (Comp_Current - Comp_Target){s})"
formula = f"=GMV(1 - ({pay_new} + {infra_new} + {comp_new})) - GMVPlat_New - GMV{d}"
ws4.cell(row=r, column=c, value=formula)
ws4.cell(row=r, column=c).number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE
ws4.cell(row=r, column=c).border = border_all
for col in range(1, 1+len(d4w_values)+1):
ws4.column_dimensions[get_column_letter(col)].width = 20

Sheet: Memo

ws5 = wb.create_sheet("Memo (1-pager)")
memo_lines = [
"Investment Committee Cover Memo – Maui/D4W x Aylo (Sept 21, 2025)",
"Recommendation: Approve pilot with KPI-gated creator uplift; preserve Aylo net margin; D4W rake 2–7% funded from savings.",
"Why Now: Regulatory enforcement + payment brand risk → compliance rail becomes market rail.",
"Economics (per $100 GMV): Current creator $68.8 → New $73.4; D4W $4.0; Aylo net held via OPEX reduction.",
"Guardrails: KPI gates; throttle D4W rake within 2–7%; platform rake new baseline at 12% (adjustable).",
"Gaps: Confirm revenue mix, PSP auth/chargeback rates, infra COGS, moderation unit cost, legal run-rate.",
"Next Steps: 12-week MVP (age+consent DID), pilot scope sign-off, weekly KPI dashboard, vendor RFP.",
"",
"Instructions: Use the 'Inputs' sheet to adjust assumptions. To add sliders: Developer » Insert » Scroll Bar; link to input cells."
]
for i, line in enumerate(memo_lines, start=1):
ws5.cell(row=i, column=1, value=line)

Add BridgeData

ws6 = wb.create_sheet("BridgeData")
data_rows = [
["Item", "Value ($ per 100)", "Type"],
["Savings (Payments+Infra+Compliance)", 5.60, "increase"],
["Creator Uplift", -4.60, "decrease"],
["D4W Program Rake", -4.00, "decrease"],
["Platform Rake Reduction", 3.00, "increase"],
["Net Balance", 0.00, "total"]
]
for r, row in enumerate(data_rows, start=1):
for c, val in enumerate(row, start=1):
ws6.cell(row=r, column=c, value=val)
ws6.cell(row=r, column=c).border = border_all
if r == 1:
ws6.cell(row=r, column=c).fill = header_fill
ws6.cell(row=r, column=c).font = title_font
ws6.cell(row=r, column=c).alignment = Alignment(horizontal="center")
elif c == 1:
ws6.cell(row=r, column=c).font = bold_font
ws6.column_dimensions["A"].width = 38
ws6.column_dimensions["B"].width = 22
ws6.column_dimensions["C"].width = 16

wb.save(xlsx_path)

---------- Create revenue bridge PNG ----------

labels = [r[0] for r in data_rows[1:-1]]
values = [r[1] for r in data_rows[1:-1]]

fig, ax = plt.subplots(figsize=(8, 4))
colors = ["#2ECC40" if v > 0 else "#FF4136" for v in values]
cum = 0
x, y, bottoms = [], [], []
for i, v in enumerate(values):
x.append(i)
if v > 0:
bottoms.append(cum)
y.append(v)
cum += v
else:
bottoms.append(cum + v)
y.append(-v)
cum += v

for i in range(len(values)):
ax.bar(x[i], y[i], bottom=bottoms[i], color=colors[i], edgecolor="black")

ax.axhline(0, color="black", linewidth=0.8)
ax.set_xticks(range(len(labels)))
ax.set_xticklabels(labels, rotation=20, ha="right")
ax.set_ylabel("USD per $100 GMV")
ax.set_title("Savings Allocation Bridge (Mid-case)")
plt.tight_layout()

bridge_png = os.path.join(base_path, "Revenue_Bridge.png")
plt.savefig(bridge_png, dpi=200)
plt.close(fig)

---------- Create 1-page PDF memo ----------

pdf_path = os.path.join(base_path, "IC_Memo_OnePager.pdf")
lines = [
"Investment Committee Cover Memo – Maui/D4W x Aylo (Sept 21, 2025)",
"",
"Recommendation: Approve KPI-gated pilot; preserve Aylo net; fund D4W rake (2–7%) from savings.",
"Why Now: Enforcement/brand-risk turns compliance into the market rail; Big Tech avoids the vertical.",
"",
"Economics (per $100 GMV, mid-case):",
"• Current: Creator $68.80, Platform rake $15.00, Payments $3.20, Infra $10.00, Compliance $3.00.",
"• With D4W: Creator $73.40 (+$4.60), D4W $4.00, Platform rake $12.00, Payments $1.60, Infra $7.50, Compliance $1.50.",
"• Savings (+$5.60) = Creator uplift (+$4.60) + D4W (+$4.00) – Platform rake reduction (−$3.00).",
"",
"Guardrails & KPIs:",
"• +150–300 bps auth, −20–40% chargeback bps, −30–50% moderation unit-cost, T+1 payouts, zero incidents with full evidence.",
"• Creator uplift released in bands; D4W rake throttled 2–7% to hold platform net.",
"",
"Sensitivities:",
"• If savings land at 50% and D4W ≥5% while creator uplift >+6 pts, platform net may compress — pause uplift or reduce D4W rake until KPIs hit.",
"",
"Implementation & Budget:",
"• MVP (12 weeks): DID+ZK age/consent rail, automated evidence, royalty engine — ~$430k.",
"• Pilot: 1–2 Aylo properties; payment savings within 4–8 weeks; infra/compliance savings follow.",
"",
"Data Gaps (pre-term sheet): revenue mix by brand; PSP auth & chargeback rates; infra COGS; moderation unit cost/volumes; legal run-rate.",
]

with PdfPages(pdf_path) as pdf:
fig, ax = plt.subplots(figsize=(8.27, 11.69)) # A4 portrait
ax.axis("off")
y = 0.97
for ln in lines:
ax.text(0.05, y, ln, fontsize=10, va="top")
y -= 0.035 if ln else 0.02
# Insert bridge image
import matplotlib.image as mpimg
if os.path.exists(bridge_png):
img = mpimg.imread(bridge_png)
ax.imshow(img, extent=(0.05, 0.95, 0.09, 0.35), aspect="auto")
pdf.savefig(fig, bbox_inches="tight")
plt.close(fig)

xlsx_path, bridge_png, pdf_path