The assignment: find what the revenue headline conceals
A fictional product business grows sales but earns less product contribution. The file contains five product lines across two periods: three continuing products, one discontinued product and one new product. You need to reconcile both periods and explain which arithmetic components account for the change.
The case defines contribution as revenue less the listed per-unit variable cost. It excludes fixed expenses, tax, returns, capacity changes and other costs. Do not relabel the output as operating profit. The distinction matters because the analysis can explain product economics without supplying a full financial statement.
BLS describes operations-research work as quantitative analysis supporting business decisions, including pricing. This project demonstrates a bounded version of that reasoning. Its decomposition is an explicitly defined algebraic identity, not a claim that every employer uses the same variance-allocation convention.
Data and worked inputs
Sources[1]
Calculate the two endpoints before naming their drivers
For each product and period, revenue equals units multiplied by realized unit price. Contribution equals units multiplied by price less included variable cost. Sum those product values: prior revenue is $168,000 and current revenue is $188,200; prior contribution is $73,500 and current contribution is $69,950.
The revenue increase is about 12.02%, while contribution falls about 4.83%. Do not explain the difference with an unweighted average margin. A product with many units and one with few units do not carry equal financial weight. Keep the product-level calculations so the total can be checked.
NewX has no old-period sales; Legacy has no new-period sales. Their missing prices in the zero-unit periods are deliberately blank. Do not impute a prior NewX price to create a percentage change, or call Legacy’s absent price zero and treat the product’s exit as a price cut. Separate entry and exit from the continuing-product bridge.
Use one exact decomposition and disclose the order
For a continuing product, use three effects: quantity equals the change in units multiplied by the old unit contribution; price equals new units multiplied by the price change; unit cost equals negative new units multiplied by the cost change. Summed across continuing products, these are +$13,100, −$13,800 and −$4,100 respectively.
| Component | Effect |
|---|---|
| Prior contribution | $73,500 |
| Continuing-product quantity | +$13,100 |
| Continuing-product price | −$13,800 |
| Continuing-product unit cost | −$4,100 |
| New product | +$5,250 |
| Discontinued product | −$4,000 |
| Current contribution | $69,950 |
NewX contributes +$5,250 because its current contribution has no prior-period counterpart. Legacy removes $4,000 of prior contribution. The full bridge is $73,500 + $13,100 − $13,800 − $4,100 + $5,250 − $4,000 = $69,950. Every component has a defined base; no residual is needed to make the answer balance.
The chosen order places quantity interactions with price and cost in the new-quantity price and cost effects. Another consistent ordering may allocate interactions differently while reconciling to the same endpoint. This is why two bridges can disagree at line-item level without one being arithmetically wrong. Compare definitions before comparing reported effects.
Split quantity into more units and a different mix
The three continuing products sell 1,700 units in the prior period and 1,860 now. Their old contribution is $69,500, or about $40.88235 per unit on the old sales mix. Holding that average constant, the 160-unit increase contributes about $6,541.18. This is the common-product scale component.
The remaining $6,558.82 of the $13,100 quantity effect is the mix component under the stated method. It captures the change in composition at old product contribution rates. Scale and mix add back exactly to quantity before rounding. Keep full precision in calculations and round only for display.
The mix is favorable even though total contribution falls. Lower realized prices and higher unit costs more than offset the quantity benefit once product entry and exit are included. This is a useful interpretation because it prevents the manager from blaming the entire result on low volume when the defined bridge shows otherwise.
A reconciled bridge does not establish why demand changed
Core sells more units at a lower price. The file alone does not show that the price cut caused the volume increase. A promotion, distribution change, competitor move or season could also matter. The price effect in the bridge is an arithmetic component, not an experimentally estimated price elasticity.
Similarly, the new product’s $5,250 contribution is not automatically incremental to the business. Some customers may have switched from continuing products. The total bridge records what happened across the supplied product lines; estimating what would have happened without NewX requires a different comparison.
Recommend an investigation that follows the evidence: inspect Core and Pro discounting, the sources of unit-cost increases and the expected economics of the product transition. Do not recommend a price increase solely because the price component is negative. Reversing a discount may change volume; the static bridge does not forecast that response.
A commercial explanation with a clear decision boundary
Use the model to make the discussion more specific. The manager should be able to challenge a product, a cost assumption or a proposed follow-up, not merely admire a waterfall chart.
Original fictional commercial memo
Build the bridge and deliberately challenge it
Create a row for each product showing both period totals and the applicable variance components. Mark new and exited lines explicitly. Check that each continuing-product bridge reconciles individually before summing to the business. A total can hide offsetting errors in two products.
Change current units while keeping prices and costs fixed and verify which components respond. Set a continuing product’s current units to zero and move it into the exit treatment rather than divide by zero. Add a new product with an unknown current price but positive units; reject the incomplete input instead of calculating a confident total.
The optional scale-and-mix extension should use only the continuing-product population. Adding NewX to the current-unit denominator but excluding it from the prior mix creates an inconsistent comparison. The supplied reference and answer checker make these population choices explicit; adapt them only after stating the new definition.
Data and worked inputs
Use the optional SQL track to demonstrate the query itself
The optional SQL track exposes product_bridge as an intermediate view, with one row per product and separate columns for each contribution effect. Read that view before the totals. A new product has no prior price to subtract; an exit has no current selling price. CASE expressions preserve those boundaries rather than inventing a comparable price of zero.
The local run_sql.py script imports the CSV into SQLite and runs analysis.sql without a server or external service. Build your own version in a copy of sql-starter.sql, then pass its filename to the runner. Keep an exceptions view for contradictory product records or required inputs that are missing. A malformed number must not quietly become zero through a permissive database cast.
Inspect the common_scale calculation independently of total quantity effect. It uses the prior quantity-weighted contribution of comparable products; mix is the residual under this stated decomposition. Change one current quantity and reconcile the whole bridge again. Being able to explain that update is better evidence of SQL skill than reproducing the supplied total once.
Data and worked inputs
Sources[2]
Explain the result in a pricing or finance interview
A strong project description is: “Built an exact contribution bridge for a synthetic five-product business; separated a 12% revenue increase from a $3,550 contribution decline and isolated entry, exit and mix effects.” That describes a calculation you can defend, not a decision a real company adopted.
Expect questions about why the variance order matters, why absent prices remain blank and what evidence is needed before changing a price. Demonstrate the algebra on one product, then explain the business limits. A student who can do both is showing more than spreadsheet speed.
Make your extension a genuine analytical choice: compare two decomposition orders, add an explicitly modeled return cost or test a proposed price-and-volume scenario. Label the resulting numbers as scenarios. Do not make the project look more advanced by adding unsupported forecasts or charts that obscure the reconciliation.