FIFO REPORT

[naris]

I have a file that contains the calculation of inventory using FIFO method, in the beginning to the end of the period, thus found the value end of the inventory.What I want is I want to know the position of each period, for example I want to know the position of the balance of supply each periods same as in my file (see Data base - Each of Period).
Is there someone who can help me, t give good formula for solve it, without manually like as my data "each of peiod" ? ..:help

Thanks.
Naris

[Bob Phillips]

I really don't understand those calculations in L26:L33.

[MaximS]

Hi naris,

I've created User Defined Function to calculate inventory using FIFO method. It require following parameters to work: (Period Range, Price Range, Intake Range, Outake Range, Period Number).


Public Function FIFO(ByRef Period_Range As Range, ByRef Price As Range, _
ByRef Intake As Range, ByRef Outake As Range, ByVal Period As Integer) As Long

Dim Per() As Variant, Pri() As Variant, Inta() As Variant, Outa() As Variant
Dim Bal() As Variant, i As Integer, j As Integer, Mx As Long
Dim x As Long, y As Long, z As Long

ReDim Preserve Pri(1 To Period)
ReDim Preserve Inta(1 To Period)
ReDim Preserve Outa(1 To Period)
ReDim Preserve Per(1 To Period)

For i = 1 To Period
Per(i) = i
Inta(i) = WorksheetFunction.SumIf(Period_Range, Per(i), Intake)
Mx = WorksheetFunction.Match(Per(i), Period_Range, 0)
Pri(i) = WorksheetFunction.Index(Price, Mx, 0)
Outa(i) = WorksheetFunction.SumIf(Period_Range, Per(i), Outake)

Next i

ReDim Bal(1 To Period)

j = 1
Bal(1) = (Inta(1) - Outa(1))

If Period > 1 Then

For i = 2 To Period

Bal(i) = Inta(i)

If (Bal(j) - Outa(i)) > 0 Then
Bal(j) = Bal(j) - Outa(i)
Else

z = Bal(j) - Outa(i)
Bal(j) = z

Do Until Bal(j) > 0
Bal(j) = 0
j = j + 1
Bal(j) = Bal(j) + z
z = Bal(j)
Loop
End If
Next i

For i = 1 To Period
y = y + (Bal(i) * Pri(i))
Next i

Else
y = (Bal(1) * Pri(1))
End If

FIFO = y

End Function


which can be seen working in attached file.

[naris]

Great good news !! :clap: , yes .., this is such as what I want & need, thanks you very much..

I hope in this new year, this forum still & always become the first & the best VBA forum ..

Happy nice day :hi: ..
naris

[mal0042002]

thanxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

[hyc]

Thankss for the Q-A, now I got what I want,

[hyc]

How come I still see nothing in attached files?

[Aussiebear]

How come I still see nothing in attached files?

Which attached files and what would you be seeking to find?

[hyc]

The second one, can you sir help me to upload it in other hostweb?