I've been posting here for a couple of weeks and thanks to the help of you guys I have figgured a workable method for how to manage royalty payments using QB Basic... or any QB I would guess.
But of course, after you all look this over you'll probably tell me it is too complicated or doesn't even really work... oh Well. Just in case anyone else is interesed, here is what I did and I think it is workable as opposed to spending big bucks on a system to handel royalty payment accounting for royalties that vary as a function of item discount...
Read it and yawn.
Here is the setup...
1 - When you setup your inventory items add two custom fields... ... Designer ... DiscountThe Discount field is necessary as we pay royalties on a declining scale... the more we have to discount the product, the less the royalty percentage.
2 - When you set up the Designer custom field go ahead and enter the name or initials of the person due the royalty... the designer in our case.3 - Create a Discount item for each discounts you offer to your customers... in our case we have 3 basic discounts we offer depending on weather the customer is an unqualified reseller, qualified reseller or wholesaler/distributor.
4 - Click List | Templates and make a duplicate copy of the standard QB invoice.5 - Open the duplicate and customize it by adding 2 columns... ... Desinger ... Discount (Rename the custom invoice if you wish to whatever you wish)
4 - Next go to Reports and open the Sales by Item Detail report and customize it by adding two special columns ... Designer ... DiscountNow here is how to use this and make it work...
1 - When you get an order create an Invoice2 - Add the Items to the Invoice that your customer is ordering.
3 - Note that as you add each Item QB will fill in the Designers name or initials in the Deisner Column fo that Item.4 - You will have to manually enter the Discount that you allow for that customer for each Item they order. (This goes in the Discount Column by each item. Like 20% or 40% or whatever.)
(Note that this discount is for your reference only and does not adjust the final invoice price...)
5 - When you have entered all the Items the customer ordered on the invoice, you must then enter the appropriate Discount Item to be applied to the invoice... this will adjust the final price that will appear at the bottom of the invoice to what the customer should pay.6 - Save, Print or email your invoice.
(Note you may make the two Custom Columns not print if you don't want the customer to see them... these two columns are for you to use in calculating the royalty due each designer at the end of each royalty period.)
When you are ready to pay royalties...
1 - Bring up your custom Sales by Item Detail report and note that by each item is the name of the designer and Discount for that particular sale.2 - Export to Excel if you have QB Pro or higher... or if you have QB Basic like we do just print the report to file as a comma delimited file... Excel will open it fine and create the various columns on the report.
3 - Delete the bottom of the report that shows the Discount Items. You only need the Inventory Items at the top.4 - Top section of the report should show each inventory item and the associated Designer and the discount that was applied to each individual sale. All you have to do is sort the discounts for each part/designer to end up with...
Part Qty Designer Discount A 10 Joe 20% A 15 Joe 40% A 5 Joe 40%
Now add a column for the royalty you pay and a column for what is due the designer
Part Qty Designer Discount Royalty Due A 10 Joe 20% 10% 10% of net is $xx.xx A 15 Joe 40% 5% 5% of net is $yy.yy A 5 Joe 40% 5% 5% ofnet is $yy.yy
Total due designer Total
This may look a little complicated but if you only pay royalties once or twice a year like we do, the exporting and sorting in Excel is not really that big of a deal.
If you offer your customer different discounts on different product so that mixed discounts will appear on the same invoice, well, I'm not sure QB will handel that as I think it will only apply a discount to the entire invoice... so you would have to do a different invoice for each discount. That is not the case for us. We apply the discount to the entire order based on the customers classification.
You can also approach this problem by assigning multiple part numbers for each part... one for each possible discount... but then it is hard to roll up sales for that particular part.
You can also use class to manage this I think, but we saved "class" to identify different divisions or product lines.
I'm not sure I describee this accurately as I did this summary quickly but hopefully it will give you some ideas on how to approach the problem and save you going out and buying an expensive package just to help you manage royalties that decline as a function of discount.
Thanks everybody for your assistance.
Now, someone, please tell me it isn't worth all this work... or that my yet to be applied approach just won't work :) Then we pull out the old paper ledger and pencil :)
Will