Credit Limit Modeling

[ychbouki]

Hi,

I just started laying down the foundations for a small model that I am currently working on. It is to model purchases knowing that there is both a credit limit and payment terms for a supplier file. The main purpose of the model is to decide when to ask for a credit limit increase. I determined the seasonality of our spend by determining the seasonal indices associated with the last 10 years of our spending. The structure of the model should look like the one below. The main thing from this model is to determine the payout schedule based on the terms and on which month there is a risk of going over the credit limit. I hope that I was clear in what the model is supposed to do. So, I would appreciate for you to shed some light on how to better approach this model either through VBA or plain formulae.

Thank you,

Inputs:
Current Credit Limit	150,000
Payment Terms (in days)	50
Payment Terms in months	1.67
Payment Discounts	???
Discount Taken (Yes or No?)
Annual Buy	3,000,000
Daily Buy	8,333
Monthly Buy	250,000
Outputs:
Required Credit Limit (unadjusted for seasonality)	416,667
High Season Credit Limit	795,389
Credit Limit Increase Required	645,389
New Credit Limit Should be	795,389

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[glissa]

Inputs:
Current Credit Limit	150,000
Payment Terms (in days)	50
Payment Terms in months	1.67
Payment Discounts	???
Discount Taken (Yes or No?)
Annual Buy	3,000,000
Daily Buy	8,333
Monthly Buy	250,000
Outputs:
Required Credit Limit (unadjusted for seasonality)	416,667
High Season Credit Limit	795,389
Credit Limit Increase Required	645,389
New Credit Limit Should be	795,389

<tbody>
</tbody>

Hi,

Did you figure this one out?

I'm looking into the same problem from the opposite view, we need to see what credit limit our customer needs and compare to what they qualify for.

Our goal is to find the best combination of recomended credit limit, required credit limit, invoicing and payment terms.

// Ørjan