Thursday, January 5, 2012

Year-End Inventory Template**

While it's not quite tax time, there is a very important year-end step when it comes to all of those blanks we have laying around! Even though I am a former CPA and tax accountant, I must admit that I have had to back in to my year-end inventory before because I forgot to take a year-end count. I realized in February that I forgot to count at year-end so I had to take my February count and add back all of the items I sold between year-end and my February count date. Trust me - it was not fun! So hopefully this inventory template will help you take stock of your inventory now so when it is tax time you are ready to just pop in your year-end inventory number and keep on rolling!

Here are a few screen shots and explanations of how it works.

Inventory Template 3

First, there are 9 columns. Most are self-explanatory, but here is a quick rundown:

*Gender: Boy, Girl, n/a
*Style: I use this column for the type of shirt - Crew, Frill, Lettuce Edge, etc.
*Sleeve: Short, Long
*Color: any
*Size: you can enter however you like, just make sure you are consistent. For example, don't enter 2T on one row and 2 T on another row. (Did you catch the difference? I put a space between the 2 and T on the latter).
*Price: This is the price you paid for the item. It might be necessary to pull some invoices if you order wholesale. Or if your blank still has a pricetag on it you could use that (be sure to adjust the price if you think you bought it on sale.)
*Quantity: The number of the specific item that you have in stock.
*Value: This column is a formula. It multiplies the Price column by the Quantity column. DO NOT ADJUST THIS COLUMN.
*Low?: I set up a formula in this column to let you know which items are low in stock. If you only have 1 of any item left, the word "YES" will appear in the "Low" column. If you choose to keep this inventory spreadsheet year-round, this will help you know when to re-order certain sizes or when to run out to Wal-Mart/Old Navy/etc. to stock up!

There is also a Total row at the very bottom of the spreadsheet.
Once you have entered your items, the Total Row will give you the total year-end inventory number. Woo hoo!

The first time you fill this spreadsheet out will be the toughest. Going forward you will just be able to edit the quantity and/or price column and add whatever new blank items you acquire throughout the year.

Important: Once you have your year-end inventory entered into the spreadsheet, be sure to save that document as 12.31.2011 Year-End Inventory. Do not make any other changes to that file. I like to print out my year-end inventory just so I have a hard copy in case something funny happens and I accidentally write over the information. It happens to the best of us. Sigh.

If you want to build on your 12/31/2011 workbook for the current year, the first thing you need to do is to save the 2011 workbook as a 12/31/2012 Year-End Inventory workbook. This will ensure that you do not change your 12/31/2011 inventory numbers. Basically, you will have a workbook for each year. You can modify the workbook throughout the year, but at each year-end the workbook becomes final!

Once your data is entered, you can manipulate the columns to give you some important information quickly. For example, if a customer wants to know if you have enough inventory to make several "Boy" shirts, you can tell the spreadsheet to just show you "Boy" items. Here's how:

Screen Captures1

Once you have followed those steps, your data should look like this:

Inventory Template 2

Notice there are only "Boy" items listed. The Total row also updated to show you how much in "Boy" inventory you currently have in stock.

Looking at that data though, I want to adjust it a little bit more. I want the "Boy" shirts to be in order from smallest size to largest size. To do this, I click on the dropdown box in the "Size" column and click on the "Sort A to Z" row.

Inventory Template  - Sort

Here is my "Boy" inventory sorted by "Size":

Inventory Template

When you are ready to go back to viewing all of the data again, simply click on the dropdown box for whatever columns you chose to filter and click on the "Clear Filter from ______" row. You can tell which columns have a filter applied because the dropdown arrow has a funnel looking icon next to it.

Fullscreen capture 152012 81116 PM.bmp

So there you have attempt at a year-end inventory template for all you embroiderers out there! I hope this helps!

To download the spreadsheet, simply visit and click on the "Forms" category. This spreadsheet is on sale for only a penny, but while you are there, feel free to pick up one of your favorite designs! And remember, our Valentine designs are currently 50% off!

My best advice - count that inventory now! :)

**NOTE: This post is for those of you that have a small embroidery business and need to count and total your year-end inventory. This blog post and template are meant to be a helpful tool for inventory counting and is not accounting advice.

No comments:

Post a Comment