A company I work with has a new compensation plan, and individual reps have a
need to track ongoing future commissions depending on contract terms. I am
having a lot of trouble envisioning how to create a spreadsheet that will
handle this.

Here is the situation - contracts are sold for 1-10 year service periods,
and for several thousand dollars per year, with variable terms (monthly
payments, quarterly payments, pay in full, etc.) Commission is paid only
when an amount is collected from the client, at a flat rate (5%).

We want to be able to input the account name, amount of the contract,
duration of the contract, and terms - then have the spreadsheet calculate and
tally current and future planned commission (based on the premise that every
customer will meet their payment schedule).

Example: A Rep sells three contracts in the first month - Contract 1 is a
two-year service for 24K, collected in equal monthly amounts (1K/month) for
the next 24 months. Contract 2 is a three-year service for 24K collected in
eight equal quarterly payments (3K/quarter). Contract 3 is a one-year service
for 10K in one payment, collected immediately. In the first month, the rep
collects 1) 5% on 1K; 2) 5% on 3K; and 3) 5% on 10K. In month two, the rep
sells Contract 4, a one-year service for 15K, and collects 5% on 1K
(collected from Contract 1) and 5% from 15K (Contract 4).

Can anyone provide me some direction on setting this calculator up? It is a
little advanced for me.

Brian

Hello, and thanks for the response. I made very little progress this weekend
and could use your help.

please use bartholomewbrian@hotmail, and post here after you have sent.
Thank you!

"aamerrasheed via OfficeKB.com" wrote:

> Hi,
>
> I've made a file for you. Please give me your email address so that I can
> forward it to you.
>
> Thanks
>
> BB.2009 wrote:
> >A company I work with has a new compensation plan, and individual reps have a
> >need to track ongoing future commissions depending on contract terms. I am
> >having a lot of trouble envisioning how to create a spreadsheet that will
> >handle this.
> >
> >Here is the situation - contracts are sold for 1-10 year service periods,
> >and for several thousand dollars per year, with variable terms (monthly
> >payments, quarterly payments, pay in full, etc.) Commission is paid only
> >when an amount is collected from the client, at a flat rate (5%).
> >
> >We want to be able to input the account name, amount of the contract,
> >duration of the contract, and terms - then have the spreadsheet calculate and
> >tally current and future planned commission (based on the premise that every
> >customer will meet their payment schedule).
> >
> >Example: A Rep sells three contracts in the first month - Contract 1 is a
> >two-year service for 24K, collected in equal monthly amounts (1K/month) for
> >the next 24 months. Contract 2 is a three-year service for 24K collected in
> >eight equal quarterly payments (3K/quarter). Contract 3 is a one-year service
> >for 10K in one payment, collected immediately. In the first month, the rep
> >collects 1) 5% on 1K; 2) 5% on 3K; and 3) 5% on 10K. In month two, the rep
> >sells Contract 4, a one-year service for 15K, and collects 5% on 1K
> >(collected from Contract 1) and 5% from 15K (Contract 4).
> >
> >Can anyone provide me some direction on setting this calculator up? It is a
> >little advanced for me.
> >
> >Brian
>
> --
> Message posted via http://www.officekb.com
>
>