Michel Baudin‘s comments:
The issues listed by Chad Smith’s friend are not specific to Excel. His company’s MRP or ERP system does not meet the functional needs of the Planning Department, and its members supplement it by crunching data extracts from it on their personal systems, in their own ways. The manager does not control what formulas are used, and does not know how diligent each member is at keeping the data up do date. The planners happen to be using Excel, but these problems would not be solved if they replaced Excel with any other single-user tool: they should all work on the same data, not individually ordered extracts of inconsistent vintage, and the planning logic should be shared, not buried in private spreadsheets.
By his own admission, Chad Smith’s friend has no solution. The only one he considers is buying a new ERP system, which he has no budget for. The first issue I would recommend addressing is the sharing of the methods and formulas used by the different planners, with the recognition that it is a human, not a technical problem. Chances are, individual planners don’t mind being the only ones to know how to do a task and are not eager to train others to replace them. To them, job security hinges on being a hedgehog, who knows one thing and being the only hedgehog to know it. The company needs to show in concrete terms that it places greater value on foxes, who know many things.
The situation is similar to that of machine shops where, traditionally, operators develop expertise on one type of machines — lathes or milling machines, for example — and never learn to run other types of machines. This is changed by cellularizing the work, requiring operators to attend to multiple machines of different types, rotating operators between jobs, posting a skills matrix on the floor, and factoring each operator’s range of skills in performance evaluation, raises, and promotions. Then hoarding know-how in your head is no longer an option.
Introducing job rotation among planners may not be easy, but it is key to bringing to light what their spreadsheets actually do. Planners can’t use each other’s spreadsheets without sharing information on how they work, and most likely uncover bugs in the process. If it is not one but 19 other planners who eventually have to use your spreadsheet, the explanations have better be in writing, and the least painful way of generating this documentation is by using a private forum where planners can ask questions and their colleagues post answers.
Controlling the data that is input to all the planners’ personal tools is a different challenge, that also has both managerial and technical dimensions. Over decades, the MRP/MPR-II/ERP industry has sold its products as all-in-one solutions that could address all the needs of manufacturers, but, so far, has failed to eliminate the need for application-specific add-ons.
The reason for this is not technology, but the practical impossibility of developing products embodying the requisite domain expertise on subjects ranging from production planning and scheduling to maintenance management, accounting and human resource management (HR). The players in the ERP market have typically started by excelling in one of these niches and expanding beyond it.
One started with multi-currency accounting in the EU, before the advent of the Euro as a common currency; another, as a supplier of general-purpose database management systems; a third one, in costing custom-engineered products; a fourth one, in HR. In all areas other than those they originally succeeded in, the suppliers have struggled to find subject matter experts, recruit them, and follow their direction in product design, with the software developers always having reasons to take simplistic shortcuts rather than implementing the more complex functions the experts were asking for.
Manufacturing companies currently use multiple systems, including personal spreadsheets, and there is no reason to believe that they will ever stop. As a consequence, they need to do what it takes to make the best of this situation, and make these multiple systems play together well, and they need to acquire the requisite skills. Generating extracts from one system to feed the others and individual tools is technically easy but leads directly to the “Excel Hell” of Chad Smith’s friend.
The first, technology-neutral step is to give the same level of attention to the company’s data that a manufacturer devotes to its product designs and production processes. If you use Value-Stream Maps (VSMs), think of it as drilling down into the upper half of the maps, the one intended to show data flows between customers, various parts of the company, and suppliers. I remember being invited to a meeting of department heads in a medium-size plant at the end of a day on site, where the agenda was to “draw a VSM” in anticipation of implementing a new ERP system. No map was drawn, but it became clear that the managers of production, engineering, maintenance, quality assurance, sales, accounting and HR didn’t know what data any of their counterparts needed from them. Before the meeting, implementation of the new ERP system was “a train that had left the station.” Shortly afterwards, it was postponed until the managers could figure out these issues among themselves, unbiased by the preselection of a software product.
This task is less daunting than it looks, because you don’t start from scratch. It is more a matter of customizing existing, generic models to the specific needs of an industry, a company, and a plant. The key is to stay neutral with respect to technology, especially the technology currently in use. If, for example, you have a process planning system for a group of linked machining centers that only allows you to schedule batches of identical parts, it should not prevent you from considering the scheduling of sets of matching parts instead, if it better fits your needs.
The next step is technical. You need to replace the ad-hoc generation of extracts with middleware pulling data from the existing systems, restructuring it to conform with the unified model you developed in the previous step, and making it available to all systems and users in a timely and controlled manner. There usually are at least two families of tools used in parallel, a data warehouse for historical data, and an in-memory database for current status. Neither one is pushing the state of the art; both have been in existence for 25 years.
This approach requires IT skills that are above average for manufacturing companies, but it is orders of magnitude cheaper than attempting to replace all legacy systems, and it is effective. It does not remedy the intrinsic limitations of Excel as a tool, it does not eliminate the need to stay informed of new developments, for example by experimenting with the Internet of Things (IoT), but it makes it easier for management to get simple answers to simple questions out of the company’s data.
Aug 2 2016
Excel Hell – An Insider’s Report | Chad Smith | LinkedIn Pulse
“95% of companies report that they are using spreadsheets to augment their ERP system for planning. I asked a good friend that I have known for 20 years to share his experiences with the proliferation of work-arounds and ad-hoc planning “solutions” that we tend to see in most companies that run MRP. My friend cannot specifically name the products his company makes because the market is dominated globally by only two players (he works for one of them). The sales of this company are between $100M – $500M (US) annually. Read about his experiences and let me know if you can relate.”
Sourced through LinkedIn Pulse
Michel Baudin‘s comments:
The issues listed by Chad Smith’s friend are not specific to Excel. His company’s MRP or ERP system does not meet the functional needs of the Planning Department, and its members supplement it by crunching data extracts from it on their personal systems, in their own ways. The manager does not control what formulas are used, and does not know how diligent each member is at keeping the data up do date. The planners happen to be using Excel, but these problems would not be solved if they replaced Excel with any other single-user tool: they should all work on the same data, not individually ordered extracts of inconsistent vintage, and the planning logic should be shared, not buried in private spreadsheets.
By his own admission, Chad Smith’s friend has no solution. The only one he considers is buying a new ERP system, which he has no budget for. The first issue I would recommend addressing is the sharing of the methods and formulas used by the different planners, with the recognition that it is a human, not a technical problem. Chances are, individual planners don’t mind being the only ones to know how to do a task and are not eager to train others to replace them. To them, job security hinges on being a hedgehog, who knows one thing and being the only hedgehog to know it. The company needs to show in concrete terms that it places greater value on foxes, who know many things.
The situation is similar to that of machine shops where, traditionally, operators develop expertise on one type of machines — lathes or milling machines, for example — and never learn to run other types of machines. This is changed by cellularizing the work, requiring operators to attend to multiple machines of different types, rotating operators between jobs, posting a skills matrix on the floor, and factoring each operator’s range of skills in performance evaluation, raises, and promotions. Then hoarding know-how in your head is no longer an option.
Introducing job rotation among planners may not be easy, but it is key to bringing to light what their spreadsheets actually do. Planners can’t use each other’s spreadsheets without sharing information on how they work, and most likely uncover bugs in the process. If it is not one but 19 other planners who eventually have to use your spreadsheet, the explanations have better be in writing, and the least painful way of generating this documentation is by using a private forum where planners can ask questions and their colleagues post answers.
Controlling the data that is input to all the planners’ personal tools is a different challenge, that also has both managerial and technical dimensions. Over decades, the MRP/MPR-II/ERP industry has sold its products as all-in-one solutions that could address all the needs of manufacturers, but, so far, has failed to eliminate the need for application-specific add-ons.
The reason for this is not technology, but the practical impossibility of developing products embodying the requisite domain expertise on subjects ranging from production planning and scheduling to maintenance management, accounting and human resource management (HR). The players in the ERP market have typically started by excelling in one of these niches and expanding beyond it.
One started with multi-currency accounting in the EU, before the advent of the Euro as a common currency; another, as a supplier of general-purpose database management systems; a third one, in costing custom-engineered products; a fourth one, in HR. In all areas other than those they originally succeeded in, the suppliers have struggled to find subject matter experts, recruit them, and follow their direction in product design, with the software developers always having reasons to take simplistic shortcuts rather than implementing the more complex functions the experts were asking for.
Manufacturing companies currently use multiple systems, including personal spreadsheets, and there is no reason to believe that they will ever stop. As a consequence, they need to do what it takes to make the best of this situation, and make these multiple systems play together well, and they need to acquire the requisite skills. Generating extracts from one system to feed the others and individual tools is technically easy but leads directly to the “Excel Hell” of Chad Smith’s friend.
The first, technology-neutral step is to give the same level of attention to the company’s data that a manufacturer devotes to its product designs and production processes. If you use Value-Stream Maps (VSMs), think of it as drilling down into the upper half of the maps, the one intended to show data flows between customers, various parts of the company, and suppliers. I remember being invited to a meeting of department heads in a medium-size plant at the end of a day on site, where the agenda was to “draw a VSM” in anticipation of implementing a new ERP system. No map was drawn, but it became clear that the managers of production, engineering, maintenance, quality assurance, sales, accounting and HR didn’t know what data any of their counterparts needed from them. Before the meeting, implementation of the new ERP system was “a train that had left the station.” Shortly afterwards, it was postponed until the managers could figure out these issues among themselves, unbiased by the preselection of a software product.
This task is less daunting than it looks, because you don’t start from scratch. It is more a matter of customizing existing, generic models to the specific needs of an industry, a company, and a plant. The key is to stay neutral with respect to technology, especially the technology currently in use. If, for example, you have a process planning system for a group of linked machining centers that only allows you to schedule batches of identical parts, it should not prevent you from considering the scheduling of sets of matching parts instead, if it better fits your needs.
The next step is technical. You need to replace the ad-hoc generation of extracts with middleware pulling data from the existing systems, restructuring it to conform with the unified model you developed in the previous step, and making it available to all systems and users in a timely and controlled manner. There usually are at least two families of tools used in parallel, a data warehouse for historical data, and an in-memory database for current status. Neither one is pushing the state of the art; both have been in existence for 25 years.
This approach requires IT skills that are above average for manufacturing companies, but it is orders of magnitude cheaper than attempting to replace all legacy systems, and it is effective. It does not remedy the intrinsic limitations of Excel as a tool, it does not eliminate the need to stay informed of new developments, for example by experimenting with the Internet of Things (IoT), but it makes it easier for management to get simple answers to simple questions out of the company’s data.
Contents
Share this:
Like this:
Related
By Michel Baudin • Blog clippings • 0 • Tags: Data Warehouse, ERP, In-Memory Database, Job rotation, Middleware