What's your deal?
Your company doesn't have an enterprise resource planning (ERP) system. It can't afford one or it just can't justify the cost of implementation with the current volume of sales. You're still expected to maintain an updated inventory report, monitor performance, address process bottlenecks andtrendcustomer service claims. Did you know your company likely has the resources to cut 50% of the current administrative and data collection labor, automate your reporting tasks and much more?
Cell highlighting, merging and sorting is the extent of the average Microsoft Excel user's knowledge. Some dig into plotting data to study trends, aggregate values in a pie chart, concoct formulas longer than the screen can display on one line and get quick with filters. Fewer will use named ranges in a VLOOKUP function or to set data series in achart. Only the most ambitious are automating data collection from RS-232, Bluetooth and USB connected devices; creating applications that import delimited values from data logging equipment, analyze the data and generate graphs or reports; building graphical user interfaces that prompt for information, print labels, generate QA checklists and store data in a record that other departments use for making sales, reporting performance, paying suppliers and collecting debts. They're doing all this with Excel and Visual Basic, both standard features of Microsoft Office.
Figure 1: A label printing application.
What is the true value of creating these applications?
Rapid and inexpensive application development that sets the foundation for future growth. If an organization lacks effective, homegrown data management tools, an ERP system won't help get it where it needs to go without a lot of unnecessary spending in the design process. The organization doesn’t really know what it needs or,equally important, what it doesn’t need. ERP system configurations and modifications are not cheap, so it's in the organization's best interest to know what functionality is required to succeed before shopping for an ERP system and then let the ERP consultant determine how to achieve or enhance that functionality by using standard features of the ERP software.
ERP systems automatetasksand will amplify the effects of an efficient or inefficent existingprocess. In companies embarking on the journey to ERP, you'll often hear someone respond to inquiries about the current convoluted processes with, “just wait until the new system goes live.” They're expecting the ERP consultant to come in, build everything according to a wish list of options that sounded nice to have and then sit back to watch operation efficiencies go through the roof. The reality is that if a company can't demonstrate efficient flow of information throughout its organization, the consultant is going to do their best to understand the needs of the company and then build a system that they feel is suitable based on experiences with previous clients. Sometimes this works. When it doesn't, because many companies have some unique needs, the cost of modifications after implementation may have your 100 employee operation going back to that old spreadsheet they used to use until the funds free up next quarter... or the one following that.
Figure 2: A data analysis program that imports data from an comma delimited file, performs various calculations and generates graphs.
What can your company do to prepare?
Developing prototype applications using Excel is an inexpensive way for companies to hone in-house processes and determine exactly what they need before migrating to a powerful, yet costly, ERP system. When working with ERP consultants, the most difficult part of the entire implementation process is mapping out how information moves through an organization. What triggers the creation of a sales order after a deal has been made with a customer? How does that information get to the warehouse? How did the sales department know that product was available to sell? I've seen companies create inventory reportsand email that information to everyone in the sales department once per week. On Monday, one person sells the entire inventory of Widget A because they were in the latest static inventory report. Tuesday, another team member makes the same sale of Widget A to differentcustomer based on the out of date inventory report. Both contracts promised a 2 week lead time. The warehouse ships one order, finds that there's no inventory to fulfill the second and the lead time to manufacture enough Widget A to fill the order is 8 weeks. The customer files a claim because the company breached the contract and now the profit margin takes a hit. When that company sat down with an ERP consultant to discuss the flow of information through the organization, neither party knew where to connect the dots in any of their disjointed, departmentally isolated processes because the company had never made the effort to do so on their own. Does everybody know why they keep pushing out the "go live" dateor why they’re taking a fine tooth comb to that wishlist of ERP nice-to-haves? They didn’t know what they needed from the beginning and figuring it out used 250% of the budgeted consulting time for that phase of the project. Remember that custom label generating application that you wanted; the one that was going to reduce picking errors from illegible, hand written labels? That'll have to wait until Q4 of next year because we're way over budget.
If these Excel applications are so great, why does a company need an ERP system?
Excel will quickly get you to a minimum viable product with a return on investment of just a few months. The exercise of creating these applications will reveal bottlenecks and process inefficiencies as tasks are automated. See it as a sort of prototyping stage. As Visual Basic code becomes more complex and the amount of data stored within a workbook increases, limitations of the prototypebecome apparent. Applications run slow, simultaneous user access creates network issues, etc. By that point, however, the company knows what it truly needs the ERP system to do and there's proof the ERP system will be effective if it mimics or improves on the minimum viable product. The consultant studies the minimum viable product, employs the functions of the much more powerful ERP system and configures the system to get the company to that next level of operational excellence with glorious, amplified efficiency.
Figure 3: A shipment tracking system. Users input data in this form and click the green button. Form data is inserted as a row in a spreadsheet with previously entered data.
Are you ready to EXCEL?
Everyone who enters data into a spreadsheet, hand writes a label, fills out a form, generates a report or performs any repetitive administrative task can greatly reduce the cycle time by scrutinizing their processes and getting crafty Excel and Visual Basic. If you are not taking advantage of everything this software has to offer, you are losing money and wasting time. Get with the program (geek pun) or find someone who is. Whether your company is gearing up to implement its firstERP system or just trying to streamline a few time consuming tasks, rapid application development with Excel and Visual Basic should be a milestone this year.
Figure 4: The user interface for an application that generates quality control checklists based on form selections. This was a prototype quality control system for a company that was preparing to implement an ERP system.
