help calculating year to year commissioned payroll

[csmith461]

So I am working on a modeling spreadsheet for a business partner and I am stuck on how to model sales rep growth and the corresponding payroll. Since commissions paid also include renewals based on previous year sales I cant figure out how to correctly reflect that in my main worksheet.

Anyway, the point of the matter is that a sales rep that starts in 2014 will not earn the same amount of renewal commissions as a rep who starts in 2016.

What I need is for this spreadsheet to adjust the column titled "Pay To Sales Reps" to correctly reflect the amount of pay going to both types of sales reps based on how many there are and when the sales staff was increased.

I know how to calculate it all manually but I would really like for it to all be automated.

What I would like is M16:M36 to display total payroll for the corresponding year but have it be dependent on how many of each sales reps there are and how long each has been at the company.

EDIT: Updated file is found in third post.

Thanks for any help!

[mikeTRON]

I can't follow your example in the worksheet.
Simplify the example or at least highlight ONE particular example as you walk through it and you might be able to get some help.

[csmith461]

Yeah, I'm probably making this sound harder than it is...

Lets try this as an example:

I have two types of staff and each one gets renewal commissions based on their previous years' sales. The staff that gets a base salary gets a lower percentage of their renewals than the staff that gets straight commissions.

At this point, I am not really too concerned with displaying what each individual staff member gets but I would like to see what the total payroll outlay would be for the sales staff.

Now, because the amount of renewal commissions available to each sales rep is dependent on how long they have been working the total cost of payroll will fluctuate based on not just how many reps there are but also how long they have been with the company.

For example, Sales Rep A started in 2014 and in 2016 will get renewal commissions from sales in 2014 and 2015 while Sales Rep B who started in 2015 will only get renewals from 2015 once 2016 rolls around. I can calculate the total amount manually in each scenario but would prefer it happen automatically when I change the number of sales reps in columns I and J in Sheet1.

Here is an updated link with many of the other variables removed along with some notes based on the information currently entered in the sheet.

https://docs.google.com/file/d/0B-w6...it?usp=sharing

I have already tried a blended rate formula in hopes that would help but I still am off by nearly 14%...

[csmith461]

any suggestions?