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
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.
1 4. COUNTING THE STOCK/INVENTORY
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;
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!
Cheers.
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.
You may also be interested in HOW TO MANAGE PURCHASES, SALES & INVENTORY BALANCE ON EXCEL
Comments
Post a Comment