Weighted Moving average for Inventory.

[mahenkj2]

I am in the process of making a database for Inventory management and our accounting department need the cost of Issued material on moving weighted average.

One case:

When a material is received its price for example is $1000 each and initial supply is of 10 pcs. If 5 are issued, shall be based on $1000. Later same part 10 numbers arrives and cost of new delivery is $1500 each. Total inventory value should be weighted average of below

5 pc remaining from first lot @ $1000
10 pc from new lot @ $1500.

This way things should move on.

I think this concept is commonly used in accounting for inventory management and experts should already be knowing the method hopefully. Kindly advise how should I proceed.

best regards.

 

[Galaxiom]

I don't thnk we could make any really useful suggestion without knowing a lot more details about your data structure.

 

[mahenkj2]

The desired details are as under:

I have two transactions namely Issues and Receipts. When a material is received, its information including cost is reocrded in a table. A query is made grouped by materials and summing up quantity. The same thing is done for issues. When ever a material is issued, its information is recorded in Issue table and a query is made grouped by materials.

Another query is based on above two queries which takes into account opening stock (only once required) and shows total inventory at any moment.

I still have not formulated anything on processing the receipt cost as I am not clear how to start. Still, the receipt table has cost fields and for receipts, I have two forms, one for receipts materials only and second for inputting the cost later anytime.

I assume I could input what all you wanted, kindly let me know if you need further details or in some other format.

regards.

 

[mahenkj2]

Does somebody have idea or can indicate me some clues to go further.

 

[RainLover]

Do you know what formula you wish to apply.

 

[jdraw]

I suggest you start here
http://en.wikipedia.org/wiki/Moving_average and see the depth of RainLover's question.

 

[RainLover]

I was thinking that as the Accounts department has asked for this then they must know what to use.

Anything else will not satisify that department. If it does then they do not know what they really want.

Another thought would be to consult the Logistics manager.

 

[Galaxiom]

Once having calculated the stock levels you work backwards through the received transactions adding the costs of each until you reach the one where the received stock is partially issued. Then apportion that transaction pro rata.

It probably could be done using a variation on a running total query.

Otherwise it could be done stepping pack through a recordset making the calculation on the way. In most cases it is probably only going to be one or two steps.

This would be implemented as a function. Would probably be a useful function to include in the system even if you do the big reports with a query.

 

[mahenkj2]

Thanks for the suggestions. Galaxioms reply becomes an outline for me to start. I do not know yet to define functions but I would like to first start in making logic in excel and then I may again request for function.

best regards.

 

[RainLover]

Sounds like a good plan to me.