Metode FIFO (First In, First Out) merupakan metode pencatatan yang digunakan dalam mengelola persediaan. Sesuai namanya, FIFO mengasumsikan bahwa barang yang pertama kali dibeli adalah barang yang pertama kali dijual atau digunakan. Artinya, nilai persediaan akhir akan dihitung berdasarkan harga pembelian terbaru.
Metode ini cocok digunakan untuk bisnis yang menjual produk yang mudah basi atau cepat usang, seperti makanan, obat-obatan, atau barang elektronik. Selain itu, FIFO juga mencerminkan arus barang yang logis dan lebih realistis dalam kebanyakan proses distribusi.
Kesulitan Menghitung FIFO dengan Spreadsheet Biasa
Meskipun konsep FIFO terdengar sederhana, menghitungnya secara manual di spreadsheet seperti Excel bisa menjadi tantangan tersendiri, terutama ketika volume transaksi tinggi dan terdiri dari banyak jenis barang. Beberapa kendala umum antara lain:
-
Pengurutan kronologis transaksi masuk dan keluar: Setiap pembelian dan penjualan harus diurutkan berdasarkan tanggal agar urutan FIFO tetap konsisten.
-
Pencocokan jumlah barang yang dijual dengan pembelian yang relevan: Penjualan harus "menghabiskan" pembelian secara berurutan, dan sisa dari pembelian sebelumnya harus dilacak.
-
Kesalahan manusia: Proses manual rawan kesalahan terutama jika tidak menggunakan formula dinamis atau pencatatan terstruktur.
-
Waktu pengerjaan yang lama: Untuk perusahaan dengan ratusan transaksi per bulan, menghitung FIFO secara manual bisa memakan waktu berjam-jam.
Solusi: Menggunakan Macro Excel untuk FIFO
Salah satu solusi efektif untuk mengatasi kompleksitas ini adalah dengan menggunakan Macro (VBA) di Excel. Macro memungkinkan pengguna untuk mengotomatiskan proses pencocokan pembelian dan penjualan berdasarkan logika FIFO.
Scipt yang digunakan adalah sebagai berikut:
Option Base 1 Sub FIFO() ' Dim QtySold() As Long, SKU_TYPE() As String, SalesINV() As String, source() As String, Cost() As Double Dim i As Integer, t As Integer, pending As Integer, matched As Integer, j As Integer, x As Double Dim rngA As Range Dim cell As Range ' www.excel4routine.com ' ZKL 13/04/19 Application.ScreenUpdating = False Worksheets("Inventory").Range("F2:H1000000").Clear Worksheets("Inventory").Range("N2:P1000000").Clear Worksheets("LOG").Range("A2:F1000000").Clear 'if inventory records < 1 row exit sub 'else add remaining column fill down With ActiveSheet If .Cells(.Rows.Count, "A").End(xlUp).Row > 2 Then 'Sort Inventory by Pdt,by Date 'https://trumpexcel.com/sort-data-vba/ With ActiveSheet.Sort .SortFields.Clear ' to clear prior sort data .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SetRange Range("mydata") .Header = xlYes .Apply End With .Range("G2:G" & .Cells(.Rows.Count, "C").End(xlUp).Row).Formula = "=C2-F2" .Range("H2:H" & .Cells(.Rows.Count, "C").End(xlUp).Row).Formula = "=G2*D2" .Range("O2:O" & .Cells(.Rows.Count, "K").End(xlUp).Row).Formula = "=SUMIFs(LOG!F:F,LOG!A:A,K2,LOG!C:C,L2)" End If End With 'Check Availability of stock for those pending insufficient cases Set rngA = ActiveSheet.Range("P2:P" & ActiveSheet.Cells(ActiveSheet.Rows.Count, "P").End(xlUp).Row) t = 0 For Each cell In rngA If cell.Value = "Insufficient Stock" Then If Not WorksheetFunction.SumIf(ActiveSheet.Range("B:B"), ActiveSheet.Range("L" & cell.Row).Value, ActiveSheet.Range("G:G")) < ActiveSheet.Range("M" & cell.Row).Value Then ActiveSheet.Range("N" & cell.Row).Value = ActiveSheet.Range("M" & cell.Row).Value ActiveSheet.Range("P" & cell.Row).ClearContents 'Narrow down the range for SKU lookup 'goto by find Let endrow = Columns("B:B").Find(What:=ActiveSheet.Range("L" & cell.Row).Value, After:=ActiveSheet.Range("B1"), LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= _ False, SearchFormat:=False).Row Let startrow = Columns("B:B").Find(What:=ActiveSheet.Range("L" & cell.Row).Value, After:=ActiveSheet.Range("B1"), LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Row x = ActiveSheet.Range("M" & cell.Row).Value 'Loop through Inventory For i = startrow To endrow With Range("B" & i) If x <> 0 And .Offset(, 5).Value > 0 Then t = t + 1 ReDim Preserve QtySold(t) ReDim Preserve SKU_TYPE(t) 'Range("L" & j).Value ReDim Preserve SalesINV(t) 'Range("K" & j).Value ReDim Preserve source(t) '.Offset(, 3) ReDim Preserve Cost(t) '.Offset(, 2) If .Offset(, 5).Value >= x Then .Offset(, 4) = .Offset(, 4) + x QtySold(t) = x SKU_TYPE(t) = ActiveSheet.Range("L" & cell.Row).Value SalesINV(t) = ActiveSheet.Range("K" & cell.Row).Value source(t) = .Offset(, 3) Cost(t) = .Offset(, 2) x = 0 Else SKU_TYPE(t) = ActiveSheet.Range("L" & cell.Row).Value SalesINV(t) = ActiveSheet.Range("K" & cell.Row).Value source(t) = .Offset(, 3) Cost(t) = .Offset(, 2) QtySold(t) = .Offset(, 5).Value x = x - .Offset(, 5).Value .Offset(, 4) = .Offset(, 4) + .Offset(, 5) End If End If End With Next i End If End If Next cell 'Do a check for new orders pending to be matched comparing the last row of col M & N Let pending = Columns("M:M").Find(What:="*", After:=ActiveSheet.Range("M1"), LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= _ False, SearchFormat:=False).Row Let matched = Columns("N:N").Find(What:="*", After:=ActiveSheet.Range("N1"), LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= _ False, SearchFormat:=False).Row 'Do a check for availability of remaining inventory b4 going on 'Loop through sales order .if stock available proceed to match else just 0 and skip to next iteration For j = matched + 1 To pending If WorksheetFunction.SumIf(ActiveSheet.Range("B:B"), ActiveSheet.Range("L" & j).Value, ActiveSheet.Range("G:G")) < ActiveSheet.Range("M" & j).Value Then Range("N" & j).Value = 0 Range("P" & j).Value = "Stock tidak mencukupi" 'Update those outstanding "insufficient stocks" that are just matched to LOG GoTo NextIteration: Else Range("N" & j).Value = Range("M" & j).Value End If 'Narrow down the range for SKU lookup 'goto by find Let endrow = Columns("B:B").Find(What:=ActiveSheet.Range("L" & j).Value, After:=ActiveSheet.Range("B1"), LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= _ False, SearchFormat:=False).Row Let startrow = Columns("B:B").Find(What:=ActiveSheet.Range("L" & j).Value, After:=ActiveSheet.Range("B1"), LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Row x = ActiveSheet.Range("M" & j).Value 'Loop through Inventory For i = startrow To endrow With Range("B" & i) If x <> 0 And .Offset(, 5).Value > 0 Then t = t + 1 ReDim Preserve QtySold(t) ReDim Preserve SKU_TYPE(t) 'Range("L" & j).Value ReDim Preserve SalesINV(t) 'Range("K" & j).Value ReDim Preserve source(t) '.Offset(, 3) ReDim Preserve Cost(t) '.Offset(, 2) If .Offset(, 5).Value >= x Then .Offset(, 4) = .Offset(, 4) + x QtySold(t) = x SKU_TYPE(t) = ActiveSheet.Range("L" & j).Value SalesINV(t) = ActiveSheet.Range("K" & j).Value source(t) = .Offset(, 3) Cost(t) = .Offset(, 2) x = 0 Else SKU_TYPE(t) = ActiveSheet.Range("L" & j).Value SalesINV(t) = ActiveSheet.Range("K" & j).Value source(t) = .Offset(, 3) Cost(t) = .Offset(, 2) QtySold(t) = .Offset(, 5).Value x = x - .Offset(, 5).Value .Offset(, 4) = .Offset(, 4) + .Offset(, 5) End If End If End With Next i NextIteration: Next j 'UPDATE LOG On Error Resume Next 'http://www.cpearson.com/excel/ArraysAndRanges.aspx 'Could be improved through split function I think....to be explored later Dim Destination As Range Set Destination = LOG.Cells(LOG.Rows.Count, "A").End(xlUp).Offset(1, 0) Set Destination = Destination.Resize(UBound(SalesINV), 1) Destination.Value = Application.Transpose(SalesINV) Set Destination = LOG.Cells(LOG.Rows.Count, "B").End(xlUp).Offset(1, 0) Set Destination = Destination.Resize(UBound(source), 1) Destination.Value = Application.Transpose(source) Set Destination = LOG.Cells(LOG.Rows.Count, "C").End(xlUp).Offset(1, 0) Set Destination = Destination.Resize(UBound(SKU_TYPE), 1) Destination.Value = Application.Transpose(SKU_TYPE) Set Destination = LOG.Cells(LOG.Rows.Count, "D").End(xlUp).Offset(1, 0) Set Destination = Destination.Resize(UBound(QtySold), 1) Destination.Value = Application.Transpose(QtySold) Set Destination = LOG.Cells(LOG.Rows.Count, "E").End(xlUp).Offset(1, 0) Set Destination = Destination.Resize(UBound(Cost), 1) Destination.Value = Application.Transpose(Cost) LOG.Range("F2:F" & LOG.Cells(LOG.Rows.Count, "E").End(xlUp).Row).Formula = "=E2*D2" '''''End If With ActiveSheet .Range("Orders").Value = .Range("Orders").Value .Range("MyData").Value = .Range("MyData").Value End With DoEvents Application.ScreenUpdating = True End Sub
Langkah-langkah penggunaan script tersebut yaitu:
1. Input data persediaan pada kolom dengan warna biru muda
2. Input rincian pengeluaran barang pada kolom dengan warna kuning muda
3. Klik tombol "Click for FIFO Matching"
4. Hasil perhitungan FIFO akan secara otomatis muncul di kolom dengan warna biru tua dan kuning tua.
Bagi anda yang tidak mau pusing dengan script, sudah saya sediakan file yang bisa di download di Macro Excel - Persediaan FIFO.xlsm
Selamat mencoba 😁