Calculation of stock valuation (FIFO Basis)

R

RAMU

Active Member
Joined
Dec 11, 2009
Messages
345
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
Dear Friends,
I have a Raw Data Like this:
PURCHASE
FRUITSUPPLIERINV NODATEQTYRATE
GUAVAGK TRADER10
10-04-2021​
96​
8​
APPLESM102
09-04-2021​
50​
12​
BANANASM134
12-05-2021​
144​
3​
APPLEGK TRADER15
19-05-2021​
252​
10​
BANANASOM35/58
05-05-2021​
180​
3.25​
APPLESOM35/59
17-05-2021​
192​
11.5​
GUAVASM149
22-05-2021​
120​
7.3​

From this data I am able to get result in this format for a single fruit:

STOCK DATE :
13-06-2021​
SUPPLIERINV NODATERATE
PurchaseSellStock (Balance)Value
GUAVA200GK TRADER10
10-04-2021​
8​
96​
16​
80​
640​
SM149
22-05-2021​
7.3​
120​
0​
120​
876​
​
​
Total​
216​
162001516

Guava is a Drop Down List here, Stock Date & Qty i.e. 200 is manually entered, rest results i.e. from SUPPLIER to Value are formula driven:
For SUPPLIER : =IF($L3="","",INDEX(B$3:B$9,MATCH($L3,$D$3:$D$9,0)))
INV NO : =IF($L3="","",INDEX(C$3:C$9,MATCH($L3,$D$3:$D$9,0)))
DATE : =IFERROR(AGGREGATE(15,6,D$3:D$9/($A$3:$A$9=$H$3)/(D$3:D$9<=$I$1),ROWS($1:1)),"")
RATE: =IF($L3="","",INDEX(F$3:F$9,MATCH($L3,$D$3:$D$9,0)))
PURCHASE : =IF($L3="","",INDEX(E$3:E$9,MATCH($L3,$D$3:$D$9,0)))
SELL : =IF($L3="","",MIN($N3,MAX(0,SUM($N$3:$N$8)-I$3-SUM(O$2:O2))))
STOCK (BALANCE) =IF($L3="","",$N3-$O3)
VALUE : =IFERROR(P3*M3,"")

Now my problem is till this I am able to give stock valuation for a single fruit only but my modified & latest format is changed & now need result in the form mentioned below:

FRUITSTOCKSUPPLIERSDATESINV NOSQTYVALUE
APPLE
300​
GK TRADER, SOM19-05-21, 17-05-2115, 35/59252, 48
3072​
BANANA
300​
SM, SOM12-05-21, 05-05-21134, 35/58144, 156
939​
GUAVA
200​
SM, GK TRADER22-05-21, 10-04-21149, 10120, 80
1516​

FRUIT & STOCK these two columns are already available, rest all I need formulas & desired results would be in the same form, please note in case if I take reference from my previous formula driven cells, then if Stock (Balance) becomes 0, that would be ignored. for better understanding I have uploaded an image of my file. From F14 to J16 is my desired result. Yellow cells & Red fonts are my present formula driven results.

Please help, please note that in original sheet there are about more than 10000 data of fruits & result for Suppliers, dates etc may come more than 10 nos.

Regards
RAMU
 

Attachments

  • stock-val.png
    stock-val.png
    208.8 KB · Views: 50

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
RAMU said:
Dear Friends,
I have a Raw Data Like this:
PURCHASE
FRUITSUPPLIERINV NODATEQTYRATE
GUAVAGK TRADER10
10-04-2021​
96​
8​
APPLESM102
09-04-2021​
50​
12​
BANANASM134
12-05-2021​
144​
3​
APPLEGK TRADER15
19-05-2021​
252​
10​
BANANASOM35/58
05-05-2021​
180​
3.25​
APPLESOM35/59
17-05-2021​
192​
11.5​
GUAVASM149
22-05-2021​
120​
7.3​

From this data I am able to get result in this format for a single fruit:

STOCK DATE :
13-06-2021​
SUPPLIERINV NODATERATE
PurchaseSellStock (Balance)Value
GUAVA200GK TRADER10
10-04-2021​
8​
96​
16​
80​
640​
SM149
22-05-2021​
7.3​
120​
0​
120​
876​
​
​
Total​
216​
162001516

Guava is a Drop Down List here, Stock Date & Qty i.e. 200 is manually entered, rest results i.e. from SUPPLIER to Value are formula driven:
For SUPPLIER : =IF($L3="","",INDEX(B$3:B$9,MATCH($L3,$D$3:$D$9,0)))
INV NO : =IF($L3="","",INDEX(C$3:C$9,MATCH($L3,$D$3:$D$9,0)))
DATE : =IFERROR(AGGREGATE(15,6,D$3:D$9/($A$3:$A$9=$H$3)/(D$3:D$9<=$I$1),ROWS($1:1)),"")
RATE: =IF($L3="","",INDEX(F$3:F$9,MATCH($L3,$D$3:$D$9,0)))
PURCHASE : =IF($L3="","",INDEX(E$3:E$9,MATCH($L3,$D$3:$D$9,0)))
SELL : =IF($L3="","",MIN($N3,MAX(0,SUM($N$3:$N$8)-I$3-SUM(O$2:O2))))
STOCK (BALANCE) =IF($L3="","",$N3-$O3)
VALUE : =IFERROR(P3*M3,"")

Now my problem is till this I am able to give stock valuation for a single fruit only but my modified & latest format is changed & now need result in the form mentioned below:

FRUITSTOCKSUPPLIERSDATESINV NOSQTYVALUE
APPLE
300​
GK TRADER, SOM19-05-21, 17-05-2115, 35/59252, 48
3072​
BANANA
300​
SM, SOM12-05-21, 05-05-21134, 35/58144, 156
939​
GUAVA
200​
SM, GK TRADER22-05-21, 10-04-21149, 10120, 80
1516​

FRUIT & STOCK these two columns are already available, rest all I need formulas & desired results would be in the same form, please note in case if I take reference from my previous formula driven cells, then if Stock (Balance) becomes 0, that would be ignored. for better understanding I have uploaded an image of my file. From F14 to J16 is my desired result. Yellow cells & Red fonts are my present formula driven results.

Please help, please note that in original sheet there are about more than 10000 data of fruits & result for Suppliers, dates etc may come more than 10 nos.

Regards
RAMU
Click to expand...
Can TEXTJOIN help ? Just curious. pls help, most urgent for me.
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: stock valuation (FIFO based)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Fluff said:
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: stock valuation (FIFO based)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Click to expand...
Yes, extremely sorry for this, i gave the same to my wifey for the solution, & she was not aware of this, if I am not eligible to get solution for that that's different, but it's strange that someone is getting any solution here, that does not mean she / he should not go in other forum. It's ridiculous.
 
Upvote 0
Fluff said:
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: stock valuation (FIFO based)
If you have posted the question at more places, please provide links to those as we
Click to expand...

Fluff said:
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: stock valuation (FIFO based)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn
Click to expand...

RAMU said:
Dear Friends,
I have a Raw Data Like this:
PURCHASE
FRUITSUPPLIERINV NODATEQTYRATE
GUAVAGK TRADER10
10-04-2021​
96​
8​
APPLESM102
09-04-2021​
50​
12​
BANANASM134
12-05-2021​
144​
3​
APPLEGK TRADER15
19-05-2021​
252​
10​
BANANASOM35/58
05-05-2021​
180​
3.25​
APPLESOM35/59
17-05-2021​
192​
11.5​
GUAVASM149
22-05-2021​
120​
7.3​

From this data I am able to get result in this format for a single fruit:

STOCK DATE :
13-06-2021​
SUPPLIERINV NODATERATE
PurchaseSellStock (Balance)Value
GUAVA200GK TRADER10
10-04-2021​
8​
96​
16​
80​
640​
SM149
22-05-2021​
7.3​
120​
0​
120​
876​
​
​
Total​
216​
162001516

Guava is a Drop Down List here, Stock Date & Qty i.e. 200 is manually entered, rest results i.e. from SUPPLIER to Value are formula driven:
For SUPPLIER : =IF($L3="","",INDEX(B$3:B$9,MATCH($L3,$D$3:$D$9,0)))
INV NO : =IF($L3="","",INDEX(C$3:C$9,MATCH($L3,$D$3:$D$9,0)))
DATE : =IFERROR(AGGREGATE(15,6,D$3:D$9/($A$3:$A$9=$H$3)/(D$3:D$9<=$I$1),ROWS($1:1)),"")
RATE: =IF($L3="","",INDEX(F$3:F$9,MATCH($L3,$D$3:$D$9,0)))
PURCHASE : =IF($L3="","",INDEX(E$3:E$9,MATCH($L3,$D$3:$D$9,0)))
SELL : =IF($L3="","",MIN($N3,MAX(0,SUM($N$3:$N$8)-I$3-SUM(O$2:O2))))
STOCK (BALANCE) =IF($L3="","",$N3-$O3)
VALUE : =IFERROR(P3*M3,"")

Now my problem is till this I am able to give stock valuation for a single fruit only but my modified & latest format is changed & now need result in the form mentioned below:

FRUITSTOCKSUPPLIERSDATESINV NOSQTYVALUE
APPLE
300​
GK TRADER, SOM19-05-21, 17-05-2115, 35/59252, 48
3072​
BANANA
300​
SM, SOM12-05-21, 05-05-21134, 35/58144, 156
939​
GUAVA
200​
SM, GK TRADER22-05-21, 10-04-21149, 10120, 80
1516​

FRUIT & STOCK these two columns are already available, rest all I need formulas & desired results would be in the same form, please note in case if I take reference from my previous formula driven cells, then if Stock (Balance) becomes 0, that would be ignored. for better understanding I have uploaded an image of my file. From F14 to J16 is my desired result. Yellow cells & Red fonts are my present formula driven results.

Please help, please note that in original sheet there are about more than 10000 data of fruits & result for Suppliers, dates etc may come more than 10 nos.

Regards
RAMU
Click to expand...
Dear Friends,
Is it not possible
 
Upvote 0
RAMU said:
Dear Friends,
Is it not possib
RAMU said:
Dear Friends,
I have a Raw Data Like this:
PURCHASE
FRUITSUPPLIERINV NODATEQTYRATE
GUAVAGK TRADER10
10-04-2021​
96​
8​
APPLESM102
09-04-2021​
50​
12​
BANANASM134
12-05-2021​
144​
3​
APPLEGK TRADER15
19-05-2021​
252​
10​
BANANASOM35/58
05-05-2021​
180​
3.25​
APPLESOM35/59
17-05-2021​
192​
11.5​
GUAVASM149
22-05-2021​
120​
7.3​

From this data I am able to get result in this format for a single fruit:

STOCK DATE :
13-06-2021​
SUPPLIERINV NODATERATE
PurchaseSellStock (Balance)Value
GUAVA200GK TRADER10
10-04-2021​
8​
96​
16​
80​
640​
SM149
22-05-2021​
7.3​
120​
0​
120​
876​
​
​
Total​
216​
162001516

Guava is a Drop Down List here, Stock Date & Qty i.e. 200 is manually entered, rest results i.e. from SUPPLIER to Value are formula driven:
For SUPPLIER : =IF($L3="","",INDEX(B$3:B$9,MATCH($L3,$D$3:$D$9,0)))
INV NO : =IF($L3="","",INDEX(C$3:C$9,MATCH($L3,$D$3:$D$9,0)))
DATE : =IFERROR(AGGREGATE(15,6,D$3:D$9/($A$3:$A$9=$H$3)/(D$3:D$9<=$I$1),ROWS($1:1)),"")
RATE: =IF($L3="","",INDEX(F$3:F$9,MATCH($L3,$D$3:$D$9,0)))
PURCHASE : =IF($L3="","",INDEX(E$3:E$9,MATCH($L3,$D$3:$D$9,0)))
SELL : =IF($L3="","",MIN($N3,MAX(0,SUM($N$3:$N$8)-I$3-SUM(O$2:O2))))
STOCK (BALANCE) =IF($L3="","",$N3-$O3)
VALUE : =IFERROR(P3*M3,"")

Now my problem is till this I am able to give stock valuation for a single fruit only but my modified & latest format is changed & now need result in the form mentioned below:

FRUITSTOCKSUPPLIERSDATESINV NOSQTYVALUE
APPLE
300​
GK TRADER, SOM19-05-21, 17-05-2115, 35/59252, 48
3072​
BANANA
300​
SM, SOM12-05-21, 05-05-21134, 35/58144, 156
939​
GUAVA
200​
SM, GK TRADER22-05-21, 10-04-21149, 10120, 80
1516​

FRUIT & STOCK these two columns are already available, rest all I need formulas & desired results would be in the same form, please note in case if I take reference from my previous formula driven cells, then if Stock (Balance) becomes 0, that would be ignored. for better understanding I have uploaded an image of my file. From F14 to J16 is my desired result. Yellow cells & Red fonts are my present formula driven results.

Please help, please note that in original sheet there are about more than 10000 data of fruits & result for Suppliers, dates etc may come more than 10 nos.

Regards
RAMU
Click to expand...
Now it has become brain storming for me. Can't understand how to do this
Click to expand...
 
Upvote 0
RAMU said:
Dear Friends,
I have a Raw Data Like this:
PURCHASE
FRUITSUPPLIERINV NODATEQTYRATE
GUAVAGK TRADER10
10-04-2021​
96​
8​
APPLESM102
09-04-2021​
50​
12​
BANANASM134
12-05-2021​
144​
3​
APPLEGK TRADER15
19-05-2021​
252​
10​
BANANASOM35/58
05-05-2021​
180​
3.25​
APPLESOM35/59
17-05-2021​
192​
11.5​
GUAVASM149
22-05-2021​
120​
7.3​

From this data I am able to get result in this format for a single fruit:

STOCK DATE :
13-06-2021​
SUPPLIERINV NODATERATE
PurchaseSellStock (Balance)Value
GUAVA200GK TRADER10
10-04-2021​
8​
96​
16​
80​
640​
SM149
22-05-2021​
7.3​
120​
0​
120​
876​
​
​
Total​
216​
162001516

Guava is a Drop Down List here, Stock Date & Qty i.e. 200 is manually entered, rest results i.e. from SUPPLIER to Value are formula driven:
For SUPPLIER : =IF($L3="","",INDEX(B$3:B$9,MATCH($L3,$D$3:$D$9,0)))
INV NO : =IF($L3="","",INDEX(C$3:C$9,MATCH($L3,$D$3:$D$9,0)))
DATE : =IFERROR(AGGREGATE(15,6,D$3:D$9/($A$3:$A$9=$H$3)/(D$3:D$9<=$I$1),ROWS($1:1)),"")
RATE: =IF($L3="","",INDEX(F$3:F$9,MATCH($L3,$D$3:$D$9,0)))
PURCHASE : =IF($L3="","",INDEX(E$3:E$9,MATCH($L3,$D$3:$D$9,0)))
SELL : =IF($L3="","",MIN($N3,MAX(0,SUM($N$3:$N$8)-I$3-SUM(O$2:O2))))
STOCK (BALANCE) =IF($L3="","",$N3-$O3)
VALUE : =IFERROR(P3*M3,"")

Now my problem is till this I am able to give stock valuation for a single fruit only but my modified & latest format is changed & now need result in the form mentioned below:

FRUITSTOCKSUPPLIERSDATESINV NOSQTYVALUE
APPLE
300​
GK TRADER, SOM19-05-21, 17-05-2115, 35/59252, 48
3072​
BANANA
300​
SM, SOM12-05-21, 05-05-21134, 35/58144, 156
939​
GUAVA
200​
SM, GK TRADER22-05-21, 10-04-21149, 10120, 80
1516​

FRUIT & STOCK these two columns are already available, rest all I need formulas & desired results would be in the same form, please note in case if I take reference from my previous formula driven cells, then if Stock (Balance) becomes 0, that would be ignored. for better understanding I have uploaded an image of my file. From F14 to J16 is my desired result. Yellow cells & Red fonts are my present formula driven results.

Please help, please note that in original sheet there are about more than 10000 data of fruits & result for Suppliers, dates etc may come more than 10 nos.

Regards
RAMU
Click to expand...
For more better understanding I am sharing the a link, hope this may help

Calculation of Stock valuation based in FIFO.xlsx - Shared with pCloud

Keep all your documents safe, access them on any device you own and share with just the right people. Create a free pCloud account!
u.pcloud.link u.pcloud.link
 
Upvote 0
hi everyone
Is it not possible in excel ?
 
Upvote 0
RAMU said:
For more better understanding I am sharing the a link, hope this may help
Click to expand...
RAMU said:
For more better understanding I am sharing the a link, hope this may help

Calculation of Stock valuation based in FIFO.xlsx - Shared with pCloud

Keep all your documents safe, access them on any device you own and share with just the right people. Create a free pCloud account!
u.pcloud.link u.pcloud.link
Click to expand...
Can anyone help pls
 
Upvote 0
It seems that not possible in excel, should I take it as "REGRETTED"
Awaiting reply from the MVPs....................!!!!!!
Shall wait for next three hours, thereafter shall ask help from Microsoft
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
lookupifs?? lookup value so long as date value is greater than specified date
Replies
5
Views
120
Peter_SSs
Peter_SSs
C
  • Question Question
Formula not working to show total sales per day, #VALUE! Can't find the issue (Ignore Conditionals) I3 works fine, J3&J4 are what I have tried.
2
Replies
15
Views
599
Asbestos_Jen
Asbestos_Jen
K
  • Solved
Macro to compare old prices with new prices for stock and show(-/+)
4 5 6
Replies
58
Views
687
Peter_SSs
Peter_SSs
I
  • Question Question
Cascading Drop Downs
Replies
3
Views
272
imran999
I
A
  • Solved
put the QTY to adjacent ID based on arranged sheet when compare two sheets
Replies
7
Views
267
abdo meghari
A

Forum statistics

Threads
1,224,828
Messages
6,181,212
Members
453,023
Latest member
alabaz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top