Other parts of the Series
Part 1 Principles and Best Practices
Part 2 Excel Weaknesses
Part 3 Hammers and Nails and Case Studies
Part 4 Current Page
But if basic or Intermediate Excel is not the right tool in these cases, what is it?
and just as important, How I can implement these new tools so they can work with Excel over the shortest period of time and at the lowest possible cost?
Generally, Best practices strongly recommends that when working with Big Data the appropriate tool is a database, along with ways of using Excel more effectively with data in general and databases in particular. And the good news for us analysts, financiers, managers and others, is that implementing these tools do not have to be neither long nor expensive. To tell you truth, the 2 best tools to start addressing these challenges are free... but I'm getting ahead.
ADVANTAGES OF USING A DATABASE WITH EXCEL
First, it is important to know that databases were CREATED specifically to handle large and complex data.
If Excel was originally created for ad-hoc and non-Recurring analysis, databases were DESIGNED taking into account the 5 characteristics of our continuous improvement process (see Part 1) (Integrity of Data, Speed Response of Model, File weight, degree of automation possible, capacity for error.) It is caring for these five characteristics that will reduce or eliminate the Excel Hell and the huge time this rob us in our work
Volumes from 100k to 1 million records, where we start having performance, Business Logic and Excel Automation challenges are the entry levels of data expected in a small database.
But if this is correct, if a Database Management Systems (DBMS) such as Access, Microsoft SQL Server and Hyperion has been used, from some time now, with good success in corporate settings, to support Excel in handling large and complex data, why so many analysts, managers and CFOs do not know or implement these tools?
The most obvious answer is PRICE. Implementing DBMS is expensive, sometimes very expensive. If you have recently spoken with someone from Oracle, IBM or SAP you know exactly what I mean ... we're talking easily between $ 25,000- $ 150,000 or a lot more ...
But then I think: we have Access right?, a more than competent DBMS for the job, why we still do not implement it?
The simplest answer I have found is that the databases are not our usual territory, EXCEL IS. As Excel Pros (part 2) We have worked in Excel for a long time, and we fought hard to master what we know. Databases for most of us is a strange concept, more associated with IT staff wich accounting and finance generally have a love-hate relationship with.
I can almost hear the complain of hundreds of fellow Managers and CFOs:
"As an analyst or Finance guy, I feel the powerful desire to control the cost, the tool and the process for the key elements of my work!... If I spent 10-15 years mastering Excel, and half or more of my processes are already based on Excel, you better bet that this “database” that you mention either is IN Excel or works VERY well with it. I don’t what to become an IT guy and learn and talk differently just because we are handling more data Now…”
The best way to continue using Excel and not harm our process or allocated budget, is to start with simple but powerful databases that we already have in our arsenal, (although we did not know,) so you have no extra cost, and start to apply them in simpler and less critical area of our processes so we can master them, and later we can scale them to more and more key processes.
What Tools I recommend?, 3 years ago I would say Access first, but after the enormous progress that Microsoft has made with Excel in the area of Business Intelligence, I prefer to speak of new best friends, PowerPivot, then, of his cousins, PowerQuery and PoweView
For those who have not yet had time to find out the PowerPivot Add-In, it is an extremely powerful and free tool, part of the new suite of Business Intelligence for Excel developed for Microsoft Excel 2010, 2013 and 2016. PowerPivot incorporate in Excel a real database, fitting the skills and work style of the Excel Pros, allowing us to work faster and in new ways with large and/or complex data
The findings we already have are revealing but mildly unsettling:
1. In large or complex data (Part 3) We are facing a new reality, and to place screws and not nails, a screwdriver must be incorporate our toolbox.
2. NOT doing it in a dynamic and increasingly complex work situations WILL take us into quicksands with data (part 1 and part 2) and will stop us from developing continual improvements process, which eventually will condemn us (again) to Excel Hell (Part 1)
3. Using new tools, even in Excel, and even if they are relative simple to implement is a challenge, but we can do so continuously and gradually, free and with our current staff, in small steps and processes each month until we master them and implement then in more complex and key processes...
In the end, that's how humans evolved right?, as the challenges we face grow, man (or woman) invents and adds to its arsenal more powerful tools to manage those challenges
The very personal question that you must ask yourself is:
What do you prefer to do?
Implement tactical changes to reduce your Excel Hell (since you're probably already overworked!,) and use that time to gradually strengthen your knowledge and skills of these new and powerful tools (PowerBI) to continually improve our processes?
-or-
Facing present Problems of NOT implementing features of continuous improvement (Data Integrity, Speed Response Model, File weight, degree of automation possible, capacity for error.) AND being in a constant fear that we will have even worst Excel Hell in our Future
In the Next article, we will begin to talk about:
1. tactics that can be implemented in Excel to accelerate and automate our reports. We will discuss Push Vs. Pull methods of bring data into Excel and how we can automate these processes
2. Then we will further introduce PowerPivot, which according to Bill Jelen, the famous Mr. Excel in person and author of over 40 books on Excel, described as the best tool to Excel implemented in the last 20 years ... (after PivotTables ;-))
Long Live to the Excel Pros!