Formula Pendistribusian Persediaan

---

Muatan :

• Kilasan tentang formula distribusi
• Konsep pendistribusian persediaan dengan formula
• Cerita tentang sebuah kasus
• Penyusunan formula

---

Kilasan

Salah satu kasus yang berkaitan dengan permintaan dan persediaan adalah mengkomputasi pendistribusian persediaan sesuai dengan permintaan yang ada. Hasil akhir yang diharapkan adalah diketahuinya dengan jelas item-item permintaan yang masih belum terpenuhi karena keterbatasan persediaan. Jumlah kekurangan persediaan setiap item permintaan yang belum terpenuhi bisa menjadi informasi penting dalam tahap pengadaan persediaan berikutnya. Tentu masih banyak informasi lain yang bisa didapatkan dari terpetakannya permintaan dan persediaan tersebut.



Bahasan kali ini akan difokuskan pada formula untuk mengkomputasi jumlah persediaan yang digunakan untuk memenuhi permintaan.

---

Konsep pendistribusian persediaan dengan formula

Pada dasarnya, konsep pendistribusian persedian adalah sebagai berikut :

1. Mengetahui jumlah permintaan secara progresif
2. Mengetahui sisa persedian yang masih bisa digunakan untuk memenuhi permintaan secara progresif
3. Menentukan jumlah permintaan yang bisa dipenuhi oleh persediaan secara progresif

Maksud dari kata 'progresif' adalah pada keadaan saat itu. Sebuah item persediaan yang telah digunakan untuk memenuhi sebuah permintaan tidak mungkin digunakan untuk memenuhi permintaan yang lain.

Dengan berpegang teguh pada konsep diatas, maka pendistribusian persediaan dengan formula bisa digambarkan sebagai berikut :

1. Penyusunan tabel permintaan vs persediaan
2. Data permintaan akan tertuang secara vertikal, sehingga setiap baris terfokus pada sebuah permintaan
3. Data item-item persediaan akan tertuang secara horisontal dan bersifat unik berdasar kriteria permintaan
4. Dari hal nomor 1 dan 2 diatas, maka terbentuklah ruang untuk nilai-nilai pemenuhan permintaan yang bisa disediakan oleh setiap item persediaan
5. Prioritas akan terbentuk dengan sendirinya berdasarkan urutan item-item permintaan (sisi vertikal) dan berdasarkan urutan item-item persediaan (sisi horisontal)

Untuk memudahkan pembahasan, maka pembahasan kali ini tidak akan mencakup tentang cara penyusunan prioritas dan cara penyusunan item-item persediaan yang harus unik berdasar kriteria permintaan. Salah satu cara penyusunan prioritas bisa memanfaatkan coretan Formula sort data pada data teks. Sedangkan salah satu cara menyusun item-item persediaan yang bersifat unik bisa memanfaatkan coretan Formula penyusun data unique yang terurut (sorted), setelah didapatkan susunan persediaan yang terurut menurut prioritas pemanfaatannya.

---

Cerita tentang sebuah kasus

Sebuah gudang sedang melakukan penataan ulang setiap slot persediaan yang dimilikinya. Setiap slot bisa menyimpan banyak produk. Masa tinggal setiap produk dalam sebuah slot diwakili oleh suatu nilai kualitatif yang disebut level. Level hanya terbagi menjadi 2, yaitu :

• level A
  untuk produk yang masa tinggalnya didalam gudang telah lama dan harus segera terpakai.
• level B
  untuk produk yang masa tinggalnya didalam gudang masih relatif baru menurut proses kalkulasi tertentu dan dapat digunakan jika seluruh level A untuk suatu produk telah habis terpakai.

Pada contoh kasus ini, akan digunakan sebuah produk saja, yaitu produk 901, untuk memudahkan pemahaman terhadap pembahasan. Gambar berikut adalah data :

• permintaan dari slot-slot gudang yang membutuhkan persediaan baru yang dapat diambil dari slot lainnya untuk suatu produk tertentu. [Tabel di A2:D5]
• referensi kriteria pemilihan sumber persediaan berdasar levelnya. [Tabel di G2:H4]
• persediaan yang bisa digunakan oleh slot lain yang lengkap dengan status levelnya. [Tabel di J2:M7]

Dari gambar diatas dapat diketahui bahwa :

• Tabel kebutuhan telah terurut menurut kolom Slot dan Level. Pada kasus ini data kebutuhan bersifat unik menurut kolom Slot dan Level. Meski demikian, penerapan di dunia nyata, tidak diharuskan terbentuknya keunikan data kebutuhan berdasar suatu kondisi tertentu.
• Tabel kelebihan telah terurut menurut kolom Level dan Slot. Data tidak unik.
• Tabel referensi menunjukkan kaidah pemenuhan kebutuhan berdasar Level. Level A bisa dipenuhi oleh persediaan dengan level A maupun B. Tidak tampak keterangan tentang keharusan untuk menggunakan seluruh item level A sebelum menggunakan item level B.

Gambar berikut ini adalah target hasil kalkulasi.

Pada gambar diatas, tampak bahwa :

• item permintaan (dari tabel kebutuhan) tersusun vertikal.
• slot-slot persedian (dari tabel kelebihan) tersusun horisontal yang bersifat unik menurut kriteria pemenuhan kebutuhan, yaitu berdasar level dan slot (area dengan latar hijau).
• keharusan untuk menggunakan item persediaan dengan level A sampai habis lebih dulu sebelum menggunakan item dengan level B (area dengan latar hijau). Baris level pada sisi slot kelebihan terurut mulai dari level A seluruhnya diikuti level B setelahnya tanpa mempedulikan nomor slot.
• pada item permintaan pertama (baris 14) dengan level A dan kebutuhan senilai 40 item dipenuhi oleh :
  
  • Item persediaan level A dari slot 2 sebanyak 20 item yang merupakan total dari cell L3 dan L4.
    -> Dengan terpenuhinya sebanyak 20 item ini, maka sisa kebutuhan adalah 20 item (40 total item kebutuhan - 20 item dipenuhi oleh [level A slot 2])
  • Item persediaan level A dari slot 4 sebanyak 15 item.
    -> Sisa kebutuhan pada keadaan ini adalah 5 item (40 total item kebutuhan - 20 item dipenuhi oleh [level A slot 2] - 15 item dipenuhi oleh [level A slot 4]).
  • Item persediaan level B dari slot 2 sebanyak 5 item.
    -> Meskipun pada tabel kelebihan untuk level B slot 2 tersedia sebanyak 10 item, yang digunakan untuk memenuhi kebutuhan pertama (baris 14 ini) hanyalah 5 item.
    -> Sisa item yang dibutuhkan pun jadi 0 item atau habis, sehingga slot berikutnya (cell i14) bernilai 0.
• pada item permintaan kedua (baris 15) dengan level B dan kebutuhan senilai 50 item dipenuhi oleh :
  
  • Item persediaan level B dari slot 2 sebanyak 5 item.
    -> Hal ini terjadi bukan dikarenakan sudah habisnya persediaan level A akibat dipakai untuk memenuhi permintaan pertama, tetapi disebabkan oleh level permintaan yang berupa level B.
    -> Menurut tabel referensi, permintaan kedua harus dipenuhi hanya oleh item persediaan level B juga.
    -> Jika dilihat dari tabel kelebihan saja, disana ada 10 item level B slot 2.
    -> Berhubung permintaan pertama telah menggunakan 5 item dari level B slot 2, maka yang tersisa untuk memenuhi permintaan berikutnya hanyalah 5 item.
    -> Item tersisa inilah yang bisa digunakan oleh level B slot 2 untuk memenuhi permintaan kedua.
    -> Sisa kebutuhan pada keadaan ini adalah 45 item (50 total item kebutuhan - 5 item dipenuhi oleh [level B slot 2]).
  • Item persediaan level B slot 5 sebanyak 45 item.
    -> Sampai disini, semua permintaan kedua dapat terpenuhi.
• pada item permintaan ketiga (baris 16) dengan level B dan kebutuhan senilai 20 item dipenuhi oleh :
  
  • Item persediaan level B slot 5 sebanyak 15 item.
    -> Jika dilihat dari tabel kelebihan saja, disana ada 60 item level B slot 5.
    -> Berhubung total permintaan pertama dan permintaan kedua telah menggunakan 45 item dari level B slot 5, maka yang tersisa untuk memenuhi permintaan berikutnya hanyalah 15 item.
    -> Item tersisa inilah yang digunakan oleh level B slot 5 untuk memenuhi permintaan ketiga.
    -> Sisa kebutuhan pada keadaan ini adalah 5 item (20 item total kebutuhan - 15 item dipenuhi oleh [level B slot 5]).
  • Sampai disini, tidak ada lagi slot persediaan dengan level B yang masih bisa digunakan untuk memenuhi sisa kebutuhan sebanyak 5 item ini.
• summary tentang jumlah kebutuhan yang belum terpenuhi masih menyisakan kebutuhan sebanyak 5 item. Data ini akan bermanfaat ketika menyusun data pemesanan produk untuk persediaan kepada supplier.

---

Penyusunan formula

Formula disusun dalam tahap-tahap berikut :

1. Membuat nama-nama range
   
   • Pada bahasan ini, nama range akan memudahkan alur penyusunan formula, karena rujukan ke tabel-tabel data dapat langsung dibedakan dengan bentuk rujukan ke record yang sedang diproses.
   • Cara membuat nama range :
     
     • Blok range yang akan diberi nama
     • (Excel 2003 ke bawah) :: menu Insert -> Name -> Define
       
       (Excel 2007 ke atas) :: Klik kanan cell yang terblok -> pilih Name a range
     • Tulis namanya
     • Tekan OK
   • Berikut ini daftar range yang harus diberi nama :
     

     No.	Range yang diblok	Nama Range	Keterangan
     1	G3:H4	_RefLevel_	Data level source
     2	J3:J7	_ExSlot_	Data kelebihan kolom Slot
     3	K3:K7	_ExProduk_	Data kelebihan kolom Produk
     4	L3:L7	_ExExcess_	Data kelebihan kolom Excess
     5	M3:M7	_ExLevel_	Data kelebihan kolom Level

2. Menyusun tabel hasil dilengkapi informasi level persediaan yang bisa digunakan oleh setiap permintaan dan beberapa kolom summary (lihat gambar)
   
   
   • Kolom Source_Level
     
     • Berisi informasi tentang level persediaan yang bisa digunakan oleh record permintaan tersebut
     • Fungsi VLookUp digunakan untuk me-lookup data level pada baris permintaan tersebut untuk mengambil data kolom source_level ditabel referensi (nama range _RefLevel_) dengan tipe pencarian yang sama persis (Exact Match atau opsi FALSE atau opsi bernilai 0 pada fungsi VLookUp)
     • Formula untuk cell E14 adalah (copy formula ke baris permintaan lainnya)
       
       =VLookUp( D14 , _RefLevel_ , 2 , 0 )
   • Area slot kelebihan (area hijau di F12:I13)
     
     • Disusun berdasar kriteria pemenuhan permintaan yang harus berdasar Level dan Slot
     • Urutan prioritas adalah menghabiskan Level A lebih dulu sebelum menggunakan Level B, yang berarti sisi Level diurutkan Ascending dan sisi Slot mengikuti urutan dalam tabel kelebihan
   • Kolom-kolom summary terdiri dari :
     
     • Jumlah Terpenuhi dengan formula pada cell J14 (copy ke baris permintaan lainnya)
       =Sum( F14:I14 )
     • Jumlah Belum Terpenuhi dengan formula pada cell K14 (copy ke baris permintaan lainnya)
       =C14 - J14
3. Formula kalkulasi jumlah persediaan yang diperoleh dari setiap level dan slot dari tabel kelebihan (area range F14:I16)
   
   Formula akan disusun dalam beberapa step. Untuk memudahkan pemahaman, formula akan disusun untuk cell F14 yang kemudian di-copy ke cell lain di area F14:I16. Step-step tersebut adalah :
   1. Kondisi bahwa Level dari slot kelebihan yang sesuai source_level yang akan dihitung. Jika tidak sesuai maka nilai hasilnya adalah 0
      
      • Pemeriksaan ada atau tidaknya level dari slot kelebihan (baris 12) pada daftar source_level di record permintaan tersebut dapat dibantu oleh fungsi CountIF dengan kriteria Contain, yaitu dengan memanfaatkan wildcard character asterix (*) :
        
        CountIF( $E14 , "*" & F$12 & "*" )
        Perhatikan peletakan tanda absolute reference ($), karena formula akan di-copy ke cell lainnya.
      • Nilai permintaan atau total kebutuhan yang akan dikalkulasi haruslah lebih dari 0 ( $C14 > 0 )
      • Formula pengkondisiannya akan menjadi :
        
        =IF( CountIF( $E14 , "*" & F$12 & "*" ) * ( $C14 > 0 ) , "hitung" , 0 )
        
      • Hasil formula yang berupa kata 'hitung' akan diganti oleh formula kalkulasi jumlah item persediaan yang akan disusun pada step berikutnya.
      • Seluruh level persediaan yang tidak sesuai untuk menjadi source_level permintaan telah terisi dengan nilai 0 (diformat number menjadi '-')
   2. Jumlah item persediaan (Excess) dijumlahkan dengan fungsi SumProduct berdasar kecocokan nilai Produk, Level, Slot, dan Excess lebih dari 0 (4 kriteria) yang harus terpenuhi seluruhnya (operator AND yang setara dengan perkalian)
      
      • Kriteria berdasar Produk ( _ExProduk_ = $B14 )
      • Kriteria berdasar Level ( _ExLevel_ = F$12 )
      • Kriteria berdasar Slot ( _ExSlot_ = F$13 )
      • Kriteria berdasar Excess ( _ExExcess_ > 0 )
      • Susunan formula SumProduct untuk menjumlahkan nilai-nilai Excess persediaan
        
        SumProduct( (_ExProduk_=$B14) * (_ExLevel_=F$12) * (_ExSlot_=F$13) * ( _ExExcess_ > 0 ) * _ExExcess_ )
      • Menggantikan kata 'hitung' dalam formula step-1, menjadi :
        
        =IF( CountIF( $E14 , "*" & F$12 & "*" ) * ( $C14 > 0 ) , SumProduct((_ExProduk_=$B14)*(_ExLevel_=F$12)*(_ExSlot_=F$13) *( _ExExcess_ > 0 )*_ExExcess_) , 0 )
        
      • Hasilnya masih berupa seluruh item persediaan dan belum memperhitungkan jumlah item persediaan yang sudah digunakan oleh permintaan sebelumnya. Hal ini tampak pada kolom H dan I
   3. Mendapatkan jumlah item persediaan yang tersisa karena telah digunakan oleh permintaan sebelumnya
      
      • Pada hasil formula step-2 di kolom H, total persediaan untuk slot 2 level B adalah 10. Jika permintaan pertama (baris 14) telah menggunakan 10 item, maka seharusnya pada permintaan kedua nilainya menjadi 0. Begitu juga pada permintaan ketiga. Artinya, yang dibutuhkan adalah sisa persediaan secara progresif pada keaadaan tersebut. Dengan kata lain, ketika mengkalkulasi permintaan kedua, maka sisa persediaan yang dapat digunakan oleh permintaan kedua.
      • Sisa persediaan adalah total persediaan - total terpakai sampai saat itu
        
        Peringatan!!! bahwa proses kalkulasi baru sampai pada tahap pengerjaan per kolom (per slot per level)
      • Total persediaan adalah hasil SumProduct yang disusun di step-2
      • Total terpakai sampai saat itu (sampai permintaan baris yang aktif diproses) adalah penjumlahan yang bertambah referensi barisnya. Formula pengkalkulasinya juga harus bisa digunakan oleh record pertama permintaan. Oleh sebab itu, rujukan cell pertama dalam penjumlahan ini adalah baris 13. Formulanya akan tersusun sebagai berikut : (perhatikan peletakan tanda absolute reference '$')
        
        SUM( F$13:F13 )
      • Karena nilai F13 bisa bertipe numerik, maka untuk menetralisir dampaknya, dibutuhkan proses pengurangan terhadap nilai di F13 dengan memanfaatkan fungsi N(). Susunan formula penetralisir dampak tipe data numerik di F13 adalah
        
        - N( F$13 )
      • Susunan formula total terpakai sampai saat itu adalah
        
        SUM( F$13:F13 ) - N( F$13 )
      • Status formula total terpakai sampai saat itu adalah sebagai pengurang. Maka susunan formulanya menjadi
        
        - ( SUM( F$13:F13 ) - N( F$13 ) )
        yang dapat menyebabkan terjadinya perubahan tanda, sehingga formula menjadi
        
        - SUM( F$13:F13 ) + N( F$13 )
      • Formula tersebut masih belum menyertakan kriteria bahwa yang dijumlahkan pada kolom F tersebut haruslah yang memenuhi seluruh kriteria (dengan operator AND yang setara perkalian [ * ]) sebagai berikut :
        
        1. Produk sebelum baris dikalkulasi (sebelum baris 14) adalah sama dengan produk pada baris 14 yang berupa ekspresi
           ( $B$13:$B13 = $B14 )
        2. Pada source_level sebelum baris dikalkulasi (sebelum baris 14) ditemukan adanya nilai source_level yang sama dengan source_level slot kelebihan yang sedang dikalkulasi (source_level kolom F) yang berupa ekspresi
           IsNumber( Find( F$12 & "," , $E$13:$E13 & "," ) )
           • Fungsi Find akan mencari secara case sensitive (A<>a)
           • Ketika fungsi Find tidak menemukan yang dicari, maka hasilnya adalah error value #Value!
           • Fungsi IsNumber digunakan untuk mengeliminasi munculnya error value tersebut. Jika Find tidak menemukannya, maka hasil IsNumber adalah FALSE yang setara 0
        Maka formula :
        - SUM( F$13:F13 ) + N( F$13 )
        akan mendapatkan tambahan proses terhadap array berdasar kriteria di atas, sehingga fungsi SUM akan diganti dengan fungsi SumProduct. Dengan adanya kedua kriteria diatas, maka penetralisir dampak tipedata numerik pada cell F13 dapat dihilangkan karena tidak mungkin tercapainya kondisi yang sesuai kriteria diatas untuk baris 13. Susunan formula menjadi :
        
        - SumProduct( F$13:F13 * ( $B$13:$B13 = $B14 ) * IsNumber( Find( F$12 & "," , $E$13:$E13 & "," ) )

)

Formula sisa item persediaan yang dapat dipakai untuk permintaan baris tersebut adalah formula SumProduct hasil step-2 yang disatukan dengan formula jumlah item terpakai sampai saat itu yang sudah berstatus sebagai pengurang. Susunannya formula hasil step-2 seutuhnya akan berubah menjadi

=IF( CountIF( $E14 , "*" & F$12 & "*" ) * ($C14>0) , SumProduct((_ExProduk_=$B14)*(_ExLevel_=F$12)*(_ExSlot_=F$13) *( _ExExcess_ > 0 )*_ExExcess_) - SumProduct( F$13:F13 * ( $B$13:$B13 = $B14 ) * IsNumber( Find( F$12 & "," , $E$13:$E13 & "," ) ) ) , 0 )

Sampai disini, sudah tampak pada kolom H dan I bahwa yang digunakan untuk mengisi cell adalah sisa persediaan yang belum terpakai oleh permintaan sebelumnya

Mendapatkan jumlah item pemenuhan kebutuhan sesuai sisa kebutuhan saat itu

• Pada hasil formula step-3, didapatkan hasil berupa nilai persediaan tersisa. Formula tersebut belum mempertimbangkan kondisi jika kebutuhan telah dipenuhi oleh slot dan level sebelumnya, maka sisa kebutuhanlah yang harus dipenuhi dan bukan lagi total kebutuhan.
• Misal pada F14:H14. Ketika F14 telah memenuhi kebutuhan sebanyak 20 item, maka sisa kebutuhan yang harus dipenuhi oleh G14 adalah sebanyak 40 item total kebutuhan - 20 item yang dipenuhi oleh F14, yaitu sebanyak 20 item.
• Begitu juga ketika akan mencari nilai di H14, maka sisa kebutuhan yang harus dipenuhi oleh H14 adalah sebanyak 40 item total kebutuhan - ( 20 item F14 + 15 item G14 ) = 5 item. Formula step-3 yang belum memperhitungkan hal ini akan menghasilkan 10 item yang berupa sisa persediaan slot 2 level B.
• Formula sisa kebutuhan dibentuk menyerupai formula sisa persediaan. Perbedaannya adalah pada orientasi formula. Formula sisa persediaan berorientasi vertikal (khusus per lot per level persediaan), sedangkan formula sisa kebutuhan adalah berorientasi horisontal (khusus per permintaan saja).
• Sisa kebutuhan adalah total kebutuhan - total yang telah terpenuhi sampai saat itu. Total kebutuhan adalah nilai di kolom C (dalam hal ini adalah $C14).
• Total kebutuhan yang telah terpenuhi adalah penjumlahan seluruh cell sebelumnya. Karena formula harus bisa digunakan oleh slot dan level pertama yang ada dalam daftar slot kelebihan (oleh kolom F), maka titik awalnya adalah kolom E dengan formula Sum yang referensi rujukannya akan bertambah pada orientasi horisontal (dalam sebuah permintaan saja). Formulanya adalah
  
  Sum( $E14:E14 )
• Karena data pada kolom E bisa bertipe data numerik, maka perlu antisipasi berupa penetralisir (pengurang) dampak nilai di kolom E, yaitu dengan memanfaatkan fungsi N(). Formula penetralisir dampak nilai numerik di kolom E adalah
  
  - N( $E14 )
• Formula total kebutuhan yang telah terpenuhi adalah
  
  Sum( $E14:E14 ) - N( $E14 )
• Formula sisa kebutuhan akan tersusun sebagai
  
  $C14 - ( Sum( $E14:E14 ) - N( $E14 ) )
  yang dapat diuraikan tanda pengurangannya menjadi
  
  $C14 - Sum( $E14:E14 ) + N( $E14 )
• Sampai disini, sudah didapatkan formula-formula :
  
  • Sisa kebutuhan yang berbunyi
    
    $C14-SUM($E14:E14)+N($E14)
  • Sisa persediaan yang berbunyi
    
    SUMPRODUCT((_ExProduk_=$B14)*(_ExLevel_=F$12)*(_ExSlot_=F$13) *(_ExExcess_>0)*_ExExcess_) -SumProduct(F$13:F13*($B$13:$B13=$B14)*IsNumber(Find(F$12&",",$E$13:$E13&",")))
• Sebagian kondisi yang terbangun dari Sisa kebutuhan terhadap Sisa persediaan adalah
  
  • Jika Sisa kebutuhan >= Sisa persediaan maka ambil nilai Sisa persediaan
  • Jika Sisa kebutuhan < Sisa persediaan maka ambil nilai Sisa kebutuhan
  yang artinya, akan selalu mengambil nilai terendahnya. Untuk sebagian kondisi ini, fungsi Min() dapat dimanfaatkan untuk memilih nilai terendah dari Sisa kebutuhan dan Sisa persediaan dalam bentuk rumusan formula
  
  Min( Sisa kebutuhan , Sisa persediaan )
  Formula seutuhnya akan menjadi
  
  Min( $C14-SUM($E14:E14)+N($E14) , SUMPRODUCT((_ExProduk_=$B14)*(_ExLevel_=F$12)*(_ExSlot_=F$13) *(_ExExcess_>0)*_ExExcess_) -SumProduct(F$13:F13*($B$13:$B13=$B14)*IsNumber(Find(F$12&",",$E$13:$E13&","))) )
• Formula step-3 akan berubah menjadi
  
  =IF( CountIF( $E14 , "*" & F$12 & "*" )*($C14>0) , Min( $C14-SUM($E14:E14)+N($E14) , SUMPRODUCT((_ExProduk_=$B14)*(_ExLevel_=F$12)*(_ExSlot_=F$13) *(_ExExcess_>0)*_ExExcess_) -SumProduct(F$13:F13*($B$13:$B13=$B14)*IsNumber(Find(F$12&",",$E$13:$E13&","))) ) , 0 )
  
• Sampai disini, seluruh formula telah terbentuk.

---

Closing :

Prioritas dalam kalkulasi distribusi diatas tergantung urutan record permintaan dan urutan daftar slot dan level.

---

File(s) :

Formula pendistribusian persediaan pada sheet distribusi

---

Coretan terkait :

• Formula sort data pada data teks
• Formula penyusun data unique yang terurut (sorted)

---