Challenge #479: Raw Material Lot Allocation

[AYXAcademy]

 

Hi Community members,

A solution to last week’s challenge can be found here. 

This challenge was submitted by Casey Koopmans @cwkoops. Thank you, Casey, for this interesting challenge!

In global supply chain operations, duty drawback programs allow exporters to reclaim duties paid on imported raw materials once the resulting finished goods are exported. To comply with such programs, companies must maintain traceability between the raw materials used and the finished goods produced.

For this challenge, imagine you work with a manufacturer that processes raw materials into finished goods. Each raw material is received in lots, and the manufacturer needs to trace how much of each raw material lot is consumed in each finished good lot, following a strict First In, First Out (FIFO) usage policy.

Your task is to develop a FIFO allocation model that maps the consumption of raw material lots to finished good lots, enabling full traceability.

You are provided with two datasets:

a.        Raw Material Lots: a table that lists all the raw material lots received:

b.        Finished Good Requirements: a table that lists each finished good lot and its corresponding raw material requirements:

Each finished good lot consumes a certain amount of a specific raw material type (SUB), which must be traced back to specific raw material lots.

Your task for this challenge:

Using FIFO allocation, build an iterative macro that helps determine which raw material lots are consumed by each finished good lot, and how much of each lot is used. To do so, consider the following:

• Group raw material lots by ITEM (raw material type) and sort them in the order received.
• Iterate through each finished good lot in the order produced.
• For each finished good lot:
     - Identify the required SUB (raw material type) and SUB QTY (quantity required).
     - Allocate this quantity using the earliest available raw material lots (FIFO).
     - Deduct the used quantity from raw lot availability and record which lots were used and how much.
• Output a table that shows:
     - Finished Good Lot ID (FG LOT)
     - Raw Material Type (SUB)
     - Raw Material Lot (RAW LOT)
     - Quantity Used (QTY USED)

Source:

• Sample Data set provided in the workflow as text inputs.

Good luck!

The Academy Team

Download Start File

Download Solution File

[Pilsner]

Definitely a tough one this week! My answer didn't quite match the output provided, so I'm interested to see others' solutions. 

Spoiler

I may have misunderstood the question, but I effectively treated the [SUM QTY] as the amount required to fill each bin [FG LOT].

Macro:
 

 
Time Taken: 00:40:14

[Louie-Horn]

Very difficult this week, I think I've missed something as my solution doesn't exactly match the output data as mine returns the amount of each that is required per FG (rather than the cumulative data)... oh well, I'm happy that I eventually figured something out :) 

Spoiler

 

 

[Kenda]

Spoiler

Went with the no macro route 😅 there definitely could be holes poked in this, but it works for this data set. I agree with others that it seems as though C1 from RAW_2 could be pulled from LOT_1, not LOT_2 as the given solution suggests.

[gawa]

I must have missed some context of FIFO. Couldn't get my answer matched.

Spoiler

As we have only two type of Items, generating rows so each record represents "1" material, and let them 'positionally' join for each item. By doing so, unused materials are dropped from R anchor of the Join tool.
I admit it does not work if we have 3 or more(dynamic) items, compromising about generalizability.

In my understanding on FIFO, I got this result. Wanna see how others solve this puzzle.

[RWvanLeeuwen]

I'm having a hard time interpreting the assignment so I'll revisit this challenge later to try and learn what is required here. The QTY and SUBQTY fields seem a little bit of an acquired taste, but perhaps I can understand what is happening in the real world when revisiting this

[DaisukeTsuchiya]

Since I couldn't fully understand the problem statement, I created a workflow that matches the Output as closely as possible.

Spoiler

I implemented logic to skip assigment once the SUB QTY is fulfilled by LOT of the raw material. However, with this logic, the final assignment ends up being LOT_3.

Could someone please advise on the correct approach? 
I'm still unclear on how the QTY in the Finished Good Requirements should be properly utilized.

[alexnajm]

Based on the discussion I am going to wait for either the updated prompt/file or someone to get it on the nose! 😊

Tagging @AYXAcademy for awareness

[balajilolla2]

Spoiler

Got close to the output except for the last record which is being allocated to Lot_3

[Bennu]

You and me both Alex!  The Fields names and table relationships are confusing and I'm not even sure what exactly is being asked. 😕