@arbe thank you so much for posting the Continental Extension with new FIFO LIFO utility node back in December 2024. The node is elegantly simple and works great! One follow-up question for you, in order for FIFO inventory valuation to work for accounting purposes (aka FIFO layers), we need the output table to pull in the cost basis and fair market value fields from the CSV node that is feeding into the LIFO Resolver node. I could perhaps do a lookup by RowIN back to the source file, unless you can help explain how I might add additional output columns to the FIFO Resolver node? Thank you!
Hi @gburleigh ,
great to see that the node is fit for your use case!
Your idea is exactly right. We wanted to keep the node simple and fit for purpose. If you want to bring in additional information like cost basis, you could use the Joiner node based on RowID of the source table and RowId_IN column of the FIFO Resolver node.
Best regards
Arne
2 Likes
Hi @arbe,
The node is very interesting. Thank you very much for sharing it with the KNIME community. Unfortunately, it doesn’t seem to cover what I would consider a fairly frequent use case in the world of finance. Would it be possible to tweak the node so that it can also handle overshooting outputs with subsequent inputs, if so desired (user checkbox)? Excess outputs currently result in Phantom rows. The excess output is disregarded in the subsequent sequence. It is not compensated by subsequent inputs, but should. In the world of finance, it is very well possible to sell more than you have and close the short position afterwards and the order in which short positions are offset matters. If they are left open, the whole subsequent chain is off because the following input transactions are deemed settled by completely different (subsequent) output items from the chain rather than by preceding (Phantom) excess outputs, which can’t remain open. An excess output is an input of a corresponding short position. For as long as the open position is negative, the logic should flip signs (input = minus, output = plus). Such additional functionality would be awesome.
Thank you.
Best regards
RoBex7
Hi @RoBex7,
Thanks a lot for your feedback and this idea. I tried to keep the node as lean as possible. The FIFO topic is complex enough, so I believe too many options scare away the users. So far, I always succeeded to achieve what I intended with simple workarounds. Doesn’t the same apply to your use case? If I understand your concern correctly, you could achieve what you want by adding a fix offset like 1,000,000,000 to your quantity column, then apply the FIFO node, then substract the offset again. The node will not introduce phantom positions if the offset is high enough. You can identify the short positions, if needed, by observing wehtehr a cumulative sum falls underneath the offset value.
Best regards
Arne
2 Likes
Hi Arne,
I’m afraid, the suggested solution doesn’t work. Can you please have a look?
To illustrate the scenario, I created a small example (table to solve), then connected it to your FIFO Resolver. Phantom Rows resulting from an negative running total balance, if any, are not offset against anything in the FIFO chain (but should be).
The idea to increase the start value (if I understood you correctly) helps to avoid Phantom Rows (no overall negative running total), but also results in a wrong FIFO chain (even before the 1st Phantom row already), because the order in which open positions are (deemed) settled is modified by the ingoing adjustment.
This is shown in the tab FIFO_Adjusted Start.
I also added a tab which the expected FIFO solution with input table to solve on the left, expected solution (manual) in the middle and Pivot tables to check overall consumption of Row_IN and Row_OUT values respectively on the right.
The expected solution differs a lot from the output of the FIFO resolver (without/with start value adjustment).
The strict sequence of when and how positive or/and negative positions are offset matters a lot. In the real word, this could for example be lots in foreign currencies for which FX gains and losses must be computed using FX rates valid at individual transaction and (partial ) lot settlement dates. Thus, the exact right allocation is key.
Unfortunately, I myself can’t yet do this in KNIME. I can send the first IN and OUT transaction, calculate the amount which can be offset, know from which chain IN vs OUT I should pull the next row into the loop and how much residual is left from the other side and increment the flow variable accordingly, but something must be wrong with the configuration of the recursive loop. Can’t (yet) get it to loop through the entire IN and OUT chain.
FIFO.xlsx (69.7 KB)
Thank you.
Best regards,
RoBex7
Hi @RoBex7 , you are right, my initial idea with an offset won’t work in your case. I had a logistics use case once where I needed to run the FIFO node in LIFO mode first to fix correction bookings. Then I could run the intended FIFO. But I would need to think your case through more thorougly, whether this can help here. My feeling is, that there is an elegant solution to your problem with the node as is. But I’m sorry I don’t have the time to invest in this before the holidays next week.