180 lines
11 KiB
Markdown
180 lines
11 KiB
Markdown
# mpm-ad-roi-cap-table
|
||
|
||
60-month ROI / cap-table projections for MPM-financed digital signage paid back out of advertising revenue.
|
||
|
||
**Version:** 1.0.0
|
||
**Author:** Andy Gilliom
|
||
**Repo:** https://git.mpm.to/mpm/mpm-ad-roi-cap-table
|
||
**CoWork Project:** CW-027 — MPM Ad ROI Cap Table
|
||
|
||
---
|
||
|
||
## Overview
|
||
|
||
MPM increasingly fronts the capital for signage deployments — hardware, mounting, installation, provisioning, professional services — and recovers it from a share of the advertising revenue those screens generate. This skill answers the question management actually asks: in what month does the deal pay for itself, what does the money cost while it is tied up, and how does financing compare to simply selling the system.
|
||
|
||
It takes two inputs: an advertising revenue proposal (Passent Media / Galli Media, or any DOOH pro-forma) and the Odoo sales order for that same deployment. It pulls `sale.order.line` records with their `margin` and `purchase_price` fields, derives COGS per Andy's shorthand (`price_subtotal − margin`), separates one-time capital from recurring licensing, and projects 60 months of revenue distribution across MPM, Passent, the reseller, and the end user.
|
||
|
||
The output is three Excel workbooks, deliberately separate because they have different audiences and different disclosure rules. Skill-only — no MCP server. Requires the Odoo MCP for the sales-order pull and `openpyxl` for workbook generation.
|
||
|
||
---
|
||
|
||
## Skills
|
||
|
||
| Skill | What It Does |
|
||
|---|---|
|
||
| `mpm-ad-roi-cap-table` | Builds the three-model projection set from an ad proposal plus an Odoo quote. Triggers on "ROI projection", "cap table", "payback model", "when does this pay off", "ad-financed hardware", "revenue share model", "how long to recover COGS", "free hardware paid by ads", "Passent proposal", "model this deal against ad revenue", questions about ROI at MSRP versus discounted sale price, requests to model the partner/end-user split, comparisons of MPM-financed against customer-financed acquisition, and any mention of cost of money or interest on capital MPM fronts. |
|
||
|
||
---
|
||
|
||
## The Three Models
|
||
|
||
| Model | Split | Carries | Audience |
|
||
|---|---|---|---|
|
||
| **2A** Internal — MPM Financed, ROI & Payback | MPM 30 / Passent 30 / Reseller 10 / End User 30 | COGS, sale-price and MSRP milestones against MPM's share, plus interest on financed capital | Internal — contains cost and margin |
|
||
| **2B** Internal — MPM Financed, Revenue Distribution | same 30/30/10/30 | Revenue only. No cost basis, no milestones, no margin | Internal, safe to share numbers with partners |
|
||
| **2C** Customer-Financed Acquisition | MPM 5 / Reseller 2.5 / Passent 22.5 / End User 70 | MSRP milestone only, against the **End User's** 70% share | Customer-facing — no COGS or sale price |
|
||
|
||
Each is written as its own workbook. The separation is deliberate: 2A contains margin data that must not reach a reseller or a customer, and the cheapest way to guarantee that is for the customer-facing file to never contain it.
|
||
|
||
### Payoff milestones
|
||
|
||
| Milestone | Meaning | Cell fill |
|
||
|---|---|---|
|
||
| COGS | Hard cost recovered — the deal stops losing money | Yellow |
|
||
| Project Sale Price | Discounted contract value recovered — parity with the deal as quoted | Orange |
|
||
| Total MSRP | Full list value recovered — the deal beat a full-price sale | Green |
|
||
|
||
In 2A these measure MPM's recovery of capital at risk. In 2C nothing is at risk for MPM, so the single MSRP milestone answers the customer's question instead: having bought at list, when does their 70% share pay them back.
|
||
|
||
---
|
||
|
||
## Cost of Money
|
||
|
||
Model 2A carries an explicit financing layer. Principal is MPM's **one-time COGS** — the actual cash out the door, not the price it would have sold for, because you cannot borrow your own margin. Interest accrues monthly on the declining balance at 12% APR by default, and MPM's monthly share services interest before principal.
|
||
|
||
A milestone clears only when cumulative net recovers the target **plus** the interest accrued to that month. That definition makes the COGS milestone and the loan balance reaching zero the same event, which doubles as a built-in consistency check — if the Summary reports different months for the two, the config is wrong.
|
||
|
||
The script detects negative amortization (MPM's share smaller than accruing interest during the revenue ramp) and flags it in red on the Summary sheet.
|
||
|
||
---
|
||
|
||
## Guardrail: One Proposal, One Sales Order
|
||
|
||
An advertising proposal is not a market rate card. It prices a specific deployment — its vehicles, ridership, journey length, slot count, fill assumption. The skill models **only the sales order the user names, using only the proposal written for that same deployment.**
|
||
|
||
- One proposal pairs with one sales order. No fanning a proposal across quotes, no rolling quotes into one projection.
|
||
- If a sales order has no proposal for that project, the skill stops and asks for one. It will not substitute or scale figures from another deal, another agency, a rate card, or an estimate.
|
||
- Before building it reconciles customer/project reference, screen count (proposal versus display quantity on the quote), and deployment type (mobile/onboard versus wayside versus LED).
|
||
|
||
The builder automatically compares `ad_revenue.screen_count` against the quantity on lines flagged `is_display` and marks mismatches in red on the Summary sheet.
|
||
|
||
---
|
||
|
||
## Usage
|
||
|
||
**Step-by-step:**
|
||
|
||
1. Read the advertising proposal and extract gross monthly revenue, screen count, ridership, journey time, slot count, fill rate, and per-source breakdown. Capture event surges but leave them disabled.
|
||
2. Verify the proposal and the sales order describe the same deployment.
|
||
3. Pull the order from Odoo:
|
||
```
|
||
search_sales_orders(query="S01027")
|
||
odoo_search_read(model="sale.order.line", domain=[["order_id","=",<id>]],
|
||
fields=["name","product_id","product_uom_qty","price_unit","discount",
|
||
"price_subtotal","margin","margin_percent","purchase_price",
|
||
"display_type","sequence"])
|
||
```
|
||
4. Classify lines one-time versus recurring and confirm the split with the user.
|
||
5. Write a JSON config (see `references/config_schema.md`) and run the builder.
|
||
6. Sanity-check, then deliver.
|
||
|
||
```bash
|
||
python3 scripts/build_cap_table.py config.json --model all --outdir ./out
|
||
python3 scripts/build_cap_table.py config.json --model 2A -o internal_roi.xlsx
|
||
```
|
||
|
||
The console recap prints each model's split, 60-month distribution, interest, and milestone months so numbers can be checked without opening a file.
|
||
|
||
### Setup Checklist
|
||
|
||
- [ ] Skill installed in CoWork
|
||
- [ ] Odoo MCP connected (CW-003) for the sales-order pull
|
||
- [ ] `openpyxl` available in the sandbox (`pip install openpyxl --break-system-packages`)
|
||
- [ ] Advertising proposal on hand for the specific deployment being modeled
|
||
- [ ] Sales order number confirmed and in a state worth modeling
|
||
|
||
---
|
||
|
||
## Workbook Structure
|
||
|
||
**2A** — Summary, Monthly Projection, Cost Basis, Assumptions.
|
||
**2B** — Summary, Monthly Projection, Assumptions.
|
||
**2C** — Summary, Monthly Projection, Assumptions.
|
||
|
||
| Sheet | Contents |
|
||
|---|---|
|
||
| Summary | Deal identity, pairing check, distribution table (share, steady-state monthly, year one, 60-month total), financing block, milestone table, ROI table |
|
||
| Monthly Projection | 60 rows. Month, date, ramp %, gross, a column per party plus a cumulative column per party, then the milestone party's recurring charge, net, interest, balance, and cumulative net with milestone fills |
|
||
| Cost Basis (2A only) | Every Odoo line with qty, MSRP unit, extended MSRP, discount %, net, margin, COGS, recurring flag. Lines whose margin equals their full net are flagged red — no standard cost in Odoo, so the COGS target is optimistic |
|
||
| Assumptions | Ad revenue inputs, the split, ramp parameters, financing terms, who bears recurring licensing, exclusions and caveats |
|
||
|
||
---
|
||
|
||
## Defaults
|
||
|
||
| Parameter | Default | Note |
|
||
|---|---|---|
|
||
| Horizon | 60 months | |
|
||
| Ramp | Linear, 25% of steady-state in month 1 to 100% by month 12 | Reflects the proposal's own fill-rate caveat |
|
||
| Cost of money | 12% APR, declining balance | 2A only |
|
||
| Principal basis | One-time COGS | Overridable to sale, msrp, or an explicit amount |
|
||
| Recurring licensing | MPM at vendor cost in 2A/2B; End User at invoiced price in 2C | |
|
||
| Client contribution | $0 | Dated contributions credit the milestone party |
|
||
| Event surges | Off | Documented in Assumptions as excluded upside |
|
||
|
||
Share sets must sum to 100% of gross — the script raises rather than building a split that loses or duplicates a party's dollars.
|
||
|
||
---
|
||
|
||
## Files
|
||
|
||
| Path | Purpose |
|
||
|---|---|
|
||
| `SKILL.md` | Workflow, guardrails, interpretation guidance |
|
||
| `scripts/build_cap_table.py` | Model engine and workbook builder (openpyxl) |
|
||
| `references/config_schema.md` | Every config field, the model presets, financing, Odoo field mapping |
|
||
| `references/worked_example.md` | John Wayne Airport deal end to end with the exact numbers it should produce |
|
||
| `evals/evals.json` | Three test cases including the no-proposal refusal case |
|
||
|
||
---
|
||
|
||
## Requirements
|
||
|
||
- Python 3 with `openpyxl` — https://openpyxl.readthedocs.io
|
||
- Odoo MCP Bridge (CW-003) for `sale.order.line` access with margin fields
|
||
- An advertising revenue proposal for the specific deployment
|
||
|
||
---
|
||
|
||
## Troubleshooting
|
||
|
||
| Symptom | Fix |
|
||
|---|---|
|
||
| `Model 2A shares sum to 0.9500, not 1.0` | A party's share was dropped from the override. Shares must allocate every dollar of gross. |
|
||
| COGS milestone month differs from "Balance retired in month" | The principal basis or interest config is wrong. With `principal_basis: cogs` the two are the same event by definition. |
|
||
| Screen count shows MISMATCH in red | The proposal and the quote describe different deployments, or the proposal predates a scope change. Resolve before delivering — do not model through it. |
|
||
| Screen count "not reconciled" | Set `ad_revenue.screen_count` and `is_display: true` on the display line. |
|
||
| COGS looks too low | Lines with margin equal to full net have no standard cost in Odoo. Check the red figures on Cost Basis; the real COGS is higher. |
|
||
| Negative amortization flagged in red | MPM's share doesn't cover accruing interest during the ramp. Renegotiate the share or the rate — this is a finding, not a display bug. |
|
||
| Milestone reported as `>60 months` | It genuinely isn't reached. Report how far short it lands rather than extending the horizon. |
|
||
| Skill refuses to build for a sales order | There is no advertising proposal for that deployment. This is the one-proposal-one-order guardrail working as designed. |
|
||
|
||
---
|
||
|
||
## Changelog
|
||
|
||
| Date | Version | What Changed |
|
||
|---|---|---|
|
||
| 2026-09-11 | 1.0.0 | Initial build. Three model variants (2A/2B/2C), 12% declining-balance cost of capital, one-proposal-one-order guardrail with screen-count reconciliation, partner distribution columns. Validated against S01027 × Passent JWA001-071425. |
|