Do you run a Supermarket, superstore or small shop and you deal with a whole lot of Product items? How often do you carry out a stock count exercise? I know that you are aware stock is a very sensitive matter in business and if wrongly managed can kill your business.

Imagine that you have invested a huge chunk of your working capital into the Products that you sell or use in making other products and when you think you have enough of that product to meet a customer’s order or to make a particular product only to realize that there is shortage of that product or it is not even available.

A client once had an experience in her Pharmacy where she employed a young sales girl to manage the place. She didn’t carry out routine or month end Stock Count. The sales book indicated that there were poor sales and it was evident on the drug shelf as there were so many products displayed on the shelf. After a while the sales girl resigned and left the employment and that was when the business owner realized that the products displayed on the shelf are empty boxes/packets of the products. The Sales girl sold the products without the boxes/packets.

I believe that you do not need a further article to explain to you that routine Stock Count is vital for your business to survive. I will post another article to explain to you the Importance of Stock Taking and the dangers of not taking regular stock of your products. I will also explain to you the various types of Stock/Inventory and the risk they face in the business if not properly managed.

In this article, I want to show you a simple Excel Template that will enable you to count your inventory/stock properly and regularly. This Template was designed because of the following reasons; 
1. A lot of business owners are too busy to look into their stock list not to talk of counting them regularly. 
2. Some businesses have a long list of Product items and most times a lot of time if not days are set outside to count stock. This they can’t afford on a regular basis. 
3.They don’t have the adequate manpower to even run their businesses not to talk of stock count.

I can go on and on but I need to stop here and allow you to list out more Reasons why you think regular and routine Stock Count cannot be achieved. Kindly go to the comment box below and tell us why it is difficult for you to take stock in your business and we shall provide you with possible solutions or you can ASK THE ACCOUNTANTS FOR FREE for assistance in carrying out a routine Stock count.

I am introducing to you an EXCEL STOCK COUNT TEMPLATE. This Excel Template will enable you take physical Stock count of your Inventory Items be it in a Supermarket, Warehouse or Store. The Template works with a Bar-code Scanner. So, the Template is for products that have BAR-CODES. If some of your product don’t have a bar-code, then you can isolate those products and count them manually without a scanner. This Template is user friendly and you don't need to have any accounting background to use. Even if you are new to Excel, this Template is easy for you. Ensure that you see the Video Tutorials.

Template Details:
1. Number of Inventory Items to count: 200 Different Product Types for the FREE TEMPLATE VERSION and above 200 different Product Types for the CUSTOMIZED TEMPLATE VERSION & PAID VERSION from the STORE.
2. Limit of Data Entry and Quantity Count: No Limit
3. Mode of Data Entry: Direct to Database. No User-form
4. Method of Calculations: Excel Formulas. No Macros. No VBA.
5. Any Additional Cost after buying: No! One off Payment.

Note: This Template will work well on Excel 2010, 2013, and 2016. It can also work on 2007 version. Always ensure that your ENABLE CONTENT and ENABLE FOR EDITING if anything like it pops up after downloading this Template. You can request for Further Customization if you need us to add any feature to the Template you have already downloaded.  See Video Tutorials below;

How to Use this Template:
       1. Download the FREE OR PAID TEMPLATE from the FREE EXCEL TEMPLATE & STORE 
If you download this from the FREE TEMPLATES Page, then you will see the sheet below once you open the Excel File.

The Page above is just a warning page and nothing really to worry about.  Once you are ok with this page, kindly click on NEXT. It will take you to the STOCK REPORT SHEET as shown below;

2. Stock/Inventory Arrangement:
        It doesn't matter how your Inventory are arranged in the shop or warehouse. It doesn't matter the location of the products. For example, you could have one Product stored in different shelves. You don't have to finish counting that product from shelf to shelf before you go to the next product. You can start from the first shelf and count any product that you come across. The Excel Template helps to group all Product with the same BAR-CODE as one product and sum up the quantities from all Shelves. Is this not lovely and dynamic? 

3. Copying Product Names, Codes and Groups into the Excel Template:
The first thing you do when you are in the STOCK/INVENTORY COUNT REPORT Sheet is to paste the Names and Codes of your Product into the Template. When you EXPORT the Product Records from the Software you use in your business (either Accounting Software or Sales System), ensure that you put them in headings like this on an excel sheet;

Product Barcode: This is the Code that comes with the Product. You can look for it on the body or pack of the product.

Product Name: This is the Name of the Product as known by Customers or assigned by the Manufacturer.

Product Group: This is the Group in which the Product falls. This is not really necessary for this Template but if you are a good user of Excel you will find this useful after the stock count. For example, Lumia 535 could be grouped as Lumia Phones or Phones depending on the nature of your Product Grouping/Classification/Categories.

Product Book Balance: This is the balance of the Products in terms of quantity as captured in your Accounting or Sales System. It could even be the balances you have recorded in your Stock Book.

With your Product listed in the above format what you will get will look like this;

The Remarks will return a “Shortage Message” because you are yet to count the stock and input the physical count quantities. The Physical Count Balance Column will be populated by the system once you start counting your stock.

Once you have arranged the stock list in this Template then you proceed with the counting. You can have many people counting with this Template installed in their computers/laptops but you must ensure that they count their ASSIGNED SHELVES to avoid DOUBLE COUNTING or MISSING COUNTS.
The Stock Count Page looks like the picture below;

Double click on the row (cell) under the Product Bar-Code column. You must have connected your Bar-code scanner to your computer by now. Get a Product and scan the Bar-Code. The Quantity Column for that row (cell) will return with the value 1. Then the cursor will jump to the next cell. Keep Scanning. You are good to go. Once you are down, go back to your STOCK COUNT REPORT and view the report. It would have looked like the picture below;

We hope this article on STOCK/INVENTORY COUNT ON EXCEL USINGE BAR-CODE was useful. Kindly Leave a Comment below.

If your Stock list is more than 200 items then you can buy the Template below for 500 Product List;

You can CONTACT US for Suggestions and Other Questions.

Be the first to receive our articles. Subscribe Now!

You are free to ASK THE ACCOUNTANTS any question and get quick response to your questions. Just drop your question on the space for ENTER YOUR COMMENT on this ATAs Page.