Formula model for FIFO calculations

[riiiiiichhhhhhhhh]

Hi all,

I have been really struggling with a model that is able to calculate exchange FIFO based exchange rate differences. i could not find a way that does not make the model file to weight a ton (250MB). below is an explanation for what i am trying to acheive and at the bottom is a link to a file


FIFO

Charges are made on a daily basis and payments are made randomly in different days. Each payment made shall cover the earliest unpaid charge.

Example 1

A charge of 2 made on 01/01/2024, a charge of 2 made on 02/01/2024, and a payment of 3 made on 04/01/2024. The payment will first cover the charge of 2 made on 01/01/2024, and the remaining amount of payment of 1 will cover partially the charge of charge of 2 made on 02/01/2024.

Example 2

Example – a charge of 4 made on 01/01/2024, a charge of 2 made on 02/01/2024, a payment of 2 made on 01/01/2024, a payment of 3 made on 03/01/2024, and a payment of 1 made on 04/01/2024. The payment of 2 made on 02/01/2024 will cover 2 out of the 4 of the charge made on 01/01/2024. Out of the payment of 3 made on 03/01/2024, 2 will go to cover the remaining 2 of the charge made on 01/01/2024, and 1 of the payment of 3 made on 03/01/2024 will go to cover 1 out of the charge of 2 made on 02/01/2024. The payment of 1 made on 04/01/2024 will go to cover the remaining 1 out of the charge of 2 made on 02/01/2024.

FIFO with the addition of FX gain/loss

Each day has a rate. The rate of a payment will be the rate of the day in which the payment is made, and the rate of a charge will be the rate of the day in which the charge is made. The requirement is to calculate the exchange rate difference for each payment made against all the charges or portions of charges covered by this payment.

Steps of calculation for obtaining the result for a payment:

• Identify all charges or portion of charges covered by the payment
• Multiply each such charge or portion of such charge by its respective daily rate
• sum the results of 2
• multiply the payment by its respective daily rate
• deduct the result of 4 from the result of 3

Example 1

A charge of 2 made on 01/01/2024 with a daily rate of 4.3, a charge of 2 made on 02/01/2024 with a daily rate of 4.4, and a payment of 3 made on 04/01/2024 with a daily rate of 4.5.

Steps of calculation for obtaining the result for a payment:

• Identify all charges or portion of charges covered by the payment: the payment of 3 made on 04/01/2024 covers the charge of 2 made on 01/01/2024 and a portion of 1 out of the charge of 2 made on 02/01/2024.
• Multiply each such charge or portion of such charge by its respective daily rate: charge of 2 made on 01/01/2024 covered by the payment multiplied by the respective daily rate of the charge 2*4.3=8.6. portion of 1 out of the charge of 2 made on 02/01/2024 multiplied by the respective daily rate of the charge 1*4.4=4.4
• sum the results of 2: 4.4 + 8.6 = 13
• multiply the payment by its respective daily rate: 3*4.5 = 13.5
• deduct the result of 4 from the result of 3: 13.5 – 13 = 0.5

in the attached file, sheet “example” can show the above examples, and sheet “master” can show a model that automatically calculates the result, but only for up to 15 charges.

link to the file:

https://file.io/EX7JaiypHoBs

 

[Quiet_Nectarine_]

I can't open your file but I tried recreating and adding helper columns.
A1:G3 is Charge table, I1:N2 is Payment table,

I type this formula in G2:
=IF([@Accumulative]<MAX(Payment[Accumulative]),"Paid",[@Accumulative]-MAX(Payment[Accumulative]))

 

[riiiiiichhhhhhhhh]

Hi all,

I have been really struggling with a model that is able to calculate exchange FIFO based exchange rate differences. i could not find a way that does not make the model file to weight a ton (250MB). below is an explanation for what i am trying to acheive and at the bottom is a link to a file


FIFO

Charges are made on a daily basis and payments are made randomly in different days. Each payment made shall cover the earliest unpaid charge.

Example 1

A charge of 2 made on 01/01/2024, a charge of 2 made on 02/01/2024, and a payment of 3 made on 04/01/2024. The payment will first cover the charge of 2 made on 01/01/2024, and the remaining amount of payment of 1 will cover partially the charge of charge of 2 made on 02/01/2024.

Example 2

Example – a charge of 4 made on 01/01/2024, a charge of 2 made on 02/01/2024, a payment of 2 made on 01/01/2024, a payment of 3 made on 03/01/2024, and a payment of 1 made on 04/01/2024. The payment of 2 made on 02/01/2024 will cover 2 out of the 4 of the charge made on 01/01/2024. Out of the payment of 3 made on 03/01/2024, 2 will go to cover the remaining 2 of the charge made on 01/01/2024, and 1 of the payment of 3 made on 03/01/2024 will go to cover 1 out of the charge of 2 made on 02/01/2024. The payment of 1 made on 04/01/2024 will go to cover the remaining 1 out of the charge of 2 made on 02/01/2024.

FIFO with the addition of FX gain/loss

Each day has a rate. The rate of a payment will be the rate of the day in which the payment is made, and the rate of a charge will be the rate of the day in which the charge is made. The requirement is to calculate the exchange rate difference for each payment made against all the charges or portions of charges covered by this payment.

Steps of calculation for obtaining the result for a payment:

• Identify all charges or portion of charges covered by the payment
• Multiply each such charge or portion of such charge by its respective daily rate
• sum the results of 2
• multiply the payment by its respective daily rate
• deduct the result of 4 from the result of 3

Example 1

A charge of 2 made on 01/01/2024 with a daily rate of 4.3, a charge of 2 made on 02/01/2024 with a daily rate of 4.4, and a payment of 3 made on 04/01/2024 with a daily rate of 4.5.

Steps of calculation for obtaining the result for a payment:

• Identify all charges or portion of charges covered by the payment: the payment of 3 made on 04/01/2024 covers the charge of 2 made on 01/01/2024 and a portion of 1 out of the charge of 2 made on 02/01/2024.
• Multiply each such charge or portion of such charge by its respective daily rate: charge of 2 made on 01/01/2024 covered by the payment multiplied by the respective daily rate of the charge 2*4.3=8.6. portion of 1 out of the charge of 2 made on 02/01/2024 multiplied by the respective daily rate of the charge 1*4.4=4.4
• sum the results of 2: 4.4 + 8.6 = 13
• multiply the payment by its respective daily rate: 3*4.5 = 13.5
• deduct the result of 4 from the result of 3: 13.5 – 13 = 0.5

in the attached file, sheet “example” can show the above examples, and sheet “master” can show a model that automatically calculates the result, but only for up to 15 charges.

link to the file:

https://file.io/EX7JaiypHoBs

i am addint two more links in case the first is not working

https://file.io/aIsXejbBUu1O

https://fastupload.io/1ea10ff0c6d5b365

 

[riiiiiichhhhhhhhh]

I can't open your file but I tried recreating and adding helper columns.
A1:G3 is Charge table, I1:N2 is Payment table,

I type this formula in G2:
=IF([@Accumulative]<MAX(Payment[Accumulative]),"Paid",[@Accumulative]-MAX(Payment[Accumulative]))
View attachment 111556

thank you. unfortunately i don't think that's what i was looking for. i added links in a reply, perhaps you could take another look?

 

[Quiet_Nectarine_]

Hi, sorry for delayed reply. I have tried to create another table to do what you want. Not as elegant as I would like but at least it can go beyond 15 values.

https://fastupload.io/426bc31d4abb037b