Proposal Summary
This proposal summarizes the functionality of generating spiff reports automatically once every month and emailing them to appropriate spiff personals.
Requirement
The Spiff report email needs to be sent to respective spiff persons once every month.
As of now, the script for implementing this is executed manually every month and need to find a solution to implement the functionality with less manual interventions
Deliverables
- To automate the entire process, we’ll provide a custom interface allowing users to input the date range for calculating spiff amounts for spiff vendors each month. Users can then execute the script and send spiff reports to respective vendors via email.
- Within the interface, users can input the end date of the month for which they want to generate the spiff report.
- Once the date is entered, users will see a button labelled ‘Review Spiff Report.’
- Clicking this button redirects the user to a new page where they can review the spiff report, including vendor names, respective spiff amounts, and sales reps for the selected month.
- On this page, all displayed spiff vendors are considered for spiff email sending. However, users can deselect specific spiff lines from the report, and the spiff email will only be sent to the selected vendors.
- After reviewing the spiff report, users can click the ‘Email Spiff Report’ button on the custom interface to initiate script execution and send the spiff report to the appropriate vendors.
Generating Spiff Report:
- We will utilise a saved search based on the conditions listed below;
- Only paid-in-full invoices will be considered for spiff amount calculation.
- The end date entered on the custom interface will be utilized as a criterion in this saved search to filter out the invoices that were closed between January 1, 2022, and the specified end date.
- Among the invoices closed before October 1, 2023, only those closed within 47 days of their creation are considered for spiff calculation, whereas all the invoices closed after this date will be considered for spiff calculation regardless of late payment.
- The spiff amount calculation for each spiff person will depend on the class of the item selected on the invoice, and the SPIFF PERCENTAGE VALUE (%) or SPIFF DOLLAR VALUE ($) specified on each class record.
- For a vendor to be considered for spiff report calculation, the spiff vendor should be added in the ‘SPIFF’ field of an invoice, and the vendor should be ACH INFORMATION VERIFIED.
- The spiff amount is calculated as follows: all items on all paid-in-full invoices closed within the specified end date are considered. If a value is specified in the SPIFF PERCENTAGE VALUE (%) field of the item’s class record, the spiff amount for that item line will be “value in SPIFF PERCENTAGE VALUE (%) * Amount”. Alternatively, if a value is specified in the SPIFF DOLLAR VALUE ($) field of the item class record, the spiff amount for that item line will be “value in SPIFF DOLLAR VALUE ($) * Quantity”.
- The spiff amount for a vendor for a month will be the sum of the spiff amounts calculated in the above-explained way for all the item lines in all the invoices considered for that month.
- The total spiff amount for a vendor for the selected month should be greater than or equal to $100.
Automatically Sending Spiff Report emails to Vendors
- To send the spiff report to the appropriate spiff vendors, we will develop functionality that prepares a CSV file listing the paid-in-full invoices of each vendor and automatically sends these to the respective vendors.
- When an end date is entered on 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 month.
- Upon clicking the ‘Email Spiff Report’ button on the interface, the vendors will receive their spiff reports, which are prepared using these invoices.
- Once an invoice is used for spiff calculation and the report is sent, we will flag this invoice and ensure it is never used for any future spiff calculations.
Assumptions
- We assume that the end date entered on the user interface will be the last day of the month for which the spiff is to be calculated. For example, if the spiff report to be displayed should be for May, then the date to be entered on the page should be 03/31/2024.
- The start date for spiff calculation will always be 1/1/2022.
- For all the spiff vendors selected on the page for reviewing the generated spiff report, an email will be sent upon clicking the button ‘Email Spiff Report’ on that page.
- The email will contain a CSV file attached listing the invoices and other details.
- The spiff amount is calculated as follows: all items on all paid-in-full invoices closed within the specified end date are considered. If a value is specified in the SPIFF PERCENTAGE VALUE (%) field of the item’s class record, the spiff amount for that item line will be “value in SPIFF PERCENTAGE VALUE (%) * Amount”. Alternatively, if a value is specified in the SPIFF DOLLAR VALUE ($) field of the item class record, the spiff amount for that item line will be “value in SPIFF DOLLAR VALUE ($) * Quantity”.
- For sending the spiff report to a vendor, the vendor should be ‘ACH Information Verified’, and their total spiff amount for the selected month should be greater than or equal to $100.