• 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.

stock valuation (FIFO based)

T

TPR

Member
Dear All,
The attached file's H1 to Q9 is my existing result, where FRUITS are data validated & Stock Qty is manually entered.
Problem is now my result sheet is changed (D13 to J16), where D & E columns are existing figures & others are my desired results which would be formula based. It means from F14 to J16 I need formulas for my desired results.

Please note in actual file there are about 10000 data in the fruit column & for getting perfect result in the cells of F & J columns data may be more than 10.

Kindly help, thanks in advance
Regards
TPR
 

Attachments

  • Calculation of Stock valuation based in FIFO.xlsx
    13.3 KB · Views: 22
Your question does not related to "stock valuation (FIFO based)", this is because of your new data all are in merge cell type and need to un-merge them.

Your question can be solved by Power Query, and will transfer to the Power Query Forum for the other readers to solve.

Regards
 
Last edited:
bosco_yip said:
Your question does not related to "stock valuation (FIFO based)", this is because of your new data all are in merge cell type and need to un-merge them.

Your question can be solved by Power Query, and will transfer to the Power Query Forum for the other readers to solve.

Regards
Click to expand...
Dear Sir,
Eagerly awaiting for the solution, requesting you & other readers to help
 
bosco_yip said:
Your question does not related to "stock valuation (FIFO based)", this is because of your new data all are in merge cell type and need to un-merge them.

Your question can be solved by Power Query, and will transfer to the Power Query Forum for the other readers to solve.

Regards
Click to expand...
Dear Sir,
i think little misunderstanding, my data is not merge cell type, I need the formula driven result in that form & need not to unmerge
 
vletm said:
TPR
Please, reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
There You could find - what to do if You would like to use cross-posting.
... and if You would like to go to an another doctor - that would be possible too.
How many different doctors would You use?
Click to expand...
Dear Sir
You can do anything, you may delete my post also, you may cancel my membership also, but sir my life will not stop, if I don't get a solution that does not mean everything will be stopped. However, for your last query I can say if required / needed I use end number of doctors for my treatment, any problem for that ? Certainly not for me.
 
AlanSidman said:
Please read this as it is a standard for virtually all forums. It will help you to understand why this rule is in effect. You are not sick and we are not doctors.

Excelguru Help Site - A message to forum cross posters

post people forums answer issue forum cross time pointed free
www.excelguru.ca
Click to expand...
Dear Sir
You are absolutely right, we were needy for the solution, and were not getting solution that's why i told him to post. And for that I apologized. Now tell me for that am I not eligible to get the solution ? If so, I shall not post anything further against this issue
 
You are eligible, however, we are a totally volunteer forum. People respond when and if they care to work on your issue and have the time. No one is obligated to respond. Members here are from all parts of this world. Some are asleep or at work. Patience is necessary when posting to a Free forum.
 
With Power Pivot, I can replicate your expected results for all except Total Values. You may want to do that in the source Excel before creating the Pivot. See attached.
 

Attachments

  • Calculation of Stock valuation based in FIFO.xlsx
    136.4 KB · Views: 21
AlanSidman said:
With Power Pivot, I can replicate your expected results for all except Total Values. You may want to do that in the source Excel before creating the Pivot. See attached.
Click to expand...
Dear Sir,
Thanks for your reply, see there is little misunderstanding. My desired result is not only based on source data, it based on the today's stock also, suppose if today's stock of guava is 150(I3), then in the calculation field (J3 to Q9, Yellow highlighted red font) is changed based on FIFO basis purchase history (A2 to F9). It was my earlier result, and here we can get result for only one single fruit.

But now management needs to get result for all fruits based on each fruit's present stock. And present format is D13 to J16. In this D14 to E16 are manually entered & Blue highlighted white fonts are to be formula driven which would come based on present stock(E14 to E16) ( it means if I change stock qty manually, desired result will be changed on FIFO basis, which were perfectly calculated in my earlier format[J3 TO Q9], but it was given me result for a single fruit, and I was getting that in multiple cells).

See, when I have changed today's guava stock from 200 to 150(I3), then GK Trader's stock balance becomes 30 in P3 as it is reflecting on FIFO basis purchase. But this is only for a single fruit & I have changed manually the same to my present desired result (I16), similarly I need for all fruits in the present format (F14 To J16)

Regards
TPR
 

Attachments

  • Calculation of Stock valuation based in FIFO-guava-modified.xlsx
    130 KB · Views: 17
vletm said:
TPR
Did You Forum Rules? Seems You won't answer questions.
misunderstanding ... if You have to use that kind of term, it would mean that You should focus - what have You written before press [ Post reply ].
sell ... means that You should give details which makes others interesting about Your thread
- if only You'll be interesting then ... this needs better selling.
Hint: Cross-posting won't help You to get replies.

( Note: I'll clean some replies here - because those won't help Your thread )​
Click to expand...
Dear Sir,
I don't know how should I have to proceed further.
Forum Rule : Cross Posting - I apologized for that, what more should I have to do ? After all I can't hold anyone's leg for this
Misunderstanding : I mentioned in detail against Mr. AlanSidman's post with an example attached, and I clearly mentioned in that post.
Sell : From next time I shall touch little Hollywood thrilling expressions in my posts.
Cross-posting : Sir If I don't get help, what can I do ? Max to max what will happen ? one of my problems will remain unsolved.
However, if possible, pls help, am also losing interest here day by day, and realizing that Excel may not solve all problems.
Regards
TPR
 
TPR
I cannot help with Power BI, Power Query and Power Pivot-threads ( because my Excel won't have to ... features ),
except try to give hints - how to get replies?
Thread should be done same way as 'measure twice - cut once'.
You seemed to measure once -- misunderstandings.
My personal opinion is that if someone uses Cross-posting then 'no need to hurry'.
Excel won't solve all challenges - eg how to sleep?
You could try to do one more time a sample file with expected results with clear rules ( = without any misunderstandings ).
If You're asking to use ... formulas ... then You would expect to get only formulas ... no other possible solutions.
If You're asking to get a solution - then there would be more possible solutions.
 
vletm said:
TPR
I cannot help with Power BI, Power Query and Power Pivot-threads ( because my Excel won't have to ... features ),
except try to give hints - how to get replies?
Thread should be done same way as 'measure twice - cut once'.
You seemed to measure once -- misunderstandings.
My personal opinion is that if someone uses Cross-posting then 'no need to hurry'.
Excel won't solve all challenges - eg how to sleep?
You could try to do one more time a sample file with expected results with clear rules ( = without any misunderstandings ).
If You're asking to use ... formulas ... then You would expect to get only formulas ... no other possible solutions.
If You're asking to get a solution - then there would be more possible solutions.
Click to expand...
Dear Sir,
First of all I am not at all aware of VBA, since my actual file is with huge data I shall not be able to handle, second I clearly mentioned my desired result in my file, if you pls tell what more do you need, I shall again try to explain.
 
TPR
I checked Your the latest file ...
a) If someone offer VBA-solution for You ... then can You write more data? That's all You should able to do.
b) Where should be Your huge data? It cannot be that green area because rows 13..16 has something ... but what?
c) What do You really would like to get? You seems to show something, but aren't You asking something more?
d) You seems to have there some ... formulas. eg why do need to sum Sell-text?
e) Is there some kind of clear logic?
f) What for is Sheet2?
g) You are using term FIFO ... I know FIFA ... It's related with Football.
... what do I need? ... something clear
 
You must log in or register to reply here.
Back
Top