how to get profit as per fifo method?

[mahaveer]

i want to put a formula in attached file for calculation of profit as per First in First out method...(FIFO) method...


if is there any vba module you can provide me then also i can manage that one...


any one can help me...?


my sample query data and expected output (profit) manually done in attached sheet....


any help will much appreciated....


http://www.2shared.com/document/HzsFPv5z/sample.html


Regards

CA Mahaveer Somani

 

[Hui]

Mahaveer


Can you please explain how you arrived at $2000 profit?

 

[Shaun]

Hi mahaveer


Looking at your spreadsheet, should your profit not be $2,050.00? Perhaps I have missed something also Hui?


Sales:

22/04/2012 20 @ $350 = $7,000


Cost of product:

01/04/2012 10 @ $250 = $2,500

20/04/2012 10 @ $300 = $3,000

------

Profit for transaction $1,500


Sales:

27/04/2012 15 @ $250 = $3,750


Cost of product:

20/04/2012 2 @ $300 = $ 600

25/04/2012 13 @ $200 = $2,600

------

Profit for transaction $ 550


Therefore $1,500 + $550 = $2,050


Unfortunately, I do not possess the Excel awesomeness required to solve this problem. I have often tried to solve a similar problem in relation to trust distributions/ taxable income/ tax payable efficiency.


So I will be very interested in any solution that maybe offered


Cheers


Shaun

 

[Hui]

Mahaveer


If the answer should be $1,850 which is the profit up to and including the last sale

use: =SUMPRODUCT((A$3:A6<=MAX($F$3:$F6))*(E$3:E6)-(F$3:F6<=MAX($F$3:$F6))*(I$3:I6))


Otherwise Please explain how to get to $2,000

 

[NARAYANK991]

Hi Mahaveer ,


If you don't mind using VBA , try this :

[pre]

Public Sub Calculate_FIFO_Profit()
Dim Purchase_Qty As Variant, Purchase_Rate As Variant, Selling_Qty As Variant, Selling_Rate As Variant
Dim Counter As Integer

'          Purchase_Qty is a named range referring to =Sheet2!$C$3:INDEX(Sheet2!$C:$C,COUNTA(Sheet2!$C:$C)+1)
'          Purchase_Rate is a named range referring to =Sheet2!$D$3:INDEX(Sheet2!$D:$D,COUNTA(Sheet2!$D:$D)+1)
'          Selling_Qty is a named range referring to =Sheet2!$G$3:INDEX(Sheet2!$G:$G,COUNTA(Sheet2!$G:$G)+1)
'          Selling_Rate is a named range referring to =Sheet2!$H$3:INDEX(Sheet2!$H:$H,COUNTA(Sheet2!$H:$H)+1)

Purchase_Qty = Range("Purchase_Qty").Value
Purchase_Rate = Range("Purchase_Rate").Value
Selling_Qty = Range("Selling_Qty").Value
Selling_Rate = Range("Selling_Rate").Value

Counter = 1
i = LBound(Selling_Qty, 1)
Sell_Qty = Selling_Qty(i, 1)
Pur_Qty = Purchase_Qty(Counter, 1)
Do
If Sell_Qty > Pur_Qty Then
Profit = Profit + (Selling_Rate(i, 1) - Purchase_Rate(Counter, 1)) * Pur_Qty
Sell_Qty = Sell_Qty - Pur_Qty
Counter = Counter + 1
Pur_Qty = Purchase_Qty(Counter, 1)
Else
Profit = Profit + (Selling_Rate(i, 1) - Purchase_Rate(Counter, 1)) * Sell_Qty
Pur_Qty = Pur_Qty - Sell_Qty
i = i + 1
If i > UBound(Selling_Qty, 1) Then Exit Do
Sell_Qty = Selling_Qty(i, 1)
End If
Loop
MsgBox Profit
End Sub

[/pre]
Please note that there is no date checking to see whether the quantity sold as on any date is actually less than or equal to the total quantity purchased till that date.


Narayan

 

[shrivallabha]

Cross post:

http://www.vbaexpress.com/forum/showthread.php?p=286558#post286558


Please provide cross posting reference while doing so!

 

[mahaveer]

thank you very much narayank....


really awesome vba code...........


much much thanks dear.......its really useful for me......

 

[Hui]

Mahaveer


Can you tell us how you arrived at 2000?

 

[Faseeh]

@ Hui,


It seems it should be 2050!


=(350*20+250*15)-(250*10+300*12+200*13)


Regards,

 

[mahaveer]

@ Hui,


I M really so sorry sir, actually answer will be 2050...i had mistake in manual totaling....but here all are excel experts....they can read from my brain..what i want....


so thanks to all experts...and thanks faseeh.......


and again sorry to @hui.....


Regards

CA Mahaveer Somani