PDA

View Full Version : Automate calculation using First in First Out way



paradise
12-19-2013, 11:05 AM
Dear Sir,

Pls find enclosed in attachment,I want the exact result of column G which is highlighted and has shown calculation manually.This column G is based in column F which is a closing qty as I have shown formula there.Column G value has been calculated by taking the rate from column D using FIFO method which means First in First Out.

If you require any further info,pls do let me know.This is a sample data as I do have a large data which is not possible to do in that case by manual calculation,hence would like to automate my calculation.

With Best Rgds.
suresh

Bob Phillips
12-19-2013, 12:39 PM
See what this does for you

=(C3+MIN(0,F2-E3))*D3+(MAX(0,F2-E3)*D2)

paradise
12-19-2013, 07:56 PM
Hi xld,

Thanks for the reply.The formula you suggested
=(C3+MIN(0,F2-E3))*D3+(MAX(0,F2-E3)*D2)

does not work i.e it is displaying a wrong result.I think you have not cross verified the expected result which I have shown in G2:G9.

Still waiting for the expected result

With Best Rgds,

suresh

paradise
12-20-2013, 01:09 AM
hi,

I have enclosed in attachment another workbook in which 'manual working' sheet explains in more comprehensive manner.With another method now the column I is required data in 'FIFO' sheet which now will be the helper column and with this helper column rest calculation will be done by a simple formula in subsequent column.This column I is how calculated is done in 'manual working' sheet in Column H.

I have enclosed a macro i.e vba code which I found at >> http://www.mrexcel.com/forum/excel-questions/167756-inventory-fifo-lifo-average-cost.html

But in that website example under FIFO,it works .But when I use that code in my data which is slightly different it does not work.As it has considered to some rows only.But here,I have filled all rows.

If you or any forum contributor helps me to modify the code taking the assumption that there may or may not be data in 'IN' AND 'OUT' Column,it would be a great sense of gratitude and appreciation.

With Best Rgds,
suresh

vert
12-20-2013, 08:03 AM
paradise,

The calculation issue is coming form a -1 in the For Next. I also came across a divide by zero when calculating the cost and not being able to clear the contents of a merged cell. Try this and see if it fixes your issues.


Sub FIFO()
Dim a As Variant, Cost As Double, sumIn As Double, sumOut As Double, _
i As Long, ii As Long, n As Long, LastRow As Integer
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With Sheets("FIFO")
LastRow = .Cells.Range("A6").End(xlDown).Row 'added to find the last row for the clear contents below.
.Range("i7:i" & LastRow).ClearContents 'added last row instead of clearing the entire column's contents
a = .Range("e7", .Cells(Rows.Count, "g").End(xlUp)).Resize(, 5).Value
n = 1
For i = LBound(a, 1) To UBound(a, 1)
If Not IsEmpty(a(i, 3)) Then
sumOut = a(i, 3)
For ii = n To i 'removed the -1
If Not IsEmpty(a(ii, 2)) Then
sumIn = sumIn + a(ii, 2)
If sumIn > sumOut Then
Exit For
Else
Cost = Cost + a(ii, 1) * a(ii, 2)
a(ii, 2) = Empty
End If
End If
Next
If sumOut = 0 Then 'added to skip if sumout is 0
ElseIf sumIn - sumOut > 0 Then
Cost = (Cost + (a(ii, 1) * (a(ii, 2) - (sumIn - sumOut)))) / sumOut
a(ii, 2) = sumIn - sumOut
Else
Cost = Cost / sumOut
End If
a(i, 5) = Cost
sumIn = 0: sumOut = 0: Cost = 0: n = ii
End If
Next
.Range("i7").Resize(UBound(a, 1)) = Application.Index(a, 0, 5)
Erase a
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

Bob Phillips
12-20-2013, 09:29 AM
Thanks for the reply.The formula you suggested
=(C3+MIN(0,F2-E3))*D3+(MAX(0,F2-E3)*D2)

does not work i.e it is displaying a wrong result.

When (you think) the result is wrong, it might be a good idea to say why rather than expecting us to guess.


I think you have not cross verified the expected result which I have shown in G2:G9.

I did verify against the expected results, and it balanced perfectly except for one value which you got wrong, because you seemed to do a copy-pastre and not correct it.


Still waiting for the expected resultI find that statement very ungracious, even rude, so as far as I am concerned, you can continue waiting.

paradise
12-20-2013, 10:52 PM
When (you think) the result is wrong, it might be a good idea to say why rather than expecting us to guess.



I did verify against the expected results, and it balanced perfectly except for one value which you got wrong, because you seemed to do a copy-pastre and not correct it.

I find that statement very ungracious, even rude, so as far as I am concerned, you can continue waiting.


Dear Sir @xld,

You & all other forum contributor has a deep sense of gratitude and respects and the same from me.I have never tries to hurt with any of the words,if you think in my statement if it does so to hurt you then I am extremely sorry for it.

Whether,I receive or not the answer, that is not the big things but in any forum,I never state rudely/hurt thru my statements.I think they(all forum contributors) all taking out time from their busy work schedule and tries to sort out the problems.Hence,they are entitled to get respects and I always respects them.

As stated by you,I have checked your formula of post #2 my attachment in post #1 .I have applied in H3 and dragged to down.But there is something missing,perhaps,I might be wrong.Kindly look into the attachment as enclosed here with your formula in H column and difference in I column once again.And pls revert back with attachment with no confusion remain which it was did & hurt you earlier.

With Best Rgds,
suresh

paradise
12-21-2013, 12:32 AM
paradise,

The calculation issue is coming form a -1 in the For Next. I also came across a divide by zero when calculating the cost and not being able to clear the contents of a merged cell. Try this and see if it fixes your issues.


Sub FIFO()
Dim a As Variant, Cost As Double, sumIn As Double, sumOut As Double, _
i As Long, ii As Long, n As Long, LastRow As Integer
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With Sheets("FIFO")
LastRow = .Cells.Range("A6").End(xlDown).Row 'added to find the last row for the clear contents below.
.Range("i7:i" & LastRow).ClearContents 'added last row instead of clearing the entire column's contents
a = .Range("e7", .Cells(Rows.Count, "g").End(xlUp)).Resize(, 5).Value
n = 1
For i = LBound(a, 1) To UBound(a, 1)
If Not IsEmpty(a(i, 3)) Then
sumOut = a(i, 3)
For ii = n To i 'removed the -1
If Not IsEmpty(a(ii, 2)) Then
sumIn = sumIn + a(ii, 2)
If sumIn > sumOut Then
Exit For
Else
Cost = Cost + a(ii, 1) * a(ii, 2)
a(ii, 2) = Empty
End If
End If
Next
If sumOut = 0 Then 'added to skip if sumout is 0
ElseIf sumIn - sumOut > 0 Then
Cost = (Cost + (a(ii, 1) * (a(ii, 2) - (sumIn - sumOut)))) / sumOut
a(ii, 2) = sumIn - sumOut
Else
Cost = Cost / sumOut
End If
a(i, 5) = Cost
sumIn = 0: sumOut = 0: Cost = 0: n = ii
End If
Next
.Range("i7").Resize(UBound(a, 1)) = Application.Index(a, 0, 5)
Erase a
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub


Dear Sir @vert,

Thanks for the reply.Since,I did not know about vba, but when I used above modified code,it worked and have tested as other cases also under FIFO. In the post #4, and file enclosed "IFO Final.xlsm (http://www.vbaexpress.com/forum/attachment.php?attachmentid=10988&d=1387526968)",there is still more two code which are " Sub LIFO()" & "Sub AVR_COST()" just below FIFO.Can you kindly modify accordingly those two vba code.As there are three codes-FIFO,Avg Cost and LIFO,I was expecting of FIFO only when I receive your help in this regard,my interest increases to the other too as well.

Hope you could solve other two cases as well.In addition to above,I would like to further state that,kindly you should have to make little changes more in FIFO as well as other two .The changes is that whenever I run macro/vba code in any sheet it should run in any sheet and not when the sheet name is 'FIFO' and other two 'LIFO' & 'AVR COST'.

Thanks again in helping.

With Best Rgds,

vert
12-30-2013, 09:57 AM
paradise,

For the LIFO make the same changes as the FIFO and it should work.
Replace

.Range("i7", .Cells(Rows.Count, "i").End(xlUp)).ClearContents
With

LastRow = .Cells.Range("A6").End(xlDown).Row 'added to find the last row for the clear contents below.
.Range("i7:i" & LastRow).ClearContents 'added last row instead of clearing the entire column's contents
Replace

For ii = i - 1 To 1 Step -1
With

For ii = i To 1 Step -1
Replace

If sumIn - sumOut > 0 Then
Cost = (Cost + (a(ii, 1) * (a(ii, 2) - (sumIn - sumOut)))) / sumOut
a(ii, 2) = sumIn - sumOut
Else
Cost = Cost / sumOut
End If
With

If sumOut = 0 Then
ElseIf sumIn - sumOut > 0 Then
Cost = (Cost + (a(ii, 1) * (a(ii, 2) - (sumIn - sumOut)))) / sumOut
a(ii, 2) = sumIn - sumOut
Else
Cost = Cost / sumOut
End If

To have them run in any sheet change Sheets(sheetname) to Activesheet. When you fire the macro i will run in the active sheet. If your fields are not exact this could really mess up the sheet though.

As for the AVR COST since you didn't include the sheet that the person who wrote the original code used i can't tell what its trying to do or were the data is supposed to go. I don't see any output but that could be because I can't follow it.

mike

paradise
01-19-2014, 12:55 AM
Dear Sir,
Thanks for the reply and explaining me in detail.The above code I have found at http://www.mrexcel.com/forum/excel-questions/167756-inventory-fifo-lifo-average-cost.html
becoz this has been extracted from there.I have also mentioned at post #4.Hope this would give u sufficient info.

With Best Rgds,
Suresh

Mihaela Manu
02-05-2015, 02:58 AM
First of all thanks for your postings it helped me alot!!!!
This works fine if I had a single type of items , unfortunately ihave many items
Column N in my attachment is the criteria .
I tried something like IF N= n+1 then run the macro andif not “” and loop.
But i wasn’t able to make it work.
I will be grateful for any help.
Best regards

Bob Phillips
02-05-2015, 04:01 AM
Mihaela,

What do you expect to see in column I? Can you give us that and explain why those number.

Mihaela Manu
02-05-2015, 04:18 AM
xld,
In the sheet named "FIFO" in column I are calculated the cost by FIFO method ( running macro) .What I'd like is to calculate for itch items separately ( considering column N - where is the item code)
Thanks a lot for your nice Quote is wonderful.

Mihaela Manu
02-05-2015, 11:49 AM
Women are wiser than men because they know less and understand more

James Thurber