A DATA DISCOVERY TOOL WITH EXCELLENT MS 365 INTEGRATION

Using Excel for Business Intelligence and ERP Software - Are You Really Using Excel?


ABSTRACT
Many ERP Systems and Business Intelligence (BI) vendors will claim their product interoperates with Excel all the time when in fact this operation only involves some form of extract and copy into Excel.  Vendors and product presenters are perplexed when, after a thorough Business Intelligence or ERP software presentation mentioning Excel one-click downloads/extracts/reporting at numerous times, the accountants in the room will still say, "I wish I can do all that in Excel."  This article will try to uncover what accountants mean by this.

KEYWORDS
Excel, ERP Systems, ERP software, Business Intelligence, Financials reporting, accountants
 
ERP software for Financials like Oracle eBusiness Suite typically market the General Ledger as a comprehensive and reliable single source of truth.  Meanwhile, accountants typically work in Excel to generate financial reports that the Chief Financial Officer (CFO) sign off and release to the board.  For this to happen, the "truth" from the "single source" needs to "travel" from the ERP/BI into the accountant's hands (aka Excel) to be analysed.

Immediately, you would ask, "Why does it have to travel?".  Why can't the CFO and the accountants all STAY in the ERP systems and sign-off on the General Ledger without using Excel?  Hmmm, ERP software vendors would love that.  That's a big question actually and sorry, that's not going to be answered in this short article.  Suffice it to say, that's not what is happening right now, and won't be happening anytime soon.  (Perhaps something to do with why accountants and CFO's are highly paid professionals, and why we want to prevent ENRONs in the future.)

What is happening now, is that accountants spend up to 80% of their time collecting data and only 20% analysing them.  The portion of time they spend in Excel, i.e. to analyse/annotate the financials data and for the Chief Financial Officer to sign-off on them, would be considered useful analysis time.  Everything else is data-collection time and this includes the data "travel".

Unfortunately, "travel" is not just the transmission of data, but for accountants, it includes the added burden of ensuring that no human error was introduced during the process, and what they saw in the ERP/BI is what is being analysed later on in Excel.

This is the crucial point.  Typically, ERP software for Financials will display account balances in a web screen (eg, Internet Explorer, Firefox, or Chrome).  Once displayed, there is a button to export to Excel.  Clicking on this button will open Excel and show the same results as a spreadsheet.  In some cases, there is no export button, and the user actually needs to highlight the web rows and columns, hit Ctrl-C, open Excel separately, and paste the data in the spreadsheet.

What is wrong with this scenario?  Accountants don't like this picture, because:

1. Firstly, there is good chance of human error in the copy-and-pasting and all that ERP navigation.  The accountant will be doing this step in the middle of a gruelling 3-hour session with Excel perhaps late in the night during month-end in a high-pressure situation.  And more importantly, it will be done repeatedly.  Imagine the accountant doing a series of Excel steps - pivot table, sort, filter, delete, insert, [then ERP steps -> invoke ERP, logon, navigate ERP, copy-paste ERP style], pivot some more, etc.  Then repeat the whole thing for the next accounting entity.  This mixture of Excel steps and non-Excel steps can make the accountant pull his hair.  It is just error-prone, and explains the 80% of his time spent in gathering useable data for analysis.

2. Secondly, the format of the ERP results is very generic and not always exactly what is needed.  There may be missing columns that still need to be computed, or simply many columns that are not needed but get pasted anyway.  The accountant still needs to remove, filter, and manipulate the information that travelled from the ERP - before it is analysed.  Arguably, this puts doubt to the validity of the single source of truth.  One accountant will "manipulate" the extracted data in his preferred way and another accountant will manipulate the same ERP / Business Intelligence data.  Both may have different "truths" by the time they start analysing in Excel!  Imagine what the CFO thinks about this.

What would be nice is if the ERP-related steps can be dispensed with.  Obviously, this is impossible as the ERP holds the data, and Excel is just a blank spreadsheet to begin with.  But what is possible is that the ERP data can be retrieved within Excel without using ERP navigation.  If the accountant can somehow get the data from the ERP using only built-in Excel commands and menus, then there is no "travel" at all.  Perhaps just longer wait times.  But this is still much better than switching his frame of mind from Excel to non-Excel then back to Excel.  The accountant would be happy to sit all day and all night just looking at Excel, and be confident that he used only his proven Excel skills when analysing data.  The Chief Financial Officer would also be confident in his sign-off.

How can this be achieved?  Excel can already access external data for any ODBC database or ERP using VBA.  VBA allows addins to be created and used by accountants as if they were native built-in functions of Excel.  Many ERP/BIs fail to do this for reasons that are not clear.  However, there are other add-on products that can do this for the accountants.  These products need to be purchased separately, but can also be re-used for different ERPs or even other databases.

CONCLUSIONS
Hopefully, ERP/BIs and other IT software providers will be able to recognise that accountants have a unique way of using Excel for Financials data.  This recognition could lead to the development of better and more effective software that would allow accountants to do less data gathering and more data analysis.
 
AUTHOR
Antonio Elinon is a consultant who has worked with accountants and in his work has successfully identified what some accountants need when they use Excel.  He recommends a fully integrated Excel-driven product like Data Mart for Excel (www.datamart4excel.com) when addressing the accountant's need to extract Financials from ERP/BIs like Oracle eBusiness Suite and SAP.  Visit www.Datamart4Excel.com for more information.
 
COMMENTS
Your comments on this article are welcome.  Please post your comments or start a discussion in the Forum category Article Comments under this article title.   

If you like this article, please donate to support creation of more articles by various authors.