[Trunk/7.0/6.0/5.0] return products to supplier does not update price average

Bug #610738 reported by invitu
156
This bug affects 31 people
Affects Status Importance Assigned to Milestone
Odoo Addons (MOVED TO GITHUB)
Confirmed
High
OpenERP Publisher's Warranty Team

Bug Description

When returning products to a supplier, the average standard price should be updated using the price of the packing list which is returned.

For example :
1- We have 10 products with average price = 100
2- We receive 5 products with price = 80 --> average price is calculated as (10*100+5*80)/(10+5) = 93,33
3- We return 3 products from the last packing list (the one where products cost 80)
The new average price should be
(93,33*15 - 3*80)/(15-3)=96,67

If the return is made directly from the stock and does not concern any incoming packing list, we do not update the average price.

Related branches

Changed in openobject-addons:
status: New → Confirmed
Revision history for this message
Nhomar - Vauxoo (nhomar) wrote :

@Jay....

Hello.

Be carefull, take the price from the picking related to the return packing, not any value from the product.....

I mean:

the behaviour exposed by invitu is the idealsituation and he is right, but in the real world exist a case with several purchases of several products and the return _must_ be done taking in reference exactly _wht packing is being returned.

Taking the same invitu's example and adding this situation

For example :
1- We have 10 products with average price = 100
2- We receive 5 products with price = 80 --> average price is calculated as (10*100+5*80)/(10+5) = 93,33
NEW SITUATION 2.5- We receive 5 products with price = 82 --> average price is calculated as (10*100+5*82)/(15+5) = 90,5
CASE A.
3- We return 3 products from the last packing list (the one where products cost 82)
The new average price should be
(93,33*20 - 3*82)/(20-3)=92
CASE B
3- We return 3 products from the last packing list (the one where products cost 80)
The new average price should be
(93,33*20 - 3*80)/(20-3)=92,35

As you can see is not the same taking as reference one or another...

I attach a spreedsheet that explain this example with this explicit equations are in cells and the verification model of the concepts, _ALWAYS_ the cost calculated by the average price* inventory should be the same that sum all transactions (quantity1*pricetransactios1+quantity2*pricetransactios2+.......)

thanks jay.

Changed in openobject-addons:
importance: Undecided → Critical
Revision history for this message
Nhomar - Vauxoo (nhomar) wrote :
  • COSTO.ods Edit (18.7 KiB, application/vnd.oasis.opendocument.spreadsheet)

Sorry I attach the wrong file..... I attach again.

regards

Revision history for this message
invitu (invitu) wrote :

Nhomar,

You got what I meant but there was some errors in your formulae (btw, the results were ok). It should be :
1- We have 10 products with average price = 100
2- We receive 5 products with price = 80 --> average price is calculated as (10*100+5*80)/(10+5) = 93,33
2.5- We receive 5 products with price = 82 --> average price is calculated as (15*93,33+5*82)/(15+5) = 90,5
CASE A.
3- We return 3 products from the last packing list (the one where products cost 82)
The new average price should be
(90,5*20 - 3*82)/(20-3)=92
CASE B
3- We return 3 products from the last packing list (the one where products cost 80)
The new average price should be
(90,5*20 - 3*80)/(20-3)=92,35

Jay, could you keep in mind that the returns from customers should follow the same rule EXCEPT that you should take the cost price instead of the sale price to update the average price. I opened a bug for customer returns case : https://bugs.launchpad.net/openobject-addons/+bug/613286
and there also is a linked bug in case packing are cancelled or deleted : https://bugs.launchpad.net/openobject-addons/+bug/495363

==> 2 consequences :
- returns to suppliers and returns from customers should not take the same price to update average price
- both cost price and sale price should be stored in the picking. In the purchase pickings, the cost price field should remain empty

Moreover, please take into account that products could be re-returned (and re-re-returned...etc) in both supplier and customer cases.

Thanks for your attention

Regards

Revision history for this message
Nhomar - Vauxoo (nhomar) wrote :

In the attached file they are fine (the second one, the first one was wrong....)

But, we are tottally agree this is a real big Bug, because the accounting is almost unauditable with this if you automate the proceess of cost (almost all ERP have this functions in core) is when the account people love the ERP, is impossible (at least you sell _only_ services) use out of the box this cost accounting feature....

BTW it should be ported to both versions V5 and V6.

regards.

Changed in openobject-addons:
assignee: nobody → DHS(OpenERP) (dhs-openerp)
Changed in openobject-addons:
importance: Critical → Medium
assignee: DHS(OpenERP) (dhs-openerp) → Sbh (Open ERP) (sbh-openerp)
Revision history for this message
Nhomar - Vauxoo (nhomar) wrote :

Sorry Nicolas, I'm not agree to down the priority for this bug.

But I need to show you an scenary, imagine you loss the half of all data around 100.000 transactions? in a way without logical, and if you try to recover a backup (even having planified all SDRP posible in market) you can not recover it? what would be the dimension of the problem?

Well. With Average price BAD calculated it is what is happening, almost half of all acounting moves posted around cost in an standard implementation on V5 will explote at the end of the year when all partners or consultant will have to give some answer to customers.... around .... What Happend that the stock is WRONG???, we audit the accounting every month and we detected this issue 6 month ago, we have reported at least 5 bugs related, and NOW IMHO this is CRITIC, because the error is _hidden_ and will show up so late....

We are developing a module to trace all the cost movements to back, but for example ferdinand@chricar has a merge proposal from 5 month ago related too this issue, an nothing happend, please folks, I know this issues are dificult to detect but when this is detected, You need give priority.....

Now.... Do you think this is critic or not?

Revision history for this message
Nhomar - Vauxoo (nhomar) wrote :

By the way I confirm this bug exist in V6 too!

See the pad{1}....

I could check is taken in account.... all the process to check cost related to logistic should be improved...

I know the team is working on this, my point is that not only when the program is down totally the bug should be considered Critical, this bug is at least High.

Regards.

[1]http://pad.openerp.com/6-test-purchases

Revision history for this message
hbto [Vauxoo] http://www.vauxoo.com (humbertoarocha) wrote : Re: [Bug 610738] Re: [5.0] return products to supplier does not update price average

@Nicolas,

I don't either agree with you,

this is critical, because this affects the whole data of financial and
product logistic of an enterprise,
this could represent a misled index meaning that you have an inventory with
one value when it really is not..

Undo all, and correct what all this bug has done, is daunting task

2010/10/12 Nicolas Vanhoren <email address hidden>

> ** Changed in: openobject-addons
> Importance: Critical => Medium
>
> ** Changed in: openobject-addons
> Assignee: DHS(OpenERP) (dhs-openerp) => Sbh (Open ERP) (sbh-openerp)
>
> --
> [5.0] return products to supplier does not update price average
> https://bugs.launchpad.net/bugs/610738
> You received this bug notification because you are subscribed to
> OpenObject Addons.
>

Revision history for this message
invitu (invitu) wrote : Re: [5.0] return products to supplier does not update price average

Hello

I will support the bug as critical for the reasons Nhomar explained.

Regards

Revision history for this message
Fabien (Open ERP) (fp-tinyerp) wrote :

Hello,

I am not sure it must be like that. As far as I remember my accounting books (at least for belgium) average costs must be changed only when you receive or produce products, not when you send the products. The rule is (Qty Rcvd * PO Price + Existing Qty * Current Cost) / (Qty Rcvd + Existing Qty).

The following operations should not change the average cost:
  - delivering a customer (with products that come from a particular supplier or not)
  - scrapping defects products (that come from a particular supplier or not)
  - returning the products back to a supplier (fortunately, otherwise you have to reconcile each product you send to a
    supplier with the right reception order.)

In FIFO or LIFO, those operations impact the average price, but I don't think it's the case in average cost. I looked on the web for information about this but I did not find any relevant one ? Do you have a link that explicitly says that sending product back to the supplier

If you really want your average cost not to be impacted by the products, here is two propositions of setup:
  - receive the order only partially (and cancel the remaining part)
  - set the input location as a supplier location instead of an internal one. (eventually a quality control location like the one in
    the demo data of the stock_location module.) this way, you can return the products back to your supplier before they are
    valorized in your accounting.

Our policy has been to keep OpenERP simple, if you need others features, it's better to do it as an extra module than adding several fields in several screens for each feature. One can have a lot of new requests around average cost:
   - impact the service/delivery costs automatically based on what's on the SO
   - impact the service/delivery costs automatically based on what's on the reception order
   - change the valorization of the avg cost if you scrap products from a supplier
   - propose the price which is on the supplier invoice and not on the purchase order (and thus reconcile both)
   - add automatically delivery invoices later on the cost of some products that can from a reception.
   - updating the average cost on manufacturing orders (which is the biggest lack of the current system in my opinion)

All those propositions are valid and some companies will need it. But I repeat our policy: "make it simple" ! If someone need more feature, we can still provide this as extra module so that the main modules remain simple.

PS/ I agree with Nicolas, the status is not "critical". A security issue or a bug that prevent using the software IS critical. A new feature should not be critical.

We still have hundreds of tasks to close for the V6. I hope your understand our point of view to not change the official addons for this in order to not add complexity, so I propose starting to work on a new module that implements what you need.

Changed in openobject-addons:
status: Confirmed → Won't Fix
Revision history for this message
Fabien (Open ERP) (fp-tinyerp) wrote :

I set it as won't fix. If someone is ready to contribute a new module, please change the status and assign the bug to you.

Does anyone know how this feature is implemented in very simple accounting software like sage or quickbooks ? I don't think they update the average price, when you send back products to a supplier. If I am wrong, I will reopen the bug.

Revision history for this message
Nhomar - Vauxoo (nhomar) wrote :

@Fabien,

Sorry, but I think this is still an important bug, if the tool will make somehing it should be maden in a good way.

BTW.
The solution is technically simple but the refactoring is making us study stock management code again.

ADD cost_transaction field on stock.move object.
RECORD what cost was pushed on wizard in the moment of this transaction. (In and Out dont worry in flter this.)

With this 2 little changes propose one value (actual cost) or another (real cost used in transaction when return is done) can be done easily with an extra module.
Without this change it can be almost imposible to do, this is the point.

It doesn't brake the KISS principle OpenERP if we do not do that we are broken because in the close of year - period pocedure it will be so complicated.

Revision history for this message
Nhomar - Vauxoo (nhomar) wrote :

@ Fabien.

The equation:

(Qty Rcvd * PO Price + Existing Qty * Current Cost) / (Qty Rcvd + Existing Qty).

Is correct with Purchase goods procedure.

In return sale equation is:

(Qty Rcvd * transaction Cost Used + Existing Qty * Current Cost) / (Qty Rcvd + Existing Qty).

In return purchase equation is:

(Existing Qty * Current Cost - Qty deliv * PO Price) / (Existing Qty - Qty deliv ).

i'm surprissed you didn't find support for this if when somebody studies average cost calculation this is a basic exampĺe in how to do that.

Te problem is the "Cost proposed" only this, we can develop somthing extra to be able to correct the "Purchase return" pocess because it can be more dificult but the anoher one is very plain to solve.

I hope it can help.

Revision history for this message
Joel Cabral (hephaestus) wrote :

Hello everyone,

In regard to all this, I just want to ask, how can I correct average price if I received items with incorrect price.

lets say i received item in 90 amount which is suppose to be 100 amount. Receiving items with 90 cost would make my average price wrong. And if you are saying, when returned goods no recomputation of average price. how can I correct my average price using the amount 100.

thanks,

Revision history for this message
invitu (invitu) wrote :

@Fabien, please check this article in French : http://www.comptabilite-move.com/gestion-stock/sorties-gestion-de-stock/
##
REMARQUE
Exceptionnellement, des sorties de matières peuvent s’expliquer par des retours aux fournisseurs, retours évalués aux coûts d’achat correspondants et non aux coûts de sorties.
##
It is clearly written that the returns to suppliers should be done with cost prices of the incoming picking and not with the actual cost of the products.

This article should be crossed with other references and from literature from other countries.

Revision history for this message
Nhomar - Vauxoo (nhomar) wrote : Re: [Bug 610738] Re: [5.0] return products to supplier does not update price average

Hello.

With the actual behaviour is imposible make it automatically..

You will need to return the picking.
Refound the invoices.
Recalculate by hand the cost.
Repeat the purchase opération.

If you have done sales for this product.

wait our module ;-)!! or consult an account manager.

2010/10/14 hephaestus <email address hidden>

> Hello everyone,
>
> In regard to all this, I just want to ask, how can I correct average
> price if I received items with incorrect price.
>
> lets say i received item in 90 amount which is suppose to be 100 amount.
> Receiving items with 90 cost would make my average price wrong. And if
> you are saying, when returned goods no recomputation of average price.
> how can I correct my average price using the amount 100.
>
> thanks,
>
> --
> [5.0] return products to supplier does not update price average
> https://bugs.launchpad.net/bugs/610738
> You received this bug notification because you are a member of OpenERP
> Drivers, which is subscribed to OpenObject Addons.
>
> Status in OpenObject Addons Modules: Won't Fix
>
> Bug description:
>
> When returning products to a supplier, the average standard price should be
> updated using the price of the packing list which is returned.
>
> For example :
> 1- We have 10 products with average price = 100
> 2- We receive 5 products with price = 80 --> average price is calculated as
> (10*100+5*80)/(10+5) = 93,33
> 3- We return 3 products from the last packing list (the one where products
> cost 80)
> The new average price should be
> (93,33*15 - 3*80)/(15-3)=96,67
>
> If the return is made directly from the stock and does not concern any
> incoming packing list, we do not update the average price.
>
>
>

--
Saludos Cordiales

Revision history for this message
Fabien (Open ERP) (fp-tinyerp) wrote : Re: [5.0] return products to supplier does not update price average

@invitu

No, the article you point to is about valuation using FIFO and LIFO, not about average price. In FIFO/LIFO, every transaction have impacts on the cost of the product, which is quite different from the average price method.

I would be interested to find articles that explicitly says what to do when you send back products to suppliers with average price. I did not find one. I checked my accounting book, they only talks about the reception of products. I think it's important to find references that explicitly explains the rules before doing any development on this; i heard so much different things about average price that I prefer being sure before integrating something.

Revision history for this message
invitu (invitu) wrote :

@Fabien

Thanks for you interest in this bug and for the time you spend to reply.

Please read the article again : it speaks about FIFO, LIFO, average price and stock accounting in general.
The remark I underlined at the end of the article is located outside the FIFO / LIFO / average parts. It concerns the returns to suppliers in general case, whatever the method of stock accounting.

The second line of the article explains the global rule : "En principe, tout élément stocké sort des magasins au prix auquel il était entré." => The return to supplier goes out with the price it had when it got into the stock, no doubt about it.
Then, if average method is used, the cost price should be recalculated.

In general, average method is used by companies who want to have very detailed control for their margins and do not accept approximations.

BTW, FIFO and LIFO do not interfere with returns to suppliers : when you return products to suppliers, whatever you use FIFO, LIFO, average or other method, you always return an identified product because "you have it in your hands" (it's not the same with returns from customers because when the products are at the customer's place, you lose the traceability for them).

Please believe this article, moreover, it is good sense and logical. Trust me, we will have more complicated discussions when we will talk about return to suppliers for products that have been imported (with landed costs that could be not refunded when you re-export the product back... :-))

About #9, I finally do not agree with the two propositions of setup you proposed : The aim of this bug starts at the time when the product is already in the stock, whatever the workflow that has been used to get it in. As long as the average method is proposed in the core, I strongly think that this bug should be fixed in the core because OpenERP should not make errors by default and actually, it does ! (I repeat several times the same arguments, am I too old or am I misunderstood ?)

Revision history for this message
Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c) wrote :

Dear all,

I must say I agree with Invitu & Nhomar on this point. Average price should be affected by a return at the cost of those products were purchased.

A simple example to illustrate :

Purchase & receipt 10 product at 20.- => Avg = 20.-
Purchase & receipt 5 product at 15.- => Avg = 18.33.-
Return 2 product of the last packing...

1) If no impact on average price : Avg price is still of 18.33.-
2) If returned at the purchased price: Avg price is now = 18.84.-

When I receive again the 2 missing products at the same price (15.-) the Avg price is computed...

In case 1) Avg price will be now of 17.94.- ((10*20+5*15+2*15)/17)
In case 2) Avg price will again be at 18.33.- which is the correct answer

So, if we do not change the average price when making a return, then we will have a wrong Avg price when making again the packing for the returned product.

According to my understanding, we want to know how much does this product cost me here... The case 1) doesn't give me the right answer... My customers agree with that.

I think we should make what Nhomar suggest.

Regards,

Joël

Revision history for this message
Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c) wrote :

I forgot something... I agree this should not be tagged as "Critical" as it always allow the system to work "correctly", but at least "High" seems reasonable to me.

Revision history for this message
Nhomar - Vauxoo (nhomar) wrote :

Agreed Joël, High is good too!

BTW, this bug is affecting V5 and V6, and IMHO openerp needs to refactor this concepts,

I mean, we can not for useability reasons down the quality of products, the stock issues are complex by themeselves try to make it less complex, the only real effect is externalize the problem for the accounting people, we want to help, but they need to hear us, openerp don't want hear around accounting issues, I'm really worry about it.

@fabien

We can develop all the description,we are improving V5 for this, but the more important thing in first time is record the cost used on transaction.

For rhine accounting it should be on picking line, for anglo_saxon it should be on invoice.....

Changed in openobject-addons:
importance: Medium → High
status: Won't Fix → Confirmed
Revision history for this message
Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c) wrote :

Hi again,

Well, I reset the priority to High and status Confirmed. Sorry if it make some troubles, but I wanted the OpenERP Team to pay attention to the following.

We in the community (or at least those who post on this bug) want to have a solution for this problematics. I understand your point Fabien that we cannot include everything "out of the box" and the philosophy of OpenERP is to keep things simple : We all follow you on that point.

Unfortunately, stock is not that simple... I suggest the following at least :

 - Add a field and record the purchased cost in the stock move in the core of OpenERP

This way a lot of people (including Ferdinand, Me and Nhomar) will already be happy to have the possibility to improve that in modules.

My opinion (as well as Nhomar and Ferdinand) will be that we really need to include that in the core. If I had the choice, I would vote for that... This, in my own opinion, is a must have in stock accounting.

Thanks to take our remarks in your future though,

Best regards,

Joël

Revision history for this message
Bruno JOLIVEAU - www.savoirfairelinux.com (zeekom) wrote :

@Nhomar @Joel @Invitu
+1 for us (we can write +9 because of our 9 consultants worried about this)

We expect exactly the same process and we had already talk about this point at Grand Rosière in 01/2009 during a french localization workshop with other integrators !!!

During i was reading this bug request, i thought several countries are expecting the same process.
I agree the work to modify this process is important, no matter for me the LP level, but VERY important for opinions of account-expert in state and administrative companies (i wrote about our position in France).

Thanks

Bruno

Revision history for this message
Ferdinand (office-chricar) wrote :

Just to clarify
I added "move_value_cost" and "move_value_sale" to stock_move
avg_price is always sum(move_value_clost)/sum(product_qty)
* for product
* for product + stock_location
* for product + stock_location + production_lot
* for product + production_lot
obviously the avg_price might be different for these aggregates
the avg_price must be used for moves where the source location is internal.
only this guarantees that sum(move_value_clost) is 0 when product_qty is 0

move value_cost must also be used to create accounting moves
* either for each move
* at month end
this guarantees that accounting values = stock_accouting values (basic assumption for any audit)

further more this allows to calculate the correct and matching value of inventoy for historical dates (fiscal years/month end) - a must have for accountants

return moves must use the price based on the "move_value_cost" of the corresponding "out" move
= move_value_cost/product_qty * return_qty

saving move_value_cost will also avoid rounding differences
sum(move_value_cost) is likely not sum(product_qty*price_unit) or product.avg_price * sum(product_qty)
this could be avoided by sum(round(product_qty*price_unit, precision)) whereas precision is now a variable in V6 and hence make the script unnecessary complex

the usage of move_value_cost would also allow more easily to integrate the definition of unit_prices like
price / Unit
price / 100 units
price / 1000 units
price in ¢ / Unit
which is a common issue in technical production and even day to day products like gasoline price is priced in ¢/Liter here
see https://code.launchpad.net/~openerp-commiter/openobject-addons/chricar_price_unit
the often discussed method to increase the number of decimals is not helpful, because this is done for all products.

Revision history for this message
Manu (manu-tiedra) wrote :

+1

please, at least save the cost in the core so other modules can fix this.

Thank you!

Revision history for this message
hbto [Vauxoo] http://www.vauxoo.com (humbertoarocha) wrote : Re: [Bug 610738] Re: [5.0] return products to supplier does not update price average

This is just one side of the coin, we have been focused on Rhine accounting
way of dealing with the costs, but there's another problem
when dealing with the way OERP treats the cost using Anglo-Saxon Accounting.

We, at Venezuela, have been trying to tackle the anglo-saxon way of dealing
with the cost, and it have been working hard to get a reliable way to get a
stock valuation taking into account perpetual inventory system, as should be
used by OERP as today.

When we get to something that can be reproducible, we will let you know, so
we can tackle together both Rhine & Anglo-Saxon stock valuation

regards.

Hbto.

2010/10/26 Manu <email address hidden>

> +1
>
> please, at least save the cost in the core so other modules can fix
> this.
>
> Thank you!
>
> --
> [5.0] return products to supplier does not update price average
> https://bugs.launchpad.net/bugs/610738
> You received this bug notification because you are subscribed to
> OpenObject Addons.
>

Revision history for this message
hbto [Vauxoo] http://www.vauxoo.com (humbertoarocha) wrote :

Hello Fabien,
Returning products back to supplier _does_ change average cost.

I Disagree with you in this point.

> The following operations should not change the average cost:
> - delivering a customer (with products that come from a particular
> supplier or not)
> - scrapping defects products (that come from a particular supplier or not)
> - returning the products back to a supplier (fortunately, otherwise you
> have to reconcile each product you send to a
> supplier with the right reception order.) (*)
>
> (*) Returning products back to supplier _does_ change average cost.

Let's say you have:
1) 10 Products A @ AVG €100,00 (Initial Inventory) that yields €1.000,00
2) Buy 5 Products A @ €150,00 yielding €750,00 which causes AVG to change to
AVG = (1.000,00+750,00)/(10+5)
AVG = 116,67 yields a total inventory of €1.750,00 = 15*116,67
3) Sell 3 Products A @ AVG €116.67, causing a reduction in the inventory
1750-3*116,67 = 1.400,00 keeping 12 Products A @ AVG= 116,67
4) Return to supplier 2 Products A @ same purchase price €150, which causes
inventory to reduce to 1.400,00 - 2*150,00 = 1.100,00.
That let us with an Inventory Valued in 1.100,00 for 10 Products A.
And the only way that we can "guarantee that accounting values =
stock_accouting values (basic assumption for any audit)", as pointed by
Chricar, is that those 10 remaining Products A be recomputed theirs average
cost to 1.100,00/10 = 110,00 = AVG.

When returning products to suppliers we _must_ do them at same purchase
price, otherwise, if returning at average price, we could be harnessing a
cost that is not ours, whenever AVG > purchase price, or earning a profit
that either is not ours, whenever AVG < purchase price.

Sorry for posting this too late, but better late than never.

Regards.

Hbto

Revision history for this message
hbto [Vauxoo] http://www.vauxoo.com (humbertoarocha) wrote :

@Fabien
 Hello Again,

I checked my accounting book, they only talks about the
> reception of products.

I have checked mine and all of them talk about the same.

> I think it's important to find references that
> explicitly explains the rules before doing any development on this;

But this issue can be resolved just doing a little math, as pointed out
above, in previous comment.
I know that weighted average cost method, considers all products at same
price, but just a little review
of an in_invoice and an in_refund (not an purchase allowance) will enlighten
all of us,

when we return products to suppliers, we "don't know" which physical
product we are returning, but in our
records we _do_ know which product we intend to return.

And if you bought the product you are intending to return to a more
expensive price, you will not allow your
supplier send you a refund for less money than that you paid for.

> i
> heard so much different things about average price that I prefer being
> sure before integrating something.
>
> --
> [5.0] return products to supplier does not update price average
> https://bugs.launchpad.net/bugs/610738
> You received this bug notification because you are subscribed to
> OpenObject Addons.
>

Amit Parik (amit-parik)
Changed in openobject-addons:
assignee: Sbh (Open ERP) (sbh-openerp) → nobody
importance: High → Undecided
status: Confirmed → Won't Fix
Revision history for this message
Nhomar - Vauxoo (nhomar) wrote : Re: [5.0] return products to supplier does not update price average

We will develop an extra addon to solve this, but, We believe it is an important bug yet, i changed the status because is a really important bug and we can't mark freely and won't fix.

Regards.

Changed in openobject-addons:
status: Won't Fix → Confirmed
importance: Undecided → High
assignee: nobody → Javier Duran - http://openerp.netquatro.com (javieredm)
Revision history for this message
invitu (invitu) wrote :

Nhomar, our customer who is strongly impacted by this bug recently subscribed a warranty contract with OpenERP SA.
It will soon be registered as a bug included in the warranty (we both agree that the actual behaviour of product returns to suppliers are incorrect)

Revision history for this message
Nicolas Bustillos (Poiesis) (nicolas-bustillos) wrote :

I'm wondering, did you guys come up with a solution for this bug? It has been over a year since you reported this, and just recently I came up against it in version 6.0.3. Is there a patch or branch or module to fix this? We are also importantly impacted by this issue and hope there is some groundwork to work on top of.

We'll be standing by to find a solution.

Regards,

Revision history for this message
Peter Langenberg (peter-langenberg) wrote :

How is the status of this bug ?

Amit Parik (amit-parik)
summary: - [5.0] return products to supplier does not update price average
+ [Trunk/7.0/6.0/5.0] return products to supplier does not update price
+ average
Revision history for this message
Kevin McMenamin (kevin-mcmenamin) wrote :

just a note on this - the current logic creates a difference between the GL value of inventory and the calculated value of inventory (I am using Anglo-Saxon accounting).

The stock journal created by the purchase return is qty * original cost. However, since the average cost is not being updated the effect on calcuated inventory value is qty * average cost.

This is totally unacceptable from an acocunting integrity perspective.

Revision history for this message
Kevin McMenamin (kevin-mcmenamin) wrote :
Download full text (6.3 KiB)

code below is for V6 and updates both stock valuation and GL correctly to keep aligned.

# Average price computation
                #2/4/2013 - have raised bug report 1163060 (see also 610738) to get the change made below
                #included in the core product. The problem is that purchase returns were
                #not being catered for - code below does.
                #the bug exists in V7 so can be fixed there.

                if pick.purchase_id and move.product_id.cost_method == 'average':
                    product = product_obj.browse(cr, uid, move.product_id.id)
                    move_currency_id = move.company_id.currency_id.id
                    context['currency_id'] = move_currency_id
                    qty = uom_obj._compute_qty(cr, uid, product_uom, product_qty, product.uom_id.id)

                    if pick.type == 'in':
                        if product.id in product_avail:
                            product_avail[product.id] += qty

                    #for a return, get the price from the move. if none, use average cost
                    if pick.type == 'out':
                        if move.price_unit:
                            product_price = move.price_unit
                        else:
                            product_price = product.price_get('standard_price', context)[product.id]

                    new_price = currency_obj.compute(cr, uid, product_currency,
                                move_currency_id, product_price)
                    new_price = uom_obj._compute_price(cr, uid, product_uom, new_price,
                                product.uom_id.id)

# if qty > 0:
                    if pick.type == 'in':
                        if product.qty_available == 0 or (product.qty_available + qty) == 0 :
                            new_std_price = new_price
                        else:
                            # Get the standard price
                            amount_unit = product.price_get('standard_price', context)[product.id]
                            new_std_price = ((amount_unit * product.qty_available)\
                                + (new_price * qty))/(product.qty_available + qty)

                    else:
                        if product.qty_available == 0 or (product.qty_available - qty) == 0:
                            new_std_price = new_price
                        else:
                            # Get the standard price
                            amount_unit = product.price_get('standard_price', context)[product.id]
                            new_std_price = ((amount_unit * product.qty_available)\
                                + (new_price * (0-qty)))/(product.qty_available -qty)

                        # Write the field according to price type field
                    product_obj.write(cr, uid, [product.id], {'standard_price': new_std_price})

                        # Record the values that were chosen in the wizard, so they can be
                        # used for...

Read more...

Revision history for this message
Josse Colpaert (OpenERP) (jco-openerp) wrote :

I will put this bug to invalid. Weighted average cost does not change when returning products in the normal case. Weighted average cost is based on purchases and starting inventory and not on returns. (and I do not say there can not be any exceptions or that it won't be handled when developing FIFO modules, ...)

Just for reference:

http://www.accounting-basics-for-students.com/fifo-method.html
http://en.wikipedia.org/wiki/Average_cost_method#Weighted_Average_Cost

Changed in openobject-addons:
status: Confirmed → Invalid
Revision history for this message
Kevin McMenamin (kevin-mcmenamin) wrote :

Excuse the language but this is bullshit!

I am a qualified accountant with over 30 years of expereince, so please excuse me if I don't take a 5 line wikipedia entry as the basis for making accounting decisions.

What should happen when products are returned to a suppler (when generated from the original picking):

1. The credit should be at the original purchase price and generate accounting entries accordingly
2. The average cost should be updated using the amount of the credit (qty/cost)

I have now rewritten both anglo-saxon and parts of stock to fix this and other accounting issues for v6.0 but OpenERP should make this right in V7 - correct accounting goes to the heart of an ERP system.

Please set this back to confirmed and will soemone at OpenERP get it fixed once & for all.

Revision history for this message
Graeme Gellatly (gdgellatly) wrote :

I have 10 products at average cost of $10.
I buy 10 more products with average cost of $1.
I return the products at average cost of $5.50 but actual cost of $1 (the credit I receive). Where has the $4.50 gone?

I made profit without doing anything but fiddle some papers - clearly wrong (I may have got example back to front but principle holds).

Although reverse case is possibly a very good way to beat your tax bill and increase assets at same time it will sadly will put me in prison. Prison for following OpenERP's version of average costing seems harsh.

It seems every bug about average costing (and there are more than this one) is met with a won't fix, wait till next version and a wikipedia reference. Like Kevin, total rewrite of anglosaxon, plus stock just to make work - plus needing to use c2c picking_invoice relation addon to keep it all straight.

OpenERP I hope you can understand why this is a very real bug.

When you return to supplier, you are sending the COST. When you sell to customer you are sending the PRICE. Therefore you are not returning AVERAGE cost - you are returning ACTUAL cost and must adjust average cost accordingly to reflect this. Putting up Wiki entries that deal with average cost sales is not helpful in a discussion on returns.

However and importantly - when you take a return from a customer you are receiving products not at AVERAGE cost but at the original ACTUAL sale cost. Therefore average cost needs adjusting. If this is not the case - you have another bug. If this is the case then you have proven yourself that the current supplier return behaviour is wrong. Whenever you increase or decrease inventory by a KNOWN cost you must adjust average cost.

Revision history for this message
Stefan Rijnhart (Opener) (stefan-opener) wrote :

I am misusing my membership of OpenERP Drivers to set this bug to 'confirmed' because of the wide range of support for this bug.

Changed in openobject-addons:
status: Invalid → Confirmed
Revision history for this message
Josse Colpaert (OpenERP) (jco-openerp) wrote :

I wanted to tell you that we are taking this seriously and are about to clean this up. Kevin, maybe I overlooked, but if you have a branch it would be interesting to have a look at least.

I see the average price is now updated like this (only for picking in): new_std_price = ((amount_unit * product_avail[product.id]) + (new_price * qty))/(product_avail[product.id] + qty)

So when returning products, you use a negative quantity (qty) and a new_price = price of original in move (of purchase order/supplier invoice)?

Revision history for this message
Graeme Gellatly (gdgellatly) wrote : Re: [Bug 610738] Re: [Trunk/7.0/6.0/5.0] return products to supplier does not update price average

Josse

There are lots of branches but they all focus on correcting the sympton not
fixing the underlying cause. If OpenERP is serious about supporting
average cost and fifo then the critical question is this- Do you need to
calculate costs at time of stock move - in that is? Or put slightly
differently - what's the difference between an account move used to
calculate a balance at a point in time and a stock or account move used to
determine an average or fifo cost for that matter.
On 16/04/2013 10:02 PM, "Josse Colpaert (OpenERP)" <email address hidden> wrote:

> I wanted to tell you that we are taking this seriously and are about to
> clean this up. Kevin, maybe I overlooked, but if you have a branch it
> would be interesting to have a look at least.
>
> I see the average price is now updated like this (only for picking in):
> new_std_price = ((amount_unit * product_avail[product.id]) + (new_price
> * qty))/(product_avail[product.id] + qty)
>
> So when returning products, you use a negative quantity (qty) and a
> new_price = price of original in move (of purchase order/supplier
> invoice)?
>
> --
> You received this bug notification because you are subscribed to OpenERP
> Addons.
> https://bugs.launchpad.net/bugs/610738
>
> Title:
> [Trunk/7.0/6.0/5.0] return products to supplier does not update price
> average
>
> Status in OpenERP Addons (modules):
> Confirmed
>
> Bug description:
>
> When returning products to a supplier, the average standard price should
> be updated using the price of the packing list which is returned.
>
> For example :
> 1- We have 10 products with average price = 100
> 2- We receive 5 products with price = 80 --> average price is calculated
> as (10*100+5*80)/(10+5) = 93,33
> 3- We return 3 products from the last packing list (the one where
> products cost 80)
> The new average price should be
> (93,33*15 - 3*80)/(15-3)=96,67
>
> If the return is made directly from the stock and does not concern any
> incoming packing list, we do not update the average price.
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/openobject-addons/+bug/610738/+subscriptions
>

Revision history for this message
Mario Arias (the-clone-master) wrote :

Josse

Please help get this fixed !

It has been around since v5.0 !!!!

tags: added: ocb-stock-v1
Revision history for this message
Hugh Turner (hugh-turner) wrote :

Does it really take 3 years to fix a bug like this?

If you link the Return to the data from the Goods Receipt, then you will have all of the specific data to return the correct batch from the correct location at the correct costs.

This would be the usual requirement. There may be requirements for returning at average cost, but I cannot think of one.

Revision history for this message
Normunds (Alistek) (3pm) wrote :

Hello all!

I just made full research of issue on OCB 6.1 branch only.

My conclusions:
current 6.1 OCB branch does compute accounting entries regarding average price and product return almost :) correctly.
In this I completely agree with Fabien.
Note that I do not intentionally including landed costs in computation what can change results.
What is happening here in this launchpad thread- is mixing apples with pears.

In general you cannot mix AVG price computation with FIFO. What people here generally do, thus receiving incorrect results and mess in accounting.

According to my research:
- Receiving goods from supplier -> you DO update AVG cost price

- Sending goods to customers -> you USE actual AVG cost price (reducing your stock by actual AVG price)

- Returning back to supplier -> you DO NOT update AVG price (reducing your stock by PURCHASE price, then DIFFERENCE goes to "Goods Purchase Expenses" account: debit if Purchase price is < actual AVG price, credit if Purchase price is > than actual AVG price)* + you should return any quantity from exact “done” Incoming picking

- Returning back from Customer -> you DO NOT update AVG price (increasing stock by actual AVG price, then DIFFERENCE goes to "Goods Purchase Expenses" account: debit if SALE/cost price is < actual AVG price, credit if SALE/cost price is > than actual AVG price)* + you should return any quantity from exact done Outgoing picking

* in both mentioned cases you increase or decrease your profit (what is interesting for tax authorities), but you have legal reason.

So what is missing here, at least for OCB branch I'm using ?

1. Suppliers returns: everything is correct now, except we need additonal account entries in "Goods Purchase Expenses" for difference's debit or credit if actual AVG price != Purchase price of picking we want to return.
Now we should add it manualy to adjust.

2. Customer returns: everything is correct now, except we need additonal account entries in "Goods Purchase Expenses" for difference's debit or credit if actual AVG price != Sale/cost price of picking we want to receive back

Conclusion: AVG price calculation rules are correct. There are some missing automatic accounting entries welcome, to keep trial profit reports up to date.

Those missing automatic adjustment accounting entries are very necessary for companies with large amount of transactions.

Normunds
Vilcans
Alistek , Ltd

Revision history for this message
Ferdinand (office-chricar) wrote :

please keep in mind
* avg price is not multi-company ready
* current avg price can not be used to compute historical stock values (but is currently done in OpenERP)

IMO we have to distinguish
* sales-price and refund price - these prices * quantity should go on different accounts each not the difference
* avg price at time of sales and avg price at time of refund must be used for stock evaluation
it is most likely that many companies will take the avg-price at sales for stock evaluation of refund - especially if the product can be resold. I even would assume it's against the law in Austria to use an higher current avg price for stock evaluation at time of refund.

Revision history for this message
Stefan Rijnhart (Opener) (stefan-opener) wrote :

@normunds for the record, I don't think there is anything patched in ocb 6.1 that is not in upstream OpenERP branches with regards to this topic.

Revision history for this message
GUENARD (michel-guenard) wrote :

Hi all
I just discover this issue.

Comment https://bugs.launchpad.net/openobject-addons/+bug/610738/comments/35 is the only clear message I understand, as a professional.

SAP' technical answers and workflows - which OpenErp R&D's people have certainly studied in depth - could be a safe source to implement whithin the OpenErp structure!

Revision history for this message
Normunds (Alistek) (3pm) wrote :

" avg price is not multi-company ready "

Is OpenERP accounting itself is?
This is another related bug, but not in this bugs's scope.

"current avg price can not be used to compute historical stock values (but is currently done in OpenERP)"
Agree it can not be used. Therefore you added nice fields in stock.move object "move_value_cost" and "move_value_sale"
For those purposes.

"I even would assume it's against the law in Austria to use an higher current avg price for stock evaluation at time of refund"

Maybe. I do not insist that my proposal is right. What I wanted to say that AVG price itself should not be recalculated on any refunds.

Revision history for this message
Normunds (Alistek) (3pm) wrote :

This is what I am trying to show in numbers. See attached xls file.

File is downloaded from:
http://toolboxes.flexiblelearning.net.au/demosites/series11/11_06/toolbox11_06/resources/html/ffact_weightavinvent.htm

So, first definition: we use here Weighted Average Cost Valuation- NOT FIFO OR LIFO!!!!

According this table we recalculate Average price in INPUTS only.

We USE our current Average Cost Price for customer refunds - therefore there is no need to adjust Average Cost Price on customer refund, because we DO NOT use original Cost price at time of Sale on Customer Refund, because of two reasons:
1. We do not use FIFO
2. We may not use incoming/outgoing lots tracking what is not required with Weighted Average Cost Valuation but required with FIFO. When we do not use lots we even do not know cost price at time of Sale of particular item.

It looks like Fabien is right. He is right sometimes :)!

I am also not accounting guru, therefore should rely on external sources, but we need this made crystal clear in OpenERP.

I tested Camptocamp c2c_stock_accounting module also - I got messy results with it. Is this latest version available on launchpad?

Please, make your comments.

Normunds
Alistek Ltd

Revision history for this message
Stephan Keller (r-skeller) wrote :

Done with v7.0.

I am sorry if this was already submitted somewhere else, we are new to OpenERP.

Here is a way I think to see that the calculation of the average cost/way to handle the supplier return has a problem:

Start with a fresh database. Set it up such that the accounting consequences for the delivery are calculated. Setup one item with income account, expense account, stock input account, stock output account = COGS, and stock valuation account.

Purchase 10 items at $10, validate all (purchase order, incoming shipment, invoice)

Purchase 10 items at $20, validate al.

Average price at this point: $15

Stock valuation: $300 ($100+$200)

So far perfect.

Go back to the first incoming shipment and use “Return Product”, validate the return. (note that the return does not use the average cost to calculate the accounting consequence, but the cost in the original purchase = $100).

Stock Valuation: $200 ($300-$100)
Average cost: still $15.

Enter a sale for the product: 10 items, validate sales order and delivery. Accounting consequences are calculated with the average cost of $15.

Stock Valuation: $50. ($200-$150)
Average cost: $15.

But there is no stock left, everything was either sold or returned to supplier. Yet the stock valuation account shows $50. I don’t think anybody can argue that it is ok to have no stock and stock valuation non zero.

To me there are two choices:

1. Recalculate the average cost on return to supplier(as many are advocating here)
Then after the return: stock valuation: $200, average cost = $20, and the sale would move $200 out of stock valuation -> 0.

2. When calculating the accounting consequences of the return, use the average cost = $150.
Then after the return: stock valuation: $150., average cost = $15, and the sale would move $150 out of stock valuation ->0.

Most will want 1 but 2 would solve the problem of following the rules of average cost.

Stephan
Sodexis.

Changed in openobject-addons:
assignee: Javier Duran (javieredm) → OpenERP Publisher's Warranty Team (openerp-opw)
tags: added: maintenance
Revision history for this message
Michael Karrer (michaelkarrer81) wrote :

Any news on this one?

Feels that the discussion just stopped - I Know there is a new WMS / WCS underway for 8.0 but still 7.0 should work!

Revision history for this message
Stephan Keller (r-skeller) wrote :

Michael,

Check this out: https://code.launchpad.net/~openerp-dev/openobject-addons/7.0-opw-600550_601711-ado/+merge/197818
it was done for one of our customer, but it was not merged.

Revision history for this message
Ashley Marc (ashleymarc-e) wrote :

Any idea if this has been fixed in 7?

Revision history for this message
Andres Calle (TRESCLOUD) (andres-calle) wrote :

It is not yet fixed, but a working patch is provided (hope it works in all possible escenarios).

I've tested the patch (as listed by Stephan) in https://code.launchpad.net/~openerp-dev/openobject-addons/7.0-opw-600550_601711-ado/+merge/197818 and looks like working. What is need to have it merged into 7.0?

BR

Andrés Calle
TRESCLOUD

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Duplicates of this bug

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.