Royalties calculator for pyramidal income

**stefanocps** · 05-29-2023, 04:23 AM

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 excel (or access) and start gain will be hard, same for excel
Do you know of any templates available (may be to be customizable with some edit) or software ready (online or not) that does this?

edit
i add more details
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

thank you

**AliGW** · 05-29-2023, 04:51 AM

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

**stefanocps** · 05-29-2023, 06:03 AM

thanks for th einfo
As i saidmy ability is totally stiffed now and i am afraid would be so difficult for me to create a sample file with excel or acces..at least for now..as i would not now where to start form

Meanwhile i have given som eprecious info which allow me to adds ome specifical details

There should be at least 3 tables:

employees, sales and royalty rates.

The employess tables,which is where i concentrate now as it seems the core of the sw, should be working this way

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

So the agents/employees will be given a unique number, the PK which will tell the sw who is related

**AliGW** · 05-29-2023, 06:21 AM

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A sample workbook is a manual mock-up of what you are trying to achieve.