Our company sells telephone service to business customers in USA. We sell directly and also sell via agents (salesmen).
This spreadsheet is for tracking the monthly commission that needs to be paid to the agents.
The agent is paid based on a chart in the Agent Agreement. I've uploaded an image with the payment structure.
In the example below, I will use Roger Smith as an agent, and also the names Acme, Inc. and Fantastic, Inc. as customers that Roger Smith has sold our service to.
-For each agent, such as Roger Smith, we will need to calculate thier commission so they can be paid.
-In the uploaded image, the term MRC means "monthly recurring amount". This is the cost to the client each month. For example, the client may pay $200 each month.
Roger Smith is the agent and the client he sold service to is Acme, Inc.
Acme is paying $200 on a 36 month agreement, so I would owe Roger $200 in the first month (the bounty from the chart), and then in months 2 through 36 (and beyond) I would owe $30 each month. Roger would be paid each month that Acme, Inc. remains a customer.
Because Roger is a good agent, he brings in a new customer called Fantastic, Inc. and the MRC is $500, on a 24 month agreement. So Roger would be paid $0 bounty, and would be paid 12% each month. So month 1 - 24 would be worth $60 each month.
Notice that Fantastic, Inc. is on a 24 month agreement and thus there is not a first-month bounty.
Obviously, each month Roger would be paid for ALL of his clients.
In addition to Roger there will be other salespeople following the same rules and thus needing the same calculations.
The tracking of the clients would need to be flexible, because Acme or Fantastic, Inc may alter their service plan and either increase or decrease their MRC, thus changing how much would be paid to the agent from that point forward.
Must track multiple agents
Must track multiple customers per agent
Must provide printable reports for each agent to be submitted with paycheck showing current calculations and historical calculations.
Must provide printable report for my company with overall reporting on monthly payments (both per agent and overall)
You May use multiple sheets within a workbook to represent multiple agents if you like
MUST be free of calculation errors
If there is more questions, please ask.
February 19, 2018
I am willing to pay higher rates for the most experienced freelancers