• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculation of Stock Qty, Rate, Supplier based on FIFO

TPR

Member
Dear Friends,

Pls go through the attached file. Green Highlighted Area is basic Data. Blue highlighted are criteria & Yellow highlighted are desired Result.
What I need is formula for K3 to O3 and so on based on stock qty as on Date mentioned in J1.

The formula will stop when qty will reach equal to stock qty (J3).
Better to get formula rather than VBA

By default calculation would be FIFO basis. Pls note that Data may be or may not be in ascending or descending order in date column.

Please help.
Thanks in Advance
Regards
TPR
 

Attachments

bluesky63

Member
Hi TPR

Use Sumifs for running total for Fruit/Date, assuming there is stock level for each fruit for month (2nd worksheet), use Power Query to achieve what you want ( in the result worksheet Load from PQ)

You can amend, add data for testing and goto Result table right click > refresh to test

Note:- Click on Data > Show queries, click on Result query to PQ Editor to view the applied steps query
 

Attachments

TPR

Member
Hi TPR

Use Sumifs for running total for Fruit/Date, assuming there is stock level for each fruit for month (2nd worksheet), use Power Query to achieve what you want ( in the result worksheet Load from PQ)

You can amend, add data for testing and goto Result table right click > refresh to test

Note:- Click on Data > Show queries, click on Result query to PQ Editor to view the applied steps query
Hi Chris,
Thanks for your effort & helping me. First of all we don't have any stock level for any fruit for any month. Second when I am amending the stock 445, then stock of which supplier, which invoice & what qty is not coming up in your source sheet automatically. Moreover Supplier Name is reflecting wrong in the desired cell also. In case of power query is concerned am newbie. I tried, may be due to my mistake am not getting perfect result. See if I put stock 445, then one qty from supplier SM's inv no 102 should be added in the row in source sheet. Otherwise it would be very tough for me to calculate how much qty from which invoice is still lying.
I appreciate your involvement, if you kindly help me more, I shall be able to get more perfect result that I want.
Regards
TPR
 

bluesky63

Member
Hi Chris,
Thanks for your effort & helping me. First of all we don't have any stock level for any fruit for any month. Second when I am amending the stock 445, then stock of which supplier, which invoice & what qty is not coming up in your source sheet automatically. Moreover Supplier Name is reflecting wrong in the desired cell also. In case of power query is concerned am newbie. I tried, may be due to my mistake am not getting perfect result. See if I put stock 445, then one qty from supplier SM's inv no 102 should be added in the row in source sheet. Otherwise it would be very tough for me to calculate how much qty from which invoice is still lying.
I appreciate your involvement, if you kindly help me more, I shall be able to get more perfect result that I want.
Regards
TPR
you need to right click and refresh

by the way, if you just want a simple stock-take, perhaps just pivot table and calculation of actual fulfill quantity is another consideration (See attached), however this is not as dynamic as power query

without spelling out your actual process, i.e. what you want to achieve by given us the source (can be mock up) as real as possible, and the desire result, we can only put in our assumption

Cheers


Chris
 

Attachments

TPR

Member
Hi Chris,
Thanks again. You have spent time for me. See, I could not able to convince you my points.
First of all green highlighted area in my file is purchase history of fruits.
Second: Blue highlighted cells are criteria.
Third: Yellow highlighted cells are Result against the criteria.
Here there is no any minimum & maximum level of stock.

Assume stock of Apple on 31st May is 232, and consumption was on FIFO basis.
Now I need Result as per yellow highlighted cells, If you see my green highlighted cells there you will observe that if I take consumption on FIFO basis, then SM's first supply against inv no 102 dt 09-04-2019 was fully sold. Now second supplier was GK Trader. He supplied 252 nos against inv no 15 dt 19-05-2019. Out of that 252, 212 was sold as per FIFO basis and 40 nos are in stock as on 31-05-2019. Third supplier's (SOM) full qty i.e. 192 nos are full in stock. Then only 192+40 = 232 is covering the stock qty.
This would be back calculated. I shall put the stock & date of any fruit. Formula will tell me the supplier names, inv nos, dates, qty, & rate which will cover the stock qty of a particular day. But those must be earlier supply of the date that I shall put. The result may be in one row or multiple row based on stock & date

Hope now You can understand my issue. Pls help.
Thanks & Regards
TPR.
 

bluesky63

Member
Hi TPR,

no issue as I am learning too

Frankly speaking, pardon me that I still don't really fully understand, haha
from the green highlighted table, how you know SM's first supply against inv no 102 dt 09-04-2019 was fully sold ? probably I don't understand the business, like Qty in highlighted green mean sold ? and stock 232 and the date 31/5/19.....

perhaps other guru in this forum can help and provide solution for my learning Cheers !1
 

TPR

Member
Hi TPR,

no issue as I am learning too

Frankly speaking, pardon me that I still don't really fully understand, haha
from the green highlighted table, how you know SM's first supply against inv no 102 dt 09-04-2019 was fully sold ? probably I don't understand the business, like Qty in highlighted green mean sold ? and stock 232 and the date 31/5/19.....

perhaps other guru in this forum can help and provide solution for my learning Cheers !1
how you know SM's first supply against inv no 102 dt 09-04-2019 was fully sold
As calculation based on FIFO basis ( First in First Out )
However, thanks for you involvement, and appreciate your effort.
 

TPR

Member
TPR
1) Is Your 'yellow range' K3:O4 ALL needed results which could get from Your given data with 'APPLE'?
Yes Sir, all needed result, and result would come out against the criteria, if I change Date, Fruit & stock qty that will calculate previous Purchase Data only.
2) The formula will stop when qty will reach equal to stock qty (J3).
Yes, Formula will stop.
What ... if values won't match and ever reach?
Sir, Here I need the rate of the invoice, invoice no & supplier. Total Stock value will come out from there.
3) Better to get formula rather than VBA
... Are You looking solution?
Ha Ha Ha, your are right, I need solution, if VBA can help, then always welcome Sir. Please help me.
Awaiting for your enormous help.
Regards
TPR
 

TPR

Member
TPR
One more time ...

1) Is Your 'yellow range' K3:O4 ALL needed results which could get from Your given data with 'APPLE'?
Of course, You would need ALL results, BUT I didn't ask that!
Is there NOW ALL POSSIBLE NEEDED results with 'APPLE' in Your file?

2) My question was>> What ... if values won't match and ever reach?
You fill Yourself 'whatever values' in Blue ... eg to J3 value -19.567 - hmm?

3) Better to get formula rather than VBA ...
... then You would try to answer to questions rather write something else.
Actually, You need those answers to questions, that You would get solution - not me!
1) Yes, Suppose, I give stock 445, instead of 232, then one more data will be added. As 1 no of apple from the first supply i.e. SM's 102 no inv would remain in stock.
2) In case of fruit it can't be in decimal, but if item is a fabric or lubricant & their UOM in Mtr & Ltr or Kg then I may put fraction figure manually in J3.
3) Yes, I need solution; but honestly I don't know anything about VBA but in case of formula I shall try to evaluate & can learn.
Regards
TPR
 

TPR

Member
TPR
Please, sit down - close Your eyes - take two deep breaths - open Your both eyes
read below line - close Your eyes - think more than one sec - open Your eyes and answer step one:
1) from below GREEN-data ALL NEEDED RESULTS are shown in YELLOW- table ( case APPLE ) ... YES or NO?
View attachment 64639 => View attachment 64638

2) You didn't answers to my question!
3) ... then You would try to answer to questions rather write something else.
Actually, You need those answers to questions, that You would get solution - not me!
1) Yes from Green highlighted area results are shown in yellow
2) Data not matched, ever reached (considering FIFO basis sell / consumption against green highlighted purchase data)
3) Hope now I answered properly, as I closed my eyes before answering.
Regards
TPR
 

TPR

Member
TPR
1) You couldn't answer YES or NO!
Did You noticed term ALL?
Where would be 09/04/2019 APPLE?
2) Yes ... Your The formula will stop when qty will reach equal to stock qty (J3). ... would last for ever!
3) Excel and hope --- where would be connection? ... seems that You can write with closed eyes.
I've asked ... tried to ask ... tried to help ...
Pls note that Data may be or may not be in ascending or descending order in date column.
1) Sir, If I put stock manually 445 (J3), then one data would be added in result (yellow highlighted area) as to cover 445 stock, 1 no is needed from inv no 102 dt 09-04-2019 on FIFO basis.
2) To cover or reach stock qty on FIFO basis formula will stop till it reaches or cover the stock qty.
3) Either formula or vba or composite, you may apply.
Pls help me sir.
Regards
TPR
 

TPR

Member
Where would be 09/04/2019 APPLE?
It would be in the last Row if I put stock 445 manually
like this in yellow highlighted Area
STOCK DATE
31-05-2019​
FRUITSTOCKSUPPLIERINV NODATEQTYRATE
APPLE
445​
SOM35/59
17-05-2019​
192​
11.5​
GK TRADER
15​
19-05-2019​
252​
10​
SM
102​
43564​
1​
12​
 

TPR

Member
TPR
This seems to clear fuzzy logic without known FIFO.
Your QTY-values meaning seems to be different in each case ...
Green ... maybe inventory values
Blue ... maybe something - depends that date
Yellow ... combination of inventory and something
It would be more clear for others, who should try to do this for You, that QTY means always same!
Sir,
You are absolutely right.
Green - my purchase history
Blue - The Data that I shall put manually (Fruit Name, Date, & Stock Qty)
Yellow - is the result that I need.
But it would be on FIFO Basis. As you can see that I consumed / sold full qty which was purchased earlier, based on the stock qty( first i gave 232, & later 445) for both the cases.
And yellow highlighted will give result based on the criteria (blue)
Regards
TPR
 

TPR

Member
TPR
I see - You skipped it!
as well as 'Why'-question,
as well as 'fifo' ...
Then I should combine all result in same cell with random order
... and You'll continue calculate something more
Should I figure something ... hmm?
Sir,
Please figure out something, then I shall request you for any modification if required.
 

TPR

Member
TPR
Edit Your "blues" and ... get results.
If "blues" would be in other position ...
Sir,
Misunderstanding. my desired result is in yellow in my file sir which would be based on the criteria which I highlighted in blue. But in your file it is not reflecting Sir.
 

TPR

Member
TPR
Okay - yellow
and
seems that You continue skipping my writings ... reread #29
... and if still wondering then You would read even the 1st time all my writings.
Dear Sir,
Requesting you please check my file, I need that result only that I highlighted in yellow, if I put Stock 232, date 31-05-19 & fruit apple manually. but not getting sir.
 

TPR

Member
TPR
I cannot check 'Your' file just now
... because I cannot know about 'Your' file,
but I tested with 'My' file - which You could find from #31 reply.
If ... those values were there ready
... then nothing so visible won't see! ( = same results! )
If You WRITE to Stock value 231 and press <ENTER>
... then something would happen
If You write date as 30/05/2019 (as in that file were ... I also tested with Your format ... hmm? ... it worked)
... then also something would happen
If You CHOOSE from Fruit any of those used Fruits, then also something would happen
... or of course, You could WRITE JUST same way as those three Fruits are there, then also something would happen.

You wrote something ... I need that result only that I highlighted in yellow
... that would mean that AFTER You have entered ANY OF THOSE THREE BLUEs
... ... then EVERYTHING else would be cleared.
That's sounds ... hmm?
You could do same manually Yourself...

Ps Of course, You've Macros Enabled ... of course?
Dear Sir,
1) I am appending a file with more detail in the worksheet. Hope it will help you to understand what I need.
2) I don't know anything about binary file, in case your file helps me, am in dark how can I implement that in other file also. Though it's secondary matter.
3) If you need more specific detail please let me know.

Regards
TPR
 

Attachments

TPR

Member
TPR
1) You should get those from my file
2) If You have not tested my file, then how do You have thought to accept VBA?
3) Where should I need more details?
Bonus) When would You start to read my comments or start to give needed answers?
Dear Sir,
I have changed the date & qty but not getting desired result.
 

Attachments

Top