Compare the contract with what was invoiced.
Lookups bring in the average invoiced price and the latest cost for each item. Compliance, cost drift and margin are formulas on top, recalculated when the contract refreshes.
| Column | Formula |
|---|---|
| Invoiced Price | lookup(source="datasets", name="Invoice lines", result="Net Price", match_by=['Item Number':'Item Number'], filter_by=['Customer' == 'header.Customer', 'Invoice Date' >= 'header.effectiveDate'], agg="avg") |
| Compliance | if(abs('Invoiced Price' - 'Contract Price') > 0.01, "Off contract", "On contract") |
| Current Cost | lookup(source="datasets", name="Item costs", result="Unit Cost", match_by=['Item Number':'Item Number'], sort_by="-Effective Date", agg="first") |
| Cost Drift | ('Current Cost' - 'Cost at Signing') / 'Cost at Signing' |
| Margin % | ('Contract Price' - 'Current Cost') / 'Contract Price' |