Inventory Management - Does anyone have any suggestions or best practices?

[localpottery]

What are the best practices when it comes to inventory management for an art gallery that carries over 50 artists and each item in the gallery is one of a kind?

Currently we are not using inventory management through Square and have the artists set up as "items" and when we are selling a piece we use the notes section to write down what the item number is. The item number is one we have assigned to it on our manual inventory tracking sheets. We'd like to go ditial with all of this and have explored using "categories" as descriptiors, so if I sell a bowl with the inventory number 302 made by Jane Doe I'd select "Jane Doe" then choose "bowl" in the categories and type into notes JD-302. Then at the end of the month I explort an item sales sheet to get my totals then manually look and read through the notes section, and with a pen and paper cross off 302 from Jane Doe's inventory sheet. You can see this is not scalable, but I need a way to track hundreds of different unique items in our system. Suggestions? Instead of using "categories" as descriptors, maybe write in all the individual inventory numbers like JD-302?  The way we are using categories right now with descriptors is not helpful at all so I feel like there must be a way to incorporate them in differently to solve this problem.

Any input would be appreciated. We're not opposed to completely shaking up the way we are doing things.

Thanks!

[LocavoreStore · Accepted solution]

Some people would suggest something out of the box like Shopventory (which integrates with Square), or other inventory platforms. I prefer to do it myself.

I would first set up a spreadsheet where all new product goes through before being entered into Square POS. Excel or Google Sheets is my jam, so I would start there... If nothing else, but to create unique IDʻs for each artwork piece. I could imagine a very simple version of what you are going for might be done in 2 or 3 worksheets.

1. A Master Inventory Input Sheet where all new artwork is added. This sheet would mostly exist to create a unique ID for each piece of artwork. But it would also house the artist, the price, and the quantity of product, etc... The unique ID would be used in your Square Item information... I would probably store it in the Description field.

2. A Sales Sheet where you would import/paste a Sales Report from your Square Dashboard regularly (thatʻs it).

3. A Current Inventory Sheet that looks at the previous 2 worksheets and populates 2 different tables... 1 table to show all of the pieces that sold, and another table that shows all of the pieces that havenʻt sold (in stock). 

In the first worksheet (Master Inventory), I might create a column for Artist Name, Artwork Name, Price, Quantity, Unique ID, and Sales columns. The Unique ID and the Sales columns are calculated fields, so you would not enter any information there...  The Unique ID might look at the Artistʻs Name, the artwork title, and a row number to make a unique string... For instance: "JaneDoe_ArtworkTitle_101"...Considering that the first row is probably headers, and your data dosenʻst start until row 2 the formula for the Unique ID column might look like this =A2&"_"&B2&"_"&100+ROW(1:1)

The Sales Sheet, I would use only to paste a downloaded Square Summaries report into... So after the product is sold, I would download this report as a .csv file then copy and paste it into a new blank row on this Sales Sheet... That is it for here.

Now a note about the first Worksheet (Master Inventory) which has a column for Sales... Itʻs another precalculated field, so you would not enter information here. All this column does is look at the 2nd worksheet (Sales Sheet) for any matching entries that have the same unique ID in the Description column from your Square Sales Report... If there is a match, it would show a number of items sold (in your case, 1). The formula for this column is a little more complex, but not hard.

The 3rd worksheet (Current Inventory Sheet) would have 2 tables... The first table (Unsold Inventory)  would look in your first worksheet (Master Inventory) "Sold" column for any number that is less than 1, and list all of the data in those particular rows... The 2nd table would look the worksheet (Master Inventory) "Sold" column for any number that is greater than 0, and list all of the data in those particular rows.

If you're not familiar with Excel, or Google Sheets this might sound complicated, but even for a novice, you could easily set something like this up in 10 minutes. 

[Alexandrascraft]

We have a similar situation with some of our items. We have multiple people we buy a similar item from and they may have more than one style that we buy. We have have converted everything to barcodes, but the same idea applies, the first x numbers indicates the person's name we buy the item from, and the last 3 numbers indicate which item it is. In your case you could use the persons name and a description, or a number as you currently are in your inventory, and square will accept it. We've unfortunately discovered that square will accept duplicate named items, but not dublicate barcodes/sku's. 

You can get as technical or not technical as you want. In your case you could make the person who's item it is the category name, and then name each item based on what it is. If you have 50 artists, you'd have 50 categories. That's essentially how we started out. We sell smalls, and have 40 choices of each item. As long as each one is entered as it's own item, and not a variation it's easy to move to more detailed systems. We started out with vairiations which required moving everything to it's own item to move to barcodes. 

For example Jone Doe Category, Dolphin Sculpture 1, Dolphin Sculpture 2, Starfish Sculpture 1, Whale Sculpture 1, Whale Sculpture 2, Whale Sculpture 3, Whale Photo 1, Whale Painting 1, Whale Drawing 2. If Jane Doe works in multiple mediums. If Jane Doe only does Sculptures, You might only list them as Dolphin 1, Dolphin 2, or Maybe Turtle Rock, Turtle Island, Turtle Bridge, Turtle Sand. Whatever identifies them to you when you're checking inventory, selling the item, accounting for the item when the artist is in front of you.