217 lines
13 KiB
Python
217 lines
13 KiB
Python
#!/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])
|