Forum Discussion
Map invoice and receipt on FIFO basis
Hi, I have tried to understand your task and have developed a formula that works under Office 365. But I'm not sure if it fits.
| Document No | Document Date | Document Totals | Adjusted Amount | Adjusted Document No | Adjusted Date |
| 1 | 11/26/22 | 737429 | |||
| 2 | 11/30/22 | 809893 | |||
| 3 | 01/27/23 | 281682 | |||
| 4 | 01/31/23 | 348288 | |||
| 5 | 03/31/23 | 599277 | |||
| -400000 | 6 | 04/03/23 | |||
| 7 | 04/05/23 | 200000 | |||
| -162594 | 8 | 04/06/23 | |||
| -400000 | 9 | 04/17/23 | |||
| -9299.81 | 10 | 04/21/23 | |||
| -100000 | 11 | 04/24/23 | |||
| -100000 | 12 | 04/26/23 | |||
| -100000 | 13 | 05/04/23 |
=LET(
data,SORT(A2:C14,2),
totals,C2:C14,
VSTACK(
HSTACK(A1:C1,"Adjusted Amount","Adjusted Document No","Adjusted Date"),
HSTACK(IF(totals>0,data,""),CHOOSECOLS(IF(totals<0,data,""),3,1,2)
)
)
)
Hi dscheikey
Thanks you so much for your efforts, but as per output in your sheet, positives and negatives columns have been separated, but my desired output is to map negative amounts against positive amounts on FIFO basis, each negative line item needs to be mapped against a positive line to the extent of positive amount on FIFO basis in the way below
Regards
Sandeep Daga
I'm sorry. I don't understand it. Maybe you could explain what FIFO is first. I would have translated it as First in First out. Is that correct? Why does position number 9 exist twice? Once positive and once negative? What do you mean mapping? Number 9 is -400000 in the first List! Please explain the logic of your comparison in more detail.
- Hi dscheikey,
So in first case Positive amount is 737429 and its document no. is 1.
Now we want to adjust negative amount with positive amount on First in First out basis.
So, the 1st negative value is of 400000 and its corresponding document number is 6, so it 1st mapped against 737429
Now remaining positive balance is (737429-400000) 337429, the second negative value is of 162954, which is now mapped against 737429, but in 2nd line with its document number and date.
After adjustment of above balance amount is (737429-400000-162954) 174475, the next negative amount is of 400000, but the maximum adjustment can be made up to 174475, so the mapping of of 174475 is mapped against 737429 in 3rd line with document number and date of 3rd negative amount.
Now Since the 1st positive value has been exhausted, now we will move to 2nd Positive amount, the 2nd positive amount is of 809893, now we will map the remaining negative value of (400000-174475) 225525 with document number as 9 and its date, as 225525 is balance against document number 8 of 400000.
And so on....
Let me know, if you have any query.
Regartds
Sandeep dagaNow I've understood half of it. I still have 2 problems. You have a transposed number. In your table the value is -162594, in your description you write -162954. In addition, the value 225525 does not appear in your table.
Honestly, that's too high for me. I'm not an accountant. I also wouldn't know how to implement this with Excel. I'm not smart enough for that.