Dec 08

Recalculating average costing backwardly

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.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
    select trans.n+1,,trans.amt,trans.qty, trans.oqty, trans.sqty,
    lag(trans.avg) over (order by 1),
    case when runsum.sqty=0 then runsum.amt else
    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

Oct 24

Penerapan Cost Center atau Profit Center di Adempiere

Dalam implementasi Adempiere, sering kita dihadapkan pada pertanyaan bagaimana menerapkan Cost Center atau Profit Center dengan tepat. Adempiere menyediakan berbagai pilihan untuk model konsep ini.

Pertama, dan yang terpenting adalah pengaturan Organisasi. Di Adempiere, Anda dapat membuat entitas organisasi tanpa batas . Anda dapat menentukan dan membedakan berbagai jenis organisasi ( Divisi , Cabang, Anak Perusahaan, Sister Company, dll). Anda juga bisa menciptakan beberapa hirarki organisasi yang berbeda ( misalnya dari sudut pandang kepemilikian, geografis, pelaporan , dsb ) . Sebuah Organisasi setidaknya mempunyai Asset, entah itu berupa kas atau persediaan barang. Jadi ketika Anda membuat sebuah Organisasi, biasanya akan diikuti pula dengan pembuatan buku kas dan gudang.

Setiap transaksi dalam Adempiere (seperti Invoice dan Payment) harus diasosiasikan dengan suatu Organisasi, karena nantinya Organisasi tersebut juga dipakai dalam laporan pembukuan.

Organisasi dapat dihubungkan dengan Business Partner (Mitra Usaha) sehingga Anda bisa menciptakan transaksi antar Organisasi dengan mudah. Dari sudut pandang akuntansi, entitas Organisasi ini merupakan segment yang harus balance. Oleh karena itu, secara default, Adempiere akan menciptakan jurnal hutang & piutang inter-company secara otomatis sehingga Anda bisa mendapatkan laporan yang balance dari sudut mana pun. Adempiere bahkan juga mampu menciptakan dokumen lawan transaksi secara otomatis, jika fitur Counter Document diaktifkan.

Tetapi mungkin pula Anda tidak membutuhkan fitur ini. Mungkin Anda mempunyai bisnis proses yang melibatkan banyak transaksi antar unit, dan Anda sama sekali tidak membutuhkan tambahan jurnal inter-company. Dengan sedikit pengaturan, Anda bisa membuat Adempiere bekerja seperti yang Anda inginkan.

Dalam Adempiere, ada dua segment Organisasi yakni Organization (AD_Org_ID) dan Transaction Organization (AD_OrgTrx_ID). Anda bisa menempatkan badan usaha (legal entity) sebagai Organization, sementara unit-unit usaha yang lebih kecil sebagai Transaction Organization. Adempiere tidak akan menciptakan tambahan jurnal inter-company pada  segment Transaction Organization.

Contoh implementasi seperti ini adalah pada perusahaan dealer mobil yang memiliki 10 cabang yang masing-masing memiliki 1 sampai 3 unit bisnis yakni Sales, Spareparts dan Services. Karena cabang adalah legal entity (NPWP sendiri, laporan neraca dan rugi laba yang terpisah), maka Anda menempatkan cabang sebagai Organization. Sedangkan 3 unit bisnis tadi sebagai Transaction Organization. Dengan demikian, Anda dapat membagi satu Invoice ke dalam beberapa unit bisnis ( misalnya baris transaksi jasa masuk ke unit Services dan baris transaksi barang masuk ke unit Spareparts ).

Dengan sedikit customization, Anda bisa melakukan pengaturan misalnya cabang X mempunyai ketiga unit tersebut sementara cabang Y hanya memiliki unit Sales saja. Tekniknya adalah cukup dengan menggunakan Dynamic Validation. Untuk kasus yang lebih kompleks, Anda bisa juga menerapkan Model Validation.

Apabila kondisi Cost Center mempunyai struktur yang lebih lepas (tidak terkait dengan struktur Organisasi, tidak mempunyai Asset), Anda cukup menggunakan segment Activity. Semoga tulisan ini bermanfaat dan memberikan pencerahan.

Goodwill Consulting adalah bagian dari komunitas pengembang dan pendukung Adempiere. Jika Anda membutuhkan bantuan profesional untuk implementasi Adempiere, silahkan menghubungi kami.