Overlap Tool

This is the first of a series of posts about tools I've built to make life easier. The overlap tool was the first one I built to include SAP code. All of these tools in their current iterations are the property of my former employer, but I built them once and I could build them again. This one in particular was bugging me a few weeks before I was laid off, where I was thinking that I would build it completely differently because of what I know now.

Headline improvement: 1 week of effort to 5 minutes (99.8%)

In 2022-ish, I was the Senior Manager for Work Transfer. I had a team of six Work Transfer Mangers who were handed business plans for dual-sourcing or re-sourcing a collection of part numbers and then charged with executing those transfers within the required timeline and avoiding any gap in supply. This meant that the managers needed to see the number of parts in-stock at our company, incoming supply from the incumbent and future sources, and the timing and quantity of any demand. Our business system SAP had all of this information (in theory) but no way to present it. To make things worse, a given project might include hundreds of part numbers, lead times could be in excess of year, and some parts in stock might be quarantined pending production approval.

One of my work transfer managers (hi TW!) came to me with the problem: his internal customers were worried about security of supply and were asking for frequent updates on the line of balance from incumbent and future sources and the status of the "overlap" where the incumbent was still producing (to maintain supply) and the future source was delivering (to prove out quality and rate-readiness). He couldn't report frequently because compiling that line of balance was taking him roughly a week of work each time.

I started with a lot of Excel formulas: we built the first iteration of the overlap tool where he had to follow a specific set of instructions on the reports to run in SAP, the format to download them, how to paste them into the tool, and then refresh some PivotTables into a report that was suitable for the internal customers. We're now down from a week to 30-60 minutes of work. We'd figured out which reports to run and how to mash them together, but everything was finicky enough that it was very easy to break the tool. Still, major progress! Roll it out to the rest of the work transfer team.

The work transfer team was scattered across the US and Canada, so I convened us a few times per year in-person to talk goals, do some training (we always toured the factory floor), and maintain relationships. During one of those sessions, a different work transfer manager (JY) asked me if the tool could talk directly to SAP and pull the data? I said I didn't think so, but I'd do the research.

Wow, Microsoft VBA could do that! Run a macro recorder in SAP, pull that code into an Excel VBA macro, and Excel could run SAP reports. Up to that point I'd done some very basic macros, but now I had to learn how to code VBA directly, not just record actions or copy/paste code from the internet. In addition, I had to read the SAP code and modify it to add variables, eliminate useless actions like scrolling or selecting, and help the tool deal with unexpected situations. The automation removed the "run the reports exactly this way" and "paste it in perfectly" issues, and Excel could run SAP faster than the user.

The final result was the headline above: about 5 minutes, mostly just drinking coffee while Excel and SAP chugged along and eventually notified the user that the update was complete. The 99.8% above assumes a 30-hour week because I'm sure that TW was spending some time on projects other than the line of balance.

Automating the report also enabled me to add features as the team requested them without retraining. These included identifying the future and incumbent sources (rather than just recognizing the name), fusing in SAP reports about quarantined inventory (which also flagged when it was time to be released!), and how far inside lead time we were. We were able to satisfy many more internal customers because the report was effortless to run for each project and now available roughly weekly or on-demand. Three years later, the overlap tool is still running strong.

If you have any comments, please reach out to me at blog@saprobst.com or this page is cross-posted at LinkedIn and you can leave a comment there.