Weighted Price Average Inventory DB

[skunk711]

I have a database that I have been maintaining for a while the serves as an inventory control DB. It supports the recevial, transfer, return and issuing of items.

Everything works perfectly, but I'd like to extend the functionality of the DB to use a weighted average for the value of the items in the store, rather than a straight multiplcation of the current price by the unit qty.

Has anyone had experience in this field before? I have some basic idea of how 'd like to achieve this, but to put it in code has me a little stumped.

Even a FIFO system would be better than what I am using.

Does anyone have ideas?

 

[KKilfoil]

Correct me if I'm wrong...

If a transaction is a removal from stock, then the weighted average price is unaffected.

If a transaction is an addition to stock (either a receival or a return), the a new weighted average need to be computed.

The formula for the new price would be something like
NewPrice = (OldPrice * OldQuantity + AdditionsPrice * AdditionsQuantity) / (OldQuantity + AdditionsQuantity)

for the particular stock item being added to stock.



So what you could do depends on how you process a stock transaction.

Do you

A) Modify a single existing record for each stock item to reflect the changes caused by a transaction (simple but lacks history)

B) Add a separate record for each transaction, and then sum up all of the transactions for a particular item whenever you want to know quantity-in-stock

?

 

[skunk711]

Well the answer to your last question is that I have a seperate record for each transaction.

I may be wrong (or maybe what I am after is not a weighted average at all, perhaps it is a FIFO queue) but I was hoping to organise something along the lines of:

Receive 10 items at $1.00
Receive 10 items at $1.50
the avg cost of the items would be $1.25.

If i then issued 12 items, I would want the new price for the items to be $1.50 (8 units remain at the price).

This seems to be First In First Out, so sorry about misleading you in the first post.

 

[namliam]

I dont think this can be done with a query, unless your keeping track of issuing if items in the supply table as well....

If not you (or someone else) will have to do some VBA to track it.
This will then take up more and more time as you get more ins and outs of supplies.

I would suggest that you would build in a 'remaining supply' field in the supply table. Once you have that, the weighted avg of the remaining supply will be a 'peace of cake' query.

Regards

 

[KKilfoil]

skunk711:

You have now described a FIFO.


To do a Weighted Average:

What I would do is create a couple of fields in your stock item master table (NOT your transaction table). Lets call them [QuantInStock] and [ValueInStock]. The unit price can be easily calculated in a query as UnitPrice: [ValueInStock] / [QuantInStock].

Everytime you do a transaction (i.e. add a transaction record), you would need to run an update query to modify these two values.

Create a form with unbound editbox controls for each field in your transaction table, and add a control button to 'post' the transaction.

The value of unit cost for Issue-type transactions can be retrieved from the stock item master table. Adjustments for shrinkage can be treated as an Issue.

The VBA code associated with the button would have to do a couple of things:

1) perform any validity checking on the transaction as required by your business rules (i.e. do not allow a zero-quantity transaction, etc...)

2) run an pre-defined append query that adds a record to your transaction table based on the form's field values.

3) Take the existing values for [QuantInStock] and [ValueInStock] and modify them based on the values on your form. I would do this in a predefined update query, where [QuantInStock] updates to [QuantInStock] + forms![YourForm]![YourTransactionQuantityField] and [ValueInStock] updates to [ValueInStock] + forms![YourForm]![YourTransactionQuantityField] * forms![YourForm]![YourTransactionUnitPriceField]

Note that your average price will effectively remain the same for an Issue transaction, because the UnitPrice of the transaction was determined from the existing value in stock in the first place.

For this to work, you must use negative quantities when removing items from stock, so that they 'add' properly to achieve a reduction in [QuantityInStock] and [ValueInStock].



Have I confused you yet?