Stok Barang Gudang: Merancang Sheet Masuk, Keluar, dan Saldo

Stok barang gudang butuh lima tabel terpisah, bukan satu tabel besar: Master Barang, Master Lokasi, Barang Masuk, Barang Keluar, dan Mutasi antar gudang.

Bedanya sama stok toko ada di tiga hal: banyak lokasi penyimpanan, perpindahan barang antar gudang, dan barang yang punya masa berlaku.

Tiga hal itu yang bikin rancangan satu tabel langsung ambruk. Di bawah ini struktur yang tahan, rumus saldonya, dan cara nyusun sheet stock opname.

Kenapa stok gudang butuh sheet terpisah?

Karena satu kode barang bisa ada di beberapa tempat sekaligus. Barang yang sama bisa nyimpen 40 unit di gudang utama dan 15 unit di gudang cabang, dengan riwayat masuk yang berbeda. Satu tabel dengan satu kolom saldo nggak bisa nyimpen dua kenyataan itu tanpa kehilangan jejak.

Masalah kedua ada di perpindahan. Barang yang pindah dari gudang utama ke cabang itu bukan pembelian dan bukan penjualan.

Kalau dicatat sebagai masuk dan keluar biasa, angka total pembelian bulan itu langsung menggelembung. Laporan pembelian jadi ngasih angka yang lebih besar dari nota supplier.

Buat toko kecil dengan satu tempat penyimpanan, struktur tiga sheet di aplikasi stok barang di Excel udah cukup. Artikel ini buat yang udah punya lebih dari satu lokasi.

Gimana struktur sheet stok barang gudang yang benar?

Lima tabel, masing-masing satu peran. Master Barang nyimpen identitas SKU, Master Lokasi nyimpen daftar gudang dan rak, sementara tiga sisanya nyimpen kejadian: penerimaan dari luar, pengeluaran ke luar, dan perpindahan internal. Saldo nggak disimpan di mana pun, dia selalu hasil hitungan.

Sheet	Isi	Frekuensi berubah
Master	kode, nama, satuan, harga_beli, stok_minimum, punya_kedaluwarsa	Jarang
Lokasi	kode_gudang, nama_gudang, kode_rak, kapasitas	Jarang
Masuk	tanggal, kode, gudang, rak, qty, batch, kedaluwarsa, supplier, no_nota	Harian
Keluar	tanggal, kode, gudang, rak, qty, batch, tujuan, no_bukti	Harian
Mutasi	tanggal, kode, gudang_asal, gudang_tujuan, qty, batch, no_surat_jalan	Mingguan

Ubah kelimanya jadi Excel Table lewat Ctrl+T, lalu kasih nama tblMaster, tblLokasi, tblMasuk, tblKeluar, dan tblMutasi.

Satu aturan yang bikin seluruh rancangan ini jalan: satu baris mutasi cuma boleh punya satu gudang asal dan satu gudang tujuan. Kalau barang dipecah ke dua gudang, tulis dua baris.

Kolom apa yang wajib di sheet Masuk dan Keluar?

Enam kolom wajib di dua-duanya: tanggal, kode barang, kode gudang, jumlah, nomor bukti, dan pelaku transaksi. Kolom batch dan kedaluwarsa jadi wajib kalau barangnya punya masa berlaku. Kolom rak sifatnya operasional, dipakai buat nyari barang, bukan buat hitungan saldo.

Dua kolom yang paling sering dilupain dan paling mahal akibatnya:

• Kode gudang. Tanpa ini, kamu cuma tau total stok nasional dan nggak tau di mana barangnya. Waktu ada permintaan mendadak, kamu nggak bisa jawab gudang mana yang punya.
• Nomor bukti. Ini jangkar penelusuran. Waktu selisih muncul, nomor nota yang nyambungin baris di sheet sama kertas di map.

Kunci kolom kode barang dan kode gudang pakai Data Validation supaya nggak ada yang ngetik bebas. Sumber daftarnya ambil dari dua sheet master.

=INDIRECT("tblMaster[kode]")
=INDIRECT("tblLokasi[kode_gudang]")

Gimana rumus saldo gudang dihitung?

Saldo per gudang dihitung dengan SUMIFS yang nyaring dua syarat sekaligus: kode barang dan kode gudang. Hasilnya berupa penerimaan dikurangi pengeluaran, lalu ditambah mutasi yang masuk ke gudang itu dan dikurangi mutasi yang keluar darinya. Empat bagian itu dijumlahin dalam satu rumus.

Bikin sheet baru bernama Saldo. Taruh kode barang di kolom A mulai baris 2, dan kode gudang di baris 1 mulai kolom B.

Di sel B2, tulis:

=SUMIFS(tblMasuk[qty],tblMasuk[kode],$A2,tblMasuk[gudang],B$1)
-SUMIFS(tblKeluar[qty],tblKeluar[kode],$A2,tblKeluar[gudang],B$1)
+SUMIFS(tblMutasi[qty],tblMutasi[kode],$A2,tblMutasi[gudang_tujuan],B$1)
-SUMIFS(tblMutasi[qty],tblMutasi[kode],$A2,tblMutasi[gudang_asal],B$1)

Perhatikan posisi tanda dolar. $A2 ngunci kolomnya, B$1 ngunci barisnya.

Berkat dua kunci itu, satu rumus bisa ditarik ke kanan dan ke bawah buat ngisi seluruh tabel silang. Cara kerja penguncian ini dibahas di absolute reference Excel, dan argumen fungsinya di fungsi SUMIFS.

Buat pengguna Excel dengan pengaturan regional Indonesia, koma pemisah argumen ganti jadi titik koma.

Kolom total di paling kanan cukup pakai penjumlahan biasa. Angkanya harus sama persis dengan hitungan tanpa penyaringan gudang, dan itu cara cepat ngecek rumusmu bener.

=SUM(B2:D2)

Gimana cara nanganin barang kedaluwarsa dan batch?

Saldo dihitung per pasangan kode barang dan nomor batch, bukan per kode barang saja. Sheet Masuk nyimpen tanggal kedaluwarsa tiap batch, dan sheet Keluar wajib nyebut batch mana yang diambil. Dengan begitu kamu bisa tau umur tiap tumpukan, bukan cuma jumlah totalnya.

Tambah satu syarat batch ke rumus saldo:

=SUMIFS(tblMasuk[qty],tblMasuk[kode],$A2,tblMasuk[batch],$B2)
-SUMIFS(tblKeluar[qty],tblKeluar[kode],$A2,tblKeluar[batch],$B2)

Buat peringatan masa berlaku, hitung selisih hari dari tanggal hari ini:

=IF([@kedaluwarsa]-TODAY()<=0,"KEDALUWARSA",IF([@kedaluwarsa]-TODAY()<=30,"SEGERA KELUARKAN","AMAN"))

Warnai kolom ini pakai Conditional Formatting. Merah buat yang lewat, kuning buat yang tinggal sebulan.

Aturan pengeluarannya ikut masa berlaku, bukan urutan datang. Batch dengan tanggal kedaluwarsa paling dekat keluar duluan, walaupun dia datang belakangan.

Fungsi tanggal yang dipakai di atas dijelasin di fungsi TODAY.

Gimana bikin sheet stock opname?

Sheet Opname isinya empat kolom: kode barang, gudang, saldo sistem, dan hasil hitung fisik. Kolom saldo sistem ditarik dari sheet Saldo pakai rumus, sementara kolom fisik diisi manual waktu penghitungan. Kolom kelima berisi selisih, yaitu fisik dikurangi sistem.

Urutkan barisnya berdasarkan kode rak, bukan berdasarkan kode barang. Petugas gudang jalan mengikuti rak, jadi daftar yang urut rak bikin mereka nggak bolak-balik.

=[@fisik]-[@sistem]

Tambah kolom nilai selisih dalam rupiah biar prioritas penelusuran jelas:

=[@selisih]*VLOOKUP([@kode],tblMaster[[kode]:[harga_beli]],4,FALSE)

Selisih 40 unit paku senilai Rp 60.000 nggak sepenting selisih 2 unit barang senilai Rp 1,4 juta. Kolom rupiah yang bikin urutan itu kelihatan.

Setelah selesai, jangan langsung ubah angka sistem. Catat penyesuaian sebagai baris baru di sheet Masuk atau Keluar dengan keterangan "penyesuaian opname" dan nomor bukti berita acara.

Contoh kasus: dua gudang toko_berkah

Dataset latihan toko_berkah di ngulikdata punya 42 SKU yang tersebar di dua lokasi: Gudang Utama di belakang toko, dan Gudang Cabang di ruko sebelah.

Selama Juli 2025 ada 6 baris mutasi antar gudang. Total nilai persediaan akhir bulan Rp 37,2 juta.

Kode	Nama	G. Utama	G. Cabang	Total	Umur stok cabang
BRG-014	Minyak Goreng 2L	88	27	115	12 hari
BRG-007	Beras Premium 5kg	45	18	63	9 hari
BRG-019	Sirup Botol Besar	6	64	70	117 hari
BRG-026	Kecap Manis 600ml	11	48	59	102 hari

Dua baris terakhir yang bikin kolom umur stok kepakai. Sirup dan kecap numpuk di cabang lebih dari 100 hari.

Total ada 5 SKU dengan umur stok di atas 90 hari di Gudang Cabang. Nilainya Rp 4,1 juta, atau 11 persen dari seluruh nilai persediaan.

Angka 11 persen itu uang yang duduk diam di rak sebelah, sementara gudang utama sempat kehabisan gula pasir di minggu yang sama.

Akar masalahnya kelihatan waktu sheet Mutasi ditelusuri. Lima dari enam mutasi arahnya sama, dari utama ke cabang, dan nggak ada satu pun yang balik.

Cabang dipakai sebagai tempat naruh barang yang nggak muat, bukan sebagai titik jual. Sekali barang pindah ke sana, dia berhenti bergerak.

Perbaikannya nggak butuh sistem baru. Cukup satu aturan: barang boleh pindah ke cabang cuma kalau penjualan di cabang bulan lalu lebih besar dari nol.

Tanpa kolom gudang di tiap transaksi, pola ini nggak akan pernah kelihatan. Angka total 70 botol sirup kedengeran normal sampai kamu tau 64 di antaranya nyangkut di ruko sebelah.

Kesalahan umum merancang sheet stok gudang

1. Nyatet mutasi sebagai masuk dan keluar biasa. Total pembelian jadi menggelembung dan laporan ke supplier nggak cocok. Mutasi wajib punya sheet sendiri.

2. Nyimpen kolom saldo sebagai angka yang diketik. Saldo harus selalu hasil rumus. Angka yang diketik nggak bisa dicocokin balik ke transaksinya.

3. Satu kolom gudang buat semua sheet, tapi kodenya beda-beda. Gudang Utama, GU, dan G-Utama bakal dihitung sebagai tiga gudang oleh SUMIFS. Pakai satu sumber kode dari Master Lokasi.

4. Ngabaikan batch buat barang yang punya masa berlaku. Tanpa batch, kamu tau ada 70 botol, tapi nggak tau 20 di antaranya lewat tanggal bulan depan.

5. Nyusun daftar opname berdasarkan kode barang. Petugas jadi bolak-balik antar rak dan waktu hitungnya bisa dua kali lipat. Urutkan berdasarkan lokasi.

6. Ngubah angka sistem langsung setelah opname. Penyesuaian wajib jadi baris transaksi baru dengan nomor berita acara. Kalau ditimpa, jejak selisihnya hilang.

7. Nggak pernah ngecek umur stok. Barang yang nggak bergerak nggak bakal ngasih tanda sendiri. Tambah kolom selisih hari dari penerimaan terakhir dan cek tiap bulan.

FAQ

Kenapa mutasi antar gudang harus punya sheet sendiri?

Karena satu perpindahan itu dua kejadian sekaligus: keluar dari gudang asal dan masuk ke gudang tujuan. Kalau dicatat di sheet Masuk dan sheet Keluar, total masuk dan total keluar jadi menggelembung dan angka pembelian nggak bisa dibaca lagi. Sheet Mutasi misahin perpindahan internal dari transaksi sama pihak luar.

Gimana cara nanganin barang dengan tanggal kedaluwarsa di Excel?

Tambah kolom nomor batch dan tanggal kedaluwarsa di sheet Masuk, lalu wajibkan sheet Keluar nyebut batch mana yang diambil. Saldo dihitung per pasangan kode barang dan batch, bukan per kode barang doang. Buat peringatan, pakai rumus selisih antara tanggal kedaluwarsa dan tanggal hari ini, lalu warnai yang tersisa di bawah 30 hari.

Berapa banyak baris transaksi yang masih kuat ditangani Excel?

Batas baris Excel jauh di atas sejuta, tapi batas nyamannya lebih rendah. Dengan rumus SUMIFS di ribuan baris, file mulai terasa berat di sekitar 50 ribu baris transaksi. Kalau udah nyampe situ, pindahin rumus ke Power Query atau Power Pivot, atau arsipkan transaksi tahun lama ke file terpisah.

Perlu nggak kolom lokasi rak kalau gudangnya cuma satu?

Perlu kalau nyari barang di gudang itu makan waktu lebih dari dua menit. Kolom lokasi bukan buat hitungan saldo, tapi buat operasional. Waktu stock opname, petugas bisa jalan urut per rak dan nggak bolak-balik. Kalau gudangmu cuma satu ruangan kecil dengan 20 jenis barang, kolom ini boleh dilewat.

Gimana cara nutup periode di sheet stok gudang?

Jangan hapus transaksi lama. Ganti rumus SUMIFS jadi versi yang punya syarat rentang tanggal, lalu simpan hasil hitung fisik akhir bulan sebagai stok awal periode berikutnya di sheet Opname. Saldo sistem bulan baru dihitung dari stok awal itu ditambah transaksi bulan berjalan. Riwayat lama tetap ada buat penelusuran.

Penutup

Lima tabel, satu rumus SUMIFS bertingkat, dan aturan bahwa saldo nggak pernah diketik. Itu kerangka yang bikin stok gudang bisa ditelusuri sampai ke rak.

Kolom yang paling sering dilewat tapi paling banyak ngasih jawaban: kode gudang dan umur stok. Dua kolom itu yang nunjukin barang mana yang cuma numpang duduk.

Referensi resmi fungsi yang dipakai di atas ada di dokumentasi Microsoft Excel.

Buka file stokmu sekarang dan cek apakah tiap baris transaksi punya kolom gudang. Kalau belum ada, itu satu kolom yang paling cepat balik modal. Buat pencatatan per barang yang lebih rinci, lanjut ke kartu stok barang.