Field audits of business spreadsheets reviewed by researcher Raymond Panko found significant errors in 49 of 54 spreadsheets, or 91% (Computerworld; the audits date from 1997 to 2000). Those audits were not specific to costing, but the pattern will be familiar to any finance team: a workbook that grows for years, is copied by every department, and ends up deciding which products the plant makes more of.

For a manufacturer the stakes are specific: a wrong product cost means a wrong quote, a wrong product mix, and a profit report that looks healthy until the year-end close says otherwise. This guide covers what goes into manufacturing cost calculation, how standard cost and actual cost differ, where Excel breaks down, and what you need for costs you can trust, with a worked example in which one of two profitable-looking products loses money on every unit.

What does manufacturing cost calculation include?

Manufacturing cost calculation adds up four components for every product you make, which are direct materials, direct labor, manufacturing overhead and scrap, and divides the total by the number of good units produced. Each component hides its own trap.

Direct materials are the quantities in the bill of materials multiplied by their prices. For plants that import polymers, board, steel or ingredients, the right price is the landed cost, which includes freight, customs and handling, and not the supplier’s invoice price. Direct labor is the time in the routing, including setup, multiplied by the labor rate of the work center. Overhead covers everything indirect, such as energy, maintenance, depreciation, supervision and quality control, and it has to be allocated to products through a driver such as machine hours or labor hours. Scrap is the cost of material and processing lost to waste and rework: if you consume the input for 110 units to get 100 good ones, the product carries the cost of all 110.

What is the difference between standard cost and actual cost?

Standard cost is the planned cost per unit, set in advance from the bill of materials, the routing and expected prices, while actual cost is what the unit really cost based on real consumption, real prices and real overhead. Standard costing gives you a stable yardstick for quoting, budgeting and valuing inventory. Actual costing tells you the truth about what happened.

The gap between the two, called a variance, is where the management insight lives. A price variance means you paid more or less than planned for materials. A usage variance means you consumed more or less than planned. An efficiency variance means operations took more or fewer hours than the routing assumed, and an overhead variance means indirect costs were absorbed differently than planned. Most manufacturers run on standard cost day to day and analyze variances every month, which only works if the standards behind them, the bills of materials and routings, are accurate.

Where does Excel go wrong in cost accounting?

Excel goes wrong in manufacturing costing because costs depend on live data from many departments, while a spreadsheet holds a frozen copy of that data. Excel remains excellent for modeling and what-if analysis. The problem starts when it becomes the system of record for product cost.

The first failure is manual updating. Every change in a material price, a labor rate, a bill of materials revision or an overhead budget has to be typed or pasted into the right cell, and each update is another chance for an error that nobody sees, which is consistent with the audit findings above. The second is multiple versions of the file. Sales keeps a copy for quoting, finance keeps another for closing, the plant keeps a third for daily decisions, and when the numbers disagree nobody can say which version priced the last quote. The third is data delay. Actual consumption, scrap and machine hours reach finance days or weeks after they happen, so the cost report describes last month’s plant and the decision it informs is already made.

How do you calculate the cost of one product? A worked example

The cost of one product is its direct materials, direct labor, allocated overhead and scrap loss added together and divided by the good units produced. In practice this takes five steps.
First, multiply each bill of materials quantity by its landed price.
Second, multiply the routing hours, setup included, by the work center rate.
Third, divide the month’s overhead by the total driver units, such as machine hours, to get an overhead rate, then apply it to each product by the drivers it actually uses.
Fourth, add the cost of scrap and yield loss. Fifth, compare the result with the selling price.

The following figures are illustrative, not a client case. They are in Saudi riyals, but the logic is identical in any currency.
Imagine a plant that makes 4,000 units a month of Product A, sold at SAR 100, and 4,000 units of Product B, sold at SAR 95, with monthly overhead of SAR 240,000. The spreadsheet spreads overhead evenly, so every unit carries SAR 30 (240,000 ÷ 8,000). Product A shows material of SAR 50, labor of SAR 10 and overhead of SAR 30, a cost of SAR 90 and a margin of SAR 10, or 10%. Product B shows material of SAR 40, labor of SAR 12 and overhead of SAR 30, a cost of SAR 82 and a margin of SAR 13, or about 14%. On paper, Product B is the better product, and the plant should make more of it.
Now cost them by what they actually consume. Product A uses 0.2 machine hours per unit and Product B uses 0.8, so the plant runs 4,000 machine hours a month and overhead is SAR 60 per machine hour (240,000 ÷ 4,000). That puts SAR 12 of overhead on A and SAR 48 on B. Product B also scraps about 10% of its material, which raises its material cost per good unit to SAR 44. Product A now costs SAR 72 and earns SAR 28 per unit, a 28% margin. Product B costs SAR 104 against a price of SAR 95, which is a loss of SAR 9 on every unit.
Over a month, Product A earns SAR 112,000 and Product B loses SAR 36,000, a real profit of SAR 76,000 against the SAR 92,000 the spreadsheet reported. The SAR 16,000 difference is exactly the scrap the spreadsheet never saw, and the decision to push Product B was backwards. Two quiet assumptions caused it: a flat overhead rate and a theoretical material cost.
Not sure your spreadsheet would catch a product like Product B? Contact us to book a consultation or a demo and see which of your products is really making money. Email info@mazayasolutions.com or visit mazayasolutions.com.

What do you need for accurate manufacturing costing?

Accurate costing needs three foundations: accurate bills of materials, accurate routings, and a live link between inventory and finance. None of them is a software feature on its own, and each depends on discipline.
The bill of materials must reflect the current revision, with realistic yield and scrap factors, because a theoretical recipe produces a theoretical cost. The routing must hold realistic run and setup times and the right work center rates, which usually means measuring the floor and not copying last year’s standard. And every receipt, material issue and production report must post to the ledger as it happens, so cost is built from what was actually consumed and not from a monthly estimate. Someone also has to own this master data, because costing is only as reliable as the least maintained record in it.

Which profitability indicators should you track?

Track five measures by product, not only by plant: gross margin, contribution margin, cost variances, scrap or yield rate, and cost per good unit over time. Gross margin shows what is left after full manufacturing cost, and contribution margin shows what each unit adds after variable costs only, which is the number that matters when deciding what to make with limited capacity. Cost variances, split into price, usage and efficiency, tell you why actual cost drifted from standard. Scrap or yield rate is the early-warning signal for the kind of hidden loss in the example above. Cost per good unit, tracked month after month, shows whether the plant is getting cheaper to run or merely busier.

What did Bel Fuse change?

Bel Fuse, a global manufacturer of electronic circuit components, moved from a highly customized, expensive-to-maintain ERP to Dynamics 365 Finance and Supply Chain Management to unify finance and operations across its sites. It reports saving nearly USD 2 million per year in IT service and support fees and says everyone from decision makers to factory floor managers now works in the same system (Microsoft Customer Stories, July 15, 2020).
Its relevance is the foundation: one system of record in place of separate systems per business, which is the precondition for comparable product costs.

How does Dynamics 365 Finance & Operations support accurate costing?

Dynamics 365 Finance and Supply Chain Management, the applications many still call Finance & Operations (F&O), keeps bills of materials, routings, inventory and the ledger in one data model, so cost is calculated from the same records that run production. Its costing versions hold standard cost records for items and manufacturing processes, BOM calculations roll those standards up for manufactured items, and production orders are costed and inventory is valued from the active cost records (Microsoft Learn).
Standard cost variances, including quantity, price and lot size variances, can be posted to ledger accounts and analyzed by cost group, so the gap between plan and reality is visible inside the financial close instead of in a side file.

Frequently asked questions

Is Excel enough for product costing?
Excel is enough for modeling and for small, simple, single-site operations with few products and stable prices. It stops being safe when costs depend on frequent bill of materials changes, shared overhead, several people editing copies, or data that arrives late from the floor.
When should a manufacturer move from Excel to ERP costing?
Move when quotes are questioned because numbers differ between departments, when month-end close takes long because costs are reconciled by hand, or when you add products, shifts or sites that share overhead. Those are signs that the spreadsheet has become the bottleneck.

How do I calculate cost per unit when scrap is high?
Divide the total cost of all units started, good and scrapped, by the number of good units only. For example, if 110 units of input are consumed to produce 100 good units, the cost of all 110 must be carried by the 100 good ones.

What is each of your products really costing you?
A wrong product cost does not announce itself. It shows up as a mix that looks fine and a margin that never quite matches the plan. Contact us to book a consultation or a demo. We will review how your costs are calculated today, test it against the three foundations above, and show how an integrated ERP would surface the gaps earlier. Reach us at info@mazayasolutions.com, or at mazayasolutions.com.

About Mazaya Integrated Solutions

Mazaya Integrated Solutions is a Microsoft Solutions Partner with more than 20 years in the technology market and a growing footprint across the Gulf. Founded in 2005 as a software development company, Mazaya moved its headquarters to Kuwait in 2014 to expand regionally, and in 2023 relocated it to Riyadh as part of a company-wide restructuring. Its end-to-end implementation and consulting services center on Microsoft Dynamics 365, including Finance & Operations for financial and supply chain management, Business Central, CRM and Contact Center, alongside AI and robotic process automation, cybersecurity, risk and compliance, and cloud and infrastructure solutions.

Mazaya works with Saudi ministries and government authorities as well as companies in the food, financial, real estate, healthcare and education sectors across Saudi Arabia and Kuwait. In manufacturing, its Dynamics 365 Finance & Operations work includes an ongoing implementation for Azyan Company for Boxes & Carton, a packaging manufacturer in Saudi Arabia. Mazaya also holds ISO 9001, 27001, 45001, 14001, 20000-1, 22301 and 42001 certifications. With offices in Riyadh and Kuwait, the team helps decision-makers choose the systems that fit their operations and turn them into measurable results.