B
BB.2009
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
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