Inventory Days Cover - Creating Dynamic Query

[bdh]

Hi,



I am trying to create a query using 2 tables that will allow me to determine days of cover of inventory by using current inventory balance and the forward demand by day. i.e:

1) inventory on hand by product
2) forward demand by product by Day

I am looking to create a formula field that allows me to do this.

Noting:
- Inventory is held at different locations/DCs
- Not every day will have demand

Can someone please assist by helping determine the correct formula to be used?

An example of the datasets:

1) inventory - balance = SOH


2) demand

[June7]

[Item IDNT] is the product code? Use an aggregate (Totals) query to summarize by product code. Access Help has guidance on building Totals query.

Not sure I understand your other requirement. Possibly calculate average use over a specified period as an indicator of future requirements. This is another aggregate query.

[bdh]

[Item IDNT] is the product code? Use an aggregate (Totals) query to summarize by product code. Access Help has guidance on building Totals query.

Not sure I understand your other requirement. Possibly calculate average use over a specified period as an indicator of future requirements. This is another aggregate query.

Item IDNT is the product code.

I know how to build basic queries, what i need is assistance with a build formula that allows precise calculation of the days cover by taking into account each day of sales demand going forward.

For example: Scenario Item 123

	STOCK ON HAND	SALES DAY1	SALES DAY2	SALES DAY3	SALES DAY4
ITEM 123	100	25	20	0	6

in this example Stock On Hand is 100 for Item 123. An average is not precise and can be diluted by zeros and impacted by demand outside of the true days cover. Using this method days cover = 100/average(25,20,0,6) = 8 days.

What is required is a formula that steps through day by day and determines the sales day where the stock on hand is diminished to zero. In this case the formula would get to Sales Day 4 before inventory decrease to or below 0. Therefore the days cover would be 3.9 days.

[June7]

So item 1 is resolved?

And item 2 is issue?

Lost me with your determination of 3.9 days. Stock starts at 100. By day4 51 items are used, 49 remaining. How is inventory at or below 0? How did you derive 3.9?

What does 'steps through day by day' involve? This would take place over what period? What would be the dataset to use - Sales details?

You need to describe in plain English an algorithm and maybe we can devise a method to program it. Whatever the calculation, I expect will need a custom VBA function to accomplish.