# Credit Limit Modeling

#### ychbouki

##### New Member
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>

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
 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

1,203,259
Messages
6,054,415
Members
444,724
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back