#!/usr/bin/env python3 # ruff: noqa: E501 """ Build YUSI financial model workbook. Cinema-integrated education platform - B2B/B2G/B2C financial model. Generates an Excel workbook with live formulas (not static values). Sheets: YUSI | Parameters | Consolidated | Investment | School Pipeline | Institutional | Consumer (B2C) | PD & Licensing | Cash Flow """ from __future__ import annotations from pathlib import Path from openpyxl import Workbook from openpyxl.styles import Alignment, Border, Font, PatternFill, Side # --------------------------------------------------------------------------- # Paths # --------------------------------------------------------------------------- BASE_DIR = Path(__file__).resolve().parent OUTPUT_PATH = BASE_DIR / "yusi-model.xlsx" # --------------------------------------------------------------------------- # Constants # --------------------------------------------------------------------------- YEARS = ["Year 1", "Year 2", "Year 3", "Year 4", "Year 5"] YEAR_COLS = ["B", "C", "D", "E", "F"] ALL_COLS = ["A", "B", "C", "D", "E", "F", "G", "H"] NOTES_COL = "H" # Sheet name references for cross-sheet formulas PC = "'Parameters'" SCH = "'School Pipeline'" MIN = "'Institutional'" PDL = "'PD & Licensing'" CONS = "'Consolidated'" CF = "'Cash Flow'" B2C = "'Consumer (B2C)'" # --------------------------------------------------------------------------- # Styles (YUSI brand palette) # --------------------------------------------------------------------------- COVER_FILL = PatternFill( start_color="0D1824", end_color="0D1824", fill_type="solid" ) HEADER_FILL = PatternFill( start_color="0D1824", end_color="0D1824", fill_type="solid" ) SECTION_FILL = PatternFill( start_color="162030", end_color="162030", fill_type="solid" ) GOLD_FILL = PatternFill( start_color="C8872A", end_color="C8872A", fill_type="solid" ) INPUT_FILL = PatternFill( start_color="FDF3C0", end_color="FDF3C0", fill_type="solid" ) FORMULA_FILL = PatternFill( start_color="EDE8E0", end_color="EDE8E0", fill_type="solid" ) WHITE_FONT = Font(color="FFFFFF", bold=True, size=10) SECTION_FONT = Font(color="FFFFFF", bold=True, size=10) BOLD_FONT = Font(bold=True, size=10) BODY_FONT = Font(size=10, color="EDE8E0") BODY_FONT_DARK = Font(size=10) NOTE_FONT = Font(size=9, italic=True, color="8A7D6E") MONEY_FMT = "$#,##0" PRICE_FMT = "$#,##0.00" PCT_FMT = "0.0%" NUM_FMT = "#,##0" THIN_TOP_BOTTOM = Border( top=Side(style="thin"), bottom=Side(style="double") ) # --------------------------------------------------------------------------- # Helpers (shared infrastructure) # --------------------------------------------------------------------------- def style_cell( cell, *, fill=None, font=None, align=None, num_fmt=None, border=None ): if fill is not None: cell.fill = fill if font is not None: cell.font = font if align is not None: cell.alignment = align if num_fmt is not None: cell.number_format = num_fmt if border is not None: cell.border = border def setup_sheet(ws, title: str, subtitle: str = ""): ws.sheet_properties.tabColor = "C8872A" ws.column_dimensions["A"].width = 44 for col in YEAR_COLS: ws.column_dimensions[col].width = 16 ws.column_dimensions["G"].width = 4 ws.column_dimensions[NOTES_COL].width = 60 for col in ALL_COLS: style_cell(ws[f"{col}1"], fill=COVER_FILL) style_cell(ws[f"{col}2"], fill=COVER_FILL) ws["A1"] = title style_cell( ws["A1"], font=Font(size=13, bold=True, color="C8872A"), fill=COVER_FILL ) if subtitle: ws["A2"] = subtitle style_cell(ws["A2"], font=NOTE_FONT, fill=COVER_FILL) ws.freeze_panes = "B5" def fill_row(ws, row: int, fill): for col in ALL_COLS: style_cell(ws[f"{col}{row}"], fill=fill) def write_section(ws, row: int, label: str): fill_row(ws, row, SECTION_FILL) ws[f"A{row}"] = label style_cell( ws[f"A{row}"], font=SECTION_FONT, align=Alignment(horizontal="left") ) def write_header(ws, row: int, labels: list[str], note_header: str = "Notes"): fill_row(ws, row, HEADER_FILL) cols = ["A"] + YEAR_COLS for i, text in enumerate(labels): c = ws[f"{cols[i]}{row}"] c.value = text style_cell(c, font=WHITE_FONT, align=Alignment(horizontal="center")) if note_header: ws[f"{NOTES_COL}{row}"] = note_header style_cell( ws[f"{NOTES_COL}{row}"], font=WHITE_FONT, align=Alignment(horizontal="center"), ) def write_input_row( ws, row: int, label: str, values: list, note: str = "", fmt: str = NUM_FMT ): ws[f"A{row}"] = label style_cell(ws[f"A{row}"], font=BODY_FONT_DARK) for col, val in zip(YEAR_COLS, values): c = ws[f"{col}{row}"] c.value = val style_cell( c, fill=INPUT_FILL, align=Alignment(horizontal="right"), num_fmt=fmt ) if note: ws[f"{NOTES_COL}{row}"] = note style_cell(ws[f"{NOTES_COL}{row}"], font=NOTE_FONT) def write_formula_row( ws, row: int, label: str, formulas: list[str], note: str = "", fmt: str = NUM_FMT, ): ws[f"A{row}"] = label style_cell(ws[f"A{row}"], font=BODY_FONT_DARK) for col, formula in zip(YEAR_COLS, formulas): c = ws[f"{col}{row}"] c.value = formula style_cell( c, fill=FORMULA_FILL, align=Alignment(horizontal="right"), num_fmt=fmt, ) if note: ws[f"{NOTES_COL}{row}"] = note style_cell(ws[f"{NOTES_COL}{row}"], font=NOTE_FONT) def write_total_row( ws, row: int, label: str, formulas: list[str], note: str = "", fmt: str = MONEY_FMT, ): ws[f"A{row}"] = label style_cell(ws[f"A{row}"], font=BOLD_FONT) for col, formula in zip(YEAR_COLS, formulas): c = ws[f"{col}{row}"] c.value = formula style_cell( c, fill=FORMULA_FILL, font=BOLD_FONT, align=Alignment(horizontal="right"), num_fmt=fmt, border=THIN_TOP_BOTTOM, ) if note: ws[f"{NOTES_COL}{row}"] = note style_cell(ws[f"{NOTES_COL}{row}"], font=NOTE_FONT) def write_text_row(ws, row: int, label: str, value: str, note: str = ""): ws[f"A{row}"] = label style_cell(ws[f"A{row}"], font=BODY_FONT_DARK) ws[f"B{row}"] = value style_cell( ws[f"B{row}"], fill=INPUT_FILL, align=Alignment(horizontal="left"), ) ws.merge_cells(f"B{row}:F{row}") if note: ws[f"{NOTES_COL}{row}"] = note style_cell(ws[f"{NOTES_COL}{row}"], font=NOTE_FONT) # =================================================================== # SHEET BUILDERS # =================================================================== def build_cover_sheet(wb: Workbook): ws = wb.create_sheet("YUSI") ws.sheet_properties.tabColor = "C8872A" ws.sheet_view.showGridLines = False ws.column_dimensions["A"].width = 3 ws.column_dimensions["B"].width = 64 for col in ["C", "D", "E", "F", "G", "H"]: ws.column_dimensions[col].width = 16 for row in range(2, 46): for col in ALL_COLS: style_cell(ws[f"{col}{row}"], fill=COVER_FILL) # Saffron accent line for col in ["B", "C", "D", "E", "F", "G"]: style_cell(ws[f"{col}3"], fill=GOLD_FILL) ws.row_dimensions[3].height = 4 # Title block ws["B4"] = "YUSI" style_cell( ws["B4"], font=Font(size=42, bold=True, color="FFFFFF"), fill=COVER_FILL ) ws.row_dimensions[4].height = 56 ws["B5"] = "Financial Model" style_cell(ws["B5"], font=Font(size=16, color="C8872A"), fill=COVER_FILL) ws.row_dimensions[5].height = 28 ws["B6"] = ( "A platform for schools: curated short films, each " "with a ready-to-use lesson plan built in. Students " "discover films in class, families subscribe at home." ) style_cell( ws["B6"], font=Font(size=11, italic=True, color="8A7D6E"), fill=COVER_FILL, ) ws.row_dimensions[6].height = 20 ws["B8"] = ( "Teachers already want to use film. What stops them is the " "preparation it requires. YUSI removes that barrier: every film " "comes with a complete lesson package, ready to use in a single " "period." ) style_cell(ws["B8"], font=Font(size=11, color="EDE8E0"), fill=COVER_FILL) ws["B9"] = ( "Sales path: individual teachers, then school-wide " "adoption, then institutional agreements. Consumer " "subscriptions follow as students bring YUSI home." ) style_cell(ws["B9"], font=Font(size=10, color="8A7D6E"), fill=COVER_FILL) # Configured for ws["B11"] = "Configured for:" style_cell( ws["B11"], font=Font(size=9, italic=True, color="8A7D6E"), fill=COVER_FILL, ) ws["B12"] = f"={PC}!B6" style_cell( ws["B12"], font=Font(size=18, bold=True, color="C8872A"), fill=COVER_FILL, ) ws["B13"] = f"={PC}!B7" style_cell(ws["B13"], font=Font(size=10, color="8A7D6E"), fill=COVER_FILL) ws.row_dimensions[12].height = 30 # Lower accent for col in ["B", "C", "D", "E", "F", "G"]: style_cell(ws[f"{col}15"], fill=GOLD_FILL) ws.row_dimensions[15].height = 2 ws["B17"] = ( "Sheets in this model: Parameters (all editable inputs) " "· Consolidated (full profit & loss summary) · Investment " "(investor-facing summary) · School Pipeline (direct " "school subscriptions) · Institutional (government & " "system-level contracts) · Consumer B2C (family " "subscriptions from school exposure) · PD & Licensing " "(teacher training revenue) · Cash Flow (annual cash " "position and funding needs)" ) style_cell(ws["B17"], font=Font(size=9, color="8A7D6E"), fill=COVER_FILL) ws.row_dimensions[17].height = 48 ws["B18"] = ( "Cells highlighted in yellow are editable inputs. All other " "coloured cells are calculated automatically." ) style_cell( ws["B18"], font=Font(size=9, color="C8872A"), fill=COVER_FILL, ) # Glossary for col in ["B", "C", "D", "E", "F", "G"]: style_cell(ws[f"{col}21"], fill=GOLD_FILL) ws.row_dimensions[21].height = 2 ws["B23"] = "Glossary" style_cell( ws["B23"], font=Font(size=11, bold=True, color="FFFFFF"), fill=COVER_FILL, ) ws.row_dimensions[23].height = 20 glossary = [ ( "ARR", "Annual Recurring Revenue. The total subscription " "income expected from active schools in a given year.", ), ( "B2B", "Business to business. Schools and networks that " "subscribe directly to YUSI.", ), ( "B2B2C", "Business to business to consumer. Students discover " "YUSI in school (B2B), then families subscribe at " "home (B2C). The school is the distribution channel.", ), ( "B2C", "Business to consumer. Family subscriptions purchased " "by parents or students outside school hours.", ), ( "B2G", "Business to government. Agreements with public " "bodies, education authorities, or international " "organisations that cover many schools at once.", ), ( "CAC", "School Acquisition Cost. What it costs, on " "average, to sign up one new school.", ), ( "Churn", "The share of schools (or subscribers) that cancel " "in a given year.", ), ( "EBITDA", "Operating profit before interest, tax, depreciation " "and amortisation. A standard measure of how " "profitable a business is from its core operations.", ), ( "Gross Margin", "Revenue minus the direct costs of delivering the " "service, expressed as a percentage of revenue.", ), ( "LTV", "Lifetime Value. Total revenue expected from one " "school (or subscriber) across the full length of " "its relationship with YUSI.", ), ( "NRR", "Net Revenue Retention. Measures whether existing " "schools are spending more or less than the year " "before. Above 100% means expansion.", ), ( "PD", "Professional Development. Paid workshops and " "training sessions for teachers, delivered by YUSI.", ), ( "Revenue Share", "A content licensing structure where rights " "holders receive a percentage of subscription " "revenue. Kicks in when it exceeds the per-title " "minimum guarantee.", ), ( "Seed Raise", "The first round of external investment. Used to " "fund the product, content library, and first year " "of school acquisition.", ), ] for i, (term, definition) in enumerate(glossary): row = 24 + i ws[f"B{row}"] = term style_cell( ws[f"B{row}"], font=Font(size=9, bold=True, color="C8872A"), fill=COVER_FILL, ) ws[f"C{row}"] = definition ws.merge_cells(f"C{row}:H{row}") style_cell( ws[f"C{row}"], font=Font(size=9, color="EDE8E0"), fill=COVER_FILL, align=Alignment(wrap_text=True, vertical="center"), ) ws.row_dimensions[row].height = 18 # ------------------------------------------------------------------- # Platform Config # ------------------------------------------------------------------- def build_platform_config(wb: Workbook): ws = wb.create_sheet("Parameters") setup_sheet( ws, "PLATFORM CONFIGURATION", ( "What stops teachers from using film is not motivation " "but infrastructure. These inputs shape everything." ), ) # ---- 1 - Platform Identity ---- write_section(ws, 4, "1 - PLATFORM IDENTITY") write_header(ws, 5, ["Parameter", "", "", "", "", ""], "Description") write_text_row( ws, 6, "Platform name", "YUSI", "Cinema-integrated education platform for schools.", ) write_text_row( ws, 7, "Content vertical", "World cinema for schools (short films)", ( "Curated short films, each paired with a complete, " "curriculum-aligned lesson package." ), ) write_text_row( ws, 8, "Primary currency", "USD", "All model outputs in USD.", ) write_text_row(ws, 9, "Model horizon (years)", "5", "Projection horizon.") write_text_row( ws, 10, "Founder", "Aya Al Blouchi", "Founder and Chief Education Officer.", ) # ---- 2 - Rollout Phasing ---- # ---- 3 - School Pricing Tiers ---- write_section(ws, 20, "3 - SCHOOL PRICING TIERS") write_header( ws, 21, ["Tier"] + YEARS, "Annual license fee by enrollment bracket", ) write_input_row( ws, 22, "Tier S - Small (<200 students)", [3000, 3000, 3000, 3150, 3150], "Comparable to ed-tech platform subscriptions.", MONEY_FMT, ) write_input_row( ws, 23, "Tier M - Medium (200-500)", [5000, 5000, 5000, 5250, 5250], "Most common tier in GCC international schools.", MONEY_FMT, ) write_input_row( ws, 24, "Tier L - Large (500-1,000)", [7000, 7000, 7000, 7350, 7350], "Includes full teacher access + PD credits.", MONEY_FMT, ) write_input_row( ws, 25, "Tier XL - Extra Large (1,000+)", [8000, 8000, 8000, 8400, 8400], "Campus-wide tier with dedicated support.", MONEY_FMT, ) write_input_row( ws, 26, "Pilot discount rate", [0.50, 0.00, 0.00, 0.00, 0.00], "Y1 schools on 50% introductory discount. Full price from Y2.", PCT_FMT, ) # ---- 4 - School Tier Mix ---- write_section(ws, 28, "4 - SCHOOL TIER MIX") write_header( ws, 29, ["Tier share"] + YEARS, "Distribution of schools across enrollment tiers", ) write_input_row( ws, 30, "% Small", [0.20, 0.20, 0.15, 0.15, 0.10], "Share decreases as brand attracts larger schools.", PCT_FMT, ) write_input_row( ws, 31, "% Medium", [0.40, 0.40, 0.35, 0.35, 0.35], "Dominant tier in most markets.", PCT_FMT, ) write_input_row( ws, 32, "% Large", [0.30, 0.30, 0.35, 0.35, 0.35], "Growing with international expansion.", PCT_FMT, ) write_input_row( ws, 33, "% XL", [0.10, 0.10, 0.15, 0.15, 0.20], "Increases with institutional partnerships.", PCT_FMT, ) write_formula_row( ws, 34, "Mix validation (must equal 100%)", [f"=SUM({c}30:{c}33)" for c in YEAR_COLS], "Should be 100% in every year.", PCT_FMT, ) write_formula_row( ws, 35, "Blended annual price per school", [ f"={c}22*{c}30+{c}23*{c}31+{c}24*{c}32+{c}25*{c}33" for c in YEAR_COLS ], "Weighted average across tiers.", MONEY_FMT, ) # ---- 5 - School Sales & Retention ---- write_section(ws, 37, "5 - SCHOOL SALES & RETENTION") write_header(ws, 38, ["Metric"] + YEARS, "Notes") write_input_row( ws, 39, "End-of-year active schools (direct B2B)", [20, 75, 200, 350, 500], ( "Paid acquisition only. Y1: founder network pilots. " "Y2-Y5: sales-driven growth. Consumer (B2C) revenue " "modelled separately." ), ) write_input_row( ws, 40, "Annual school churn rate", [0.15, 0.10, 0.10, 0.10, 0.12], ( "Y1: 15% pilot churn (expected in " "validation year). Y5: slight uptick " "from international expansion. " "Benchmark: 8-12% (Holoniq)." ), PCT_FMT, ) write_input_row( ws, 41, "School CAC (per school)", [2000, 3000, 2500, 2000, 1800], "Ed sales cycles: $1,500-$5,000 (HolonIQ range).", MONEY_FMT, ) write_input_row( ws, 42, "Organic / referral rate", [0.00, 0.00, 0.15, 0.15, 0.20], ( "Teacher word-of-mouth referrals begin " "Y3 as product embeds in school culture. " "Reduces effective paid CAC." ), PCT_FMT, ) write_input_row( ws, 43, "Renewal price uplift (annual)", [0.00, 0.00, 0.03, 0.05, 0.05], "Price increase on renewal. Benchmark: 3-5% for ed-tech B2B.", PCT_FMT, ) # ---- 6 - Institutional Partnerships ---- write_section(ws, 45, "6 - INSTITUTIONAL PARTNERSHIPS") write_header(ws, 46, ["Metric"] + YEARS, "Notes") write_input_row( ws, 47, "Active institutional agreements (cumulative, EoY)", [0, 0, 1, 2, 3], ( "Y3: single pilot (20 schools). " "Y4-Y5: expansion and new partners." ), ) write_input_row( ws, 48, "Average schools per agreement", [0, 0, 20, 200, 300], ( "Y3 pilot: 20 schools at deep discount. " "Grows as adoption deepens." ), ) write_input_row( ws, 49, "Institutional discount vs direct", [0.30, 0.30, 0.30, 0.30, 0.30], "Benchmark: public sector bulk discount 20-40%.", PCT_FMT, ) # ---- 7 - Content Library ---- write_section(ws, 52, "7 - CONTENT LIBRARY") write_header(ws, 53, ["Metric"] + YEARS, "Notes") write_input_row( ws, 54, "Titles in active rotation", [40, 60, 90, 130, 180], "Total curated films available on platform each year.", ) write_input_row( ws, 55, "New films added per year", [10, 20, 30, 40, 50], "Curation pipeline: festivals, submissions, commissions.", ) write_input_row( ws, 56, "License cost per film per year (minimum)", [500, 500, 600, 700, 800], ( "3-year lock with step-up at renewal. " "Floor in MAX(per-title, rev share)." ), MONEY_FMT, ) write_input_row( ws, 57, "Teacher guide production per film (one-time)", [200, 200, 200, 200, 200], "Curriculum-aligned guide + reflection prompts.", MONEY_FMT, ) write_input_row( ws, 58, "Film curation & QA per film (one-time)", [100, 100, 100, 100, 100], "Metadata, subtitles, pedagogical tagging.", MONEY_FMT, ) write_formula_row( ws, 59, "Total titles (reference)", [f"={c}54" for c in YEAR_COLS], "Mirrors row 54 — total titles in active rotation.", ) write_input_row( ws, 60, "Content revenue share (% of sub revenue)", [0.12, 0.12, 0.12, 0.12, 0.12], ( "Applied when it exceeds per-title minimum. " "Standard content licensing structure." ), PCT_FMT, ) # ---- 8 - Platform & Delivery Costs ---- write_section(ws, 61, "8 - PLATFORM & DELIVERY COSTS") write_header( ws, 62, ["Driver"] + YEARS, "Per-school variable costs for platform delivery", ) write_input_row( ws, 63, "Hosting base ($/year)", [6000, 12000, 24000, 36000, 48000], ( "Cloud, CDN, database, storage. Lower than consumer " "streaming." ), MONEY_FMT, ) write_input_row( ws, 64, "Per-school hosting variable ($/school/year)", [100, 80, 60, 50, 40], "Bandwidth + storage. Decreasing with scale.", MONEY_FMT, ) write_input_row( ws, 65, "School support per school ($/year)", [150, 120, 100, 80, 60], "Helpdesk, onboarding, teacher training.", MONEY_FMT, ) write_input_row( ws, 66, "Content delivery per school ($/year)", [50, 40, 35, 30, 25], "Video streaming CDN costs allocated per school.", MONEY_FMT, ) write_input_row( ws, 67, "Curriculum operations per school ($/year)", [300, 280, 250, 220, 200], ( "Ongoing curriculum alignment, guide updates, " "assessment creation. Decreases with scale." ), MONEY_FMT, ) # ---- 9 - Team & Overhead ---- write_section(ws, 68, "9 - TEAM & OVERHEAD") write_header( ws, 69, ["Driver"] + YEARS, "Fully loaded costs including benefits", ) write_input_row( ws, 70, "Total team cost (salaries + benefits)", [90000, 215000, 430000, 720000, 1050000], ( "Fully loaded. Includes founder draw, core team, and " "fractional/consultant costs." ), MONEY_FMT, ) write_input_row( ws, 71, "Headcount (memo, not a formula driver)", [2, 4, 7, 10, 14], ( "Includes founder (Y1 onwards). Fractional consultants " "counted as ~0.5 FTE." ), ) write_input_row( ws, 72, "Travel & conferences", [28000, 36000, 44000, 54000, 65000], "School visits, education conferences, partner meetings.", MONEY_FMT, ) write_input_row( ws, 73, "Sales & marketing", [5000, 25000, 50000, 80000, 120000], "Digital marketing, events, demos, content creation.", MONEY_FMT, ) write_input_row( ws, 74, "Legal & admin", [20000, 22000, 25000, 30000, 35000], "Legal, accounting, tax, compliance.", MONEY_FMT, ) write_input_row( ws, 75, "Office / co-working", [3000, 6000, 12000, 18000, 24000], "Co-working space scaling with team.", MONEY_FMT, ) write_input_row( ws, 76, "Tools & software", [5000, 11000, 18000, 27000, 35000], ( "CRM, screening platform, analytics, " "curriculum tools, automation." ), MONEY_FMT, ) # ---- 10 - Professional Development ---- write_section(ws, 79, "10 - PROFESSIONAL DEVELOPMENT") write_header( ws, 80, ["Metric"] + YEARS, "Teacher training workshops and certification", ) write_input_row( ws, 81, "PD workshops per year", [15, 20, 50, 100, 150], "Scales with school base. ~1 workshop per 4-8 active schools.", ) write_input_row( ws, 82, "Price per workshop", [800, 1000, 1000, 1000, 1000], "Benchmark: $500-$1,500 per cohort (strategy.md).", MONEY_FMT, ) write_input_row( ws, 83, "Delivery cost per workshop", [300, 250, 200, 180, 150], "Facilitator, materials, venue (declining with scale).", MONEY_FMT, ) write_input_row( ws, 84, "Teachers per workshop", [15, 15, 20, 20, 25], "Cohort size.", ) # ---- 11 - Filmmaker Co-Promotion ---- write_section( ws, 86, "11 - FILMMAKER CO-PROMOTION" ) write_header( ws, 87, ["Metric"] + YEARS, "Collaborative promotion (no placement fees)", ) write_input_row( ws, 88, "Placement fee per film", [0, 0, 0, 0, 0], ( "Eliminated: YUSI co-promotes with " "filmmakers rather than charging " "placement fees." ), MONEY_FMT, ) write_input_row( ws, 89, "Films placed per year", [0, 0, 0, 0, 0], ( "Co-promoted films are part of content " "licensing (section 7), not a separate " "revenue stream." ), ) # ---- 12 - Fee Structure ---- write_section(ws, 91, "12 - FEE STRUCTURE") write_header( ws, 92, ["Fee component"] + YEARS, "Applied in Consolidated P&L", ) write_input_row( ws, 93, "Opex management fee (% of cost base)", [0.15, 0.15, 0.15, 0.15, 0.15], "Management services fee.", PCT_FMT, ) write_input_row( ws, 94, "Profit participation (% of EBITDA)", [0.15, 0.15, 0.15, 0.15, 0.15], "Applied on positive EBITDA only.", PCT_FMT, ) # ---- 13 - Working Capital ---- write_section(ws, 96, "13 - WORKING CAPITAL") write_header(ws, 97, ["Metric"] + YEARS, "Cash conversion assumptions") write_input_row( ws, 98, "Average collection lag (days)", [45, 50, 55, 55, 60], "Blended school (30-60d) + institutional (60-90d) payment terms.", ) # ---- 14 - Funding & Use of Proceeds ---- write_section(ws, 100, "14 - FUNDING & USE OF PROCEEDS") write_header(ws, 101, ["Parameter"] + YEARS, "Notes") write_input_row( ws, 102, "Seed raise target", [500000, 0, 0, 0, 0], "Pre-seed / seed round size. Y1 deployment only.", MONEY_FMT, ) write_input_row( ws, 103, "Post-money valuation target (memo)", [2500000, 0, 0, 0, 0], "Reference only — not used in P&L calculations.", MONEY_FMT, ) write_input_row( ws, 104, "% to Product & Platform buildout", [0.35, 0, 0, 0, 0], "Technology, UX, content management platform.", PCT_FMT, ) write_input_row( ws, 105, "% to Content library", [0.20, 0, 0, 0, 0], "Rights acquisition for initial curated titles.", PCT_FMT, ) write_input_row( ws, 106, "% to Sales & marketing", [0.25, 0, 0, 0, 0], "School outreach, pilot management, events.", PCT_FMT, ) write_input_row( ws, 107, "% to Team (first hires)", [0.15, 0, 0, 0, 0], "Education lead + technical contractor.", PCT_FMT, ) write_input_row( ws, 108, "% to Working capital reserve", [0.05, 0, 0, 0, 0], "Buffer for collection lag and contingencies.", PCT_FMT, ) write_formula_row( ws, 109, "Use of funds validation (must = 100%)", ["=B104+B105+B106+B107+B108", "=0", "=0", "=0", "=0"], "Adjust percentages above so this row reads 100%.", PCT_FMT, ) # 9 - Unit sensitivities write_section(ws, 111, "9 - UNIT SENSITIVITIES (Y5 REFERENCE)") write_header( ws, 112, ["Sensitivity", "Impact", "", "", "", ""], "Approximate Y5 deltas", ) for row, label, formula in [ ( 113, "+$1K avg school price -> Y5 revenue impact", f"={SCH}!F12*1000", ), ( 114, "+1pp school churn -> Y5 schools lost", f"=ROUND({SCH}!F7*0.01,0)", ), ( 115, "+1 institutional agreement -> Y5 revenue impact", f"={MIN}!F17*F48", ), (116, "+10% team cost -> Y5 EBITDA impact", "=-0.10*F70"), ]: ws[f"A{row}"] = label style_cell(ws[f"A{row}"], font=BODY_FONT_DARK) ws[f"B{row}"] = formula style_cell(ws[f"B{row}"], fill=FORMULA_FILL, num_fmt=MONEY_FMT) # ---- 15 - Consumer (B2C Home) ---- write_section(ws, 118, "15 - CONSUMER (B2C HOME)") write_header(ws, 119, ["Parameter"] + YEARS, "Notes") write_input_row( ws, 120, "Monthly subscription price (family plan)", [0, 0, 7.99, 7.99, 8.99], ( "Students discover YUSI in school, families " "subscribe at home. Launches Year 3." ), MONEY_FMT, ) write_input_row( ws, 121, "Avg active subscribers per school", [0, 0, 2, 6, 12], ( "Encapsulates student exposure, conversion, " "and retention. Grows as product embeds in " "school culture." ), ) write_input_row( ws, 122, "Annual consumer churn", [0, 0, 0.40, 0.30, 0.25], ( "Annual (not monthly). School-referred users " "retain better than open-market. Ed-tech " "benchmark: 25-50% annual." ), PCT_FMT, ) write_input_row( ws, 123, "Per-subscriber hosting ($/year)", [0, 0, 12, 10, 8], "Marginal streaming + CDN cost per consumer.", MONEY_FMT, ) write_input_row( ws, 124, "Family engagement spend", [0, 0, 15000, 30000, 50000], ( "Parent outreach, social, app-store. Low " "because schools drive discovery." ), MONEY_FMT, ) # ---- 16 - Localization, Compliance & Growth ---- write_section( ws, 126, "16 - LOCALIZATION, COMPLIANCE & GROWTH", ) write_header( ws, 127, ["Parameter"] + YEARS, "Notes" ) write_input_row( ws, 128, "Localization budget (subtitles, dub)", [50000, 80000, 60000, 60000, 50000], ( "Subtitling and cultural adaptation. " "Higher in Y2 (initial library " "localization push)." ), MONEY_FMT, ) write_input_row( ws, 129, "Data privacy & compliance", [0, 20000, 30000, 40000, 50000], ( "COPPA, GDPR-K, and regional student " "data regulations. Scales with " "geographic reach." ), MONEY_FMT, ) write_input_row( ws, 130, "NRR expansion rate (grade-band upsell)", [0, 0, 0.15, 0.20, 0.25], ( "Revenue expansion within retained " "schools via new grade bands, " "departments, and add-on content packs." ), PCT_FMT, ) # ------------------------------------------------------------------- # School Pipeline (B2B direct) # ------------------------------------------------------------------- def build_school_pipeline(wb: Workbook): ws = wb.create_sheet("School Pipeline") setup_sheet( ws, "SCHOOL PIPELINE (B2B)", "Direct school adoption, pricing, revenue, and unit economics.", ) # 1 - School dynamics write_section(ws, 4, "1 - SCHOOL DYNAMICS") write_header(ws, 5, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 6, "End-of-year active schools", [f"={PC}!{c}39" for c in YEAR_COLS], "Pulled from Platform Config.", ) write_formula_row( ws, 7, "Beginning-of-year active schools", ["=0", "=B6", "=C6", "=D6", "=E6"], "Prior year carry-forward.", ) write_formula_row( ws, 8, "Annual churn rate", [f"={PC}!{c}40" for c in YEAR_COLS], "From Platform Config.", PCT_FMT, ) write_formula_row( ws, 9, "Churned schools", [f"=ROUND({c}7*{c}8,0)" for c in YEAR_COLS], "Schools lost = opening base x churn rate.", ) write_formula_row( ws, 10, "Net school adds", [f"={c}6-{c}7" for c in YEAR_COLS], "Closing minus opening.", ) write_formula_row( ws, 11, "Gross school adds", [f"={c}10+{c}9" for c in YEAR_COLS], "Net adds + replacement of churned.", ) write_formula_row( ws, 12, "Average active schools", [f"=ROUND(({c}7+{c}6)/2,0)" for c in YEAR_COLS], "Simple midpoint average for revenue calculation.", ) # 2 - Revenue write_section(ws, 14, "2 - REVENUE") write_header(ws, 15, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 16, "Blended annual price per school", [f"={PC}!{c}35" for c in YEAR_COLS], "Tier-weighted average from Platform Config.", MONEY_FMT, ) write_formula_row( ws, 17, "Pilot discount rate", [f"={PC}!{c}26" for c in YEAR_COLS], "From Platform Config.", PCT_FMT, ) write_formula_row( ws, 18, "Effective annual price per school", [f"=ROUND({c}16*(1-{c}17),0)" for c in YEAR_COLS], "After pilot discount.", MONEY_FMT, ) write_total_row( ws, 19, "TOTAL B2B SCHOOL REVENUE", [f"=ROUND({c}12*{c}18,0)" for c in YEAR_COLS], "Average active schools x effective annual price.", ) # 3 - Unit economics write_section(ws, 21, "3 - UNIT ECONOMICS") write_header(ws, 22, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 23, "School CAC", [f"={PC}!{c}41" for c in YEAR_COLS], "From Platform Config.", MONEY_FMT, ) write_formula_row( ws, 24, "Total paid acquisition spend", [f"=ROUND({c}11*(1-{PC}!{c}42)*{c}23,0)" for c in YEAR_COLS], "Gross adds x CAC (all B2B is paid).", MONEY_FMT, ) write_formula_row( ws, 26, "LTV per school (annual price / churn)", [f"=IF({c}8>0,{c}18/{c}8,0)" for c in YEAR_COLS], "LTV should be >3x CAC for ed-tech B2B.", MONEY_FMT, ) write_formula_row( ws, 27, "LTV:CAC ratio", [f"=IF({c}23>0,{c}26/{c}23,0)" for c in YEAR_COLS], "Target: >3:1 (SaaStr / HolonIQ benchmark).", "0.0", ) write_formula_row( ws, 28, "Payback period (months)", [f"=IF({c}18>0,{c}23/({c}18/12),0)" for c in YEAR_COLS], "Months to recoup CAC from subscription revenue.", "0.0", ) # 4 - ARR Analytics write_section(ws, 30, "4 - ARR ANALYTICS") write_header(ws, 31, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 32, "Opening ARR", ["=0", "=B36", "=C36", "=D36", "=E36"], "Prior year closing ARR (Y1 = $0, first year).", MONEY_FMT, ) write_formula_row( ws, 33, "Gross new ARR", [f"=ROUND({c}11*{c}18,0)" for c in YEAR_COLS], "Gross adds x effective annual price.", MONEY_FMT, ) write_formula_row( ws, 34, "Churned ARR", [ "=0", "=ROUND(C9*B18,0)", "=ROUND(D9*C18,0)", "=ROUND(E9*D18,0)", "=ROUND(F9*E18,0)", ], "Churned schools x prior-year price. Y1 = $0.", MONEY_FMT, ) write_formula_row( ws, 35, "Net new ARR", [f"={c}33-{c}34" for c in YEAR_COLS], "", MONEY_FMT, ) write_total_row( ws, 36, "CLOSING ARR", [f"=ROUND({c}6*{c}18,0)" for c in YEAR_COLS], "EoY active schools x effective annual price.", ) write_formula_row( ws, 37, "YoY ARR growth", ["=0", "=IF(B36>0,(C36-B36)/B36,0)", "=IF(C36>0,(D36-C36)/C36,0)", "=IF(D36>0,(E36-D36)/D36,0)", "=IF(E36>0,(F36-E36)/E36,0)"], "Y1 = baseline period.", PCT_FMT, ) write_formula_row( ws, 38, "NRR — Net Revenue Retention", [ "=0", ( f"=IF(B36>0,(C7-C9)*C18" f"*(1+{PC}!C130)/B36,0)" ), ( f"=IF(C36>0,(D7-D9)*D18" f"*(1+{PC}!D130)/C36,0)" ), ( f"=IF(D36>0,(E7-E9)*E18" f"*(1+{PC}!E130)/D36,0)" ), ( f"=IF(E36>0,(F7-F9)*F18" f"*(1+{PC}!F130)/E36,0)" ), ], ( "Includes grade-band upsell expansion. " "Y2 uplift from pilot-to-full-price " "transition." ), PCT_FMT, ) # ------------------------------------------------------------------- # Ministry Pipeline (B2G) # ------------------------------------------------------------------- def build_ministry_pipeline(wb: Workbook): ws = wb.create_sheet("Institutional") setup_sheet( ws, "INSTITUTIONAL (B2G)", ( "Public sector, NGO, and institutional partnership dynamics " "and revenue." ), ) # 1 - Partnership dynamics write_section(ws, 4, "1 - PARTNERSHIP DYNAMICS") write_header(ws, 5, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 6, "Active institutional agreements (end-of-year)", [f"={PC}!{c}47" for c in YEAR_COLS], "From Platform Config.", ) write_formula_row( ws, 7, "Beginning-of-year agreements", ["=0", "=B6", "=C6", "=D6", "=E6"], "Prior year carry-forward.", ) write_formula_row( ws, 8, "New agreements this year", [f"={c}6-{c}7" for c in YEAR_COLS], "Net new institutional agreements signed.", ) write_formula_row( ws, 9, "Schools per agreement", [f"={PC}!{c}48" for c in YEAR_COLS], "From Platform Config.", ) write_formula_row( ws, 10, "Total institutional schools (end-of-year)", [f"=ROUND({c}6*{c}9,0)" for c in YEAR_COLS], "Active agreements x schools per agreement.", ) write_formula_row( ws, 11, "Average institutional schools", [f"=ROUND(({c}7*{c}9+{c}6*{c}9)/2,0)" for c in YEAR_COLS], "Midpoint for revenue calculation.", ) # 2 - Pricing & revenue write_section(ws, 13, "2 - PRICING & REVENUE") write_header(ws, 14, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 15, "Direct blended price per school", [f"={PC}!{c}35" for c in YEAR_COLS], "Reference: direct B2B school price.", MONEY_FMT, ) write_formula_row( ws, 16, "Institutional discount", [f"={PC}!{c}49" for c in YEAR_COLS], "Benchmark: public sector bulk discount 20-40%.", PCT_FMT, ) write_formula_row( ws, 17, "Institutional per-school rate", [f"=ROUND({c}15*(1-{c}16),0)" for c in YEAR_COLS], "Discounted rate applied to all covered schools.", MONEY_FMT, ) write_total_row( ws, 18, "TOTAL INSTITUTIONAL REVENUE", [f"=ROUND({c}11*{c}17,0)" for c in YEAR_COLS], "Avg institutional schools x per-school rate.", ) # 3 - Key metrics write_section(ws, 20, "3 - KEY METRICS") write_header(ws, 21, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 22, "Average agreement value", [f"=IF({c}6>0,{c}18/{c}6,0)" for c in YEAR_COLS], "", MONEY_FMT, ) # ------------------------------------------------------------------- # PD & Licensing # ------------------------------------------------------------------- def build_pd_licensing(wb: Workbook): ws = wb.create_sheet("PD & Licensing") setup_sheet( ws, "PD & LICENSING", ( "Professional development workshops " "and filmmaker co-promotion." ), ) # 1 - Professional development write_section(ws, 4, "1 - PROFESSIONAL DEVELOPMENT") write_header(ws, 5, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 6, "PD workshops per year", [f"={PC}!{c}81" for c in YEAR_COLS], "From Platform Config.", ) write_formula_row( ws, 7, "Price per workshop", [f"={PC}!{c}82" for c in YEAR_COLS], "From Platform Config.", MONEY_FMT, ) write_formula_row( ws, 8, "PD revenue", [f"=ROUND({c}6*{c}7,0)" for c in YEAR_COLS], "Workshops x price.", MONEY_FMT, ) write_formula_row( ws, 9, "Delivery cost per workshop", [f"={PC}!{c}83" for c in YEAR_COLS], "From Platform Config.", MONEY_FMT, ) write_formula_row( ws, 10, "PD delivery costs", [f"=ROUND({c}6*{c}9,0)" for c in YEAR_COLS], "Total workshop delivery cost.", MONEY_FMT, ) # 2 - Filmmaker co-promotion write_section( ws, 12, "2 - FILMMAKER CO-PROMOTION" ) write_header( ws, 13, ["Metric"] + YEARS, "Collaborative promotion (no placement fees)", ) write_formula_row( ws, 14, "Co-promoted films per year", [f"={PC}!{c}89" for c in YEAR_COLS], "From Parameters (currently zero).", ) write_formula_row( ws, 15, "Fee per film (eliminated)", [f"={PC}!{c}88" for c in YEAR_COLS], "Set to $0: co-promotion, not licensing.", MONEY_FMT, ) write_formula_row( ws, 16, "Co-promotion revenue", [f"=ROUND({c}14*{c}15,0)" for c in YEAR_COLS], "Zero by design. Films x fee.", MONEY_FMT, ) # 3 - Summary write_section(ws, 18, "3 - SUMMARY") write_header(ws, 19, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 20, "Total PD & co-promotion revenue", [f"={c}8+{c}16" for c in YEAR_COLS], "PD workshops only (co-promotion = $0).", MONEY_FMT, ) write_formula_row( ws, 21, "Total PD delivery costs", [f"={c}10" for c in YEAR_COLS], "", MONEY_FMT, ) write_total_row( ws, 22, "NET PD CONTRIBUTION", [f"={c}20-{c}21" for c in YEAR_COLS], "Revenue minus delivery costs.", ) # ------------------------------------------------------------------- # Consumer (B2C Home) # ------------------------------------------------------------------- def build_consumer_b2c(wb: Workbook): ws = wb.create_sheet("Consumer (B2C)") setup_sheet( ws, "CONSUMER (B2C HOME)", ( "Students discover YUSI in school, families " "subscribe at home. Revenue launches Year 3." ), ) # 1 - Subscriber base write_section(ws, 4, "1 - SUBSCRIBER BASE") write_header(ws, 5, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 6, "Total schools (all channels, avg)", [ f"={SCH}!{c}12+{MIN}!{c}11" for c in YEAR_COLS ], "B2B + institutional average active schools.", ) write_formula_row( ws, 7, "Active subscribers per school", [f"={PC}!{c}121" for c in YEAR_COLS], "From Parameters. Grows as product embeds.", ) write_formula_row( ws, 8, "Total active subscribers", [f"=ROUND({c}6*{c}7,0)" for c in YEAR_COLS], "Schools x subs per school.", ) write_formula_row( ws, 9, "Annual consumer churn", [f"={PC}!{c}122" for c in YEAR_COLS], "", PCT_FMT, ) write_formula_row( ws, 10, "Churned subscribers (memo)", [f"=ROUND({c}8*{c}9,0)" for c in YEAR_COLS], "For reference only. Net base in row 8.", ) # 2 - Revenue write_section(ws, 12, "2 - CONSUMER REVENUE") write_header(ws, 13, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 14, "Monthly subscription price", [f"={PC}!{c}120" for c in YEAR_COLS], "Family plan.", MONEY_FMT, ) write_formula_row( ws, 15, "Annual revenue per subscriber", [f"={c}14*12" for c in YEAR_COLS], "", MONEY_FMT, ) write_total_row( ws, 16, "TOTAL B2C REVENUE", [f"=ROUND({c}8*{c}15,0)" for c in YEAR_COLS], "Active subscribers x annual price.", ) # 3 - Costs write_section(ws, 18, "3 - CONSUMER COSTS") write_header(ws, 19, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 20, "Family streaming costs", [ f"=ROUND({c}8*{PC}!{c}123,0)" for c in YEAR_COLS ], "Active families x streaming cost.", MONEY_FMT, ) write_formula_row( ws, 21, "Family engagement spend", [f"={PC}!{c}124" for c in YEAR_COLS], "Parent outreach, social, app-store.", MONEY_FMT, ) write_total_row( ws, 22, "TOTAL B2C COSTS", [f"={c}20+{c}21" for c in YEAR_COLS], "", ) # 4 - Unit economics write_section(ws, 24, "4 - UNIT ECONOMICS") write_header(ws, 25, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 26, "Effective CAC per subscriber", [ f"=IF({c}8>0,ROUND({c}21/{c}8,2),0)" for c in YEAR_COLS ], "Marketing spend / subscribers. Low: schools " "are the distribution channel.", MONEY_FMT, ) write_formula_row( ws, 27, "LTV per subscriber", [ f"=IF({c}9>0,{c}15/{c}9,0)" for c in YEAR_COLS ], "Annual revenue / churn rate.", MONEY_FMT, ) write_formula_row( ws, 28, "B2C gross margin", [ f"=IF({c}16>0,({c}16-{c}20)/{c}16,0)" for c in YEAR_COLS ], "Revenue minus hosting only.", PCT_FMT, ) # ------------------------------------------------------------------- # Consolidated P&L # ------------------------------------------------------------------- def build_consolidated(wb: Workbook): ws = wb.create_sheet("Consolidated") setup_sheet( ws, "CONSOLIDATED P&L", ( "Integrated view across all revenue streams, " "costs, and management fees." ), ) # 1 - Revenue write_section(ws, 4, "1 - REVENUE BY STREAM") write_header(ws, 5, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 6, "B2B School revenue", [f"={SCH}!{c}19" for c in YEAR_COLS], "", MONEY_FMT, ) write_formula_row( ws, 7, "Institutional revenue", [f"={MIN}!{c}18" for c in YEAR_COLS], "", MONEY_FMT, ) write_formula_row( ws, 8, "PD revenue", [f"={PDL}!{c}8" for c in YEAR_COLS], "", MONEY_FMT, ) write_formula_row( ws, 9, "Co-promotion revenue", [f"={PDL}!{c}16" for c in YEAR_COLS], "Filmmaker co-promotion ($0 by design).", MONEY_FMT, ) write_formula_row( ws, 10, "Consumer (B2C) revenue", [f"={B2C}!{c}16" for c in YEAR_COLS], "Family subscriptions. Launches Year 3.", MONEY_FMT, ) write_total_row( ws, 12, "TOTAL REVENUE", [f"=SUM({c}6:{c}10)" for c in YEAR_COLS], ) write_formula_row( ws, 13, "B2B share", [ f"=IF({c}12>0,{c}6/{c}12,0)" for c in YEAR_COLS ], "", PCT_FMT, ) write_formula_row( ws, 14, "B2G share", [ f"=IF({c}12>0,{c}7/{c}12,0)" for c in YEAR_COLS ], "", PCT_FMT, ) write_formula_row( ws, 15, "PD share", [ f"=IF({c}12>0,({c}8+{c}9)/{c}12,0)" for c in YEAR_COLS ], "", PCT_FMT, ) write_formula_row( ws, 16, "B2C share", [ f"=IF({c}12>0,{c}10/{c}12,0)" for c in YEAR_COLS ], "", PCT_FMT, ) # 2 - Operating metrics write_section(ws, 18, "2 - OPERATING METRICS") write_header(ws, 19, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 20, "B2B average active schools", [f"={SCH}!{c}12" for c in YEAR_COLS], ) write_formula_row( ws, 21, "Institutional schools (avg)", [f"={MIN}!{c}11" for c in YEAR_COLS], ) write_formula_row( ws, 22, "Total schools served", [f"={c}20+{c}21" for c in YEAR_COLS], "B2B direct + institutional schools.", ) write_formula_row( ws, 23, "Active B2C subscribers", [f"={B2C}!{c}8" for c in YEAR_COLS], "Family subscriptions from school exposure.", ) write_formula_row( ws, 24, "Titles in active rotation", [f"={PC}!{c}59" for c in YEAR_COLS], ) write_formula_row( ws, 25, "Headcount (memo)", [f"={PC}!{c}71" for c in YEAR_COLS], ( "Includes founder. Fractional consultants " "~0.5 FTE each." ), ) # 3 - Cost of revenue write_section(ws, 27, "3 - COST OF REVENUE") write_header(ws, 28, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 29, "Content rights (floor or revenue share)", [ ( f"=MAX(ROUND({PC}!{c}59*{PC}!{c}56,0)," f"ROUND(({c}6+{c}7+{c}10)" f"*{PC}!{c}60,0))" ) for c in YEAR_COLS ], ( "Higher of (titles x per-title minimum) " "or (subscription revenue x 12% share)." ), MONEY_FMT, ) write_formula_row( ws, 30, "Teacher guide production", [ f"=ROUND({PC}!{c2}55*{PC}!{c2}57,0)" for c2 in YEAR_COLS ], "One-time per new film.", MONEY_FMT, ) write_formula_row( ws, 31, "Film curation & QA", [ f"=ROUND({PC}!{c2}55*{PC}!{c2}58,0)" for c2 in YEAR_COLS ], "One-time per new film.", MONEY_FMT, ) write_formula_row( ws, 32, "Platform hosting", [ f"=ROUND({PC}!{c}63+{PC}!{c}64*{c}22,0)" for c in YEAR_COLS ], "Base + per-school variable x total schools.", MONEY_FMT, ) write_formula_row( ws, 33, "School support", [ f"=ROUND({PC}!{c}65*{c}22,0)" for c in YEAR_COLS ], "Per-school support cost x total schools.", MONEY_FMT, ) write_formula_row( ws, 34, "Content delivery", [ f"=ROUND({PC}!{c}66*{c}22,0)" for c in YEAR_COLS ], "Per-school CDN/delivery cost x total schools.", MONEY_FMT, ) write_formula_row( ws, 35, "PD delivery costs", [f"={PDL}!{c}10" for c in YEAR_COLS], "Workshop delivery costs.", MONEY_FMT, ) write_formula_row( ws, 36, "B2C family streaming costs", [f"={B2C}!{c}20" for c in YEAR_COLS], "Marginal streaming cost per family.", MONEY_FMT, ) write_formula_row( ws, 37, "Curriculum operations", [ f"=ROUND({PC}!{c}67*{c}22,0)" for c in YEAR_COLS ], "Per-school curriculum alignment and updates.", MONEY_FMT, ) write_formula_row( ws, 38, "Localization (subtitles, cultural adapt.)", [f"={PC}!{c}128" for c in YEAR_COLS], "Subtitling, dubbing, cultural adaptation.", MONEY_FMT, ) write_total_row( ws, 39, "TOTAL COST OF REVENUE", [f"=SUM({c}29:{c}38)" for c in YEAR_COLS], ) write_formula_row( ws, 40, "GROSS PROFIT", [f"={c}12-{c}39" for c in YEAR_COLS], "", MONEY_FMT, ) write_formula_row( ws, 41, "Gross margin", [ f"=IF({c}12>0,{c}40/{c}12,0)" for c in YEAR_COLS ], ( "Y1 depressed (fixed content library vs few " "schools). Improves as library amortises. " "Ed-tech target: 70-85%." ), PCT_FMT, ) # 4 - Operating expenses write_section(ws, 43, "4 - OPERATING EXPENSES") write_header(ws, 44, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 45, "Team (salaries + benefits)", [f"={PC}!{c}70" for c in YEAR_COLS], "", MONEY_FMT, ) write_formula_row( ws, 46, "School acquisition spend", [f"={SCH}!{c}24" for c in YEAR_COLS], "Paid CAC x gross adds.", MONEY_FMT, ) write_formula_row( ws, 47, "Family engagement spend", [f"={B2C}!{c}21" for c in YEAR_COLS], "Parent outreach, social, app-store.", MONEY_FMT, ) write_formula_row( ws, 48, "Travel & conferences", [f"={PC}!{c}72" for c in YEAR_COLS], "", MONEY_FMT, ) write_formula_row( ws, 49, "Sales & marketing", [f"={PC}!{c}73" for c in YEAR_COLS], "", MONEY_FMT, ) write_formula_row( ws, 50, "Legal & admin", [f"={PC}!{c}74" for c in YEAR_COLS], "", MONEY_FMT, ) write_formula_row( ws, 51, "Office / co-working", [f"={PC}!{c}75" for c in YEAR_COLS], "", MONEY_FMT, ) write_formula_row( ws, 52, "Tools & software", [f"={PC}!{c}76" for c in YEAR_COLS], "", MONEY_FMT, ) write_formula_row( ws, 53, "Data privacy & compliance", [f"={PC}!{c}129" for c in YEAR_COLS], ( "COPPA, GDPR-K, student data regs. " "Scales with geographic reach." ), MONEY_FMT, ) write_total_row( ws, 54, "TOTAL OPEX", [f"=SUM({c}45:{c}53)" for c in YEAR_COLS], ) # 5 - P&L write_section(ws, 56, "5 - CONSOLIDATED P&L") write_header(ws, 57, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 58, "Total operating costs (COGS + Opex)", [f"={c}39+{c}54" for c in YEAR_COLS], "", MONEY_FMT, ) write_total_row( ws, 59, "EBITDA", [f"={c}12-{c}58" for c in YEAR_COLS], ) write_formula_row( ws, 60, "EBITDA margin", [ f"=IF({c}12>0,{c}59/{c}12,0)" for c in YEAR_COLS ], "", PCT_FMT, ) # 6 - Fees write_section(ws, 62, "6 - FEES") write_header(ws, 63, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 64, "Opex management fee", [ f"=ROUND({c}58*{PC}!{c}93,0)" for c in YEAR_COLS ], "", MONEY_FMT, ) write_formula_row( ws, 65, "Profit participation", [ f"=ROUND(MAX(0,{c}59)*{PC}!{c}94,0)" for c in YEAR_COLS ], "Applied on positive EBITDA only.", MONEY_FMT, ) write_formula_row( ws, 66, "Total fees", [f"={c}64+{c}65" for c in YEAR_COLS], "", MONEY_FMT, ) write_total_row( ws, 67, "NET P&L AFTER FEES", [f"={c}59-{c}66" for c in YEAR_COLS], ) # 7 - Key metrics write_section(ws, 69, "7 - KEY METRICS") write_header(ws, 70, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 71, "Revenue per school (blended, all channels)", [ f"=IF({c}22>0,{c}12/{c}22,0)" for c in YEAR_COLS ], "", MONEY_FMT, ) write_formula_row( ws, 72, "Revenue per employee", [ f"=IF({c}25>0,{c}12/{c}25,0)" for c in YEAR_COLS ], "", MONEY_FMT, ) write_formula_row( ws, 73, "Total schools served (B2B + B2G)", [f"={c}22" for c in YEAR_COLS], ) write_formula_row( ws, 74, "B2C revenue per school", [ f"=IF({c}22>0,{c}10/{c}22,0)" for c in YEAR_COLS ], "Consumer revenue attributed back to school base.", MONEY_FMT, ) # ------------------------------------------------------------------- # Cash Flow # ------------------------------------------------------------------- def build_cash_flow(wb: Workbook): ws = wb.create_sheet("Cash Flow") setup_sheet( ws, "CASH FLOW", "Annual cash position, working capital, and injection requirements.", ) write_section(ws, 4, "1 - ANNUAL CASH FLOW") write_header(ws, 5, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 6, "Net P&L after fees", [f"={CONS}!{c}67" for c in YEAR_COLS], "", MONEY_FMT, ) # Working capital: change in receivables = delta_revenue x # collection_days/365 write_formula_row( ws, 7, "Working capital adjustment", [ f"=-ROUND({CONS}!B12*{PC}!B98/365,0)", ( f"=-ROUND(({CONS}!C12-{CONS}!B12)" f"*{PC}!C98/365,0)" ), ( f"=-ROUND(({CONS}!D12-{CONS}!C12)" f"*{PC}!D98/365,0)" ), ( f"=-ROUND(({CONS}!E12-{CONS}!D12)" f"*{PC}!E98/365,0)" ), ( f"=-ROUND(({CONS}!F12-{CONS}!E12)" f"*{PC}!F98/365,0)" ), ], "Change in receivables (revenue growth x collection lag).", MONEY_FMT, ) write_total_row( ws, 8, "NET CASH FLOW", [f"={c}6+{c}7" for c in YEAR_COLS], ) write_formula_row( ws, 10, "Cumulative cash position", ["=B8", "=B10+C8", "=C10+D8", "=D10+E8", "=E10+F8"], "", MONEY_FMT, ) write_section(ws, 12, "2 - INJECTION REQUIREMENT") write_header(ws, 13, ["Metric"] + YEARS, "Notes") write_formula_row( ws, 14, "Cash injection needed", [f"=MAX(0,-{c}8)" for c in YEAR_COLS], "", MONEY_FMT, ) write_formula_row( ws, 15, "Cash surplus generated", [f"=MAX(0,{c}8)" for c in YEAR_COLS], "", MONEY_FMT, ) write_formula_row( ws, 16, "Cumulative injections", ["=B14", "=B16+C14", "=C16+D14", "=D16+E14", "=E16+F14"], "", MONEY_FMT, ) write_formula_row( ws, 17, "Cash-positive in year?", [f'=IF({c}8>0,"YES","NO")' for c in YEAR_COLS], ) # =================================================================== # Theme font & build # =================================================================== def build_investor_view(wb: Workbook): """Investment summary — 2nd tab. Aggregates all key investor metrics.""" ws = wb.create_sheet("Investment") ws.sheet_properties.tabColor = "C8872A" ws.sheet_view.showGridLines = False ws.column_dimensions["A"].width = 44 for col in YEAR_COLS: ws.column_dimensions[col].width = 16 ws.column_dimensions["G"].width = 4 ws.column_dimensions[NOTES_COL].width = 60 # Hero section (dark background, rows 1-9) for row in range(1, 10): for col in ALL_COLS: style_cell(ws[f"{col}{row}"], fill=COVER_FILL) # Gold accent bar row 2 for col in YEAR_COLS + ["G"]: style_cell(ws[f"{col}2"], fill=GOLD_FILL) ws.row_dimensions[2].height = 3 ws["A3"] = "INVESTOR VIEW" style_cell( ws["A3"], font=Font(size=22, bold=True, color="FFFFFF"), fill=COVER_FILL, ) ws.row_dimensions[3].height = 36 ws["A4"] = "YUSI · Cinema-Integrated Education Platform" style_cell(ws["A4"], font=Font(size=11, color="C8872A"), fill=COVER_FILL) ws.row_dimensions[4].height = 20 ws["A5"] = f"={PC}!B7" style_cell( ws["A5"], font=Font(size=9, italic=True, color="8A7D6E"), fill=COVER_FILL, ) ws.row_dimensions[5].height = 16 ws.row_dimensions[6].height = 6 # Hero KPIs: labels row 7, values row 8 hero_kpis = [ ( "B", "Year 5 ARR", f"={SCH}!F36", MONEY_FMT, ), ( "C", "Year 5 Schools", f"={SCH}!F6+{MIN}!F10", "#,##0", ), ( "D", "Y5 Gross Margin", f"={CONS}!F41", "0.0%", ), ( "E", "Funding Required", f"=MAX({CF}!B16,{CF}!C16,{CF}!D16,{CF}!E16,{CF}!F16)", MONEY_FMT, ), ] ws.row_dimensions[7].height = 14 ws.row_dimensions[8].height = 26 for col, label, formula, fmt in hero_kpis: lbl = ws[f"{col}7"] lbl.value = label style_cell(lbl, fill=COVER_FILL, font=Font(size=8, color="666666")) val = ws[f"{col}8"] val.value = formula style_cell( val, fill=COVER_FILL, font=Font(size=15, bold=True, color="C8872A"), num_fmt=fmt, align=Alignment(horizontal="left"), ) ws.row_dimensions[9].height = 4 ws.freeze_panes = "B11" # Section 1: 5-Year Financial Summary write_section(ws, 10, "5-YEAR FINANCIAL SUMMARY") write_header(ws, 11, ["Metric"] + YEARS, "Benchmark / Source") write_formula_row( ws, 12, "Total Revenue", [f"={CONS}!{c}12" for c in YEAR_COLS], "Consolidated P&L — all streams combined.", MONEY_FMT, ) write_formula_row( ws, 13, "Annual Recurring Revenue (B2B schools)", [f"={SCH}!{c}36" for c in YEAR_COLS], "Closing ARR: EoY schools x effective annual price.", MONEY_FMT, ) write_formula_row( ws, 14, "ARR growth (YoY)", [f"={SCH}!{c}37" for c in YEAR_COLS], "Y1 = baseline; growth driven by school adds and pricing.", PCT_FMT, ) write_formula_row( ws, 15, "Total schools served (B2B + B2G)", [f"={SCH}!{c}6+{MIN}!{c}10" for c in YEAR_COLS], "EoY direct (B2B) + institutional coverage (B2G).", NUM_FMT, ) write_formula_row( ws, 16, "Gross Margin", [f"={CONS}!{c}41" for c in YEAR_COLS], "Target: 70-85% for ed-tech at scale (HolonIQ).", PCT_FMT, ) write_formula_row( ws, 17, "EBITDA", [f"={CONS}!{c}59" for c in YEAR_COLS], "Pre-fee operating profit.", MONEY_FMT, ) write_formula_row( ws, 18, "EBITDA Margin", [f"={CONS}!{c}60" for c in YEAR_COLS], "Target >20% at maturity.", PCT_FMT, ) write_formula_row( ws, 19, "Net Cash Flow", [f"={CF}!{c}8" for c in YEAR_COLS], "After working capital adjustment.", MONEY_FMT, ) write_formula_row( ws, 20, "Cumulative Funding Required", [f"={CF}!{c}16" for c in YEAR_COLS], "Peak year = minimum raise to reach cash-positive.", MONEY_FMT, ) # First cash-positive year — single text formula spanning B:F ws["A21"] = "First cash-positive year" style_cell(ws["A21"], font=BODY_FONT_DARK) cf_formula = ( f'=IF({CF}!B8>0,"Year 1",' f'IF({CF}!C8>0,"Year 2",' f'IF({CF}!D8>0,"Year 3",' f'IF({CF}!E8>0,"Year 4",' f'IF({CF}!F8>0,"Year 5","Beyond 5-yr horizon")))))' ) ws["B21"] = cf_formula style_cell( ws["B21"], fill=FORMULA_FILL, align=Alignment(horizontal="right"), ) ws.merge_cells("B21:F21") ws[f"{NOTES_COL}21"] = ( "Year when annual net cash flow first turns positive." ) style_cell(ws[f"{NOTES_COL}21"], font=NOTE_FONT) # Section 2: Unit Economics Scorecard write_section(ws, 23, "UNIT ECONOMICS SCORECARD") write_header(ws, 24, ["Metric"] + YEARS, "Benchmark") write_formula_row( ws, 25, "LTV : CAC Ratio", [f"={SCH}!{c}27" for c in YEAR_COLS], "Target >3x (SaaStr / HolonIQ benchmark).", "0.0", ) write_formula_row( ws, 26, "CAC Payback (months)", [f"={SCH}!{c}28" for c in YEAR_COLS], "Target <18 months for ed-tech B2B.", "0.0", ) write_formula_row( ws, 27, "NRR — Net Revenue Retention", [f"={SCH}!{c}38" for c in YEAR_COLS], ( "Y2 uplift: pilot-to-full-price transition. Target >100% " "from Y3." ), PCT_FMT, ) write_formula_row( ws, 28, "Revenue per School (all channels)", [f"={CONS}!{c}71" for c in YEAR_COLS], "Blended across B2B, B2G, and ancillary.", MONEY_FMT, ) write_formula_row( ws, 29, "Revenue per Employee", [f"={CONS}!{c}72" for c in YEAR_COLS], "Ed-tech benchmark: >$150K by maturity.", MONEY_FMT, ) write_formula_row( ws, 30, "Active B2C Subscribers", [f"={B2C}!{c}8" for c in YEAR_COLS], "Family subs from school exposure. Launches Y3.", NUM_FMT, ) # Section 3: Capital & Use of Proceeds write_section(ws, 32, "CAPITAL & USE OF PROCEEDS") # Custom header (not year-based) fill_row(ws, 33, HEADER_FILL) for col_ltr, label, align_h in [ ("A", "Category", "left"), ("B", "Amount", "right"), ("C", "% of Raise", "right"), ]: ws[f"{col_ltr}33"] = label style_cell( ws[f"{col_ltr}33"], font=WHITE_FONT, align=Alignment(horizontal=align_h), ) ws[f"{NOTES_COL}33"] = "Notes" style_cell( ws[f"{NOTES_COL}33"], font=WHITE_FONT, align=Alignment(horizontal="center"), ) proceeds_items = [ ( 34, " Product & Platform Buildout", f"=ROUND({PC}!B102*{PC}!B104,0)", f"={PC}!B104", "Technology, UX, content management platform.", ), ( 35, " Content Library (launch set)", f"=ROUND({PC}!B102*{PC}!B105,0)", f"={PC}!B105", ( "Licensing rights for initial titles entering " "active rotation." ), ), ( 36, " Sales & Marketing (pilots)", f"=ROUND({PC}!B102*{PC}!B106,0)", f"={PC}!B106", ( "School events, conference presence, " "pilot management." ), ), ( 37, " Team (first hires)", f"=ROUND({PC}!B102*{PC}!B107,0)", f"={PC}!B107", "Education lead + technical contractor.", ), ( 38, " Working Capital Reserve", f"=ROUND({PC}!B102*{PC}!B108,0)", f"={PC}!B108", "Buffer for collection lag and contingencies.", ), ] for row, label, formula_b, formula_c, note in proceeds_items: ws[f"A{row}"] = label style_cell(ws[f"A{row}"], font=BODY_FONT_DARK) b = ws[f"B{row}"] b.value = formula_b style_cell( b, fill=FORMULA_FILL, align=Alignment(horizontal="right"), num_fmt=MONEY_FMT, ) c_cell = ws[f"C{row}"] c_cell.value = formula_c style_cell( c_cell, fill=FORMULA_FILL, align=Alignment(horizontal="right"), num_fmt=PCT_FMT, ) ws[f"{NOTES_COL}{row}"] = note style_cell(ws[f"{NOTES_COL}{row}"], font=NOTE_FONT) # Total row ws["A39"] = "TOTAL SEED RAISE" style_cell(ws["A39"], font=BOLD_FONT) b39 = ws["B39"] b39.value = f"={PC}!B102" style_cell( b39, fill=FORMULA_FILL, font=BOLD_FONT, align=Alignment(horizontal="right"), num_fmt=MONEY_FMT, border=THIN_TOP_BOTTOM, ) c39 = ws["C39"] c39.value = "=C34+C35+C36+C37+C38" style_cell( c39, fill=FORMULA_FILL, font=BOLD_FONT, align=Alignment(horizontal="right"), num_fmt=PCT_FMT, border=THIN_TOP_BOTTOM, ) ws[f"{NOTES_COL}39"] = "Should equal 100%." style_cell(ws[f"{NOTES_COL}39"], font=NOTE_FONT) # Section 4: Investment Thesis write_section(ws, 41, "INVESTMENT THESIS") thesis_points = [ ( "Untapped SaaS market: cinema education " "is served entirely by manual DVD/USB; no " "digital infrastructure exists today." ), ( "Founder-market fit: Aya Al Blouchi is a " "former Senior Film Programmer & Youth " "Programmes Specialist at the Doha Film " "Institute." ), ( "Pedagogical intelligence layer: the real " "moat is curriculum-aligned lesson packages, " "teacher guides, and reflection frameworks " "that create deep switching costs." ), ( "B2B2C flywheel: school adoption seeds " "consumer subscriptions at near-zero CAC " "as students bring YUSI home." ), ( "Smoothed institutional path: Y3 pilot " "proves the model before scale agreements " "unlock exponential reach in Y4-Y5." ), ( "Proven content economics: per-title " "minimums protect the downside; revenue " "share at scale ensures costs track " "revenue. 70%+ gross margins at maturity." ), ] for i, point in enumerate(thesis_points): row = 42 + i ws.merge_cells(f"A{row}:H{row}") ws[f"A{row}"] = f" \u00b7 {point}" style_cell( ws[f"A{row}"], font=BODY_FONT_DARK, align=Alignment(wrap_text=True, vertical="center"), ) ws.row_dimensions[row].height = 22 def set_theme_font(wb: Workbook): for ws in wb.worksheets: for row in ws.iter_rows( min_row=1, max_row=ws.max_row, min_col=1, max_col=ws.max_column ): for cell in row: if cell.font is None: cell.font = Font(name="Space Grotesk", size=10) elif cell.font.name is None: cell.font = Font( name="Space Grotesk", size=cell.font.sz, bold=cell.font.bold, italic=cell.font.italic, color=cell.font.color, underline=cell.font.underline, ) def build_workbook() -> Workbook: wb = Workbook() wb.remove(wb.active) build_cover_sheet(wb) build_platform_config(wb) build_consolidated(wb) build_investor_view(wb) build_school_pipeline(wb) build_ministry_pipeline(wb) build_consumer_b2c(wb) build_pd_licensing(wb) build_cash_flow(wb) set_theme_font(wb) return wb def main(): wb = build_workbook() wb.save(OUTPUT_PATH) print(f"Built workbook: {OUTPUT_PATH}") if __name__ == "__main__": main()