How expensive? Well JP Morgan lost 6.2 billion due to this one mistake. That's a lot even for a major Wall St. Bank. And the list goes on and on:
Prominent financial blogger James Kwak calls Excel "one of the greatest, most powerful, most important software applications of all time." But perhaps we ask too much of the program, or perhaps of our ability to cut and paste. In the past few years, Excel has been implicated in some of the biggest blunders on Wall Street and in finance in general.
The standard response to this is companies need to:
Old approaches are out, and automation is in. The old approach of controlling and analyzing a business via spreadsheet-driven tools and processes is over. Today's businesses, and not just Fortune 1,000 companies, need a modern, integrated way to manage revenue, achieve operational efficiency, and reduce corporate exposure.
Yes, that's the best solution. If you can replace your system with an up to date automated system - do it. That is the best solution and it tends to pay off. The cost of an improved system, once it's actually working and the bugs have been removed, tends to be less than the cost of the financial mistakes it now prevents.
But as the old saying goes "when you're up to your ass in alligators it's hard to remember that your initial goal was to drain the swamp." If your system is Excel, and is going to continue to be Excel for some time (even if just until the new system is working), then there is a giant step you can take to reduce the chances of an expensive mistake. A very simple step.
Often the culprit in these errors, is not Excel itself. It's not the data held on the desktop in a spreadsheet. It's cut & paste. When part of the process is cut & paste that means part of the process is a human being performing a repetitive task. And when humans perform a repetitive task - they make mistakes. No matter how smart they are, no matter how careful, no matter how much they check - they will get it wrong at times.
If cut & paste is part of your process it's not that it can lead to errors, it's that it will lead to errors. Absolutely unequivocally when your process includes cut & paste, you have made a decision to introduce errors into your system. Regularly & repeatedly. This will cost you, the only question is how much, and how often.
The trick is to automate the cut & paste part. This is incredibly quick & easy to do in most cases. You create a template in Excel that the data will populate, including all formulas as needed. You review and test that template with initial data runs very carefully. And then you automate the process of populating the spreadsheet with data.
This entire process can be accomplished in days. And because you still have the same system, the same spreadsheets, there is no change in your system. No need to train anyone or add additional people, steps, costs. All you've done is automate one step of your existing process, delivering the required spreadsheets faster. And you've eliminated a source of errors in your system. A source of very expensive errors.
There's a lot of valid reasons why you can't implement a perfect solution tomorrow. But there's no excuse for leaving the cut & paste steps in human hands. There are numerous products that can have you safer in a week:
- Excel Smart Tags is the simplest and is part of Excel. If this can work for you (it's limited in functionality), you have a no cost solution.
- Excel Automation (in Excel or with PowerShell) takes advantage of Excel's automation API. In this case you're writing VBA code to perform the merging of the data into the spreadsheet. Again this is part of Excel (free) and has more power than the smart tags. One issue to watch with this approach, if your VBA code becomes very complex, you now have to worry about subtle bugs in the code.
- Windward AutoTag provides the ability to design your template in Excel so the layout, formatting, & formulas are all created in Excel. With its powerful tagging system, you can then pull in any data from most any source, to populate the generated spreadsheet.
- SpreadsheetGear & Aspose - These (and other products) give you the ability to create the entire spreadsheet from code. This is the most work and has the downside that every part of the spreadsheet is in the program - there's no exiting spreadsheet as a template. Creating the spreadsheet takes the longest with this approach. On the plus side, you have the ability to do anything.