Bugs Are Everywhere – Don’t Single Out Excel & Access

by

It’s fashionable to dunk on Excel and Access in many quarters.

Take The Register for example. It blames

Excel and Access – or, more specifically, the way in which they were used – may be culpable for the snafus in these specific cases but, based on my personal experience, I wouldn’t denounce these two Microsoft bestsellers in a generic context.

EXCEL

In circa 1995, I was the Sales Manager for a hardware and networking products distributor in the Middle East. Our hottest selling product was Server. As they are even today, the cost of various server components like CPU, RAM, Hard Drive, and Monitor were very volatile at the time. It was imperative for us to rework the costing of our bill of material continually so that we could always offer competitive pricing and thus improve our win rates on government tenders.

To help in this pursuit, the company had developed a rudimentary Configure-Price-Quote tool on Excel. The spreadsheet accepted the part number of the server components, their descriptions and duty paid landed cost (DPLC) as inputs and used a simple ADD formula to output the BOM cost of the fully assembled server.

Part number and description were available in the server manufacturer’s product guide. The DPLC was computed by the company’s centralized inventory management system running on a mainframe and would be periodically circulated to us by way of voluminous reports.

Sales reps would manually enter the part number, description and DPLC from their respective reports into the CPQ tool. At the time, we just called this “manual data entry” but today the practice is known as “Swivel Chair Interface”.

Was a time when accountants at my company would read data from a report that came out of one software and key it in manually into another software. Don’t recall any jargon for it but wondering if Swivel Chair Interface is an improvement over that or what.

(Given that I came across this jargon last January, it appears that this practice is still in vogue even 25 years later.)

I wanted to do better than the status quo.

I learned that Microsoft Office supported a feature called ODBC, which allowed Word, Excel, Access and other Office programs to access data from external sources. I set up an ODBC link on my Excel worksheet to fetch the DPLC data from the mainframe system. This eliminated the need to enter the data manually. I also added a forecasting model to predict future DPLC of the components – this was helpful because government tenders took months to finalize and the input costs would have changed by then.

I spent a couple of days to learn lookup, pivot table, and other advanced features of Excel.

It took me a week to develop and test the spreadsheet and the mainframe interface. Once the tool went live, my team used it to respond to every government tender thereafter. Our pricing reflected realtime costs. Our win rates went up dramatically.

My Excel model worked exactly as advertised and delivered tangible business benefits. I didn’t – couldn’t – write a single line of code. It soon became the talk of the company and many coworkers developed similar models for their own LOBs.

More in 3 Ways To Execute Government Technology Contracts Profitably.

During the subsequent 25 years, I’ve felt the need for a similar forecasting model many times, including as recently as last month.

Everytime, I’ve gone to techies as the first port of call to get it developed the “proper way” i.e. by using a programming language and database. And, everytime, I’ve received answers like “it will take awhile”, “we’ll need a MATLAB license”, and so on, all of which I suspect are geekspeak for “get lost” to an exec who they can’t rebuff outright.

Ergo, if I had to redo such a forecasting model on a tight deadline today, I’d still use Excel.

Yeah, I know it’s 2020 but it’s not my fault that Excel is the oldest “no-code” platform on the planet!

It’s not only me. Per anecdata, 70% of Fortune 500 companies have ERP but 90% of them use Excel to submit reports to their Board of Directors!

ACCESS

Bugs happen in SQL Server, Oracle, HANA, and other million dollar databases.

Take a leading ERP software. In its early days, the vendor, which used Oracle, discovered a strange bug: The inventory management module failed to update the stock status after material was issued from the warehouse. We could not demo the software with this critical defect. Engineering said it would take a couple of quarters to fix the bug. Obviously Sales couldn’t stop selling for six months, so it devised its own workaround.

During demos to prospective customers, the presales consultant would raise a Delivery Note on the system. Had the database worked correctly, the inventory would automatically be deducted by the quantity stocked out via the DN. But it did not and it would not.

The workaround: The consultant would shout “We have raised a Delivery Note for 30 units” thrice.

Many prospects used to wonder why the presales consultant was so excited about such a mundane transaction.

Little did they know that his or her voice had to carry over to the next room where a developer would manually update the stock level by 30 units!

Coming to SAP HANA, in this well documented horror story, German retailer Lidl wrote off €500M because the database couldn’t handle a change in Inventory Valuation from Selling Price (SAP’s standard) to Purchase Price (Lidl’s requirement). More in When ERP Projects Get Derailed By “Silly” Reasons.

Bugs are everywhere. Let’s not single out Excel and Access.

I threw a gauntlet to all Excel and Access haters on Twitter recently:

I challenge the next coder who hates on Access and Excel because they’re buggy to ship bug free code in C or COBOL or Python or whatever, so that I can fire my entire testing team.

No one has taken up my challenge so far.

Having heard senior software engineering leaders of some of the largest enterprise software companies in the world say “putting bugs in code is my birthright”, I’m guessing that no one will anytime soon, either.