EasyDoesIt
New Member
- Joined
- Mar 5, 2023
- Messages
- 4
- Office Version
- 2010
- Platform
- Windows
Hi,
I would like to ask for some help with a FIFO cost calculator for tax purposes that uses earliest stocks bought first at those prices. No averaging, only real prices paid to be used. I have found a few excel sheets with VBA online and seen a few in here, but none of them tick all the boxes. For example, some only deal with one type of stock / item, others average, others can only keep track of the prices paid in the last two batches bought. So if one buys batches of 30 x 3 at different prices, it cannot use the price of the first batch bought. There is one I found that does all I need, but it is not giving consistent results each time. I have tried to fiddle with variables and redim, but I know almost nothing about this, so cannot tweak it. Even closing excel and the VBA editor does not reset whatever is happening. Each time the COGS get bigger and bigger,
An example of what the macro should do:
So one might buy 50 then another 55, before selling 40. When the 40 is sold, it is costed at the per unit price of the original 50. 10 remains of that 50.
When another 30 is sold, it is costed at 10 (remainder after previous sale) at the price paid for the 50, and the remaining 20 at the price paid for the 55. 35 remain in the second lot bought (on hand), ready for the next sale.
I also need the on hand values of all items at the end, which the sheet is doing correctly.
The code also sorts the purchases into items, then dates, so all of the same item purchases are grouped together. That is happening correctly.
It calculates quantity sold (QTY_out), leftover / on hand (Remaining_Qty) and remaining value of on hand (Remaining_Valuation) correctly.
The only field that is incorrect is column O, COGS (FIFO). To re-run, I delete all the data in the yellow blocks and press the button again. If I look at individual lines, every COGS value is wrong and it increases with each run. I would really appreciate if someone can tell me what is going wrong please. I have put the code in the VBA editor as well as a mini-sheet below. Thank you.
I would like to ask for some help with a FIFO cost calculator for tax purposes that uses earliest stocks bought first at those prices. No averaging, only real prices paid to be used. I have found a few excel sheets with VBA online and seen a few in here, but none of them tick all the boxes. For example, some only deal with one type of stock / item, others average, others can only keep track of the prices paid in the last two batches bought. So if one buys batches of 30 x 3 at different prices, it cannot use the price of the first batch bought. There is one I found that does all I need, but it is not giving consistent results each time. I have tried to fiddle with variables and redim, but I know almost nothing about this, so cannot tweak it. Even closing excel and the VBA editor does not reset whatever is happening. Each time the COGS get bigger and bigger,
An example of what the macro should do:
So one might buy 50 then another 55, before selling 40. When the 40 is sold, it is costed at the per unit price of the original 50. 10 remains of that 50.
When another 30 is sold, it is costed at 10 (remainder after previous sale) at the price paid for the 50, and the remaining 20 at the price paid for the 55. 35 remain in the second lot bought (on hand), ready for the next sale.
I also need the on hand values of all items at the end, which the sheet is doing correctly.
The code also sorts the purchases into items, then dates, so all of the same item purchases are grouped together. That is happening correctly.
It calculates quantity sold (QTY_out), leftover / on hand (Remaining_Qty) and remaining value of on hand (Remaining_Valuation) correctly.
The only field that is incorrect is column O, COGS (FIFO). To re-run, I delete all the data in the yellow blocks and press the button again. If I look at individual lines, every COGS value is wrong and it increases with each run. I would really appreciate if someone can tell me what is going wrong please. I have put the code in the VBA editor as well as a mini-sheet below. Thank you.
VBA Code:
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
'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 = "Insufficient Stock" '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| FIFO_Inventory2Cogs.xlsm | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
| 1 | Date | SKU | Qty_IN | Price | Inv/PO | QTY_OUT | Remaining_Qty | Remaining_Valuation | Sales Invoice | SKU | QTY ORDER | MATCHED | COGS (FIFO) | REMARKS | |||||||
| 2 | 1/01/2016 | A | 100 | 1.20 | PO1 | 100 | 0 | - | Invoice 1 | A | 420 | 420 | 25108 | ||||||||
| 3 | 1/02/2016 | A | 100 | 1.30 | PO2 | 100 | 0 | - | Invoice 2 | A | 280 | 280 | 27534 | ||||||||
| 4 | 1/03/2016 | A | 100 | 1.20 | PO3 | 100 | 0 | - | Invoice 3 | B | 500 | 500 | 16400 | ||||||||
| 5 | 1/04/2016 | A | 200 | 1.25 | PO4 | 200 | 0 | - | Invoice 4 | A | 600 | 600 | 10319.5 | ||||||||
| 6 | 1/05/2016 | A | 200 | 1.33 | PO5 | 200 | 0 | - | Invoice 5 | C | 250 | 250 | 16380 | ||||||||
| 7 | 1/06/2016 | A | 100 | 1.40 | PO6 | 100 | 0 | - | Invoice 6 | A | 300 | 300 | 7609.5 | ||||||||
| 8 | 1/07/2016 | A | 150 | 1.44 | PO7 | 150 | 0 | - | Invoice 7 | B | 300 | 300 | 13640 | ||||||||
| 9 | 1/01/2017 | A | 200 | 1.46 | PO8 | 200 | 0 | - | Invoice 8 | B | 400 | 400 | 12300 | ||||||||
| 10 | 1/02/2017 | A | 200 | 1.45 | PO9 | 200 | 0 | - | Invoice 9 | C | 5 | 5 | 210 | ||||||||
| 11 | 1/03/2017 | A | 200 | 1.50 | PO10 | 200 | 0 | - | Invoice 10 | B | 300 | 300 | 13380 | ||||||||
| 12 | 1/04/2017 | A | 200 | 1.55 | PO11 | 50 | 150 | 232.50 | Invoice 11 | B | 3 | 3 | 145.8 | ||||||||
| 13 | 1/05/2017 | A | 250 | 1.60 | PO12 | 250 | 400.00 | Invoice 12 | B | 4 | 4 | 194.4 | |||||||||
| 14 | 1/06/2017 | A | 150 | 1.55 | PO13 | Invoice 13 | B | 5 | |||||||||||||
| 15 | 1/07/2017 | A | 150 | 1.55 | PO14 | Invoice 14 | B | 6 | |||||||||||||
| 16 | 1/08/2017 | A | 150 | 1.50 | PO15 | Invoice 15 | B | 7 | |||||||||||||
| 17 | 1/09/2017 | A | 150 | 1.48 | PO16 | ||||||||||||||||
| 18 | 1/10/2017 | A | 150 | 1.38 | PO17 | ||||||||||||||||
| 19 | 1/11/2017 | A | 150 | 1.40 | PO18 | ||||||||||||||||
| 20 | 1/12/2017 | A | 150 | 1.50 | PO19 | ||||||||||||||||
| 21 | 6/06/2018 | A | 300 | 3 | PO35 | ||||||||||||||||
| 22 | 1/08/2016 | B | 200 | 2.00 | PO20 | ||||||||||||||||
| 23 | 1/09/2016 | B | 200 | 2.10 | PO21 | ||||||||||||||||
| 24 | 1/10/2016 | B | 200 | 2.00 | PO22 | ||||||||||||||||
| 25 | 1/11/2016 | B | 200 | 2.00 | PO23 | ||||||||||||||||
| 26 | 1/12/2016 | B | 200 | 2.00 | PO24 | ||||||||||||||||
| 27 | 1/01/2018 | B | 200 | 2.10 | PO25 | ||||||||||||||||
| 28 | 1/02/2018 | B | 200 | 2.20 | PO26 | ||||||||||||||||
| 29 | 1/03/2018 | B | 200 | 2.00 | PO27 | ||||||||||||||||
| 30 | 1/04/2018 | B | 200 | 2.50 | PO28 | ||||||||||||||||
| 31 | 1/05/2018 | B | 200 | 2.00 | PO29 | ||||||||||||||||
| 32 | 1/06/2018 | B | 200 | 2.20 | PO30 | ||||||||||||||||
| 33 | 1/06/2018 | B | 600 | 3.00 | PPP999 | ||||||||||||||||
| 34 | 2/06/2018 | C | 100 | 3.1 | PO31 | ||||||||||||||||
| 35 | 3/06/2018 | C | 100 | 3.2 | PO32 | ||||||||||||||||
| 36 | 4/06/2018 | C | 100 | 3 | PO33 | ||||||||||||||||
| 37 | 5/06/2018 | C | 100 | 3 | PO34 | ||||||||||||||||
Inventory | |||||||||||||||||||||
This is the code I used: 
