How to use Manual Portfolio Bonus Tool

This feature gives NGU the ability to customize and calculate Portfolio Bonus in many ways:

Steps to Set Up and Use the Tool:

Note: All these actions will be performed by the NGU’s Sales Agent Admin or any other user-chosen by the NGU.

Configuring the bonus
1. Overview of how to use the tool
  In DBeaver
  - Open the query in DBeaver
  - In the query, edit the info that you need to edit: you may need to change the dates or the definition of a good customer
  - Run the query
  In Excel tool
  - Export the data as a CSV file
  - Copy and paste data in the excel tool
    - But you may also read the description in the first sheet in the excel template, to understand well the used terms.
  - Manipulate the inputs on the first sheet (called “Inputs & Description”)
  - The second sheet will contain:
    - Data you copied
    - Bonus calculated based on the copied data and the inputs used
  - The third sheet will contain the analysis of the cost impact of different structures
2. Customize the bonus structure : You can manipulate the inputs as you want but you need to read the description first to understand the used terms, and the inputs you use here will impact the bonus calculation in the 2nd sheet below:
  1. This sheet has two parts:
    1. Constraints and Bonus structures
3. Description
4. Open the query and customize fields to match your chosen bonus structure: Anything that is not grey is a code that should not be changed except if you know what you are doing or you are guided by the comment and anything that is grey is a comment to guide you on what to expect and what you can edit. See the description in the below screenshot:
Bonus Calculation and Payment
1. Run the query and export the data:
  - Right-click on the query results in the table, and click on Export data
  - Choose to export to CSV and click Next
  - Click Next
  - Choose the directory and the file name and click on Next
2. Click on Start and wait for it to be exported in the directory you defined
3. Paste the exported data in the excel tool
  - Open the exported file
  - Copy the exported data
  - Paste them in cell A2 of the second sheet (named: “TerritoryData”)
    - It means in the first column but second row
  - It will be pasted from column A to column K
  - If you have fewer territory agents than the ones in the excel template:
    - You will need to delete the rows that are not related to your data (for example: you have 100 territory agents while in the excel there are 255, when you paste your data in excel, they will occupy the first 100 rows and you will need to delete the other 155 rows.)

d. Bonus Calculation

After you already have:

decided the metric to use
Inputs for the calculation

You can go to the 2nd sheet called “Territory Data” where you will find the bonus calculated based on your inputs.

And copy the relevant info.

For example: if you have decided to use Tiered bonus and good customers to be defined based on UR, you can copy and paste Column A, B and S in another excel file to have your chosen bonus data (not just everything).

That’s the file you will need when paying the bonus.

e. Add Portfolio Bonus to Commission Invoices

Open ERP
Go to:
- Sales Agents module
- Commission Invoices
Choose invoices that in draft (Waiting for Validation)
Choose agent one by one
Click on Edit
You can:
- Add an item
- Edit the existing commission
- Delete the existing commission
Update Values
Save
Validate

3. Calculating the cost impact of different bonus structures
1. Analysis of the bonus to be paid per metric : With the 3rd sheet called “Analysis”, you can know how much bonus you will pay on each metric (in the currency used in the NGU)
2. links related to Excel file and SQL query to be used in case needed respectively :
  1. Excel file
  2. SQL Query: