# Subtract forecasted usage & return a date?

aero-spaces
Guest
Posts: n/a

 12th Jun 2008
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)

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).

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
Guest
Posts: n/a

 12th Jun 2008

"aero-spaces" <(E-Mail Removed)> wrote in message
news:E13E623A-9F64-4653-A56D-(E-Mail Removed)...
>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)

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).
>
>
>
> 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
Guest
Posts: n/a

 13th Jun 2008
that we can better understand the situation.

"Brian Evans" wrote:

> 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?

Ryan C.

aero-spaces
Guest
Posts: n/a

 13th Jun 2008
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])));

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post jland Microsoft Excel Misc 1 25th Mar 2008 04:21 AM =?Utf-8?B?UHJvamVjdA==?= Microsoft Excel New Users 0 22nd Feb 2005 12:35 PM =?Utf-8?B?TWVtb3J5IFVzYWdlL0NQVSBVc2FnZQ==?= Windows XP General 2 4th Jul 2004 04:25 AM shalin Microsoft Excel Charting 1 8th Jan 2004 10:56 PM brosenau[ Windows XP Performance 6 3rd Dec 2003 04:37 PM

Features