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
