The Secret Trick that Turns Excel into a Flexible Business System

ERP software is deeply embedded in most mid- and large-sized businesses.

Love it or hate it, the fact is the large majority of transactions are executed on some kind of ERP system.

A rigid system designed to process transactions is going to be somewhat inflexible with other functions that you may desire.

Try to get, say, the right report on business performance.  Or visibility on supply chain constraints.  More often than not, you’ll end up feeling let down and frustrated by ERP.

To get around this problem, companies choose from one of two options.  Either they spend big money on business intelligence.  Or they bring data out of the system and manipulate it in a spreadsheet.  (Actually..*Ahem*.. folks with big BI budgets are also often caught with data in a spreadsheet).

I’d like to give you a trick that gets Excel to behave like it was a business system.  First, let’s confront some fears about Excel hell.

The spreadsheet deed is committed furtively with hand-wringing and guilt.  We have all (been) lectured about the horrors of “spreadmarts” and the deadly spreadsheet sins.  These fiendish data stores slither across the organization, leaving a slime trail of error and risk wherever they go.  Ugh.

Over there we can see users emailing huge Excel file versions to one another.  No-one knows who, if anyone, has the right data.  Down we fall into spreadsheet hell.

Of course, Excel is not evil.  It is just a tool.  And like any tool, it will not give you the right results if you don’t use it properly.

Excel integration with databases is getting better.  There are more users hooking up to Open Database Connectivity Drivers and pulling tables directly into a worksheet.

The problem is that setting it up usually requires the support of IT.  And they are usually the most pious in their preaching against spreadsheet evil.  It is understandable that business users don’t get the support they need to connect up Excel into the database.  Apparently, they shouldn’t be using Excel in the first place.

Much of our consulting business is with multinational clients.  We work with business people who just want to get the job done.

Their needs are not served by the functionality of the ERP system.  And they can’t always wait the year or longer that it takes to get a new solution approved and pushed through the bureaucracy.

For these clients, Excel is an “under the radar” solution that we can usually get delivered in the time it takes to fill out the right requisition form. A stealthy hero that appears out of nowhere and gets results where no-one else can.

Anyway, I digress a little.  Let’s go directly to that secret trick that anyone can use to turn Excel into a flexible business system.

The answer is simple, yet unglamorous.  The secret trick is text files.  We love text files because they allow us to move volumes of data across the network.  A text file can carry large tables of data, with one row per line and the columns delimited with a character, usually tab or comma.  they are simple, fast and difficult to break.

An Excel Planning System can output text files in a format that other systems read very quickly.  You can connect a  text file with Excel and then over-write it, the connection will remain in place.  No-one needs special permissions or software beyond Excel.  You write text files from Excel manually or automatically using macro VBA.

Most of the systems we develop for our clients are connected by way of text files.  A simple example is the Capacity Planning Tool. This has five simple tables that form the input.  They can either come from the host system, or from another Excel tool or system.

This is a tutorial on creating and managing text files as an input to an Excel Planning System.

If you’re not familiar with importing text files into Excel, then there is an article on that for you here.

The Fast Excel Development Template has a Query Template that is purpose-built to pull in data from text file.  It comes ready-made with macros that can copy files from a network folder to a local drive.  The text file imports can be refreshed from a new file location.  There are even some filter functions that can filter a text file based on its content and write a new one that fits Excel 2003 limit.

Use text files for a simple method to connect Excel with the business system or build a business system from multiple Excel workbooks.  The PS Cycles Integrated Planning System has all the functions in one workbook for convenience, but it is easy to see how this can be broken up into modules and spread across multiple users.

Text files are a simple, yet powerful way to build Excel Planning and Reporting Systems.  Who’d have figured so much virtue and heroics coming from such a humble creature?