How to Calculate Product Cost for Manufactured Products in Excel
This practical guide shows you how to calculate the true unit cost of manufactured products in Excel using the our unique product costing template. You will learn the role of Bills of Material (BOM), how costs roll up across levels, and how to use what‑if costing to protect your margins.
What is a Bill of Materials (BOM)?
A Bill of Materials (BOM) is the definitive list of components and sub‑assemblies required to manufacture a finished product. Each line identifies a stock code and the quantity per needed to make one unit. In a multi‑level BOM, sub‑assemblies are themselves manufactured items with their own component lists. The template rolls up costs across all levels so the finished‑goods cost includes the cost of every underlying component and conversion activity.
Tip: Use clear, consistent stock codes and units of measure. That makes requirements planning and cost roll‑ups simpler and less error‑prone.
How the template calculates product costs
1) Maintain stock codes & prices
Create stock codes for all bought‑in components and manufactured products. Enter standard purchase prices and optional what‑if prices for quick sensitivity checks.
2) Build BOMs (supports multiple levels)
For each manufactured item, add its components and quantities. When a component is a manufactured sub‑assembly, the template rolls that cost up automatically.
3) Apply labour & overhead drivers
Add labout and overheads as stock codes in the BOM structure and use the cost drivers as the units of measure.
4) Instant cost roll‑ups
Unit costs are calculated automatically for every manufactured product — including all lower‑level components. What‑if prices flow through the entire BOM for quick margin impact analysis.
5) Margin & price review
Use the review section to compare current vs what‑if unit costs, set selling prices, and see gross profit per product.
6) Requirements planning
Enter a quantity forecast to estimate component usage and purchasing requirements — helpful for planning and cash‑flow.
Note: All calculations run on formulas — no code needed for the core logic.
What makes this template unique
Unlimited products
Create stock codes for bought‑in and manufactured items.
Multi‑level BOMs
Support for multiple levels of sub‑assemblies with automatic roll‑ups.
What‑if costing
Model price changes or waste assumptions without touching live data.
Requirements planning
Quantity forecasts generate component usage and purchasing estimates.
Margin analysis
See gross profit at both current and what‑if costs.
Frequently Asked Questions
How are overheads handled?
You can allocate overheads using drivers that make sense for your operation — e.g., per‑hour, per‑unit, machine time, or a percentage basis. Choose one approach and apply it consistently.
Does this work for multi‑pack or batch sizes?
Yes. The workbook calculates totals per batch and converts them to a per‑unit basis for margin comparisons.
Can I switch between current and what‑if costs?
Yes. Maintain alternate prices for components and the workbook will instantly recalculate unit costs and margins using the what‑if set.
