Creating a Reoccurring Commission Sheet

**worldtechms** · 01-15-2015, 06:21 PM

We now have sales reps at our company. I have been tasked with creating an excel sheet that will help with the reoccurring commission we pay our sales reps. Here is what I need:

Column 1: Client Name
Column 2: Date of Contract
Column 3: Contract Amount
Column 4: =bX*.2 (This is a one time fee for each contract)

I got this part. However, the next part is where I am lost

A sales rep gets paid 20% of the contract for the first 6 months and then 10% for the last 6 months. After 1 year their commission drops off on that contract. I need an an excel spreadsheet to show what they make each month when I enter Columns 1 - 3. I would like it to see the month of the contract and auto populate the columns for each month. for example if the contract was signed in January then February - June columns will show Column 3*.2 and July - Jan show Column 3*.1.

Each month (i believe) would have a if then statement. If Column 2 row X is Month x- month x then multiply Column 3 Row X *.2 if not then multiply Column 3 Row X *.1

Can this be done or am I asking to much. Please see attached for the excel spreadsheet. Commission Calculator.xlsx

**FDibbins** · 01-15-2015, 06:51 PM

Hi, welcome to the forum

1st, I suggest that you change the month names in F1:Q1 to actual dates and format them to show them as months - this will make the calcs easier.
2nd is that 20% for each of the 6 months and then 10% for the next 6 months (total 0f 180% ??) or is it 20%/6 and then 10%/6

Can you show some sample data in your WB, with your expected results?

**worldtechms** · 01-15-2015, 07:01 PM

See Attached

Commission Calculator.xlsx

Thank you for your quick response.

I have added 2 contracts that would close at 2 different months and this is what I would like to see.