Multi Level BOM Cost Calculator Excel, Semi Finished Goods, Process Yield, Food Production Costing

Enter your raw-material prices, processing stages and recipes – Excel rolls every stage, yield and loss into the real cost of one unit, the margin of every product and the price you need for your target margin.

✔ Semi-finished goods up to 5 levels, with batch recipes
✔ Process yield and losses raise the cost automatically
✔ Cost and margin per product: OK · LOW MARGIN · LOSS
✔ Instant download: BLANK + EXAMPLE · Quick Start PDF
✔ Built by a food production manager · one-time purchase, no subscription, no macros

▶ Requires Microsoft Excel 2016 or newer on a Windows or Mac computer. Not for Google Sheets, Apple Numbers or mobile apps.

500 kg of raw potatoes become 400 kg peeled, then 370 kg cooked and diced, then potato salad mix, then a 500 g cup. Every stage loses weight and adds labour, machine time and energy – and that is where the real cost hides. One-level calculators cannot see it. This Multi-Level Product Costing template can: semi-finished goods with their own batch recipe, yield and processing cost roll up automatically through up to 5 levels into every finished product. Change the raw-material price or a yield, and every stage and product updates.

Built by a production manager of a salad and ready-food plant.

WHAT IT DOES
• Semi-finished goods in up to 5 levels: each made from materials and lower-level items
• Batch recipe per semi-finished item with good output per batch – yield % calculated
• Processing cost per batch: labour hours, machine hours and other stage costs (steam, energy, water)
• Cost per kg / per unit of every processing stage, with its materials share
• Product cost per unit: purchased materials, semi-finished materials, semi-finished processing, packaging, packing-line labour and machine, setup, overhead, scrap
• Margin, markup, price for your target margin, break-even and profit at forecast
• Status for every product: OK · LOW MARGIN · LOSS · NO PRICE · INCOMPLETE · CHECK
• Drop-down lists with names for every component, parent and product – codes are also accepted
• Level rule: an item may only use LOWER levels – no circular references, LEVEL ERROR flagged
• Problems travel up the chain: a product that uses a semi-finished item with a problem shows CHECK RECIPE instead of a wrong cost
• Printable product card: breakdown with chart, recipe with the yield of each stage, what-if test (material prices %, labour rate %, new price)

WHAT'S INCLUDED (ZIP)
• SmartFactoryTools_Multi_Level_Product_Costing_v1.0_BLANK.xlsx – empty, ready for your plant
• SmartFactoryTools_Multi_Level_Product_Costing_v1.0_EXAMPLE.xlsx – fictional deli-salad plant: 24 materials, 8 semi-finished items in 3 levels, 6 products
• Quick_Start_Guide.pdf – 2 pages: steps, formulas, statuses, top questions
Workbook sheets: START_HERE · SETUP · MATERIALS · SEMI_FINISHED · SEMI_RECIPE · PRODUCTS · PRODUCT_RECIPE · COST_SUMMARY · PRODUCT_CARD · FAQ · LICENSE

HOW IT WORKS
1. MATERIALS: everything you buy, with price per kg, L or piece.
2. SEMI_FINISHED: each processing stage – level, good output per batch, labour and machine hours, other cost per batch.
3. SEMI_RECIPE: what goes into one batch – pick components from the lists.
4. PRODUCTS and PRODUCT_RECIPE: selling price, packing line, scrap and what goes into one unit. Read COST_SUMMARY and print the PRODUCT_CARD.

WHO IT'S FOR
Food and ready-meal producers, salad and deli plants, sauce makers, bakeries, meat and dairy processors, cosmetics and small process manufacturers – production managers, plant managers, food technologists and cost controllers.

LIMITS
• 200 materials, 60 semi-finished items in 5 levels, 600 batch-recipe lines, 40 products, 800 product-recipe lines
• No unit conversion: enter quantities in the unit each material is priced in (kg, L, pcs)
• No by-product / co-product cost allocation
• A management cost estimate – not accounting, stock valuation or tax. No currency conversion
• No macros. Sheets are protected without a password to prevent accidental damage

FAQ
Q: Does it work in Google Sheets?
A: No. It is built and tested for Microsoft Excel 2016 or newer on Windows or Mac desktop.
Q: Where do I enter the yield?
A: As the good output per batch: 500 kg raw in, 400 kg peeled out → output 400. Yield % is calculated for you.
Q: What is a level?
A: Level 1 items are made only from materials; level 2 may use level 1, and so on up to level 5. Products may use any level.
Q: Overhead % and fixed costs – both?
A: Overhead % goes into the unit cost. Fixed costs per month are used only for break-even and profit – do not enter the same costs twice.
Q: We only pack or assemble, no processing stages.
A: Then the simpler Manufacturing Cost per Unit Calculator by SmartFactoryTools is the right tool.

Related SmartFactoryTools tools: Manufacturing Cost per Unit Calculator · Takt Time & Line Balancing Calculator · NCR Log & Cost Tracker · OEE Dashboard & Downtime Cost Tracker.

DIGITAL DOWNLOAD
This is a digital product – nothing is shipped. After purchase, download the ZIP from Etsy (Purchases and Reviews) on a computer, unzip it and open the files in Excel.

LICENSE
For use within your own company or organisation, at any number of sites. Reselling, sharing or uploading the template is not permitted. © SmartFactoryTools