HOW TO FIGURE HARDWARE DEMAND AT POINT OF USE (multiple locations) UTILIZING BOMs & SALES

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.)