Using Automations for Inventory Management

AnnieR

Hello!

I'm struggling to come up with solutions for inventory management in Smartsheet. We order many different parts and don't really have any sort of "item master" for any cross sheet solutions that I can think of. Each project get's it's own BOM which varies quite a bit.

We often order items just into inventory and then later assign them to a project. I was looking at using a copy row automation. If I just made a status "move to project - x" and when I change that status it copies the row to the project, that's a start. I tried to enter a formula into the automation to change the quantity to QTY - Moved QTY but discovered there is no way to add in a formula using automations.

Other than manually adjusting the formula on the Inventory sheet, and in the project sheet, is there a better way?

I haven't done any cross sheet solutions yet so maybe there is potential there but I'm not sure what I'd use as a reference to do any lookups from.

Let me know if any additional information would help and thank you in advance!

Annie

Genevieve P.

Hi @AnnieR

Additional information would actually be good! Screen captures would be the most helpful, but please block out sensitive data.

You note that you don't have an "Item Master", but then there is an "Inventory" sheet. Does your Inventory sheet have all of your items, so it actually is a type of Item Master where you want the formula to calculate current inventory?

What formulas do you currently have on the Inventory sheet (and a Project Sheet)?

I would perhaps suggest that you could use a Report including all of your Project Sheets, then Group by the Item Name or ID. Then that way you can use the Summary function in a Report to see total QTY across all projects. (See: Redesigned Reports with Grouping and Summary Functions)

If you need to compare that number to the QTY in the Inventory sheet, you could leverage the Report to create a Chart and put it on a Dashboard, next to a Chart sourced from the Inventory sheet. Or two Reports side by side... there's a few ways to do this.

Cheers,

Genevieve

AnnieR

Hi @Genevieve P. !!

Thank you for your response :)

So by "item master" I meant a master database of all items that we order. The items vary a lot by project.

I do bulk orders catagorized under "inventory" and log those in my "inventory" sheet. Eventually I need to split those bulk orders out by projects (ex project 1, 2, 3, 4). Each project has it's own smartsheet.

If I have 20 widgets in my inventory sheet, eventually I need to split those out in a variety of ways. For example I might need to assign 5 each to projects 1, 2, 3, and 4. I'd like an automated way of doing this from my inventory sheet that isn't just copy pasting this line into my separate "project" 1, 2, 3, 4 sheets and manually adjusting the quantities.

The only formula I have right now is just my QTY column * Price Each column to calculate my Total Price column.

There's about 500-1000 or so items per project so I'm struggling to imagine the grouping and report functions you're referencing. We'd like to utilize parent/child relationships in order to group project sheeets by assembly. Each assembly might have 20-100 rows or so. However I feel stuck on that thought since if I start grouping by parent/child structure, it seems like I'll lose the functionality to sort columns by a-z in each sheet. I'm also concerned about the lack of flow through of the parent/child structures into reports. As a work around I've just used a helper column to later group my parent/child relationships back together in reports, but I'm nervous to plow forward with that thought since I don't understand the limitations or risks yet.

Does this help? I attached a screenshot of my inventory sheet but not sure how helpful it will be.

I immensely appreciate your insight!! :)

Annie

Mike TV

@AnnieR

I'd probably start by changing "QTY" to "QTY (Actual)" and then add additional columns such as "QTY (P1)" etc for each project. Then finally adding a "QTY (after projects)" with a formula to subtract the project quantities from the actual quantity.

Then on your Project sheets with the BOM you can enter the quantity that will be needed for the project. On your master inventory you can Index Match the "QTY (P1)" from the quantity amount you'll need to use for Project 1 on it's project sheet. That can be a column formula which would match up quantities from all 500-1000 items without having to write 500-1000 formulas. It looks like you can probably Index Match against either the Description or the Vendor Part Number as long as none of them repeat identical information for different inventory items.

It shouldn't take too long to set all this up. The most time you'll spend would be creating your BOM of 500-1000 items (which is unavoidable) and adding the quantity that the project will need to use of each inventory item (again, unavoidable).

AnnieR

@Mike TV Thank you for your comment! I'm sorry that I'm late to respond.

I just started pulling together a meta data sheet with quantities from all projects:

Were you thinking that the QTY(Actual) columns would be the physical count number we did when we received the parts in? And then an additional count would be done after the projects? We typically use up all the products we receive in. I'm trying to reassign excess to "inventory" from the start.