11 Replies Latest reply on Jun 25, 2014 1:35 PM by bill1650

    Keeping track of Salespersons commissions

    bill1650

      Title

      Keeping track of Salespersons commissions

      Post

           I am trying to figure out the best way to accomplish keeping track of a commissioned only sales persons earned and paid commissions.

           The salesperson will get paid an advance of a set amount on the 15th of each month. Earned commissions will be paid on the 1st of each month for all invoices that were paid the previous month minus the advance.

           I am having a difficult time figuring out how to structure this.

           I am working in FM Pro 13 Advanced on Mac OS X 10.9.3.

           Thanks.

        • 1. Re: Keeping track of Salespersons commissions
          philmodjunk

               What do you have set up thus far?

               Typical invoicing systems look like this:

               Customers-----<Invoices-----<LineItems(InvoiceData)>------Products/Services

               Does yours?

               What version of FileMaker are you using?

               To that system you can link in both a table of commissions and a table of paychecks. But having once worked on commission, I'm aware that there are different setups for how you manage commissions. Might two or more sales people split the commission for a particular invoice? That requires a more complex relationship than a setup where there is one and only one salesperson getting the commission for a given sale.

          • 2. Re: Keeping track of Salespersons commissions
            bill1650

            I am working in FM Pro 13 Advanced on Mac OS X 10.9.3.

            Only one salesperson will get the commission for each sale. It might be possible that not every sale will have the same commission rate.

                 My invoicing system looks like this:

                 Clients-----<Invoices-----<LineItems(InvoiceData)>------Products

                  

                  
            • 3. Re: Keeping track of Salespersons commissions
              philmodjunk
                   

              It might be possible that not every sale will have the same commission rate.

              And what governs what rate is applied? Is it based on the details of a particular sale (such as selling an extended warranty), the salesperson's numbers for the payperiod (did they exceed their goals?) or ??? (Yes, I once worked as a commissioned sales person for a short time...)

              Since it's one commission to an invoice, a field in invoices should be able to compute and/or record an commission unless something like the issues that I mentioned in the previous paragraph make this impossible.

              Thus, you might be able to set up these relationships:

                   PayRoll | Staff>-----SalesPersonnel------<Invoices>------PayRoll | Invoice

                   PayRoll | Staff::_fkEmployeeID = SalesPersonnel::__pkEmployeeID
                   SalesPersonnel::__pkEmployeeID = Invoices::_fkSalesPersonID

                   PayRoll | Invoice::cMonth = Invoices::cMonthPaid AND
                   PayRoll | Invoice::_fkEmployeeID = Invoices::_fkSalesPersonID

                   PayRoll | Invoice and PayRoll | Staff would be two table occurrences of your Employees table.

                   cMonth would be a calculation field that returns a Date Type: Date ( Month ( PaycheckDate ) - 1 ; 1 ; Year ( PaycheckDate ) )

                   Which computes the date for the first of the preceding month in order to match to cMonthPaid which would also be a calculation field defined to return a Date data type: DatePaid - Day ( DatePaid ) + 1, a field that computes the date for the first of the month in which the invoice was paid. (This assumes that an invoice is not paid at the time of sale.)

                   With such relationships, an auto-enter calculation can sum the total commissions for the preceding month's paid invoices as the total commission so that this value can then have the Draw subtracted to compute a paycheck total. (And this isn't simple subtraction as your sales person could have a bad month and fail to meet the draw...)

              • 4. Re: Keeping track of Salespersons commissions
                bill1650

                     Is SalesPersonnel it's own table?

                • 5. Re: Keeping track of Salespersons commissions
                  philmodjunk
                       

                            PayRoll | Invoice and PayRoll | Staff would be two table occurrences of your Employees table.

                       Correction: PayRoll | Invoice and PayRoll | Staff would be two occurrences of the same PayRoll table with one record for each paycheck issued to an employee--which might or might not be SalesPersons as you can use one table for all paychecks to all employees if you need to. (You may already have a payroll program for noncommissioned staff.)

                       SalesPersonnel needs to be a table with one record for each sales person that currently works for you. It can be an occurrence of an employees table that includes other staff working for you or just be a table only of SalesPersonnel.

                  • 6. Re: Keeping track of Salespersons commissions
                    philmodjunk

                         And I forgot to include this:  For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                    • 7. Re: Keeping track of Salespersons commissions
                      bill1650

                           This is what I have so far:

                           PayRoll | Staff>-----Sales------<sales_Invfile>------PayRoll | Invoice

                           PayRoll | Staff::_fkSalespersonID = Sales::__pkSalespersonID
                           Sales::__pkSalespersonID = Invfile::_fkSalesPersonID

                           PayRoll | Invoice::cMonth = Invfile::cMonthPaid AND
                           PayRoll | Invoice::_fkSalespersonID = Invoices::_fkSalesPersonID

                           I have tried to figure out what the auto-enter calculation should be and exactly where it should go but have not been successful. 

                            

                      • 8. Re: Keeping track of Salespersons commissions
                        philmodjunk

                             Do you have one record in Sales for each person on your Sales Staff?

                             In Payroll, you can setup your calculation for computing the total commissions minus draw calculation.. You can set this up to evaluate from the context of Payroll | Invoice

                             In that calculation, Sum ( Sales_Invfile::Commission )

                             will return the total commission for the month.

                        • 9. Re: Keeping track of Salespersons commissions
                          bill1650

                               Thank you. I have that calculation working just fine.

                               Is it possible to put a portal on the payroll layout that shows only the invoices and the commissions earned for the month?

                          • 10. Re: Keeping track of Salespersons commissions
                            philmodjunk

                                 From the relationships shown, this should be possible if you base the layout on Payroll | Invoice and not Payroll | Staff.

                            • 11. Re: Keeping track of Salespersons commissions
                              bill1650

                                   Thank you. It now works just like I wanted it to.