Manufacturing BOM, Inventory & COGS Tracker

Manufacturing BOM, Inventory & COGS Tracker connects raw materials, recipes, production and sales in one Excel workbook. Each product has a bill of materials listing every raw material and the quantity that goes into one finished unit, with a waste allowance. When you log a completed build, the workbook consumes the right quantity of every material, values it at weighted average cost and adds labor and overhead to give the total cost of the build and the cost per unit.

Raw Materials keeps live stock for every material: opening quantity plus goods received, minus quantities consumed by builds, plus stock adjustments. It calculates the weighted average cost from your purchases, stock value, usage over the last 30 days, days of cover, the quantity needed for planned builds and a status of OK, Reorder, Short for plan, Out of stock or Overstock, with a suggested order quantity, last received date and last price.

Products shows the material cost per unit from the BOM, unit COGS including labor and overhead, gross profit and margin, units built and sold, finished goods on hand and value, and the maximum number of units you can build right now from current raw stock. Planned work orders are checked against stock and flagged when a material is short, and products below your margin target are flagged too.

Sales records finished goods sold with revenue, COGS at the product's unit cost, gross profit and margin. The Monthly Report covers twelve months of units built, material, labor and overhead cost, purchases, units sold, revenue, COGS of sales and gross profit with a chart. The Dashboard brings together raw and finished stock value, reorder alerts, planned builds, monthly figures, unit costs, top materials by consumption, latest builds and stock value by category.

Capacity covers 150 materials, 60 products, 500 BOM lines, 1,000 builds and purchases and 1,500 sales lines. Over 37,000 formulas sit in locked cells and protection has no password, so you can unprotect any tab to customise it. Sample data from a fictional furniture workshop is included so you can see every tab working before you add your own. Built for Microsoft Excel (desktop or Excel for the web) and contains no macros.