Subtract forecasted usage & return a date?

[aero-spaces]

I have literally been working through this problem in my brain for over a
year, and I still have not been able to come to any conclusion (don't ask why
I'm just now asking for help ).

I have information in three different tables that give me the following
information for each part number of a manufacturing plan:

*current inventory level (updated daily), and recommended min/max levels
*average daily usage (in both a "used x per day" and "used every x days"
format)
*forecasted usage by month (more info below)

First, let me give some filler data to talk about what I am looking for:

tbl_CurrentInventory =
[PartNumber], [qty], [min], [max]
12345A, 20, 6, 24
56789B, 5, 3, 9

tbl_Usage =
[PartNumber], [Used x per day], [used every x days]
12345A, 0.33, 3
56789B, 0.03, 100

tbl_Forecast =
[Part Number], [Date of Last Update], [m1], [m2], [m3],...,[m25] (where m1=
current month, m2=next month, etc.)
12345A, 6/12/08, 3, 7, 7,...,7
56789B, 6/12/08, 0, 0, 1,...,1

OK, now my problem--
I would like to know when our current inventory level will reach the min
(both from the tbl_CurrentInventory table) based mostly on the forecasted
usage table. The reason is this: on many parts, the usage is fairly constant
and higher (in the above example, the one that's used every 3 days). On
others, the usage is lower and more sporadic, i.e., they will use 1 a month
for several months, then go to 0 for several months, etc. The system we have
currently works fine the higher the average usage, because our margin of
error is +/- 3 days--in the above example--which is for the most part,
acceptable. On the sporadic low-usage parts, however, our current queries
will say we'll run out in 200 days (i.e., if we have min+2), but as soon as
they use one part, it will instantly drop to 100 days. Not very helpful when
planning manufacturing schedules.

What I want is a query that will take the current inventory level ([qty]),
then subtract each month of the forecast until it reaches [min], then stop
and return that month. THEN use the average usage ([used every x days])
starting from the 1st of that month to tell me which day of that month we'll
hit [min].

I'm having a VERY hard time conceptualizing how this can work, so more than
anything, I need help in the concept part, and then I can come back here if I
run into technical problems. I'll post our current query below which only
uses the average daily demand (AVGDD below).

Thanks in advance for your ideas!!


SELECT [tbl-CurrentInventory].[Part Number], [tbl-CurrentInventory].[Min],
[tbl-CurrentInventory].[qty], [tbl-CurrentInventory].[Max],
[tbl-CurrentInventory].Status, ([qty]-[Min]) AS InvOverMin, tbl_Usage.AVGDD,
Round(daysUntilZeroFunction([AVGDD]),4) AS UsedEveryXDays,
([InvOverMin]*[UsedEveryXDays]) AS DaysUntilMin,
([tbl-CurrentInventory]![Status]+[DaysUntilMin]) AS DateInvMin
FROM [tbl-CurrentInventory] INNER JOIN tbl_Usage ON
[tbl-CurrentInventory].[Part Number] = tbl_Usage.PartNumber
WHERE ((([tbl-CurrentInventory].[qty])>[Min]));

Here's the function referenced:

Public Function DaysUntilZeroFunction(AVGDD)

If (AVGDD < 1) Then
DaysUntilZeroFunction = Round((1 / AVGDD))
ElseIf (AVGDD > 1) Then
DaysUntilZeroFunction = Round((1 / AVGDD), 3)
Else
DaysUntilZeroFunction = 0
End If

End Function

 

[Brian Evans]

I have literally been working through this problem in my brain for over a
year, and I still have not been able to come to any conclusion (don't ask
why
I'm just now asking for help ).

I have information in three different tables that give me the following
information for each part number of a manufacturing plan:

*current inventory level (updated daily), and recommended min/max levels
*average daily usage (in both a "used x per day" and "used every x days"
format)
*forecasted usage by month (more info below)

Months aren't a good division to track this type of thing with. They vary
too much in length and especially work days. One solution is to use
13 periods of 4 weeks each (Just call them periods A thru M).

First, let me give some filler data to talk about what I am looking for:

tbl_CurrentInventory =
[PartNumber], [qty], [min], [max]
12345A, 20, 6, 24
56789B, 5, 3, 9

tbl_Usage =
[PartNumber], [Used x per day], [used every x days]
12345A, 0.33, 3
56789B, 0.03, 100

tbl_Forecast =
[Part Number], [Date of Last Update], [m1], [m2], [m3],...,[m25] (where
m1=
current month, m2=next month, etc.)
12345A, 6/12/08, 3, 7, 7,...,7
56789B, 6/12/08, 0, 0, 1,...,1

OK, now my problem--
I would like to know when our current inventory level will reach the min
(both from the tbl_CurrentInventory table) based mostly on the forecasted
usage table. The reason is this: on many parts, the usage is fairly
constant
and higher (in the above example, the one that's used every 3 days). On
others, the usage is lower and more sporadic, i.e., they will use 1 a
month
for several months, then go to 0 for several months, etc. The system we
have
currently works fine the higher the average usage, because our margin of
error is +/- 3 days--in the above example--which is for the most part,
acceptable. On the sporadic low-usage parts, however, our current queries
will say we'll run out in 200 days (i.e., if we have min+2), but as soon
as
they use one part, it will instantly drop to 100 days. Not very helpful
when
planning manufacturing schedules.

Historical usage information is needed for this. The current inventory on
it's
own is not enough. If a part is used every 100 days and one was used 50 days
ago there is still 50 days left before the next estimated usage or 1/2 a
part.
For low use parts it matters if the last usage was last week or three months
ago.

What I want is a query that will take the current inventory level ([qty]),
then subtract each month of the forecast until it reaches [min], then stop
and return that month. THEN use the average usage ([used every x days])
starting from the 1st of that month to tell me which day of that month
we'll
hit [min].

I'm having a VERY hard time conceptualizing how this can work, so more
than
anything, I need help in the concept part, and then I can come back here
if I
run into technical problems. I'll post our current query below which only
uses the average daily demand (AVGDD below).

Thanks in advance for your ideas!!


SELECT [tbl-CurrentInventory].[Part Number], [tbl-CurrentInventory].[Min],
[tbl-CurrentInventory].[qty], [tbl-CurrentInventory].[Max],
[tbl-CurrentInventory].Status, ([qty]-[Min]) AS InvOverMin,
tbl_Usage.AVGDD,
Round(daysUntilZeroFunction([AVGDD]),4) AS UsedEveryXDays,
([InvOverMin]*[UsedEveryXDays]) AS DaysUntilMin,
([tbl-CurrentInventory]![Status]+[DaysUntilMin]) AS DateInvMin
FROM [tbl-CurrentInventory] INNER JOIN tbl_Usage ON
[tbl-CurrentInventory].[Part Number] = tbl_Usage.PartNumber
WHERE ((([tbl-CurrentInventory].[qty])>[Min]));

Here's the function referenced:

Public Function DaysUntilZeroFunction(AVGDD)

If (AVGDD < 1) Then
DaysUntilZeroFunction = Round((1 / AVGDD))
ElseIf (AVGDD > 1) Then
DaysUntilZeroFunction = Round((1 / AVGDD), 3)
Else
DaysUntilZeroFunction = 0
End If

End Function

 

[aero-spaces]

Thanks for your reply.. I will try to respond to each of your comments so
that we can better understand the situation.

Months aren't a good division to track this type of thing with. They vary
too much in length and especially work days. One solution is to use
13 periods of 4 weeks each (Just call them periods A thru M).

Unfortunately, all three of the above mentioned tables come directly from
the end customer, not something that's internally generated. And while I can
understand wanting to get it in a more uniform schedule of 13 4-week periods,
the data doesn't come to me in that format, and it would be very difficult to
force the data into that format, not knowing exactly when during those months
they plan to use the parts.

Historical usage information is needed for this. The current inventory on
it's
own is not enough. If a part is used every 100 days and one was used 50 days
ago there is still 50 days left before the next estimated usage or 1/2 a
part.
For low use parts it matters if the last usage was last week or three months
ago.

Yes, this has been one of my stumbling blocks as well. Perhaps you can help
me figure out how to record this data from my existing queries--

I have one query that compares today's current inventory to yesterday's
inventory and subtracts it giving me the number of parts used in the past 24
hours. Can I use this to build a table that records historical use data? If
so, do I keep all records for each part number, or do I only keep the most
recent usage? And then, assuming I can get this historical use table built,
what would my next step be?

Thanks again for your help!
Ryan C.

 

[aero-spaces]

Just to keep you updated on my progress:

I created a table with [Part Number], [Status] (date), and [Usage] (the
recorded difference on any given day). I will include this in the daily run
macro so that every day's usage will get appended to this table.

Now, to select *only* the most recent date in which a part was used, I have
come up with this query, let me know if this is correct:

SELECT tbl_HistUsage.[Part Number], tbl_HistUsage.Status
FROM tbl_HistUsage
GROUP BY tbl_HistUsage.[Part Number], tbl_HistUsage.Status
HAVING (((tbl_HistUsage.Status)=Max([tbl_HistUsage]![Status])));