ERP and Excel: Part I
Excel is an indispensable tool in modern business. We use it every day. We use it in varying degrees of deftness and aptitude. Some of us use it as a giant calculator while others take advantage of functions, pivot tables, macros, and visual basic. We use it to handle small amounts of data to creat files so large they exceed the limits of some email services (thankfully there is dropbox and other file sharing applications). We use it to draw all kinds of graphs. We cut and paste part of spreadsheets and graphs from Excel to Word and PowerPoint.
As helpful and indispensable a tool as Excel is, there are good uses and there are certainly abuses. For purposes of this posting, we will consider the use of Excel in conjunction with ERPs. ERPs such as SAP and Oracle are designed to manage, in a connected way, the transactions of a business. They provide an integrated data structure from transactions to the general ledger to financial reporting. The main benefit from our perspective is data integration and allowing the system to handle the bulk of the transactions and people to manage exceptions. The people then work to minimize the exceptions over time through process improvements and sound data management.
The proper use of Excel is to download data from the ERP, perform analyses, and create reports beyond the standard reporting of the ERP. Let's say we were interested in increasing the minimum order threshold from X to X+Y in dollars. How many orders and customers would be affected by this decision? How many of these would be forced to go to distributors and how many would simply order less frequently? We would have to download orders by customers for a period of time, probably a year or two. We would have to determine order profitability either from the ERP if possible or estimate it somehow in Excel. Then we could answer the question to determine the amount of sales that would be moved from direct sales to fulfillment through a distributor in the X+Y scenario and make a business decision.
The not so good use of Excel is to plan or execute transactions especially when these are functionalities available in the ERP. We had the opportunity to make a presentation at the Business Forecasting 2012 conference in Chicago June 15-16. During our presentation to an audience of about 50 people, we asked who was using Excel in their planning activities. Over three quarters of the audience raised their hands. We followed up by asking those that raised their hands, how many had an ERP that had planning modules for the same tasks they were using Excel. About half of the people raised their hands. The people that raised their hands the second time need to use their ERP for planning. By using Excel they are forced to maintain the Excel program(s) and the data that drives their Excel planning models. They are downloading data from the ERP, executing the planning activity, and then uploading the data back to the ERP. This is a task that should be done by the ERP. Downloading data to Excel, manipulating the data, and then uploading the result back into the ERP requires too much human involvement. Too much human involvement leads to errors. Managing the data inside the ERP is daunting enough, managing the same data twice is NOT highly recommended. If you are doing this, fix the ERP and make it work to your satisfaction, and wean yourself off of Excel.
Does your use of Excel with your ERP need improvement? Share your challenges, solutions, and lessons learned.
Reader Comments (1)
Great blog Mark! I think we can all learn from this.