Sep 24

Cara Adjustment Nilai Persediaan sesuai hasil Audit

PT. ABC mendapatkan hasil Audit nilai persediaan dan Qty per 31 Des 2017, hasil tersebut di terima pada bulan Maret 2018. Dan hasil Audit yang diberikan terdapat perbedaan nilai Persediaan dan Qty per 31 Des 2017. Untuk itu PT. ABC harus melakukan Adjustment untuk menyamakan nilai persediaan dan Stock yang ada di sistem ERP dengan hasil audit yang di terima. Dikarenakan hasil diterima di bulan Maret 2018, maka PT. ABC setelah melakukan Adjustment untuk nilai persediaan dan Stock produk yang berbeda, PT ABC juga harus melakukan proses Recalculate Costing. Proses ini fungsinya untuk menghitung ulang cost atas transaksi yang sudah terjadi selama Jan 2018 s/d Maret 2018.

Berikut Langkan yang harus dilakukannya PT. ABC :

1. Open Period

Buka window Calender Year and Period, kemudian Open period Des 2017 s/d periode berjalan.

2. Cek Window Unposted Documents

Pengguna harus memastikan tidak ada transaksi yang belum terposted sampai tgl 31 Des 2017

3. Adjustment nilai Stock

3a.Untuk Adjsutment nilai Stock, pengguna bisa menggunakan transaksi Internal Use,lalu lakukan adjustment untuk item-item yang qty nya berbeda dengan hasil Audit.

4. Adjustmenmt Pesediaan sesuai dengan hasil Audit

Untuk langkah ini bisa dilakukan dengan 2 cara : Manual (4a) dan Import (4b)

4a.Untuk Adjustment nilai persediaan, pengguna bisa menggunakan window Inventory Cost Adjustment. Di window ini kolom Rebuild Cost From Asset dipilih Y ( Ini fungsinya agar pengguna mendapatkan nilai Current Cost Price untuk item yang akan di Adjustment sesuai hasil persediaan/Qty yang saat ini di akui sistem.JANGAN COMPLETE DOKUMEN INI

4b.Jika data produk yang akan di Adjustment nilai persediaannya banyak, maka pengguna bisa memanfaatkan fitur Import Inventory Cost Adjustment. Import Cost Adjustment per 31 des 17 (Hanya Element :Material PO saja yg diimport). Sebelumnya pengguna sudah menyiapkan data CSV untuk item-item yang akan di Adjustment nilai persediaannya sesuai format yang sudah di sediakan di sistem ERP

Note : Untuk proses Inventory Cost Adjustment jika di lakukan di tanggal 31 des 2017, artinya sistem melakukan Adjustment untuk stock per 30 des 2017.Untuk itu disarankan di tanggal Cost Adjustment sudah tidak ada transaksi lagi.

5.Jalankan proses Update product Cost per 31 des 17.

Ketika menjalankan proses ini, list box Zero Other Cost Element harus dicentang. Karena pengguna akan memperbaiki nilai saldo awal per 31 des 17.

6. Completed Inventory Cost Adjustment yang sudah di buat atau di Import sebelumnya .

Jika data inventory cost adjustment-nya sudah di buat atau diimport maka di window ini kolom Rebuild Cost From Asset dipilih Y ( Ini fungsinya agar pengguna mendapatkan nilai Current Cost Price untuk item yang akan di Adjustment sesuai hasil persediaan/Qty yang saat ini diakui sistem. Lalu COMPELETE dokumen ini.

7. Jalankan proses Update product Cost lagi per 31 des 17 untuk memastikan.

Ketika menjalankan proses ini,list box Zero Other Cost Element harus dicentang. karena pengguna akan memperbaiki nilai saldo awal per 31 des 17.

8. Buka Window Calender Year and Periode kemudian list box Hold Material Transaction dicentang kemudian klik Save.

9. Buka Sysconfig Average Negative Qty dan Audit Log Average negative Qty set menjadi=Y.

10. Buka report Average Costing Check (As Of Date ). Harusnya tidak ada product yang muncul.

1
1. Jalankan proses Recalculate costing

Pilih Organisasi yang akan di recalculate , kemudian Account date diisi dari 1 Jan 2018 s/d saat ini. Centang Delete Cost detail supaya program dapat menghitung kembali. Tapi jangan centang Reset Current Cost Pricekarena kita mau menggunakan angka yang baru saja kita upload via Inventory Cost Adjustment sebagai saldo awal.

12. Periksa Unposted Dokumen ( Patikan tidak ada transaksi yang belum terposted )

1
3. Periksa Average Costing Check. Harusnya tidak ada product yang muncul

1
4. Buka Sysconfig Average Negative Qty dan Audit Log Average negative Qty set menjadi =N

15.Buka Window Calender Year and Periode kemudian list box Hold Material Transaction dibuang centangnya kembali, kemudian klik Save.


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.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