Replacing formula manufacturing systems – when it is time to change? – Part 1 Excel

Change comes hard to many of us. My friends will agree that I am not immune to this. I personally over analyze the situation looking for reasons to keep the status quo. While on occasion this serves me well its result is often missed opportunities and sticking with a situation much longer than warranted.
In more than 20 years of implementing systems for formula manufacturing companies I would suggest that there are many of you in the same boat. Many of us are holding onto existing ERP system well beyond their useful lives.
Like me holding onto my obsolete phone system many are holding onto formula manufacturing systems well longer than we should.
In the following blog entries I will provide insight into the primary reasons formula manufacturers typically hold onto their obsolete solutions, key indicators that a time has come for a change and alternatives to the current systems.
In my experience there are 3 primary categories of formula manufacturing systems that need to be reviewed and serious consideration should be given to determine if they are still providing value to your organization.

1. Excel spreadsheets running the formula manufacturing business
2. Obsolete formula manufacturing application that is no longer up to date
3. Custom written solution that has not been updated with changes in the business environment

Excel spreadsheets – While I have never seen a static supporting this claim I think it is a fair assertion that the most prolific formula manufacturing solution on the market today is – EXCEL.
You would be amazed at how many companies trust their greatest asset – their formulas – with an easy to customize but impossible to integrate tool such as spreadsheets. Every month I am approached by companies that store all of their formulas in hundreds – if not thousands – of spreadsheets or one mammoth spreadsheet. Over time they have become obsolete and the company is dependent on one person to update and maintain the data. Great job security if you can get it.
It is obvious to me why these installations exist.
In the infant stages of a formula manufacturing company the R&D director (or equivalent) decided that writing the formulas on the backs of envelopes or in the lab notebook was "old school' and there was a better way to centralize the formulas. Armed with their version of Excel that shipped with their desktop computer version of Microsoft Office they began creating spreadsheets to store this data. After all the software was already owned, Excel is easy to use and new employees already had experience with the tool. What more would we want?
Well as time went on one copy of a formula became the basis for another formula. Someone requested more information to be tracked and every subsequent formula had this new addition. Changes to existing formulas made duplicates of the master formula and often multiple copies of the formulas were stored on various workstations. You know – just in case something happened to the original.
This approached worked fine as long as only one person maintained the formulas or virtually no changes to the formula structure changed. Also as long as no one needed any information across multiple formulas or the inventory data (such as price) never changed – then you were golden.
Well life does not work that way. Things change. Yes I said it – things change – and so should we.
With the growing compliance requirements such as Country of Origin, allergen disclosure, SQF audits and HACCP our life is no longer simple enough to be trusted with a spreadsheet written in the back room of an R&D laboratory. Instead formula manufacturers need centralized formulas that feed production data. We need formulas that have integrated QC functionality and the ability to perform cost rollups on the fly without additional data entry.
Excel spreadsheets are fine for analysis of existing data – such as yield trending or material usage – but to store master data such as formulas and batch tickets – we need a database oriented formula management system.
So are you one of these companies relying heavily on spreadsheets to store your master formula and production data? If so take a long hard look at what you are building. Do you really think it can continue into the future? The odds are against that design. Fortunately with enlightenment comes education and with education comes change. I am a poster child for that enlightenment – once again ask my friends.
Over the past 20 years many solutions have come and most have gone. The key is that each of the successful applications are built around a central database of master file data that multiple users can access. There is only one set of data and a change in one place affects all other parts of the organization. In this case that is a good thing.
If you are interested in a state of the art solution priced at a reasonable level then you should check out Vicinity from Vicinity Manufacturing (www.viciintymanufacturing). There are others but this one was written from the ground up with the sole purpose to address the formula manufacturing needs and is not attempting to be everything to everyone.
So take a look at your existing system. Are you using excel to store data or to analyze results? I challenge you to dig deep take a long hard look and make changes where they make sense. Once centralized source of formula data will do wonders for your company and believe it or not will save you significant profits in the months and years to come.
In the meantime stay tuned to the next couple installments where we look at obsolete market leaders and what to do with that custom solution the "Bob" wrote before he headed to Tahiti.