Optimasi Inventori Barang Pakai Spreadsheet

Halo! Di artikel ini kita bakal bahas Optimasi Inventori Barang Pakai Spreadsheetsecara super lengkap, santai, dan gampang dimengerti—cocok buat remaja Indonesia yang pengen ngerti cara ngatur stok pakai Excel atau Google Sheets.

Artikel ini panjang dan rinci: dari konsep dasar inventori, struktur data, contoh tabel nyata yang bisa kamu copy-paste, rumus step-by-step, sampai strategi praktis (EOQ, Safety Stock, Reorder Point, ABC Analysis), dashboard stok, dan latihan praktek.

Siap? Yuk mulai!

Daftar isi (biar gampang lompat)

• Kenapa optimasi inventori penting

• Konsep dasar inventori yang harus kamu tahu

• Struktur spreadsheet yang rapi (sheet yang direkomendasikan)

• Contoh data nyata: master barang + transaksi stok

• Helper column & format yang wajib

• Dasar-dasar rumus: SUMIFS, COUNTIFS, VLOOKUP/XLOOKUP, UNIQUE, FILTER

• Menghitung stok aktual: masuk - keluar

• Safety Stock, Lead Time, Reorder Point: rumus + contoh

• EOQ (Economic Order Quantity): teori & contoh hitungan

• Inventory Turnover & Days Inventory Outstanding (DIO)

• ABC Analysis: cara praktis dan contoh tabel

• FIFO vs LIFO (dasar penerapan di spreadsheet)

• Menangani retur, stok rusak, dan stock adjustment

• Membuat notifikasi reorder otomatis (pakai conditional formatting & formula)

• Dashboard stok sederhana & KPI inventori

• Otomasi: Named ranges, Tables, ARRAYFORMULA, QUERY

• Tips UX & best practice supaya spreadsheet tidy

• Latihan praktis lengkap (soal + jawaban langkah-per-langkah)

• Kesalahan umum & cara menghindarinya

• Checklist implementasi inventory management dengan spreadsheet

• Glosarium istilah penting

Kenapa optimasi inventori penting?

Singkatnya: terlalu banyak stok = modal nganggur; terlalu sedikit stok = kehilangan penjualan dan pelanggan. Optimasi inventori bantu kamu:

• Menjaga arus kas sehat

• Mengurangi biaya penyimpanan

• Menghindari stockout

• Mengetahui produk yang cepat laku atau slow-moving

• Memutuskan kapan harus reorder dan berapa jumlah pesan

Spreadsheet (Excel/Google Sheets) adalah alat paling accessible buat pemilik usaha kecil/belajar manajemen inventori. Dengan struktur dan rumus yang tepat, kamu bisa dapat insight berguna tanpa software mahal.

Kata kunci SEO yang relevan akan muncul alami:optimasi inventori,manajemen stok,reorder point,safety stock,EOQ, inventory spreadsheet,excel inventory.

Konsep dasar inventori yang wajib kamu tahu

Sebelum masuk rumus, paham dulu istilah ini:

• Stock on Hand / Stok Aktual: jumlah unit fisik yang tersedia sekarang.

• Reorder Point (ROP): level stok yang memicu pemesanan ulang.

• Safety Stock: stok pengaman untuk mengantisipasi variabilitas permintaan atau lead time.

• Lead Time: waktu dari pemesanan sampai barang datang.

• Economic Order Quantity (EOQ): kuantitas pemesanan paling efisien untuk meminimalkan total biaya.

• Inventory Turnover: seberapa cepat stok berputar dalam periode tertentu.

• Days Inventory Outstanding (DIO): rata-rata hari stok bertahan sebelum terjual.

• ABC Analysis: pengelompokan produk berdasarkan nilai penjualan (A=paling penting, C=kurang penting).

• FIFO / LIFO: metode penilaian stok (First In First Out / Last In First Out).

Kalau paham konsep ini, kita bisa terapkan di spreadsheet.

Struktur spreadsheet yang rapi (sheet yang direkomendasikan)

Buat file spreadsheet dengan sheet terpisah supaya rapi:

1. Products / Master_Items — daftar semua SKU, deskripsi, satuan, cost, price, category.

2. Inventory_Transactions — log setiap gerakan stok (in, out, adjustment, return).

3. Stock_Balance— laporan stok aktual per SKU (bisa dihitung otomatis dari `Inventory_Transactions`).

4. Suppliers — daftar supplier + lead time typical + contact.

5. Reorder_Planner — kalkulasi ROP, Safety Stock, EOQ, rekomendasi reorder.

6. ABC_Analysis — tabel hasil analisis ABC.

7. Dashboard — KPI stok, grafik, daftar produk yang butuh reorder.

8. Archive / Backup — cadangan data bulanan (opsional).

Alasan: pemisahan input (Inventory_Transactions) dan kalkulasi (Stock_Balance, Reorder_Planner) membuat sistem lebih aman dan mudah audit.

Contoh data nyata: master barang + transaksi stok

Berikut contoh tabel untuk `Master_Items` dan `Inventory_Transactions` yang bisa langsung kamu copy-paste.

Sheet: Master_Items

Sheet: Suppliers

Sheet: Inventory_Transactions

Kolom: TransactionID, Date, SKU, Type (IN/OUT/ADJUSTMENT/RETURN), Quantity, Reference (purchase/order/return), Note

Catatan: `IN` berarti masuk (purchase, return in), `OUT` berarti keluar (sales, transfer out), `ADJUSTMENT` bisa negatif/positif untuk koreksi stok.

Helper column & format yang wajib

Supaya rumus gampang, tambahkan beberapa helper column di `Inventory_Transactions`:

• `YearMonth` = `=TEXT(B2,"yyyy-mm")` → untuk ringkasan bulanan.

• `SignQty` = kalau Type = IN -> Quantity, Type = OUT -> -Quantity, ADJUSTMENT -> Quantity (positif atau negatif tergantung input).

Bisa pakai rumus:

excel
  =IF(D2="IN", E2, IF(D2="OUT", -E2, E2))

• `RunningBalance` (opsional) jika mau lihat per-SKU history berurutan. Diurutkan by Date.

Format kolom:

• Date → Date format

• Quantity → Number, 0 decimal

• Cost/Price → Currency (Rp)

Rumus dasar yang sering dipakai

Beberapa rumus yang akan sering muncul:

• `SUMIFS(range_sum, criteria_range1, criteria1, ...)` — jumlah bersyarat

• `COUNTIFS(...)` — hitung baris bersyarat

• `VLOOKUP` / `XLOOKUP` / `INDEX-MATCH` — cari data master (supplier, cost)

• `UNIQUE`, `SORT` (Google Sheets / Excel terbaru) — buat daftar SKU otomatis

• `FILTER` / `QUERY` (Google Sheets) — ambil subset data

• `IF`, `IFERROR`, `MAX`, `MIN` — logika dan penanganan error

Kita akan pakai SUMIFS banyak kali untuk menghitung stok.

Menghitung stok aktual: masuk - keluar

Intinya: stok aktual per SKU adalah penjumlahan semua `SignQty` pada SKU itu.

Contoh: buat sheet `Stock_Balance` dengan kolom SKU, ProductName, TotalIn, TotalOut, Adjustments, StockOnHand.

Misal letak data `Inventory_Transactions`:

• SKU di kolom C

• SignQty di kolom H

Rumus contoh (Excel/Google Sheets):

Di `Stock_Balance` cell B2 (ProductName) gunakan XLOOKUP:

excel
=XLOOKUP(A2, Master_Items!A:A, Master_Items!B:B, "Not Found")

Di C2 (TotalIn):

excel
=SUMIFS(Inventory_Transactions!$E:$E, Inventory_Transactions!$C:$C, $A2, Inventory_Transactions!$D:$D, "IN")

Di D2 (TotalOut):

excel
=SUMIFS(Inventory_Transactions!$E:$E, Inventory_Transactions!$C:$C, $A2, Inventory_Transactions!$D:$D, "OUT")

Atau lebih simpel (pakai SignQty):

excel
=SUMIFS(Inventory_Transactions!$H:$H, Inventory_Transactions!$C:$C, $A2)

Ini langsung memberikan net stock (IN positive, OUT negative, ADJUST positive/negative).

Contoh hasil (dari sample transaksi):

Perhitungan: P001: 100 IN (T0001) -10 (T0002) -20 (T0005) -5 adjustment (T0009) = 65.

Safety Stock, Lead Time, Reorder Point: rumus + contoh

Ini bagian penting untuk optimasi.

1) Lead Time (LT)

Dapat dari `Suppliers` sheet, misal S001 punya LT = 7 hari.

2) Demand Rate (DLT) — rata-rata pemakaian per hari

Hitung periode tertentu (misal 30 hari terakhir):

excel
=SUMIFS(Inventory_Transactions!$E:$E, Inventory_Transactions!$C:$C, $A2, Inventory_Transactions!$D:$D, "OUT", Inventory_Transactions!$B:$B, ">=2025-08-01", Inventory_Transactions!$B:$B, "<=2025-08-31")

Lalu dibagi jumlah hari periode.

Simpler: gunakan total out per bulan / days_in_month.

3) Safety Stock (SS)

Banyak metode, kita pakai dua yang umum:

• Metode sederhana (variabilitas permintaan):

SS = Z * σLT

Di mana Z = z-score service level (misal 95% ~ 1.65), σLT = std dev of demand during lead time.

• Metode praktis (sederhana):

SS = (Max daily usage * Max lead time) - (Average daily usage * Average lead time)

(berguna jika data variatif dan kamu mau angka aman)

Untuk tutorial ini, kita pakai pendekatan sederhana berbasis deviasi permintaan:

Langkah:

1. Hitung daily usage (OUT) per hari untuk SKU.

2. Hitung standard deviation of daily usage (σ).

3. σLT = σ * SQRT(LeadTime)

4. SS = Z * σLT

Contoh:

• Average daily usage = 2 unit/day

• σ daily = 1.5 unit/day

• LeadTime = 7 days

• Z (service level 95%) = 1.65

• σLT = 1.5 * sqrt(7) ≈ 1.5 * 2.646 = 3.97

• SS = 1.65 * 3.97 ≈ 6.55 → bulatkan 7 unit

4) Reorder Point (ROP)

ROP = (Average daily usage * LeadTime) + SafetyStock

Contoh:

• Avg daily = 2 * 7 = 14

• SS = 7

• ROP = 21 unit

Artinya: jika stok turun ke 21, kamu sebaiknya pesan ulang.

Implementasi rumus di spreadsheet

Di `Reorder_Planner` buat kolom: SKU, AvgDailyUsage, StdDevDaily, LeadTime, SafetyStock, ROP, StockOnHand, ReorderQtySuggested.

Contoh rumus:

AvgDailyUsage (periode 30 hari):

excel
=SUMIFS(Inventory_Transactions!E:E, Inventory_Transactions!C:C, $A2, Inventory_Transactions!D:D, "OUT", Inventory_Transactions!B:B, ">=2025-08-01", Inventory_Transactions!B:B, "<=2025-08-30") / 30

StdDevDaily (gunakan daily OUT per day array and STDEV.S)

SafetyStock:

excel
=Z * StdDevDaily * SQRT(LeadTime)

(Z bisa kamu simpan di cell global, misal 1.65)

ROP:

excel
=AvgDailyUsage * LeadTime + SafetyStock

ReorderQtySuggested: bisa gunakan EOQ (next section) atau reorder to a target level (TargetStock - OnHand).

EOQ (Economic Order Quantity): teori & contoh hitungan

EOQ adalah rumus klasik untuk menentukan berapa banyak harus dipesan tiap kali agar total biaya (holding + ordering) minimal.

Rumus:

EOQ = sqrt( (2 * D * S) / H )

Di mana:

• D = demand per periode (unit/year)

• S = ordering cost per order (biaya pemesanan)

• H = holding cost per unit per period (cost untuk menyimpan satu unit selama satu periode)

Contoh:

• D = 2.400 units/year (200 per bulan)

• S = Rp 50.000 per order

• H = holding cost = unit cost * holding rate (misal cost 25.000 * 20% = 5.000 per year)

EOQ:

EOQ = sqrt( (2 * 2400 * 50000) / 5000 ) = sqrt( (240,000,000) / 5000 ) = sqrt(48,000) ≈ 219 units

Interpretasi: lebih hemat memesan sekitar 219 unit per order.

Menaruh rumus EOQ di spreadsheet

Di `Reorder_Planner` tiap SKU:

• D (annual demand) bisa = AvgDailyUsage * 365

• S = biaya pesan (masukkan default global)

• H = CostPerUnit * HoldingRate (masukkan HoldingRate global, misal 0.2 = 20%)

Rumus EOQ:

excel
=SQRT( (2 * D * S) / H )

Setelah EOQ, kamu bisa tentukan reorder quantity = EOQ, atau kalau supplier punya MOQ (minimum order), pilih max(EOQ, MOQ).

Inventory Turnover & Days Inventory Outstanding (DIO)

Inventory Turnover (IT)

IT = Cost of Goods Sold (COGS) / Average Inventory

Untuk bisnis sederhana, jika tidak ada COGS terpisah, kamu bisa gunakan Sales Cost atau gunakan cost column pada `Master_Items` diakumulasikan.

Contoh:

• COGS per tahun = Rp 120.000.000

• Average Inventory = (Beginning Inventory + Ending Inventory) / 2 = Rp 10.000.000

• IT = 120,000,000 / 10,000,000 = 12 → stok berputar 12 kali per tahun.

Days Inventory Outstanding (DIO)

DIO = 365 / Inventory Turnover

Contoh: DIO = 365 / 12 ≈ 30.4 hari.

Menaruh ini di `Dashboard` membantu tahu berapa lama stok "diam".

Rumus di spreadsheet:

• COGS per period → from sales * cost per unit (oleh product)

• Average Inventory → (OpeningValue + ClosingValue)/2

• IT = COGS / AverageInventory

• DIO = 365 / IT

ABC Analysis: cara praktis dan contoh tabel

ABC Analysis mengelompokkan SKU berdasarkan nilai kontribusi (biasanya revenue atau margin):

• A = top 70% nilai kumulatif (small % of SKUs but high value)

• B = next 20%

• C = last 10%

Langkah:

1. Hitung annual sales value per SKU: `AnnualUnitsSold * Price`

2. Sort SKU descending by sales value

3. Hitung cumulative value dan cumulative percentage

4. Tentukan kelompok A/B/C sesuai cutoff

Contoh ringkas:

Di spreadsheet, kamu bisa pakai `SORT`, `SUM`, `SUMPRODUCT` atau `QUERY` untuk menghitung dan kategorikan.

Manfaat ABC:

• Fokus kontrol stok dan forecasting pada kelompok A (lebih sering cek, safety stock lebih tinggi)

• Kelola C dengan strategi just-in-time atau dropshipping jika memungkinkan

FIFO vs LIFO (dasar penerapan di spreadsheet)

Untuk valuasi stok, dua metode populer:

• FIFO (First In First Out): barang yang masuk pertama keluar pertama. Umumnya realistis untuk barang fisik (makanan, pakaian).

• LIFO (Last In First Out): barang terakhir masuk keluar pertama. Jarang dipakai secara fisik, lebih untuk akuntansi spesifik.

Di spreadsheet, untuk menghitung nilai persediaan menurut FIFO, kamu perlu log detail penerimaan (IN) per batch: tanggal, qty, cost per unit. Saat ada OUT, alokasikan pengeluaran dari batch tertua dulu.

Contoh sederhana FIFO alokasi:

• Batch1: 100 @ Rp25.000 (2025-09-01)

• Batch2: 50 @ Rp26.000 (2025-09-10)

• OUT 120 unit → 100 dari Batch1, 20 dari Batch2.

Kalkulasi ini bisa diotomasi dengan formula kompleks atau menggunakan Power Query. Untuk pemula, cara sederhana: gunakan kolom `RunningBalance` dan pivot per batch.

Menangani retur, stok rusak, dan stock adjustment

• Retur penjualan: masuk sebagai `IN` (return in) atau dikurangi dari sales dan catat reference. Pastikan koreksi stok dan revenue dipisah.

• Stok rusak/hilang: catat sebagai `ADJUSTMENT` negative dengan note.

• Stock take (opname): lakukan periodik, buat jurnal perbedaan antara sistem dan fisik sebagai `ADJUSTMENT`.

Di spreadsheet, keep `Inventory_Transactions` immutable (append-only). Jangan edit transaksi lama. Kalau perlu koreksi, buat baris adjustment baru.

Membuat notifikasi reorder otomatis (Conditional Formatting & Formula)

Biar gampang tau produk yang perlu dipesan:

1. Di `Stock_Balance` tambahkan kolom `ReorderFlag`:

excel
=IF(StockOnHand <= ROP, "REORDER", "")

2. Pakai Conditional Formatting untuk highlight baris dengan `REORDER`.

3. Buat filter view di `Dashboard` yang menampilkan semua SKU dengan `ReorderFlag = "REORDER"`.

Atau di Google Sheets, pakai formula di sheet `Dashboard`:

gs
=FILTER(Stock_Balance!A2:E, Stock_Balance!E2:E <= Stock_Balance!D2:D)

(Asumsi E=StockOnHand, D=ROP)

Untuk notif via email otomatis butuh script (Google Apps Script) — di luar scope artikel ini, tapi bisa dicatat sebagai next step.

Dashboard stok sederhana & KPI inventori

KPI yang berguna:

• Number of SKUs

• Total Stock Value (sum StockOnHand * Cost)

• Inventory Turnover

• Number of SKUs below ROP

• Top 5 slow-moving SKUs

• Top 5 fast-moving SKUs

Visual:

• Bar chart: Top SKU by stock value

• Pie: Stock composition by category

• Line: Inventory value over time

Contoh widget: KPI card showing `Total Stock Value = SUM(StockOnHand * CostPerUnit)`

Rumus Total Stock Value:

excel
=SUMPRODUCT(Stock_Balance!StockOnHandRange, Master_Items!CostRange)

Pastikan SKU alignment via XLOOKUP or array mapping.

Otomasi: Named ranges, Tables, ARRAYFORMULA, QUERY

Tips automasi praktis:

• Convert `Inventory_Transactions` menjadi Table (Excel) atau gunakan `ARRAYFORMULA` (Google Sheets) supaya kolom helper terisi otomatis saat tambah baris.

• Named ranges: beri nama `InventoryTable`, `MasterItems`, `Suppliers` untuk rumus lebih readable.

• QUERY (Google Sheets) atau Power Query (Excel) untuk transformasi data yang lebih kompleks.

• `ARRAYFORMULA` bisa membuat kolom `YearMonth` otomatis:

gs
=ARRAYFORMULA(IF(A2:A="", "", TEXT(B2:B,"yyyy-mm")))

Tips UX & best practice supaya spreadsheet tidy

• Satu sumber kebenaran: input hanya di `Inventory_Transactions` dan `Master_Items`.

• Protect sheet untuk kalkulasi agar tidak terhapus.

• Header jelas + freeze panes.

• Data validation (dropdown) untuk kolom `Type`, `SKU`, `SupplierID`.

• Format konsisten: tanggal, currency, number.

• Backup rutin: buat snapshot tiap akhir bulan.

• Audit trail: jangan hapus data; buat adjustment record.

• Komentar / instruktsi di sheet supaya tim tahu cara input.

• Gunakan warna: input green, calculation blue, alert red.

Latihan praktis lengkap (soal + jawaban langkah-per-langkah)

Berikut latihan untuk praktek ambil dari contoh data di atas.

Soal 1 — Hitung StockOnHand tiap SKU

Langkah:

1. Pastikan `Inventory_Transactions` berisi sample transaksi.

2. Buat sheet `Stock_Balance` daftar SKU (pakai UNIQUE dari Master_Items).

3. Gunakan rumus:

excel
=SUMIFS(Inventory_Transactions!$H:$H, Inventory_Transactions!$C:$C, $A2)

Jawaban:

• P001 = 65

• P002 = 40

• P003 = 185

• P004 = 30

• P005 = 500

Soal 2 — Hitung ROP untuk P001

Asumsi:

• Avg daily usage = total OUT selama 30 hari / 30

Dari transaksi OUT untuk P001: 10 + 20 = 30 units selama periode 10 hari contoh (disederhanakan).

• Avg daily = 30 / 30 = 1 unit/day

• LeadTime S001 = 7 days

• σ daily (asumsi) = 1 unit

• Z = 1.65

Calculate:

• σLT = 1 * sqrt(7) = 2.646

• SS = 1.65 * 2.646 ≈ 4.37 → bulat 5

• ROP = AvgDaily*LT + SS = 1*7 + 5 = 12 units

Jika StockOnHand = 65 > ROP → tidak reorder.

Soal 3 — EOQ untuk P001

Asumsi:

• Annual demand D = AvgDaily * 365 = 1*365 = 365

• Order cost S = Rp 50.000

• Cost per unit = Rp 25.000, HoldingRate = 20% → H = 25.000 * 0.2 = 5.000

EOQ:

excel
=SQRT((2*365*50000)/5000) = sqrt((36,500,000)/5000) = sqrt(7300) ≈ 85 units

Reorder quantity suggested = 85 units.

Kesalahan umum & cara menghindarinya

• Input ganda / duplicate TransactionID → pastikan TransactionID unik (pakai timestamp).

• Typo SKU → pakai dropdown dari Master_Items.

• Mengedit transaksi lama → jangan hapus, buat adjustment instead.

• Menggunakan teks untuk angka → pastikan numeric type.

• Data tidak konsisten (tanggal format berbeda) → gunakan format standar ISO (yyyy-mm-dd).

• Over-reliance on volatile functions (OFFSET, INDIRECT) → gunakan Tables / structured references.

Checklist implementasi inventory management dengan spreadsheet

• [ ] Buat sheet `Master_Items` lengkap (SKU, cost, price, supplier)

• [ ] Buat sheet `Suppliers` dengan lead time

• [ ] Gunakan `Inventory_Transactions` sebagai satu-satunya tempat input gerakan stok

• [ ] Tambahkan helper column `SignQty`, `YearMonth`

• [ ] Buat `Stock_Balance` otomatis pakai SUMIFS/SignQty

• [ ] Hitung AvgDailyUsage, StdDevDaily per SKU

• [ ] Tentukan SafetyStock, ROP, dan EOQ per SKU

• [ ] Setup `Reorder_Planner` dengan flag REORDER

• [ ] Buat Dashboard untuk SKU yang butuh pemesanan

• [ ] Setting Data Validation untuk kolom yang sensitif

• [ ] Backup bulanan & proteksi sheet perhitungan

Glosarium istilah penting

• SKU: Stock Keeping Unit, kode unik produk.

• Safety Stock: stok pengaman.

• Lead Time: waktu antar pemesanan dan penerimaan barang.

• ROP: Reorder Point, level stok pemicu reorder.

• EOQ: Economic Order Quantity.

• Inventory Turnover: rasio COGS/AverageInventory.

• DIO: Days Inventory Outstanding.

• ABC Analysis: pengelompokan produk berdasarkan nilai.

• FIFO/LIFO: metode alokasi stok.

Optimasi inventori pakai spreadsheet itu bukan sulap — ini soal konsistensi input, struktur data yang rapi, dan pemilihan rumus yang tepat.

Mulai dari daftar master items dan log transaksi yang bersih, lalu tambahkan kalkulasi ROP, Safety Stock, dan EOQ.

Buat notifikasi sederhana dengan conditional formatting, dan dashboard untuk memonitor.

Kalau udah jalan, kamu bisa scale dengan otomatisasi lebih lanjut (Apps Script / Power Query) atau pindah ke software inventory ketika bisnis berkembang.

Selamat praktek! Semoga spreadsheet-mu jadi sistem inventori yang rapi, efisien, dan membantu usaha kamu tumbuh.