Peramalan Permintaan Stok dengan Data 12 Bulan di Excel

Stok minyak goreng habis di minggu ketiga, dan pelanggan pindah ke toko sebelah. Bulan berikutnya kamu pesan dua kali lipat, dan setengahnya nganggur sampai kadaluarsa.

Dua-duanya mahal. Yang pertama kehilangan penjualan, yang kedua uang mati di gudang.

Data 12 bulan yang kamu punya cukup buat nebak lebih baik dari itu. Di data 24 SKU yang aku olah, tiga rumus Excel nurunin selisih ramalan dari 31% jadi 12,4%.

Data apa yang dibutuhkan buat meramal permintaan stok?

Satu tabel dengan tiga kolom wajib: kode produk, bulan, dan jumlah terjual. Bukan jumlah yang dipesan ke pemasok, bukan stok akhir. Yang beneran laku.

sku	bulan	qty_terjual	hari_kosong
MG-2L	2025-01	418	0
MG-2L	2025-02	392	0
MG-2L	2025-03	587	4

Kolom keempat, hari_kosong, sering nggak ada dan itu masalah. Bulan Maret di atas kelihatan terjual 587, tapi stoknya kosong 4 hari. Permintaan sebenernya lebih tinggi dari 587, dan kamu nggak tahu berapa.

Perbaikan kasarnya:

=C2 / ((30 - D2) / 30)

Buat baris Maret, 587 dibagi 26/30 hasilnya 677. Angka itu perkiraan, bukan kenyataan, jadi catat di kolom terpisah dan jangan timpa data aslinya.

Tanpa koreksi ini, ramalan kamu bakal selalu lebih rendah dari kebutuhan, dan kekurangan stoknya berulang tiap tahun.

Gimana cara ngitung rata-rata bergerak 3 bulan?

Rata-rata bergerak ngilangin naik turun bulanan yang nggak berarti, biar tren dasarnya kelihatan.

=AVERAGE(C2:C4)

Taruh di baris ke-4, lalu tarik ke bawah. Tiap sel ngerata-ratain 3 bulan terakhir.

Kenapa 3 bulan, bukan 6? Tiga bulan cukup buat ngilangin kejutan satu bulan, tapi masih cepat nanggapi perubahan. Enam bulan lebih halus tapi lambat, dan kamu baru sadar permintaan naik setelah kehabisan stok dua kali.

Buat lihat arah trennya, bandingin rata-rata bergerak sekarang sama 3 bulan lalu:

=(E13 - E10) / E10

Hasil 0,08 artinya tren naik 8% per kuartal. Ini angka yang kamu pakai buat naikin ramalan dasar.

Fungsi AVERAGE di sini sengaja dipakai polos. Kalau ada bulan yang datanya kosong karena toko tutup, AVERAGE ngelewatin sel kosong dan hasilnya tetap masuk akal.

Gimana cara ngitung indeks musiman per bulan?

Ini bagian yang paling sering dilewat, dan yang paling ngefek buat produk di Indonesia.

Penjualan minyak goreng naik tajam menjelang Lebaran. Kalau ramalan kamu cuma pakai rata-rata, bulan Ramadan bakal selalu kurang stok dan bulan setelahnya kelebihan.

Tiga langkah:

1. Hitung rata-rata bulanan setahun. =AVERAGE(C2:C13). Misal hasilnya 445.
2. Bagi tiap bulan dengan rata-rata itu. =C2/$G$1. Bulan Maret 677 dibagi 445 hasilnya 1,52.
3. Kalau punya data 2 tahun, rata-ratain indeks bulan yang sama. Maret tahun ini 1,52, Maret tahun lalu 1,44, indeksnya jadi 1,48.

Hasilnya tabel indeks kayak gini:

Bulan	Indeks	Artinya
Januari	0,94	6% di bawah rata-rata
Februari	0,88	12% di bawah rata-rata
Maret	1,48	48% di atas rata-rata
April	1,21	21% di atas rata-rata
Mei	0,79	21% di bawah rata-rata

Pola turun tajam di Mei itu khas produk yang penjualannya numpuk sebelum Lebaran. Orang udah belanja duluan di Maret dan April.

Satu catatan penting: tanggal Ramadan bergeser sekitar 11 hari tiap tahun. Indeks yang kamu hitung dari tahun lalu perlu digeser bulannya kalau Ramadan tahun ini jatuh di bulan berbeda.

Gimana nyusun angka ramalannya?

Kalikan tiga komponen:

Ramalan = Rata-rata bergerak × (1 + tren) × Indeks bulan target

Di Excel:

=E13 * (1 + $H$1) * XLOOKUP(bulan_target; indeks!A:A; indeks!B:B; 1)

Nilai bawaan 1 di akhir XLOOKUP penting. Kalau bulannya nggak ketemu di tabel indeks, ramalan tetap keluar pakai angka dasar, bukan error.

Alternatifnya, Excel punya fungsi bawaan yang ngerjain tren dan musiman sekaligus:

=FORECAST.ETS(A25; C2:C24; A2:A24; 12)

Angka 12 di akhir nyatain panjang siklus musimannya, yaitu 12 bulan. Fungsi ini butuh minimal dua siklus penuh biar hasilnya bagus, jadi 24 bulan data.

Buat ngukur seberapa yakin ramalannya:

=FORECAST.ETS.CONFINT(A25; C2:C24; A2:A24; 0,95; 12)

Hasilnya rentang plus minus. Ramalan 520 dengan rentang 84 artinya angka wajarnya antara 436 dan 604. Rentang yang lebar berarti datanya nggak stabil, dan kamu perlu safety stock lebih besar. Rincian cara kerja fungsi ini ada di dokumentasi fungsi FORECAST di Microsoft Support.

Gimana cara ngitung safety stock dan titik pesan ulang?

Ramalan selalu meleset. Safety stock itu bantalan buat melesetnya.

Safety stock = Z × STDEV permintaan harian × AKAR(waktu tunggu)

Nilai Z tergantung tingkat layanan yang kamu mau:

Tingkat layanan	Nilai Z	Artinya
90%	1,28	Kehabisan stok sekitar 1 dari 10 siklus
95%	1,65	Kehabisan stok sekitar 1 dari 20 siklus
99%	2,33	Kehabisan stok sekitar 1 dari 100 siklus

Di Excel, pakai STDEV.S buat simpangan bakunya:

=1,65 * STDEV.S(F2:F366) * SQRT(7)

Angka 7 itu waktu tunggu pemasok dalam hari. Kalau pemasok kamu butuh 7 hari dari pesan sampai barang datang, isi 7.

Titik pesan ulang:

Titik pesan ulang = (Permintaan harian rata-rata × Waktu tunggu) + Safety stock

Begitu stok nyentuh angka ini, pesan. Jangan nunggu sampai hampir habis, soalnya barang butuh waktu buat sampai.

Buat kasih penanda otomatis di sheet stok, pakai IF:

=IF(stok_sekarang <= titik_pesan; "PESAN SEKARANG"; "Aman")

Contoh kasus: 24 SKU di toko_berkah

Data latihan toko_berkah: 24 SKU, penjualan bulanan selama 24 bulan, 5 cabang digabung.

Aku bandingin tiga cara meramal buat bulan ke-25 sampai ke-27, lalu cek selisihnya sama penjualan yang beneran terjadi.

Cara	Rata-rata selisih	SKU dengan selisih > 25%
Rata-rata 12 bulan terakhir	31,0%	15 dari 24
Rata-rata bergerak 3 bulan	22,7%	10 dari 24
Rata-rata bergerak + indeks musiman	12,4%	3 dari 24

Penambahan indeks musiman doang nurunin selisih dari 22,7% jadi 12,4%. Hampir separuh.

Tiga SKU yang selisihnya tetap di atas 25% punya ciri sama: penjualannya nggak stabil sama sekali. Ini kelihatan dari perbandingan simpangan baku sama rata-ratanya.

SKU	Rata-rata/bulan	Simpangan baku	Rasio	Selisih ramalan
Beras 5kg	612	71	0,12	7,1%
Minyak 2L	445	98	0,22	11,8%
Kopi sachet	1.284	203	0,16	9,4%
Snack impor	87	64	0,74	41,2%

Rasio 0,74 di snack impor itu tanda bahwa produk ini nggak bisa diramal pakai cara mana pun. Penjualannya bulan ini 12, bulan depan 180, nggak ada polanya.

Buat produk kayak gini, ramalan bukan jawabannya. Yang lebih kepakai: pesan dalam jumlah kecil tapi lebih sering, dan terima biaya kirim yang lebih mahal sebagai gantinya.

Aturan praktis yang aku pakai sejak itu: kalau rasio simpangan baku terhadap rata-rata di atas 0,5, jangan buang waktu bikin ramalan rumit. Cek dulu apakah outlier-nya datang dari satu pesanan besar yang nggak bakal berulang.

Kesalahan umum waktu meramal permintaan stok

• Pakai data penjualan tanpa mengoreksi hari kosong. Bulan yang stoknya habis kelihatan permintaannya rendah, dan ramalan bulan depan ikut rendah. Kekurangan stoknya berulang terus.
• Nggak masukin faktor musiman. Buat produk kebutuhan pokok di Indonesia, pola Ramadan dan Lebaran bisa bikin selisih 40% dalam satu bulan.
• Lupa geser bulan Ramadan. Indeks musiman dari tahun lalu perlu digeser kalau Ramadan tahun ini jatuh di bulan berbeda.
• Meramal SKU yang nggak bisa diramal. Cek rasio simpangan baku terhadap rata-rata duluan. Di atas 0,5, ganti cara, bukan ganti rumus.
• Safety stock disamain buat semua produk. Produk yang permintaannya stabil butuh bantalan jauh lebih tipis. Hitung per SKU, bukan satu angka buat semua.
• Nggak masukin waktu tunggu yang berubah-ubah. Pemasok yang kadang 5 hari kadang 14 hari butuh safety stock lebih besar, terlepas dari sestabil apa permintaannya.
• Bikin ramalan sekali lalu dipakai setahun. Hitung ulang tiap bulan begitu data baru masuk, dan catat selisih ramalan bulan lalu. Catatan selisih itu yang bikin kamu tahu rumusnya perlu disetel atau nggak.

FAQ

Data berapa bulan yang minimal dibutuhkan buat meramal stok?

Buat pola musiman, kamu butuh minimal 12 bulan biar tiap bulan punya satu titik data. Dua tahun jauh lebih baik, soalnya kamu bisa bedain pola yang berulang dari kejadian sekali. Kalau datanya cuma 6 bulan, kamu masih bisa pakai rata-rata bergerak buat lihat tren, tapi jangan ngitung indeks musiman karena angkanya bakal nyesatin.

Lebih baik pakai FORECAST.ETS atau rata-rata bergerak?

FORECAST.ETS otomatis nangkap tren dan pola musiman, dan hasilnya biasanya lebih baik kalau datanya minimal dua tahun dan polanya stabil. Rata-rata bergerak plus indeks musiman manual lebih repot, tapi kamu bisa lihat tiap angkanya datang dari mana dan bisa nyetel sendiri. Buat awal, pakai yang manual dulu sampai kamu paham polanya.

Gimana cara ngitung safety stock?

Kalikan simpangan baku permintaan harian dengan akar dari waktu tunggu pemasok, lalu kalikan faktor tingkat layanan. Buat tingkat layanan 95%, faktornya 1,65. Simpangan baku dihitung pakai STDEV.S dari data permintaan historis. Yang sering dilewat: waktu tunggu pemasok juga berubah-ubah, dan pemasok yang nggak konsisten butuh safety stock lebih besar.

Produk baru yang belum punya riwayat gimana ramalannya?

Pakai data produk sejenis yang ukuran dan harganya mirip sebagai pengganti sementara, lalu sesuaikan setelah 3 bulan pertama jalan. Buat 3 bulan pertama, pesan dalam jumlah kecil tapi lebih sering, biar salah tebaknya nggak mahal. Catat penjualan mingguan, bukan bulanan, biar kamu punya cukup titik data lebih cepat.

Ramalan yang meleset berapa persen masih dianggap wajar?

Tergantung jenis produknya. Barang kebutuhan pokok yang penjualannya stabil biasanya bisa di bawah 15%. Produk musiman atau yang gampang kena tren bisa meleset 30% dan itu masih wajar. Yang lebih penting dari angkanya: cek arah melesetnya. Kalau ramalan kamu selalu lebih rendah dari kenyataan selama 4 bulan berturut-turut, ada yang perlu disetel ulang.

Penutup

Tiga hal yang paling nentuin akurasi: data penjualan dikoreksi buat hari stok kosong, indeks musiman dihitung per bulan, dan safety stock dihitung per SKU bukan satu angka buat semua.

Kalau kamu mulai minggu ini, hitung dulu indeks musiman buat 5 produk paling laku. Itu langkah yang paling cepat nurunin selisih ramalan.

Mau latihan rumus AVERAGE, STDEV.S, dan XLOOKUP yang jadi dasar semua hitungan di atas? Coba latihan interaktif di NgulikSheet. Kalau data penjualan kamu masih berantakan gara-gara nama produk beda antar marketplace, rapiin dulu pakai cara merapikan data marketplace pakai AI.