Proposal summary
This proposal covers the scope of implementing a customized ‘Sales Commission KPI Report’ in the Al-Garawi Group NetSuite account by the implementer.
The scope and the provided estimate are based on the anticipation, expectation, and understanding through our discussions and email. If any scope change/additional feature development identified during actual development, they will be treated as change request.
Requirement from the client
The requirement is to implement a custom Sales Commission KPI Report with the following:
For direct and indirect sales (grouped /ungrouped), the implementer will consider Customers with CUSTOMER_TYPE as Main or Blank.
For Direct sales, if the Sales Manager is a sales rep, he will be entitled for commission for the same. The sales rep will have their own commission based on sales generated.
For Indirect sales, i.e., when GROUPED_INVOICE field in Invoices or Credit Memo is ‘GROUPED’, then the sales amount of the Sales Manager will be sum of the total indirect sales carried out by the subordinates/sales representatives under him along with his own indirect sales amount if he’s a sales rep. The sales rep will have their own commission based on the indirect sales generated respectively.
Furthermore, when the total sales is calculated for the Sales Manager and Sales Rep, the sales amount of direct and indirect are summed up respectively based on role.
KPI 1: Sales Achieved Percentage for employees:
The client has already shared a Target Breakdown table with the monthly sales target for team members and their supervisors based on Area
The original target amount is considered as 50% of the actual sales done by the sales employee.
The quota/ target values for sales employees will be stored in NetSuite. For Sales Manager, the quota amount will always be total of target defined for all the subordinates and his own target/quota.
The sales achieved percentage needs to be calculated using the formula:
Sales achieved % = (actual Sales / Target sales) *100 for a sales associate for a specific month
Based on the ‘Sales Achieved Percentage’ range of the employee calculated using the above formula, the KPI1 needs to be calculated by applying a loss% or gain% on the current target i.e. the 50% of the sales amount per employee.
The KPI1 value needs to be displayed in the Dashboard Portlet.
KPI2: Gross Profit based on profit percentage:
The initial Gross Profit is calculated on 20% weightage of the total sales amount per month per employee. This value will be used for deriving the KPI2.
The Gross profit for employees will be calculated based on the Cost of Goods (account:82000 Cost of Goods Sold) for the direct and indirect sales.
For the Sales Managers, the COGS will include the total COGS from the Indirect Sales by his subordinates and his own, and his COGS from his own direct sales. The sales rep will have their own COGS from indirect sales and direct sales.
The COGS values are fetched from the data provided by the client
Invoices from the Store will be standalone invoices and the COGS will be calculated from the Invoice
Invoices from the Corporate / Head office will be generated from Sales Orders and the COGS will be referred from Item Fulfilment.
For drop shipping, COGS is calculated from the vendor bill with impact on account ‘820000 Cost of Goods Sold’.
For return transactions, the COGS will be retrieved from:
Goods receipt – return from Standalone invoice
Credit Memo – return from Invoices generated from sales order
Bill Credit – return of drop ship sales.
The total sum of direct and indirect sales and COGS are considered for KPI2 calculation.
Formula:
Gross Profit % = (Sales – COGS) / Sales *100
After calculating the Gross Profit % value based on the formula, a loss percentage (mentioned in Table 2.1) is applied on initial Gross Profit amount which is 20% of the actual sales done by the sales POC. This is KPI2 value.
The KPI2 needs to be displayed in the Dashboard portlet.
KPI3: Total Collection
The Initial Collection % is calculated on 30% weightage of the total sales amount per month per employee. This value will be used for deriving the KPI3.
Total Overdue percentage is calculated using the total overdue invoiced amount and total open invoice amount for a sales employee per month.
Formula:
Overdue % = Overdue Invoices amount / Total Open invoices amount *100
Based on the range of overdue percentage calculated using the formula, a loss percentage, referring the below table (Refer table 3.1), will be applied on the initial collection i.e. 30% of the total sales. The final value is termed as KPI3.
The KPI3 needs to be displayed in the Dashboard portlet.
Dashboard Portlet displaying the KPIs
The KPIs needs to be displayed in the Dashboard portlet as a table, based on the sales employee’s name and region, along with drop down menu to select month of the year.
KPI3
The portlet will be Role-restricted wherein, the Sales Manager can view the details of his and his subordinates, and Sales Rep can view only their respective data. If the user selects the current month and year, then he will be able to see the KPI values based on the transactions until then.
Commission Record:
A custom record entry needs to be created for each employee for every month starting from next year, with Commission on KPI total (KPI Total x Commission % based on the role of the employee).
Formula:
Commission amount = KPI Total x Commission % based on the role of the employee
The commission percentage for the employee is selected based on the role as provided by the client.
This data does not needed to be displayed under the dashboard. The custom record will be role-restricted wherein, the Sales Manager can view the record entries of his and his subordinates, and Sales Rep can view only their respective record entry.
Prerequisites
For KPI 1, KPI 2, KPI 3 and Commission record, all the inputs are shared by the client and the details are mentioned under the requirement section.
Deliverables
A custom fields for Sales Rep, Supervisor, role, customer type (Customer_Type), area and grouped invoice (Grouped_invoice) will be created for COGS amount retrieval in the records: Item Fulfilment, Invoice, Goods receipt, Credit Memo, Bill Credit and Drop Ship Vendor Bills.
A custom field will be created for storing Area/Region in employee record.
A custom list field will be created for ‘Commission Role’ in employee record to choose from options: ‘Sales Manager’ and ‘Salesperson’.
New custom record will be created for storing monthly Target/Quota for Sales Associates for a year, displaying the following fields:
Area
Employee name
Role
Year
Month
Target/Quota fields (Separate fields will be created for each month).
Total Target for a year.
New custom record will be created to store Gain/Loss % based on Sales Achieved range for KPI1 calculation.
New custom record will be created to store Loss % based on Gross Profit range for KPI2 calculation.
New custom record will be created to store Loss % based on Collection range for KPI3 calculation.
New custom record to store KPI weightage and Commission percentage based on role will be created.
The following table will be displayed in NetSuite Dashboard (Portlet)
A table in the NetSuite dashboard with following data:
Area
Employee Name
Role
Year
Month
Currency (Based on employee subsidiary)
KPI 1
KPI 2
KPI 3
The table in dashboard will have a filter to choose the month and year for selection. Accessible Users can choose any month including the current month. When selecting the current month, the KPIs will be generated using the data of transactions recorded in the same month until the current date. (The KPI values for the current month will be updated daily).
New custom record will be created for displaying the values for Commission data:
Area
Employee name
Sales Manager (if any)
Role
Year
Month
Currency (Based on employee subsidiary)
Target/Quota
Target (50% of actual sales)
Gross Proft (20% of actual sales)
Overdue (30% of actual sales)
KPI1 value
KPI2 value
KPI3 value
KPI Total (KPI1 +KPI2 +KPI3)
Commission percentage based on role
Commission on KPI total (KPI Total x Commission % based on the role of the employee)
These data will be added to the record fields. The data will be generated through a scheduled process.
Area
Employee Name
Role
Year
Month
Currency
Target/Quota
Target (50%)
GP(20%)
Overdue(30%)
KPI1
KPI2
KPI3
Commission %
Commission Amount
Assumptions
All the KPI values are calculated based on the equations confirmed by the client and are added to this document under the requirement section (Requirement). Any changes in the methods used will take additional development time over the current estimated effort.
The data will be generated daily and the data from latest transactions will be reflected after a delay based on the transaction count.
Access to the KPI data will be restricted based on role hierarchy. The Salesperson can only see their own KPI data and Sales Managers can view the KPI data of the subordinates and theirs as well. Only these 2 levels of hierarchy are considered for the current requirement.
Based on the input from client defined in the input SM sheet, the implementor will use the roles (mentioned along with their internal ID for reference) defined in NetSuite as per below:
Salesman: AG – Sales Person (id: 1044) and AG- Sales Person – Dubai (id: 1047)
Area Sales Manager: AG- Sales Manager (id: 1038); AG Sales Manager Dubai (id: 1152)
Amount (Net Of Tax) field will be considered for KPI 1 and KPI 2 calculations. This field retrieves the amount from the transaction including the body-level and line-level discounts without any body-level tax.
For overdue /collection calculation, all the amounts considered are without tax, except remaining amounts of partially paid open invoices. For such cases, the implementer will deduct 15% from the remaining amount, as confirmed by the client.
For partially paid open invoices:
Amount remaining without tax = Amount remaining – 15% of Amount remaining
The ‘Overdue amount’ refers to the total amount from all remaining Open invoices with overdue days greater than 0.
The total open invoices amount is the total remaining amount to be paid for all open invoices whether its overdue or not.
Sales Manager’s region will be same as subordinates.
All the KPI values and commission record will have the amounts displayed in base currency based on the employee subsidiary.
All the transactions are selected based on Date field (field id: ‘trandate’).
COGS value when change for landed cost or any such, after the KPI generation, the new values won’t be taken into consideration, nor the KPI can be recalculated for past months.
Historical data i.e., data before 2025 won’t be displayed/calculated for the current requirement. Only data from 2025 will be displayed in the dashboard portlet and custom records.
Client will manually validate and update the KPI ranges and Commission percentages post implementation, as per the need.
Risks
Update in Sales Rep/ Manager field after commission calculation should be avoided for past month transactions. The commission calculation will only consider the data (incl. Sales Rep/ Manager field value, transactions) at the time of KPI and commission generation for real time value generation.
Addition of past dated transactions other than current month will not reflect in the KPI values or commission record.
Item fulfilment without Invoice would lead to miscalculation as there will not be any sales amount corresponding to the COGS in Item Fulfilment record. Invoice in different month than Item Fulfilment would also create mismatch in the values since the sales amount will not include in the selected period.
Item fulfilment with partial fulfilment would adversely affect the KPI 2 generation. For instance, if the Item fulfilment has 5 items and Invoice has only 3, there is a chance of discrepancy in the amounts reflected in both the records.
Similarly, Vendor Bill for dropship when posted in a month and invoice generated in different month, there will be discrepancy in the data pulled in both months.
The KPI values and Commision are generated in real-time based on monthly target.
The quantity for any transaction record, especially drop ship and its related records should always be the same. Else there will be difference in values which we fetch from different aspects, like Sales and COGS will differ when dropship quantity in Vendor Bill and Invoice is different.
In Scope
For direct sales, transaction record field GROUPED_INVOICES with empty value are considered.
For Indirect sales, transaction record field GROUPED_INVOICES with value ‘GROUPED’ are considered
KPI1 and KPI 2 will be calculated on the Amount Net of tax.
To calculate Sales amount, sum of Credit Memo amount is subtracted from sum of invoiced amount based on month and Sales Rep.
Out of Scope
For any transactions, Customers with ‘CUSTOMER_TYPE’ field value: ‘TEMP’ will not be considered.
Cash Sale is not in the scope of the requirement.
Goods Receipt/ Item receipt which are created from transaction type ‘Transfer order’ are out of scope of the requirement.
Recalculation of commission and KPI values for previous months is infeasible based on current requirement.
There will not be any ‘Drill-down’ option in the KPI data in Dashboard portlet.
All pending approval transactions are out of scope of the current requirement.
The implementer will focus on the transactions at the time of KPI and commission generation based on month and year. Handling transaction records without its corresponding related records is out of scope.
The dashboard portlet only shows KPI values month-wise (Only one month at a time). Multi-selection in month filter is out of scope.
Future Scope
Recalculation of already generated commission and KPI values of past month data, with updated inputs.
Commission and KPI calculation covering more than 1 month, like quarterly, half yearly or annually.