How to Find Risky "Informal Systems" and Migrate Them Into the System of Record

My company has been working with two organizations that use large, unwieldy Excel spreadsheets to manage some of their most critical business processes. These “informal systems” (which I wrote about in my previous article) have been around a long time, growing and becoming more complex. The people who use them are scared to make changes. And that fear is justified: One client lost six months of critical data when a frozen computer required an unexpected reboot.

In another engagement, an ERP initiative was implemented for—and led by—one department. Other business units weren’t consulted for system selection or rollout. And in both cases, those departments were left to their own devices to determine how to select and manage suppliers and supply chains, transportation, warehousing and logistics. Their tool of choice became the spreadsheet.

Most of us know Excel is an excellent tool for massaging data, analyzing and experimenting. We also know it simply wasn’t designed to do the work of enterprise systems, like today’s ERP, CRM and SCM platforms. But there are probably spreadsheets managing critical business processes and storing confidential data in your organization. They put your business at risk of security incidents, compliance issues and poor decisions based on bad information. Here are a few steps you can take to help your company break free.

Follow The Process To Find The Files

Sometimes, leaders know where these spreadsheets live and why they’re being used. Often, however, they’re brought to light during business process workshops taking place as part of an existing initiative. In these workshops, you should assemble a cross-functional team representing each area involved in a particular process, for example, new product development (NPD), procure-to-pay (PTP) or order to cash (OTC). Then walk through the process, discussing the people, activities, data and tools involved at each step—“tools” meaning all tools, including these spreadsheets, which become candidates for migration. Catalog them as they’re revealed, so you can examine them more closely.

Learn More About Them

Once identified, you should interview key stakeholders to identify the spreadsheet’s primary purpose, making note of processes that could be performed in an existing enterprise system. What is the user trying to accomplish? Is the spreadsheet for gathering data, transformation, analysis or reporting? What are its inputs and outputs? Who owns and updates it? Who relies on it? Do people use it daily, weekly or monthly to do their jobs? Regular use is often an indicator it should be migrated.

You should also identify what information it contains, looking for personal data and proprietary company information because these files are rarely password protected. Find out who created it and when. An older spreadsheet may have been created for people who are no longer around, and perhaps the “second generation” maintainers may not have a vested interest in continuing the practice. In some cases, workarounds have been created because certain functionality either didn’t exist in an enterprise system or was not as mature as it needed to be, yet those needs could be met easily by a system of record today.

Find Out Why Existing Systems Aren’t Being Used

Employees can find many reasons to work outside your systems of record—usability issues, lack of training, aversion to change or simply a preference for working in Excel. Use root cause analysis to get to the heart of the problem, asking questions like, “Why was this workaround created?” “Does the need it fills still exist?” “Do the people who created and cared about it still work in the organization?” Use the “five whys” (or more) approach: Keep asking “why” until you really understand the problem. The “why” will help you get to the “how” to solve the problem—to identify existing systems that can meet the needs and analyze gaps where they cannot.

Create A Migration Plan

Finally, you should create a prioritized list of spreadsheets to be transferred into existing systems and a plan for that work. This may involve configuration changes or development and will always require planning for data migration. With smaller spreadsheets, you can often manually input data or start from scratch if history isn’t important. With larger spreadsheets or to maintain history, you should define a migration plan that analyzes, cleans and converts the data appropriately. In the case of spreadsheets that are used for reporting and analytics, leverage your company’s business intelligence (BI) solution, which usually provides reliable and highly useful drill-down capabilities that are hard to replicate with a spreadsheet.

There will be spreadsheets that continue to live outside the systems of record. So, you may remove some information or limit access to reduce risk. You could also set up data connectors to pull data from the systems of record for use in analysis and reporting. For example, for a monthly planning process, you might set up the spreadsheet to pull data automatically from the system of record through a vendor-sanctioned data connector; the spreadsheet uses those pulled data elements to show a calculated view of what the users need.

Change management is important, so provide training, documentation and any additional support that can smooth the transition. It’s smart to ensure the use of the newly designed process by making the old spreadsheet read-only or having it display a message to use the system of record. It’s also helpful to check back with users regularly to identify issues. After an adjustment period, you can archive the spreadsheet.

Realize The Value Of Breaking Free

Running processes outside your systems of record is overhead, and all of us are trying to eliminate waste. By moving these spreadsheets into enterprise systems, your organization can benefit from a reduction in manual workarounds that take time and lead to human error. You may also avoid dire consequences that manual input can lead to, such as security breaches or loss of critical data.

 

This article was originally posted on Forbes.com