You need more than spreadsheets to manage your manufacturing business.
Spreadsheets are the most universally used tool in business today. In the hands of someone who knows how to write formulas, establish relational tables, and analyze data, a spreadsheet can be a very powerful tool. It is very common for a business to use QuickBooks and spreadsheets to manage their affairs.
How do manufacturers use spreadsheets? For practically everything:
- track inventory levels
- identify when reorders should be made
- list components used in a bill of material (BOM)
- schedule production
- capture costs of goods sold (COGs)
- track serial or lot numbers used in a manufactured item
- and many, many more things
Typically, there is one person in the company who is responsible for creating and maintaining “the spreadsheet.” If that person is a spreadsheet whiz who can write both simple and complex formulas, can protect them from accidentally being erased or changed, and diligently maintain the data in the spreadsheet, then the company will likely be fine and able to operate comfortably. But that can change:
The whiz leaves – becomes sick, takes a new job, retires… No one else at the company understands the formulas or how the spreadsheet works. Have to start over.
No control. In the name of collaboration, the spreadsheet is shared with others in the company who then accidentally change or delete some of the formulas, or possibly delete the entire spreadsheet. To protect the integrity of the data, the accountant locks down one version of the spreadsheet on her machine, but the production manager and purchaser make decisions based on a shared version of it or separately downloaded onto their own machines. Which dataset is right?
Business needs evolve, including: Increased sales, entry into new geographies or product lines with regulatory compliance issues that can’t be addressed within the spreadsheet, multiple revisions of BOMs that need to be tracked, real-time reporting requirements based on the status of production orders, and many, many more.
Are these real? Oh yeah.
Darryl Bernstein, Senior Sales Rep at MISys, often tells the “muffin fable” from when he was the purchaser for a commercial bakery in Toronto. Darryl is a bona fide spreadsheet whiz, and he built formulas into a comprehensive spreadsheet to manage the inventory needs of the bakery. It was a thing of beauty with macros and formulas across several tabs that updated the 2,000+ products based on changes in cells. But it was also a beast at over 37 Megs, pushing the ability of Excel to perform the calculations. One day he accidentally changed one of the formulas. As a large batch of muffins was in the oven, the production manager buzzed Darryl saying, “Where is the packaging?” The spreadsheet indicated there was plenty, but production insisted it wasn’t there. Darryl ran to the warehouse and scoured every foot of the facility but did not find the packaging. He dejectedly watched as $10,000 worth of muffins was thrown into the dumpster. He poured over his spreadsheet and finally found the errant formula. The next day the company reviewed and ordered MISys.
In Darryl’s case, the company had grown beyond the ability to easily manage inventory and production with a spreadsheet. Though the formulas written in the spreadsheet were sound and accurate, it was still possible to erase or alter the formula mistakenly. A well-designed inventory and MRP system like MISys Manufacturing maintains a relational database and can be used to easily predict what is needed and when without the risk of mistakenly deleting key data. Sometimes it seems like too much process and detail, but it takes just one spoiled batch of muffins (or whatever it is you make) to realize there is a better way.
