Can you help with this one please. My company uses a commmision based incentive. It has been decided to pay a one off commission on invoices from completely new Clients ( the very first invoice only ) and only when the invoice is paid. I have looked at Sage and can see that Icould probably use the one of the analysis fields when raising the invoice to say N for new business . I am thinking then that I would have to do a paid invoices report and manually check to see if any of my new business accounts have been paid. If they have then I would have to go into the account and remove the N flag on the invoice. This seems long winded and very 'manual'. Has anyone got a better idea about how this could be done via reporting in Sage , to get rid of the manual element.
Hi Marina
I don't like the analysis field, not as flexible as sage makes out ( or maybe that's just to my simple mind).
You need a custom report setting up. I would suggest you speak to Ian at Onion, he is a poster on here and has helped a few out and they have been full of praise. You can search on the forum, or just get him through his website....
Hmmm. I've given this some thought and I think a New Customer Commission (NCC) Eligible analysis code may be on the right track here. I take Joanne's point about them not being as useful as people imagine, but once you accept that the analysis relates to the customer and not to individual transactions made by the customer they can be very useful. (BTW thank you very much for the kind words Joanne).
We have a pivottable report that identifies all the paid items in the Sales ledger. It has filters for each of the analysis fields. So, with the filter for [NCC Eligible] set to Y, the report would show anything that had been paid for those customers currently tagged with [NCC Eligible] = Y. I think that would meet your requirement. The good thing about it is that there's a 30 day free trial on the report, so you can see if it meets your needs and, if not, allow you to better specify your requirements for a bespoke Sage Report Designer report.
Whether it was our report, or a bespoke Sage Report Designer report, you'd:
Tag all new customer accounts as NCC Eligible = Y
Run the report and calculate/pay the commission due
Tag those customer accounts for which the commission had been paid as NCC Eligible = N
If you also tag customers with an analysis field for Sales Rep, the report could also provide an analysis of the amounts paid broken down by Sales Rep.
I think that trying to automate this without manually setting an analysis field could get very complicated and inflexible. For example, you might have a payment on account for more than the value of the first invoice that hadn't been allocated yet. You might want to pay the commission even though Sage will still regard the invoice as "Open". Also, you might have partial or complete invoice cancellations via credit notes. So it may actually be the second invoice that would be eligible for commission if the first were cancelled. So many possibilities.
I'd be happy to discuss any/all of this with you if you'd like.
Thank you both Joanne and Ian for your help. I have been tangling with it again today and have found that I can use the department field when actually raising an invoice. Using this field (set to say N for new business ) on the invoice rather than the customer record identifies that particular invoice only for the commission which is what I need. Since a commission is only going to be paid when this first invoice is paid I have found that this is the field that shows up in the banking payments window when recording Custmer receipts. Is there a report that would show me invoices paid and this dept code. If so I think that would solve the problem?
Sorry I forgot to say that the problem with an analysis field on the customer record is that in my particular requirements the commission is a one off only on new business ( triggered by the payment of the very first invoice ) After that event the customer becomes 'ordinary' so I do not want to keep returning to the customer record to untag them as 'new business'. This is why I wanted to keep the tag on the voice as then once it had been paid and I could get a report for that , that would be job done so to speak and I would have no need to untag anything
So long as you don't want to use departmental reporting for its intended use, I think that's a good suggestion.
Have you looked at the "Customer Activity (Detailed)" report (CSTACTD.report)? It allows filtering by Department and shows the amount outstanding, so you'll know when the invoice has been matched. I'm not sure if payments on account or credit notes will be issues that might require some more thought though.
Well done you. Thinking outside the box can be rewarding.
Dates may be problematic. If you don't remove the department tag from the invoice, it'll remain on the report forever unless you use a date range filter. But how would you be able to set a date range filter? The payment delay could vary considerably from new customer to new customer.
Say you had 2 new customers and raised the first invoice on each on 1 October. One pays you on 31 October. You run the report for 1 Oct - 31 Oct and pay the commission for that paid invoice. The second customer pays you on 30 November. If you run the report for 1 Nov - 30 Nov, you'll not see the invoice because it was raised on 1 Oct. If you run the report for 1 Oct - 30 Nov you'll see both invoices and will have to have a system for tracking what customers you've already paid commission to.
The easiest way to solve the problem might be to untag the invoice once any commission is paid.
Hi
Could be a problem if you want to use departments in the proper way in the future.
Just to add to the mix though - what process are you going to use for keying the commissions due to the clients/what paperwork are you issuing/what about VAT/recognise the contingent liability? Sorry Ive not thought about it - just too busy at the mo, so just saying what pops into my head.
Do customers get the commission regardless of how long they take to pay? So even if they are outside terms they get it?
Also - how many new customers do you get at any one time? Is it really too many to have some kind of manual intervention.
Or perhaps just add an extra bit of code to the first invoice - like an 'A' or some such. Then when you are keying the allocation via the Bank you kow when you have paid the first one as you allocate the funds to an 'A' type invoice.
__________________
Joanne
Winner of Bookkeeper of the Year 2015, 2016 & 2017
Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.
You should check out answers with reference to the legal position
Hi Ian and Joanne
Firstly Joanne . It is the sales rep in my company that gets the commission on new business - so the customer is not involved in this transaction at all. Commissions make up gross pay so I am not falling foul of any statutory obligations at re vat or hmrc deductions. My apologies if I did not make this clear. Although I do take your point that it may be a case of having to manually note the new business invoices as they are paid. In truth I am not expecting hundreds! I am just lazy - if I can find a way of the software doing the the job for me I will!
Ian-thanks for your tip on the date range filter - i can indeed see why that would cause me a problem.. so untagging the invoice after payment seems the way forward , but as I am on unfamiliar ground here I will just have to see if it works. I thought you could not change the dept code on a posted invoice?
I wait with baited breath for all these new Clients!
Many thanks to you both for your brain power! I just love this forum- I always get what I'm after !
Ian-thanks for your tip on the date range filter - i can indeed see why that would cause me a problem.. so untagging the invoice after payment seems the way forward , but as I am on unfamiliar ground here I will just have to see if it works. I thought you could not change the dept code on a posted invoice?
Marina,
You may well be right. Even if you're not, I think the analysis code approach might still be worth a closer look too.
Hi Marina Oh completely ignore my numptiness!!! It is so obvious when I've just read it again, my daft comment last night was at the point where I should've given up at least an hour earlier after a hectic, mad, crazy crazy day. Shakes head
-- Edited by Cheshire on Friday 7th of October 2016 04:41:13 PM
__________________
Joanne
Winner of Bookkeeper of the Year 2015, 2016 & 2017
Thoughts are my own/not to be regarded as official advice,which should be sought from a suitably qualified Accountant.
You should check out answers with reference to the legal position
One of the reasons I'm struck by the analysis code as the way to go is the ease of update of standing data in the customer record using import templates (which includes the analysis fields).
I suppose it all depends on your workflow for these commissions. I'd envisaged a once a month or once a quarter process where you'd assess, and pay, all the commissions, and then untag all the customers for which the commission had been paid. All the customers for whom the commissions have been paid can be untagged in one simple Customer Record Import process. You list the Account Refs in one column and N in another column (to denote that the customer is no longer eligible for new customer commission) and import the file to Sage. Job done. Much easier than untagging individual invoices (assuming you can do that in the first place).
Food for thought?
Regards,
Edit: P.S. You could even work it that you didn't have to tag new accounts at all. Make the analysis field [NCC Paid?], do a single Customer Record Import upload to mark all old accounts as N/A, leave new accounts blank (i.e. don't bother tagging at all), do uploads to mark paid accounts with Y. The accounts that need considered when you go to pay commissions will be those with nothing in the [NCC Paid?] field on the customer record (easily isolated).
-- Edited by Onion4Sage on Friday 7th of October 2016 10:34:15 AM
