Demand Planning - Finished Goods & Assemblies

Hello,

We are trying to get a robust demand planning report built. We currently have a report that lists all Inventory Items and Assembly Items along with 3, 6, and 12 month consumption (including both quantity and value). The trouble we are having is that in real practice the building of Assembly Items affects the consumption of Inventory Items. But the inventory item consumption is not being affected by use on Assembly Items, only on sales. This is not only throwing off results on what we need to order, but also on identifying excess and obsolete inventory.

example: Inventory Item 1234 is purchased and only ever used on Assembly Item ABC. When you review the consumption of Inventory item 1234 it is zero for 3, 6, and 12 months and it has a current on hand quantity of 3. This is indicating that we do not need to purchase any of the item and that it should be considered excess or obsolete. However, we know we are going to build 5 ABC Assembly Items, and this will require the use of 5 123 Inventory Items. Therefore, we are actually understocked by 2.