Value chain analytics: find where margin is lost

Value chain analytics shows how purchasing, production, delivery and service costs reduce the revenue left from a defined group of sales. Map each cost once, compare stages against an activity-adjusted baseline, and prove that the result ties to the accounts. The largest unfavorable difference gives you a place to investigate.

Define the sales and cost boundary

Choose a period and a product family, customer group or business unit with identifiable revenue. Export net sales, material consumption, production costs, freight and service costs. Add the activity files that explain each stage: input units, production hours, shipments and service tickets.

Keep the period, currency, source document ID, stage, amount and inclusion status on each cost row. Finance owns the definitions and reconciliation. Purchasing, operations, logistics and service managers confirm the activity volumes and explain exceptions within their stages.

Use costs recognized for the chosen sales and period. Supplier payments or receipts can include inventory still on hand; subtracting them from current revenue can create a false margin decline. The IRS inventory guidance distinguishes inventory costs and describes costs included in purchased and manufactured goods. Your management stage mapping must also respect the company's accounting classifications.

For the related operational questions, see supply chain analytics, predictive supply chain analytics and supply chain risk analytics. This cost bridge measures recorded economics rather than future demand or exposure.

Map each cost once

A US manufacturer reviews September 2026 sales for one component family. Its revenue worksheet contains the following row, after returns and discounts:

Purchasing contains the consumed input cost; production contains conversion costs excluding those inputs. Delivery contains outbound freight and handling. Service contains the period's support costs attributable to this family. The different activity units are deliberate: they explain costs within stages and must not be added together.

Use a cost mapping register to distinguish included stage rows from unassigned corporate costs and excluded balance-sheet movements. If a source already includes material cost in production, remove that material from one stage before summing. Preserve the original amount and the mapping decision.

Calculate a baseline at actual activity volumes

Suppose approved baseline rates are available for each stage. Apply those rates to actual activity volumes, rather than comparing current costs with a budget based on different volumes.

Allowed stage cost = Actual activity volume × Baseline cost per activity unit

Unfavorable cost variance = Actual stage cost - Allowed stage cost

On a worksheet called Stages, use these columns, with stages in rows 2:5. Amounts are exact USD; unit rates are USD per unit of the activity named.

Enter the following Excel or Google Sheets formulas and fill down:

D2 = B2*C2
F2 = IF(B2=0,"",E2/B2)
G2 = E2-D2

The blank rate for zero activity requires review; it does not mean zero cost. To aggregate source costs, suppose CostRows has document ID in A, period in B, stage in C, inclusion status in D and cost in E. Put the chosen period in K1 and match the stage labels to column A:

=SUMIFS(CostRows!$E$2:$E$5000,CostRows!$B$2:$B$5000,$K$1,CostRows!$C$2:$C$5000,$A2,CostRows!$D$2:$D$5000,"Included")

Google's SUMIFS documentation describes the criteria-based sum. SUMPRODUCT provides a separate check of the allowed total:

=SUMPRODUCT(B2:B5,C2:C5)

That check returns 460,000. Covirage is not affiliated with Microsoft or Google.

Build the revenue-to-contribution bridge

Subtract actual costs from net revenue in stage order. The running remainder is a management subtotal; it is not revenue separately earned by each stage.

With net revenue in Revenue!C2, put the running remainder in H:

H2 = Revenue!$C$2-SUM($E$2:E2)

Fill down through H5. Actual contribution after stage costs is $110,000, or 18.33% of revenue. The activity-adjusted baseline is $140,000, or 23.33%. Percentages are rounded to two decimals; the $30,000 unfavorable cost variance reduces contribution by 5 percentage points.

The purchasing and production differences are each $12,000. Check input price and purchasing mix for the former, and labor or overhead rates within recorded hours for the latter. Delivery adds $8,000; service offsets $2,000. Lower service cost needs a check for delayed invoices or work shifted into another period.

Reconcile before acting

The stage cost total is $490,000. Suppose the same ledger scope also contains $60,000 of unassigned corporate operating costs: total operating costs must then be $550,000. The final proof is $600,000 - $490,000 - $60,000 = $50,000 of operating income.

Tie the $600,000 to net sales and the $550,000 to the scoped ledger accounts. Keep the unassigned amount visible. A plausible stage subtotal is insufficient if source rows remain unexplained or an account appears twice.

Where it goes wrong

• Purchases treated as consumption. Inventory acquired for a later sale belongs outside the current sales cost bridge.
• Double-counted materials. Production totals may already contain the purchasing amount.
• Mixed activity units. Cost per shipment cannot be compared directly with cost per production hour.
• A baseline at another volume. Recalculate allowed costs using actual activity before reading a variance.
• Unsupported allocations. A convenient percentage can change stage rankings without changing company profit.

Compare your own stage costs in Covirage

Bring a sample of your own sales, stage cost and activity files; Covirage maps the columns, and its tools compute the comparisons and check the supplied totals. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring your files.

See cost variance analysis with Covirage for the supporting workflow.