Inventory Management: How to set up a simple, practical spreadsheet

Inventory Management How to set up a simple, practical spreadsheet.jpg

Yesterday on the podcast we talked about the importance of setting up an inventory management system for your craft business that helps us predict what our next products should be and how we can use that to formulate our sales strategy and later a marketing strategy going into new seasons, or upcoming shows. In fact, it’s a really useful tool even if we sell completely unique gift ideas every time.

 

What inventory management systems are there?

There are two main types; either a simple spreadsheet that you manually set up and work with, or a cloud-based software that you link to your Etsy shop, website, or multiple sites together. An example of a Cloud-based system is one called SynCommerce, which I blogged about here (An Interview With SynCommerce The Multi-Channel Inventory Tool).

If you are starting out or have a small budget, then using a spreadsheet is adequate enough to begin with. You can even have this on your computer or use an online spreadsheet provider.

 

How to set up a simple, practical spreadsheet as an Inventory Management System

I’m using Excel for this example inventory management spreadsheet, but you can use other software like Google Sheets if you want to keep yours online.

 

Here’s a step by step guide;

  • Open Excel

  • Select a blank document

inventory management spreadsheet1.PNG
  • Save as ‘Inventory Management Spreadsheet’

  • Type in rows 1-3;

    • Business Name

    • Tax Year

    • SKU Number (the unique number you will give each product)

  •  Next to SKU Number in the adjacent columns you will write;

    • Item Description (B3)

    • Colour (C3)

    • Size (D3)

    • Cost Per Unit (E3)

    • Wholesale Price Per Unit (F3)

    • Retail Price Per Unit (G3)

    • Original Quantity (H3)

    • Date Entered System (I3)

    • Date Sold (J3)

    • Number Sold Wholesale (K3)

    • Number Sold Retail (L3)

    • Where Sold (M3)

    • Notes About Sale (N3)

    • Qty Remaining (O3)

    • Cost of Total Stock Holding (P3)

    • Turnover from Sold Items Wholesale (Q3)

    • Turnover from Sold Items Retail (R3)

    • Fees for Sale (S3)

    • Profit from Sales (T3)

    • Remaining Cost of Stock Holding (U3)

  •  Select the top left triangle

inventory management spreadsheet2.PNG
  • Select all borders from the border drop down box.

inventory management spreadsheet3.PNG
  • Select row 1-3 and make bold

  • Select row 4 of columns E, F, G. Left click on mouse and select ‘format cells’. In the pop up box select ‘currency’ and choose your currency remembering to keep 2 decimal places. Press ok and this will change those columns into currency figures.

inventory management spreadsheet4.PNG
  • Do the same in Row 4 for columns P-T.

  • In row 4 column F enter the following formula; =Sum(E4*2)

    E4 is the cost column multiplied by 2 which is the wholesale mark up in this example. Change as appropriate. Type ‘10’ in the cost ‘E’ column (which should appear as £10, or which ever currency you selected) so you can check your formula gives you ‘£20’ in column F. If your wholesale rate is higher, say 3, then change the ‘*2’ in the formula of column F to read ‘*3’.

  •  In row 4 column G enter the following formula; =Sum(F4*2)

F4 is the wholesale price column multiplied by 2 which is the retail mark up in this example. Change as appropriate. Your formula should give you ‘£40’ in column G. If your retail mark up is higher then change the formula as you did for column F if there was a change.

  •  In row 4 column H (original quantity) put ‘100’, this will help us test the next formula.

  • In row 4 column K (number sold wholesale) put ‘0’, this will help us test the next formula.

  • In row 4 column L (number sold retail) put 50, this will help us test the next formula too (meaning you have sold half under retail pricing).

  • In row 4 column O (Quantity Remaining) enter the following formula; =SUM(H4-L4)

    This should calculate what stock you have remaining. Your formula should give you ‘50’ (half the original stock in this case).

  •  In row 4 column P (Cost of Total Stock Holding) enter the following formula; =SUM(E4*H4)

    This should calculate and give you ‘£1000’ in this example. This shows you exactly how much your stock is worth.

  •  In row 4 column Q (Turnover from Sold Items Wholesale) enter the following formula; =SUM(F4*K4)

This should calculate and give you ‘£0’ in this example, because we sold no items at wholesale.

  •  In row 4 column R (Turnover from Sold Items Retail) enter the following formula; =SUM(G4*L4)

This should calculate and give you ‘£2000’ in this example, because we sold half of the stock at the retail price.

  •  Type 10 in Fees of Sale column S as an example (giving you £10).

  • In row 4 column T (Profit from Sales) enter the following formula; =SUM((Q4+R4)-(((P4/H4)*(K4+L4))+S4))

This should calculate and give you ‘£1490’ in this example. This gives you the amount of money you made after fees and the cost of the original stock you just sold.

In other words: Profit = wholesale and retail turnover – (cost of stock sold so far + fees)

  •  In row 4 column U (Remaining Cost of Stock Holding) enter the following formula; =SUM(E4*H4)-((K4+L4)*E4)

This should calculate and give you ‘£500’ in this example, because you have sold half of your stock and the cost of half remaining to consider.

  •  Select columns A-U in row 4 and press the bottom right corner of this selection, holding the mouse button draw down the page a few more rows.

inventory management spreadsheet5.PNG

This will have copied and pasted the formula to subsequent rows. You will notice that the numbers change but that’s because excel has increased the price in the cost per unit column (trying to be helpful). This doesn’t matter because you will adapt this column data later on when you input your product data.

inventory management spreadsheet6.PNG
  • Select and change the colour (using the paint pot dashboard symbol) of the following columns from row 4 downward; F, G, O-R, T and U. these are the columns you do not need to enter data in again, as they have now been set up to work for you.

inventory management spreadsheet7.PNG
  • Finally change the name of the sheet at the bottom of the page to the Tax year you are working in. This means that you will be able to copy and paste this form next year to a new sheet (names appropriately) and collect year on year data to help you build your historical sales data in your inventory management system.

inventory management spreadsheet8.PNG

 After this you can input and format until your heart is content, so that your sheet is uniquely yours and contains the content pertinent to your business.

How to use the Inventory Management spreadsheet going forward

Every time you make a new product, or run of products you need to input it’s details into the spreadsheet. Make sure you are accurately describe and cost the product entries. this will help you establish your wholesale or retail prices quickly.

As items sell out keeping your spreadsheet up to date will help you create an historical sales record that is easy to come back to when you are planning future stock, because you can easily see what sold, where, when and how. Yesterday in the podcast we discussed why this is useful, so go back and watch that to see the bigger picture of why you are doing this.

This spreadsheet is also useful for your accounts, because it will help you see how much money you have tied up in stock before you invest further. It will also help you to establish what your future sales and marketing strategy focus need to be.

Takeaway

Even simple inventory management systems can work, if you take the time to set them up in a way that helps you track the information your business needs in order to highlight the important data that helps you plan your sales strategy. This really does make a difference to your craft business.

 

Resources

·         Excel

·         https://www.excelfunctions.net/ - lots of tips and help on formulas and excel functions.

·         Google sheets

·         SynCommerce Interview

 

How was that as a tutorial? Do you have any questions? Did I miss anything? Is it clear?

Let me know in the comments!

 

 

Thank you for reading! I hope that was helpful, or at least thought provoking?

If you have any questions or comments, please pop them in the comment section below.


Inventory Management Spreadsheet

If this blog post has helped you, please share it on Pinterest!