I am a strong advocate of Microsoft Excel and PowerPivot for the analysis and organization of information. While it may seem odd to many, Microsoft should be applauded for the newest release of Excel and, in particular, the free add-on of PowerPivot enabling the business intelligence like crunching of information. I’m impressed. As with most good things, there’s a caution that comes with this new tool.
I’ve had the implications of poor internal controls beat into me many moons ago when I managed an IT Auditing group for an international firm. As a result, I can’t shake the butterflies when I see organizations relying on these tools for complex and manually intensive data analysis and reporting. Splitting large files into chunks of under one million rows for PowerPivot and combining the reporting results on the back can introduce an opportunity for inconsistency. Combining that with the manual intervention necessary to integrate multiple data sources compounds the risk of inconsistencies; particularly if the individual with all the knowledge of manual manipulation is not available for whatever reason. Ouch.
To be clear, I am not discouraging the use of Excel and PowerPivot. They are excellent tools and I use them quite effectively on an ongoing basis. However, if you need a repeatable, reliable process to generate information used in key decisions, get yourself a production worthy tool. Spend your time utilizing the information as opposed to manually crunching it. The number of affordable solutions that can solve your production challenges might surprise you.