Build something that is not there yet
Explain gross-margin change with a reconciled price, volume, mix, and cost bridge
- Finance subtracts one period's gross margin from another and writes the explanation by hand, and the story changes with whoever builds it.
- Price, volume, mix and cost effects do not add up to the margin change, or an unlabelled line absorbs the difference.
- New products, discontinued products, returns or a zero base quantity distort the volume and mix effect and nobody can see by how much.
Explain the gross-margin change between two periods under the user's approved method as price, unit cost, volume and mix components plus any new, discontinued, no-quantity, interaction, translation, period-length or late-change components the approved rules define, adding up to the observed change when the bridge closes, with any rounding shown and every line left out accounted for.
Use this Recipe
Use the Vibedata Recipe `finance-gross-margin-variance-bridge` at https://github.com/accelerate-data/vibedata-cookbooks/blob/main/recipes/finance-gross-margin-variance-bridge/recipe.md Read the Recipe and execute it in the context of the current Intent.
Recipe id finance-gross-margin-variance-bridge · Canonical definition
Verified by
What has to be observably true before this Recipe is finished.
- On a run that completes, each row's components plus its rounding remainder equal its observed margin change exactly at the approved precision and rounding point; the remainder appears where the approved rule puts it, with its amount, never as an unlabelled plug.
- Components equal values worked out independently of the model, from the sources and approved rules, for each agreed case: price-only lands in price alone in that cell, cost-only in unit cost alone, quantity-only in volume and mix (price and cost zero unless an approved translation moves them).
- A row whose pair has a set-aside line is flagged incomplete, and a new or discontinued class resting on one reads unverified; the total shows set-aside margin and revenue and the share of change explained, and reads unverified above the approved tolerance.
- A cell with no sales in one period, a zero base quantity or netted sales and returns follows the approved classification, and no rate is divided by zero; new, discontinued and no-quantity amounts are shown as approved, separately identifiable, never folded into volume or mix unless approved.
- A return or credit note is valued and shown as the approved rule says; a return priced differently from its sale moves price or unit cost only as that rule says.
- Where the total closes within the approved bound it equals the placed rows' components and remainders and, over placed lines, an independent comparison margin less base margin; a roll-up holds each member once or by shares that sum to the whole, never summed across levels.
- On a run that completes, every input line is placed once, set aside with its reason and amount, or shown or counted as outside the approved periods or scope with how many and why; counts, revenue and cost tie out, and a missing or malformed amount is listed, never read as zero unless approved.
- Currencies, units and entities are never netted unless an approved translation, conversion or consolidation rule is stated; each reconciles on its own, is bridged separately or is set aside with its reason, and a translation effect appears as the approved rule says, never silently as price or cost.
- The periods compared are exactly the approved ones and each line sits in the period the approved date basis gives it; a difference in length follows the approved rule (kept in the components, normalised, or the run rejected or flagged), never silently.
- A cell whose product matches across the two periods under the approved rule and grain is never also new or discontinued; each unmatched product follows the approved classification, and a renamed, merged or split product is not reported as a false new-plus-discontinued pair.
- A late or backdated posting, or a changed amount on a valued line (credit, rebate, cost revision), follows the approved time basis; a change the rule does not restate appears as its own labelled movement, and a source without dated versions reports restatement as unproven.
- Where a bridge does not close within the approved bound and the approved rule publishes it, the gap amount and reason stay visible and closure reads failed or unverified for that row or total, never passed; otherwise the run fails or publishes nothing, naming each offending line or row.
Recipe promptThe task specification the agent reads. Reference only — it is not what you copy.
Build <bridge_model>, <bridge_total_model>, <exceptions_model> and <coverage_model> from <sales_line_source> and the mapping, rate and factor sources the approved rules use. Inherit the semantics the Intent or its approved requirements already establish, and resolve with the user only the semantics they leave open. For the two approved periods, place each in-scope sales line at the approved grain, currency, unit and entity, value margin as approved, classify each cell under the approved rule, and split its margin change into the approved price, unit cost, volume and mix components plus any new, discontinued, no-quantity, interaction, translation, period-length or late-change components the approved rules define, so that, on a run that closes, the components and any approved rounding remainder equal the observed change exactly at the approved precision. Total the rows and recompute the total independently. List every line that cannot be placed with its reason and amount, show or count lines outside the approved periods or scope as approved, and reconcile all lines to the input by count, revenue and cost. Keep sources read-only. Report what the inputs cannot prove.
Agent guidanceHow the agent approaches the work, and what it will not do.
Inherit the Intent's repository, platform, Domain, sources and every semantic decision its approved requirements already record; do not ask the user to confirm them again. Profile the sales lines (periods, products, customers, quantities, prices, costs, currencies, units, returns, lines with an amount but no quantity, null costs) before modelling. Use Ask first only for semantics still open, and record each decision as the user's own answer. Keep the periods, benchmark, precision and bound, and the reason lists editable as data where the approved requirements say so. Work out expected results for the agreed cases independently of the models, from the source lines, before trusting them, including the cases an example does not carry, check that each row and the total close, and say which paths the example did not exercise.
Composes
- capturing-requirements
- profiling-source-data
- applying-medallion-data-modelling
- generating-dbt-model
- running-dbt-in-sandbox
- verifying
Asks first
Semantic decisions the Intent cannot supply. Never context Studio already holds.
- If unresolved, which line revenue and cost amounts count toward gross margin (discounts, rebates, freight and fees carried on lines), which records are in scope (intercompany, samples, free goods, test or statistical lines), and are excluded records shown or counted?
- If unresolved, which currency, unit of measure and legal entity are the basis, are lines in another one set aside, bridged separately, or translated, converted or consolidated under approved rates (basis and date) and factors, and is a translation effect its own component?
- If unresolved, which two periods are compared and how are length and calendar handled, which date places a line in a period, is the time basis as known or restated, how does a late posting or changed amount reach published figures, and are lines outside both shown or counted?
- If unresolved, at what grain is the bridge built (product, customer, segment or a pair), how is a product matched across periods (renames, merges, splits, bundles), are roll-ups summed from pairs or recomputed, and how is a customer in several groups shared?
- If unresolved, how are a cell's price and unit cost derived from its lines, at what quantity are they valued (comparison, base or midpoint), where does any remaining interaction go, and how are volume and mix separated (which benchmark, period and cells)?
- If unresolved, how is a cell classified when one side has no sales, a zero quantity, or netted sales and returns, how are new, discontinued and no-quantity amounts shown, and how is a return linked to its sale and valued (own price or the sale's)?
- If unresolved, what precision applies, is each component rounded on its own, where does any rounding remainder go (a named line or an approved component), and what bound is allowed on a rounding-only remainder?
- If unresolved, which lines can't be placed (missing cost, unknown key, malformed amounts), are they set aside and flagged, excluded or does the run fail, what share set aside is tolerable, and should a bridge that cannot close fail, publish flagged or nothing?
Guardrails
- Do not choose the margin definition, basis, periods, grain, valuation quantity, interaction, benchmark, classification, return treatment, precision or exception handling for the user, or take them from the data, a reporting tool or common practice; the user may ask for options or a recommendation.
- Never absorb a difference in an unlabelled line or an unapproved component so the bridge closes; a remainder is shown where the approved rule puts it.
- Never read a missing cost as zero cost or a line with revenue and no cost as full margin, unless the approved rule says so.
- Never fold new, discontinued or no-quantity amounts into volume or mix unless the approved rule says so.
- Never net two currencies, units of measure or entities into one figure unless an approved rule and rate or factor say so.
- Never drop, double count or silently net away a sales line or an amount, or set aside a placeable line under a catch-all reason; lines left out stay visible, shown or counted with their reasons as approved.
- Never treat a renamed, merged or split product as new plus discontinued, or default the compared periods, date basis or length treatment, without approval.
- Stop at the bridge: do not compare with plan, forecast, reprice, make business recommendations, or write commentary or charts.