Dealing with average costing could be a nightmare for most of us. Chuck Boecking, a fellow Adempiere consultant based in US explained it very well in his blog why we (not to) choose average costing in a ERP.

ERP is a perpetual inventory accounting system. Therefore, the system  will post every inventory transaction as they happen. When you use average invoicing, your inventory valuation will almost always be inaccurate. For example, Let’s assume you buy a bunch of products over time over different costs. Then, over time, you sell all product. The resulting inventory GL balance will almost always be non-zero. Therefore, you will have a phantom inventory balance. If your operations are even a little complex, the variances that result from average invoice costing can be difficult to track and explain.

Most of the time we heard about recommendation on using Standard Costing as a way to escape from this nightmare. In earlier release of Compiere (v. 2.5), even there are only Standard Costing and Last PO price method. However in Indonesia, the requirement for standard accounting practice is to use either Average Costing or FIFO/LIFO. I still can recall back in 2005 how our team had to develop our own average costing functionality to satisfy the clients’ need.

Eventually Compiere overhauled its costing engine and introduced average costing and FIFO/LIFO. But it’s so poor and immature we could hardly use it in real world. Our team once again drilled down the costing engine and did some major hacks. We contributed it back to community in 2006 although we know it’s not crystal clear perfect yet. At least now we had a working average costing in Adempiere (Hengsin Low later added more workarounds in 2010).

One culprit we still found anyway is the fact that the ERP is allowing a back-dated transaction. Since we are using a moving (weighted) average costing, we could have trouble if user is entering transactions in a not so chronological manner.

I am thinking to have a process where we could tell the ERP to recalculate the costing for a given period. Our team had made a good effort so far. However I’m still not satisfied and then I stumbled on this link referenced by one of my client, whom now became a good friend of mine.

The idea is to calculate the average costing backwardly, instead of from the beginning which is very time and resource consuming. I copy the content of the link here, in case it’s lost.

So let’s begin.

There are two tables:
-the one that holds inventory transactions, and
-the one that holds the latest inventory valuation

I am trying to make an inventory valuation report using average costing method based on a certain date. Doing it the normal way, calculating from the beginning until that specific date, will yield variable response time. Imagine calculating on five years worth of data ( and thousands different inventory items ). It will take considerable amount of time ( and my company is not silicon-valley grade. meaning, 2 core cpu and 8 GB of RAM only) so I am calculating it backwardly: from the latest (current) backtrack to that specific date.

(Every month the accounting dept will check on data, so the calculation will only deal with 1 month’s worth of data, forever. equal to consistent unchanging performance)

I have merged the table into one on the script below

create table test3 ( rn integer, amt numeric, qty integer, oqty integer);
insert into test3 (rn,amt,qty,oqty) values (0,2260038.16765793,8,0);
insert into test3 (rn,amt,qty,oqty) values (1,1647727.2727,3,0);
insert into test3 (rn,amt,qty,oqty) values (2,2489654.75326715,0,1);
insert into test3 (rn,amt,qty,oqty) values (3,2489654.75326715,0,1);
insert into test3 (rn,amt,qty,oqty) values (4,1875443.6364,1,0);
insert into test3 (rn,amt,qty,oqty) values (5,1647727.2727,3,0);
insert into test3 (rn,amt,qty,oqty) values (6,3012987.01302857,0,1);
insert into test3 (rn,amt,qty,oqty) values (7,3012987.01302857,0,1);

select * from test3; (already sorted desc so rn=1 is the newest transaction)

rn  amt        qty  oqty
0   2260038.168 8   0    --> this is the current average
1   1647727.273 3   0
2   2489654.753 0   1
3   2489654.753 0   1
4   1875443.636 1   0
5   1647727.273 3   0
6   3012987.013 0   1
7   3012987.013 0   1

Average Costing Method backtracking ( given current avg calculate last transaction avg, and so on until nth transactions )

Avg (n) = ((Avg(n-1) * (Cum Qty(n)+In Qty(n))) – (In Amount(n) * In Qty (n)) + (Avg(n-1) * Out Qty(n))/(Cum Qty(n)+Out Amount(n))

Cumulative qty for backtracking transactions would be minus for in, plus for out. So if current qty is 8, transaction in qty before is 3, then cumulative qty for that transaction is 5.

To calculate the average for one transaction before last, then we use current average to use in that transaction calculation.

with recursive
runsum (id,amt,qty,oqty,sqty,avg) as
    (select data.id, data.amt, data.qty, data.oqty, data.sqty, data.avg
     from (
        select rn as id,amt,qty, oqty,
        sum(case when rn=0 then qty else
             case when oqty=0 then qty*-1
                else oqty end end) over (order by rn) as sqty, lag(amt) over (order by rn) as avg
          from test3 ) data
         ),
counter (maximum) as
         (select count(rn)
          from test3
         ),
trans (n, id,amt,qty,oqty,sqty,prevavg,avg) as
    (select 0 n, id,amt,qty,oqty, sqty,avg,avg
      from runsum
     union
    select trans.n+1, runsum.id,trans.amt,trans.qty, trans.oqty, trans.sqty,
    lag(trans.avg) over (order by 1),
    case when runsum.sqty=0 then runsum.amt else
    ((trans.prevavg*(runsum.sqty+trans.qty))-(runsum.amt*trans.qty)+(trans.prevavg*trans.oqty))/(runsum.sqty+trans.oqty)
    end
    from runsum join trans using (id)
    where trans.n<(select maximum*2 from counter))
select *
from trans
where prevavg is null and avg is not null
order by id;

The result is supposed to be like this

rn  amt        qty oqty sqty sum avg
1   1647727.273 3   0   5   2627424.705
2   2489654.753 0   1   6   2627424.705
3   2489654.753 0   1   7   2627424.705
4   1875443.636 1   0   6   2752754.883
5   1647727.273 3   0   3   3857782.493
6   3012987.013 0   1   4   3857782.493
7   3012987.013 0   1   5   3857782.493

I hope we could have something working in near future. So stay tuned.

—

Goodwill Consulting is a long time Adempiere / Idempiere supporter since their inception. We are offering software-as-a-service solution on the cloud based on Adempiere / Idempiere. For more information, you can drop us a visit at www.goodwillerp.com