As an example: CompanyZ sells stuff, and needs to figure out when to buy its raw materials. Factors such as shipping rates, lead time, and sales forecasts must be accounted for.
The purchasing guy draws up a model to calculate the buying numbers for the next three months, cleverly using variables and named ranges to allow for adjustments in the factors. His boss likes it, and asks him to blow it out to 1 year.
Ok... not too difficult, since the formulas were set up right. A bunch of new months are tacked on to the right end of the sheet. Then the marketing guys ask him to break it down by region. That's not so easy, because this is a brand new DIMENSION on the data. Here's where a typical business model starts to become a "house of cards", as significant additions are hung onto a basic functional foundation.
As our models take on more and more of a mission-critical role, they expand into dozens of worksheets, with invisible formulas linking everything together. Some key person understands the unique mechanics of it all, and every Excel jockey out there, (you know who you are,) has a personal spreadsheet-building style, usually self-learned and utterly unique.
Then that person quits or dies, and the model becomes a "black-box" for a while... still in use, still giving answers, but we're not 100% sure how. Then the business environment changes, the model can't be understood and adapted, and it dies. The company scrambles to replace it, and a small ripple of chaos rolls through the departments that relied on all those reports and forecasts that are suddenly unavailable.
This situation is very common. Thousands of companies use millions of complex Excel models for critical business decision-making tasks. Based on 20 years of experience in the field, and on the horror stories I've read on-line, most of the spreadsheets out there contain errors, and some of those errors can lead to catastrophic results.
To mitigate this risk, companies need to take active steps to manage their Excel assets. The creation of spreadsheet-based tools needs to be approached the way other business software projects are: with planning, a solid design methodology, controlled testing and documentation.
This will entail extra cost, to be sure. The required engineering resources may not be available. But this is a budget question, which boils down to a matter of priority. When the value at risk is actually computed, (what is the cost of losing this customer? Losing all customers?) the numbers might make sense.
A flat spreadsheet model is fine for one-off reports, or succinctly defined micro-problems that are purely mathematical. At a certain point, as spreadsheets expand to fulfill more advanced forecasting and reporting duties, the flat spreadsheet needs to be treated as a working prototype for an actual Application, which can be audited, understood, fixed and validated.
Excel is still a viable platform, but complex models need to be assembled methodically, by someone who understands basic software engineering. Object Classes should be used to abstract the different business entities. A Relational Database should be used to store the data and intelligently retrieve it. A Report Engine should be used to produce the output.
Such a system, with it's logic and structure openly visible in VBA code, will not break down and be cast aside, but will grow, prosper, and add far more value to the company than it's original cost. Any programmer can step in, see what's happening, and make the required fixes or additions, confident that the whole thing won't come crumbling down.
Posts
