Calculation of stock valuation (FIFO Basis)

R

RAMU

Active Member
Joined
Dec 11, 2009
Messages
321
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: 35

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
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

T
  • Question
VBA generates word docs, request to amend code to include description of deadlines/tasks due
Replies
0
Views
186
ttp921
T
H
  • Question
using sorting and array to deal with big data for expected result
Replies
3
Views
107
Hasson
H
S
  • Solved
Lookup results in a table based on multiple criteria and set number of occurrences
Replies
4
Views
125
kevin9999
K
T
  • Solved
VBA generates word docs, request to amend code to include description of deadlines/tasks due
Replies
4
Views
345
ttp921
T
R
  • Locked
  • Question
Calculation of Stock Execution on FIFO basis
Replies
1
Views
919
Fluff
Fluff

Forum statistics

Threads
1,214,983
Messages
6,122,595
Members
449,089
Latest member
Motoracer88

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