Detrmine if inventory movement is occuring in FIFO order

[Todd R K]

Good afternoon!

Background

Each day, I receive an inventory report that I paste into a different tab labeled with the current date (the tabs are numbered according to the numerical equivalent of the date). The list will change in length and composition on a daily basis but will usually be approximately 1200 rows long.

Goal

I need to compare the current list with the previous day's list to determine if the inventory was utilized in FIFO order. In other words, the oldest inventory must be used first.

Examples

In the examples below, I have created a small sample of two consecutive inventory reports. When you look at the second report, you can see that the Gizmo inventory from container 0003 dated 05/05/05 was used before the Gizmo inventory dated 11/09/04, which is also in container 0003. This is determined by the change in the inventory amount from 690,000 to 400,000.

In addition, the Widget inventory from container 0017 dated 11/30/05 was used before the Widget inventory from container 0001 dated 11/18/05. Also, when a container is completely emptied as in the case of container 0017, it is removed from the list the following day.

Both of the above types of scenarios will usually occur each day.

Finally, there are 7 types and approximately 10 subtypes.

Summary

I would like to have a small matrix on a separate sheet with a column for each of the 7 types that would alert me if newer inventory were used before older inventory.

How can I achieve this?

Thank you

Todd

FIFO Tracking 112205.xls
	A	B	C	D	E
1	Container	Type	Subtype	FIFO Date	Amount
2	0001	Widget	301	Nov 18 2005	208,000
3	0001	Widget	301	Nov 23 2005	36,000
4	0003	Gizmo	202	Nov 9 2004	540,000
5	0003	Gizmo	202	May 5 2005	690,000
6	0017	Widget	301	Nov 30 2005	480,000
38688

FIFO Tracking 112205.xls
	A	B	C	D	E
1	Container	Type	Subtype	FIFO Date	Amount
2	0001	Widget	301	Nov 18 2005	208,000
3	0001	Widget	301	Nov 23 2005	36,000
4	0003	Gizmo	202	Nov 9 2004	540,000
5	0003	Gizmo	202	May 5 2005	400,000
38689

 

[Todd R K]

Good morning:

After re-reading my post, I think it may have been too long. Here is the abridged version.

If the inventory amount changes, was it the oldest inventory? If not, then what is the date that should have been used?

I think I am struggling with how to perform multi conditional look-ups or I am approaching the problem incorrectly. For example, I would like to identify the container # with the oldest inventory of a particular type, subtype from the previous day and compare the inventory amount to the next day's inventory report for that container.

Does this clarify my problem or am I still not articulating the issue concisely?

Thank you

Todd

 

[Todd R K]

Friends

I think I have finally been able to articulate my questions based on the HTML example below:

=if(B3:B1200="widget",and,C3:C1200=301,what is the earliest FIFO date)
(Answer is 11/18/05)
=if(B3:B1200="widget",and,C3:C1200=301,what is the container that matches the FIFO date found above) (Answer is 0001)

With these two formulas, I can identify the container that should have been used. The final test would be:

=if(B3:B1200="widget",and,C3:C1200=301,what is the container number whose amount changed from yesterday's inventory report) (answer is 0003)

With the above information, I know that the wrong container was used and with conditional formating, I can create an "alert" in my Dashboard.

How do I construct the multi conditional formulas that I have described above?

Thank you

Todd

FIFO Tracking 112205.xls
	A	B	C	D	E
1			Yesterday's inventory report
2	Container	Type	Subtype	FIFO Date	Amount
3	0001	Widget	301	Nov 18 2005	208,000
4	0001	Widget	301	Nov 23 2005	36,000
5	0003	Gizmo	202	Nov 9 2004	540,000
6	0003	Gizmo	202	May 5 2005	690,000
7	0017	Widget	301	Nov 30 2005	480,000
8
9
10			Today's inventory report
11	Container	Type	Subtype	FIFO Date	Amount
12	0001	Widget	301	Nov 18 2005	208,000
13	0001	Widget	301	Nov 23 2005	36,000
14	0003	Gizmo	202	Nov 9 2004	540,000
15	0003	Gizmo	202	May 5 2005	400,000
38688

 

[Todd R K]

Good afternoon:

The more I work on this issue, the more it seems that applying a logical test to a range versus a single cell is the answer (e.g. the if statement below that is looking at the range B3:B1200 or C3:C1200).

=if(B3:B1200="widget",and,C3:C1200=301,what is the earliest FIFO date)

I have performed searches but have not found anything that addresses this issue.

Any suggestions?

Thank you

Todd

 

[Todd R K]

Bump