Spaces:
Running on CPU Upgrade
Running on CPU Upgrade
File size: 20,980 Bytes
149aa87 2e7e617 149aa87 2e7e617 149aa87 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 | """Financial module — gross margin (the bridge between Sales revenue and Inventory cost),
margin by brand / category / SKU, margin trend & YoY, margin-dilutive SKUs, and the
cash-conversion-cycle capstone (DSO + DIO − DPO) that ties the AR + Inventory + Financial
working-capital picture together.
Margin source: Odoo's Margin module is installed, so sale.order.line carries `margin`
(= price_subtotal − purchase_price×qty) and `purchase_price` (snapshot cost at sale).
Both are read_group-aggregatable (verified). COGS is derived as revenue − margin.
Data-quality caveats surfaced and reported, not hidden:
- ~4% of sales-line revenue runs through zero-cost lines → their margin reads as 100%
and slightly overstates GM. Quantified in `uncosted_share()` and the validation panel.
- product.list_price is uniformly 1.0 in this Odoo (pricing lives in pricelists), so
"discount vs list" is not computable here — price realization is expressed as realized
GM% instead. (A pricelist-join version is on the backlog.)
"""
import sys
import datetime as dt
from copy import deepcopy
from pathlib import Path
sys.path.insert(0, str(Path(__file__).resolve().parents[1]))
import core.odoo as O
import core.periods as P
import modules.inventory as inv_mod
import modules.ar as ar_mod
# These descriptors are intentionally data-free. The Finance application registry may import
# them on every ordinary page load without opening Odoo or inventing an application-specific
# store. A cell becomes available only after its named input is promoted into finance_spine's
# governed tenant-mirror fact plane.
_CASH_CYCLE_DASHBOARD = {
'key': 'cash-cycle',
'title': 'Cash cycle',
'kind': 'metric_strip',
'basisLabel': 'LTM turnover with point-in-time working-capital balances',
'metrics': [
{
'key': 'dso_days', 'label': 'DSO', 'format': 'days',
'state': 'unavailable', 'scope': 'hq_consolidated',
'basisLabel': 'LTM posted customer-invoice sales and point-in-time AR',
'missingSource': 'A dated AR balance snapshot and its LTM posted-invoice denominator are not yet joined in the canonical mirror.',
'validationMethod': 'financial.validate',
},
{
'key': 'dio_days', 'label': 'DIO', 'format': 'days',
'state': 'unavailable', 'scope': 'hq_consolidated',
'basisLabel': 'LTM posted inventory cost flow and point-in-time inventory value',
'missingSource': 'Canonical inventory value and posted cost-flow facts do not yet share a validated turnover basis.',
'validationMethod': 'financial.validate',
},
{
'key': 'dpo_days', 'label': 'DPO', 'format': 'days',
'state': 'unavailable', 'scope': 'hq_consolidated',
'basisLabel': 'LTM posted vendor-bill throughput and point-in-time AP',
'missingSource': 'A dated AP balance snapshot and validated vendor-bill throughput are not yet joined in the canonical mirror.',
'validationMethod': 'financial.validate',
},
{
'key': 'ccc_days', 'label': 'Cash conversion', 'format': 'days',
'state': 'unavailable', 'scope': 'hq_consolidated',
'basisLabel': 'DIO + DSO - DPO on one reconciled LTM basis',
'missingSource': 'Cash conversion stays unavailable until DSO, DIO, and DPO are all available on the same governed basis.',
'validationMethod': 'financial.validate',
},
],
}
_MARGIN_DASHBOARD = {
'key': 'margin-drill',
'title': 'Margin drill',
'kind': 'drill_matrix',
'basisLabel': 'Odoo sale-line margin on confirmed wholesale orders',
'metrics': [
{
'key': 'category_margin', 'label': 'Category margin', 'format': 'usd',
'state': 'unavailable', 'scope': 'business_unit', 'drillDimension': 'category',
'sourceFact': 'revenue',
'basisLabel': 'Confirmed sale/done wholesale lines; GIFTWARE DEALS excluded; sale-line margin',
'missingSource': 'The canonical Finance spine does not yet expose a validated gross-margin fact grouped by category.',
'validationMethod': 'financial.validate',
},
{
'key': 'customer_margin', 'label': 'Customer margin', 'format': 'usd',
'state': 'unavailable', 'scope': 'business_unit', 'drillDimension': 'order_partner',
'sourceFact': 'revenue',
'basisLabel': 'Confirmed sale/done wholesale lines; GIFTWARE DEALS excluded; sale-line margin',
'missingSource': 'The canonical Finance spine does not yet expose a validated gross-margin fact grouped by customer.',
'validationMethod': 'financial.validate',
},
{
'key': 'sku_margin', 'label': 'SKU margin', 'format': 'usd',
'state': 'unavailable', 'scope': 'business_unit', 'drillDimension': 'product_code',
'sourceFact': 'revenue',
'basisLabel': 'Confirmed sale/done wholesale lines; GIFTWARE DEALS excluded; sale-line margin',
'missingSource': 'The canonical Finance spine does not yet expose a validated gross-margin fact grouped by SKU.',
'validationMethod': 'financial.validate',
},
],
}
def cash_cycle_dashboard_contract():
"""Return the mirror-safe cash-cycle projection without performing a live read."""
return deepcopy(_CASH_CYCLE_DASHBOARD)
def margin_dashboard_contract():
"""Return governed margin drill requirements without performing a live read."""
return deepcopy(_MARGIN_DASHBOARD)
# ---------- core margin window ----------
def _window(date_from, date_to, team_id=None):
"""Revenue, margin, derived COGS and GM% for a sale-line window (RI+FFS scope)."""
dom = O.sale_line_domain(date_from=str(date_from), date_to=str(date_to), team_id=team_id)
rev = O.sum_field('sale.order.line', dom, 'price_subtotal')
margin = O.sum_field('sale.order.line', dom, 'margin')
cogs = rev - margin
return {
'revenue': rev,
'margin': margin,
'cogs': cogs,
'gm_pct': (margin / rev * 100) if rev else 0.0,
}
def headline(t=None, team_id=None):
"""YTD gross margin vs same-period last year (consistent with the Sales headline)."""
t = t or P.today()
yf, yt = P.ytd(t)
lf, lt = P.ytd_last_year(t)
this = _window(yf, yt, team_id)
last = _window(lf, lt, team_id)
lf2, lt2 = P.ltm(t)
ltm = _window(lf2, lt2, team_id)
return {
'window': f'{yf} → {yt}',
'revenue': this['revenue'],
'revenue_ly': last['revenue'],
'revenue_yoy_pct': P.yoy_pct(this['revenue'], last['revenue']),
'cogs': this['cogs'],
'cogs_ly': last['cogs'],
'cogs_yoy_pct': P.yoy_pct(this['cogs'], last['cogs']),
'gm_dollars': this['margin'],
'gm_pct': this['gm_pct'],
'gm_pct_ly': last['gm_pct'],
'gm_pp_delta': this['gm_pct'] - last['gm_pct'],
'gm_dollars_ly': last['margin'],
'gm_dollars_yoy_pct': P.yoy_pct(this['margin'], last['margin']),
'ltm_revenue': ltm['revenue'],
'ltm_gm_dollars': ltm['margin'],
'ltm_gm_pct': ltm['gm_pct'],
}
def by_brand(t=None):
"""Fisch vs Royal: revenue, GM$, GM% (YTD) + same-period-LY for variance columns."""
t = t or P.today()
yf, yt = P.ytd(t)
lf, lt = P.ytd_last_year(t)
rows = []
for tid in O.TEAM_IDS:
w = _window(yf, yt, team_id=tid)
ly = _window(lf, lt, team_id=tid)
rows.append({'brand': O.TEAM_NAMES[tid], 'revenue': w['revenue'],
'gm_dollars': w['margin'], 'gm_pct': w['gm_pct'],
'gm_dollars_ly': ly['margin'], 'gm_change': w['margin'] - ly['margin'],
'gm_pct_ly': ly['gm_pct'], 'gm_pp_delta': w['gm_pct'] - ly['gm_pct']})
return sorted(rows, key=lambda r: -r['gm_dollars'])
def _product_cat_map():
"""product_id → main category name (reusing inventory's category resolver)."""
o = O.get_odoo()
catmap = inv_mod._cat_main_map()
prods = o.search_read('product.product', [('default_code', '!=', False)],
['id', 'categ_id'])
return {p['id']: (catmap.get(O.m2o_id(p.get('categ_id'))) or '(uncategorized)')
for p in prods}
def _per_product(date_from, date_to, team_id=None):
"""read_group sale lines by product → rev, margin, qty (YTD window)."""
o = O.get_odoo()
dom = O.sale_line_domain(date_from=str(date_from), date_to=str(date_to), team_id=team_id)
g = o.read_group('sale.order.line', domain=dom,
fields=['price_subtotal:sum', 'margin:sum', 'product_uom_qty:sum'],
groupby=['product_id'], lazy=False)
out = []
for r in g:
pid = O.m2o_id(r.get('product_id'))
if not pid:
continue
out.append({'product_id': pid, 'name': O.m2o_name(r.get('product_id')),
'revenue': r.get('price_subtotal') or 0.0,
'margin': r.get('margin') or 0.0,
'qty': r.get('product_uom_qty') or 0.0})
return out
def by_category(t=None, limit=20, team_id=None):
"""Where the gross-profit dollars come from, by main category (YTD) — with same-period-LY
so the page can show each category's GM$ change and pp drift (the mix/variance read)."""
t = t or P.today()
yf, yt = P.ytd(t)
lf, lt = P.ytd_last_year(t)
catmap = _product_cat_map()
agg = {}
for r in _per_product(yf, yt, team_id):
cat = catmap.get(r['product_id'], '(uncategorized)')
a = agg.setdefault(cat, {'category': cat, 'revenue': 0.0, 'gm_dollars': 0.0,
'revenue_ly': 0.0, 'gm_dollars_ly': 0.0})
a['revenue'] += r['revenue']
a['gm_dollars'] += r['margin']
for r in _per_product(lf, lt, team_id):
cat = catmap.get(r['product_id'], '(uncategorized)')
a = agg.setdefault(cat, {'category': cat, 'revenue': 0.0, 'gm_dollars': 0.0,
'revenue_ly': 0.0, 'gm_dollars_ly': 0.0})
a['revenue_ly'] += r['revenue']
a['gm_dollars_ly'] += r['margin']
for a in agg.values():
a['gm_pct'] = (a['gm_dollars'] / a['revenue'] * 100) if a['revenue'] else 0.0
a['gm_pct_ly'] = (a['gm_dollars_ly'] / a['revenue_ly'] * 100) if a['revenue_ly'] else None
a['gm_change'] = a['gm_dollars'] - a['gm_dollars_ly']
a['gm_pp_delta'] = (a['gm_pct'] - a['gm_pct_ly']) if a['gm_pct_ly'] is not None else None
return sorted(agg.values(), key=lambda x: -x['gm_dollars'])[:limit]
def _shift_years(d, years):
"""Same calendar date shifted by whole years (Feb-29 → Feb-28)."""
d = dt.date.fromisoformat(str(d))
try:
return d.replace(year=d.year + years).isoformat()
except ValueError:
return d.replace(year=d.year + years, day=28).isoformat()
# Bridge cutoff presets: label + window fn. The compare window is ALWAYS the same dates shifted
# back one year (for LTM that IS the prior 12 months), so seasonality never distorts the bridge.
BRIDGE_BASES = {
'ytd': ('YTD', P.ytd),
'qtd': ('QTD', P.qtd),
'mtd': ('MTD', P.mtd),
'ltm': ('LTM', P.ltm),
}
def margin_bridge(t=None, team_id=None, basis='ytd'):
"""The gross-margin bridge — decomposes ΔGM$ (the `basis` window vs the same window one year
earlier) into per-SKU effects that sum EXACTLY (validated to the cent):
volume = (Q1−Q0)·(P0−C0) price = (P1−P0)·Q1 cost = −(C1−C0)·Q1 (SKUs in both periods)
new = GM1 of SKUs with no prior-window sales lost = −GM0 of SKUs gone this window
basis ∈ BRIDGE_BASES: 'ytd' (default), 'qtd', 'mtd', 'ltm' — the owner-selectable cutoff.
P/C are realized $-per-unit from the window aggregates (Odoo margin basis). Zero-qty lines
(services, adjustments) can't carry a unit price — their ΔGM lands in `other` so the bridge
still ties. The classic FP&A "why did margin move" chart (IBCS/PVM canon)."""
t = t or P.today()
_lbl, _win = BRIDGE_BASES.get(basis, BRIDGE_BASES['ytd'])
yf, yt = _win(t)
lf, lt = _shift_years(yf, -1), _shift_years(yt, -1)
this = {r['product_id']: r for r in _per_product(yf, yt, team_id)}
last = {r['product_id']: r for r in _per_product(lf, lt, team_id)}
vol = prc = cst = new = lost = other = 0.0
fx = [] # per-SKU effect rows — the drill-down behind each bridge bar
for pid, r1 in this.items():
r0 = last.get(pid)
if r0 is None:
new += r1['margin']
fx.append({'name': r1['name'], 'bucket': 'New SKUs', 'volume': 0.0, 'price': 0.0,
'cost': 0.0, 'change': r1['margin']})
continue
q0, q1 = r0['qty'], r1['qty']
if q0 > 0 and q1 > 0:
p0, c0 = r0['revenue'] / q0, (r0['revenue'] - r0['margin']) / q0
p1, c1 = r1['revenue'] / q1, (r1['revenue'] - r1['margin']) / q1
v, pr, ct = (q1 - q0) * (p0 - c0), (p1 - p0) * q1, -(c1 - c0) * q1
vol += v
prc += pr
cst += ct
fx.append({'name': r1['name'], 'bucket': 'Continuing', 'volume': v, 'price': pr,
'cost': ct, 'change': r1['margin'] - r0['margin']})
else: # unit price undefined on either side → exact residual bucket
other += r1['margin'] - r0['margin']
fx.append({'name': r1['name'], 'bucket': 'Other', 'volume': 0.0, 'price': 0.0,
'cost': 0.0, 'change': r1['margin'] - r0['margin']})
for pid, r0 in last.items():
if pid not in this:
lost += -r0['margin']
fx.append({'name': r0['name'], 'bucket': 'Lost SKUs', 'volume': 0.0, 'price': 0.0,
'cost': 0.0, 'change': -r0['margin']})
gm0 = sum(r['margin'] for r in last.values())
gm1 = sum(r['margin'] for r in this.values())
fx.sort(key=lambda r: -abs(r['change']))
return {'gm_ly': gm0, 'gm_ytd': gm1, 'volume': vol, 'price': prc, 'cost': cst,
'new': new, 'lost': lost, 'other': other,
'delta': gm1 - gm0,
'sku_effects': fx[:600],
'basis': basis, 'basis_label': _lbl,
'cmp_label': 'prior LTM' if basis == 'ltm' else 'LY',
'date_from': str(yf), 'date_to': str(yt), 'cmp_from': str(lf), 'cmp_to': str(lt),
'window': f'{yf} → {yt}', 'cmp_window': f'{lf} → {lt}',
'ties': abs((vol + prc + cst + new + lost + other) - (gm1 - gm0)) <= 1.0}
def margin_trend(n=13, t=None, team_id=None):
"""Monthly revenue, GM$ and GM% over the last n months (trend + seasonality)."""
t = t or P.today()
out = []
for label, mf, mt in P.month_starts(n, t):
w = _window(mf, mt, team_id)
out.append({'month': label, 'revenue': w['revenue'],
'gm_dollars': w['margin'], 'gm_pct': w['gm_pct']})
return out
def low_margin_skus(t=None, limit=25, min_rev=2000.0, team_id=None):
"""Margin-dilutive SKUs: meaningful YTD revenue but low realized GM%. Zero-cost
(uncosted) lines are flagged separately — their 100% GM is a data gap, not real."""
t = t or P.today()
yf, yt = P.ytd(t)
rows = []
for r in _per_product(yf, yt, team_id):
if r['revenue'] < min_rev:
continue
gm_pct = (r['margin'] / r['revenue'] * 100) if r['revenue'] else 0.0
rows.append({'name': r['name'], 'revenue': r['revenue'],
'gm_dollars': r['margin'], 'gm_pct': gm_pct,
'uncosted': abs(r['margin'] - r['revenue']) < 0.01})
real = [r for r in rows if not r['uncosted']]
return sorted(real, key=lambda x: x['gm_pct'])[:limit]
def uncosted_share(t=None, team_id=None):
"""Share of YTD revenue running through zero-cost lines (margin overstated)."""
t = t or P.today()
yf, yt = P.ytd(t)
dom = O.sale_line_domain(date_from=str(yf), date_to=str(yt), team_id=team_id)
rev_all = O.sum_field('sale.order.line', dom, 'price_subtotal')
rev_zero = O.sum_field('sale.order.line', dom + [('purchase_price', '=', 0)], 'price_subtotal')
return {'rev_all': rev_all, 'rev_uncosted': rev_zero,
'pct': (rev_zero / rev_all * 100) if rev_all else 0.0}
def cash_conversion_cycle(t=None):
"""CCC = DIO + DSO − DPO (days). Directional, company-wide.
Throughput basis = LTM purchases (vendor bills net of refunds), the most consistent
company-wide proxy for COGS available without a P&L-account reconciliation. DSO comes
from the AR module (revenue-based, solid). The dominant driver — inventory days — is
robust to the exact basis, which is the point.
"""
t = t or P.today()
o = O.get_odoo()
lf, lt = P.ltm(t)
base = [('state', '=', 'posted'), ('invoice_date', '>=', str(lf)), ('invoice_date', '<=', str(lt))]
purch = (O.sum_field('account.move', base + [('move_type', '=', 'in_invoice')], 'amount_total')
- O.sum_field('account.move', base + [('move_type', '=', 'in_refund')], 'amount_total'))
daily = (purch / 365.0) if purch else None
ap_rows = o.search_read('account.move',
[('move_type', 'in', ['in_invoice', 'in_refund']),
('state', '=', 'posted'),
('payment_state', 'in', ['not_paid', 'partial'])],
['amount_residual_signed'])
ap_open = abs(sum(r.get('amount_residual_signed') or 0.0 for r in ap_rows))
inv_value = inv_mod.summary(t)['total_inv_value']
dso = ar_mod.summary(t)['dso_days']
dio = (inv_value / daily) if daily else None
dpo = (ap_open / daily) if daily else None
ccc = (dio + dso - dpo) if (dio is not None and dso is not None and dpo is not None) else None
return {
'ltm_purchases': purch,
'inventory_value': inv_value,
'ap_open': ap_open,
'dio_days': dio,
'dso_days': dso,
'dpo_days': dpo,
'ccc_days': ccc,
'basis': 'LTM purchases (vendor bills net of refunds) as COGS/throughput proxy',
}
def validate(t=None, team_id=None):
"""Reconcile margin metrics to independent Odoo aggregates. When team_id is set (a single BU
selected) every check runs SCOPED to that BU, so the validation panel never reconciles
against — or exposes — the other BU's numbers. The cross-BU brand-mirror check (#1) only
makes sense consolidated, so it runs only when team_id is None."""
t = t or P.today()
yf, yt = P.ytd(t)
o = O.get_odoo()
checks = []
total = _window(yf, yt, team_id)
# 1. Σ(brand margin) == total margin (consolidated only — cross-BU)
if team_id is None:
brand_sum = sum(b['gm_dollars'] for b in by_brand(t))
checks.append({'check': 'Margin: Σ(brand) == total (YTD)',
'a': round(brand_sum, 2), 'b': round(total['margin'], 2),
'gap': round(brand_sum - total['margin'], 2),
'ok': abs(brand_sum - total['margin']) <= 1.0})
# 2. Σ(per-product margin) == total margin (grouping integrity)
prod_sum = sum(r['margin'] for r in _per_product(yf, yt, team_id))
checks.append({'check': 'Margin: Σ(per-product) == total (YTD)',
'a': round(prod_sum, 2), 'b': round(total['margin'], 2),
'gap': round(prod_sum - total['margin'], 2),
'ok': abs(prod_sum - total['margin']) <= 1.0})
# 2b. The margin bridge decomposition sums exactly to ΔGM (volume+price+cost+new+lost+other)
br = margin_bridge(t, team_id=team_id)
_parts = br['volume'] + br['price'] + br['cost'] + br['new'] + br['lost'] + br['other']
checks.append({'check': 'Margin bridge: Σ(effects) == ΔGM YTD vs LY',
'a': round(_parts, 2), 'b': round(br['delta'], 2),
'gap': round(_parts - br['delta'], 2),
'ok': abs(_parts - br['delta']) <= 1.0})
# 3. Odoo line-level margin identity: margin == price_subtotal − purchase_price×qty
dom = O.sale_line_domain(date_from=str(yf), date_to=str(yt), team_id=team_id)
smp = o.search_read('sale.order.line', dom,
['price_subtotal', 'purchase_price', 'product_uom_qty', 'margin'], limit=1)
if smp:
r = smp[0]
recomputed = (r.get('price_subtotal') or 0) - (r.get('purchase_price') or 0) * (r.get('product_uom_qty') or 0)
checks.append({'check': 'Margin identity: price_subtotal − cost×qty (sample line)',
'a': round(r.get('margin') or 0, 2), 'b': round(recomputed, 2),
'gap': round((r.get('margin') or 0) - recomputed, 2),
'ok': abs((r.get('margin') or 0) - recomputed) <= 0.05})
return checks
|