Hi. Can anyone help me keep my design simple for the following inventory management scenario.
My colleague works in IT and used excel to try and manage his stock of kit that he needs to deliver to our VIP's on demand. I created a smartsheet to help him identify the level of stock that he currently has, along with reorder level and reorder quantity, supplier name, quantity and contact details, serial number and barcode. Each item is on a separate row, with a formula rolling up the stock level to parent row. There is also an automation on the parent to prompt him when stock falls below order level. This is sheet 1.
In order for the stock to fall in sheet 1 above, my colleague would assign a piece of kit to a colleague eg a Dell Laptop model 123 to John Smith. He wants to keep track of what has been sent where and to whom, so I created a second automation to MOVE this row once assigned into a sheet 2 - (Kit assignments) that contains all the info from above (obviously) , as well as the assigned to person, date, etc. This works so far.
However, once there is a reorder requirement, my colleague then needs to re order eg 10 more dell laptops model 123. (the actual process is that he contacts the vendor to ask them to quote for resupply. If the quote isnt good enough he might approach another supplier - I could do something here but not yet). Once he has found the best quote, he raises a PO on our internal Purchasing system and confirms the order with the supplier.
So my question is what is the best way to manage the reorder process above Should I add reorder it as a new row in sheet 1 Stock, with a status of requested? Then once the order is received (ie received date has a value), then a formula is used to count everything that has just come in and adds this to the additional (available) stock level for dell laptops, model 123. My colleague would use his mobile app to scan the serial number of the higher value assets upon arrival in the stockroom and it would then become available for kit assignment when requested as per sheet 2 above.
I thought originally of having a 3rd sheet, called pending orders, but figured that I could achieve the same with the pending orders sitting in sheet 1 Stock, but with a status to differentiate them from actual stock that could be assigned.
I just want something that simplifies the above and that will work. Has anyone built anything similar. i stared with the template and took a few ideas, but as I chatted with my colleague the requirements became more sophisticated.
Any help is appreciated. Hope you are all staying safe. Kind regards Stuart (London)
