Map invoice and receipt on FIFO basis

Question

Hi, i am attaching a sample data below, in which i have amounts in Positive as Invoices raised and Negative Values as Amount received, now i want to map receipts against invoice documents on FIFO basis.

format of desired output is as below.

Kindly help.

dscheikey · Answer

SandeepDaga&nbsp;&nbsp;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.&nbsp;Document NoDocument DateDocument TotalsAdjusted AmountAdjusted Document NoAdjusted Date111/26/22737429&nbsp;&nbsp;&nbsp;211/30/22809893&nbsp;&nbsp;&nbsp;301/27/23281682&nbsp;&nbsp;&nbsp;401/31/23348288&nbsp;&nbsp;&nbsp;503/31/23599277&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-400000604/03/23704/05/23200000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-162594804/06/23&nbsp;&nbsp;&nbsp;-400000904/17/23&nbsp;&nbsp;&nbsp;-9299.811004/21/23&nbsp;&nbsp;&nbsp;-1000001104/24/23&nbsp;&nbsp;&nbsp;-1000001204/26/23&nbsp;&nbsp;&nbsp;-1000001305/04/23&nbsp;&nbsp;=LET(
 data,SORT(A2:C14,2),
 totals,C2:C14,
   VSTACK(
      HSTACK(A1:C1,"Adjusted Amount","Adjusted Document No","Adjusted Date"),
      HSTACK(IF(totals&gt;0,data,""),CHOOSECOLS(IF(totals&lt;0,data,""),3,1,2)
      )
   )
)&nbsp;&nbsp;

sandeepdaga · Answer

&nbsp;Hi&nbsp;dscheikey&nbsp;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&nbsp;&nbsp;RegardsSandeep Daga&nbsp;&nbsp;

dscheikey · Answer

SandeepDaga&nbsp;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.&nbsp;

sandeepdaga · Answer

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 737429Now 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.RegartdsSandeep daga

dscheikey · Answer

SandeepDaga&nbsp;Now 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.&nbsp;&nbsp;

Forum Discussion

Map invoice and receipt on FIFO basis

6 Replies

Resources

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