Purchase Requisitions and Past-Due Burndown

In addition to continuing my series on tools that I've built or used to make life easier, this post also lets me associate a blog post with every accomplishment on my resume for the past two roles. This project spanned a year of iteration and improvements as we turned chaos into data and then data into actual improvements.

Previous posts in this series:

Headline: 3,000 parts / 11,000 purchase requisitions tracked

The purchase-requisition tracker ended up as one of the most iterative projects I've worked on so far. It started with a senior-management directive to quantify how many purchase orders we were late to place, and develop a burn-down plan for those past-due orders. At this company, our SAP system would review all demand for a part (assemblies that used the part, orders, safety-stock, etc.) and all supply for that part (planned and in-work production, purchase orders, inventory) and if demand exceeded supply it would generate a "purchase requisition" that would say "on this date, we need X quantity of these parts, so taking into account lead time someone needs to order it by [date]." Past-due or "red" requisitions were ones where the "need to place an order" date was in the past. The part itself might not be late yet, but we were inside lead time.

Iteration 1: find the data and make it useful for me

Asking around, I found that the affected Director of Procurement (KB) had an Excel tracker that she refreshed weekly with most of the data I needed. Account managers added comments and categories which let me estimate when a particular requisition might close. KB allowed to add some columns to her summary sheet which let me put the data in the format I needed to generate the charts for senior management. But in this process KB asked me to help her with the week-over-week update of the file: she would pull the data from an SAP Business Objects report, but getting all of the comments over from the previous week was a pain, as well as identifying new requisitions not in the previous report.

Iteration 2: make it easier (but slow) for the original users

Making the updates more automated wasn't too difficult at the start. I set up the tracker so that I could open the file, save it as a new version (to preserve the old data), open the Business Objects report, and run a macro that would import the data into her summary sheet. The macro ran very slowly both because of the amount of data (roughly 11,000 lines each week with 26 raw-data columns and many more calculated ones) and because the tool had to transform the data from purchase requisitions into part numbers. Business Objects and SAP in general reported on requisitions, but supply chain Account Managers worked by part number: whether a purchase order fulfilled one requisition or 20, they had to perform the same tasks. Rather than just run a PivotTable or UNIQUE formula, I had the macro add and delete lines where needed so the links between the parts and their comments would be preserved.

Still faster than it used to be! Punchcards warehouse 1959

This early iteration was buggy and we kept finding tweaks that we wanted to make to senior-management reporting, Director-level reporting, and just usability. I often ran the macro before KB did just so I could try to catch any errors and fix them before she encountered them.

Iteration 3: speed it up and expand the data

As the Procurement team kept using the file and I learned more about what KB was doing manually after the macro ran, I kept adding enhancements. The biggest one in terms of code structure was changing how the macro pulled the data from Business Objects. Initially, it had filtered and copied the relevant data and pasted it onto the Excel sheet, and then made any adjustments necessary directly on the sheet (e.g. the source data came from two different tables with slightly different column counts and these needed to be aligned). In another project, I'd learned how much faster arrays were so I had the tool slurp up all of the source tables into a giant array, manipulated the array in VBA, and only after that was done did I drop it into the spreadsheet. Much faster!

This also allowed expanding the data sets that went into our tracker: senior management wanted to understand why purchase requisitions had closed the previous week. SAP requisitions had a ton of churn: some requirements would get new numbers each week while remaining identical, some would disappear without any obvious reason, and some would retain their requisition numbers but pull earlier or push later so that they might be outside our analysis window. I had to pull additional reports from SAP to try to categorize each of the ~3000 parts each week (obviously with some formula rules rather than manually), and that meant some of the time saved with arrays went to additional reporting.

Over time the macro also started turning formulas into values if they didn't change within the week (e.g. lookups between tabs) and further automating the reporting from a semi-simple "run these macros in this order and do these steps manually" to "push the button and paste the results into PowerPoint".

Interlude: Oops, no centralization

Sometime between iterations 2-4, my manager had recommended that we push maintaining the tool back to the Central team in charge of business processes, with the goal of turning an Excel sheet in Microsoft Teams into an actual web tool or application. I passed along the initial requirements and the current version of the tool and then didn't hear anything for a few months.

A meeting popped up on my calendar and I assumed it was to talk requirements, but it turned out the team had created / customized a tool based on what I'd written and wanted feedback. Unfortunately, the tool was also an Excel / VBA combination (so it already didn't feel like a step-change to me) and I'd added many features to my tool since initially writing the requirements as I kept working with the various users. We ended up agreeing to keep using my tool for now and I would have to figure out how to hand it off to a sustaining organization later.

If nothing else, this helped remind me of how much progress we'd made since I started work on the tool!

Interlude: Fix the problem

The purpose of collecting all of this data on purchase requisitions was to burn down past-due requisitions. I co-led a continuous-improvement event onsite in Texas (also called a Lean event, Kaizen event, pick your terminology) where we discussed the trends in the tool, identified potential root causes and countermeasures, and went off to complete our action items. Per our scope sheet, we wanted to see a big improvement in 6 months.

After three months, we saw small improvements in our key metrics of first-pass approval through our Compliance team and parts past-due, but nowhere near our objectives.

Iteration 4: more data!

After our Lean event, I refined the tool per our discussions to help focus people's attention on the main roadblocks. This included automating more of the manual tasks that I kept learning KB was doing after running the macro. The macro pulled more reports, often incorporating them into the same array as the original Business Objects data so that the entire tool could be analyzed as a flat database. I added error-checking so that if an Account Manager said that there weren't any problems with a part number but it was very late, the tool would flag that comment as needing an update. More bug-fixes, more refinements in the various analysis rules and report-generation.

I also followed up with the action owners from the event to ensure that we were doing what we committed in driving the improvements. And when our three-month improvement period was up and the data didn't reflect the major change we were expecting, I worked with the team to dig into additional potential causes and scheduled a follow-up event to try again at fixing the problem.

Conclusion

The follow-up event was in February 2025 and we reviewed all of the previous data and efforts. We defined the tool being taken into the Central team (with more coordination this time, and the tool was MUCH more stable than the previous year). Problems had reduced enough that senior management was not looking for weekly and monthly reports anymore. I wish I could end this post with "and we achieved our initial goal of reducing past-due parts by 88%", but I was laid off in March 2025 so all I can say is that the team was working hard and the tracker had enabled a significant decrease in past-due requisitions.

The Procurement team was happy with the tool and was expanding it to other groups, we just needed the data to prove it was helping.

What's your experience been on improvement projects? How often do they achieve their initial goals?

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.

Search