RFQ/CBF Excel-to-SAP quoting tool

This is the third of a series of posts about tools I've built or used to make life easier. The request for quote (RFQ) / competitive bid file (CBF) file was the result of two different projects which a) took a list of all parts and potential suppliers for customer request for proposal, b) created RFQs for every part/supplier combination, c) enabled easy data-entry of quotes when they came back, and d) uploaded everything to our SAP system so that we had a single central database of all RFQ and quote information and the ability to convert those quotes to purchase orders. 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 tool included Excel formulas, Visual Basic for Applications (VBA) code, and SAP macros.

Headline improvement: 98% touch-time reduction

Previous posts in the series:

At the beginning of 2024 I started work on two different projects: the first was to update the "competitive bid file" and the second was to explore and implement a database to record every RFQ we sent out and every quote we received back in. Eventually, I realized that keeping the projects separate would prevent them from reaching their full benefit, and so combined them into a single tool.

The Competitive Bid File (CBF)

A much-younger me had built the current iteration of the CBF in 2010 (with many modifications over the intervening years but the basics intact). The Cost Estimating group used it to communicate with Supply Chain when they developed proposals for military and US government contracts. Estimating would populate a template with part numbers, quantities, and other basic information, Supply Chain would then send out requests for quote based on that template, and finally enter the detailed quote information back onto the CBF and re-submit it to Estimating. This CBF was a key piece of our evidence package whenever we had to prove that we'd gotten fair and reasonable pricing from our suppliers (i.e. we weren't cheating the government), and it was then used as an input to the proposals we submitted to our customers and a guide for the Procurement organization when it came time to order those parts.

The old file was no longer fit for purpose: many of its features were no longer used, and other features had been kludged in but required additional manual work. I got pulled into the team to update the file. As I now understood much more about Excel and how to integrate it with SAP with 15 years' more experience, we were able to automate many features that were manual before:

Capturing the US government prime contract number if known and flowing it onto the requests for quote
Pulling recent purchase history from SAP for a baseline understanding of current suppliers and pricing
Pulling current drawing revision letters
Support for many different scenarios for delivery quantity and years
Automatically generating Excel-based RFQ forms for sending to the supplier
Automatically capturing quote data from those Excel templates into a database sheet on the CBF

The first versions of the updated CBF did much of the calculation on the worksheets themselves, but then a user complained (rightly) about how slowly a quote would transfer into the database sheet, and so I had to teach myself about arrays in VBA. The upload time went from minutes to barely-measurable. The VBA macro created an array of the database as it currently existed and an array of the quote to be entered in the database. The macro then compared the two databases to identify if the quote had already been uploaded (and updated the relevant lines if so) or needed to be appended to the database array. After all calculations were complete, it wrote the modified database array back into the database sheet and returned a message box telling the user everything was done.

Side note: this is also when I learned to routinely include code that recorded how long each step was taking so that I could fix slow code as well as broken code.

The Request for Quote (RFQ) database

The first phase of our project for an RFQ / quote database was to identify how we wanted it stored. We could use third-party software (some other business units were doing this), put a big Excel or Access database on a SharePoint site, or somehow use our enterprise SAP software. It turned out that none of the third-party software was fit for our purpose. A big database would have been the easiest (and was the default option), but would have been easy to corrupt through sloppy data-entry and lots of additional work for the various Commodity Managers as they manually entered quotes in the database. The problem with SAP was similar: lots of manual entry, and also less user-friendly than Excel. But SAP was universally-accessible (like a SharePoint site wasn't), already had all of our purchase order data, and if quotes were in SAP then we could do additional neat things like "check a box to make this quote the default source for the next purchase order" or "convert this quote into an order".

We decided to implement SAP as the database, but we needed Excel as a "front end" where the person creating and evaluating the quotes would do their data entry in Excel and then the information had to end up in SAP for future use. This included:

Creating an RFQ "header" with quote due-date, delivery date, a narrative about the RFQ, and standard terms & conditions
Collecting part numbers and quantities in every combination needed
Creating similar RFQs for several vendors who would be quoted for the same part numbers

After the user entered all of the Excel information, the macro would act as an agent for the user and run the SAP transactions faster than the user could to perform all of the SAP data entry. It would have been faster and cleaner if I had access to SAP's underlying tables and updated them directly, but I worked around the limitation and advised the user to take a coffee break after hitting the "upload" button.

Once quotes came back from the suppliers, the tool could pull key information (part number, quantity, etc.) out of SAP and into Excel. The user would enter pricing, etc. in Excel, and then push another button and the updated information would go back into SAP.

Putting them Together

Late in 2024, after prototypes of both tools were deployed, I realized that while Commodity Managers working on non-military proposals might exclusively use the SAP RFQ tool and save themselves time, Commodity Managers working on military proposals would need to fill out both. The two were also structured very differently, making it hard to copy and paste between them: the "customer" for the RFQ tool was SAP, so it looked like an SAP RFQ, while the customer for the CBF was Cost Estimating, so its output had to mesh nicely with their spreadsheets. Well, I had built both tools, they were both Excel macro-enabled workbooks, I could combine them, how hard could it be?

It turned out to be very hard: I'd been "learning by doing" that year (e.g. first use of arrays) and different modules varied in structure and made mismatched assumptions about global variables, worksheet names, and data structure. I ended up adding the SAP RFQ code to the CBF. The Excel structure matched Estimating's needs, but I had streamlined as much as I could to make it easier on the non-military users. Eventually, I had a tool where:

Cost Estimating would enter their general information (delivery dates, contract numbers) and parts and quantity scenarios
Supply Chain / Commodity Management would enter the candidate suppliers for each part number, with the incumbent defaulting to one of the candidates (but could be overridden)
The tool would then summarize for Supply Chain the number of competitive and non-competitive quotes and allow the Commodity Manager to enter general information like the point of contact (their name and title, quote need date, and anything else they needed the supplier to know.
Finally, the tool would take everything and generate RFQs in Excel format (for sending to the suppliers) and in SAP (for data retention). The tool would record the RFQ numbers for future reference and report to the user if SAP had any issues.
Wait for the supplier to respond to the RFQ
When the quote came back, if the suppliers used the format correctly, the Commodity Manager could copy the filled-out quotes in the CBF file and it would automatically populate the CBF's database sheet (that Estimating used) and SAP with all of the relevant quote information.
The Commodity Manager would use the tool and/or SAP to identify the preferred supplier for every part and quantity-scenario and send the sheet on to Estimating and/or update SAP. The tool provided comparisons between the quoted prices from various suppliers and the most-recent purchased price.

Right before I left the company, we did some time studies on the "old" way of creating RFQs and analyzing quotes (which didn't include a centralized parts database) and the new method. The new method did include some long waits for the tool to operate SAP (measured on 100+ part counts and complex lists of suppliers), but it still totaled a fraction of the original time. Manual time with the new tool was minimal. So total time saved was 60%-80% and touch-time saved was 96%-98%.

I had to teach myself a lot for this tool: arrays, global variables, pulling SAP outputs from the clipboard into an array and manipulating them before they ever touch the Excel worksheet, and error handling.

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