Re-edit average price

Dear All,

        I am trying to build simple pos application where I receive goods. The problem each time I receive goods I will average the overall cost price inclusive of the new stock. Then based on this new cost price I would transfer my goods over to another outlet. My problem is described below with an example.

Ok let me explain the data first.

  1. ReceiveOrderID=1 so now the average price is 50.00

2)ReceiveOrderID=2 the price is now 45.00 so the average become 47.50

3)TransferID=1 so the average price is the transfer price that is 47.50

4)RecieveOrderID=3 here where say there is a mistake happened instead of 45.00 then it was entered as 450.00 so the whole average price goes wrong it becomes 198.44

5)TransferID=2 so the average price is now wrong and transfered as 198.44 so there after all the next stock which goes into the outlet will be wrong

6)ReceiveOrderID=3 the price is now 50 so the average become 167.04 due to earlier mistake.

So can you see one mistake will lead to all next mistakes? So do you have any idea how to solve this kind of mistake? Thank you.

The sample data is attached in .doc document.

If you enter in a wrong number, you need to fix it. No algorithm is going to correct for that 450 if you leave it there.

Assuming you get the right numbers in, you can recompute the average each time you insert a row. Select the sum of the costs divided by the number of rows.

Also, shouldn’t you be taking into account how many items (quantity) you add at each price?

Dear Dan,
How to correct it in this case it have been transfered over to another outlet? Is it in this case I have to work backwards is it? For the point of the mistakes then work from there n do corrections is it? It have to be manually done is it?

Can’t you just update that row in your table with the correct price?

Dear Dan,
No the problem is that one mistake will propogate as you can see and will make all the rest prices to be wrong. So we have to do a lot of changes that is why I dont know how to go about it to make it to correct pricing?

I fail to see why this is a big problem, just correct the wrong price and correct the 3 averages that were affected

UPDATE ReceiveOrderTable SET CostPrice = 45, AveragePrice= 46.67 WHERE ReceiveOrderID = 3;

UPDATE ReceiveOrderTable SET AveragePrice = 47.5 WHERE ReceiveOrderID = 4;

UPDATE TransferTable SET TransferPrice = 46.67 WHERE TransferID = 2;

Dear Dan,
The problem which I shown you was a simple one. In reality there is many more outlets will be involved in many different transaction between the outlets e.g transfer from outlet to another outlet etc. So when the average price is wrong in outlet then after transfer to another outlet then it will also be affected and thereafter the rest of the transfers. So that where I see the complexity. So do u have any algorithm to solve this problem? Thank you.