lookup three tables, extract information and create summary of due dates for planning

[adelkam]

I use a spreadsheet to manage an inventory of 25 items in stock. I need to monitor the stock and project the date when I should place an order before any item runs-out. I have three tables:

Table 1- listes the “Projected” quantity to be used of each item over the next 60 days by date

Table 2- lists the “Stock” that is currebtly available for each item

Table 3- Lists the items including the “lead time”, which is the number of days it takes until the item gets shipped when I order

What I need to accomplish is the following:

I need to determine "when" I should order each item in light of the given information? I need to determine when I will run out of each item (if I would!): this is from information in table 1 (projected use by date) & table 2 (stock available)

Then I need to list when to order: By knowing when I will run out of each item (determined above) & table 3 (contains lead time it takes for each item to get delivered) - This is by simpley subtracting the number of days (lead time for shipping) of that item ..
I uploaded sample file in the link below .. I appreciate any help

Inventory Planning - Download - 4shared

 

[sijpie]

wants me to sign up or log in before I can download...
Do you just need the formula?

Order when Stock=LeadTime x Usage
LeadTime in Days, Usage in Units/Day

 

[sergioMabres]

Probably you would like to use dropbox, skydrive or box for sharing files, so we can have a look at it and share a solution with you
Sergio