Expediting Using the Shortage Report
Update schedule change: I'm on hiatus next week and my next post will be April 21.
This is the latest of a series of posts about tools I've built or used to make life easier. I've iterated on these sorts of tools several times, and this particular tool started life as a method to stop SAP from logging me off and losing my information and then evolved into a tool that others could use for complete reporting.
Previous posts in the series:
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 shortage report included Excel formulas, Visual Basic for Applications (VBA) code, and SAP macros.
Headline improvement: from 2 years past-due to within 90 days of on-time delivery
In late 2023, my boss's boss asked me to help out on analyzing and expediting one of our largest military products. Landing gear lead times span multiple US government budget cycles, so our prime customer (the aircraft manufacturer who buys landing gear from my former company and sells to the government) often contracted us for "long-lead material", and then everyone might be in negotiations for months or years while production was ongoing before a price was settled.
The problem that arose was that our customer wanted to fund us for long-lead material, but they wanted to know the schedule impact (even this funding was inside lead time) before they would sign any contract. But before there was a contract, we couldn't put demand in SAP (our enterprise planning system), which would enable us to know how late we were.
How late are we?
Silly guesses using Excel alone
Priority One was getting the long-lead contract signed, because no matter how bad the delivery schedule looked now, failing to start would only make the situation worse. Using previous production lots as a guide and looking at the current state of production, I was able to forecast VERY roughly how late we would be. Most of this was collaborative and in Excel using a downloaded bill of material (BOM), applying a lead time to every purchased part, and then using some fancy formulas to roll up those lead times through the various levels of assembly to reach a top-line number. When each assembly might have tens or hundreds of sub-components and only the one with the longest lead time matters, I used the trick of hard-coding the "parent" (next-level-up) assembly for each component. Then the parent could use MAXIFS to find the longest-lead part labeled as belonging to the parent. Avoiding circular references was another problem to solve, but I either used multiple lead time columns based on the BOM level or some tricks with relative references to avoid them, I forget which.
First decent guess using SAP and Excel
Once we had a "good enough" estimate, we could sign the contract with the customer and start approaching the real answer using SAP. SAP knows the inventory level of each of the thousands of part numbers, and the lead time for all of those parts. Some of the latest parts from the first analysis might have excess inventory which would mitigate any lateness. SAP also takes into account all of the dozens of deliveries on the contract, while our analysis was implicitly about the first delivery only (i.e. SAP analyzes a BOM for every single delivery, we only analyzed the BOM once).
Unfortunately, SAP also makes some heroic assumptions about how good we are at expediting, where if something is past-due to its requirement date already (e.g. a part hasn't arrived yet despite its purchase order delivery date being in the past or a purchase requisition isn't converted to an order when expected) the next-level-up assembly doesn't reflect that lateness.
The first iteration of the report had to account for those issues by identifying any parts late to need, checking which parts were the latest to need, and using those parts to measure the current gap to on-time delivery to the customer's requirement dates.
This was the first big step: our estimate of our deliveries went from "late" to "roughly two years".
Fix it!
Early versions
As we started frantic discussions with Program Management and other people in Supply Chain (I was in Supply Chain but didn't have any suppliers assigned), I started using the SAP/Excel report as my working file. Each week I'd run an updated report from SAP, download it into Excel, and extend any formulas and update the PivotTables. This part of the process could take more than an hour because a) SAP struggled to create and deliver these 100,000-line reports, and b) I had a "lightweight" laptop at the time which really couldn't handle the number crunching I was asking of it.
Once I had the report, I could pull every part number with a requirement date more than 90 days past-due. This helped me focus on the parts that were really driving late deliveries; if memory serves there were 50 or 100 part numbers at first. Then for each of those parts I could dig into SAP to verify that there was an issue and reach out to the suppliers, account managers, commodity managers, etc. to get these parts pulled back in. I also noted any parts on quality hold and talked with the Engineering and Quality teams to get these parts dispositioned as either usable (yay, near-zero lead time!) or scrap (at least we can stop worrying about them). Then once or twice per week I could report our progress to Program Management.
First VBA automation
The delays in having the SAP report run started to bother me. My first solution was to boot up my laptop while still at home and run the SAP report before I left for the office. This meant that the report would be ready when I started the workday, and really the only task was entering a few parameters and hitting the "execute" button. Or was it?
There were really 3 steps:
- Enter parameters and run the report in SAP
- Once the report finished running, tell SAP to save the results to the clipboard or a local file
- Wait for SAP to write everything to the clipboard or .txt file
The biggest problem was between those steps, the computer would sit idle for an extended period while it waited for the report to run or for me to have a moment to check on its status. During that time, the computer could go to standby, resetting the connection with SAP and losing all of my progress (solved by changing the Windows power settings) and an automatic SAP timeout if it idled too long. I still don't know how long that is, but it wasn't much longer than it took to run the report. Big heartache when I would wake up the laptop screen to see all of my data ready for export, but it was covered with an SAP message box saying that the connection had timed out and I needed to log in again.
I had previously written Microsoft VBA code to integrate with SAP, so I wrote a pretty simple VBA macro that just did all three steps automatically. Because the macro would download the SAP report to a .txt file without any further action by me, the problem went away. At the start of the workday, I now had an SAP report that I could copy into my Excel working file to confirm progress and flag problems.
Side note: if I'd known about running SAP reports in the background, I probably would have done that instead of coding myself something custom. I ended up beyond "background" capabilities, but this initial use was perfect for it.
Evolution
Over time, I tweaked the VBA code to make it more effective. Rather than writing the SAP data to a .txt file, I had the macro import everything directly into the Excel file, including updating the PivotTables. I could run the macro for either of two production lots that we were analyzing (having gotten within lead time of a second lot while still analyzing the first). And this was one of my first uses of tools to track how long a macro took to execute, just because I was curious about how long SAP was really taking.
As the weeks went on, we were able to expedite the latest parts to get everything back inside the 90-day window.
Can others use it?
After several months, it was time for me to move on from this project: I wasn't supposed to be the "military expediting guy", and several other programs were asking for similar reports. To support others using the report, I had to write up instructions in the file and make everything require a bit less manual intervention. As the creator of the file, a few quick copy/pastes in the right spot might be second nature to me, but it wouldn't be to the next user.
The biggest issue I ran into was the SAP report itself. Deep in the mists of time (literally ~10 years before) I had set up a custom view for the shortage report in SAP and couldn't seem to restore it to default. I figured out how to get SAP to return a particular preset layout that was close enough to what I needed and adjusted everything to work with that new layout. In addition, the macro automatically changed the SAP output to that layout before downloading everything.
I was able to hand over the file to other users on AK's team and they've been using it to identify the latest parts and pull them back in ever since.
Conclusion
Two big take-aways this time:
- You can't expedite until you know which parts need expediting. It's no use pulling in the part that's four months late if you've got three more that are six months late. You also need to keep validating which parts are late: schedules slip or improve, inventory is adjusted, and this month's production breaks something and steals one of yours instead.
- Tools and analysis are iterative. What started as a bunch of Excel formulas with tons of manual SAP and analysis work eventually because push-button to run a report and highlight the results. But I didn't know how to build the push-button tool without a lot of customer feedback and identifying the pain points. Conversely, I could have kept soldiering on with the manual tool, but by continuing to iterate on it my work got faster and more thorough.
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.
 |
| Can't build an airplane without parts! |
Comments
Post a Comment