calculating credit limit with payment terms

[LarsSJensen]

Hi all

i got a question where my Excel ability's are not good enough.

my setup is like this.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]V[/TD]
[TD]X[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]turn over[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Vat[/TD]
[TD]determent value 12[/TD]
[TD]determent value 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Customer Name[/TD]
[TD]debtor nr[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Jan[/TD]
[TD]Credit max in the system[/TD]
[TD]Calculated credit max[/TD]
[TD]Rounded to odd 100[/TD]
[TD]Payment Terms[/TD]
[TD]1,25[/TD]
[TD]2,4[/TD]
[TD]1,2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]xxxxxx[/TD]
[TD]xxxxxx[/TD]
[TD][TABLE="width: 101"]
<tbody>[TR]
[TD="class: xl66, width: 101"] 461.906,56[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]545547,54[/TD]
[TD]340851,82[/TD]
[TD]361979,26[/TD]
[TD]545131,28[/TD]
[TD]445432,88[/TD]
[TD]442010,84[/TD]
[TD]376223,91[/TD]
[TD]291560,17[/TD]
[TD]199202,61[/TD]
[TD]182573,6[/TD]
[TD]545,77[/TD]
[TD]1299000[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl66, width: 114"] 1.048.241,56[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl66, width: 118"] 1.049.000,00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]LM 30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in cell R3 =IF((((SUM(E3:P3)/12)*$Z$2)*$AA$2)>(((SUM(O3:P3)/2)*$Z$2)*$AB$2);(((SUM(E3:P3)/12)*$Z$2)*$AA$2);(((SUM(O3:P3)/2)*$Z$2)*$AB$2))
Formula in cell S3 =ISEVEN(CEILING(R3;1000)/1000)*1000+CEILING(R3;1000)


Okay, the thing is, atm the formula in cell R3 doesn't take in to account the Payment Terms which here is (see cell V3) LM30 which is current month plus 30 days. but i need to have this degree of detail in the formula.

there are several different payment terms i will list them below

LM08 = everything that is purchased with in that month plus 8 days, need to be payed on the 9th day (i hope it makes sense)
LM10
LM14
LM15
LM20
LM25
LM30
LM40
LM45
LM55
LM60
LM90

NET08= NET is the value of the single invoice needs to be payed within 8 days (i hope it makes sense)
NET14
NET20
NET25
NET30
NET45
NET60
NET90

it is possible to get the turnover from day to day, if that is needed to calculated this.

hope my question makes sense, and that you guys can help me.

 

[Rickmaurinus]

Hi Lars,

I'm trying to figure this out.

First of all, there was a mistake in the formula you mentioned. It didn't add up to anything. The formula in your post refers to cell Z for the VAT. This should be Cell X
So the formula is:

=IF(   (((SUM(E3:P3) /12)  *  $X$2)  *  $AA$2)  >  (((SUM(O3:P3) / 2) * $X$2) * $AB$2);
       (((SUM(E3:P3) /12)  *  $X$2)  *  $AA$2);
       (((SUM(O3:P3) / 2)  *  $X$2)  *  $AB$2))

Now perhaps you can help me out a little. What is the underlying logic of calculating the credit Max. If it is 30 days, or 8 days. How does that logically impact the credit max within your company? You must have some assumptions there...

Also, how have you calculated the "Determent value 12" and "determent value 2", and what do those mean? Once I know a little more about the logic, perhaps me (or someone else) can come up with something.


Regards,
Rick

 

[LarsSJensen]

Hi

Yeah i can see there was an error xD it should be cell X

the underlying logic is to make the creditmax static, so we dont need to adjust it manually, and there by not having safe credit max (we have over 5000 customers, and we cant manually change there credit max with the seasons (we sell more in the summer then winter)).


the determent value isnt calculated, it is something we use in the company, its regarding giving the customer some extra credit, so the customer doesnt go on hold every month.

 

[Rickmaurinus]

Hi,

I'm guessing you have an idea how 8 days payment term, or 30 days payment term impacts the total credit max you give to your clients.

Please give me a mathematical example of how that works. If I fill in 30 days, how much should the credit max be? Or when I fill in 8 days payment term, how much will it be then?
I don't there there is a generic rule to how that impacts the credit max. This is company specific. Perhaps you would like to charge extra if the client pays late, or you would give a discount when they pay with a short payment term. I would need some more info to think of an excel solution.

Regards,
Rick

 

[LarsSJensen]

Hi

if customer X purchases every day, for the value of 10.

then if his payment terms is 8 days, then it should be 80 (plus the vat and a new determent value) and if it was 30 days, then it should be 300 (plus the vat and a determent value)