We are trying to figure out the hardware demand at each rack location based on trailer sales and build of materials (BOMs). There are several racks around the plant and many pieces of hardware are located at multiple racks. Analyzing the hardware usage as whole isn't difficult. It's when we need to break it down by rack where it becomes time consuming.
Currently, the "Component Where Used" report breaks down assemblies from the BOMs into levels / tiers. Each top-level assembly has a description of what that assembly is. We can use this description to determine which rack a particular piece of hardware belongs to. (Ideally, I'd like to add a column that indicates which specific rack an assembly pulls its hardware from. But that issue can be addressed at a different time.)
Is there a way to break down a piece of hardware within an assembly, then into locations based on the assembly description, and then finally use trailer sales to determine the overall demand for a particular piece of hardware at EACH unique location (rack) in a TIMELY manner? I'm currently copy and pasting from "Component Where Used Inquiry" into an Excel sheet and then manipulating the data from there.
Here's what I currently do: Per the snap shot below, eight pieces of 92605M are needed in one assembly for VAW-1200 (trailer model number), then four more are needed in another assembly; therefore, a total of 12 pieces are required for this trailer based on just what you see here. But the eight pieces in the first assembly are for an axle connection (based on the description). These eight pieces would come from the "Axle" rack. The other four pieces from the other assembly are for a winch stand connection. These four pieces would come from the "Winch" rack.
I would need to figure these numbers and locations for all trailer models then multiply trailer sales by each location's totals to determine an estimated annual usage (EAU) at each unique location. This is very time consuming when you deal with 200+ hardware part numbers, 10+ racks / locations, and 150+ trailer models. And you're using the copy-paste method into a spreadsheet. 😵
Any help / guidance is appreciated. Thank you. 😊