Andy Gilliom
157 lines
6.0 KiB
Markdown
157 lines
6.0 KiB
Markdown
# Config schema for build_cap_table.py
|
||
|
||
A single JSON file. Everything except `ad_revenue.monthly_gross` and `lines` has a default.
|
||
|
||
```json
|
||
{
|
||
"project_name": "SNA John Wayne Airport Shuttle — Onboard Infotainment",
|
||
"customer": "John Wayne Airport-Orange County",
|
||
"sale_order": "S01027",
|
||
"ad_proposal_ref": "Passent Media JWA001-071425 (14-Jul-2026)",
|
||
"prepared_on": "2026-09-11",
|
||
|
||
"start_date": "2027-03",
|
||
"horizon_months": 60,
|
||
"ramp_months": 12,
|
||
"ramp_start_pct": 0.25,
|
||
|
||
"finance": {
|
||
"annual_rate": 0.12,
|
||
"principal_basis": "cogs",
|
||
"method": "declining_balance"
|
||
},
|
||
|
||
"client_contributions": [
|
||
{"month": 1, "amount": 0}
|
||
],
|
||
|
||
"ad_revenue": {
|
||
"screen_count": 11,
|
||
"monthly_onboardings": 64000,
|
||
"journey_minutes": 6,
|
||
"ad_slots": 24,
|
||
"monthly_impressions": 1536000,
|
||
"fill_rate": 0.65,
|
||
"monthly_gross": 22138,
|
||
"annual_gross": 265651,
|
||
"sources": [
|
||
{"name": "TransitAds.co (6 local ads/mo)", "monthly": 12000, "fill": 0.25},
|
||
{"name": "Programmatic Exchange Min. (Ad Floor)", "monthly": 2765, "cpm": 12.00, "fill": 0.15},
|
||
{"name": "National Direct Buy", "monthly": 7373, "cpm": 24.00, "fill": 0.20}
|
||
]
|
||
},
|
||
|
||
"surges": [
|
||
{"label": "2028 LA Games", "start_month": 18, "months": 2.5,
|
||
"total": 334200, "enabled": false}
|
||
],
|
||
|
||
"notes": [
|
||
"Optional MP.TV Fully Managed Support is included as quoted."
|
||
],
|
||
|
||
"lines": [
|
||
{
|
||
"name": "M27V — Ruggedized, Intelligent Bus Display (Direct, G4)",
|
||
"qty": 11,
|
||
"msrp_unit": 4096.00,
|
||
"discount": 25.0,
|
||
"net_subtotal": 33792.00,
|
||
"margin": 9918.98,
|
||
"recurring": false,
|
||
"is_display": true
|
||
}
|
||
]
|
||
}
|
||
```
|
||
|
||
## Models and splits
|
||
|
||
The three model variants are presets in the script — you do not declare them in the config.
|
||
Run `--model all` and you get all three; the splits, milestones, interest behaviour and
|
||
sheet set come from the preset:
|
||
|
||
| Key | Split | Milestones | Party | Interest | Recurring borne by |
|
||
|---|---|---|---|---|---|
|
||
| 2A | MPM 30 / Passent 30 / Reseller 10 / End User 30 | COGS, sale, MSRP | MPM | yes | MPM, at vendor cost |
|
||
| 2B | same | none | — | no | MPM, at vendor cost |
|
||
| 2C | MPM 5 / Reseller 2.5 / Passent 22.5 / End User 70 | MSRP | End User | no | End User, at invoiced price |
|
||
|
||
Override a preset only when a specific deal was negotiated differently:
|
||
|
||
```json
|
||
"models": {
|
||
"2A": {"shares": {"MPM": 0.35, "Passent": 0.30, "Reseller": 0.10, "End User": 0.25}}
|
||
}
|
||
```
|
||
|
||
Shares must sum to exactly 1.0 — the script raises rather than building a split that loses
|
||
or duplicates a party's dollars.
|
||
|
||
## Financing
|
||
|
||
`finance.annual_rate` is the cost of money (default 0.12). `principal_basis` picks which
|
||
target the loan is drawn against — `cogs` (default, and the right answer: it is the cash
|
||
MPM actually spends), `sale`, or `msrp`. Set `principal_amount` instead to hard-code a
|
||
figure, e.g. when only part of the scope is financed.
|
||
|
||
Interest applies to 2A only. It accrues monthly on the declining balance; MPM's share
|
||
services interest first, then principal. Milestones clear when cumulative net covers the
|
||
target plus interest accrued to date, which makes the COGS milestone and the balance
|
||
reaching zero the same month. If those two disagree in the output, the config is wrong.
|
||
|
||
## The pairing fields
|
||
|
||
`sale_order` and `ad_proposal_ref` identify the one-to-one pair this model represents, and
|
||
both appear at the top of the Summary sheet. A workbook that does not name both is not
|
||
finished.
|
||
|
||
`ad_revenue.screen_count` and `is_display` drive an automatic reconciliation: the screen
|
||
count the proposal's impression math assumes should equal the display quantity on the
|
||
quote. Set `is_display: true` on the display/panel line (or lines) and the script compares
|
||
the two, printing a warning and flagging the Summary sheet in red on a mismatch. A mismatch
|
||
means the proposal and the quote describe different deployments — resolve it before
|
||
delivering the model rather than shipping a projection built on a foreign assumption.
|
||
|
||
## Field notes
|
||
|
||
**`lines`** — one object per Odoo `sale.order.line`, excluding `line_section` and
|
||
`line_note` rows. Map straight across:
|
||
|
||
| Config field | Odoo field |
|
||
|---|---|
|
||
| `name` | `name` (first line is enough; long descriptions clutter the sheet) |
|
||
| `qty` | `product_uom_qty` |
|
||
| `msrp_unit` | `price_unit` |
|
||
| `discount` | `discount` (percent, e.g. `25.0`) |
|
||
| `net_subtotal` | `price_subtotal` |
|
||
| `margin` | `margin` |
|
||
| `recurring` | your classification — see SKILL.md step 3 |
|
||
|
||
COGS is derived as `net_subtotal − margin`, so there is no separate cost field to fill in.
|
||
|
||
**`recurring: true`** means the line's `net_subtotal` and cost represent **one year**.
|
||
The script divides it by 12 and charges it every month for the whole horizon, and excludes
|
||
recurring lines from the payoff targets. Which basis is charged depends on who bears it:
|
||
MPM pays vendor cost (`net_subtotal − margin`), the End User pays the invoiced price
|
||
(`net_subtotal`). The preset decides the bearer per model.
|
||
|
||
**`start_date`** — the month the screens go live and ads start, not the contract date.
|
||
The quote's terms give a six-month standard delivery timeline, so this is usually the
|
||
quotation month plus six.
|
||
|
||
**`ramp_start_pct` / `ramp_months`** — month 1 runs at `ramp_start_pct` of steady-state
|
||
and climbs linearly to 100% at `ramp_months`. With the defaults (25%, 12 months) the ramp
|
||
costs roughly three months of revenue versus an instant-on assumption.
|
||
|
||
**`client_contributions`** — dated one-off credits against the financed balance. Use this
|
||
for deposits, milestone payments, or an agency capital contribution. Multiple entries in
|
||
the same month are summed.
|
||
|
||
**`surges`** — `total` is spread evenly across `months` starting at `start_month`. Leave
|
||
`enabled: false` for the base model; flip it on to produce an upside variant. A disabled
|
||
surge still appears in the Assumptions sheet as a documented exclusion, which is the point.
|
||
|
||
**`client_contributions`** credit only the milestone party (MPM in 2A, the End User in 2C),
|
||
since a deposit offsets the capital of whoever fronted it.
|