Cara Melakukan Analisis ABC di Excel: 5 Langkah Mudah

Analisis ABC mengelompokkan item inventaris ke dalam tiga kelas berdasarkan nilai penggunaan tahunannya. Item Kelas A adalah beberapa item yang menyumbang sebagian besar uang. Item Kelas C adalah banyak item yang menyumbang sedikit uang, dan kelas B berada di antaranya. Di Excel, Anda dapat melakukannya dengan satu tabel: nilai tahunan, bagian dari total, total kumulatif, dan rumus yang menetapkan setiap kelas.

Panduan ini menjelaskan arti dari kelas-kelas tersebut, lima langkah Excel, contoh pengerjaan, dan cara membuat grafik dari hasilnya. Panduan ini juga membahas cara memilih batas nilai (cutoff) Anda dan apa yang harus dilakukan dengan setiap kelas setelah analisis selesai.

Apa itu analisis ABC

Analisis ABC adalah cara untuk memutuskan item mana yang paling layak mendapatkan perhatian. Analisis ini didasarkan pada pola sederhana: sebagian kecil item menyumbang sebagian besar pengeluaran.

Sebuah bab tahun 2012 tentang analisis dan pengendalian pengeluaran farmasi, dari Management Sciences for Health (MSH), menjelaskannya dengan gamblang. Bab tersebut mencatat bahwa "sejumlah kecil item menyumbang sebagian besar nilai konsumsi tahunan." Ditambahkan pula: "Analisis terhadap fenomena ini dikenal sebagai analisis Pareto atau, yang lebih umum, analisis ABC."

Bab yang sama menjelaskan bahwa item "dapat diklasifikasikan ke dalam tiga kategori (A, B, dan C) berdasarkan nilai penggunaan tahunannya." Metodenya tetap sama baik Anda menyetok obat-obatan, suku cadang, maupun produk ritel.

Satu hal yang sering kali terlewatkan. Kelas-kelas ini bukanlah label permanen. MSH mencatat bahwa "Jika pola penggunaan berubah, item tersebut mungkin masuk ke kategori yang berbeda saat analisis ABC berikutnya dilakukan." Jadi, analisis ABC berfungsi paling baik sebagai pemeriksaan rutin, bukan proyek sekali jalan.

Arti dari kelas A, B, dan C

Bab MSH tersebut memberikan rentang tipikal untuk setiap kelas:

Kelas	Bagian dari item	Bagian dari nilai tahunan	Arti biasanya
A	10 hingga 20 persen	75 hingga 80 persen	Sedikit item, sebagian besar uang
B	10 hingga 20 persen	15 hingga 20 persen	Kelompok menengah
C	60 hingga 80 persen	5 to 10 persen	Banyak item, sedikit uang

Ini adalah rentang tipikal, bukan aturan baku. MSH menyatakan "Batasan ini agak fleksibel." Contohnya justru menetapkan kelas A pada item yang jika dijumlahkan mencapai 70 percent dari dana.

Nilai yang menentukan kelas-kelas ini adalah nilai konsumsi tahunan: unit yang digunakan dalam setahun dikalikan biaya per unit. Item murah yang digunakan dalam volume besar bisa masuk ke kelas A. Item mahal yang digunakan sekali setahun bisa masuk ke kelas C.

Sebuah artikel tahun 2014 di American Journal of Business Education mempertanyakan penggunaan nilai saja. Artikel tersebut berargumen bahwa buku teks "berfokus pada volume dolar sebagai satu-satunya kriteria" dan menyarankan untuk menambahkan kriteria lain. Untuk tahap awal, nilai adalah metode yang digunakan dalam bab MSH tersebut.

Apa yang Anda butuhkan sebelum memulai

Analisis ABC di Excel hanya membutuhkan beberapa kolom per item:

• Nama item atau SKU. Satu baris per item.
• Unit tahunan yang digunakan atau dibeli. Gunakan periode 12-month yang sama untuk setiap item.
• Biaya per unit. Biaya untuk satu unit, dalam satuan unit yang sama dengan yang Anda hitung.

MSH menekankan pentingnya kesamaan periode: "Pastikan periode peninjauan yang sama digunakan untuk semua item guna menghindari perbandingan yang tidak valid." MSH juga menyarankan untuk menggunakan unit dasar yang sama untuk biaya dan kuantitas, seperti satu tablet atau satu kotak, daripada mencampuradukkan ukuran kemasan.

Jika data Anda berasal dari sistem inventaris atau pembelian, ekspor data tersebut sebagai file CSV atau Excel. Hapus item yang tidak memiliki aktivitas dalam periode tersebut, atau pertahankan dan biarkan item tersebut masuk ke kelas C.

Cara melakukan analisis ABC di Excel

Lima langkah di bawah ini mengikuti metode dalam bab MSH, yang disesuaikan dengan rumus Excel. Contoh ini menempatkan judul di baris 1, header di baris 2, dan 10 item di baris 3 hingga 12. Kolom A, B, dan C berisi nama item, unit tahunan, dan biaya per unit.

Langkah 1: Buat daftar item, unit, dan biaya per unit

Masukkan atau tempel satu baris per item beserta nama, unit tahunan, dan biaya per unitnya. Tambahkan header di baris 2 agar tabel mudah diurutkan nanti.

Periksa data sebelum melangkah lebih jauh. Cari biaya yang kosong, kuantitas negatif, dan SKU duplikat, karena masing-masing hal tersebut akan mengacaukan totalnya. Filter cepat pada setiap kolom biasanya dapat menemukannya.

Jika beberapa pembelian untuk item yang sama dilakukan dengan harga berbeda, gunakan satu biaya yang konsisten. MSH mencatat bahwa "rata-rata tertimbang atau rata-rata FIFO" adalah alternatif paling akurat ketika biaya per unit yang sebenarnya sulit dilacak.

Langkah 2: Hitung nilai tahunan dan bagiannya dari total

Di kolom D, kalikan unit dengan biaya untuk mendapatkan nilai tahunan setiap item. Di D3, masukkan =B3*C3 dan tarik rumus ke bawah.

Di kolom E, bagi setiap nilai dengan total dari semua nilai untuk mendapatkan bagiannya. Di E3, masukkan =D3/SUM($D$3:$D$12) dan tarik ke bawah. Tanda dolar menjaga rentang total tetap terkunci saat rumus disalin. Format kolom E sebagai persentase dengan dua tempat desimal.

MSH merekomendasikan presisi tersebut karena suatu alasan. Menurut mereka, "beberapa item mungkin memiliki nilai yang berdekatan dan banyak item yang mungkin mewakili kurang dari 1 percent dari total nilai."

Langkah 3: Urutkan item berdasarkan nilai, dari yang terbesar

Pilih seluruh tabel, termasuk header, lalu urutkan berdasarkan kolom D dari yang terbesar ke terkecil. Di Excel, caranya adalah Data, lalu Sort, dengan kolom D dan urutan diatur ke Largest to Smallest.

Jika Anda lebih menyukai rumus, fungsi SORT akan mengembalikan salinan yang telah diurutkan. Sintaks dari Microsoft adalah =SORT(array,[sort_index],[sort_order],[by_col]), di mana urutan pengurutan -1 berarti menurun. Untuk tabel ini, =SORT(A3:E12,4,-1) mengurutkan berdasarkan kolom keempat, dengan nilai tertinggi terlebih dahulu.

Setelah langkah ini, item dengan nilai tahunan tertinggi akan berada di paling atas. Urutan itulah yang membuat total kumulatif pada langkah berikutnya menjadi bermakna.

Langkah 4: Tambahkan persentase kumulatif

Di kolom F, tambahkan total kumulatif dari bagian tersebut. Di F3, masukkan =SUM($E$3:E3) dan tarik ke bawah. Bagian pertama dari rentang tetap terkunci, dan bagian kedua bertambah satu baris setiap kalinya.

Baris terakhir harus menunjukkan 100 percent. Jika tidak, periksa apakah ada sel kosong atau nilai teks di kolom D dan E.

Kolom ini adalah inti dari analisis ABC. Kolom ini menunjukkan seberapa besar nilai total yang disumbangkan oleh item-item di atas setiap baris secara bersama-sama.

Langkah 5: Tetapkan kelas A, B, dan C

Di kolom G, gunakan rumus untuk melabeli setiap item. Dengan batas nilai 80 dan 95 percent, masukkan ini di G3 dan tarik ke bawah:

=IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")

Fungsi IFS memeriksa setiap kondisi secara berurutan dan mengembalikan kecocokan pertama. Contoh dari Microsoft sendiri menggunakan pola yang sama, dengan TRUE sebagai penampung akhir untuk semua kondisi lainnya. Item hingga kumulatif 80 percent menjadi A, item hingga 95 percent menjadi B, dan sisanya menjadi C.

Terakhir, hitung setiap kelas dengan =COUNTIF(G3:G12,"A") dan lakukan hal yang sama untuk B dan C. Bandingkan jumlahnya dengan rentang tipikal di atas. Sesuaikan batas nilai jika kelas A terlalu besar atau terlalu kecil untuk dikelola oleh tim Anda.

Contoh pengerjaan

Berikut adalah tabel ilustrasi untuk 10 item, yang sudah diurutkan berdasarkan nilai tahunan. Angka-angka ini adalah contoh, bukan data dari perusahaan nyata.

Item	Unit tahunan	Biaya per unit	Nilai tahunan	Bagian	Kumulatif	Kelas
SKU-01	1,200	$45.00	$54,000	36.00%	36.00%	A
SKU-02	3,000	$12.00	$36,000	24.00%	60.00%	A
SKU-03	500	$40.00	$20,000	13.33%	73.33%	A
SKU-04	8,000	$1.50	$12,000	8.00%	81.33%	B
SKU-05	2,000	$4.00	$8,000	5.33%	86.67%	B
SKU-06	600	$10.00	$6,000	4.00%	90.67%	B
SKU-07	1,000	$5.00	$5,000	3.33%	94.00%	B
SKU-08	1,500	$3.00	$4,500	3.00%	97.00%	C
SKU-09	700	$5.00	$3,500	2.33%	99.33%	C
SKU-10	400	$2.50	$1,000	0.67%	100.00%	C

Total nilai tahunan adalah $150,000. Tiga item, yaitu 30 percent dari daftar, menyumbang 73.33 percent dari nilai tersebut dan masuk ke kelas A. Empat item masuk ke kelas B, dan tiga item terakhir, yang bernilai 6 percent dari nilai tersebut, masuk ke kelas C.

Dua detail tampak menonjol. SKU-04 memiliki unit yang jauh paling banyak, tetapi biayanya yang rendah menempatkannya di kelas B. Dan dengan hanya 10 item, bagian kelas tidak akan cocok dengan rentang tipikal, yang mana merupakan hal normal untuk daftar yang pendek.

Cara membuat grafik dari hasilnya

Grafik membuat pola tersebut mudah ditunjukkan dalam rapat. MSH menyarankan untuk memplot persentase kumulatif terhadap nomor item, yang akan menghasilkan kurva ABC yang umum dikenal.

Excel memiliki grafik bawaan untuk hal ini. Microsoft mendeskripsikan grafik Pareto sebagai grafik yang "berisi kolom-kolom yang diurutkan dalam urutan menurun dan garis yang mewakili persentase total kumulatif." Untuk membuatnya, pilih nama item dan nilai tahunan, lalu pilih Insert, Insert Statistic Chart, dan Pareto.

Tambahkan dua garis horizontal atau label pada batas nilai Anda, seperti 80 dan 95 percent, sehingga audiens dapat melihat di mana setiap kelas dimulai. Panduan kami tentang cara membuat Pareto chart with AI membahas grafik itu sendiri secara lebih mendalam.

Memilih batas nilai (cutoff) Anda

Tidak ada satu batas nilai yang benar mutlak. MSH menjelaskan bahwa pilihan tersebut "tergantung pada bagaimana volume dan nilai tersebar di antara item-item dalam daftar." Hal itu juga tergantung pada "bagaimana hasil analisis ABC akan digunakan."

Kapasitas manajemen adalah batasan praktisnya. MSH menyatakannya secara langsung: "alokasi item ke kelas A harus didasarkan pada kapasitas manajemen." Jika tim Anda hanya dapat meninjau 50 item secara mendalam setiap bulan, kelas A yang berisi 300 item akan membuat tujuan analisis ini sia-sia.

Beberapa pendekatan umum:

• Batas nilai berdasarkan nilai uang. A hingga 80 percent dari nilai, B hingga 95 percent, C untuk sisanya. Ini adalah metode yang digunakan di atas.
• Batas nilai berdasarkan jumlah item. 20 percent item teratas berdasarkan nilai menjadi A, 30 percent berikutnya menjadi B, dan sisanya menjadi C.
• Daftar tetap. Beberapa tim menetapkan kelas A sebagai 25 atau 50 item teratas, berapa pun bagian nilai yang mereka miliki.

Apa pun yang Anda pilih, catatlah dan gunakan setiap kali melakukan analisis. Membandingkan kelas kuartal ini dengan kuartal sebelumnya hanya akan berfungsi jika batas nilainya tetap sama.

Apa yang harus dilakukan dengan setiap kelas

Inti dari analisis ABC adalah memfokuskan upaya pada tempat di mana uang berada. Bab MSH mencantumkan beberapa cara untuk menggunakan hasil tersebut:

• Pesan item kelas A lebih sering. MSH menyatakan bahwa memesan item kelas A "lebih sering dan dalam jumlah yang lebih kecil akan mengarah pada pengurangan biaya penyimpanan inventaris."
• Negosiasikan harga kelas A terlebih dahulu. "Pengurangan harga untuk item yang diklasifikasikan sebagai produk A dalam analisis dapat menghasilkan penghematan yang signifikan," menurut bab tersebut.
• Hitung stok kelas A lebih sering. MSH mencatat bahwa "perhitungan stok siklis harus dipandu oleh analisis ABC, dengan perhitungan yang lebih sering untuk item kelas A."
• Awasi status pesanan kelas A. Kekurangan item kelas A yang tidak terduga dapat menyebabkan pembelian darurat yang mahal.

Item Kelas C dapat diberikan aturan yang lebih sederhana, seperti pesanan yang lebih besar namun lebih jarang, serta perhitungan yang lebih sedikit. Kelas B berada di antaranya. Jika barang yang lambat laku menjadi perhatian, panduan kami tentang cara spot slow-moving inventory sangat cocok dipadukan dengan analisis ini.

Melakukannya lebih cepat dengan AI

Langkah-langkah Excel hanya membutuhkan waktu beberapa menit setelah datanya bersih. Namun, membersihkan data ekspor dan mengulangi pekerjaan tersebut setiap kuartal membutuhkan waktu lebih lama.

Ruang kerja AI dapat melakukan perhitungan aritmetika dan pengurutan dalam satu permintaan. Unggah hasil ekspor inventaris atau pembelian ke Powerdrill Bloom dan mintalah dalam bahasa sehari-hari untuk melakukan analisis ABC dengan batas nilai Anda. Mintalah nilai tahunan, bagian, persentase kumulatif, dan kelas untuk setiap item, ditambah grafik Pareto.

Kemudian periksa hasilnya seperti spreadsheet lainnya. Konfirmasikan total nilai tahunan dengan penjumlahan Anda sendiri, dan lakukan pemeriksaan acak pada dua item di setiap kelas. Halaman Excel AI assistant kami membahas jenis pekerjaan spreadsheet tersebut secara lebih mendetail. Untuk melihat alat peramalan secara lebih luas, lihat rangkuman AI tools for inventory and demand forecasting ini.

Kesalahan umum yang harus dihindari

• Mencampuradukkan periode waktu. Dua belas bulan untuk satu item dan enam bulan untuk item lainnya membuat bagian nilai menjadi tidak berarti.
• Menggunakan unit alih-alih nilai. Kelas-kelas ini bergantung pada unit dikalikan biaya, bukan pada unit saja.
• Lupa mengurutkan sebelum menghitung total kumulatif. Persentase kumulatif pada daftar yang tidak diurutkan akan menempatkan item di kelas yang salah.
• Menganggap kelas bersifat permanen. Jalankan kembali analisis setiap kuartal atau tahun, karena item dapat berpindah antar-kelas.
• Batas nilai yang mengabaikan kapasitas. Daftar kelas A yang terlalu panjang untuk dikelola secara mendalam tidak akan mendapatkan perhatian yang lebih baik daripada kelas B.
• Mengabaikan item murah yang penting. Item bernilai rendah tetap dapat menghentikan pekerjaan jika habis. Bab MSH memasangkan analisis ABC dengan penilaian terpisah untuk item vital, esensial, dan non-esensial.

Ketika daftar item Anda berasal dari ekspor yang berantakan, Anda dapat try Powerdrill Bloom untuk membuat tabel dan grafik ABC pertama Anda.

Pertanyaan yang sering diajukan

Apa itu analisis ABC dalam manajemen inventaris?

Analisis ABC mengelompokkan item ke dalam tiga kelas berdasarkan nilai konsumsi tahunan. Item Kelas A adalah beberapa item yang menyumbang sebagian besar nilai. Item Kelas C adalah banyak item yang menyumbang sedikit nilai, dan kelas B berada di antaranya. Analisis ini membantu tim memfokuskan upaya pengendalian pada tempat di mana uang berada.

Bagaimana cara menghitung analisis ABC di Excel?

Kalikan unit tahunan dengan biaya per unit untuk setiap item, lalu bagi dengan totalnya untuk mendapatkan bagian masing-masing item. Urutkan berdasarkan nilai dari yang terbesar ke terkecil, tambahkan total kumulatif dari bagian tersebut, dan tetapkan kelas dengan rumus seperti IFS. Batas nilai 80 dan 95 percent berada dalam rentang tipikal dalam bab MSH.

Berapa persentase untuk analisis ABC?

Panduan umum menyatakan bahwa kelas A mencakup 10 hingga 20 percent item dan 75 hingga 80 percent nilai. Kelas B mencakup 10 hingga 20 percent item lainnya dan 15 to 20 percent nilai. Kelas C mencakup 60 hingga 80 percent item dan 5 to 10 percent nilai.

Apa rumus untuk klasifikasi ABC di Excel?

Dengan persentase kumulatif di kolom F dan data dimulai dari baris 3, gunakan =IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C"). Ubah 0.8 dan 0.95 untuk mencocokkan dengan batas nilai Anda sendiri. Rumus IF bersarang juga dapat melakukan pekerjaan yang sama.

Mengapa analisis ABC itu penting?

Analisis ini menunjukkan ke mana sebagian besar uang inventaris mengalir, sehingga tim dapat mengelola item-item tersebut dengan lebih ketat. Penggunaan tipikal meliputi pemesanan item kelas A yang lebih sering, negosiasi harga terlebih dahulu, dan penghitungan yang lebih sering. Analisis ini juga menandai pengeluaran yang tidak sesuai dengan rencana.

Sources: Management Sciences for Health, MDS-3 Bab 40: Menganalisis dan mengendalikan pengeluaran farmasi · Ravinder dan Misra, Analisis ABC untuk Manajemen Inventaris (2014) · Microsoft Support, fungsi SORT · Microsoft Support, fungsi IFS · Microsoft Support, Membuat grafik Pareto.