Field notes: replacing a 77,000-cell spreadsheet with two BOMs
I stepped into a Master Scheduling function for a manufacturer of highly configurable portable spas — 25 products across multiple brands, each with up to 5 outer shells, 7 seating and jet configurations, 10 seat color patterns, and a menu of jet packages, lighting, and audio options on top.
All of that lived in one spreadsheet: 26 tabs, one master tab pulling everything together, roughly 77,000 formula-driven cells — some of the longest nested formulas I’ve ever worked with. The data behind those formulas came from seven separate CSV exports, pulled nightly out of the ERP. Every weekend, I uploaded that master spreadsheet by hand so the MRP could run. Backspace over the wrong cell — which happened constantly — and you rebuilt the formula from scratch. The whole process ran 50+ hours a week and ate every weekend.
The spreadsheet wasn’t compensating for bad data. It was compensating for a planning structure that didn’t exist. The ERP already had everything it needed to plan configurable products — nobody had built the BOM structure to let it do that.
I restructured each product line into two BOMs instead of one. Using an 8-person spa as the example: an 8P-Common BOM, master-scheduled through the standard S&OP process against historical demand, and an 8P-Options BOM, where every option carried a decimal usage quantity based on its historical order rate — an option 25% of customers chose last year went in at .25. Those percentages could be adjusted directly as market mix shifted, no formula rebuild required.
It took a couple of months to get every product line rebuilt this way. Once I moved 100% off the spreadsheet, weekly Master Scheduling time dropped from 50+ hours to under 10 — and the ERP, not a spreadsheet, was finally doing the job it was built for.
Lee Stout is the founder of Staudt Advisory, an independent business and systems advisory for mid-market manufacturers. He has spent 25 years inside manufacturing companies — on the floor, in the systems, and with the leadership team — plus 11 years supporting ERP from the I.T. side. He advises owners, COOs, and CFOs on fixing broken ERP systems, untangling operations, and making confident decisions without hiring full-time executives.
