looking for a parts inventory spreadsheet template

[Jim1972jvc]

Hi all

I've been just getting back into my electronics hobby after many years, and I'm starting to accumulate a lot of different parts (resistors, capacitors, diodes, etc.) I'm looking to create an excel spreadsheet to keep track of my inventory, and I was wondering if someone out there has a template that they already use and they could share with me? I want to make sure I include all the necessary parameters so I can keep track of all my parts quickly and accurately.

Thanks!
Jim

 

[(*steve*)]

It depends on what you want to use your data for.

Here are the columns in my spreadsheet:

A ID - a number that I use to tag bags, tubes, reels, etc. (just goes from 1 up to ... n)
B Manufacturer - not always known
C Part Number - manufacturer part number
D Type - broad classification, semi, passive, hardware, etc.
E Subtype - more narrow classification, e.g. passive divided into resistor, capacitor, inductor (and also resistor array, resistor pot, resistor trimmer)
F Sort - Contains something to sort by. e.g. resistor, capacitor, inductor value, transistor Vceo, etc.
G Tolerance - List tolerance (for resistors, capacitors, inductors, voltage references, etc.
H Package - the package (TO-3, SO-23-5, etc)
I Style - mostly SMD, or through hole (TH)
J Description
K Data - Do I have the datasheet for this component
L Qty - quantity when purchased
M Qty Used - Quantity used
N Date - when the part was delivered
O Cost ea - Calculated from cost and original qty
P Retail Each - typical price in quantities of 1
Q Bulk each - typical price in larger quantities (qty originally bought typically)
R From - Where I got them from
S Part No - supplier's part number
T Location - My storage location
U Total (1) - calculated value of parts remaining at purchase price (inc freight)
V Total (2) - calculated value of parts remaining at 1 off price (not inc freight)
W Total (3) - calculated value of parts remaining at bulk price (not inc freight)
X Notes
Y Cost - cost of line item
Z Order - total cost of order including this line item
AA Freight - freight for this order
AB Accum Second level of accumulation
AC Freight Second level of freight

Sounds complex, but it allows me to locate parts quickly, list them in an order suitable for a first approximate search (to see if I have suitable or alternate parts), allows me to cost projects based on replacement cost of parts, and it gives me a total value of inventory for insurance purposes.

If you have different needs, you may require fewer, or different columns.

edit: I have added the column letters and 2 additional columns I have because I get my Sister-in-law to send me stuff sometimes where people refuse to ship outside the US or where they want *way too much* to do it (e.g. places that insist on using international courier delivery)

 

[Jim1972jvc]

awesome! thank you Steve. you included several parameters that I would not have thought of but make a lot of sense.

 

[(*steve*)]

If you're interested in the formulae, here they are:

Titles in row 1, except for columns U/V/W that contain totals

U1: sum(U2:U2000) Make it large enough that you don't have to change it all the time
V1: sum(V2:V2000) Make it large enough that you don't have to change it all the time
W1: sum(W2:W2000) Make it large enough that you don't have to change it all the time

O2: =(Y2+Y2/IF(Z2=0,1,Z2)*AA2+Y2/IF(AB2=0,1,AB2)*AC2)/L2 Cost plus proportional fright (proportional based on cost)
U2: =(L2-M2)*O2 Quantity remaining times actual cost
V2: =(L2-M2)*IF(P2>0,P2,IF(Q2>0,Q2,O2)) Qty remaining times (qty 1 price, or actual price if no qty 1 price)
W2: =(L2-M2)*IF(Q2>0,Q2,IF(P2>0,P2,O2)) As above, but use prices for bulk qty, then unit qty, then actual

The only real bug is that if your qty is ever blank or zero you get #VALUE errors, but that only really happens whilst entering new rows from scratch (I copy the row above and edit).

For things like wire or heatshrink tubing I set the qty to length -- this may be a bit anal, but it does give you an estimate of qty remaining (note that I *DON'T* have a QTY remaining column -- you might add one!

You might also like to note down the name of the datasheet too.

I have all my datasheets in a series of directories categorised similarly to the type/subtype thing in this spreadsheet (but more detailed) I also rename the datasheet so that it more closely refers to what I I have. e.g. you might see one labelled "CRCW - thick film resistors.pdf" or "FDC6561AN Dual N channel logic level MOSFET 2.5A 30V 0.15 Ohms.pdf".