Royalties calculator for pyramidal income

[stefanocps]

Hello i need to do a simple., i think, calculation on royalties credit for agents


We have some agent, and each agent has its own subagents..total 3 levels of agent
We need to calculate the royalties for each agent considering that of course if the dependancies. So when the agent in the bottomline (the third) will make a sell, the uppe rline eill ge some royalties. When the agent on second line will make a sell, he and the first line willget royalties, when the top line make a sell only he will get royalties
Every month we have a report and we insert income generated for each agent, and we need to calculte the amount of royalties for each agent in th esystem.
With possibilities to query the db to see how much an agent has gained within a certain interval of time
To me it looks quite easy to do, but it is long time i don't use acces and start gain will be hard
Do you know of any templates available (may be to be customizable with some edit) or software ready (online or not) that does this?

thanks alot

[Bob Fitz]

Tell us about your table structure.
How do you store agent details and their dependent relationship?

[CJ_London]

I’m not aware of any templates but the calculation should be pretty straightforward if your tables and relationships are designed properly. Suggest you provide details of your tables and relationships - I would expect at least 3, employees, sales and royalty rates.

also need to know how you handle when someone is ‘in post’ I.e. when someone joins, leaves or moves from one post to another either sideways or upwards

[stefanocps]

i think the core is to estabilish the relation within the agents
So we have 3 level of agent, that can be identified with a single letter code..A, B, C ...ecc
The the second level, they are subagent of the 1st level, that can be identified with 2 letter codes (the forst being their "pather agent" so AA, AB, AC..or BA, BB, BC..or CA, CB, CC..and so on
then the third level that as well as the second will be like AAA, AAB, AAC or ABA; ABB or ACC...and more BAA, BAB, BAC.
and so on..i think this is clear
Now at the end of the month we recevi a report for all the income generated from each agent
The 1st level agent wil take the whole royalties, the 2nd level willshare with the 1st, and the 3rd level will share with 2nd and 1st
The amonut of percentage is to be defined

I hope this is clear enough..thank you

edit:
the mehtid with letters is one i just think about..can be different as there no agent yet and the system can be modified to suit best need!

[stefanocps]

I’m not aware of any templates but the calculation should be pretty straightforward if your tables and relationships are designed properly. Suggest you provide details of your tables and relationships - I would expect at least 3, employees, sales and royalty rates.

also need to know how you handle when someone is ‘in post’ I.e. when someone joins, leaves or moves from one post to another either sideways or upwards

yes it seems quite easy..what it seems difficult at glance is to identify the relations among agents to assign the proper percentage..in practical how to make the db recognize the share/agent couple according to a code
About your second point...yes i did not think about, can be somehting to consider and intoriduce as option inthe sw, but i have to talk and see if this is somehting that can happen. thanks

[CJ_London]

I hope this is clear enough..thank you

gives an idea, but not really clear. Using a code as you have makes for difficulties on determining who is related to who in db terms. Better to use a fk. Please get out of the habit of describing tables, show us. Descriptions can be misleading

I have many years experience on sales commission schemes and as an absolute minimum I would expect something like

tblAgents
AgentPK - auto number
AgentName
ReportsTo - link back to parent agent

AgentPK....AgentName....ReportsTo
1..............A..................null (this is a top agent)
2..............B..................null (this is a top agent)
3..............C..................1 (managed by A)
4..............D..................2 (managed by B)
5..............E..................1 (managed by A)
6..............F...................3 (managed by C)
7..............G..................3 (managed by C)
etc

May need other fields to identify areas, markets etc and note this does not meet the requirements regarding my second point of when things change - when you know the answer to that, the design can be finalised - might require more tables.

then you need a table for the royalties % split

and another for sales which can be associated with the agentPK

[stefanocps]

gives an idea, but not really clear. Using a code as you have makes for difficulties on determining who is related to who in db terms. Better to use a fk. Please get out of the habit of describing tables, show us. Descriptions can be misleading

I have many years experience on sales commission schemes and as an absolute minimum I would expect something like

tblAgents
AgentPK - auto number
AgentName
ReportsTo - link back to parent agent

AgentPK....AgentName....ReportsTo
1..............A..................null (this is a top agent)
2..............B..................null (this is a top agent)
3..............C..................1 (managed by A)
4..............D..................2 (managed by B)
5..............E..................1 (managed by A)
6..............F...................3 (managed by C)
7..............G..................3 (managed by C)
etc

May need other fields to identify areas, markets etc and note this does not meet the requirements regarding my second point of when things change - when you know the answer to that, the design can be finalised - might require more tables.

then you need a table for the royalties % split

and another for sales which can be associated with the agentPK

ok let's first concentrat eon this which is the most complicated (at least for me)
As i said we can use any type of system to create relations as there are not yet any
We just need to consider that when we create a code (or anything similar) to an agent this code will be used in website when a customer will buy a product using that code
Now..for me is not easy to describe tables as i am not doing programming since long, and need to reorganize my mind about

Coming to you suggestion..what is an FK, (or PK..in the message you wrote FK in the example PK)?
For what i understand the agent..wheter 1st or 2nd or third level, will receive a progressive number (the PK) which will tell the software who is related..right?
So this seems really good, a proper way to manage relation of course, it completelly open a new perspective on my idea..good, thanks!
Don't know yet how to put in coding..but we'll see that later

I'll wait for th einfo you required but i need to know more
It is clear the change if an agent move upward or downward,
Also it is clear if an agent leave, as we come to the point that others should move upward or downards
But when a new agent joins..well this should be ordinary..we just should create a new agent to the right level..top or sub..right?

[CJ_London]

what does this mean?

We just need to consider that when we create a code (or anything similar) to an agent this code will be used in website when a customer will buy a product using that code

How does the customer (or the website) know what code to use? How does that get reported back to you as a sale? What procedures are in place to ensure the customer/website does not use a wrong code? Or worse, types in a wrong code, but still valid. e.g. code should be BAB but customer types ABB?

PK=Primary Key. It is a value that uniquely identifies a specific record. Typically it will be an autonumber and has no meaning other than as a unique identifier.

FK=Foreign Key. it is a value that identifies the parent to the record by storing the PK of the parent.

You really need to be clear on what the payment rules actually are

They will have a significant impact on your design. Say agent X is in the 'ABB' position, they leave and the position is vacant, what happens to the royalty? Or agent X is replaced with agent Y but the sale predates when agent Y joined so technically is due to agent X? Or agent X is promoted and takes the AB position - do they get the ABB and AB royalties for that month? Timing can have a big effect.

Or another scenario - agent X goes on sick leave or an extended break and someone else steps in and covers their existing ABA role as well as ABB for a period of time.

I'm not going to be around much for this week so perhaps Bob or someone else can step in? Otherwise I'll respond when I can

[stefanocps]

what does this mean?


How does the customer (or the website) know what code to use? How does that get reported back to you as a sale? What procedures are in place to ensure the customer/website does not use a wrong code? Or worse, types in a wrong code, but still valid. e.g. code should be BAB but customer types ABB?

PK=Primary Key. It is a value that uniquely identifies a specific record. Typically it will be an autonumber and has no meaning other than as a unique identifier.

FK=Foreign Key. it is a value that identifies the parent to the record by storing the PK of the parent.

You really need to be clear on what the payment rules actually are

They will have a significant impact on your design. Say agent X is in the 'ABB' position, they leave and the position is vacant, what happens to the royalty? Or agent X is replaced with agent Y but the sale predates when agent Y joined so technically is due to agent X? Or agent X is promoted and takes the AB position - do they get the ABB and AB royalties for that month? Timing can have a big effect.

Or another scenario - agent X goes on sick leave or an extended break and someone else steps in and covers their existing ABA role as well as ABB for a period of time.

I'm not going to be around much for this week so perhaps Bob or someone else can step in? Otherwise I'll respond when I can

let me answer

the customer will have a code which is given to them by the agent.
The agents'job is to find new customer...so when they find one they 'll give them their personal code.
In the site the procedure will go on only when a cutomer will insert a valid code..so if the y want to buy the service they need to insert a code which is registered in the site as a valid code, and that is associated to an agent name.
In case they put a wrong code that can still work that can be a problem..., that's why i was trying to use a complex code, so that there is no possiibility to have this misundertsanding
Of course if we use a progressive number, is very likely that a misundertsnading is going to happen

At the end of the money we get a report with all the income that are generated from customers
So we now, for example, that the customer Paul Young has generated 1000 euro
We know that Paul Young has registered with a certain code...and we know that this code belong to a certain agent
This is the procedure which tell us how to use the income

For the rest still waiting...

[stefanocps]

let me answer

the customer will have a code which is given to them by the agent.
The agents'job is to find new customer...so when they find one they 'll give them their personal code.
In the site the procedure will go on only when a cutomer will insert a valid code..so if the y want to buy the service they need to insert a code which is registered in the site as a valid code, and that is associated to an agent name.
In case they put a wrong code that can still work that can be a problem..., that's why i was trying to use a complex code, so that there is no possiibility to have this misundertsanding
Of course if we use a progressive number, is very likely that a misundertsnading is going to happen

At the end of the money we get a report with all the income that are generated from customers
So we now, for example, that the customer Paul Young has generated 1000 euro
We know that Paul Young has registered with a certain code...and we know that this code belong to a certain agent
This is the procedure which tell us how to use the income

For the rest still waiting...

ok i have the answer fpr your request

Agents will not go up or down

In case one agent will leave will not be replace and his royaltes will be given to the main organization

so resuming



weshould not consider more than 3 lines!