Kalkulasi Bill of Materials yang error-prone jadi otomatis dan akurat
Perhitungan kebutuhan bahan baku dari Bill of Materials dilakukan manual setiap siklus produksi, menghasilkan error kalkulasi yang baru terdeteksi saat bahan sudah kurang di gudang. PowerPivot dan DAX mengeliminasi error ini sepenuhnya.
Masalah
Kebutuhan bahan baku dihitung manual dari file Bill of Materials terpisah setiap siklus produksi. Formula yang kompleks dan tidak konsisten antar periode menyebabkan kesalahan kalkulasi yang baru ketahuan saat bahan sudah kurang di gudang.
Rincian Tantangan
- 1BOM, Production Order, dan data stok berada di 3 file Excel terpisah tanpa relasi
- 2Kalkulasi kebutuhan bahan = QtyProduksi × QtyPerUnit harus dihitung per baris secara manual
- 3Tidak ada early warning — kekurangan stok baru ketahuan saat material sudah dibutuhkan di lantai produksi
- 4Setiap kali ada perubahan production order, seluruh kalkulasi harus diulang dari awal
- 5Formula VLOOKUP yang dipakai sebelumnya sering rusak ketika ada baris baru ditambahkan di tabel BOM
Solusi
Template Excel terstruktur dengan PowerPivot dan DAX formula untuk kalkulasi BOM otomatis berdasarkan production order. Pivot Table untuk monitoring stok minimum dan early warning ketika stok mendekati batas aman.
Langkah-Langkah Implementasi
- 1
Buat 3 sheet terpisah: tbl_BOM, tbl_ProductionOrder, tbl_Stok
- 2
Format tiap sheet sebagai Excel Table (Ctrl+T) dengan nama tabel yang jelas
- 3
PowerPivot → Add to Data Model untuk ketiga tabel
- 4
Diagram View: buat relasi tbl_BOM[KodeProduk] → tbl_PO[KodeProduk]
- 5
Buat relasi tbl_BOM[KodeBahan] → tbl_Stok[KodeBahan]
- 6
DAX Measure: Kebutuhan = SUMX(tbl_PO, tbl_PO[QtyProduksi] * RELATED(tbl_BOM[QtyPerUnit]))
- 7
DAX Measure: Kekurangan = MAX(0, [Kebutuhan] - SUM(tbl_Stok[StokTersedia]))
- 8
Pivot Table: Rows = Nama Bahan, Values = Kebutuhan, Stok, Kekurangan
- 9
Conditional formatting: merah jika Kekurangan > 0, kuning jika Stok < Stok Min
- 10
Setiap planning cycle: update sheet tbl_PO saja → semua kalkulasi otomatis refresh
Sebelum vs Sesudah
3 file Excel terpisah tanpa relasi (BOM, Production Order, Stok)
| Kode Produk | Kode Bahan | Nama Bahan | Qty/Unit (kg) | Stok (kg) | Stok Min (kg) |
|---|---|---|---|---|---|
| PRD-001 | BHN-A01 | Tepung Terigu | 0.5 | 800 | 200 |
| PRD-001 | BHN-A02 | Gula Pasir | 0.2 | 350 | 100 |
| PRD-002 | BHN-B01 | Minyak Goreng | 0.3 | 1200 | 300 |
Data Model PowerPivot — kebutuhan & status stok otomatis terhitung
| Kode Bahan | Nama Bahan | Kebutuhan (kg) | Stok Tersedia (kg) | Kekurangan (kg) | Status |
|---|---|---|---|---|---|
| BHN-A01 | Tepung Terigu | 2.500 | 800 | 1.700 | ⚠ KURANG |
| BHN-A02 | Gula Pasir | 1.000 | 350 | 650 | ⚠ KURANG |
| BHN-B01 | Minyak Goreng | 900 | 1.200 | 0 | ✓ AMAN |
Hasil
4 jam → 15 menit
Per siklus planning, error kalkulasi turun ke nol