Cost per unit Excel template

A cost per unit Excel template divides each product's included costs by its matching completed output, with separate controls for both inputs. Keep the source cost rows, production quantities and allocation schedule visible so a reviewer can rebuild every product result.

This is a workbook layout you can reproduce, rather than a downloadable file. For the underlying metric definition and choice of denominator, use how to calculate cost per unit.

Set up the input sheets before the summary

Use four sheets: Costs, Output, Allocation and Summary. Put the reporting period and cost scope above the summary. The example uses October 2026, USD and completed metal brackets at a US manufacturer. Included costs are materials, direct production labor and allocated factory support; selling costs are outside this management comparison.

The manufacturer reports no unfinished production in this example. If unfinished work is material, resolve its cost and completion basis before using a completed-unit denominator; cost per equivalent unit covers that separate problem.

Build these input columns:

Store period as a real month-start date, formatted as a month. Use the same product codes across every sheet. Keep source references unique at their true posting level; an invoice number alone may cover several legitimate cost lines.

Format the source ranges as tables so their column boundaries and headers are clear. Microsoft's table instructions describe creating and formatting a table from a selected range. Covirage is not affiliated with Microsoft.

Fill the allocation schedule once

For this management report, finance approves a factory-support pool of $13,000 allocated using machine hours. Record the pool separately from the allocated product amounts so the original expense and its distribution are not both included in the summary.

The rate is 13,000 / 1,300 = $10.00 per machine hour. Each allocated amount is hours multiplied by that rate. The schedule must satisfy 6,000 + 4,000 + 3,000 = $13,000, leaving no unallocated difference.

This is a documented example assumption, not a universal allocation policy. Keep any finance-approved idle-capacity or other excluded amount in a separate reconciliation instead of forcing it into product costs.

Build the product summary

Use columns A through G in the order below, starting the first product on row 2. Materials and labor come from Costs; support comes from Allocation; completed pieces come from Output.

In F2 and G2, enter these formulas and copy down:

=SUM(C2:E2)
=IF(B2>0,F2/B2,NA())

Small brackets cost (18,000 + 9,000 + 6,000) / 3,000 = $11.00 each. Medium brackets cost $12.00 and large brackets $19.00. Display unit costs to two decimal places while retaining full precision in the cells.

To populate C2 from Costs, assume A is period, B product, C category and D amount. Put the selected month-start date in J1:

=SUMIFS(Costs!$D:$D,Costs!$A:$A,$J$1,Costs!$B:$B,$A2,Costs!$C:$C,"Materials")

Microsoft documents SUMIFS for summing values that meet multiple criteria. Use the same period and product criteria for direct labor and for the separately aggregated output and allocation schedules. A missing product mapping must become an exception, rather than disappearing from the summary.

Add the checks beside the result

First, reconcile the cost categories: $44,000 of materials + $19,000 of labor + $13,000 of support = $76,000. Then reconcile completed output independently: 3,000 + 2,000 + 1,000 = 6,000 pieces. Both totals must match their respective source schedules for October.

The combined cost per piece divides totals:

=SUM(F2:F4)/SUM(B2:B4)

That is 76,000 / 6,000 = $12.67, rounded. Treat it as a product-mix summary, since a large bracket and a small bracket are different outputs. The individual product costs remain the operating comparison.

Rebuild included cost with the unrounded unit-cost cells:

=SUMPRODUCT(B2:B4,G2:G4)-SUM(F2:F4)

This difference should be zero. A difference caused by manually replacing formulas with rounded values is a workbook error, not a cost variance.

Test what a volume change does

Duplicate the summary into a clearly labeled scenario area. Change only small-bracket output from 3,000 to 2,700 pieces while leaving its included cost at $33,000. Its cost becomes 33,000 / 2,700 = $12.22 per piece, rounded, compared with $11.00 originally.

Using unrounded values, the increase is ((33,000 / 2,700) / 11) - 1 = 11.1%, rounded to one decimal place. No spending increased in that scenario; the denominator decreased. It does not tell you the cost of making one extra bracket, which belongs in the separate marginal cost per unit calculation.

Keep the original actuals intact. A scenario is an assumption change and should never overwrite the production report used to prove the original result.

Where it goes wrong

• Repeated output. Copying completed units onto every cost-category row multiplies the denominator when those rows are summed. Aggregate costs and output separately.
• Duplicated support. Counting the original support pool alongside its product allocations includes the same cost twice.
• Mixed periods. A late production posting and an earlier cost extraction can make one month's cost look unusually high. Compare cutoff dates before interpreting it.
• Different units. Pieces, cartons and pounds need explicit conversion rules. A blended denominator without those rules has no stable meaning.
• Zero output hidden as zero cost. Replacing division errors with zero makes an unresolved product look inexpensive. Keep it on the exception list.

Compare your own product costs in Covirage

Bring a sample of your own cost workbook, output report and documented allocation schedule to Covirage, which maps the columns. Its tools compute and check the product comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and set up your business with a sample of your own data.

See cost variance analysis for the wider review of changes in your operating costs.