Proposal Summary
This proposal summarizes the functionality of generating commission reports automatically once a month and emailing them to appropriate sales reps.
Requirement
The client would like to automatically generate commission reports for their sales reps and email them to respective sales reps once a month.
Deliverables
- We will develop functionality to automatically generate commission reports, listing the commission amounts for sales reps based on the start and end dates of the month for which the report should be calculated.
Development of a custom interface:
- To automate the entire process, we will provide a custom interface where users can input the date range for calculating the commission amount. After doing so, users themselves can execute the script and send the commission reports to the sales reps.
- Within this interface, users can specify the start and end dates of the month for which they want to calculate commissions.
- Once the date range is entered, users will see a button labeled ‘Review Commission Report.’ By clicking this button, users can access the commission report for the selected month.
- After reviewing the commission report, users can click the ‘Send Commission Report’ button in the custom interface to initiate the script execution and send the commission report to the appropriate sales reps.
Generating Commission Report:
- We will develop a saved search based on the conditions like;
- Only paid in full invoices will be considered for commission calculation.
- The commission percentage of currently existing sales reps are as follows:
- Tom Willman: 6% on all price levels
- Randy Wold: 6% on all price levels
- Jim Sinkinson: 6% on all price levels
- Jack Fyffe: 6% on all price levels
- Rafael Mejia: 4% on all price levels
- Kurt Witt: 6% on all price levels except for 2 dealers Madison Liquidators LLC & Everything2go.com – Office Furniture and Home Furnishings Direct from Manufacturers LLC. Will be paid 2% commission on all price levels for these 2 dealers.
- Jason Bowman: 7% for price level 50/10 and 6% for price level 50/15 and 50/20.
- Tatiana Krusell: First 90 days starting 7/1/2023 10% for all price levels then drops down to 6% for all price levels.
- Here, the commission percentage is based on the price level entered in the item line.
- If the invoice is not paid in full even after 50 days from the invoice creation date, then the commission percentage will be reduced by 1%.
- The amount of non-inventory items will be removed from the total as non inventory items are not considered for commission calculation.
- Only inventory and Kit items will be considered for commission calculation.
- The commission percentage will be calculated on the discounted amount if any discount is applied to the order.
Automatically Sending Commission Report to Sales Reps
- To send the commission report to the appropriate sales representatives, we will develop functionality that prepares a CSV file listing the paid-in-full invoices for each sales rep and automatically sends these to the respective sales reps.
- When a date range is entered in the custom interface, as mentioned in the above point, the script will consider the invoices that satisfy the conditions mentioned, also the invoices that were closed within the specified date range.
- Upon clicking the ‘Send Commission Report’ button on the interface, the sales reps will receive their commission reports, which are prepared using these invoices.
- Once an invoice is used for commission calculation and the report is sent, we will flag this invoice and ensure it is never used for any future commission calculations.
- Unlike the spiff report, in this case, the commission report for a sales rep will be sent even if the commission total amount is less than $100.
Assumptions
- We assume that we currently need to send the commission report to the existing sales representatives: Tom Willman, Randy Wold, Jim Sinkinson, Jack Fyffe, Rafael Mejia, Kurt Witt, Jason Bowman, and Tatiana Krussel.
- We will update the sales rep list whenever a new sales rep joins, and the CORP team will inform us of the commission percentage for the new sales rep.
- We will calculate commission for Inventory and Kit items only.
- The commission calculation will be calculated based off the invoice total amount.
- If the invoice is not paid in full even after 50 days from the invoice creation date, we should reduce the commission percentage by 1%.
- In the case of the spiff report, we used to send you the spiff report each month to verify the spiff amount. However, now you can review the commission report with just a click of a button using the custom interface we provide.
- The custom interface will include two fields where you can enter the ‘from’ date and the ‘to’ date, as well as two buttons. One button is labeled ‘Review Commission Report’ for checking the commission amounts of sales reps before sending the reports to them. The other button is ‘Send Commission Report,’ which is used to automatically send the reports to the appropriate sales reps
- The commission percentage will be calculated on the discounted amount if any discount is applied to the order.
- If the discount is applied to a non inventory item, or any item other than inventory and kit item, we will remove this amount from the total. Similarly, if a mark up item is added to a non inventory item, we will also remove this mark up amount and the amount of non inventory item from the total before commission calculation.