Calculating numbers for an Inventory purchase forecast report

[xicana]

I need to create a forecast report that gives an estimate of how much of an inventory item should be purchased in order to have enough on hand for the following month.

Besides pulling up information (like how much onHand, onPurchaseOrder, onSalesOrder and onBackOrder - which are easy enough) straight from a table, my purchase amount number is calculated by averaging the "sold" amount from the previous 3 months (periods). Periods are based on a fiscal year that begins in June (Period 1 = July).

So, if I were to pull the report today (August=Period 2), my purchase amount number would be calculated like so: [(QtySoldPeriod1 + QtySoldPeriod12 + QtySoldPeriod11) / 3]. But you see, I would need to pull QtySoldPeriod12 and QtySoldPeriod11, where the "year" is 2003 (that field is available in the table where QtySoldPeriod12 is located by the way)...

I'm working off of four tables imported from MAS200:
IM1 - InventoryMasterFile (average cost, item description..etc)
IM2 - InventoryItemDetail (onHand, onPO, etc...)
IM3 - ItemSalesDetail (this has the two fields that tell me how much was sold in each period and what year it corresponds to)
IM0 - Parameter Record (this table tells us what period we're in and the current year - seperate fields)


I understand the logic behind the data I need - I just don't know the syntax or where the code must go (in a query, or in the actual report - and if on the report, where?)

I hope this is clear...and I hope someone out there can help!


Sandy

 

[MichaelRed]

hmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmm,


For starters, I don;t see any real reason to deal with the "Fiscal" year stuff. Regardless of that "item", you just want the data rfrom the three previous months.

This is easily obtained (I think) from simple date math (DateDiff; DateSerial; ... ) and, perhaps, some small recourse to the ubiquitous {F1} (aka HELP).

Although keeping sales by "period" may complicate the issue. Even there, a small procedure should be able to 'un-translate' you periods to have a (MS style) valid date ...





MichaelRed
mlred@verizon.net

 

[xicana]

Well,
I do need to deal with the fiscal year because the table I imported looks something like this:

Item Year QtySoldPeriod12 QtySoldPeriod1 QtySoldPeriod2 and so on
cracker 2003 5 6 3
cracker 2004 3 4 0

If I'm in August (period 2 of fiscal year 2004), I'm going to need the data from QtySoldPeriod12 (June)where the year is 2003 (fiscal year) - I'm probably not making myself clear...just wanted to throw this out there to get some ideas...

I've got some formulas going on the actual report that will give me the current period and the fiscal year...now I just need to get the Sold numbers from the correct years into the report...I feel like I'm close...we'll see what I have at the end of the day.

Thanks MichaelRed,


Sandy

 

[mrstnturner]

I have created somethinga little more detailed but for the same type of purposes, but I did mine through crystal reports. You might try this, it is easier to parameter and alter fields.

 

[xicana]

I know this is a *little* late but I never received an email alert to this last response...

I believe we tried doing it in Crystal - but I know more Access than I do crystal (if you can believe it!) - We were able to find a solution - this is too many projects behind for me to remember what worked - but I think we worked it out within Access.

Thanks Mrstnturner!


Sandy