Skip to main content
Home  ›  Excel

Rekap Stok Opname Akhir Tahun di Excel: Selisih Stok, Barang Minus, dan Nilai Persediaan (Format + Rumus)

Diperbarui: 18 Desember 2025

Ringkasan cepat:

  • Stok opname akhir tahun yang rapi cukup pakai 4 sheet: Master_Barang → Stok_Catatan → Stok_Fisik → Rekap_Selisih.
  • Rumus inti: Selisih = Fisik − Catatan, Nilai Selisih = Selisih × Cost per Unit, plus Nilai Persediaan Akhir = Fisik × Cost per Unit.
  • Anti salah hitung: pakai Kode Barang (bukan nama), samakan satuan, dan hindari VLOOKUP untuk stok fisik (lebih aman pakai SUMIFS jika input bisa dobel).

Daftar isi

Kapan stok opname akhir tahun biasanya dilakukan?

Stok opname akhir tahun umumnya dilakukan untuk:

  • Tutup buku: menentukan persediaan akhir yang masuk laporan keuangan.
  • Audit internal/eksternal: memastikan stok bisa dipertanggungjawabkan.
  • Mendeteksi kebocoran: barang sering minus karena retur/rusak tidak tercatat, atau salah input.

Apa itu rekap stok opname akhir tahun?

Rekap stok opname adalah perbandingan antara:

  • Stok Catatan (menurut sistem/buku pada tanggal cutoff), dan
  • Stok Fisik (hasil hitung nyata di gudang/toko).

Output yang biasanya diminta atasan/owner:

  • Barang minus (fisik lebih kecil dari catatan),
  • Nilai selisih (rupiah dampak selisih),
  • Nilai persediaan akhir (rupiah stok yang benar-benar ada).

Catatan amanah: stok opname menyangkut hak dan tanggung jawab. Hindari “mengakali” angka karena bisa merugikan orang lain dan merusak keputusan bisnis.

Syarat & data yang perlu disiapkan

  • Kode barang konsisten (wajib).
  • Nama barang (memudahkan tim lapangan).
  • Satuan (pcs/box/kg/liter) harus sama antara catatan dan fisik.
  • Cost per Unit (harga beli/biaya perolehan) untuk valuasi stok dan nilai selisih.
  • Opsional: kategori, lokasi rak, dan PIC pemeriksa.

Langkah membuat rekap stok opname (format Excel siap pakai)

Struktur file (disarankan)

  • Master_Barang: daftar item + satuan + cost per unit.
  • Stok_Catatan: stok menurut sistem pada tanggal cutoff.
  • Stok_Fisik: hasil hitung fisik (boleh ada baris dobel per kode, nanti ditotal dengan SUMIFS).
  • Rekap_Selisih: hasil gabungan + analisis + ringkasan.

1) Sheet Master_Barang (contoh tabel + kolom A, B, C…)

A: Kode Barang B: Nama Barang C: Satuan D: Cost per Unit (Rp) E: Kategori (opsional) F: Lokasi Rak (opsional)
BRG001 Keripik Pisang 200g pcs 12000 Makanan R1-A
BRG002 Sambal 150ml pcs 9000 Makanan R1-B

2) Sheet Stok_Catatan (stok menurut sistem)

A: Kode Barang B: Stok Catatan C: Tanggal Cutoff D: Catatan (opsional)
BRG001 120 31/12/2025 Stok sistem per tutup buku
BRG002 85 31/12/2025 -

3) Sheet Stok_Fisik (hasil hitung lapangan)

Kalau 1 item dihitung di 2 lokasi/rak, boleh masuk dua baris (nanti dijumlah otomatis). Contoh:

A: Kode Barang B: Stok Fisik C: Lokasi (opsional) D: PIC Hitung E: Tanggal Hitung F: Catatan
BRG001 80 R1-A Andi 31/12/2025 -
BRG001 36 Backroom Sinta 31/12/2025 Dus sobek

Kolom cek dobel (opsional tapi bagus): di G2 (Duplikat?) pada sheet Stok_Fisik:

=IF(COUNTIF($A:$A,A2)>1,"Dobel","OK")

4) Sheet Rekap_Selisih (gabungkan + hitung otomatis)

Susun header begini:

A: Kode B: Nama Barang C: Satuan D: Cost per Unit E: Stok Catatan F: Stok Fisik G: Selisih (F-E) H: Nilai Selisih (G×D) I: Nilai Persediaan Akhir (F×D) J: Status
BRG001 (otomatis) (otomatis) (otomatis) (otomatis) (otomatis) (otomatis) (otomatis) (otomatis) (otomatis)

Rumus yang dipakai (contoh baris 2):

B2 (Nama Barang) dari Master_Barang:

=IFERROR(VLOOKUP($A2,Master_Barang!$A$2:$F$1000,2,FALSE),"")

C2 (Satuan):

=IFERROR(VLOOKUP($A2,Master_Barang!$A$2:$F$1000,3,FALSE),"")

D2 (Cost per Unit):

=IFERROR(VLOOKUP($A2,Master_Barang!$A$2:$F$1000,4,FALSE),0)

E2 (Stok Catatan):

=IFERROR(VLOOKUP($A2,Stok_Catatan!$A$2:$B$5000,2,FALSE),0)

F2 (Stok Fisik) — pakai SUMIFS (lebih aman dari VLOOKUP):

=SUMIFS(Stok_Fisik!$B:$B,Stok_Fisik!$A:$A,$A2)

G2 (Selisih):

=F2-E2

H2 (Nilai Selisih):

=G2*D2

I2 (Nilai Persediaan Akhir):

=F2*D2

J2 (Status):

=IF(G2<0 inus="">0,"Lebih","Sesuai"))

5) Ringkasan cepat (yang biasanya diminta saat tutup buku)

Letakkan di bagian atas Rekap_Selisih (misal sel L2 dst):

  • Total Nilai Persediaan Akhir: =SUM(I2:I5000)
  • Total Nilai Selisih: =SUM(H2:H5000)
  • Jumlah Item Minus: =COUNTIF(J2:J5000,"Minus")
  • Jumlah Item Lebih: =COUNTIF(J2:J5000,"Lebih")

6) Cara jadikan file ini “template” (biar tahun depan tinggal pakai)

  • Simpan file master: StokOpname_Template.xlsx, lalu duplikasi tiap tahun (misal: StokOpname_2026.xlsx).
  • Kunci header & rumus di Rekap_Selisih (Protect Sheet) agar tim hanya input di Stok_Fisik / Stok_Catatan.
  • Gunakan Data Validation untuk satuan (pcs/box/kg) agar tidak typo.
  • Tambahkan cutoff di 1 sel (misal B1) supaya semua tim sepakat tanggal hitung.

Tips kualitas data (biar selisih tidak “abu-abu”)

  • Samakan satuan sebelum input (box → pcs harus dikonversi dulu).
  • Wajib pakai Kode Barang sebagai kunci utama (nama sering beda spasi/varian).
  • Cutoff jelas: transaksi setelah cutoff jangan “diam-diam” mengubah stok catatan untuk opname ini.
  • Double-check untuk item mahal: hitung oleh 2 orang (mengurangi salah hitung dan menjaga amanah).
  • Dokumentasikan sebab selisih: rusak, kadaluarsa, retur, salah input masuk/keluar, barang pindah lokasi.

Risiko & kesalahan umum

  • Satuan beda → selisih besar padahal sebenarnya normal.
  • Kode tidak konsisten (BRG01 vs BRG001) → lookup gagal, angka jadi 0.
  • Cost per Unit tidak update → nilai persediaan tidak realistis.
  • Cutoff tidak disiplin → minus/lebih sulit dijelaskan karena ada transaksi “nyangkut”.
  • Duplikasi input fisik → kalau pakai VLOOKUP, hasil bisa salah; gunakan SUMIFS seperti di atas.

FAQ (Pertanyaan yang sering muncul)

1) Untuk valuasi stok akhir, pakai HPP atau harga beli?

Untuk nilai persediaan akhir, yang dipakai umumnya cost per unit (harga beli/biaya perolehan). HPP biasanya konteksnya biaya barang yang terjual dalam periode, bukan nilai stok yang masih tersisa.

2) Kenapa banyak barang minus padahal saya merasa penjualan wajar?

Penyebab umum: transaksi keluar tidak tercatat, retur/rusak tidak dicatat, cutoff tidak jelas, atau salah satuan. Mulai audit dari item minus terbesar (nilai selisih paling besar).

3) Stok fisik saya dicatat per rak (banyak baris), apa aman?

Aman, asalkan rekap stok fisik memakai SUMIFS berdasarkan kode barang (seperti rumus F2), sehingga semua lokasi otomatis dijumlah.

4) Apakah stok opname harus tepat 31 Desember?

Tidak wajib. Bisa beberapa hari sebelum/sesudahnya, asalkan cutoff disepakati dan transaksi setelah cutoff diperlakukan konsisten.

5) Bisa nggak laporan stok opname ini dijadikan dashboard?

Bisa. Setelah rekap selisih rapi, buat Pivot dan dashboard agar cepat difilter per kategori/lokasi.

Baca juga di Beginisob.com

Comments

Post a Comment

Edukasi Terpopuler

Connect With Us

Copyright @ 2023 beginisob.com, All right reserved