#!/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])