Workflow for Tracking Inventory Counts and Bin Locations

MTGoodman

Hi,

I am trying to setup a sheet to keep track of when we count bin locations in our warehouse and how often. I would like every bin to be counted at least once ever 12 weeks. In our bin locations column, I have a multi-checkbox setup with all 403 of our bin locations in it. We typically count 5-12 bins a day. Can I setup an automation to tell me when a bin needs to be counted if it is outside that 12 week window?

I plan on making a metadata sheet as well as a dashboard for future metrics as well.

MTGoodman

Okay, so, I ended up figuring out what I needed to do. I have a Form our warehouse associate will fill out with the Date, Bin Locations, Counts, and variance. The form will put the fields into the Cycle Count sheet.

Then, I have a metadata sheet with all of the bins in our warehouse with 2 other columns for Number of counts preformed for each bin, and one for the most recent count date to track the last time a bin was counted.

Last Count Date Formula: =MAX(COLLECT({Inventory Cycle Counts Range 2}, {Inventory Cycle Counts Range 1}, HAS(@cell, [Bin Location]@row)))

Number of Counts Total Formula: =COUNTIF({Inventory Cycle Counts Range 1}, HAS(@cell, [Bin Location]@row))

With these 2 sheets I have created a Dashboard that has 2 widgets on it so far. One widget is tracking our IRA % and the other Widget will list all bins that are out of the 90 day audit window.

Jeff Reisman

@MTGoodman

Sure, you can do this with automation. The question is, do you want the notification to be at precisely 12 weeks (84 days,) or will 90 days do? If it's 84 days you want, we'll need a helper column. If 90 days is okay, we can just do that with automation.

Let's do just automation first:

If you want the alert at exactly 12 weeks, add a helper date type column. Enter a column formula:

=[Cycle Count Date]@row + 84

Then create a Workflow just like the one above, just set it to run when the date in the helper column is reached.

MTGoodman

Okay, so, I ended up figuring out what I needed to do. I have a Form our warehouse associate will fill out with the Date, Bin Locations, Counts, and variance. The form will put the fields into the Cycle Count sheet.

Then, I have a metadata sheet with all of the bins in our warehouse with 2 other columns for Number of counts preformed for each bin, and one for the most recent count date to track the last time a bin was counted.

Last Count Date Formula: =MAX(COLLECT({Inventory Cycle Counts Range 2}, {Inventory Cycle Counts Range 1}, HAS(@cell, [Bin Location]@row)))

Number of Counts Total Formula: =COUNTIF({Inventory Cycle Counts Range 1}, HAS(@cell, [Bin Location]@row))

With these 2 sheets I have created a Dashboard that has 2 widgets on it so far. One widget is tracking our IRA % and the other Widget will list all bins that are out of the 90 day audit window.

Jeff Reisman

@MTGoodman That's some nice work!

I built some similar things for my company's warehouse as well, but for tracking Bills of Lading.

MTGoodman

@Jeff Reisman Would you care to share your idea on how you implemented that? That was one of our next goals. I'm curious to see how we could use BOL tracking.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326172#Comment_326172

😅 It's a really large workspace, and has just been retired since we moved to real-time tracking in SAP, but it did help the warehouse staff over the course of two years or so to get used to organized workflow and tracking. Let's see if I can nutshell it for you.

Each warehouse started with a BOL tracking sheet populated by admin staff via a form. They had USB bar code scanners to scan the BOL numbers off the paper copies' bar code, scan the assigned picker's name and a carrier name off sheets of bar codes on the wall, and enter the BOL $ value. (I actually had them scanning these into a form on an entry sheet, with a move row workflow that actually moved the rows through a secondary entry sheet, and then moved them into the main BOL tracking sheet. This was because as the tracking sheet got larger, it took too long to save each submission. By leapfrogging, the entry sheet never had more than 1 or two rows in it at a time, so saving was instantaneous.)

When the picker collected their assigned BOLs, the floor supervisor would update the status from Assigned to In Progress. When the order was picked and on the truck, the supervisor would verify and then give the paper BOL back to the admin to update status to Closed. (Before this, nobody was tracking who was working which BOL, so if one got lost - a handful went missing each day - nobody knew who was responsible for it.)

I created a handful of metrics sheets for each warehouse. For each pickers, counting number of BOLs assigned to them on the current day, number closed by them for the day, $ value closed by each one on the current day, as well as counts for total assigned, total in progress, total closed for the current day. I used the data to create a dashboard with donut charts - # assigned to each picker, % assigned to each, # Closed by each, metrics for total number and values, a chart for total $ value closed by each picker for the day. This dashboard went up on two big 75" TVs in the warehouse. Seeing their current metrics actually got some of them to work faster, which was good because our sales picked up a lot over the last two years!

I also listed the oldest BOL in the tracking sheet that was not yet closed, and the date/time it was entered, so at a glance they could find out what was getting left behind. I wrote about how I did that wrong at first and how I got it right, here: https://community.smartsheet.com/discussion/69081/find-the-row-with-the-earliest-created-entered-date-that-matches-certain-criteria

Here's what the dashboard looked like, there's no data, but just imagine three donut graphs, a bar graph, and a chart!

And part of a metrics sheet:

Good luck!

MTGoodman

Jeff,

Thank you so much for going into detail on this. It really gives me a good idea on the direction I want to go with our warehouse.

Great work all around.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326190#Comment_326190

Glad I could help!