Using Trends to predict when to order just-in-time
Posted on 1997-05-15
I am a newbie VB programmer. I am in "advanced" programming in my school. The first semester was about HTML coding and now we are learning about VB code. The problem is, our school isn't that rich and the guy who is teaching the class doesn't have enough time to teach our class (he donated his time freely), so now my school is calling the class an "Independent Study" class (because he ins't teaching or helping us anymore). Any ways, has given us our final project and we have little coding know-how to do his little wizard VB projects.
We are using the VB 4.0 that comes with Excel 95. My project is "Inventory Management". I have to use the TREND function to predict when to order stock, for just-in-time. In my module, I would have something like this for code:
OnHandRead = Application.InputBox("Number of Products Sold", "Enter on hand count", OnHand)
OnHand = IsNumeric(OnHandRead)
If OnHand = False Then MsgBox "Invalid Number", 16, "Error"
Loop Until OnHand = True
OnHand = OnHandRead
(and one for dates too) I was thinking of taking the dates and converting them back to real numbers and taking the TREND of that (i.e. 1/1/98 comes out to be around 36790 or something like that).
So, lets say we have 39790, 39791, 39792, 39793, 39794 for the numbers for the dates, so TREND(39790, 39793, 39796, 39799, 39802) would produce 3 days for the trend, right? Then, I could have 5,10,15,20,25 for each day for the number of products sold, so that TREND(5,10,15,20,25) would produce 5? Then I could have them type in the original on-hand they had and setup something like this (assuming they originally had 100 on-hand):
SomeVar = (100 - [30 / 3] * [5 * 1])
If Somevar / OrginalOnHandCount < 40 then something...???
I am not sure what formula to use or how to set it up. I already tried the TREND function in the module coding and didn't work. Any formulas, coding or suggestions will taken gladly.