Inventory management by future date

Good Morning -

I was asked by a member of our IT team to help put together an inventory tracking workbook that can help manage future inventory for better accuracy in placing orders and calculating lead times. I initially built out a simply inventory in/out sheet (sheet 2) that worked with existing product and required minimal data entry, but the team would prefer to use their existing set-up sheet (sheet 1) and have it interact with the inventory tracker that way. The tricky part is, that we build out entire computers for new hires each week and need to know when we will run out of product. I can't use average product use as an indicator, as we'll have 10 new hires one week and 2 new hires the next, so we'll need to know exact inventory for each individual's machine build (did I mention each new hire has different inventory requirements)? I'm somewhat of an Excel novice (currently in intermediate course on coursera), so I am completely stumped here. Is there a way to get sheet #1 talk to sheet #2, while still indicating # of each product on a specific date AND have a cell highlight via conditional formatting on sheet 1 if that product will be below minimum count for that future new hire? The big point here is to keep sheet #1 as-is, given they're not willing to manually calculate total inventory used each week and track it that way. Thanks!