Optimizing your inventory format in Excel for production

Start with three linked Excel Tables

A flat inventory list answers how much you have. A production workbook must also answer what that stock can become. For a bakery, flour belongs in Raw Materials, the flour required by each loaf belongs in the BOM, and baked loaves ready to sell belong in Finished Goods.

Create three sheets and turn each data range into an Excel Table using Insert > Table. Name the tables Raw_Materials, BOM and Finished_Goods. Structured references adjust as table data changes, and calculated columns can fill formulas down. Separate tabs make the relationships easier to inspect, but they do not prevent errors by themselves.

The format below assumes a single-level BOM, positive recipe quantities, one row per product and material combination, and a consistent currency. A starter or other intermediate recipe needs its raw inputs expanded before you calculate capacity.

Tab 1: raw materials and available stock

Record ingredients, jars, boxes and labels as separate materials. Use a stable Material_SKU as the lookup key, even if the item's display name changes. This workbook identifier does not mean you need a sales-channel SKU to use Makeable.

A negative available balance signals existing commitments already exceed stock. Investigate it rather than hiding it. Reserved_Stock must exclude materials already consumed by completed production runs, or you will subtract them twice. See the material shortage calculation guide for reservation examples.

Tab 2: recipe and bill of materials

Each BOM row connects one finished product to one raw material. Write all quantities per finished unit, not a mixture of batches and individual items. A batch requiring 5,000 g of flour and yielding 10 loaves needs 500 g per loaf. Include packaging lines when you want material cost to include packaging.

These formulas require Excel with XLOOKUP support, such as Microsoft 365 or Excel 2021 and later. XLOOKUP is not available in Excel 2016 or 2019; use INDEX with MATCH there. A missing component should show a lookup error for you to correct, not silently turn into zero cost.

Do not repeat a component within the same product: combine its quantities first. Two flour lines checked independently would each see the same stock and could overstate capacity. Our bill of materials guide explains the recipe structure.

Tab 3: finished goods, cost and capacity

Keep Units_On_Shelf separate from Makeable_Units. The first is already produced; the second is possible new production using available materials. Use Finished_SKU, Product_Name, Units_On_Shelf, BOM_Unit_Cost, Makeable_Units and Retail_Price as your main columns.

Format Material_Margin as a percentage. This is a margin based on the BOM's material cost, not net profit. Labour, overhead, payment fees and any packaging omitted from the BOM still need accounting for. MINIFS requires a supported Excel version, including Excel 2019 and later or Microsoft 365.

Capacity is a material limit, not a guaranteed delivery date. Oven time, curing, staff and equipment can lower actual output. Capacities for products sharing ingredients are separate scenarios, not quantities you can add together. Read production capacity planning for the wider constraints.

Standardize purchase and production units

Choose one practical stock unit per material and use it everywhere in calculations. A 25 kg flour bag contains 25,000 g. Receiving two bags adds 50,000 g, not 2 or 50. If one bag costs 40 in your chosen currency, its cost per gram is 40 divided by 25,000, or 0.0016.

The diagram's factor of 1,000 applies to one kilogram. A whole 25 kg bag uses 25,000 grams per bag. Always label what one purchase unit means.

Mass and volume are different. Do not convert grams to millilitres without the material's density, and distinguish US from imperial gallons. Drops vary with liquid and dropper, so our drops to mL guide gives estimates and calibration guidance, not a universal production conversion.

Worked example: how many loaves can you make?

Consider an illustrative recipe needing 500 g flour, 350 mL water and one bag per loaf. After other commitments, the bakery has 8,000 g flour, 7,000 mL water and 12 bags. This simplified example assumes no extra waste and no other recipe inputs.

Bags limit the run to 12 packaged loaves. An order for 15 needs three additional bags; flour and water are covered. If flour has a 5% extra-usage allowance, its effective requirement becomes 525 g per loaf, supporting 15 whole loaves. Bags still set the overall limit at 12.

Your real BOM must include every required input, including starter, salt and labels where applicable. This is an inventory calculation, not a bread recipe. Check your own quantities in the free material shortage calculator.

Record receipts, production and sales once

The three tables are the workbook's core, not a complete audit trail. For day-to-day use, add a movement log with date, reference, item key, movement type and signed quantity. Supplier receipts increase raw stock. Production consumes raw materials and adds finished goods. Shipping finished goods reduces finished stock, not the raw ingredients a second time.

When an accepted order is produced, release its outstanding raw-material reservation as you deduct the consumed stock. If an order uses finished goods already on the shelf, reserve those goods instead of reserving raw inputs for the same units.

Reconcile physical counts with recorded balances regularly. Protect calculated columns and keep a recoverable workbook history. An Excel Table does not automatically import orders, log production or perform stock deductions.

Check inputs before trusting the formulas

Use data validation lists for identifiers and units, and restrict recipe quantities to positive numbers. Validation helps guide entry, but it is not a substitute for checking pasted data and reviewing errors.

Before confirming a batch, check for duplicate material keys, unknown BOM components, duplicate product-component pairs, mismatched units and missing recipes. Review negative stock, nonpositive quantities and lookup errors. Keep enough decimal precision in unit costs; rounding a tiny cost per gram too early can distort the batch total.

The formulas shown use English function names and commas. Some Excel language and regional settings use translated function names or semicolon separators. Adapt the syntax to your installation rather than changing the underlying calculation.

When the workbook becomes hard to maintain

Excel can model shared materials and collaborate on supported cloud-hosted workbooks. The question is whether your own process keeps receipts, reservations, recipe revisions and production records current. Software becomes useful when keeping those records synchronized is taking attention away from making.

Look for practical warning signs: recounting stock before every promise, stale copies of recipes, missing production deductions, or several orders competing for one ingredient. There is no universal number of products or hours that makes a spreadsheet unsuitable.

Makeable links recipes to live raw-material stock to show material capacity, unit costs and order feasibility. You can test your own materials and recipes on the free plan without a credit card. See why production spreadsheets become difficult to maintain before deciding whether to switch.

Sources and formula notes

Microsoft documentation checked on 2026-10-31. Examples are illustrative; adapt the quantities and units to your own production records.

• Microsoft: structured references with Excel Tables
• Microsoft: XLOOKUP function and version support
• Microsoft: SUMIFS function
• Microsoft: apply data validation to cells

Common questions