This is part 2 in my series about how I do my inventory lists. Last week I started the series talking about To Do lists. In this post I am going to continue and start talking about my master price list and how it drives all the other sheets in the Excel file. This series is 4 parts:
Part 1 - To Do List
Part 2 - Master Price List
Part 3 - Packing for a Fair and Prep
Part 4 - Tallying after a Fair

We are going to continue along in the sample file that you can use for your own studio. Reminder, I recommend downloading and using Excel vs Google Sheets as some of the formulas may not work, or work differently, in Sheets. I use Square for my credit card transactions and my sale web site. In Square I have Categories and Items setup and they are repeated in Excel. I use excel in addition to Square because I can visualize the detailed data better in Excel. It's also easier for me to see the history of shows in Excel than in Square.
This blog post focuses on the Master Price List worksheet (or tab) at the bottom of the workbook.


This worksheet is really simple, but it drives the rest of the tabs. The first column is Category. This is just a grouping of items. Your categories can be anything that makes sense for you. They can also change over time as your work changes. I find that things which may start in my Misc category often end up as their own category. I try to not have more than 10 or so categories because for me it gets difficult to manage when it's more than that.

The next column is Item. You'll see that for some of my items I repeat the category. You don't have to do this, but in the next post in this series when we talk about prep for the fair when I sort on Items it will make more sense why I do this. When I first started out, I had a whole lot of individual items. As I've done more and more shows, I've found that I can condense a lot of items which makes it easier to find when I'm at a show.

The last column is price, which should be fairly self-explanatory. It is important to keep the price here and the price in your credit card tool in sync.

When I was first pricing my items, I really had no idea what I was doing. I read a lot of posts on Facebook, but I really didn't know how to price stuff. So I started by putting all my items in Excel and then putting a price that I thought made sense. I know that there are calculators out there for materials + labor, but this isn't my primary job. I'm not trying to get rich making pottery; my goal is to be able to enable my hobby to pay for itself and continue to use it as my way to de-stress. Once I had the prices in for everything I sorted on price and wanted to see if things made sense. I found that I had weird discrepancies. Why would someone pay $30 for a mug and $45 for a bowl? I adjusted the prices so that items that were like sized and like amounts of work were like priced.

Now it's time for me to geek out on Excel for a bit. Excel tables are one of the most powerful tools you can use. This sheet is a table that is named Inventory. You can see the name by clicking anywhere in the table then selecting Table at the top and then the name will be on the left. Tables are powerful because now anywhere in this workbook I can use the name Inventory in a formula, and it knows that I mean this table. You can name your tables anything you want as long as it starts with a letter, has no spaces, and isn't an Excel reserved word (e.g., you wouldn't want to name it something like Sum since sum is a formula to sum numbers).




This is an example of a formula on another worksheet where I am referencing the Inventory table. We'll talk more about this formula in the next post in this series Part 3 - Packing for a Fair and Prep.