Music Store Inventory Management: Used Gear and Trade-Ins With AI

Give every used instrument its own row: a stock number, the serial number stored as text, a condition grade, what you paid, prep costs, asking price and a status. Log each trade-in on a second tab that links to those rows. Use AI to draft condition notes and listings from your photos and notes, not to value anything.

New stock is tracked by product and quantity. Used gear can't be, because every guitar, amp or horn is a one-off with its own history, condition and price. A spreadsheet is enough for a shop with a few hundred used items and one or two people updating it. If you also need rentals, repair tickets and card payments in one system, compare point-of-sale options first, starting with the music store POS checklist for serials, rentals and repairs.

Follow me on Instagram@sagnikteaches

One row per instrument: where used gear breaks normal stock logic

A shelf of new strings is "12 packs of 10-46". A used guitar is "this guitar, serial as stamped, graded B, bought for $379, on the wall by the window". Quantity-based stock lists can't hold that, which is why shops end up with used gear in a notebook, a photo folder and someone's memory. The fix is to switch from tracking product to tracking the physical item.

Connect on LinkedInSagnik Bhattacharya

The workbook has three tabs. Items holds one row per instrument and is the working sheet. Trade-ins holds one row per transaction: trade-in reference, date, customer reference, whether ID was checked, ownership evidence, the stock numbers received, offer total, cash or credit, and who approved it. Lists holds the drop-down values for status, category, grade and source, so people pick rather than type and your filters keep working.

Subscribe on YouTube@sagnikteaches

One rule governs the whole system: the till records the sale, the sheet records the instrument. When something sells, update its status in the sheet the same day, from the receipt, not from memory. A common cause of double-selling and phantom stock in a sheet-based shop is that update happening late.

The columns on the Items tab, and why each earns its place

If that feels like a lot of columns, start with the identity, serial, money and status groups and add the rest when you feel the gap. The Google Sheets AI prompts tutorial is useful for building the drop-downs and the formulas below without hand-typing them.

Serial numbers: store as text, photograph, flag duplicates

Serial numbers are identifiers, not quantities, and spreadsheets love to turn identifiers into numbers. Excel can drop leading zeros and display long digit strings in scientific notation, and it keeps only 15 significant digits, changing every digit after the fifteenth to a zero (Microsoft's own guidance on keeping leading zeros and large numbers says so). So the first step is boring and essential: format the serial column as Text (in Excel, Format Cells then Text; in Google Sheets, Format then Number then Plain text) before typing or pasting a single serial.

1. Type it exactly as stamped, including letters, dashes and spaces. Write "none" or "illegible" in the cell rather than leaving it blank, so that blank always means "not yet checked".
2. Record where you found it: neck plate, back of the headstock, rear panel, under the bell. The next person to check will thank you.
3. Photograph it at intake with the stock tag in the same frame, and paste the photo link into the sheet.
4. Let a formula watch for duplicates. Say Make is in column D and Serial no. in column H:

=IF(H2="","Not checked",
 IF(OR(H2="none",H2="illegible"),"No serial",
 IF(SUMPRODUCT(($D$2:$D$2000=D2)*($H$2:$H$2000=H2))>1,"Duplicate","OK")))

I use SUMPRODUCT rather than the more familiar COUNTIF for a reason. COUNTIF converts digit-only text back into numbers while it matches, so "0123" and "123" can count as the same serial, and digit strings longer than 15 characters are compared on only their first 15. SUMPRODUCT with an equals sign compares the cells as text. You can prove the point in 30 seconds: type "0123" in one cell and "123" in another, then count with each method. The formula includes the make because different makers reuse similar numbers; extend it to the model if your brands repeat serials within a make.

Add conditional formatting to turn the whole row red when the check says Duplicate. A flag has three common causes, and you want to know which before you pay anyone: a typing slip, the same instrument coming back through the door, or two instruments that shouldn't share a number.

Two further notes. Many makers publish serial charts that give a build year, but some number formats repeat across decades, so treat the chart as a clue, record where you found it, and leave the year blank when you're unsure. And before paying for a trade-in, check the serial against any stolen-instrument register or alert service that operates in your market.

AI has a role here, but as the second pair of eyes. After intake, upload the day's serial photos with the typed serials and ask:

Below are N photos of serial numbers and the serials a staff member typed for
each. List ONLY the mismatches, by stock number. If any character could be read
two ways (0 and O, 1 and I, 5 and S, 8 and B), mark it AMBIGUOUS and say which.
Do not correct anything and do not guess missing characters.

STOCK NO / TYPED SERIAL:
[paste list]

A person looks at every item it returns. The model can miss worn stampings and may read a clear one wrongly, so it never gets to be the first to type a serial or to overwrite a typed one.

Condition grades two staff will apply the same way

"Excellent" means one thing to the owner and another to the Saturday assistant. A shared scale with plain descriptions, tested once, cuts down the arguments. This one is deliberately short:

The grade describes cosmetics and function. It says nothing about value, rarity or authenticity, and your notes shouldn't either. To test the scale, pick five instruments and have two people grade them without conferring. Wherever they differ by more than one step, rewrite the descriptor until they stop differing.

From the tech's shorthand to a condition note

The person who checks an instrument knows the most about it and writes the least. They scribble. AI is good at turning scribble into three plain sentences, provided you stop it embellishing. Here is a prompt that does that, followed by a typical tech note and the result (illustrative).

You write condition notes for a music shop's used-gear sheet.
Use ONLY the TECH NOTES. Do not add a year, "all original", "rare", "mint",
"vintage", "pro set-up", "plays great" or any value claim. Do not add
adjectives the tech did not use (small, minor, light) unless the tech used them.
If something is not in the notes, list it under NOT CHECKED.

Output:
GRADE: (use the grade I give)
NOTE: 2-3 plain sentences. Defects first, then what works, then repairs done.
INCLUDED / NOT INCLUDED:
NOT CHECKED:

GRADE: B
TECH NOTES: double-cut electric, sunburst, maple neck. buckle rash on back,
couple of dings top edge. frets 1-5 worn flat, no buzz. neck straight, truss
rod turns fine. tone pot scratchy, cleaned, ok now. strap button replaced.
new strings 10-46. gig bag, no case. nut fine. didnt check intonation.

GRADE: B

NOTE: Buckle rash on the back and a couple of small dings on the top edge. Frets 1 to 5 are worn flat with no buzz. The neck is straight and the truss rod turns freely. The tone pot was scratchy, has been cleaned and now works. Strap button replaced; new strings (10-46).

INCLUDED: original gig bag. NOT INCLUDED: case.

NOT CHECKED: intonation, year, serial history.

Read it like an editor. The tech wrote "dings", not "small dings", so "small" is the AI's judgement and comes out, even though the prompt said not to add adjectives. Rules reduce that drift; they don't remove it. The tech wrote "gig bag", not "original gig bag", so "original" is invented and comes out. Those two edits take ten seconds, and they are exactly the kind of drift a buyer would later call misleading. Everything else matches the notes, and "not checked" is worth having: it turns an unspoken gap into a recorded one. The tech initials the final note, and that initial is the approval.

Pricing a trade-in: the sum behind the offer

This is where the sheet pays for itself. The method has four steps.

1. Expected resale price. Collect five recent sold prices (sold, not asking) for the same model in similar condition, and take the median. A guitar's five recent sold prices might be $540, $585, $610, $620 and $660. The median is $610. The median beats the average because one freak sale at $1,100 won't distort it.
2. Target margin. Choose the gross margin you want on used gear, as a share of the selling price. I'll use 30% (illustrative; yours depends on your costs and how fast the category turns).
3. Prep cost. Strings, set-up, cleaning, parts and tech time at cost.
4. Offer ceiling = expected resale x (1 - target margin) - prep cost.

Now a customer arrives with three items. The numbers (illustrative):

The ceiling for the lot is $629.50, so you offer $625 in cash. Spread it across the three rows in proportion to their ceilings, which the sheet does: $379 for the guitar, $180 for the amp and $66 for the pedal (they add back to $625). Those figures go into each row's "Cost paid". Check the guitar: sold at $610 with $379 paid and $45 of prep, the profit is $186, a margin of 30.5%. The target holds.

Offer ceiling (per item):   =ROUND(B2*(1-$G$1)-D2,2)
   B2 = expected resale, G1 = target margin, D2 = prep cost
Allocated cost paid:        =ROUND($H$1*F2/SUM($F$2:$F$4),0)
   H1 = total cash offer, F2 = this item's ceiling
Expected resale (median):   =MEDIAN(K2:O2)   (five sold prices)
Margin % after sale:        =(SoldPrice-TotalCost)/SoldPrice

If the customer prefers store credit, you can offer more, because credit costs you the cost price of whatever they later buy, not the face value. A 10% premium, for instance, gives $687.50, rounded to $690. Two cautions. Record the "Cost paid" at the cash-equivalent figure ($625), so your margins aren't flattered by the credit bonus. And ask your accountant how to book the credit as a liability.

AI's part comes after the maths: explaining the offer without revealing your margin. A prompt that takes the grade, the market range and the two options and drafts the message does the job:

Hi [first name], thanks for bringing these in. We've graded the guitar B (light wear, needs new strings and a set-up), the amp B and the pedal A. Recent sales of similar items put the guitar between about $540 and $660, and our offer is worked out from those sales. For all three we can offer $625 in cash or $690 in store credit. The offer is open for 7 days. [your name]

It tells the customer what the offer rests on (grade and recent sales) and omits what it doesn't need to (your margin).

Trade-in paperwork: ownership, ID and a signed declaration

In some markets, second-hand dealers must record the seller's identity, the item and its serial number, and keep those records for a set period, or hold items before resale. The rules vary, and being a music shop doesn't exempt you, so ask your trade body, your licensing authority or an adviser what applies to you before you rely on a sheet alone.

Whatever your rules, a sensible intake record has these fields on the Trade-ins tab: customer reference; ID checked (type, date, staff initials, with the copy kept in your secure records, not in the sheet); proof of ownership (a receipt, an old listing, photos of the item in the customer's home, a warranty card); serial checked against stolen-gear lists; serial photo; and a signed declaration. A short one might read (have an adviser check the wording for your market):

I confirm that I am the owner of the item(s) listed above, that I am entitled to sell them, and that they are not stolen or subject to any finance agreement. I understand that the shop keeps a record of this sale.

Keep customer data out of ChatGPT. The sheet carries a customer reference only; names, addresses, phone numbers and ID images stay in your secure records. The AI steps above use item data alone.

Listings drafted from the row

Once a row is complete, the listing nearly writes itself, which is a good moment to let it. Marketplace limits matter here: eBay allows titles of up to 80 characters and up to 24 photos, as of October 2026. For a wider look at the workflow, the tutorial on how resellers use AI to list second-hand items faster covers photo batches and templates.

Write a marketplace listing from this sheet row. Use only the row.
Title: max 80 characters: make, model, finish, plus up to two facts from
the row (for example new strings, gig bag).
Description: 90-130 words: what it is, the grade in words, the condition
note exactly as written, what is included and not included, then this
policy line: [paste].
No year, no "rare", no "vintage", no value claims.
Finish with the 6 photos we must take.
ROW: [paste the row]

A typical result (illustrative), with the title at 74 characters:

Title: Double-Cutaway Electric Guitar, Sunburst, Maple Neck, New Strings, Gig Bag

Used double-cutaway electric guitar in a sunburst finish with a maple neck. Condition grade B: buckle rash on the back and a couple of dings on the top edge. Frets 1 to 5 are worn flat with no buzz; the neck is straight and the truss rod turns freely. The tone pot was scratchy and has been cleaned. Strap button replaced and new strings (10-46) fitted. Gig bag included; no case. Intonation not checked. Our stock number U-0412; serial number verified.

Photos: front, back, headstock front and back, neck joint, serial plate, each defect, gig bag.

One phrase needs to go: "serial number verified". Your row says the serial was photographed and typed, and nothing about verification against anything. Replace it with your policy wording, such as "serial number available on request". Whether to publish a full serial is a policy call; some shops give it to buyers once they commit.

A monthly 20-minute review: stale stock, drift and the count

A used-gear sheet decays if nobody reads it. Once a month, set aside 20 minutes and work through four formulas and a walk. Replace the column names in the formulas with your own cell references.

Days in stock:  =IF(Status="Sold",DateSold-DateIn,TODAY()-DateIn)
Stale flag:     =IF(AND(Status="For sale",TODAY()-DateListed>60),"Review price","")
Stock at cost:  =SUMIFS(TotalCost,Status,"For sale")
Margin by source: =AVERAGEIFS(MarginPct,Source,"Trade-in")

1. Filter for the stale flag and decide on each item: drop the price, move it to a different channel, or bring it into a promotion. An illustrative ladder: take 5% off at 60 days and 10% at 90, never below the floor price without the owner's say-so.
2. Read the stock-at-cost figure against what you expect to have tied up in used gear, and compare margin by source (trade-in, bought from the public, consignment) to see which is paying.
3. Count. Print the For sale list sorted by location, walk the shop and tick each item. Anything you can't find goes onto a short list, and anything found that isn't on the sheet gets a row. Update Last verified as you go.
4. Chase the gaps. Common causes of missing items are a sale not recorded, an item moved to the workshop and an item on hold that was never released. The causes of stock count variance tutorial is a useful list to work through, and running a stocktake with a phone scanner shows how to speed the walk up.

When you've finished, paste the review rows (stock number, category, days in stock, margin, no customer data) into ChatGPT and ask for five bullets: what is stale, what sells fastest, where margin is below target. It can summarise what you give it; check any figure it quotes, and keep the decisions yourself.

When the serial is missing, illegible or already on your sheet

• No serial at all. Common on pedals and some budget instruments. Enter "none", rely on your stock number, and photograph distinguishing marks (dings, stickers, hardware). Note them in the condition note so the item can be identified later.
• Partly legible. Record what you can read with a placeholder, such as "74?81", mark the check column "illegible", and get a manager's sign-off before paying.
• Duplicate flag. Stop. Compare the photos. It may be a typo, a returned item or an instrument that deserves a harder look.
• The same instrument comes back. Don't overwrite the old row. Give the returning item a new stock number and a note linking to the old one, so the history survives.
• Consignment. You don't own it, so Cost paid is zero, Source says Consignment, and the owner's share is agreed in writing. Keep these out of your stock-at-cost total.
• Customer-owned gear in for repair. It isn't stock. Track it as a repair ticket, not on this sheet.
• Swapped or refinished parts. Write "non-original neck" or similar only if the tech says so. A serial usually belongs to one part, and the note should say which.

Used-gear sheet: questions from music shop owners

Is Google Sheets or Excel better for a used-gear sheet?

Either works, and the formulas here run in both. Google Sheets suits a shop where two or three people update stock during the day, because edits sync and there is a change history. Excel suits a single desktop. Whichever you choose, set the serial column to text first, use drop-down lists for status and grade, and keep one master copy.

Can ChatGPT read a serial number from a photo?

It can often read clear stampings and will struggle with worn ones, and it may confuse 0 and O, 1 and I or 5 and S without telling you. Use it as a second check on a serial a person has typed, asking it to list mismatches and ambiguous characters only. Never let it be the first to enter a serial.

When should a music shop move from a spreadsheet to POS software?

When two or more people update stock during trading hours, when you sell across more than two channels, or when the sheet and the till drift apart more than occasionally. Wanting rentals, repair tickets and card payments in one system is another trigger. Until then, a well-structured sheet is cheaper and easier to adapt.

Can AI work out what a used instrument is worth for a trade-in?

Not reliably. It has no live view of what instruments are selling for and will give a confident number either way. Use recent sold prices from the marketplaces you sell on, take the median, and apply your own margin. AI is better used for drafting the explanation to the customer and the listing afterwards.

Further reads

• Music Store Marketing Ideas: Lessons, Repairs and Used Gear, With AI — Turn the used-gear sheet into weekly posts and a monthly email.
• Game Store Inventory Management: Preorders and Trade-Ins With AI — A parallel trade-in system for a games shop.
• Game Store Software: Trade-Ins, Tournaments and AI Product Listings — Software options when trade-ins outgrow a sheet.
• Boutique Inventory Management: AI for Size Runs and Reorders — Stock logic for goods that come in variants.
• Phone Repair Shop Marketing: Local Search and Trade-In Offers With AI — Trade-in offers promoted by another repair-led retailer.
• Hardware Store Inventory Management: SKU Clean-Up With AI — Cleaning a messy stock list before you build on it.
• AI Based Marketing Tools for a Music Shop: Lessons and Instrument Hire — A music-shop shortlist of scheduling, email and design tools for filling lesson slots and promoting instrument hire across one school term.
• Record Store Software: Catalogue Used Vinyl and Track Stock With AI — A one-row-per-copy sheet, a shared grading standard, AI-drafted listings and a pricing check you do yourself, worked through on a 40-record box.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Microsoft Support guidance on keeping leading zeros and large numbers in Excel; Exceljet and Microsoft Tech Community explanations of how COUNTIF matches digit-only text; eBay seller help on title length and photo limits (checked October 2026). Sample prices and margins are illustrative.