Global Fastening Distributor Cut Inventory Report Prep Time With One Automated Workbook

Situation

The team was using a manually maintained inventory workbook that required pulling data from four separate source reports and assembling it by hand. The existing file was already in use but required significant manual work to update and was not connected directly to the underlying data exports.

Every time the team needed a current view of inventory, someone had to pull the source files, combine them, and work through the cleanup before the data was usable. There was also no consistent way to log comments, track expedite dates, or include vendor-level detail without adding more manual steps.

The team knew what they wanted the workbook to do. They needed ProsperSpark to help them build it correctly.

Solution

ProsperSpark treated this as a client-directed build from the start. The initial phase focused on understanding how inventory reports were generated, what data each source contained, and what the final output needed to look like. From there, the work moved into a structured beta build.

The core of the solution is an Excel workbook that connects to four source reports using Power Query. Instead of manually combining exports, the user points the tool to the source folder and runs a refresh. The workbook pulls the data, applies the transformations, and produces a consolidated output automatically.

Specific build elements included:

• • Source connections: Custom Power Query for each of the four source reports, with a VBA-driven input sheet for selecting the source and output folders
  • Data merging: A single Power Query that merges the source reports into a unified output, including lookup tables and user input tables
  • Comments and tracking: A comments table with a validated comment-entry process, plus fields for expedite dates and vendor detail
  • Runout analysis: A dedicated runout worksheet for additional inventory review
  • Output automation: A VBA macro to export the finalized data to the required format
  • Work instructions: Built-in documentation so the team can run and maintain the tool independently

The tool was built iteratively with ongoing input from the client, with each phase reviewed and refined before the next set of features was added.

Results

The finished workbook replaced a largely manual process with an automated one. What previously required pulling four separate reports, combining them by hand, and cleaning the data is now handled by the tool. The user selects the source folder, runs a refresh, and gets a consolidated, structured output.

The comments log and expedite date tracking give the team a way to capture operational context directly in the workbook, rather than maintaining that information separately or relying on memory.

Work instructions built into the file mean the team can run and maintain the tool without relying on outside support for routine use.

Because hard before-and-after metrics were not captured, the estimates below are directional, based on the number of manual steps removed and the nature of the automation built.

• • Estimated 60–80% reduction in manual report prep time
  • Estimated 70–90% fewer steps to produce a usable inventory view
  • Near-zero ongoing manual effort to combine source data after setup