Supply chain cost analysis by activity and driver

Supply chain cost analysis separates purchasing, inventory, warehouse and transport costs, then connects each amount to the activity that caused it. A useful report shows both total spending and a comparable cost per unit of work. It also distinguishes booked expenses from estimates, because adding them without a label produces a total nobody can reconcile.

Start with the decision and cost boundary

Decide whether you are comparing sites, explaining a monthly variance or checking the cost of serving a customer. The question determines which activities belong in the report and which driver makes a fair comparison. Do not start by dividing every expense by sales.

For a site comparison, define the period, currency and accounting basis in the heading. State whether the report includes merchandise cost, capital charges, corporate overhead and outbound delivery. The worked example below covers supply-chain operating activities plus a separately identified inventory capital charge; it excludes the purchase value of goods.

This distinction matters because acquisition costs may already contain freight. The IRS inventory guidance says the cost of purchased merchandise includes its invoice price, less appropriate discounts, plus transportation and other acquisition charges. That tax guidance illustrates the overlap to check; finance should approve the classification used in your management report.

Assemble the export rows

Bring a ledger export with document ID, posting period, site, account, activity and USD amount. Add purchasing records for order counts, warehouse records for handling activity, freight invoices for shipment costs and inventory balances for the capital-charge calculation. Retain the original document references when assigning activities.

Use one row per cost posting in the cost sheet. Keep activity counts in a separate summary so a freight invoice with several order lines does not multiply the booked amount. A useful layout is:

Keep credits negative and identify accrual reversals. For inventory movement and balances, use the separate method in inventory turnover ratio; it answers how effectively stock is used rather than how an operating expense is allocated.

Work the activity pools in a spreadsheet

An illustrative US distributor compares its West and East sites for September 2026. The following six rows are its reconciled period expense summary, occupying rows 2 through 7 in columns A through D.

Put the selected period in J1 and the selected site in J2. In Excel, sum the site's booked costs with:

=SUMIFS($D$2:$D$7,$A$2:$A$7,$J$1,$B$2:$B$7,$J$2)

For its warehouse pool alone, add the activity criterion:

=SUMIFS($D$2:$D$7,$A$2:$A$7,$J$1,$B$2:$B$7,$J$2,$C$2:$C$7,"Warehouse")

Microsoft documents SUMIFS as summing values that meet multiple criteria. Here those criteria are period, site and activity. Covirage is not affiliated with Microsoft.

Add estimates without hiding them in the ledger

Finance approves an illustrative annual inventory capital charge of 24%, spread evenly across months. It excludes warehouse expense, which is already in the booked costs. Average inventory balances and shipped units come from separate reconciled exports.

Monthly inventory capital charge = Average inventory value × Annual capital-charge rate / 12

Analytical total = Booked operating costs + Separately estimated capital charge

West's capital charge is 200,000 × 24% / 12 = $4,000. Its analytical cost is 66,000 + 4,000 = $70,000, then 70,000 / 6,000 = $11.67 per unit. Dollar rates are rounded to cents after calculation; totals use unrounded amounts.

If the summary places booked cost in B2, average inventory in C2, the annual rate in D2 and shipped units in E2, the formulas are:

=C2*D2/12
=(B2+C2*D2/12)/E2

Prove the total before explaining the difference

The first reconciliation is $66,000 + $46,000 = $112,000, matching the included ledger accounts. The second is $112,000 + $7,000 = $119,000. That bridge explains exactly why the analytical report exceeds booked expense.

The combined unit cost is 119,000 / 10,000 = $11.90. It also equals the unit-weighted site costs, using their unrounded values. Never average the displayed site rates to produce the company rate.

East's higher cost per unit is a starting point for investigation. Compare shipment size, handling steps and service requirements before judging efficiency. For shared costs, document the original pool, its total driver quantity and each site's share. Allocate warehouse costs by handling hours only when hours reasonably explain the work, and check that allocations return to the original pool.

Use analytical methods for supply chain management to choose the next comparison. A proposed site change belongs in supply-chain cost-benefit analysis, where implementation costs and uncertain benefits get their own treatment.

Where it goes wrong

• Double-counted acquisition freight. Freight included in merchandise cost cannot also enter the same product-inclusive total as another full cost line.
• Purchases treated as period consumption. Buying inventory changes an asset balance; it does not automatically describe the cost of goods sold that month.
• An estimate presented as a posting. Keep capital charges and other assumptions in a visible bridge to the ledger.
• A convenient but unrelated driver. Units shipped may compare similar products well and compare bulky and small products badly. Preserve activity-specific rates alongside the headline rate.

Analyze your own supply-chain costs in Covirage

Bring a sample of your own ledger, purchasing, inventory and freight exports; Covirage maps the columns, and its tools compute and check cost comparisons by site, activity and period. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring your files.

See how the same approach supports cost variance analysis.