ROI Case File No.435 'The Invoice with Forty Right Answers'
The Invoice with Forty Right Answers
Chapter 1: The Organization That Stops at Month-End
"From the 25th through the end of every month, our three accounting staff can do nothing else."
The director of business administration at SunPower Solutions displayed a desktop screen packed with Excel files. Filenames read "Dealer_A_Commission_Feb," "Dealer_B_Commission_Feb_Rev3," "Dealer_C_Campaign_Bonus_Final"—and so on.
"We sell residential solar power systems. Annual revenue is approximately 8.5 billion yen. We don't sell directly; we deliver to customers through a network of authorized dealers nationwide. We currently have forty-seven contracted dealers."
The administration director opened one of the Excel files. The workbook was divided into twelve sheets, each embedded with a different set of formulas.
"The problem is that each of the forty-seven dealers has a different commission structure."
"How different are they?" I asked.
"There are broadly four commission types. First, a tiered system based on sales volume—8% for up to five units per month, 10% for up to ten, 12% beyond that. Second, a flat-rate system based on sales value—a uniform 9% of revenue. Third, a recurring model—a fixed monthly payment that continues as long as the post-installation maintenance contract is active. Fourth, campaign bonuses—additional incentives for selling specific products during specific promotional periods."
"Four types, each applied with different conditions to forty-seven dealers," Claude summarized.
"Exactly. On top of that, campaigns change every season. A spring new-lifestyle campaign, a summer energy-saving campaign, a year-end rush campaign—each with different eligible products and bonus rates, and their periods sometimes overlap. To calculate a given dealer's commission for a given product in a given month, you may need to layer up to four rules."
"And this is all done manually in Excel," Gemini confirmed.
"Yes. Three accounting staff members calculate commissions one dealer at a time from the 25th through the end of the month. Calculation takes about three days, verification one day, and sending detailed statements to dealers one day. That's roughly five business days—the entire last week of the month consumed by commission calculations."
"How frequent are errors?" I asked.
The administration director's expression darkened. "Last month, four calculation errors were discovered. One was an overpayment to a dealer—420,000 yen. Another was an underpayment—180,000 yen. The underpayment triggered a strong complaint from the dealer. It's a trust issue."
"What's the total annual commission payout?" Claude asked.
"Approximately 680 million yen. That amount, managed by three people in Excel. A single formula error can produce a discrepancy of hundreds of thousands of yen."
This went beyond an efficiency problem. In a web of forty-seven intertwined calculation rules, unless you identify the process bottleneck, implementing a cloud system would merely replicate the complexity in a new environment.
Chapter 2: Five Steps to Find the Constraint
"Before organizing forty-seven commission structures, let's identify the bottleneck in the overall process."
Gemini wrote five steps on the whiteboard. TOC—Theory of Constraints.
"TOC," I began, "is a theory proposed by Israeli physicist Eliyahu Goldratt. Its core is remarkably simple: every process has a single bottleneck—a constraint—that determines the throughput of the entire system. No matter how much you improve everything else, the whole doesn't get faster. First, identify the constraint. Then, exploit the constraint to its fullest. Then, subordinate everything else to the constraint."
"Is there a bottleneck in our commission calculation process?" the administration director asked.
"There always is," Claude answered. "The commission calculation currently takes five business days. But those five days aren't being used evenly. You need to identify which step takes the most time and which step generates the most waiting time."
[Step 1: Identify the Constraint]
"First, break down the commission calculation into process steps," I requested.
The administration director listed them. "Step 1: extract each dealer's monthly sales data from the sales system. Step 2: cross-reference sales data against the commission table and determine which rules apply. Step 3: calculate commissions based on the applicable rules. Step 4: apply campaign bonuses. Step 5: verify the calculations. Step 6: prepare detailed statements and send to dealers."
"How long does each step take?" Gemini asked.
"Step 1, about three hours. Step 2, about eight hours. Step 3, about six hours. Step 4, about five hours. Step 5, about seven hours. Step 6, about three hours. That's roughly thirty-two hours total—five days split among three people."
"Step 2—rule determination at eight hours," I pointed out. "That's the longest. Why?"
"Because you have to reference each of the forty-seven dealers' contracts and determine, one by one, which rules apply to this month's sales data. Especially in months when multiple campaigns overlap, the judgment of which campaign takes priority becomes complex—"
"That's the constraint," Claude declared. "The eight hours spent on rule determination is the bottleneck. Unless you shorten this step, no amount of efficiency improvement in the other steps will significantly reduce the overall time."
[Step 2: Exploit the Constraint]
"Once you've identified the constraint, the next step is to find ways to exploit it to the fullest," I explained the second step.
"Let's dig into why rule determination takes eight hours," Gemini prompted. "Where are the forty-seven dealers' contract terms recorded?"
"In paper contracts and an Excel commission table transcribed from them. But the table is manually updated every time a campaign changes, so sometimes we can't tell which version is current."
"In other words," I pointed out, "the eight hours for rule determination aren't caused by rule complexity alone. The fact that rules are scattered across multiple locations and it takes time to identify the latest version—that's the true bottleneck."
"Exploiting the constraint means," Claude made concrete, "first, consolidating all rules for forty-seven dealers into a single master table with version control. Rule changes are reflected only in this master table, and management via individual Excel files is eliminated. This alone should cut rule determination workload in half."
[Step 3: Subordinate Everything Else to the Constraint]
"The third step," I continued. "Redesign the surrounding steps to serve the constraint."
"Step 1—sales data extraction is currently manual," Gemini pointed out. "Change this to automatic extraction, outputting data in a format the master table can easily reference. In other words, align step 1's output format with step 2's input requirements."
"Step 4's campaign bonus application is the same," Claude added. "Integrate campaign information into the master table and merge step 2's rule determination with step 4's campaign application into a single step. They're separated now, which creates duplicate judgment work."
[Steps 4 and 5: Elevate the Constraint and Repeat]
"Step 4 is about elevating the constraint's capacity itself," I explained. "After consolidating the master table to streamline rule determination, the next phase is implementing the determination logic as a rules engine on a cloud system. Enter the dealer code and sales data, and the applicable rules and commissions are calculated automatically."
"And step 5," Gemini concluded. "Once the constraint is resolved, a new bottleneck will emerge. When it does, return to step 1 and identify the new constraint. TOC is not a one-time improvement—it's a perpetual process of finding constraints."
Chapter 3: Taming Complexity
The administration director stared at the five steps drawn on the whiteboard.
"I thought a cloud system would solve everything. But if you implement a system without identifying the bottleneck, the complexity of forty-seven sets of rules simply migrates into the system."
"The essence of TOC," I responded, "is not trying to improve everything at once. If you tried to standardize all forty-seven sets of rules simultaneously, negotiations with dealers alone would take a year. But if the bottleneck is the scattered location of rules, then start with consolidating the master table. Resolve one constraint, find the next, resolve that one too. This repetition tames complexity in stages."
"For implementation," Claude proposed, "phase one: consolidate all forty-seven dealers' commission rules into a master table. Duration: one month. Phase two: automate sales data extraction and merge the rule determination and campaign application steps. Duration: two months. Phase three: build a rules engine on the cloud. Duration: three months. Execute on a six-month roadmap, progressively elevating the constraint."
"For the pilot," Gemini added, "select four representative dealers—one for each commission type—and cross-check the master table calculation results against the traditional Excel results. Verify zero discrepancies over a one-month period before expanding to the remaining forty-three."
"And," I reminded, "continue recording the total hours spent on monthly commission calculations, broken down by step. Which step shortened by how many hours. Which step became the new bottleneck. This record becomes the compass for running TOC's five steps."
The administration director stood and bowed deeply. "Thank you. This week, we'll start by inventorying all forty-seven dealers' commission rules."
Chapter 4: When Month-End Becomes an Ordinary Day
After she left, Claude said, "I always associated TOC's five steps with manufacturing, but they're effective for back-office operations too."
"Indeed," I answered. "What TOC teaches is that every complex problem has a single bottleneck. Forty-seven commission structures are certainly complex. But you don't need to resolve all of that complexity at once. Break the process into steps, find the single point that takes the most time, and focus there. When one constraint is resolved, the next becomes visible. This pattern of 'focused effort and repetition' is the power of TOC, and it transcends industry and function."
Gemini added, "And constraints aren't fixed. If master table consolidation speeds up rule determination, then the verification step might become the next bottleneck. When that happens, you return to step 1 and identify the new constraint. Recording this cycle and accumulating it as a pattern—that's organizational reproducibility."
Outside the window, solar panels lined the rooftops of a residential neighborhood, catching the evening sun.
Five months later, a report arrived from SunPower Solutions.
Phase one—the master table consolidation—was completed in one month. Commission rules for all forty-seven dealers were classified into four types and integrated into a single spreadsheet. At that point alone, rule determination workload dropped from eight hours to three.
For phase two, they implemented automatic sales data extraction and automated rule determination. Campaign bonuses were also integrated into the master table, merging steps 2 and 4 into one.
At the five-month mark, total commission calculation hours had been reduced from thirty-two to nine. The work that had taken five business days now finished in a day and a half. Calculation errors dropped from four per month to zero. Complaints about overpayments and underpayments vanished.
But the change the administration director valued most was the month-end atmosphere. Previously, the last week of every month saw all three accounting staff consumed by commission calculations, unable to respond to inquiries from other departments. Now, month-end proceeded with normal operations uninterrupted. "Month-end stopped being a special period"—and that transformed the entire organization's work rhythm.
At the end of the report, the administration director wrote: "We've begun applying TOC's five steps not just to commission calculations but to invoice processing and budget planning as well. Every operation has a single bottleneck. Finding it, resolving it, and searching for the next constraint—this cycle has changed the very way our accounting department works."
The invoice with forty right answers was, atop a single master table, quietly producing the correct answer every time.
"When facing complex operations, there's always the temptation to solve everything at once. But what TOC—Theory of Constraints—teaches is that a single bottleneck determines the throughput of the entire system. Break the process into steps, identify the single point that takes the longest, and focus there. Once the constraint is resolved, the next one appears. Recording that cycle and accumulating it as a pattern—that is the reproducible approach to conquering complexity."
Related Files
