GeordieKiwi
New Member
- Joined
- Jan 8, 2018
- Messages
- 2
Hi guys
I have a formula question I’ve been working on for a few days now and can’t seem to get my head round it, was hoping for some help.
I basically have 4 customers, and we have invoices/credits due in 6 time buckets 1D though to 90+D. If at a total level I owe the customer money I pay the appropriate amount assigned to that time bucket, however if the customer owes me money at a total level they will pay me that as soon as they owe me it at a total level. So both the customer and I pay on settlement date, unless at a net level the customer owes me money, then they will pay in advance.
i.e. For Customer C at a total they owe me 4m so will pay that on Day 1.
It then gets a more complex as we run through the buckets, for example Customer B in the 10 Day bucket I owe them 44m, but at that point at a total level they now owe me 18m so the net result is a 25m negative.
Essentially there are 4 scenarios:
I hope that makes sense, really appreciate the input and thoughts guys,
[TABLE="width: 776"]
<colgroup><col width="281" style="width: 211pt; mso-width-source: userset; mso-width-alt: 10276;"> <col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;"> <col width="105" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;" span="5"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <tbody>[TR]
[TD="class: xl65, width: 500, bgcolor: transparent, colspan: 3"] Money to be paid/received based on settlement date [/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 112, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Row Labels [/TD]
[TD="class: xl66, bgcolor: transparent"] 1D [/TD]
[TD="class: xl66, bgcolor: transparent"] 2D [/TD]
[TD="class: xl66, bgcolor: transparent"] 10D [/TD]
[TD="class: xl66, bgcolor: transparent"] 30D [/TD]
[TD="class: xl66, bgcolor: transparent"] 90D [/TD]
[TD="class: xl66, bgcolor: transparent"] 90+D [/TD]
[TD="class: xl66, bgcolor: transparent"] Grand Total [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer A [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 62,207.65 [/TD]
[TD="class: xl66, bgcolor: transparent"] (19,557,160.16)[/TD]
[TD="class: xl66, bgcolor: transparent"] (21,369,901.82)[/TD]
[TD="class: xl66, bgcolor: transparent"] (40,864,854.33)[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer B [/TD]
[TD="class: xl66, bgcolor: transparent"] 324,911.11 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (44,640,210.80)[/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 18,738,457.69 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (25,576,842.00)[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer C [/TD]
[TD="class: xl66, bgcolor: transparent"] 11,220.44 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (7,658,005.75)[/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 11,848,935.49 [/TD]
[TD="class: xl66, bgcolor: transparent"] 221,205.19 [/TD]
[TD="class: xl66, bgcolor: transparent"] 4,423,355.37 [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer D [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (453,096.00)[/TD]
[TD="class: xl66, bgcolor: transparent"] 35,740,680.10 [/TD]
[TD="class: xl66, bgcolor: transparent"] 2,230,689.30 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (3,621,924.15)[/TD]
[TD="class: xl66, bgcolor: transparent"] 33,896,349.25 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] Desired Result [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Row Labels [/TD]
[TD="class: xl66, bgcolor: transparent"] 1D [/TD]
[TD="class: xl66, bgcolor: transparent"] 2D [/TD]
[TD="class: xl66, bgcolor: transparent"] 10D [/TD]
[TD="class: xl66, bgcolor: transparent"] 30D [/TD]
[TD="class: xl66, bgcolor: transparent"] 90D [/TD]
[TD="class: xl66, bgcolor: transparent"] 90+D [/TD]
[TD="class: xl66, bgcolor: transparent"] Grand Total [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer A [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 62,207.65 [/TD]
[TD="class: xl66, bgcolor: transparent"] (19,557,160.16)[/TD]
[TD="class: xl66, bgcolor: transparent"] (21,369,901.82)[/TD]
[TD="class: xl66, bgcolor: transparent"] (40,864,854.33)[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer B [/TD]
[TD="class: xl66, bgcolor: transparent"] 324,911.11 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (25,901,753.11)[/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (25,576,842.00)[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer C [/TD]
[TD="class: xl66, bgcolor: transparent"] 4,423,355.37 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 4,423,355.37 [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer D [/TD]
[TD="class: xl66, bgcolor: transparent"] 33,896,349.25 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 1,391,234.85 [/TD]
[TD="class: xl66, bgcolor: transparent"] 2,230,689.30 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (3,621,924.15)[/TD]
[TD="class: xl66, bgcolor: transparent"] 33,896,349.25 [/TD]
[/TR]
</tbody>[/TABLE]
I have a formula question I’ve been working on for a few days now and can’t seem to get my head round it, was hoping for some help.
I basically have 4 customers, and we have invoices/credits due in 6 time buckets 1D though to 90+D. If at a total level I owe the customer money I pay the appropriate amount assigned to that time bucket, however if the customer owes me money at a total level they will pay me that as soon as they owe me it at a total level. So both the customer and I pay on settlement date, unless at a net level the customer owes me money, then they will pay in advance.
i.e. For Customer C at a total they owe me 4m so will pay that on Day 1.
It then gets a more complex as we run through the buckets, for example Customer B in the 10 Day bucket I owe them 44m, but at that point at a total level they now owe me 18m so the net result is a 25m negative.
Essentially there are 4 scenarios:
- The Remaining Total is always greater than 0 i.e. the customer owes me money (pays it all on Day 1)
- The remaining Total is always less than 0 i.e. I owe the customer money (I pay in the appropriate time bucket)
- The remaining Total moves from less than 0 to greater than 0 i.e. I owed the customer money and now the customer owes me money (I pay in the appropriate time bucket, the customer pays me the money in the time bucket as soon as the remaining total is greater than 0)
- The remaining Total moves from greater than 0 to less than 0 i.e. the customer owed me money (pays it on Day 1) and then I owe the customer money (and pay it in the appropriate time bucket)
I hope that makes sense, really appreciate the input and thoughts guys,
[TABLE="width: 776"]
<colgroup><col width="281" style="width: 211pt; mso-width-source: userset; mso-width-alt: 10276;"> <col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;"> <col width="105" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;" span="5"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 4096;"> <tbody>[TR]
[TD="class: xl65, width: 500, bgcolor: transparent, colspan: 3"] Money to be paid/received based on settlement date [/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 105, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 112, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Row Labels [/TD]
[TD="class: xl66, bgcolor: transparent"] 1D [/TD]
[TD="class: xl66, bgcolor: transparent"] 2D [/TD]
[TD="class: xl66, bgcolor: transparent"] 10D [/TD]
[TD="class: xl66, bgcolor: transparent"] 30D [/TD]
[TD="class: xl66, bgcolor: transparent"] 90D [/TD]
[TD="class: xl66, bgcolor: transparent"] 90+D [/TD]
[TD="class: xl66, bgcolor: transparent"] Grand Total [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer A [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 62,207.65 [/TD]
[TD="class: xl66, bgcolor: transparent"] (19,557,160.16)[/TD]
[TD="class: xl66, bgcolor: transparent"] (21,369,901.82)[/TD]
[TD="class: xl66, bgcolor: transparent"] (40,864,854.33)[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer B [/TD]
[TD="class: xl66, bgcolor: transparent"] 324,911.11 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (44,640,210.80)[/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 18,738,457.69 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (25,576,842.00)[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer C [/TD]
[TD="class: xl66, bgcolor: transparent"] 11,220.44 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (7,658,005.75)[/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 11,848,935.49 [/TD]
[TD="class: xl66, bgcolor: transparent"] 221,205.19 [/TD]
[TD="class: xl66, bgcolor: transparent"] 4,423,355.37 [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer D [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (453,096.00)[/TD]
[TD="class: xl66, bgcolor: transparent"] 35,740,680.10 [/TD]
[TD="class: xl66, bgcolor: transparent"] 2,230,689.30 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (3,621,924.15)[/TD]
[TD="class: xl66, bgcolor: transparent"] 33,896,349.25 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] Desired Result [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Row Labels [/TD]
[TD="class: xl66, bgcolor: transparent"] 1D [/TD]
[TD="class: xl66, bgcolor: transparent"] 2D [/TD]
[TD="class: xl66, bgcolor: transparent"] 10D [/TD]
[TD="class: xl66, bgcolor: transparent"] 30D [/TD]
[TD="class: xl66, bgcolor: transparent"] 90D [/TD]
[TD="class: xl66, bgcolor: transparent"] 90+D [/TD]
[TD="class: xl66, bgcolor: transparent"] Grand Total [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer A [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 62,207.65 [/TD]
[TD="class: xl66, bgcolor: transparent"] (19,557,160.16)[/TD]
[TD="class: xl66, bgcolor: transparent"] (21,369,901.82)[/TD]
[TD="class: xl66, bgcolor: transparent"] (40,864,854.33)[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer B [/TD]
[TD="class: xl66, bgcolor: transparent"] 324,911.11 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (25,901,753.11)[/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (25,576,842.00)[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer C [/TD]
[TD="class: xl66, bgcolor: transparent"] 4,423,355.37 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 4,423,355.37 [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] Customer D [/TD]
[TD="class: xl66, bgcolor: transparent"] 33,896,349.25 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] 1,391,234.85 [/TD]
[TD="class: xl66, bgcolor: transparent"] 2,230,689.30 [/TD]
[TD="class: xl66, bgcolor: transparent"] - [/TD]
[TD="class: xl66, bgcolor: transparent"] (3,621,924.15)[/TD]
[TD="class: xl66, bgcolor: transparent"] 33,896,349.25 [/TD]
[/TR]
</tbody>[/TABLE]