Designing stock management, kit assignment and ordering process.

stuartr

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)

Andrée Starå

Hi @stuartr

Interesting, I have some ideas.

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

stuartr

@Andrée Starå I've been trying to work this out in the meantime. I added a column in sheet 3 called Order status, with single select values that make sense and map the journey..

1 Quote requested

2 Quote accepted

3 Order submitted by dmg

4 Order received by supplier

5 Order despatched by supplier

6 Order delivered to dmg

7 Order checked by dmg

8 Stock scanned/rows added by dmg

Is there any way to Copy just the PARENT row from sheet 1 (Stock) to sheet 3 (Quotes and Orders) rather than copying the parent AND all the children rows complete with serial numbers etc. All I want to COPY into 3 is the detail in the PARENT row of sheet 1.

stuartr

Alternatively, if there is no solution for the parent row solution above, I can create a new summary/metrics sheet that references sheet 1 and totals up all models in one row (effectively the PARENT information even thought is not a PARENT row) - I think this could work.

I could then use automation from the summary/metrics sheet to send to the high level parent info to sheet 3? Also, any quantity in stock information on 1 could be referenced from my new summary/metrics sheet so that the totals are always in sync and still visible from stock sheet 1?

wdyt?

stuartr

@Andrée Starå - I think I have worked this out. I was trying to manage too much on a single sheet and by managing totals and notifications from a separate metrics tab this is the way to go.

Sheet 1 = Stock - Automation to push row in 2 once kit is assigned

Sheet 2 = Kit assignments - effectively asset tracking - whats out there, to whom, Serial number - no automations

Sheet 3 = Metrics/summary that references sheet 1 and summarises totals. Two automations - low stock notification to start quote process. Second automation will copy a row into sheet 4 below

Sheet 4 = managing quotes, orders and scanning kit Serial numbers once delivered. Automation will MOVE rows into sheet 1 above once Serial Number is added Based on status changes to 7 and 8 above. A second automation will overide status and still allow rows to be pushed into Sheet 1 for lower spec/cost assets that arent scanned eg cables, adapters.

I have now removed your share but do thank you for your earlier offer of investigation. If there is anything that might improve the above design please let me know.