Spreadsheet or BOM software: where a spreadsheet stops working

A spreadsheet is a fine place to start a parts list. It stops being fine when other things begin to depend on that list being right.

You've probably got one already. A grid with part names down the side and amounts across. For a single product with a short list, it does the job.

Where a grid starts to strain

Sub-assemblies. A spreadsheet is a flat grid. A BOM with levels is a tree, and the ERPNext manual describes each item having its own BOM, forming a tree of items with multiple levels.¹ You can fake that in a grid with indents, but nothing links the rows for you.

Shared parts. The same screw sits in many products. In a grid you type it in each place. In BOM software the part is one record that many lists point to.

Copies drifting apart. A web-based spreadsheet such as Google Sheets can be accessed by multiple users.² That helps editing. It doesn't tell you which copy someone ordered from last Tuesday.

Formulas that break. Spreadsheet cells can be locked to prevent accidental overwriting, but the data isn't protected until the feature is switched on.² And many common errors cause circular references, where a formula ends up pointing back at itself.²

Roll-ups by hand. If you want every raw material needed for a whole product, you have to add up the sub-assembly lists yourself. The ERPNext manual describes an exploded table that fetches raw materials for sub-assemblies and phantom items along with their quantities.¹

What changes when the list drives other work

This is the real tipping point. One ERP manual warns that purchase requests are only accurate if the BOMs behind them are kept correct.¹ If buying relies on the list, a stale list costs you money.

Software can also use the list directly. A bill of materials is often tied to a production order. Issuing the order may reserve the components you have in stock and raise requisitions, meaning purchase requests, for those you don't.³

In that same manual, a setting can use the BOM to consume raw materials automatically, at the quantities in the list multiplied by the quantity finished.¹ A spreadsheet can't take stock out of a store by itself.

How that linking works is set out in the stock and buying notes.

When a spreadsheet is still the right call

Don't buy software out of habit. Stay with the grid if:

• you make one or two simple products
• nobody orders parts from the list without checking it
• one person edits it
• the parts don't share sub-assemblies

Move when you notice you're copying the same lines between files, or when a wrong count has already cost you a rush order.

Moving across

You don't have to move everything at once. Pick one product with real sub-assemblies and put it in first. See how the levels and costs behave before you commit the rest.

Before you do, tidy the spreadsheet. Give each part one name and one number, because software treats two spellings as two parts. Naming is covered in a separate page on part numbers.

Different tools import data differently. Read your tool's own help pages before you prepare the file.