ROI Case File No.525: Every Time a Machine Changed, They Rebuilt the Macro
Every Time a Machine Changed, They Rebuilt the Macro
Chapter 1: Every Time a New Machine Arrives, We Start Over From Zero
"Every time a manufacturing machine is renewed, we rebuild the data-conversion macro from scratch."
Haruhiko Karasuma, production engineering department manager at TechInnovate, said this while opening several text files. Each machine had a different output format. The delimiter, the order of items, the number of digits—all different. "If the machine's software differs, the output format differs. To convert it into our company's specified format, we build an Excel macro by hand every time. When a new pattern appears, we rebuild it again. There's no end to it."
"How often does conversion work arise?" Claude asked.
"Daily," Karasuma answered. "We convert every time data comes out of a machine. When a new format appears, the work stops until someone rebuilds the macro. The people who can build macros are limited, so without that person, things don't run. It's personnel-dependent."
"I understand you tried to build your own with generative AI?" I confirmed.
"We tried," Karasuma answered. "We had generative AI write a simple conversion tool. It works—it does run. But I can't be confident in the coding's accuracy, and I'm anxious about the security side. Manufacturing data can't leave the company. Entrusting our operations to a half-baked in-house tool is frightening, so we've started considering outsourcing."
"You need to design not just for the technical requirements but including the internal culture and the circumstances of the sites," I responded. "Let's break it down with CAGE."
Chapter 2: CAGE Asks About Four Distances
"This case needs CAGE."
Claude wrote "Culture, Administration, Geography, Economics" on the whiteboard.
"CAGE—the CAGE framework—is a technique proposed by Ghemawat that grasps the gap between two parties in four distances: cultural, administrative, geographic, and economic," I explained. "It's originally used for analyzing international expansion, but it transfers to internal system development. Because TechInnovate's problem isn't a mere technical issue—it tangles up four distances: the internal culture around data, industry institutions, the geographic dispersion of sites, and the cost structure. Design with technology alone, and you'll stumble on these distances."
"First, let's measure the current cost," Gemini said, opening ROI Polygraph. He entered the data Karasuma had provided.
"The monthly conversion cost came out," Gemini read aloud. "Data-conversion labor averages 180 hours a month; at an hourly rate of 3,800 yen, that's 684,000 yen a month. The labor of rebuilding macros when a new format arises averages 60 hours a month, or 228,000 yen. The expected value of the work-stoppage risk from macro-building being personnel-dependent averages 500,000 yen a month. The cost of input errors and re-conversion during manual conversion averages 300,000 yen a month. The expected value of the latent risk from the in-house tool's security anxiety averages 400,000 yen a month. The total is 2,112,000 yen a month. Annualized, that's about 25.34 million yen."
Karasuma stared at the figures. "I thought it was only the conversion labor. I had no idea the personnel-dependent work-stoppage risk and the in-house tool's security anxiety would become numbers too."
"Then let's design with CAGE," I continued.
[Culture—An Internal Climate That Prizes Data Accuracy and Security]
"First, Culture," Claude said. "TechInnovate's internal culture prizes data accuracy and security to an extreme degree. Manufacturing data is the basis of quality and can't leave the company. To fit this culture, the conversion system must guarantee high accuracy and run in a closed environment. A design that closes the distance to the culture is the premise for trust."
[Administration—Industry Regulation and Legal Requirements]
"Next, Administration," Gemini continued. "We confirm the industry regulations and traceability requirements concerning the handling of manufacturing data. A mechanism to ensure no tampering occurred during conversion, preservation of conversion logs—the system design must comply with institutional requirements. Ignore the distance to institutions, and you won't pass an audit later."
[Geography—Consistent Operation Across Multiple Sites]
"The Geography perspective," I continued. "The manufacturing sites are dispersed across multiple regions. If the same conversion system can't be used at each site, the current problem of macros proliferating by site recurs. By designing so the same conversion platform runs regardless of site, we resolve the geographic distance."
[Economics—Cost Optimization With Long-Term Maintenance in View]
"Last is Economics," Claude continued. "We judge not by development cost alone but by ease of long-term maintenance and updates. A design where remodeling fees swell every time a new format appears merely replaces macro hell with a system. We give it extensibility to absorb new patterns through configuration, maximizing long-term cost efficiency."
[A Design Policy Integrating the Four Distances]
"With the four distances in mind, the design policy settles," I continued. "A conversion platform that runs in a closed environment, preserves conversion logs, can be operated commonly across all sites, and absorbs new formats through configuration. Not just the technical specifications—a design that closes the distances of culture, administration, geography, and economics produces a system that is actually used."
[Estimating the Investment Recovery]
"Let's estimate with ROI Proposal Generator," Gemini proposed.
- Initial cost: Automatic conversion system development, closed-environment construction, conversion-log preservation function, multi-site rollout design, new-format absorption mechanism, and field training—6,800,000 yen total
- Monthly cost: System operation plus maintenance ongoing cost—220,000 yen a month combined
- Monthly reduction effect: Conversion labor reduction = 550,000 yen a month (assuming 80% reduction), macro-rebuild reduction = 200,000 yen a month, reduction of personnel-dependent work-stoppage risk = 400,000 yen a month, input-error re-conversion reduction = 250,000 yen a month—1,400,000 yen a month total
- Monthly net reduction: 1,400,000 yen − 220,000 yen = 1,180,000 yen a month
- Payback period: 6,800,000 yen ÷ 1,180,000 yen = about 5.8 months
"Recovery in just under half a year," Gemini summarized. "What matters is the mechanism that absorbs new formats through configuration. Because of it, we can sever the structure where development fees arise every time a machine changes. We avoid the failure of merely moving macro hell into a system."
Karasuma confirmed the figures and said, "I'd thought choosing a development firm with technical skill would solve it. I didn't have the idea of weaving the culture, the institutions, and the circumstances of the sites into the design."
"CAGE is a tool for not overlooking the distances beyond technology," I responded.
Chapter 3: A Development Plan Grounded in the Four Distances
"Let me organize how we'll proceed," I said, standing before the whiteboard.
"Months one and two—a full inventory of existing formats, systematizing the conversion rules, and defining the requirements for the closed environment. Months three and four—designing the conversion platform, implementing the new-format absorption mechanism, and building the conversion-log preservation function. Month five—pilot deployment at one site, verifying accuracy and security. Month six—rollout to multiple sites and arranging common operating rules. Month seven—starting production operation and fully migrating from in-house macros. Month eight onward—making configuration-based operation take root when new formats arise and establishing the maintenance structure."
"How should we choose a development firm we can trust?" Karasuma confirmed.
"We translate the four distances into requirements and select on them," Claude responded. "Do they have a track record with closed environments? Do they understand manufacturing-industry institutional requirements? Do they have experience with multi-site rollouts? Is the long-term maintenance cost structure clear? Choose a vendor on technical skill alone, and you'll stumble on the distances of culture and institutions. CAGE's four axes become the selection criteria as they are."
Taking notes, Karasuma said, "I could put into words why I'd been anxious about the tool we built ourselves with generative AI. The technology worked, but it couldn't satisfy the cultural distance of security."
Chapter 4: The Day It Doesn't Stop, Even When the Machine Changes
Nine months later, a report arrived from Karasuma.
Data-conversion work was cut 80% versus before, three months after the automatic conversion system went live. "When data comes out of a machine, the system automatically converts it into the specified format. The scenes where a person moves their hands have nearly vanished," Karasuma wrote.
Handling new formats also changed dramatically. Even when a new machine came in, there was no longer a need to rebuild the macro. "Before, work stopped for several days every time a new machine arrived. Now we just add a conversion rule on the configuration screen. Handling finishes in half a day," the report said.
The biggest change appeared in resolving the personnel dependency. The structure where things didn't run without the specific staff who could build macros disappeared. "Because conversion changed to configuration operations, anyone can handle it. The tightrope of 'work stops when that person is off' is over," Karasuma wrote.
Security anxieties were also resolved. With a design that runs in a closed environment and preserves conversion logs, they could be confident in handling manufacturing data. "The vague anxiety I'd felt about the tool we built ourselves with generative AI is gone. We can explain the conversion process even in an audit," the report said.
Consistent operation across multiple sites was also realized. With all sites using the same conversion platform, the problem of macros proliferating by site was eradicated. "The recognition of data formats aligned across sites. The reassurance that the numbers seen at headquarters and the numbers in the field pass through the same conversion rules is significant," Karasuma wrote.
As a secondary effect, secondary use of converted data began. With formats unified, converted data could now be routed to analysis. "Until now we could barely manage just to convert. Now, using converted data, we can do trend analysis by machine," the report said.
At the end of Karasuma's report, he had written: "I'd simply thought asking a firm with technical skill would solve it. When I looked at the four distances with CAGE, I learned we needed to satisfy not just technology but the security culture, the institutional requirements, the site dispersion, and the long-term cost—all of them. Overlook a distance, and you get a system that runs but goes unused."
It was recorded that the day a production engineering department that had rebuilt macros every time a machine changed gained days that don't stop even when the machine changes, data conversion changed from endless manual work into a mechanism that absorbs through configuration.
"System-development consultations tend to lean toward technical requirements—the belief that choosing a good development firm will solve it. But a system that is truly used takes root only once it satisfies the distances beyond technology. What CAGE asks is the four distances—culture, administration, geography, economics. An internal culture that prizes security, the industry's institutional requirements, the geographic dispersion of sites, the cost of long-term maintenance—even if the technology runs, it won't be trusted if these distances remain. The day a field that had rebuilt macros every time a machine changed gained a mechanism that absorbs through configuration, what changed was not the conversion tool but the very way the distances were closed."
Related Files
Tools Used
- ROI Polygraph — Visualizing conversion labor, macro personnel-dependency risk, and the security anxiety of in-house tools
- ROI Proposal Generator — Investment-recovery simulation for an automatic conversion platform grounded in the four distances
