Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 28th 04, 05:02 AM
bootsy
 
Posts: n/a
Default Calculating commission schedule (will pay for help!)


Ok, thanks very much. Here goes.

1) The ONLY thing this spreadsheet needs to do is to calculate the
payment for THIS quarter. By that, I mean that what happens in the
future doesn't matter. What has happened in the past only matters to
the extent that it will determine what commission gets paid for this
quarter (if this quarter is in the 1st year, then 20%, if this quarter
is in the second year, then 10% commission, if this quarter is in the
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 for
this quarter. That's pretty easy if the quarter is entirely within
year 1, or year 2, or year 3. But, what is hard is if the quarter is
split in two, here's a couple of examples.

Example 1:

Joe invests $100K on January 1,2004. We are in the quarter ending
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%, or
$10K. Easy.

But, how about Jane? Jane invests on January 15, 2004. We are in the
quarter ending March 31, 2005 - so, the sales rep gets 15 days of year
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 and
easy is another part, but the calculations are the key.

I'm actually just trying to help out my accountant friend, who thinks
i'm an excel guru. I told him hours ago that 'I give up', but I've
been in front of the computer off and on all day trying to figure it
out. Now I am just mad ;-) that I can't beat it. I've even gone to
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 donation
to the site in your name! Or at least, I hope he will, and if not I
will.

Further question - here or

And thanks again.


Just realized I never did the pull out part - yeah, so say Jane above
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%.


--
bootsy
------------------------------------------------------------------------
bootsy's Profile:
http://www.excelforum.com/member.php...o&userid=15795
View this thread: http://www.excelforum.com/showthread...hreadid=273006


  #2   Report Post  
Old October 28th 04, 05:09 AM
Celtic_Avenger
 
Posts: n/a
Default


As far as I can see, it will still need to be first calcluated month by
month, for the spreadsheet to be able to determine which quarter has
which months within it. Then the commission can be worked out
correctly.

Give me the weekend. and I will post you a reply that will work. got to
fit it around other projects i'm involved with.

But it should not be a problem.

I could also then just send you an attachment (Virus Free I promise)
with the example spreadsheet!

I will reply soon!

Take Care For Now!

Celtic_Avenger.

PS. Where are you from? Country wise?


--
Celtic_Avenger
------------------------------------------------------------------------
Celtic_Avenger's Profile: http://www.excelforum.com/member.php...o&userid=14101
View this thread: http://www.excelforum.com/showthread...hreadid=273006



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I would like to make a schedule for my dept. Who works what days . Chiki Charts and Charting in Excel 1 December 9th 04 12:19 AM
I would like to make a schedule for my dept. Who works what days . Chiki Charts and Charting in Excel 0 December 6th 04 01:55 AM
Calculating commission schedule (will pay for help!) bootsy Excel Worksheet Functions 2 October 28th 04 07:24 AM
Calculating commission schedule (will pay for help!) bootsy Excel Worksheet Functions 0 October 28th 04 04:53 AM


All times are GMT +1. The time now is 05:42 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017