mpm-project-kickoff/SKILL.md

name, description

name

description

mpm-project-kickoff

Run MPM's internal project kickoff when a purchase order arrives — verify the customer PO against the Odoo Sales Order, fill missing line costs (warranty = MSRP × 0.7569) and write them back to Odoo, pull the end-agency contacts, build the internal kickoff deck and the three-tab Google Sheet project budget (Hardware Calculator formulas, current HS-code tariffs on Actuals), copy the Test Project Folder System, and open the project's GChat space. Use whenever Andy or anyone at MPM drops a PO and/or quotation into chat and says "we got the PO", "kick off this project", "set up the project", "kickoff deck", "project budget for S0xxxx", "new project folder", or similar — even if they only name one of these pieces.

MPM Project Kickoff

A PO just landed. This skill turns the PO + quotation into everything the internal kickoff meeting needs, and sets the project up in Drive, Odoo and GChat. The user drops the PO and the quotation (usually PDFs) into chat and calls the skill.

Deliverables, in order:

1. Project-team inputs gathered (mounting, promised timelines, hardware customizations, software customizations, anything else)
2. PO ↔ Odoo Sales Order verification, with every discrepancy catalogued and flagged
3. Every line has a cost; missing costs filled and written back to Odoo
4. End-agency contacts from Odoo (name, title, email, phone)
5. Internal kickoff deck covering 1–4 (Google Slides)
6. Project budget Google Sheet with three tabs: Summary, Project Budget, Actuals
7. A GChat space for the project, with the PM of record added
8. A copy of Test Project Folder System, renamed for the project, shared in the GChat space
9. Kickoff deck uploaded to Meeting Notes
10. Project budget uploaded to Financials
11. Source documents filed: a work order with an SOW attached goes to Financials/Contract Documents, and a purchase order goes to Financials/Purchase & Change Orders

Set up a task list for these eleven steps plus a final verification step, because the run is long and the user wants to see where things stand.

Where things live

Thing	Location
Odoo	odoo-mpm MCP tools (mpmedia.odoo.com). Prefer odoo_search_read with explicit fields.
Hardware Calculator	Shared drives/SALES/Product Cost Information/Hardware Calculator.xlsx. Read it from the connected folder, or find it with Drive search title = 'Hardware Calculator'.
Folder template	Shared drives/PROJECT MANAGMENT/Test Project Folder System (Drive id 1ZxrBJUxL75oRtqSE3qgqSNhtGxwcTQ9K, parent shared drive 0AD6TBqTBJYuOUk9PVA). If the id doesn't resolve, search by title.
Deck template	Test Project Folder System/Meeting Notes/Kickoff Deck.pptx. Slide 1 is the cover and slide 2 is the content layout (dark left title panel, body on the right).
New project folders	Siblings of the template in the PROJECT MANAGMENT shared drive, named for the agency (e.g. OshKosh, LakeTran).

Paths under /Users/<name>/Library/CloudStorage/... are specific to one person's machine. Look up the user's connected folders (get_device_info) rather than assuming Andy's path.

Step 0 — Read the inputs and find the order

1. Read the PO and the quotation. Capture: PO number and date, issuing organization (usually the reseller: Vontas, Avail, Passio, TripSpark, Swiftly, etc.), bill-to, ship-to / end agency, every line (code, description, qty, unit price, discount, extended), total, payment terms, requested delivery dates, and any special terms or notes.
2. Find the Odoo Sales Order. Use the SO number printed on the quote or PO (search_sales_orders with S0xxxx). If there isn't one, search by client_order_ref = PO number, then by customer and amount. If more than one candidate matches, ask which.
3. Pull the header: odoo_search_read('sale.order', [['id','=',ID]], ['name','state','partner_id','partner_invoice_id','partner_shipping_id','client_order_ref','opportunity_id','user_id','amount_untaxed','margin','date_order','commitment_date','payment_term_id','x_studio_quote_name'])
4. Pull the lines in one call: odoo_search_read('sale.order.line', [['order_id','=',ID]], ['sequence','display_type','name','product_id','product_template_id','product_uom_qty','price_unit','discount','price_subtotal','purchase_price'], order='sequence,id') display_type = 'line_section' rows are section headers. Keep track of which section each product line falls under, and treat anything under a section whose name starts with "Optional" as optional. Optional lines usually sit at qty 0 under an "Optional Products" section. There is no sale.order.option model in this database, so don't query for one.
5. Pull the opportunity: odoo_search_read('crm.lead', [['id','=',OPP]], ['name','x_studio_project_manager','user_id','partner_id']). The PM of record is x_studio_project_manager (a res.users record). Read that user's login/email for GChat.

Step 1 — Project-team inputs

Ask these in one chat message, not one at a time. Fill in anything the PO or quote already says so the user only has to confirm or correct it (for example: "Quote says 10–12 weeks ARO. Is that what was promised?"):

1. Mounting considerations: shelter, pole, wall, in-vehicle, custom brackets, site survey needed?
2. Promised timelines: delivery, install, go-live, and any dates the customer was promised
3. Hardware customizations: colors, sizes, enclosures, modems, ADA, anything off-catalog
4. Software customizations: integrations (GTFS/GTFS-RT, AVL/API vendor), custom apps, design work
5. Anything else the project team should know

If the user is plainly away, record "not provided" and keep going. Everything else can proceed without these answers.

Step 2 — PO ↔ Sales Order verification

Compare the PO against the Odoo SO, and the quotation against the SO (the quote may be an older revision). Catalog every difference in a table with: Severity | Item | PO says | Odoo/Quote says | Recommended action.

Check:

• PO number and SO number. The PO should reference the SO/quote number. client_order_ref should equal the PO number, and if it's blank, offer to write it.
• SO state. It should be sale (confirmed). If it's still draft/sent, flag it High and tell the user. Don't confirm the SO yourself.
• Line by line. Product/code, description, qty, unit price, discount, extended price. Also: lines on the PO but not the SO, lines on the SO but not the PO (optional sections are the usual cause), and $0 lines.
• Totals. Untaxed total, tax, and grand total. Tie out any difference to specific lines.
• Parties. Bill-to vs partner_invoice_id, ship-to vs partner_shipping_id.
• Terms. Payment terms, delivery dates vs commitment_date, Buy America/DBE/FTA clauses, retainage, and anything the SO doesn't cover.
• Execution. Is the PO or work order signed and numbered? Resellers such as TripSpark send work orders that aren't valid until executed and paired with a PO.
• Cross-border. If the end agency is outside the US (for example Canada), flag currency, sales tax/GST, and importer of record, and ask how the hardware ships (see Step 6).

Before flagging a contact detail as wrong, check it. MPM uses several email domains: mpmedia.tv, messagepoint.tv and messagepoint.media are all real.

Severity: High means money, quantity, product, or a missing or unconfirmed SO. Medium means reference fields, addresses, or terms. Low means cosmetic differences in descriptions or names. If everything matches, say so explicitly. "No discrepancies found" is a real result.

Step 3 — Every line has a cost

For each product line, purchase_price is the SO unit cost. If a line is $0, read that product variant's standard_price first. It's often already populated (for example the calculator cost), so offer it as the default and let the user confirm it.

• Warranty lines (product/template name contains "Warranty") with no cost: unit cost = unit price (MSRP, before discount) × 0.7569. That's 13% off MSRP for resellers, then another 13% margin on what remains. Apply it to every warranty line whether or not the order carries a reseller discount. Warranty cost depends on the order, so write it to the SO line only, never to the product variant.
• Warranty at $0 price and $0 cost (bundled): there's no MSRP to work from, so ask the user.
• Any other line with $0 cost: list them and ask the user for each unit cost in one message. Some really are $0 (pass-through or bundled items). Accept "0" as a confirmed answer and label it "User confirmed $0".
• Hardware on the Hardware Calculator: flag any line where the SO cost and the calculator's budget unit cost (Step 6) differ by more than 10%. Report it only; don't change the SO.

Then write back to Odoo. Show the full change table first (line, product, old cost → new cost, and the target: SO line, variant, or both) and get a clear yes before writing:

• SO line: bulk_update_records('sale.order.line', [line ids], {'purchase_price': cost}). Group lines that share a value.
• Product variant, for user-entered costs only: bulk_update_records('product.product', [variant id], {'standard_price': cost}). First check the product's category valuation (categ_id.property_valuation). If it's real_time, changing cost posts a revaluation entry, so warn the user and let them decide.
• Read the records back to confirm the values took. If Odoo refuses a write (for example on a locked order), report it and move on. Don't retry in a loop.

Step 4 — End-agency contacts

The end agency is usually the SO delivery address (partner_shipping_id). Often the delivery address is the reseller or its OEM (for example "Passio Technologies, Ashlyn Reep" or "GILLIG"). In that case find the agency from the PO ship-to, the quote name (x_studio_quote_name), or the opportunity name, then search_contacts for it.

For the agency company: odoo_search_read('res.partner', ['|',['id','=',AGENCY],['parent_id','=',AGENCY]], ['name','function','email','phone','mobile','type','street','city','state_id','zip']).

Collect name, title (function), email, phone and mobile. Also include the delivery contact and address. Add the reseller's contact on the SO as a separate group. Flag contacts that are missing a title or email. If Odoo has no agency record at all, say so and list whatever contacts appear on the PO.

Step 5 — Internal kickoff deck

Stage Kickoff Deck.pptx into the workspace and build the deck with build_deck.py (Appendix B). Slides, each an entry in slides:

1. Cover: agency name, with subtitle "Internal Project Kickoff | S0xxxx | PO xxxx"
2. Project snapshot: SO, PO number and date, reseller, end agency, salesperson, PM of record, one-time value, Year-1 recurring value, projected margin (from the budget)
3. Scope summary: hardware with quantities, services, installation, warranty term, and recurring licenses
4. Project team inputs: the five Step 1 answers
5. PO vs SO verification: the discrepancy table. If clean, a single line saying it's clean.
6. Cost verification: lines that were missing cost, how each was resolved, whether Odoo was updated, and any calculator-vs-SO flags over 10%
7. Customer contacts: a table per organization
8. Budget snapshot: category, sale price, projected cost, and margin %, plus the hardware forecast at current tariffs vs the budget
9. Links & open items: project folder, budget sheet, GChat space, and unresolved questions

Render it to PDF and look at every slide before uploading, because text that overflows is easy to miss otherwise. Keep table headings under ~40 characters. Put any explanatory text before a table in the same slide entry, because text after a table starts a new slide. A clean run is about 9 slides.

Step 6 — Project budget (Google Sheet, three tabs)

Categorize each SO product line into exactly one section:

• Travel: PS-Travel, or "Travel" / "Per-Diem" in the name
• DBE Installation: codes starting DBE- (installation and dispatch)
• Warranty: warranty products
• Annual Recurring: LIC-, SUP-, API hosting, and anything under a "...Recurring" or "Licensing" section
• MPM Professional Services: PS- (other than travel), SVC-, project management, design, training, provisioning
• Hardware: everything physical, including DSP-, MNT-, ACC-, KIT-, PRT-, controllers, modems, and PKG- shipping

Leave out optional lines that aren't on the PO, and note them. Keep $0-price lines that carry a cost (for example, a mounting budget).

Hardware Calculator. Stage the calculator and run read_calculator.py (Appendix C). For each hardware line, match default_code against skus (uppercase). Try the template code if the variant doesn't match, or the M-series model name against models. Where there's a match with a factory cost, add calc: {hs_col, hs_code, factory_cost, gross_kg} to the line. The budget sheet then computes the unit cost with the calculator's chain: Factory Cost × (1 + Total Tariff% + Roger Lu% + Overhead%) + Customs Agent $175/unit + Gross KG × freight $/kg (Ocean by default). The hardware subtotal on Project Budget is the sum of these formula-driven costs, so it ties to the projected budget. If the calculator's rate column disagrees with its own HS code, trust the HS code, and flag it for a calculator fix. (Known case: MNT-Post-EX37sw-CNTLVR is listed in the 8528 column but its HS code is 7308.90.6000 steel.) Lines with no calculator match (or no factory cost) use the SO unit cost; flag those as "not in calculator". If a match is uncertain, ask rather than guess.

Budget rates come straight from the calculator's Inputs sheet: the rates the quote was built on.

Ask how the hardware ships before researching actual rates. If it goes from the factory straight to a non-US end user (for example China → Canada), US tariffs don't apply, so the Actuals tab uses the destination country's duties instead (for Canada: CBSA MFN rates, the China steel surtax and steel-derivative surtax, and GST). The Project Budget tab still uses the normal calculator rates.

Actual rates have to be current. For each HS code on the order (for example 8471.41.0150 intelligent displays, 8528.xx secondary monitors and LED, Ch. 72/73 steel mounts; country of origin China unless the user says otherwise), research today's prevailing rates with web search. Use the USITC HTS (hts.usitc.gov), CBP CSMS messages, and Federal Register notices. Get the general duty rate and each additional program in effect (reciprocal/Section 122, IEEPA, Section 301, Section 232 steel, MPF). Map them into the four tariff rows, relabel rows through labels if the program names differ, and put the source and as-of date for each rate in notes. If a rate is ambiguous, show your reading to the user before building. Roger Lu, overhead, customs agent, and freight stay the same as the calculator.

Write budget_input.json (schema below) and run build_budget.py (Appendix A). Then recalculate and scan for errors (LibreOffice headless, or the xlsx skill's recalc.py). Sale subtotal per line must equal Odoo price_subtotal, and the total must equal the verified PO total. If they don't, fix the input before going further.

{"project": {"name","agency","reseller","so","po","po_date","pm","salesperson","built"},
 "freight_mode": "Ocean",
 "budget_rates": <calc.json rates, plus "source": "Hardware Calculator.xlsx › Inputs (modified <date>)">,
 "actual_rates": {"columns":["8471","8528","Steel","Other"], "as_of":"YYYY-MM-DD",
                  "base":[4], "s122":[4], "liberation":[4], "customs_fee":[4], "roger_lu":[4], "customs_agent":[4],
                  "air":[4], "express":[4], "ocean":[4], "overhead":[4], "labels":{...}, "notes":{"base":"source…"}},
 "lines": [{"section","code","name","qty","unit_price","discount_pct","so_unit_cost","cost_source","warranty":bool,
            "calc":{"hs_col","hs_code","factory_cost","gross_kg"} | null}]}

discount_pct is Odoo's percent (25 means 25%). unit_price is the pre-discount price.

What the tabs contain:

• Summary: sale price, projected cost, margin $ and %, forecast cost at current tariffs, actual cost to date, and remaining budget for each of the six categories. Then a one-time total (excluding recurring) and a total including Year-1 recurring.
• Project Budget: a rate table and line items by section with subtotals. Budget unit cost is formula-driven for calculator hardware, and the sheet compares budget vs SO cost and shows margin.
• Actuals: the same rows and formulas, but the tariff table holds current HS-code rates. It shows the forecast unit cost at today's tariffs. Yellow cells are for entering the actual factory cost or unit cost, vendor/bill reference, date and notes as bills arrive, and actual extended cost and variance vs budget are calculated from them.

Step 7–10 — Folder, uploads, GChat

Start the folder copy as soon as you know the agency name. It takes about 40 Drive calls, and the uploads need its folder ids.

Copy the folder (Drive MCP). A local cp -R fails on cloud-only files ("Resource deadlock avoided"), so recreate the folder server-side:

1. Folder name: the agency's short name, matching existing folders. If that name already exists (a repeat customer), ask whether to use <Agency> - <Project> or nest it inside the existing folder.
2. create_file(title=name, contentMimeType='application/vnd.google-apps.folder', parentId=<template's parentId>). Use contentMimeType, not the deprecated mimeType. Then walk the template breadth-first: for each child from search_files("parentId = '<id>'", excludeContentSnippets=true), re-create folders the same way, and copy_file(fileId, parentId=<new folder>, title=<same title>) for files. Keep the new ids of Meeting Notes and Financials.
3. Compare the new folder tree with the template (count of folders and files) before moving on.

Uploads. The deck (2–5 MB) and the budget are too large to push through a tool call as base64, so write them to the mounted Drive folder instead:

1. Copy both files to /mnt/user-data/outputs/, then device_commit_files them to the Mac paths …/<Project>/Meeting Notes/<Agency> Internal Kickoff Deck.pptx and …/<Project>/Financials/<Agency> Project Budget.xlsx. This works right away, even before device_bash ls can see the new folders (it reports "Resource deadlock avoided" until Drive for Desktop hydrates them), so don't wait for the mount. Confirm they landed with search_files("parentId = '<Meeting Notes id>' or parentId = '<Financials id>'"), and check that no duplicate folder appeared.
2. Convert to Google-native files. If the MPM Google Workspace MCP is connected and has a convert/import tool, use it. Otherwise use Chrome: open docs.google.com/spreadsheets/d/<xlsx id>/edit (or /presentation/d/<pptx id>/edit), then click File → Save as Google Sheets / Save as Google Slides. The native copy opens in a new tab, lands in the same folder, and keeps its formulas. Then trash the Office original with trash_file, but only after the Google copy exists. Close the tabs you opened.
3. If neither route works, leave the Office files in place (Slides and Sheets open them directly) and tell the user conversion is the one step left.

File the source documents the user dropped in, keeping their original file names. Copy each one from the uploads to /mnt/user-data/outputs/, then device_commit_files it:

• Work order with an SOW attached (for example a TripSpark Work Order with "Attachment 1 – Statement of Work"), or a standalone SOW or contract → Financials/Contract Documents/
• Purchase order → Financials/Purchase & Change Orders/
• A work order with no SOW is filed as the PO.
• If one PDF holds both a PO and an SOW, put a copy in both folders: Contract Documents and Purchase & Change Orders.
• Leave the quotation where it is; it's already in Odoo.

GChat space.

1. If the MPM Google Workspace MCP is connected and offers Chat tools (for example, create space or add members), use them. Otherwise drive chat.google.com in Chrome: New chat → Create a space → type the name → Create → Add members → type the PM's email → pick the suggestion → Add. Name it <Agency> – <Project> (S0xxxx). Existing spaces use names like "LakeTran Project Information" or "COTA Reflective Project".
2. PM of record comes from crm.lead.x_studio_project_manager. If it's empty, or there's no opportunity, ask the user who the PM is. Don't guess.
3. Post a first message: the project name, SO/PO, the folder link (the Drive viewUrl), the budget link, and the deck link. Pressing Enter opens a "Space members need access to these items" dialog. Keep Share with the space – Commenter and click Send message.
4. If Chrome isn't available either, give the user the space name, member list and message text to paste, and mark the step open.

Test runs

If the user says this is a test (for example, the real kickoff already happened), still run every step, but prefix the folder, file and space names with Skilltest-, and start the GChat message with "[SKILL TEST]" so the PM knows to ignore it. Odoo cost write-backs still need the user's approval.

Guardrails

• Odoo writes happen only after the user approves the change table. Never confirm, cancel or lock orders.
• Never overwrite or delete anything in the template folder. All work goes into the new copy.
• Money values the user gives go into Odoo and the sheet exactly as given. Warranty costs come from the formula.
• When something can't be done (connector missing, write refused), finish everything else and list the gaps at the end.

Wrap-up message

Keep it short: links to the project folder, deck, budget and GChat space; the count of discrepancies by severity; the Odoo changes made; open items. Following the standing instruction, add a short "MCP improvements" note listing any friction you hit that a new Odoo or Workspace MCP tool would remove. Examples: no folder-copy tool, no Office-to-Google conversion, fields_get returning every field, no single call that returns SO lines with cost and margin.

Appendix — bundled scripts

Write each script to the workspace verbatim (e.g. scripts/<name>) and run it with python3 (needs openpyxl and python-pptx). They're tested. Don't rewrite them from scratch; fix the input JSON instead.

Appendix A — build_budget.py

Builds the three-tab budget workbook. Every cost is a live formula, blue cells are inputs, and yellow cells on Actuals are filled in as bills arrive.

#!/usr/bin/env python3
"""Build the MPM project budget workbook (Summary / Project Budget / Actuals).
Usage: python3 build_budget.py budget_input.json out.xlsx
Every cost figure is a live formula; blue cells are inputs; yellow cells on Actuals get filled as bills arrive."""
import json, sys
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.utils import get_column_letter as CL

SECTIONS = ["Hardware", "MPM Professional Services", "Travel", "DBE Installation", "Warranty", "Annual Recurring"]
RATE_ROWS = [("base", "Base Tariff"), ("s122", "Section 122 Reciprocal"), ("liberation", "Liberation Tariff"),
             ("customs_fee", "Customs Fee"), (None, "Total Tariff"), ("roger_lu", "Roger Lu Fee"),
             ("customs_agent", "Customs Agent ($/unit)"), ("air", "Air"), ("express", "Express"), ("ocean", "Ocean"),
             ("overhead", "Overhead / Spoilage")]
R0, HDR = 4, 18            # rate-table header row, line-table header row
DARK, GOLD = "1C1D22", "D4AF37"
F_HDR = PatternFill("solid", fgColor=DARK); F_SEC = PatternFill("solid", fgColor="E7E6E6")
F_SUB = PatternFill("solid", fgColor="F2F2F2"); F_IN = PatternFill("solid", fgColor="DDEBF7")
F_ACT = PatternFill("solid", fgColor="FFF2CC"); F_TOT = PatternFill("solid", fgColor=GOLD)
WHITE_B = Font(bold=True, color="FFFFFF"); B = Font(bold=True); BLUE = Font(color="0000FF")
USD = '$#,##0.00;[Red]-$#,##0.00'; PCT = '0.00%'; THIN = Border(bottom=Side(style="thin", color="BFBFBF"))


def rate_table(ws, rates, title):
    ws.cell(R0 - 1, 1, title).font = B
    ws.cell(R0, 1, "Rate (by HS column)").font = WHITE_B
    cols = rates["columns"]
    for j, c in enumerate(cols):
        ws.cell(R0, 2 + j, str(c)).font = WHITE_B
    ws.cell(R0, 6, "Source / notes").font = WHITE_B
    for j in range(1, 7):
        ws.cell(R0, j).fill = F_HDR
    for i, (key, label) in enumerate(RATE_ROWS, start=1):
        r = R0 + i
        ws.cell(r, 1, rates.get("labels", {}).get(key, label) if key else label)
        for j in range(4):
            c = ws.cell(r, 2 + j)
            if key is None:
                c.value = f"=SUM({CL(2+j)}{R0+1}:{CL(2+j)}{R0+4})"; c.font = B
            else:
                c.value = rates[key][j]; c.font = BLUE; c.fill = F_IN
            c.number_format = USD if key in ("customs_agent", "air", "express", "ocean") else PCT
        note = rates.get("notes", {}).get(key or "total", "")
        ws.cell(r, 6, note)
    fr = R0 + len(RATE_ROWS) + 1          # freight-mode row (16)
    ws.cell(fr, 1, "Freight mode").font = B
    c = ws.cell(fr, 2, rates.get("freight_mode", "Ocean")); c.font = BLUE; c.fill = F_IN
    dv = DataValidation(type="list", formula1='"Air,Express,Ocean"', allow_blank=False); ws.add_data_validation(dv); dv.add(c)
    ws.cell(fr, 6, rates.get("source", ""))
    return fr


def refs():
    h = f"$B${R0}:$E${R0}"
    row = lambda n: f"$B${R0+n}:$E${R0+n}"
    return dict(hdr=h, tariff=row(5), roger=row(6), agent=row(7), ovh=row(11),
                frt_block=f"$B${R0+8}:$E${R0+10}", frt_lbl=f"$A${R0+8}:$A${R0+10}", mode=f"$B${R0+12}")


def header(ws, cols, widths):
    for j, (name, w) in enumerate(zip(cols, widths), start=1):
        c = ws.cell(HDR, j, name); c.font = WHITE_B; c.fill = F_HDR
        c.alignment = Alignment(wrap_text=True, vertical="center"); ws.column_dimensions[CL(j)].width = w
    ws.row_dimensions[HDR].height = 42; ws.freeze_panes = ws.cell(HDR + 1, 4)


def main(src, out):
    d = json.load(open(src)); p = d["project"]; lines = d["lines"]
    wb = Workbook(); sm = wb.active; sm.title = "Summary"
    bud = wb.create_sheet("Project Budget"); act = wb.create_sheet("Actuals")
    for ws, t in ((bud, "Project Budget"), (act, "Actuals")):
        ws.cell(1, 1, f"{t} — {p.get('name','')}").font = Font(bold=True, size=14)
        ws.cell(2, 1, f"SO {p.get('so','')}  |  PO {p.get('po','')}  |  {p.get('agency','')}  |  Reseller: {p.get('reseller','')}")
    rate_table(bud, {**d["budget_rates"], "freight_mode": d.get("freight_mode", "Ocean")},
               "Budget rates — Hardware Calculator Inputs (the rates the quote was built on)")
    rate_table(act, {**d["actual_rates"], "freight_mode": d.get("freight_mode", "Ocean")},
               f"Actual rates — prevailing HS-code tariffs as of {d['actual_rates'].get('as_of','')}")
    rf = refs()
    hs = lambda col, r: f"MATCH(${col}{r},{rf['hdr']},0)"

    header(bud, ["Section", "Product Code", "Description", "Qty", "Unit Price (MSRP)", "Disc %", "Sale Subtotal",
                 "SO Unit Cost", "Cost Source", "HS Col", "HS Code", "Factory Cost", "Gross KG", "Tariff %",
                 "Roger Lu %", "Overhead %", "Customs Agent $", "Freight $/unit", "Budget Unit Cost",
                 "Budget Ext Cost", "SO Ext Cost", "Budget − SO", "Margin $", "Margin %"],
           [16, 22, 44, 7, 14, 8, 15, 13, 24, 9, 14, 12, 9, 9, 9, 9, 11, 11, 14, 15, 15, 13, 14, 9])
    header(act, ["Section", "Product Code", "Description", "Qty", "HS Col", "HS Code", "Factory Cost (budget)",
                 "Gross KG", "Tariff % (current)", "Roger Lu %", "Overhead %", "Customs Agent $", "Freight $/unit",
                 "Forecast Unit Cost @ current tariff", "Budget Unit Cost", "ACTUAL factory / unit cost (enter)",
                 "Actual Unit Cost", "Actual Ext Cost", "Budget Ext − Actual", "Vendor / Bill Ref", "Date", "Notes"],
           [16, 22, 44, 7, 9, 14, 13, 9, 10, 9, 9, 11, 11, 15, 14, 16, 14, 15, 15, 20, 12, 30])

    r = HDR + 1; sub = {}
    for sec in SECTIONS:
        items = [l for l in lines if l["section"] == sec]
        if not items:
            continue
        for ws in (bud, act):
            ws.cell(r, 1, sec).font = B
            for j in range(1, 25 if ws is bud else 23):
                ws.cell(r, j).fill = F_SEC
        r += 1; first = r
        for l in items:
            calc = l.get("calc")
            b = lambda j, v: bud.cell(r, j, v)
            b(1, sec); b(2, l.get("code", "")); b(3, l.get("name", "")); b(4, l["qty"])
            b(5, l["unit_price"]).number_format = USD
            b(6, (l.get("discount_pct") or 0) / 100).number_format = PCT
            b(7, f"=D{r}*E{r}*(1-F{r})").number_format = USD
            if l.get("warranty") and not l.get("so_unit_cost"):
                b(8, f"=E{r}*0.7569"); b(9, "Warranty formula (MSRP × 0.7569)")
            else:
                c = b(8, l.get("so_unit_cost", 0)); b(9, l.get("cost_source", "Odoo SO line"))
                if l.get("cost_source", "").lower().startswith("user"):
                    c.font = BLUE; c.fill = F_IN
            bud.cell(r, 8).number_format = USD
            if calc:
                b(10, str(calc["hs_col"])); b(11, calc.get("hs_code", ""))
                b(12, calc["factory_cost"]).font = BLUE; b(13, calc["gross_kg"]).font = BLUE
                b(14, f"=INDEX({rf['tariff']},{hs('J', r)})"); b(15, f"=INDEX({rf['roger']},{hs('J', r)})")
                b(16, f"=INDEX({rf['ovh']},{hs('J', r)})"); b(17, f"=INDEX({rf['agent']},{hs('J', r)})")
                b(18, f"=M{r}*INDEX({rf['frt_block']},MATCH({rf['mode']},{rf['frt_lbl']},0),{hs('J', r)})")
                b(19, f"=L{r}*(1+N{r}+O{r}+P{r})+Q{r}+R{r}")
            else:
                b(19, f"=H{r}")
            b(20, f"=D{r}*S{r}"); b(21, f"=D{r}*H{r}"); b(22, f"=T{r}-U{r}"); b(23, f"=G{r}-T{r}")
            b(24, f'=IF(G{r}=0,"",W{r}/G{r})')
            for j, fmt in ((12, USD), (14, PCT), (15, PCT), (16, PCT), (17, USD), (18, USD), (19, USD), (20, USD),
                           (21, USD), (22, USD), (23, USD), (24, PCT)):
                bud.cell(r, j).number_format = fmt
            # ---- Actuals row (same row number) ----
            a = lambda j, v: act.cell(r, j, v)
            BP = "'Project Budget'!"
            a(1, sec); a(2, f"={BP}B{r}"); a(3, f"={BP}C{r}"); a(4, f"={BP}D{r}")
            a(15, f"={BP}S{r}")
            if calc:
                a(5, f"={BP}J{r}"); a(6, f"={BP}K{r}"); a(7, f"={BP}L{r}"); a(8, f"={BP}M{r}")
                a(9, f"=INDEX({rf['tariff']},{hs('E', r)})"); a(10, f"=INDEX({rf['roger']},{hs('E', r)})")
                a(11, f"=INDEX({rf['ovh']},{hs('E', r)})"); a(12, f"=INDEX({rf['agent']},{hs('E', r)})")
                a(13, f"=H{r}*INDEX({rf['frt_block']},MATCH({rf['mode']},{rf['frt_lbl']},0),{hs('E', r)})")
                a(14, f"=G{r}*(1+I{r}+J{r}+K{r})+L{r}+M{r}")
                a(17, f'=IF(P{r}="","",P{r}*(1+I{r}+J{r}+K{r})+L{r}+M{r})')
            else:
                a(14, f"=O{r}"); a(17, f'=IF(P{r}="","",P{r})')
            act.cell(r, 16).fill = F_ACT; act.cell(r, 20).fill = F_ACT; act.cell(r, 21).fill = F_ACT
            act.cell(r, 22).fill = F_ACT
            a(18, f'=IF(Q{r}="","",Q{r}*D{r})'); a(19, f'=IF(R{r}="","",{BP}T{r}-R{r})')
            for j, fmt in ((7, USD), (9, PCT), (10, PCT), (11, PCT), (12, USD), (13, USD), (14, USD), (15, USD),
                           (16, USD), (17, USD), (18, USD), (19, USD)):
                act.cell(r, j).number_format = fmt
            for ws in (bud, act):
                ws.cell(r, 3).alignment = Alignment(wrap_text=True, vertical="top")
                for j in range(1, 25): ws.cell(r, j).border = THIN
            r += 1
        last = r - 1
        # subtotal rows
        bud.cell(r, 1, f"{sec} subtotal").font = B
        for j in (7, 20, 21, 22, 23):
            c = bud.cell(r, j, f"=SUM({CL(j)}{first}:{CL(j)}{last})"); c.font = B; c.number_format = USD
        c = bud.cell(r, 24, f'=IF(G{r}=0,"",W{r}/G{r})'); c.font = B; c.number_format = PCT
        act.cell(r, 1, f"{sec} subtotal").font = B
        c = act.cell(r, 14, f"=SUMPRODUCT(D{first}:D{last},N{first}:N{last})"); c.font = B; c.number_format = USD
        c = act.cell(r, 15, f"=SUMPRODUCT(D{first}:D{last},O{first}:O{last})"); c.font = B; c.number_format = USD
        for j in (18, 19):
            c = act.cell(r, j, f"=SUM({CL(j)}{first}:{CL(j)}{last})"); c.font = B; c.number_format = USD
        for ws in (bud, act):
            for j in range(1, 25 if ws is bud else 23): ws.cell(r, j).fill = F_SUB
        sub[sec] = r; r += 2

    # ---------------- Summary ----------------
    sm.cell(1, 1, f"Project Financial Summary — {p.get('name','')}").font = Font(bold=True, size=16)
    info = [("End-customer agency", p.get("agency")), ("Reseller / customer", p.get("reseller")),
            ("Sales Order", p.get("so")), ("Customer PO", p.get("po")), ("PO date", p.get("po_date")),
            ("Project Manager", p.get("pm")), ("Salesperson", p.get("salesperson")), ("Budget built", p.get("built"))]
    for i, (k, v) in enumerate(info, start=3):
        sm.cell(i, 1, k).font = B; sm.cell(i, 2, v or "")
    h = 12
    cols = ["Category", "Sale Price", "Projected Cost", "Projected Margin $", "Projected Margin %",
            "Forecast Cost @ Current Tariffs", "Actual Cost to Date", "Remaining (Projected − Actual)", "% of Budget Spent"]
    for j, n in enumerate(cols, start=1):
        c = sm.cell(h, j, n); c.font = WHITE_B; c.fill = F_HDR; c.alignment = Alignment(wrap_text=True)
        sm.column_dimensions[CL(j)].width = 28 if j == 1 else 18
    sm.row_dimensions[h].height = 36
    BP, AC = "'Project Budget'!", "Actuals!"
    r = h + 1; rows = {}
    for sec in SECTIONS:
        sm.cell(r, 1, sec)
        s = sub.get(sec)
        vals = [f"={BP}G{s}", f"={BP}T{s}", f"={AC}N{s}", f"={AC}R{s}"] if s else [0, 0, 0, 0]
        sm.cell(r, 2, vals[0]); sm.cell(r, 3, vals[1]); sm.cell(r, 6, vals[2]); sm.cell(r, 7, vals[3])
        rows[sec] = r; r += 1

    def fill_derived(rr):
        sm.cell(rr, 4, f"=B{rr}-C{rr}"); sm.cell(rr, 5, f'=IF(B{rr}=0,"",D{rr}/B{rr})')
        sm.cell(rr, 8, f"=C{rr}-G{rr}"); sm.cell(rr, 9, f'=IF(C{rr}=0,"",G{rr}/C{rr})')
        for j in range(2, 10):
            sm.cell(rr, j).number_format = PCT if j in (5, 9) else USD
    for rr in rows.values():
        fill_derived(rr)
    one_time = [rows[s] for s in SECTIONS if s != "Annual Recurring"]
    for label, rng, fill in (("One-time project total (excl. recurring)", one_time, F_SUB),
                             ("Total incl. Year-1 annual recurring", list(rows.values()), F_TOT)):
        sm.cell(r, 1, label).font = B
        for j in (2, 3, 6, 7):
            sm.cell(r, j, "=" + "+".join(f"{CL(j)}{x}" for x in rng)).font = B
        fill_derived(r)
        for j in range(1, 10): sm.cell(r, j).fill = fill
        r += 1
    sm.cell(r + 1, 1, "Projected = Hardware Calculator rates the quote was built on. Forecast = same formulas at today's "
                      "HS-code tariffs (Actuals rate table). Actual = costs entered on the Actuals tab as bills arrive.").font = Font(italic=True, color="595959")
    sm.freeze_panes = "B13"
    wb.save(out); print("saved", out, "| section subtotal rows:", sub)


if __name__ == "__main__":
    main(sys.argv[1], sys.argv[2])

Appendix B — build_deck.py

Builds the internal kickoff deck from the Kickoff Deck.pptx template. Input: {"agency", "subtitle", "slides":[{"title":"line1\nline2", "blocks":[{"heading","text"} | {"heading","bullets":[...]} | {"heading","table":{"columns":[...],"rows":[[...]]}}]}]}. Text and tables share a slide while they fit; long content continues onto "(cont.)" slides automatically.

#!/usr/bin/env python3
"""Build the MPM internal kickoff deck from the Kickoff Deck.pptx template look.
Usage: python3 build_deck.py "Kickoff Deck.pptx" deck_input.json out.pptx
Template slide 1 = cover, slide 2 = content master (dark left title panel + right body). All other template
slides are dropped. Each JSON slide becomes one or more content slides (long tables/text auto-continue)."""
import copy, json, sys
from pptx import Presentation
from pptx.util import Emu, Pt
from pptx.dml.color import RGBColor

DARK, GOLD, GREY = RGBColor(0x1C, 0x1D, 0x22), RGBColor(0xD4, 0xAF, 0x37), RGBColor(0xF2, 0xF2, 0xF2)
BODY_L, BODY_T, BODY_W, BODY_H = 9596190, 1660731, 13594280, 11700000
CHARS_PER_LINE = 60
R_NS = "{http://schemas.openxmlformats.org/officeDocument/2006/relationships}"


def dup_slide(prs, src):
    new = prs.slides.add_slide(src.slide_layout)
    for shp in list(new.shapes):
        shp._element.getparent().remove(shp._element)
    for el in src.shapes._spTree.iterchildren():
        if el.tag.endswith(("}nvGrpSpPr", "}grpSpPr")):
            continue
        new.shapes._spTree.append(copy.deepcopy(el))
    for node in new.shapes._spTree.iter():          # re-point image relationships
        for attr in ("embed", "link", "id"):
            rid = node.get(R_NS + attr)
            if rid and rid in src.part.rels:
                rel = src.part.rels[rid]
                if not rel.is_external:
                    node.set(R_NS + attr, new.part.relate_to(rel.target_part, rel.reltype))
    return new


def drop_slide(prs, slide):
    lst = prs.slides._sldIdLst
    for sid in list(lst):
        if prs.part.related_part(sid.rId) is slide.part:
            prs.part.drop_rel(sid.rId); lst.remove(sid); return


def shape(slide, name, nth=0):
    """Find a template shape by name; fall back to the nth text box if the template was edited."""
    hit = [s for s in slide.shapes if s.name == name]
    return hit[0] if hit else [s for s in slide.shapes if s.has_text_frame and s.text_frame.text.strip()][nth]


def set_lines(tf, lines):
    """Replace text keeping the first run's formatting."""
    p0 = tf.paragraphs[0]; r0 = copy.deepcopy(p0.runs[0]._r) if p0.runs else None
    for p in tf.paragraphs[1:]:
        p._p.getparent().remove(p._p)
    for r in list(p0.runs):
        r._r.getparent().remove(r._r)
    for i, line in enumerate(lines):
        p = p0 if i == 0 else tf.add_paragraph()
        if i:
            p._p.insert(0, copy.deepcopy(p0._p.pPr)) if p0._p.pPr is not None else None
        if r0 is not None:
            rr = copy.deepcopy(r0); p._p.append(rr); p.runs[-1].text = line
        else:
            p.text = line


def est_lines(block):
    n = 0
    if block.get("heading"): n += 1.3
    for t in ([block["text"]] if block.get("text") else []) + block.get("bullets", []):
        n += max(1, -(-len(t) // CHARS_PER_LINE))
    return n + 0.6


def write_body(tb, blocks):
    tf = tb.text_frame; tf.word_wrap = True
    head_r = copy.deepcopy(tf.paragraphs[0].runs[0]._r)                   # Playfair heading run
    body_r = copy.deepcopy(next(r for p in tf.paragraphs[1:] for r in p.runs)._r)  # Karla body run
    for p in tf.paragraphs[1:]:
        p._p.getparent().remove(p._p)
    p0 = tf.paragraphs[0]
    for r in list(p0.runs):
        r._r.getparent().remove(r._r)
    first = [True]

    def para(run_xml, text, size):
        p = p0 if first[0] else tf.add_paragraph(); first[0] = False
        r = copy.deepcopy(run_xml); p._p.append(r); p.runs[-1].text = text; p.runs[-1].font.size = Pt(size)
    for b in blocks:
        if b.get("heading"): para(head_r, b["heading"].upper(), 36)
        if b.get("text"):
            for line in b["text"].split("\n"): para(body_r, line, 30)
        for t in b.get("bullets", []): para(body_r, "•  " + t, 30)
        para(body_r, "", 18)


def table_rows_emu(cols, rows):
    """Estimated height of each row, allowing for text wrapping in narrow columns."""
    per_col = max(6, int(92 / max(1, len(cols))))        # usable chars per column at 20pt, after word-wrap loss
    return [max(1, max(-(-len(str(v or "")) // per_col) for v in r)) * 310000 + 160000 for r in [cols] + rows]


def table_emu(b, with_heading=True):
    return (950000 if with_heading and b.get("heading") else 0) + sum(table_rows_emu(b["table"]["columns"], b["table"]["rows"])) + 450000


def add_table(slide, heading, cols, rows, top):
    """Draw a table at `top`; return the y position just below it."""
    if heading:
        tb = slide.shapes.add_textbox(BODY_L, top, BODY_W, Emu(700000))
        r = tb.text_frame.paragraphs[0].add_run(); r.text = heading.upper()
        r.font.size, r.font.bold, r.font.underline, r.font.name, r.font.color.rgb = Pt(36), True, True, "Playfair Display", DARK
        top += 950000
    heights = table_rows_emu(cols, rows)
    shp = slide.shapes.add_table(len(rows) + 1, len(cols), BODY_L, top, BODY_W, Emu(sum(heights)))
    t = shp.table
    for i, hgt in enumerate(heights):
        t.rows[i].height = Emu(hgt)
    for j, c in enumerate(cols):
        cell = t.cell(0, j); cell.text = str(c); cell.fill.solid(); cell.fill.fore_color.rgb = DARK
    for i, row in enumerate(rows, start=1):
        for j, v in enumerate(row):
            cell = t.cell(i, j); cell.text = "" if v is None else str(v)
            cell.fill.solid(); cell.fill.fore_color.rgb = GREY if i % 2 else RGBColor(0xFF, 0xFF, 0xFF)
    for i, row in enumerate(t.rows):
        for cell in row.cells:
            for p in cell.text_frame.paragraphs:
                for r in p.runs:
                    r.font.size = Pt(20); r.font.name = "Karla"
                    r.font.bold = i == 0
                    r.font.color.rgb = RGBColor(0xFF, 0xFF, 0xFF) if i == 0 else DARK
                    if i and str(r.text).upper() in ("HIGH", "CRITICAL"): r.font.color.rgb = RGBColor(0xC0, 0x00, 0x00)
    return top + sum(heights) + 450000


def paginate(spec):
    """Split one JSON slide into pages that fit. Text and tables share a page while they fit;
    text never follows a table on the same page (the body text box sits at the top)."""
    pages, cur, used, has_table = [], [], 0, False
    for b in spec.get("blocks", []):
        if "table" in b:
            rows = b["table"]["rows"] or [["—"] * len(b["table"]["columns"])]
            chunk, k = [], 0
            while k < len(rows):                      # split long tables by height, not a fixed row count
                chunk = [rows[k]]; k += 1
                while k < len(rows) and table_emu({**b, "table": {**b["table"], "rows": chunk + [rows[k]]}}) <= BODY_H:
                    chunk.append(rows[k]); k += 1
                part = {**b, "table": {**b["table"], "rows": chunk}}
                need = table_emu(part)
                if cur and used + need > BODY_H:
                    pages.append(cur); cur, used = [], 0
                cur.append(part); used += need; has_table = True
            continue
        need = est_lines(b) * 560000
        if cur and (has_table or used + need > BODY_H):
            pages.append(cur); cur, used, has_table = [], 0, False
        cur.append(b); used += need
    if cur: pages.append(cur)
    return pages or [[]]


def main(template, src, out):
    d = json.load(open(src)); prs = Presentation(template)
    cover, master = prs.slides[0], prs.slides[1]
    originals = list(prs.slides)[1:]
    set_lines(shape(cover, "Neoride…").text_frame, [d["agency"]])
    tf = shape(cover, "Neoride…").text_frame
    sub = tf.add_paragraph(); r = sub.add_run(); r.text = d.get("subtitle", "Internal Project Kickoff")
    r.font.size = Pt(40); r.font.color.rgb = GOLD
    for spec in d["slides"]:
        pages = paginate(spec)
        for k, blocks in enumerate(pages):
            s = dup_slide(prs, master)
            title = spec["title"].split("\n") + (["(cont.)"] if k else [])
            set_lines(shape(s, "Transitpoint Software and Services").text_frame, title)
            body = shape(s, "TextBox 6", 1)
            text_blocks = [b for b in blocks if "table" not in b]
            if text_blocks:
                write_body(body, text_blocks)
            else:
                body._element.getparent().remove(body._element)
            top = BODY_T + (Emu(int(sum(est_lines(b) for b in text_blocks) * 560000)) if text_blocks else 0)
            for b in blocks:
                if "table" in b:
                    top = add_table(s, b.get("heading", ""), b["table"]["columns"], b["table"]["rows"], top)
    for s in originals:
        drop_slide(prs, s)
    prs.save(out); print("saved", out, "slides:", len(prs.slides))


if __name__ == "__main__":
    main(*sys.argv[1:4])

Appendix C — read_calculator.py

Pulls budget rates and per-SKU factory cost, gross KG and HS column out of Hardware Calculator.xlsx into calc.json.

#!/usr/bin/env python3
"""Pull budget rates + per-SKU factory cost / gross KG / HS column out of Hardware Calculator.xlsx.
Usage: python3 read_calculator.py "Hardware Calculator.xlsx" calc.json"""
import json, sys
from openpyxl import load_workbook

wb = load_workbook(sys.argv[1], data_only=False)
inp = wb["Inputs"]
cols = [str(inp.cell(1, c).value).replace(".0", "") for c in range(2, 6)]      # 8471, 8528, Steel, Other HS Code
rowmap = {"base": 2, "s122": 3, "liberation": 4, "customs_fee": 5, "roger_lu": 8, "customs_agent": 9,
          "air": 11, "express": 12, "ocean": 13, "overhead": 15}
rates = {k: [float(inp.cell(r, c).value or 0) for c in range(2, 6)] for k, r in rowmap.items()}
rates["columns"] = ["8471", "8528", "Steel", "Other"]
rates["labels"] = {k: inp.cell(r, 1).value for k, r in rowmap.items() if k in ("base", "s122", "liberation", "customs_fee")}
rates["reseller_b_margin"] = inp["B17"].value; rates["msrp_margin"] = inp["B18"].value
col_of = lambda hs: {8471: "8471", 8528: "8528"}.get(int(float(hs)) if str(hs).replace(".", "").isdigit() else 0,
                                                     "Steel" if str(hs).lower().startswith("steel") else "Other")
skus = {}
pe = wb["Pricing Engine"]
for r in range(2, pe.max_row + 1):
    code = pe.cell(r, 2).value
    if code:
        skus[code.strip().upper()] = {"name": pe.cell(r, 3).value, "category": pe.cell(r, 4).value,
                                      "hs_col": col_of(pe.cell(r, 5).value or 0), "hs_code": pe.cell(r, 6).value,
                                      "factory_cost": pe.cell(r, 8).value, "gross_kg": pe.cell(r, 9).value,
                                      "source": f"Pricing Engine row {r}"}
models = {}
md = wb["Models"]
for r in range(2, md.max_row + 1):
    m = md.cell(r, 1).value
    if m:
        models[str(m).strip().upper()] = {"hs_col": col_of(md.cell(r, 2).value or "other"), "gross_kg": md.cell(r, 3).value,
                                          "factory_cost": md.cell(r, 4).value, "source": f"Models row {r}"}
json.dump({"rates": rates, "skus": skus, "models": models}, open(sys.argv[2], "w"), indent=1, default=str)
print(f"rates for {cols}; {len(skus)} SKUs ({sum(1 for s in skus.values() if s['factory_cost'])} with factory cost); {len(models)} models")