Home About Us Services Info& Events Our Customers

Enhanced Reporting and Office Automation Using Standard MS Office Products


By Barry Hanks



Many small and mid-sized companies would love to graduate to the sophisticated business performance management (BPM) software employed by the corporate giants, but they have not quite reached the necessary critical mass in terms of justifiable need and adequate funding.  These BPM solutions usually come either as a very costly add-on to an already-expensive ERP system, or as a pricey stand-alone package that needs to be integrated with the ERP.  Given the current state of affairs with the economy, major implementation projects such as this will likely remain off of most companies’ radar screen for the foreseeable future.  So what can be done to emulate the high-priced software for a fraction of the cost?  Odds are, the answer has been on your computer all along. 



You Already Own the Software


You’ve been told since you were in grade school that the human brain is so powerful that most of us never use more than 10% of it.  The fact is, the same statement is true of Microsoft Excel.  Nearly every company nowadays starts off with a barely-adequate accounting system, supplemented by Excel spreadsheets.  Excel is initially used for financial reporting, forecasting, production control, data analysis, and everything else under the sun.  As companies grow in size and complexity, they ultimately reach a point where they feel that they have outgrown Excel and need to take the quantum leap to a more robust canned package that will provide the business intelligence (BI) dashboards and scorecards and the OLAP analytical capabilities that they need to boost them to the next level.  If the truth be told, this point is generally reached when the company and their employees exceed the limits of their knowledge of Excel, rather than the limits of the software itself.


Admittedly, at some stage along the growth path from a garage business to a Fortune 500 behemoth, a company will genuinely need to upgrade from an Excel-based internal reporting and control system to something more structured and formal.  However, the most common mistake is making this transition long before it becomes necessary, and incurring the exorbitant conversion costs (i.e., the cost of the software, hardware, and the internal disruption that accompanies re-educating the entire staff) at a time when the company can little afford it. 


For the past decade, I have been helping my clients postpone the agony of costly software conversions by designing custom applications that utilize standard features of Microsoft Office, namely Excel, Access, and Visual Basic.  All of these are included in the Professional edition of MS Office.  For anyone equipped with merely the Standard version, the only missing component (Access) can be obtained for a paltry $85 or so, an expenditure that will pay for itself the first time you use it.  These three Microsoft products work amazingly well together, and they can be molded into packages that address the exact needs of the client as no off-the-shelf program can.  The result is an application that employs Excel as the visible front-end, so the user is always in familiar, comfortable territory.  By clicking an icon or responding to a prompt in a dialog box, the user invokes a Visual Basic routine that hands off the data to Access, which in turn does all the “heavy lifting” (importing data, processing queries, refreshing tables, and extracting the finished product back to Excel) silently and invisibly in the background.  After a brief delay (a few seconds, usually), the data is returned to Excel where it can be filtered, pivoted, sorted, analyzed, re-combined, charted, printed, or attached to an e-mail and distributed.



The OLAP Alternative


There is no doubt that a quality OLAP product, based on a well-constructed data warehouse and using well-designed “cubes”, can be a powerful analytical tool.  Unfortunately, for most small to medium-sized companies, OLAP is typically an expensive over-kill.  In a recent article about “Excel-friendly” OLAP products, the author listed several things that, in his view, could only be accomplished with the aid of OLAP.  As I read through the list, though, it became evident that nearly everything mentioned could be achieved through the creative use of Excel, Access, and Visual Basic. 


One of my clients referred to the OLAP software they were using as “pivot tables on steroids”.  This is a valid observation in more ways than one.  For starters, the most useful means of displaying data retrieved from an OLAP “cube” is in the form of an Excel pivot table.  All of the major OLAP products offer an Excel front-end or add-in to make them more user-friendly, because this is what the users really want and feel comfortable with.  Secondly, although many people in the company ultimately end up using the OLAP software to some extent, only a handful even come close to utilizing most of the features available.  For the vast majority of the users, the mechanism represents a cumbersome and awkward impediment.  In other words, they would be perfectly happy with just the pivot tables, without the “steroids”. 


A very cost-effective alternative is to determine what data the users really need, and make it available to them in pivot table form.  If you look into it, you will find that most users routinely utilize a relatively limited subset of the total database.  The approach I typically use is to create an Access database with an ODBC connection to the master database or data warehouse, and create links to the required tables and fields.  The data is consolidated into a single query, or visual basic code can be written to refresh a table within Access.  From there, the data can be imported into an Excel workbook and used as the data source for pivot tables, or the pivot tables can be linked directly to the query or table in the Access database as an external data source.  Some very simple visual basic routines can be written to enable the user to select date ranges or other variables in order to limit the scope of the query.  If it is later determined that more data is required from the data warehouse, it’s simply a matter of expanding the Access links and revising the query. 


Once the Excel / Access / Visual Basic package has been configured, all the user has to do is select a variable or two from pull-down menus, click the “refresh” button, and the pivot table data is promptly updated.  Users can then drill down and analyze the data to their hearts’ content, or they can create standard reporting packages consisting of a multitude of pre-configured pivot tables.  One of my clients produced a monthly financial reporting package that was nearly a quarter-inch thick, and about 80% of that consisted of pivot tables displaying the supporting data broken down by sales territory, product line, various time periods, budget vs. actual, cost element details, etc.  The total effort to create the package was limited to a few mouse clicks, because everything else (including setting the print ranges and printing the package) was fully automated.


A Cornucopia of Business Intelligence


There are ample BI packages on the market these days, but in terms of convenience, ease of use, and cost effectiveness, nothing rivals good old Excel.  In the hands of a skilled user, Excel can be used to create magazine-quality dashboards and scorecards with all the tables, charts, and diagrams that the report’s audience really needs in order to monitor performance and make intelligent, timely, and informed decisions.  Even an Excel novice can easily construct and update the classic “traffic light” (green/yellow/red) status reports that have become a standard tool for project management reporting.


One of the weaknesses of most commercial BI tools is that they are confined to the company’s central data warehouse, which in turn is derived from the ERP system.  This rigid data structure poses a serious problem because all data warehouses lack essential data from both internal and external sources.  Internal data is restricted by the “silos” that exist in nearly every organization.  For instance, it is not uncommon to exclude HR data from the data warehouse, owing to the confidential nature of some of the information.  As a result, any analysis requiring both sales and compensation statistics would have to be done off-line (most likely with the aid of Excel).  External data, by its nature, has no natural link to the ERP system and rarely finds its way into the data warehouse, yet it can be extremely valuable as a means of enabling management to view internal data in a broader context.  These sources can include economic data, market trends, web traffic, survey data, trends in the costs of key resources, exchange rates, industry-specific data, and the performance of competitors.


A tremendous advantage of the Excel/Access/VB approach is that it is not confined to a single data source.  In fact, almost no form or source of data is out of reach.  The Access database can link directly to multiple relational databases (including the company’s data warehouse) via ODBC connections, and to tables in Excel workbooks.  Excel can import data from a myriad of sources including text files (flat or delimited), web pages, databases, XML, query files, and other workbooks.  Visual basic can be used to scrub and upload data from Excel into Access, where the various data sources are merged into a cohesive database suitable for feeding pivot tables, charts, or other forms of analyses.  These, in turn, can be incorporated into the dashboards and scorecards required for management’s BI reports.  As an added bonus, this entire data flow can be automated to make the creation of the finished product almost effortless.


For example, one of my former clients was under contract to provide a range of services for one of their key customers.  The service level agreement specified a number of metrics by which my client’s performance was to be measured.  The data for the reporting package came from various sources, and was consolidated in an Access database.  The compiled data was then imported from Access to an Excel workbook, where it automatically updated an assortment of pivot tables, charts, dashboards and scorecards.  The routine for creating the finished package consisted of:  (1) selecting the desired month from a pull-down menu and hitting the “Go” button, (2) reviewing the resulting data for reasonableness and accuracy, and (3) clicking an icon to invoke a VB program that created two separate versions of the reporting package (the detailed version for my client and a summary for their customer) and e-mailed them to a pre-defined list of recipients.  Actually, the VB program could have been written to complete the entire operation in only one step, but the mid-process review served a useful purpose. 


A Cavernous Niche


No matter how expensive, well-known, or comprehensive a company’s ERP software happens to be, there will always be specific tasks, analyses, reports, and applications that for one reason or another fall outside the scope of the ERP package, or fall into the cracks between the standard ERP modules.  Out of sheer necessity, most companies somehow manage to get the job done in spite of the ERP’s failings, usually by throwing bodies at the problem, or through the creative use of Excel spreadsheets.  The voids not adequately addressed by the standard ERP create a “niche” that desperately needs to be filled.  For some fortunate companies this niche is merely a sliver, but for most it is about the size of the Grand Canyon.  The marriage of Excel/Access/VB provides an excellent and cost-effective means of filling this niche by creating custom applications to address specific requirements. 


Over the years, I have used this strategy in a broad range of contexts, including:  Financial reporting & forecasting, asset management, inventory tracking, R&D project reports, time sheet & expense report processing, sales forecasting & manufacturing production allocation, material requirements planning, custom billing solutions, sales commissions calculations, customer service reporting & order tracking, and segregation of duty monitoring for SOX compliance.  In each of these cases, the job could, in theory, have been done by an army of drones armed with calculators or spreadsheets.  However, by using the Excel/Access/VB approach, a single operator can accomplish the task in minutes with only a few mouse clicks. 


The following example illustrates how the Excel/Access/VB solution was used to automate an arduous, time-consuming task and radically improve productivity:


The Problem:  A client of mine was a major electronics company with substantial R&D expenditures.  One of the admins in the R&D department of the cell phone division prepared a monthly R&D project report, showing for each project, the cost (current month and YTD) per the general ledger, and a breakdown of labor hours by employee and task (also current month and YTD) per the payroll records.  The company’s ERP system was PeopleSoft running on an Oracle database, and they were using Cognos PowerPlay as their OLAP product.  Everyone in the company was expected to retrieve information from the system via a number of pre-defined Cognos “cubes”.  There were generally between 25 and 30 current projects in any given month with the list constantly changing as old projects were completed and new ones were begun.  To create the reports, the admin had to run four separate queries for each project:  (1) current month cost, (2) current month labor hours, (3) YTD cost, and (4) YTD labor hours.  This amounted to over 100 queries from which she had to copy and paste the results into Excel spreadsheets.  This exercise took between two and three hours every month.


The Solution:  I created an instance of Access and linked it (via an ODBC connection) to the same data warehouse that the Cognos cubes were using as a data source.  In the same Access database, a table linked directly to the spreadsheet on which the admin maintained the list of current projects.  Queries in Access pulled the required fields from the data warehouse and limited the extracted data to the projects named in the admin’s spreadsheet.  A custom visual basic program enabled the admin to select a month from a pull-down list, and then automatically constructed a workbook with one page for each of the current projects.  The sheets were populated with the appropriate data from the data warehouse, formatted, and printed, all in about four minutes.  The admin’s procedure was to select the desired month from the list, hit the “Go” button, go get a cup of coffee, and walk by the printer to pick up the finished reports on the way back to her desk.




In these troubled times, everyone is looking for ways to cut costs and improve productivity, without sacrificing the upscale features usually available only from high-priced canned software packages.  For a surprisingly robust and cost effective BI, OLAP and office automation alternative, you need to look no further than the MS Office suite that you already own.


 Contact Barry Hanks at (408) 971-6600 x242 or for more information...        




Success Stories

Plan for Success:  Enterprise Enrichment©