Update README.md

README.md

@@ -64,4 +64,303 @@ This code repository is licensed under [the MIT License](https://github.com/deep
 
 ## 6. Contact
 
-If you have any questions, please raise an issue or contact us at [service@deepseek.com](mailto:service@deepseek.com).
+If you have any questions, please raise an issue or contact us at [service@deepseek.com](mailto:service@deepseek.com).
+# 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", "=GMV*Pay_Current", "=GMV*(Pay_Current - (Pay_Current - Pay_Target)*Pay_Shift)"),
+    ("Infra COGS", "=Infra_Current", "=GMV*Infra_Current", "=GMV*(Infra_Current - (Infra_Current - Infra_Target)*Infra_Shift)"),
+    ("Compliance & legal", "=Comp_Current", "=GMV*Comp_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", "=GMV*Plat_Current", "=GMV*Plat_New"),
+    ("D4W rake", "0", "0", "=GMV*D4W_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})) - GMV*Plat_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
+