Challenge #479: Raw Material Lot Allocation

[BS_THE_ANALYST]

Really hard but enjoyable question. For the FIFO principle, I assumed that the sort order would be alphabetical (by label, especially when we consider things like employee ID in the real world). Will state logic used in spoiler.

I'm confident in my answer.

Spoiler

Time Taken: 35:19

Rough logic: if available, take all resource and move to next resource if more required. If total resources is not enough, absorb the entire resources. Used a multi-row to implement this.

Macro

Workflow

 

 

[Bobbyt23]

Like others I am a bit confused by the requirements in this challenge. I'm wondering if the qty and sub qty in the finished goods table need something doing with them.

[alineruizcampos]

Spoiler

My solution was different than the solution given. Did this based on my understanding of FIFO :)

[abacon]

Definitely took some work but I got the better of it.

Spoiler

MacroMain Workflow

[Qiu]

I am quite confident with my answer.
Ex: Finished good Lot "L1" requires  "RAW_1" in Quantity of 50
But the anwser gives 25 and 50 from lot A and B, adding up to 75?

Spoiler

[Qiu]

@Pilsner 
We are having the same answer. 😀

[Pilsner]

@Qiu Nice! Hopefully, that means we have understood it correctly 😄. Like you, I was confused about the 25+50 = 75.

I like your use of the running total tool too, I always forget about it, but it's very useful. 

[abacon]

Here is how I understood it - each Finished Good Lot could not exceed a maximum TOTAL Raw Materials amount equaling the QTY field (in the FG Table) but EACH specific RM could not exceed the SUB QTY amount. Meaning FG LOT 1 had 25 from Lot A, 50 from Lot B. However, Lot B had more RM left meaning is has to be used next. Therefore, it had to move to FG LOT 2.

Not sure if this is the correct application of FIFO but it got me the right answer.

See my original post for the macro used to get this.

Hope this helps!

Bacon

Spoiler

[Qiu]

@abacon 
I guess we need some clarification from the author.

And my logic is based on the statement below.

[abacon]

@Qiu I totally agree, definitely needs som clarification. I wanted to detail how I got the answer to match, even if it might not be correct FIFO allocation.

Bacon