Quickbooks, stock and serial numbers

[duncan drennan]

In Quickbooks is there a way to track serial numbers of stock items? What I mean is say I buy 10 widgets, each with a unique serial number, can I keep track of which ones I have sold, and which I still have on hand?

e.g. I've bought 10 widgets with serial numbers 20013 - 20022. I want to know which serial numbers I still have on hand, and which I've sold.

Thanks - I'm using QB 2003.

[Dave A]

Take a look under the Help menu at custom fields, for the items your purchase or sell (your not my typo). But it doesn't look promising.

You can buy add-ons, although getting an add-on for QB 2003 now might prove tricky. There was one I looked at that handled multiple stock centres. Something like that might also give what you're looking for.

This search result holds some promise, although some of the solutions look pricey.

If you do get an add-on, make sure it is compatible with the UK version, not the USA version.

[Yvonne]

You could capture each widget as an individual stock item listing with the serial number forming part of the item list, but you would end up with a really long stock report etc. Once you have sold the item you can mark the item as inactive, but I really do not think that this is going to be your answer.
Quickbooks is a very forgiving program, so you could enter a list of the items with say 10entries of serial numbers, and then just alter the item serial number when all your stock is sold and your new stock comes in. It all depends on how important the serial number is to your retention of records and how they function in your systems?
A spreadsheet with the serial numbers marked off as you sell them would be an easier method if you require to track them via serial number when sold.
Yvonne

[SilverNodashi]

This isn't really an option. 10 widgets isn't much, but try and do this with a 1000 widgets...... And then, let's say you buy a 1000 each, of 100 different products? This isn't feasible, and I'd also like to know how other people track items by serial number. It would be especially usefull to be able to see who client purchased widthed X with serial no 123

[Dave A]

It can be done with POS software. But essentially it's either another database or another table in the same database. From an accounting point of view it is irrelevant - it's a traceability issue. Hence it isn't built in as a default tracking option in accounting software.

[Yvonne]

Duncan, approx. how many widgets might you be dealing with?
There is the option in QBooks that you could make use of another field in the invoice which would permit you to "search" for a specific serial number, but this would only give you information on the "sold" serial numbered item.

Can you be more specific for your "need" for the serial number, could you work on the basis that "if not sold - should be in stock?"

Perhaps a very simple access database, not linked to QBooks will be your most easily controlled option.

Yvonne

[duncan drennan]

Can you be more specific for your "need" for the serial number, could you work on the basis that "if not sold - should be in stock?"

Right now it is just 10. Currently I'm using the easiest method in terms of effort - a spreadsheet.

The widgets are actually just a range of unique numbers allocated for USB products. I have 10 of these, and they get allocated to a product and sold to the client. These numbers are globally (as in worldwide) unique, so I just need to keep track of which I still have, and which have been allocated.

This is a pretty trivial case, and I was just wondering about QB's capabilities in this regard. There are a number of different solutions out there, the key is to make sure that errors don't occur when transferring from some other solution across to the accounting/invoicing domain.

I haven't spent too much time on it yet, but will look into it further in the future. Thanks for all the help and suggestions.

[SilverNodashi]

Yvonne, how would I use Quickbooks to track computer equipment? To give you an idea, something like mice & keyboards, CD's / DVD's, software licenses, hard drives, RAM, etc are (is?) tracked by serial number, and it would be really nice to have a way of knowing which item was sold to which client. This is especially handy for warranty purposes, as well as faulty batches. Let's say a supplier recalls all items of a certain batch, and I need to contact all clients with say HDD xyx, of batch abc123 - how would I handle this?

The other thing is, if a client phones and says his monitor is not working, I'd like to see on my side what the serial number was, so that I can check with the supplier if it's still under guarantee. Any suggestions?

[Graeme]

Serial Numbers

Duncan - a common form of guarding against errors in entering serial numbers is to use a check-digit, like your ID No, Bank Account No, Property Rates No etc, etc.

[duncan drennan]

Duncan - a common form of guarding against errors in entering serial numbers is to use a check-digit, like your ID No, Bank Account No, Property Rates No etc, etc.

I don't quite follow

[Dave A]

Let's tackle this a slightly different way, whilst I've got time to go into more detail.

What Yvonne has referred to is the ability to create custom fields. However, these custom fields have limited functionality and are only available in certain areas. For example, you can't do a sort or search on a custom field. But that doesn't mean you can't achieve that. If you generate a report which includes the custom field, you can export to a spreadsheet and then sort or search the data to your heart's content.

It also means that the custom field information will not be carried through the stock management system. i.e. If you capture serial numbers as the stock comes in, you can't draw (or insist on selection) of a specific item indicated by its serial number. The work around would be to create a separate item record for each item, but that is going to be messy i.m.o.

Ultimately, you need to identify your critical control points.
If you are trying to identify lost stock items, you are going to have to record serial numbers at both entry to your stock (capturing bills) and exiting your stock (generating invoices).

If you are trying to identify who bought which item (identified to serial number) and when, you need only capture the serial number in a custom field on the invoice.

The alternative, as I mentioned first, is to purchase a POS (point-of-sale) or a stock management bolt-on system that has the functionality of tracking serial numbers from entry to exit of the system. This is essentially a parallel process which reduces duplication of data capture - a big part of going with integrated software solutions in the first place.

These bolt-ons do exist. It has become a lucrative support industry that adds custom solutions to the fundamental accounting platform provided by Quickbooks. But as pointed out, being quite specialised and (unfortunately) we need to purchase EU based versions as the USA bolt-ons are not compatible, they are fairly expensive in a typical micro or very small business environment. Quite commonly the EU version of an application is near double the cost of the USA equivalent.

The problem seems to be mainly one of scale - there are simply way more customers in the USA and amortisation of the development costs is spread over a bigger customer base.

There are two particular bolt-ons I've been looking at quite closely - one to handle multiple stock centers (which has the functionality Duncan is looking for among other things), and the other is a rather sweet scheduling and CRM solution.

To cut a long story short, there is a fairly simple work around using custom fields, but it would be more a parallel system than an integrated solution.

[Dave A]

I don't quite follow

That is if you are generating serial numbers. You create a control field (commonly the last digit) that is determined by applying an algorithm to the preceding numbers. It ensures that if there is an error at data capture time, there is a 1 in 10 (if using numeric) chance that the system will still accept the number entered as valid. Perhaps even less of a chance I guess if the error is merely one of the digits.

[Graeme]

Check digits

These are extra digits added to the reference number to check that the ref No has been stated correctly; ie without wrong or transposed figures. Say the ref no is 1234567. Add 1234 = Total 10. For the figures 567 to add up to a number ending in 0, the figure 2 must be added: 1+2+3+4 = 10, and 5+6+7+2 = 20. Both end in 0. Check digit is 2. That is a grossly simple and not foolproof example; in practice a rather more complicated routine would be used. If you wish to avoid unauthorised compilation of account numbers, diabolically complicated routines may be used to establish the check digit (or alpha); I doubt if you would ever figure out what routines the banks use for account Nos. Sometimes the check digit is preceeded by a dash. The beauty of computers is that these check-digit calculations are done in a flash.

Years ago I wanted to build the National ID Number routine into some software I was writing and in my then naive state I phoned Pretoria to ask them what their routine was - the official I spoke too went bananas - complete sense of humour failure! I thought they were going to come down to Durban and arrest me!

Whenever I wrote software I always used to program in a routine that would reject an incorrect reterence number upon attempted entry.

[Gillette]

Quickbooks

Duncan - I have had the exact same experience with stock in quickbooks in 3 different versions. The problem I have found as well is that quickbooks also does a average costing on my goods and I needed a Point of sale package that gives me exact codes and FIFO (first in first out) costings and codes. I have found a point of sale package that generates its own bar codes that you can set at a very reasonable cost, and now i am just doing the accounting side with quickbooks. The company that has got this point of sale package if you are interested is at the following link www.goldencreations.co.za. Then I looked into the new Microsoft Accounting 2007 and I am very impressed because it will give me everything in one package. The only problem is it is not released or have support for SA yet. I am just waiting for that.

[Dave A]

The problem I have found as well is that quickbooks also does a average costing on my goods

Hmm. Are you sure that's right?

When you generate a stock report, it does give an average cost value and a lastest paid value. But when the item is expensed at sale, surely the FIFO principle is applied in calculating the expensed value for that transaction?