The following spreadsheet contains two tabs Inventory and Forcast. The inventory tab contains the inventory levels of two types of parts the WU(where used) and the Raw parts, the WU is the # of finished assemblies utilizing the raw parts and are specific to the product specified(3K,4K,5K). Each WU also has a Usage amount which is the number of Raw Parts required to make one WU part. The WU inventories already have the usage taken into account( For example the 200 BBB parts were made using 2000 raw parts). In the forecast tab every product has an estimated number of production per year(Assume production is evenly distributed over the 12 months).
My goal is to create a macro that can calculate the month(or year with decimals) when the inventory will run out. The WU inventory for each product will be used up first by the amount estimated in the forecast, once the WU is depleted for a certain product it will begin using the raw part inventory, this is where the usage for each part comes in to play (it takes 10 raw parts to makes one BBB for example).
Bookmarks