B
bootsy
Ok, thanks very much. Here goes.
1) The ONLY thing this spreadsheet needs to do is to calculate th
payment for THIS quarter. By that, I mean that what happens in th
future doesn't matter. What has happened in the past only matters t
the extent that it will determine what commission gets paid for thi
quarter (if this quarter is in the 1st year, then 20%, if this quarte
is in the second year, then 10% commission, if this quarter is in th
3rd, 4th, 5th year etc, then commission is 1%).
2) So, what I have been trying to do is set up a calculation that says
ok, this person started on X date, how much do we owe a sales rep fo
this quarter. That's pretty easy if the quarter is entirely withi
year 1, or year 2, or year 3. But, what is hard is if the quarter i
split in two, here's a couple of examples.
Example 1:
Joe invests $100K on January 1,2004. We are in the quarter endin
March 31, 2005, and trying to figure out how much to pay the sales rep
Well, this quarter is entirely in Joe's 2nd year, so we pay 10%, o
$10K. Easy.
But, how about Jane? Jane invests on January 15, 2004. We are in th
quarter ending March 31, 2005 - so, the sales rep gets 15 days of yea
1 commission at 20%, and 2.5 months at 10%.
That's the hard part.
Well, I should say, that's ONE of the hard parts. Making it pretty an
easy is another part, but the calculations are the key.
I'm actually just trying to help out my accountant friend, who think
i'm an excel guru. I told him hours ago that 'I give up', but I'v
been in front of the computer off and on all day trying to figure i
out. Now I am just mad ;-) that I can't beat it. I've even gone t
trying brute force (month by month by month), but it's not working
Anyway, if you can figure this out, his boss will make a nice donatio
to the site in your name! Or at least, I hope he will, and if not
will.
Further question - here or (e-mail address removed)
And thanks again.
Just realized I never did the pull out part - yeah, so say Jane abov
pulled out on January 20, 2005. So for this quarter (ending March 31
2005), sales rep would get 15 days at 20%, and 5 days at 20%
1) The ONLY thing this spreadsheet needs to do is to calculate th
payment for THIS quarter. By that, I mean that what happens in th
future doesn't matter. What has happened in the past only matters t
the extent that it will determine what commission gets paid for thi
quarter (if this quarter is in the 1st year, then 20%, if this quarte
is in the second year, then 10% commission, if this quarter is in th
3rd, 4th, 5th year etc, then commission is 1%).
2) So, what I have been trying to do is set up a calculation that says
ok, this person started on X date, how much do we owe a sales rep fo
this quarter. That's pretty easy if the quarter is entirely withi
year 1, or year 2, or year 3. But, what is hard is if the quarter i
split in two, here's a couple of examples.
Example 1:
Joe invests $100K on January 1,2004. We are in the quarter endin
March 31, 2005, and trying to figure out how much to pay the sales rep
Well, this quarter is entirely in Joe's 2nd year, so we pay 10%, o
$10K. Easy.
But, how about Jane? Jane invests on January 15, 2004. We are in th
quarter ending March 31, 2005 - so, the sales rep gets 15 days of yea
1 commission at 20%, and 2.5 months at 10%.
That's the hard part.
Well, I should say, that's ONE of the hard parts. Making it pretty an
easy is another part, but the calculations are the key.
I'm actually just trying to help out my accountant friend, who think
i'm an excel guru. I told him hours ago that 'I give up', but I'v
been in front of the computer off and on all day trying to figure i
out. Now I am just mad ;-) that I can't beat it. I've even gone t
trying brute force (month by month by month), but it's not working
Anyway, if you can figure this out, his boss will make a nice donatio
to the site in your name! Or at least, I hope he will, and if not
will.
Further question - here or (e-mail address removed)
And thanks again.
Just realized I never did the pull out part - yeah, so say Jane abov
pulled out on January 20, 2005. So for this quarter (ending March 31
2005), sales rep would get 15 days at 20%, and 5 days at 20%