I use a spreadsheet to track the inventory status of 25 items in stock. I need to plan for when I should place an order before any item runs-out. My spreadsheet contains 3 simple tables as follows:
I need to populate a summary table for all items and determine "when" I will run-out of any item, and "when" I should order each item in light of the given information above?
I think the approach can be something like:
• loop through the 60 days of projected use for all items, and calculate the accumulated quantity as we loop
• compare to the available in stock, when less than available in stock, capture the date to add in the summary table
• Calculate "when to order" by subtracting the "lead-time", which is the time required for shipping, from the date we obtained at previous point, and record that date in the summary under "when to order"..
I appreciate any help (sample file is uploaded at: Inventory - Download - 4shared)
- Table shows 60 days in advance of projected use of each item by date (how much will be used in each date)
- Table for currently available in stock for each item
- Table lists all items and the “lead time” for each (i.e. the number of days it takes, when I order until the it gets shipped to me)
I need to populate a summary table for all items and determine "when" I will run-out of any item, and "when" I should order each item in light of the given information above?
I think the approach can be something like:
• loop through the 60 days of projected use for all items, and calculate the accumulated quantity as we loop
• compare to the available in stock, when less than available in stock, capture the date to add in the summary table
• Calculate "when to order" by subtracting the "lead-time", which is the time required for shipping, from the date we obtained at previous point, and record that date in the summary under "when to order"..
I appreciate any help (sample file is uploaded at: Inventory - Download - 4shared)
