Manufacturing Cost Calculation Excel Template (Material, Labor, Overhead, Scrap)
This manufacturing cost calculation template turns a few inputs per part into a full unit cost: material grossed up for scrap, direct labor, setup spread across the batch, and overhead absorbed per labor hour. Edit the sample rows, add your own parts and export to Excel.
| Product / part | Batch quantity (good units) | Material per good unit (before scrap) | Scrap rate (%) | Run time per unit (min) | Setup time per batch (min) | Direct labor rate (per hr) | Overhead rate (per labor hr) | Material incl. scrap | Direct labor per unit | Overhead per unit | Total cost per unit | Batch cost | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
$4 | $3 | $5 | $12 | $2,990 | |||||||||
$8 | $6 | $9 | $23 | $2,296 | |||||||||
$16 | $12 | $20 | $48 | $2,857 | |||||||||
$2 | $1 | $2 | $6 | $2,295 | |||||||||
$10 | $3 | $3 | $15 | $7,442 |
Scrap is applied to material only, as a gross-up: material divided by (1 minus scrap rate). Overhead is absorbed on direct labor hours. If your plant absorbs overhead on machine hours, enter the machine-hour rate in the overhead column and the machine time in run time.
Costing worksheet wired to your ERP
We can show how routings, BOMs and rates from your ERP could feed this model. Optional, the worksheet works without it.
Optional. The tool and every export work without this. No spam.
The formulas behind the template
Unit cost is material plus conversion cost. Material including scrap is material divided by one minus the scrap rate, so 4 percent scrap on 3.80 of material gives 3.96. Labor hours per unit are run minutes plus setup minutes divided by batch quantity, all over 60. Direct labor is those hours times the labor rate; overhead is the same hours times the overhead rate. Total cost per unit is the sum, and batch cost is unit cost times batch quantity.
- Material incl. scrap = material / (1 - scrap rate)
- Labor hours per unit = (run min + setup min / batch qty) / 60
- Labor = hours x labor rate; Overhead = hours x overhead rate
- Unit cost = material incl. scrap + labor + overhead
Getting the overhead rate
Overhead absorption rate is budgeted overhead for the period divided by budgeted direct labor hours (or machine hours). If indirect labor, depreciation, utilities, rent and maintenance come to 1.2 million for a year and the plant plans 28,000 direct hours, the rate is about 43 per hour. Use the same driver in your ERP cost roll-up so the spreadsheet and the system agree. Review the rate at least twice a year, because volume swings change it sharply.
Reading the result and checking it
Look at the weighted average at the bottom and at the highest unit cost row. Compare each unit cost to the standard cost in your ERP; a gap of more than a few percent usually means a stale routing time, an old material price or a different scrap assumption. Short batches are dominated by setup: change batch quantity to see how unit cost falls as the batch grows, which is the basis for price breaks.
Common mistakes
Frequent errors include leaving setup out, applying scrap to labor as well as material, using a loaded labor rate and then adding overhead again, and mixing machine-hour and labor-hour rates in the same sheet. Also remember that this is production cost: add selling, general and administrative cost and margin separately when you build a quote.
Frequently Asked Questions
What is the formula for manufacturing cost per unit?
Cost per unit equals direct material plus direct labor plus manufacturing overhead, divided by good units produced. Include scrap by grossing up material, and spread setup cost across the batch. This template does exactly that for each row.
How do I include scrap in the cost?
Divide the material cost per good unit by one minus the scrap rate. A 5 percent scrap rate raises material by about 5.3 percent, not 5 percent, because you must start more material to get one good unit.
Should overhead be absorbed on labor hours or machine hours?
Use the driver that best explains your overhead. Automated machine shops usually use machine hours; assembly and manual work use labor hours. Keep the choice consistent with your ERP costing so reports reconcile.
Can I use this to price a quote?
It gives production cost, which is the floor. Add SG&A, tooling amortization, freight, risk and your target margin on top. The worksheet is a starting point and does not replace your standard costing process.
If you want these costs pulled from your ERP's routings, BOMs and rates instead of typed in, Netray can build a private cost assistant that reads your data on-prem.
Related Tools
Manufacturing Cost Calculator (Per Unit, With Formula)
Calculate total manufacturing cost per unit from materials (with scrap), labor, setup and overhead, see the price at your target margin, and chart cost vs batch size.
Costing & FinanceManufacturing Cost Accounting Formulas Reference
A searchable lookup of the cost accounting formulas used in manufacturing: cost flows, overhead absorption, variances, break-even and pricing maths.
Costing & FinanceCost of Goods Manufactured (COGM) Calculator
Build the schedule of cost of goods manufactured from raw material, direct labor, overhead and work in process balances, and get the unit cost.
Costing & FinanceCNC Machining Cost Calculator
Build a per part CNC price from setup hours, cycle time, shop rate, material, tooling, outside processing, scrap and margin.
More Costing & Finance tools
See all- SAP QM Inspection Effort and Cost Calculator
- SAP Standard Cost Estimate BOM Rollup Calculator
- Legacy ERP Retirement Cost and Payback Calculator
- JD Edwards World Month-End Close Procedure Template
- Distributor Customer Credit Application Template
- Distributor Customer Profitability Worksheet
- Distributor GMROI and Inventory Turns Calculator
- Vendor Early Pay Discount Worksheet
- SyteLine Cost Rollup Variance Calculator
Keep exploring
Free tools
- Manufacturing Cost Estimation Excel TemplateAn editable cost estimate worksheet that builds a per-part manufacturing cost from material, labor, machine time, setup, outside processing, overhead and margin, then exports to Excel.
- Manufacturing Overhead Rate CalculatorCalculate a predetermined manufacturing overhead rate from your indirect costs and an allocation base, then apply it to a job to see overhead cost.
- Excel Manufacturing Production Planning and Tracking WorksheetAn editable production worksheet for small manufacturers. Enter planned and actual quantities per order and work center, and it calculates hours, yield and attainment, then exports to Excel.
ERP fixes and how-tos
- How to calculate item cost prices in Infor LNRun item cost price calculation in Infor LN: cost components, simulation vs update mode, BOM/routing roll-up, and how the result reaches inventory valuation.
- How to Export from SYSPRO to ExcelFour ways to get SYSPRO data into Excel: export from a browse window, send a report to Excel, use Office Integration, or pull live data with SQL or ODBC.
- Infor OS Add-In for Microsoft Excel: Install, Sign In and UseHow to install and use the Infor add-in for Microsoft Excel with Infor OS to read ERP data into a worksheet, what you need first and when to use other methods.
Runs with your ERP
- Vertex with Infor LN: sales and use tax and VAT calculationHow Vertex indirect tax software works with Infor LN, SyteLine, M3 and other Infor ERPs for sales and use tax and VAT, with setup steps, pitfalls and AI use.
- Vertex O Series and Infor CloudSuite: sales tax and VAT calculationHow Vertex O Series indirect tax is configured in Infor CloudSuite for sales, use and VAT calculation, with data flows, setup, pitfalls and a private AI layer.
Guides
- Rubber Compound Cost and Scrap Calculation for MoldersRubber compound cost and scrap calculation: convert part volume to shot weight, add flash, runners and start-up scrap, and track yield variance in your ERP.
- Casting Yield and Scrap Cost: Getting Them Right in Your QuoteHow casting yield, melt loss and scrap rate feed cost per good part, and how to measure them from real production data so quotes stop under-pricing castings.
- How to Configure Tax Calculation in SyteLineComplete guide to configuring tax calculation in Infor SyteLine. Set up tax codes, tax rates, tax exemptions, and integration with external tax engines for manufacturing.