Hi everyone,
I have a task I can’t figure out in Board: I’m working with monthly stock data, where positive values represent items being added to stock (purchases), and negative values represent items being withdrawn (consumption or usage).
For each withdrawal, I need to calculate the total value using the FIFO (First In, First Out) logic, based on the historical purchase prices. Additionally, the quantities of each purchase batch should be updated after each withdrawal.
How can I implement this in Board?
DATE | Initial Stock | Volume added (+) or taken out (-) | Final Stock | Price |
|---|
Jan-25 | 0 | 50 | 50 | 15 €/unit |
Feb-25 | 50 | 100 | 150 | 30 €/unit |
Mar-25 | 150 | 40 | 190 | 28 €/unit |
Apr-25 | 190 | -90 | 100 | computed (FIFO) |
May-25 | 100 | -80 | 20 | computed (FIFO) |
Jun-25 | 20 | 60 | 80 | 10 €/unit |
How can I calculate the total economic value of the item sold (for the months with negative volume) using FIFO logic in Board?
This is an example of what I’ve been trying to recreate, but I haven’t been successful so far:
For example, in April-25, I have an initial stock of 190 units. If 90 units are withdrawn, I would:
- Take the first 50 units from the January-25 purchase at 15 €/unit (50 * 15 = 750 €).
- Then, take the remaining 40 units from the February-25 purchase at 30 €/unit (40 * 30 = 1200 €).Thus, the total value of the item withdrawn in April-25 would be 750 € + 1200 € = 1950 €.
Output Example for April-25:
- The total value of item sold = 1950 €.
- Remaining quantities after sale:
- January-25 batch: 0 units left.
- February-25 batch: 60 units remaining.
- Final stock: 100 units.
Thank you!
