Inventory Formula Issue

Question

Level 1

8 points

Inventory Formula Issue

I am building an inventory system, but cannot figure out how to get two separate tables on one sheet to communicate. The goal is for them to perform the same operation, when table 1's number(s) increase or decrease, but while maintaining their original and respective quantities.

Table 1 contains two versions of various "Final Products" while Table 2 contains a list of individual components that are used every single time in Table 1, regardless of version/variant.

As I sell, or add, final products (Table 1) to my inventory, I would like my components to perform the same operation. (increasing or decreasing)

The problem I keep running into is keeping the quantities separate. I do not mind if I have to have many separate formulas to make this happen, but it would save a great deal of time if I only had to alter a single final product number and the formula would take care of all my component numbers.

It is a daily inventory, however this type of issue will be used in multiple locations throughout my overall inventory setup.

I sincerely appreciate any and all information that is offered.

MacBook Air (13-inch Mid 2013), OS X Yosemite (10.10.5)

Posted on Nov 5, 2016 11:14 AM

Reply

Answer 1

t quinn

Level 5

7,063 points

Nov 5, 2016 8:53 PM in response to SgtPapaP

Hi SgtPapaP,

I am trying to understand the relationship of components to product. It seems that Product is what you will be counting and that will be reflected in the componant table. If this is correct we need to integrate how many of each componant is in each product into your formulas.

If this is a daily inventory, you will want to structure your main data input in a way that will make it easy to retrieve data from. Perhaps something like

This would provide easy data input especially with a form in iOS. The tables you show could both be report tables that draw their data from this data table. You would have a record of your daily inventories which might be useful to track in the future.

So. how many of component A in Product 1? In Product 2? Etc..

quinn

Reply

Answer 2

SgtPapaP Author

Level 1

8 points

Nov 5, 2016 9:41 PM in response to t quinn

Hi quinn,

All 3 of these specific components are used once in every single product. These components are similar to the "guts" of a product, with the product having different outer shapes/bodies, hence the 8 different final products.

So, exactly 1 each of the 3 components goes into a final product. The same rule applies to any of the 8 final product variants.

The 'components' will therefore obviously decrease at a much more rapid pace than the 'product' will. While it will be used for daily inventory purposes, the main benefit of this will be for re-ordering of the components.

I believe this is essentially trying to find a way around circular referencing. I am not sure I am understanding the reason for needing to structure the data the way you suggest, but I look forward to reading your explanation. As it stands right now, I have these two tables separate, but on the same sheet.

Thanks quinn.

Reply

Answer 3

t quinn

Level 5

7,063 points

Nov 5, 2016 9:56 PM in response to SgtPapaP

Hi SgtPapaP,

You can have data in a cell or a formula, not both. So your component A value can be calculated or entered but not both. Likewise with Products. Seems like you might want to inventory both in a single table and use those figures to inform a report table.

In my example I was imagining that your would be inventorying Products. This setup would allow you to check what your inventory of a product was last January for instance. Will you need to anticipate a run on component A next year? This might tell you over time.

quinn

Reply

Answer 4

SgtPapaP Author

Level 1

8 points

Nov 5, 2016 10:16 PM in response to t quinn

Hi quinn,

I understand your explanation of data entry regarding cells and formulas, but am still unsure of how I would set the formula up or use a report table.

In addition, and not to complicate further, but I am working with 7 different components to create the final product, or final 8 products. Only the 3 mentioned prior are used every single time, regardless of variant. So should only those 3 be included on the single table or should all components be listed on one table and just run different formulas specific to each component?

I anticipate multiple runs of each component throughout the year, as I try to maintain an inventory and monetary balance. Am I just trying to accomplish to much on one program? Are you able to provide examples of what you are explaining?

Thank you.

Reply

Answer 5

Barry

Level 9

60,254 points

Nov 5, 2016 10:25 PM in response to SgtPapaP

Hi Sarge,

I'm hitting a cognitive disconnect in this part:

Table 1 contains two versions of various "Final Products" (and the quantity of each on hand)
Table 2 contains a list of individual components that are used every single time in Table 1 (and a list of quantities of each component on hand.

When you sell two of main product 1, version A:

What change is there to each of the numbers on Table 1?
What change is there to each of the numbers on Table 2?

Regards,

Barry

Reply

Answer 6

SgtPapaP Author

Level 1

8 points

Nov 5, 2016 10:37 PM in response to Barry

Hi Barry,

Thanks for the response. Your first half is correct. Table 1 has two versions, or 8 total variants of the final product. Table 2 is a list of 3 (out of 7) specific components that are used every single time for any of those variants in Table 1.

So if you were to sell 2 of main product 1, version A:

Table 1 would see a decrease of 2 in, and only in, the exact product sold. (Product 1, Version A)
Table 2 would see a decrease of 2 for every single component.

No matter what variant or quantity of the final product is sold, each one of those 3 components will move at a ratio of 1:1.

Another example, if you sold 2 of Product 1, Version A and 3 of Product 3, Version B:

Table 1 would decrease by 2 in Product 1, Ver. A & decrease by 3 in Product 3, Ver. B. No other changes in Table 1.
Table 2 would decrease by 5 for every single component. (2x for first Product, 3x for other product.)

I hope this clarifies any confusion. Thanks for any information you are able to provide.

Reply

Answer 7

Barry

Level 9

60,254 points

Nov 5, 2016 11:01 PM in response to SgtPapaP

OK.

That would mean you have an inventory of over 3000 individual products, each of which requires one of each of the three components listed on Table 2.

How does that work, considering that you do not have 3000 of any one of the three components, and you have only enough of Component A to fit approximately 10% of your current inventory of these products?

My own logic would suggest that a 'component' exists as a component only until it is incorporated into a product. Each time a 'product' is produced, all of the 'components' comprising that 'product' cease to exist as separate inventory items, and the count of each 'component' required to create the 'product' should decrease by one as soon as the 'product' is added to inventory.

Regards,

Barry

Reply

Answer 8

SgtPapaP Author

Level 1

8 points

Nov 6, 2016 5:02 AM in response to Barry

Barry,

To answer your question, each component has different re-order points. Each component is manufactured in batches at more frequent intervals, due to their constant use. So what you see listed now, will periodically increase throughout the year.

So your logic makes complete sense. Most likely it is my explanation that is causing confusion, as I tried to isolate items for the purpose of asking a question here.

I assumed it was easier than it appears to be. I figured it was a single formula answer, but was unaware of not being able to self-reference cells. Getting back to my original problem, all I want this formula to do is:

As a cell in Table 1 decreases (any cell), all 3 cells in Table 2 decrease.

Thanks again Barry.

Reply

Answer 9

Barry

Level 9

60,254 points

Nov 17, 2016 6:44 PM in response to SgtPapaP

The formulas are pretty easy. But I'm still baffled by the logic.

Let's simplify the description to a single 'product' and one of the many components used to make that product.

At the beginning you have no product and no components.

You purchase a batch of 100 pieces of 'Component A'

Your current inventory of these two items is now:

Product 1: 0

Component A: 100

You use one Component A in manufacturing one Product 1.

Your current inventory of these two items is now:

Product 1: 1

Component A: 99

You manufacture four more of Product 1, using one Component A in each.

Your current inventory of these two items is now:

Product 1: 5

Component A: 95

You sell two of your Product 1.

Your current inventory of these two items is now:

Product 1: 3

Component A: 95

and so forth.

Inventory of a product increases whenever one or more of the product is manufactured, and decreases when one or more of the product is sold.

Inventory of a component decreases whenever one or more of that component is used in the manufacture of a product, and increases when you buy more of the component. Selling a product has no effect on the inventory of the component.

----

To calculate current inventory, you will need two (or three) additional tables:

A Transaction table on which to record the manufacture and sales of the products.

A second Transaction table on which to record the purchase of components.

A 'parts list' table containing the number of each component used in the manufacture of each product.

The basic calculation to be carried out to arrive at a current inventory number for each product is:

Inventory = Total made - Total sold

All of the required information is on the Product Tx table, and the result for each product is the result of two SUMIF statements:

Product, the inventory table contains a single formula, entered in B2, and filled from there to C6:

B2: =SUMIFS(Product Tx::$D,Product Tx::$B,"="&$A2&B$1,Product Tx::$C,"Mfg")−SUMIFS(Product Tx::$D,Product Tx::$B,"="&$A2&B$1,Product Tx::$C,"Sell")

Separated into its two sections:

SUMIFS(Product Tx::$D,Product Tx::$B,"="&$A2&B$1,Product Tx::$C,"Mfg")−

SUMIFS(Product Tx::$D,Product Tx::$B,"="&$A2&B$1,Product Tx::$C,"Sell")

This part: "="&$A2&B$1

sets one of the conditions for both counts: the 'product name' in column B of Product Tx must be identical to the text string formed by the content of A2 followed immediately by the content of B1(both cells are on Product, the table containing the formula).

For the three components listed in the question, essentially the same formula would work: Count the number of this component purchased, subtract the number of products manufactured (not the number sold). Simple because one of each of these components is used in each product.

The more general solution, allowing for components that are now used in some versions of the products (and for some versions to require more than one of a specific component) isn't a great deal more complicated, but does require much more calculation. Here's a peek at one formula that could be used—one that doesn't actually work, as it is missing repeated occurrences of a missing part.

There's enough churning of the waters going on to slow Numbers significantly. The attack might provide a start for a script, though—probably a better approach if only for its ability to avoid the continuous calculation required to update the spreadsheet after every change to its content.

Partially developed formula—not intended to be used as is.

SUMIFS(Component Transactions::$D,Component Transactions::$B,$A2,Component Transactions::C,”=Buy")−SUM(SUMIFS(Product Transactions::$D,Product Transactions::$B,"P1a",Product Transactions::$C,"Mfg")×INDEX(Parts list::$A:$H,MATCH("P1a",Parts list::$A,0),MATCH($A2,Parts list::$1:$1,0)),SUMIFS( Product Transactions::$D,Product Transactions::$B,"P1b",Product Transactions::$C,"Mfg")×INDEX(Parts list::$A:$H,MATCH("P1b",Parts list::$A,0),MATCH($A2,Parts list::$1:$1,0)),SUMIFS( Product Transactions::$D,Product Transactions::$B,"P2a",Product Transactions::$C,"Mfg")×INDEX(Parts list::$A:$H,MATCH("P2a",Parts list::$A,0),MATCH($A2,Parts list::$1:$1,0)),SUMIFS( Product Transactions::$D,Product Transactions::$B,"P2b",Product Transactions::$C,"Mfg")×INDEX(Parts list::$A:$H,MATCH("P2b",Parts list::$A,0),MATCH($A2,Parts list::$1:$1,0)),SUMIFS( Product Transactions::$D,Product Transactions::$B,"P3a",Product Transactions::$C,"Mfg")×INDEX(Parts list::$A:$H,MATCH("P3a",Parts list::$A,0),MATCH($A2,Parts list::$1:$1,0)),SUMIFS( Product Transactions::$D,Product Transactions::$B,"P3b",Product Transactions::$C,"Mfg")×INDEX(Parts list::$A:$H,MATCH("P3b",Parts list::$A,0),MATCH($A2,Parts list::$1:$1,0)),SUMIFS( Product Transactions::$D,Product Transactions::$B,"P4a",Product Transactions::$C,"Mfg")×INDEX(Parts list::$A:$H,MATCH("P4a",Parts list::$A,0),MATCH($A2,Parts list::$1:$1,0)

Partially developed formula—requires further work before being put to use.

First step in simplifying is to shorten the table names—makes no change to the effectiveness (or lack of same) of the formula, but does make it a bit shorter. That's why you'll see "Product Tx" in the image above instead of "Product Transactions"

Not sure when I'll get time to get back to this.

G'Night,

Barry

Reply