Macro in Excel: pick next 7

Hi there,

I am doing a spreadsheet to record and manage salesforce comissions and
I'm facing a problem. I'll try to be as clear and concise as possible:

There is a 30-day column and the comission will vary according to how
much they sell. The sales input will be made on a daily basis.

Suppose they get 10% normal comission. When they reach 90% of the
monthly goal (say $100.000) they get an extra 5% for next seven days.
So, for 7 days they actually get 15% comission on everything they sell.

When they reach 100% of the monthly goal they get an extra 5% as well,
but the extra comission does not go on top of the previous one. That
means they will have 14 days in a row if the 90% was achieved within
the seven days given for the 90%. Here is the biggest problem I have.
The macro has to consider that if the seven days comission in ongoing,
the extra comission (for 100%) will start only after the end of 90%
bonus.

If 100% was achieved after the seven days bonus, then they get normal
seven days comission.

The same is valid for 120%.

Points to consider:
*All bonuses stop on the 30th of the month
*The bonus 120% starts on the 1st of the month
*No bonus if they reach their goal on the 31st
*The total for the Comission goes in one cell and normal comission in
another cell

I just can't figure out how to do that. I'm a macro dunb, so please, if
anyone can help...

Cheers

Maciel,

You could use a user-defined-function, but writing it will have to wait until the problem is
clearer.

I don't understand what you mean by "The same is valid for 120%" so I'm not sure how to include
that.

The other questions I have are:

Is the commission rate always 10%, or is it entered somewhere in a cell?
Is the 5% aways the same, or is it actually 50% of the usual commission rate?
Is the sales goal entered into a cell somewhere?

And why these two contradictory statements:

> *All bonuses stop on the 30th of the month
> *The bonus 120% starts on the 1st of the month

What happens if the month is 31 days long, or 28 (or 29)?

HTH,
Bernie
MS Excel MVP


"Maciel" <[email protected]> wrote in message
news:[email protected]...
> Hi there,
>
> I am doing a spreadsheet to record and manage salesforce comissions and
> I'm facing a problem. I'll try to be as clear and concise as possible:
>
> There is a 30-day column and the comission will vary according to how
> much they sell. The sales input will be made on a daily basis.
>
> Suppose they get 10% normal comission. When they reach 90% of the
> monthly goal (say $100.000) they get an extra 5% for next seven days.
> So, for 7 days they actually get 15% comission on everything they sell.
>
> When they reach 100% of the monthly goal they get an extra 5% as well,
> but the extra comission does not go on top of the previous one. That
> means they will have 14 days in a row if the 90% was achieved within
> the seven days given for the 90%. Here is the biggest problem I have.
> The macro has to consider that if the seven days comission in ongoing,
> the extra comission (for 100%) will start only after the end of 90%
> bonus.
>
> If 100% was achieved after the seven days bonus, then they get normal
> seven days comission.
>
> The same is valid for 120%.
>
> Points to consider:
> *All bonuses stop on the 30th of the month
> *The bonus 120% starts on the 1st of the month
> *No bonus if they reach their goal on the 31st
> *The total for the Comission goes in one cell and normal comission in
> another cell
>
> I just can't figure out how to do that. I'm a macro dunb, so please, if
> anyone can help...
>
> Cheers
>

Hi Bernie,

Thanks for taking the trouble to answer.

What happens if the month is 31 days long, or 28 (or 29)?
> In fact, for calculation purposes, the sales month starts on the 26th and finishes on the 25th (e.g.: starts on 26 Nov and finishes 25 Dec).
-------------------------------------------------------------
About 120%:
When they reach 120% of the monthly goal (say $100.000) they get an
extra 10% for first seven days of the sales month. As per the answer
above, from the 26th.
-------------------------------------------------------------
"And why these two contradictory statements:

> *All bonuses stop on the 30th of the month
> *The bonus 120% starts on the 1st of the month"

My mistake:
The bonuses which stop on the 25th of the month are 90% and 100%.
The 120% bonus is for the first seven days of the sales month (from the
26th)
------------------------------------------------------
And finally:

- Is the commission rate always 10%, or is it entered somewhere in a
cell?
> It is always 10%.

- Is the 5% aways the same, or is it actually 50% of the usual
commission rate?
> It is 5%; what is in fact, 50% of the usual comission rate of 10%.

- Is the sales goal entered into a cell somewhere?
> Yes it is. In this case, C52.

Thanks again,

Maciel

Maciel,

OK. A few more questions to actually make it work....

The 120% bonus is on the first seven days of the month being considered, or on the next month?

Also, not sure when the bonus is applied specifically. Let's say that they meet the bonus
requirement sales of 90,000 on a day when their monthly total sales comes to 90,001. Do they get
the extra 5% on the one dollar, on that day's sales, or does the 7 days of bonus start the next day?
What if they had a big sales day and they jumped from 89,999 to 99,999? Would we ignore the 10,000
from that one day and start the bonus period on the next day?

HTH,
Bernie
MS Excel MVP


"Maciel" <[email protected]> wrote in message
news:[email protected]...
> Hi Bernie,
>
> Thanks for taking the trouble to answer.
>
> What happens if the month is 31 days long, or 28 (or 29)?
>> In fact, for calculation purposes, the sales month starts on the 26th and finishes on the 25th
>> (e.g.: starts on 26 Nov and finishes 25 Dec).
> -------------------------------------------------------------
> About 120%:
> When they reach 120% of the monthly goal (say $100.000) they get an
> extra 10% for first seven days of the sales month. As per the answer
> above, from the 26th.
> -------------------------------------------------------------
> "And why these two contradictory statements:
>
>> *All bonuses stop on the 30th of the month
>> *The bonus 120% starts on the 1st of the month"
>
> My mistake:
> The bonuses which stop on the 25th of the month are 90% and 100%.
> The 120% bonus is for the first seven days of the sales month (from the
> 26th)
> ------------------------------------------------------
> And finally:
>
> - Is the commission rate always 10%, or is it entered somewhere in a
> cell?
>> It is always 10%.
>
> - Is the 5% aways the same, or is it actually 50% of the usual
> commission rate?
>> It is 5%; what is in fact, 50% of the usual comission rate of 10%.
>
> - Is the sales goal entered into a cell somewhere?
>> Yes it is. In this case, C52.
>
> Thanks again,
>
> Maciel
>

Bernie,

1 - The 120% bonus is on the next month. That is to encourage them to
put an extra effort on those days.
--------------------------
2 - Well, luckly they didn't ask me that, but the 7 days always start
on the next day. ; - )

Cheers,

Maciel

Maciel,

Copy the code below into a codemodule in your workbook. Then use the function like this:

=Commission(C3:C32,C33:C62,C1)

Where C3:C32 has sales data from last month, C33:C62 has sales data for the current month (for which
the commission will be calculated), and C1 has the monthly goal.

HTH,
Bernie
MS Excel MVP

Function Commission(OldSales As Range, rngSales As Range, _
TargetAmt As Range) As Double
Dim i As Integer
Dim Sales As Double
Dim OldSalesAmt As Double
Dim Met90 As Integer
Dim Met100 As Integer
Dim Met120 As Integer

Met90 = 0
Met100 = 0
Met120 = 0
Sales = 0

OldSalesAmt = Application.Sum(OldSales)
If OldSalesAmt >= 1.2 * TargetAmt.Value Then Met120 = 1

For i = 1 To rngSales.Cells.Count
Sales = Sales + rngSales.Cells(i).Value
Commission = Commission + 0.1 * rngSales.Cells(i).Value
If Sales >= 0.9 * TargetAmt.Value And Met90 = 0 Then Met90 = i
If Sales >= TargetAmt.Value And Met100 = 0 Then Met100 = i
Next i

If Met90 > 0 Then
For i = Met90 + 1 To Application.Min(Met90 + 7, rngSales.Cells.Count)
Commission = Commission + 0.05 * rngSales.Cells(i).Value
Next i
End If

If Met100 > 0 And Met90 + 8 < rngSales.Cells.Count Then
For i = Application.Max(Met90 + 8, Met100 + 1) To _
Application.Min(Application.Max(Met90 + 8, Met100 + 1) + 6, rngSales.Cells.Count)
Commission = Commission + 0.05 * rngSales.Cells(i).Value
Next i
End If

If Met120 > 0 Then
For i = 1 To 7
Commission = Commission + 0.1 * rngSales.Cells(i).Value
Next i
End If

End Function

"Maciel" <[email protected]> wrote in message
news:[email protected]...
> Bernie,
>
> 1 - The 120% bonus is on the next month. That is to encourage them to
> put an extra effort on those days.
> --------------------------
> 2 - Well, luckly they didn't ask me that, but the 7 days always start
> on the next day. ; - )
>
> Cheers,
>
> Maciel
>

Hi Bernie,

Awesome! Thanks a bunch for the help. I wouldn't have gotten even close
to that.

All the very best,

Maciel

Maciel,

You'e welcome - I hope that you double-check the results against a manual calculation - I would hate
to be responsible for shorting someone on their commission check...

Bernie
MS Excel MVP


"Maciel" <[email protected]> wrote in message
news:[email protected]...
> Hi Bernie,
>
> Awesome! Thanks a bunch for the help. I wouldn't have gotten even close
> to that.
>
> All the very best,
>
> Maciel
>