Soldato
You know who you are, one of those people who can just whip up a formula in next to no time that works everything out and displays the correct info with next to no effort. Blooming witchcraft if you ask me, but anyway please can you help.
We're trying to limit our exposure to debt at work and I've come up with the following solution which we're going to trial but I've quickly become out of my depth when setting up a spreadsheet for a few of our guys to use to see if the solution is any good.
This is the challenge:
A customer paying by direct debit wants to amend their annual contract mid term, at the moment any additional cost is added to the remaining dd plan, which often works fine but in many cases people who have a large amount added towards the end of their contract end up defaulting and their contract is cancelled as a result, leaving a large debt which we then need to chase.
Here's the solution I want to trial:
Contract is less than 60 days old = We take 15% of the AP on a card and remainder goes to dd
60 -120 days old = 30% on card and remainder to dd
120 - 180 days old = 40% on card and remainder to dd
180 days plus = 50% on card and remainder to dd
Now here's where I need help. I have started a simple spreadsheet in which the user enters the contract expiry date, then the date of the amendment and the remaining number of days is displayed. I then have another cell where the user enters the AP but I need excel to take the days remaining into account, apply the relevant percentage and display the deposit amount which needs to be taken from the customer on a card.
If any excel sorcerers aren't busy daubing themselves in chicken blood I'd really appreciate some help.
Thanks
We're trying to limit our exposure to debt at work and I've come up with the following solution which we're going to trial but I've quickly become out of my depth when setting up a spreadsheet for a few of our guys to use to see if the solution is any good.
This is the challenge:
A customer paying by direct debit wants to amend their annual contract mid term, at the moment any additional cost is added to the remaining dd plan, which often works fine but in many cases people who have a large amount added towards the end of their contract end up defaulting and their contract is cancelled as a result, leaving a large debt which we then need to chase.
Here's the solution I want to trial:
Contract is less than 60 days old = We take 15% of the AP on a card and remainder goes to dd
60 -120 days old = 30% on card and remainder to dd
120 - 180 days old = 40% on card and remainder to dd
180 days plus = 50% on card and remainder to dd
Now here's where I need help. I have started a simple spreadsheet in which the user enters the contract expiry date, then the date of the amendment and the remaining number of days is displayed. I then have another cell where the user enters the AP but I need excel to take the days remaining into account, apply the relevant percentage and display the deposit amount which needs to be taken from the customer on a card.
If any excel sorcerers aren't busy daubing themselves in chicken blood I'd really appreciate some help.
Thanks
