IT zone
Feature
Excel’s new role – as a window onto company data. By David Carter
What role does Excel have in corporate computing? Many people in the IT industry think that Excel is not about serious computing at all. But Microsoft has decided on a new role for Excel – as the window through which people view their company data.Traditionally, reports are produced by Accounts or IT department at the end of the week or month, then distributed to managers and staff. But people are starting to question this. Why does it have to go through Accounts or IT? Why don’t we give data to users direct?
In his recent article Richard Pierce, Managing Director of PS Financials, identified this as a “hot” issue for 2008.
“Catering for non financial users
In our experience, intuitive, easy to use facilities for non financial users are "hot". Budget holders and managers want more accurate and real time information and using accruals and commitment accounting for known costs provides an accurate real-time picture. This means less admin for the finance team because they have to deal with fewer requests for information from non financial users.”
Absolutely right. The need is for intuitive, easy to use facilities for non-financial users, delivering information that is real-time and accurate, without having to involve the finance team.
But then Richard identifies another hot issue:
“Elimination of spreadsheets
A controversial one this, since David Carter and AccountingWEB's ExcelZone spend so much time discussing the use of spreadsheets in management reporting. But rather than trying to be more sophisticated about spreadsheet use, the smart move is to cull all the extra spreadsheets that sit around the outside of accounting systems. We all know that spreadsheets require loads of maintenance and are prone to error - and they make it much harder to get to one consolidated, centralised version of the truth, which continues to be very "hot" for finance managers.”
Controversial? Yes, because this is about as wrong as it is possible to be. Rather than spreadsheets being eliminated, they are going to become all-pervasive. In companies that run their accounts on packages like PS Financials, only be a small minority of users will look at the data via PS Financials itself. Most users will be viewing the PS data via an Excel spreadsheet.
Because the users love Excel
Why am I so sure of this? It’s a no-brainer. If you want to distribute data to users directly rather than get IT or Accounts to do it for them, a major issue instantly presents itself – user training. These dozens of new users, who are often not very computer-savvy, now have to be able to access and manipulate the data themselves. And for this they will need "intuitive, easy-to-use facilities", just as Richard says.
Look what happened in the BI (Business Intelligence) world. Numerous BI packages came on the market that offered better access to data. Users loved viewing the data, but hated having to learn a whole new package in order to see it. Hence the rise of “Excel add-in” packages. These are BI packages which work within Excel and are as Excel-like as possible. Now they dominate the BI market.
If the data comes via Excel the users love it, if it comes via anything else they hate it. And with Excel there’s hardly any need for training because the users know it already. In fact, it is mad to think of using anything BUT Excel.
So Excel now has this new role as a sort of “window” through which people in the organisation will be looking at company data.
The Import External Data command
The key to this new, window-like use of Excel is the Import External Data command. It’s designed to lock onto an external database such as an accounts package and to display the data tables in Excel.
With Import External Data, to update a report all the user has to do is to press the Refresh button. New transactions come through automatically into Excel and reports such as pivot tables are automatically adjusted. So, once the finance team or IT has set up the initial Excel link and reports, there’s no more work to be done. Just keep pressing Refresh.
It’s the beginning in a sea-change of how Excel is used. More and more we are going to see large-scale management reporting systems, where real-time data is routed straight from the company database to users via Excel.
Over recent months I’ve been running a series on how to get data out of your accounts package and into Excel. We’ve covered a lot of packages – Sage, Navision, Opera etc. For the benefit of AW readers who would like try distributing accounting data direct to end-users, I've based it on the Import External Data command each time.
Related links:
Management Reporting in Sage Line 50 and Instant
Import Data from Navision into Excel
Import data from Pegasus Opera into Excel
Management Reporting in QuickBooks
Import data from TAS Books into Excel
Import data from Iris Exchequer into Excel. Part 1
Import data from MYOB into Excel
Number of comments: 6
AccountingWEB.co.uk 23-Jan-2008
Categories: ExcelZone Features, Management Reporting Features, IT Features
Times read: 5617
I have been taking this approach with clients for some years now and I find it amazing how surprised they are at what can be achieved, and at how this is still far from common practice.
It is also very rarely used well even within the finance function. You can read my approach to automating management accounts in this way at:
http://not-just-numbers.blogspot.com/2008/09/do-your-management-accounts-take-weeks.html
>>A browser isn't sufficient because people don't just want to view their
data, they want to play around with it as well - filter it, sort it, add
things up etc. and for that you need Excel.<<
You don't NEED excel, there are alternatives, browser based if required, especially if you are talking pivot table style analysis (there are some very nice open source tools out there if you care to look).
>> Also, if you use Excel it's very easy to customise reports for each individual manager by bringing through only the data that concerns them
<<
Again you don't NEED excel and a fat client to do this. Its just one approach, and a not particularly secure one.
Martyn:
A browser isn't sufficient because people don't just want to view their data, they want to play around with it as well - filter it, sort it, add things up etc. and for that you need Excel. Also, if you use Excel it's very easy to customise reports for each individual manager by bringing through only the data that concerns them.
Charlie,
We've added links to some of the tutorials at the end of the article. You can also find the tutorials in our Management Reporting Zone under the heading "Import Data into Excel". There's a list of packages and QuickBooks is one of them. In fact, the QuickBooks tutorials show how to create a sales invoice report - product sales, margin earned etc.
Re ODBC, the Import External Data command is associated in most people's minds with ODBC, but Microsoft have been adding all sorts of drivers as well as ODBC. The most important ones are .TXT and .CSV and every package can export text or csv. (I used CSV for QuickBooks)
Finally, to add to Sandra's comment, Import External Data has been around since Excel 97 so it's hardly new. But I think most people so far have used it to download data onto THEIR OWN machine. I don't think many people have tjhought of using it to download data direct onto managers' machines. That idea is new.
I've been downloading data to excel for reporting purposes for many years using various packages and find it invaluable for producing good reports to non-financial people.
In my experience, many accounting packages produce the information they want to give - not the information that the Management wish (and need) to receive.
Short of writing bespoke reports on systems, where else are we to go? I find Excel invaluable for producing simple, concise reports with options for detail when required, being self-updating with links to downloaded data.
As for the "error" rate on spreadsheets - checksums are invaluable for showing these instantly. If we're honest, we will all error occasionally .... the skill comes with finding and correcting the errors prior to the information reaching the intended destination.
I think that you are right, David. I'd like to set such things up for my clients but I don't know how to do it.
You say:
"Over recent months I’ve been running a series on how to get data out of your accounts package and into Excel. We’ve covered a lot of packages – Sage, Navision, Opera etc. For the benefit of AW readers who would like try distributing accounting data direct to end-users, I've based it on the Import External Data command each time."
How can i see these briefings, and do you have you experience of taking data out of QuickBooks into Excel. I would like to develop reports for both inputting data (eg sales invoices raised by non-accountants in Excel) and exporting reports back into Excel, so that the non-accountants need never learn to use (or pay for a license!) for the accounting package.
Is this done via ODBC?
thanks
"Because this is what Microsoft has decided"
I think I'll do the opposite then!
Seriously, though - why not deliver ALL corporate info through a browser based system?
More secure as data is held centrally;
No need for retraining since most people can use a browser;
No need to pay the MS tax (windows+office);
