Creating a Formula to track inventory

JLewski-PCI

Hi All,

i am trying to track inventory and alert appropriate personnel of when they should order more parts according to lead time.

For the most part everything is working well. However, when parts come into the building and are added to inventory my formula still deducts anything that was sent previously. i am assuming that i may be able to set something up with rules and dates shipped vs. date parts are received for inventory. For example:

We have 10 widgets in inventory

on 5/1/19 we shipped 5 (only have 5 left, time to order more)

10 more widgets come in on 5/5/19 and are added to inventory

inventory should be 15 widgets. however my formula will still subtract the 5 that were shipped before more parts came in and only show us having 10 in stock.

 

Is there a way to set a "if-then statement" to stop smartsheet from continuosly deducting parts that are already out of the building?

Andrée Starå

Hi,

Yes, there probably is!

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

I can think of a few ways to set this up...

 

When parts are shipped, enter a negative amount. When parts a received, enter a positive amount. The total parts in stock should be accurate.

 

Have two separate columns. One for shipped and one for received. Add the two up by column then subtract shipped from received.

 

Enter all numbers as positives. Use something along the lines of

 

=SUMIFS(Count:Count, Type:Type, "Received") - SUMIFS(Count:Count, Type:Type, "Shipped")

.

These will all keep you from having to factor in the dates and keep things relatively simple.

JLewski-PCI

What i have set up: 

One sheet that my customer service rep inputs a value of assemblies shipped.

I link that value to another sheet that then calculates the number of components that were included. once the inventory level gets below a safe number (depending on lead time to get more in) it will alert either purchasing to buy of molding department to mold more parts. this way i separate what people see and what they can mess with.

i was thinking that this formula may work but i am not sure i have it set up right:

=IF([DATE RECEIVED / MOLDING COMPLETED]4<[LAST DATE SHIPPED]4)[STOCK @ START]4 - [PART / COMPONENT DESCRIPTION]21

here is my thought process.  if the date we received more components is BEFORE the last ship date then subtract the  the parts that we shipped.  if the date we received components is AFTER the last ship date do not subtract components.  

Does that sound like it should work?

JLewski-PCI

thanks Paul.  i think my biggest hurdle here is that this is a big assembly with various numbers of components. for example 1 base plate, 10 inserts, 2 stickers...etc.

i dont want my customer service person having to calculate all of that and try to use negative vs. positive numbers.  

Paul Newcome

As long as they are inputting a number (even just positive numbers) along with having some way to establish whether that number is received or shipped, this can be automated fairly easily in a number of different ways.

 

I see where you are essentially using a Metrics sheet to track the actual inventory, so a couple of rather basic formulas and some cross sheet references should be able to solve this for you even excluding the dates themselves.

 

How do you establish Received vs Shipped?

JLewski-PCI

Shipped is a manual input, say every Monday.  received would another manual input by another department. then added to the "stock column.

Paul Newcome

Are Shipped and Received dropdown options? I assume the actual numbers are also manual entry, but are they within the same column on the sheet?

Andrée Starå

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

JLewski-PCI

maybe this will help, please see attached.  i totally get the add or subtract parts manually approach but i have multiple people inputting data so the less clicks the better.  maybe i should have mentioned that this assembly has 8 different parts @ different quantities.

 

Paul Newcome

Please see below. Here's a list of either questions or statements regarding my understanding of your process. Please feel free to correct or answer accordingly.

 

1. Shipped counts are logged in the [Quantity Shipped] column on sheet A as a running total.

 

2. Parts molded and added to stock are recorded where?

 

3. I understand there are multiple types of parts and each have different counts. As long as the type for each entry whether shipped or received is specified, this can be easily accounted for.

 

4. Are you able to save copies to a new workspace (replacing/deleting any sensitive/confidential data), and then share access to the new workspace? Even if it is just view only so we can take a good look at how everything is fitting together?

 

5. I understand your desire for automation with this. When I say something about manually entering something, I am referring to the raw data itself. For example... If you shipped 1,000 pieces of Part A, someone would manually enter "Part A" and "1,000" somewhere. From there I am trying to get your actual calculations automated. I just need to make sure I have a clear understanding of how everything is working together to make sure we have the right solution for you.

JLewski-PCI

Paul,

Would you be up for a GO-TO meeting where we can chat and look at the workspaces live?

Paul Newcome

Unfortunately I am unable to schedule time specifically for Smartsheet Community related things. I can only look at it when I get a few minutes here and there.

 

Are you able to throw together a quick visual workflow of sorts? It doesn't have to be pretty. Just a quick run down of the process from beginning to end?

meredithm@oldmilliron

Ok, y'all really seem like you know what you are doing and I am lost! We are just starting so I am new at this. We would like to use smartsheet to track inventory but what little I'm finding on the solutions center does not make much sense to me. I can't find a video. I have seen the templete but without any information it really is no good to me. I would be happy for any help or advice - Meredith

Andrée Starå

Hi Meredith,

Unfortunately, I don't think there is a video, but I found the below articles that might help and I'd be happy to schedule a quick chat and help in any way I can. Please e-mail me at andree@getdone.se and well take it from there.

https://www.smartsheet.com/solutions/inventory-management

https://www.smartsheet.com/blog/increase-speed-accuracy-mobile-barcode-scanning

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting