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

Expect Excel formula to find the product price by FIFO Method

Dear Excel Genius

I have produced some products in my company which is having or used different raw materials purchased with different prices. I want to calculate the products price per kg by using (Raw Materials) FIFO Method.

Below is the example data I have calculated Products 1 to 5's RM 1 price manually in column X, where I need to calculate RM 1 to 10 for the Products 1 to 5. (actual RM more than 100, just for an example I added 10 RM Only)

Products 1 to 5 RM Usage is in below image (Actual will be more than 10,000 productions are there to calculate)
79247

RM 1 to RM 10 Purchase qty with price is below
79248

Products 1 to 5 RM 1 cost manual entry is in below image (Attachment Column X)
79249

In the Attachment Column W, I need each products RM 1 to RM 10 Cost summation to be showed by excel formula. Attached the file for your reference.

We can split the data into different sheet (One sheet consumtion another sheet purchase data if required)
Also, You can add helper column and it can be hide too.
I want the expected results in Column W by one excel formula.
 

Attachments

  • Product price per kg.xlsx
    27.5 KB · Views: 16

Attachments

  • Product price per kg FIFO allocation.xlsx
    30.2 KB · Views: 12
Please try at W4

=SUM(TEXT(SUM(M$4:M4)-MMULT(--(ROW($Z$4:$Z$8)>TRANSPOSE(ROW($Z$4:$Z$8))),--$Z$4:$Z$8),"0;\0")*($Y$4:$Y$8-N(+$Y$3:$Y$7)))-SUM(W$3:W3)

Ctrl+Shift+Enter

or Spill Array with MS365


=LET(q,M4:M8,p,Y4:Y8,s,Z4:Z8,sa,SCAN(0,s,LAMBDA(a,v,SUM(a,v)))-s,qa,SUM(q)-sa,
by,BYROW(SCAN(0,q,LAMBDA(a,v,a+v)),LAMBDA(v,LET(qa,v-sa,SUM(IF(qa>0,IF(qa>s,s,qa))*p)))),by-IFERROR(SMALL(by,SEQUENCE(ROWS(q),,0)),))
 

Attachments

  • Product price per kg.xlsx
    29.2 KB · Views: 13
Last edited:
@Peter Bartholomew and @Excel Wizard
Thanks a ton to both of you!

I just downloaded and it seems both formulae are working fine. I am trying to get all the 10 RM costs in Column W.

Due to table formation, in the @Peter Bartholomew solution, I need some more time to understand and transfer it to my real sheet where I have 1000 products and more than 100 RM.

@Excel Wizard Solution I will transfer the excel formula to all RM and get one final answer in column W.

I am using MS365. But, I posted the question from MS Office 2007 version.

Once again thanks a lot for your effort towards my post.
 
=SUM(TEXT(SUM(M$4:M4)-MMULT(--(ROW($Z$4:$Z$8)>TRANSPOSE(ROW($Z$4:$Z$8))),--$Z$4:$Z$8),"0;\0")*($Y$4:$Y$8-N(+$Y$3:$Y$7)))-SUM(W$3:W3)
The above formula I tried for RM 1 and transfer from RM 2 to RM 10, But for RM 6 and 7 there is an error. I couldn't track what is it? Could you please check and clear the error?

Tried column AU to BD. After this Calculation, I will transfer to the W column to get the final cost ( Sum of AU to BD)
79283

My Request as below
1) If possible please give me one excel formula to calculate and sum up the RM 1 to RM 10 to get the W column answer!
2) Actual sheet has more than 100 RM and 10,000 Products to calculate, So If I add 100 Extra columns with formula then the excel sheet will get slower to process each time.
 

Attachments

  • Master Sheet.xlsx
    56.6 KB · Views: 4
@Excel Wizard

I have applied both of your formulas and got some differences in the results. see below snapshot and the attached file for your kind consideration.
79285

The Red highlighted RM 5 Price is the wrong value by the first of your suggested formula.

The Green Highlighted RM 5 Price is correct when I compared it to the manual calculation which is your second suggested formula.

The manual calculation I added in column AH for RM 5.
 

Attachments

  • Master Sheet.xlsx
    58.2 KB · Views: 3
@Peter Bartholomew

I have observed and added the naming of RM2 to RM10 and I got the desired results in the sample file.
79290

But I don't know how to transfer the same to my actual file as it is having some kind of code inside the name manager. Sorry for my inability in the same.

Thanks again for the solution and the time spent on my post.

For Allocate I found the below.

LAMBDA(outputArray,inputArray,[weight],
LET(
cumulativeOutput, Accumulateλ(outputArray, 0, {0,1}),
cumulativeInput, Accumulateλ(inputArray, 0, {0,1}),
m, ROWS(outputArray),
n, ROWS(inputArray),
allocationArray, MAKEARRAY(
m,
n,
LAMBDA(k,h,
LET(
u, {-1,1} * INDEX(cumulativeOutput, k, {1,2}),
v, {-1,1} * INDEX(cumulativeInput, h, {1,2}),
d, SUM(IF(u < v, u, v)),
q, IF(d > 0, d, 0),
w, IF(ISOMITTED(weight), 1, INDEX(weight, h)),
q * w
)
)
),
IF(
ISOMITTED(weight),
allocationArray,
BYROW(allocationArray, Sumλ)
)
)
)
 

Attachments

  • Product price per kg FIFO allocation.xlsx
    33.7 KB · Views: 2
Try AY4
=SUM(TEXT(SUM(Q$4:Q4)-MMULT(--(ROW($AH$4:$AH$8)>TRANSPOSE(ROW($AH$4:$AH$8))),--$AH$4:$AH$8),"0.00;\0")*($AG$4:$AG$8-N(+$AG$3:$AG$7)))-SUM(AY$3:AY3)
 

Attachments

  • Master Sheet.xlsx
    58.3 KB · Views: 12
This workbook contains a set of exploratory steps intended to lead to a solution. I wrote a set of lambda functions to access the data
Code:
RMConsumptionλ = LAMBDA(idx, INDEX(RMtbl_consumption, product_Index, idx));

RMPriceλ = LAMBDA(idx, INDEX(RMtbl_purchase, product_Index, 2 * idx - 1));

RMQuantityλ = LAMBDA(idx, INDEX(RMtbl_purchase, product_Index, 2 * idx));
and REDUCE to aggregate over RM indices.
Code:
=  REDUCE(0*product_Index, RM_Index,
      LAMBDA(acc,idx, (acc + Allocateλ(RMConsumptionλ(idx), RMQuantityλ(idx), RMPriceλ(idx))))
   )
 

Attachments

  • Product price per kg FIFO allocation PB.xlsx
    39.4 KB · Views: 6
@Peter Bartholomew
...I don't know how to transfer the same to my actual file as it is having some kind of code inside the name manager. Sorry for my inability in the same.
You are perfectly correct that the 'code' is held within defined names. The apparent 'code' is simply worksheet formulas, but predominantly based upon very recent functions including Lambda. Even in Excel 2003, it was possible to write formulas and refer to them by name using Name Manager. It was, however, rarely done and the most familiar form was the Named Range where the formula just happens to be a range reference.

To transfer these complicated formulas from one workbook to another, one can copy a sheet or even just a formula from the first workbook to the second. The formulas will not work immediately because the range references to the active worksheet will no longer correspond to the data layout. In that situation, it is simply a case of working through the references one by one, changing the 'Refers to' property. I appreciate that modern Excel can be a major culture shock and hardly any users would yet claim to be fluent producing new solutions.
 
@Peter Bartholomew
Thank you so much for your post and the lambda function in detail. I will create the real data with an example set of data. then I will transfer the lambda to there. if I get stuck somewhere then I will ask the question by posting here.

Once again thanks for the lambda function which I am hearing for the first time and it's working perfectly.
 
=LET(q,M4:M8,p,Y4:Y8,s,Z4:Z8,sa,SCAN(0,s,LAMBDA(a,v,SUM(a,v)))-s,qa,SUM(q)-sa,
by,BYROW(SCAN(0,q,LAMBDA(a,v,a+v)),LAMBDA(v,LET(qa,v-sa,SUM(IF(qa>0,IF(qa>s,s,qa))*p)))),by-IFERROR(SMALL(by,SEQUENCE(ROWS(q),,0)),))
@Excel Wizard
I have transferred your suggested formula into my real working sheet where I have 34 RM (18 is Active) and above 40 products. all it is in Table format. I did some mistakes while transferring the formula. please see the below image and the attached file for the modification.

Expect the corrected formula as per tables and absolute reference to get the answer while adding the data in the tables.

79428
 

Attachments

  • Cost_Sheet_Chandoo.xlsb
    96.4 KB · Views: 9
Try


=LET(q,Consumption[Consumption RM2],p,Purchase[Price RM2],s,Purchase[Qty RM2],sa,SCAN(0,s,LAMBDA(a,v,SUM(a,v)))-s,qa,SUM(q)-sa,
by,BYROW(SCAN(0,q,LAMBDA(a,v,a+v)),LAMBDA(v,LET(qa,v-sa,SUM(IF(qa>0,IF(qa>s,s,qa))*p)))),INDEX(by-IFERROR(SMALL(by,SEQUENCE(ROWS(q),,0)),),ROWS(CJ$5:[@[Cost of RM2]])))
 

Attachments

  • Cost_Sheet_Chandoo.xlsb
    92.4 KB · Views: 29
Back
Top