Bill of Materials Template for Excel: Cost One Piece

Bill of Materials Template for Excel: Cost One Piece of What You Make

Bill of materials template excel for makers: the eight columns, one candle and one soap bar costed line by line, and a batch that takes supplies off the shelf.

By · Editorial policy

Published · 12 min read

Bill of Materials Template for Excel: Cost One Piece of What You Make

A candle sells for $18 at a market stall. Ask the maker what one costs to make, and you usually get a guess. The wax came in a 40 lb case, the jars in a case of 12, the fragrance in a 16 oz bottle. Three cases, three prices, and no sheet showing how they meet inside one finished piece. That gap is why a file that holds the recipe beside the shelf beats memory.

The short answer: a bill of materials template for Excel lists every supply in one finished piece, with a quantity and a unit cost on each line, then totals the lines into the cost of one piece. For a maker, that is a recipe with money against it. Eight columns do the job, and the same list tells a batch how much to take off the shelf.

In Short

  • One row per supply, with the quantity used in one piece.
  • Unit cost comes from the last price you paid, not from memory.
  • A waste allowance on the lines that spill keeps the total honest.
  • The same list tells a batch what to take off the shelf.

This post costs one 8 oz candle and one 4 oz soap bar line by line. Both are made up for this post, not a real shop's figures.

What a Bill of Materials Template Needs

Eight columns are enough for a bill of materials template for Excel: supply, unit, quantity used in one piece, unit cost, line cost, waste allowance, supplier and note. With those you can total the cost of one piece, and reorder before the shelf runs out.

Formula Box: the eight columns, at a glance

Column What goes in it Why it earns its place
Supply The name you use on the shelf Matches the stock row exactly
Unit oz, g, each, or a case of 12 A cost per oz and a cost per case are not the same number
Quantity per piece How much one finished piece uses The number a batch is built from
Unit cost The last price you paid, divided by the units in the pack Keeps the total current
Line cost Quantity per piece × unit cost The only number that adds up
Waste A percentage on the lines that spill Wax, oil and batter always lose a little
Supplier Who you buy it from Turns a costing sheet into a reordering sheet
Note Substitutions, a batch that ran long The thing you will forget by spring

A bill of materials template is a sheet that lists every supply in one finished piece with a quantity and a unit cost, then totals them into the cost of one piece. A bill of materials is that list. A unit cost is the price of one unit of a supply, worked out from the pack you bought and the number of units inside it.

Build it in five steps. Take the pack price. Divide by the units in the pack to get the unit cost. Enter the quantity one finished piece uses. Multiply to get the line cost. Add the lines and the waste allowance to get the cost of one piece.

The free templates on page one of Google are written for factories. Smartsheet's set carries product name, part name, part number, description, quantity, unit of measure, supplier, unit cost and total cost, with optional BOM levels and a make-or-buy field. Vertex42's version asks for a unique part number and the quantity needed, and notes that complex parts may be sub-assemblies with their own bill of materials.

All of that is correct, and most of it is surplus to a maker. You do not have part numbers, because you have twenty supplies and a shelf. You do not have BOM levels, because one candle is one level.

The factory sheets do carry a quantity column and, in Smartsheet's case, an optional waste percentage. Both sit beside part numbers and levels a maker never fills in, so they are easy to miss. What a maker needs instead is a list where the quantity per piece is the point of the row. One row that says 7 oz of wax per candle, for instance, which a batch of 18 multiplies without a second sheet.

What a bill of materials template needs: a workbench with wax flakes, a jar, a wick and a soap bar laid out in a row

The Maker's Month, Worked Through

This section costs two products from one shelf, a poured item and a cured one. The month around them holds 24 products and supplies, 3 places, 41 sales and two batches, of 18 candles and 24 soap bars. The bill of materials decides what those batches cost.

One 8 oz soy candle uses 7 oz of wax, half an ounce of fragrance oil, one wick, one jar with a lid and one label. Every supply arrives in a case or a pack, so the first job is to turn each pack price into a unit cost you can multiply.

An illustrative example, made up for this post, not a real shop's numbers. Prices are in USD and left at two decimals.

Supply Buy it as Unit cost Per candle Line cost
Soy wax 40 lb case, 640 oz, $89.60 $0.14 per oz 7 oz $0.98
Fragrance oil 16 oz bottle, $32 $2.00 per oz 0.5 oz $1.00
Cotton wick Pack of 100, $9 $0.09 each 1 $0.09
Jar and lid Case of 12, $24 $2.00 each 1 $2.00
Label Roll of 250, $30 $0.12 each 1 $0.12

The five lines add up to $4.19. Wax and fragrance both spill: a little stays in the melting pot, and a little is lost to the pour. A 5 percent allowance on the wax adds $0.05, and a 3 percent one on the fragrance adds $0.03. The candle costs $4.27 to make, and that number holds until a pack price changes.

The soap bar works the same way at a smaller scale. A 4 oz cured bar uses 4.6 oz of oils, because water leaves during the cure. Lye, fragrance, colour and a paper wrapper complete the list.

Supply Buy it as Unit cost Per bar Line cost
Oils 5 lb pail, 80 oz, $17.60 $0.22 per oz 4.6 oz $1.01
Lye 2 lb tub, 32 oz, $1.92 $0.06 per oz 0.65 oz $0.04
Fragrance oil 16 oz bottle, $32 $2.00 per oz 0.3 oz $0.60
Colourant 1 oz jar, $3 $3.00 per oz 0.05 oz $0.15
Wrapper Pack of 100, $10 $0.10 each 1 $0.10

The soap bar costs $1.90, or $1.94 with a 2 percent allowance on the oils and a 3 percent one on the fragrance. Two products, nine supplies, and a cost for each piece that will still be right next month. The methodology is the same for a crochet blanket or a jar of salve: list the supplies in one piece, price each unit, then add the allowance on the lines that spill.

Bill of Materials Template Excel: What to Use When

The right format depends on how many products you make, and on what you need the list to do. A notebook page is enough for one product. A gallery template is enough if its columns fit your trade. For a maker who also keeps stock, the list has to sit in the same file as the shelf.

Situation What works Where it strains Best for
One product, one supply list A page in a notebook No total, no reorder link A maker testing a first line
You want a free starting grid A gallery template from Smartsheet or Vertex42 Part numbers and levels you will not use A maker who will extend it
You make assemblies from your own parts A multi-level sheet More structure than one shelf needs Gift boxes and kits
You already keep stock in a sheet A Recipes tab in the same file You maintain the formulas Makers who want one file
You want the cost and the stock together A purpose-built file No one-click import from Excel Makers with ten or more supplies

If you build your own, the stock side is already covered. A supply inventory template sets out how to hold supplies by unit, and a craft inventory spreadsheet keeps supplies and finished pieces in one sheet. A bill of materials template for Excel, kept beside the stock, joins the two, because it is the only list that carries both a quantity and a cost.

Manufacturing BOM Terms, Put Simply

Five terms appear in most factory templates, including the ones from Smartsheet and Vertex42. None of them is difficult once the maker's version is in front of you, and none of them needs a part number.

  • A single-level bill of materials is one list for one finished piece. A candle, a soap bar, a jar of salve. Most makers never need anything else.
  • A multi-level bill of materials is a list whose lines include another list. A gift box holding a candle and a soap bar is multi-level, because the candle is a sub-assembly.
  • A sub-assembly is something you make that becomes a supply in something else you make. The candle inside the gift box is the sub-assembly, and it has its own supply list.
  • Make or buy is whether a part comes from a supplier or from your own bench. The candle is made, and the box is bought.
  • A revision is a dated version of the list. When the wax changes or a jar size goes up, the revision changes with it.

Write the revision on the sheet and date it. A costing sheet without a date slowly becomes a guess, because the prices behind it move and nothing tells you when. The Census Bureau's manufacturing definitions describe materials and supplies inventory as "all unprocessed raw and semifabricated commodities and supplies for which you have title". That is the factory version of the supplies on a maker's shelf, and the split from finished goods is the same one a batch makes in one afternoon.

No public benchmark exists for what one handmade piece costs to make, because the answer changes with every pack price and every maker. That is why the number belongs in your own file, not borrowed from a guide.

The Cost of One Piece, and Where It Goes

The cost of one piece is the total of the line costs plus waste. Nothing else belongs in it: not your time, not the market fee, not the fuel to the supplier. Those are real costs, but they belong to a different sum, and mixing them makes the number impossible to check against a receipt.

Keep the number small and specific. A candle at $4.27 and a soap bar at $1.94 are two numbers you can check against a pack price. A vague "materials are maybe a third" is not, and it will not survive the first price rise.

What you do with the number is yours. In this example, if the candle sells at $18, the supplies take $4.27 and $13.73 is left before your time, which is 76 percent of the price. The soap at $7 leaves $5.06, or 72 percent. Both are arithmetic on a price you already set, not a recommendation for setting one.

The gap between the two is why you cost each product separately. The candle and the soap bar look similar on a table and behave differently in the sheet, because the jar takes $2.00 of the candle's $4.27 and nothing similar sits in the soap.

The cost of one piece: two cost cards side by side, one for a candle and one for a soap bar, each with a supplies total

Batches Take the Supplies Off the Shelf

A batch multiplies every line on the list by the number of pieces in it. Eighteen candles take 18 × 7 oz, which is 126 oz of wax, plus 18 jars, 18 wicks, 9 oz of fragrance and 18 labels. Twenty-four soap bars take 110.4 oz of oils, 15.6 oz of lye, 7.2 oz of fragrance and 24 wrappers.

That multiplication is the whole reason to keep the recipe in the same file as the stock. Enter the batch once, and the wax row drops by 126 oz, the jar row by 18 and the wick row by 18. Nothing is typed twice, and nothing is remembered between one pour and the next.

If you keep the list in Excel by hand, the same arithmetic is a formula. SUMIFS adds the quantities that match one supply, and Microsoft's reference gives the syntax as SUMIFS(sum_range, criteria_range1, criteria1, ...), so a range of batch rows totals one supply in one call. Put the supply name in one column and the quantity in another, and the total for the wax is a single cell. Xappex's template totals a parts list the same way, for a maker who would rather not write the formula.

Read the result against the reorder level, not against zero. A 40 lb case holds 640 oz, and a batch takes 126 of them. That leaves 514 oz, which is four more batches before the case runs out. The count that matters is how many batches are left inside the case, not how much is left.

Batches take the supplies off the shelf: the wax case, jar case and wick pack visibly lighter after a batch

A batch also moves finished pieces, not only supplies. Finished goods inventory is the other end of the same movement: 18 candles appear on the shelf as 126 oz of wax disappear from it. How much stock to bring to a craft fair then starts from that finished count, not from a guess about the weekend.

Why Trust This Article

We are the Cosmo Suite team that built the Ultimate Inventory Tracker, so the tab names and the limits in the next section come from the file itself. The candle and soap figures are a sample bill of materials we calculated on 21 Nov 2026 from made-up pack prices. Each unit cost divides the pack price by the number of units inside it. Waste allowances of 2, 3 and 5 percent sit on the lines that spill. The definitions and the template fields come from the published pages of Smartsheet, Vertex42, Xappex and Microsoft, and the inventory terms from the US Census Bureau, all checked on 21 Nov 2026. Check each unit cost against the last pack price you paid before you set a price on the result.

How the Ultimate Inventory Tracker Handles This

The Ultimate Inventory Tracker (by Cosmo Suite: one Google Sheets and one Excel file, 12 tabs, $9 once) keeps the recipe and the stock in one file. The Recipes tab holds the supplies in one finished piece and the cost of one piece. The Batches tab takes those supplies out and puts the finished pieces in.

Recipes and Batches tab in the Ultimate Inventory Tracker

Type the nine supply rows once, and each batch draws them down by the quantities you set. Unit cost, price, margin and stock value sit on the Products & Supplies rows, and a sale fills in the cost of goods and the gross profit on its own line. The Dashboard reads profit by product and category from the same rows.

Products and Supplies tab showing unit cost, price and margin beside stock

So the list you build for the cost of one piece also answers what a batch takes off the shelf and what each product earns. The tracker and its 12 tabs are described on the product page, and the inventory spreadsheet it replaces shows how the product rows and the recipe rows fit together. If a supplier changes a pack price, the unit cost is one cell to edit, and every line that uses it moves with the change.

Two limits. It has no connection to an online shop or till and does not scan barcodes, so you log or paste each batch yourself. Cost and profit are for your own tracking, not tax figures. There is no one-click import from an Excel bill of materials, so the first typing is yours. The product page shows the twelve tabs before you buy.

Ultimate Inventory Tracker is published by Cosmo Suite. Read About Us for company details, and the editorial policy explains how the site handles sources and corrections. For product questions, contact support@ultimateinventorytracker.site.

Frequently asked questions

What should a bill of materials template include?

One row per supply, with the quantity used in one finished piece, the unit you buy it in, the unit cost and the line cost. Add a waste allowance on the lines that spill and a supplier column for reordering. A maker needs eight columns; a factory sheet adds levels and part numbers you can live without.

What is the difference between a bill of materials and a recipe?

For a maker they hold the same information. A recipe is the supplies and quantities in one finished piece. A bill of materials is the same list with a cost against each line, so you can total what one piece costs. Smartsheet describes a bill of materials as an ingredient list or recipe for an end product.

Do I need a multi-level bill of materials?

Only if you build one thing out of another thing you also make. A candle poured from wax and oil is single-level. A gift box holding a candle and a soap bar is multi-level, because the candle is a sub-assembly with its own list. Most makers with one product line stay single-level for years.

How do I work out the cost of one piece?

Multiply the quantity of each supply by its unit cost, add the lines, then add a waste allowance on the supplies that spill. In the made-up example here, one 8 oz candle comes to $4.27 and one 4 oz soap bar to $1.94. Unit cost comes from the last price you paid, not from memory.

Should a bill of materials track stock as well as cost?

Yes, if you want the list to earn its place. A cost-only list tells you what one piece costs. A list linked to stock tells you what a batch takes off the shelf, so you can see the wax run down before the next pour. The Recipes and Batches tabs do that in one file.

Does the Ultimate Inventory Tracker import a bill of materials from Excel?

No. There is no one-click import, so you type or paste the supply rows once into the Recipes tab. After that, a batch draws those quantities off the shelf by itself. It holds up to 1,000 products and supplies and up to 20 places in one file.

Sources

  1. Smartsheet: Free Bill of Materials Templates
  2. Vertex42: Free Bill of Materials Template for Excel
  3. Xappex: Bill of Materials Template for Excel and Google Sheets
  4. Microsoft Support: SUMIFS function
  5. US Census Bureau: manufacturing definitions, M3 survey

Ultimate Inventory Tracker

Ready for the Ultimate Inventory Tracker?

Log each sale, purchase and batch once. One stock file you own, pay once.

Get instant access
Ultimate Inventory Tracker