HOW TO MANAGE STOCK-INVENTORY WITH EXCEL (Stock Management Template 1)

Have you ever encountered the challenge of managing the stock of items in your business? No matter how many Items there are in your Inventory list, they can be managed. There are so many stock management software and templates available so you to choose and use. One of the many difficulties encountered with Stock Management Systems is the ability to Configure and set up correctly to achieve desired results. Sometimes the challenge could be having the trained personnel to handle the system. Getting a good system is a good thing to do but getting the personnel who will man the system trained and equipped is as important as getting the system.

Maybe you run a small business and thinks it’s expensive to get a Stock Software. Or you can’t afford to hire an extra hand to keep the Stock Records. But there is need to maintain a good stock/inventory system. If you have any of these challenges listed above then it’s not time to give up. You can maintain a good Stock Management with the help of an Excel Workbook. I have put together a simple Stock Management System with the aid of Excel program. I call it the STOCK-INVENTORY MANAGEMENT TEMPLATE 1. This Templates will help you maintain your Stock/Inventory;

1. When you Receive/Buy Stock

2. When you Issue/Sell Stock

This Template will be useful for the following Outfits;

1. RETAIL BUSINESS

2. WHOLESALE BUSINESS

3. MANUFACTURING BUSINESS (To maintain Stock of Raw Materials to be used for Production)

4. MANUFACTURING BUSINESS (To maintain Stock of Finished Goods to be Sold)

The features of this Template include;

1. Recording Stocks that come in (Items Purchased, Items Produced, Items Returned by Customers, Items Transferred from another Branch)

2. Recording Stocks that goes out (Items Sold, Items Issued for Production, Items Returned to Suppliers, Items Transferred to another Branch)

3. Maintaining Stock Balance (Real Time Balance)

So, in summary, the Template enables you do the following;

1.  STOCK IN

2. STOCK OUT

3. STOCK BALANCE

Let’s get started with the Template. Get the Template on our FREE TEMPLATES PAGE on ITEM NUMBER 7. 

Before you proceed, ensure you get the Video Tutorials so you can practice along with the Video. Get the VIDEO TUTORIAL HERE

Once you get the Template the first sheet you see is the WARNING PAGE. Click on "NEXT" and move to the SET UP PAGE;

1. SET UP COMPANY DETAILS:

2.  SET UP PRODUCT LISTS AND GROUPS:

Steps:

a.      Type in the List of the Items in your Inventory under the PRODUCT NAME Column.

b.      Assign Product Codes to them under the PRODUCT CODE Column

c.       State the Group that each Product belong to. See Examples below;

3. SET UP YOUR PARTNERS:

Your Partners could be the following depending on the nature of their impact on Stock Movement;

 Customers are your Partners

Suppliers are your Partner

Production Department could be your Partner

Other Branches could be your Partner

Steps:

a.      List the Names of your Partners under the PARTNER NAME Column

b.      State the Type of Partners you have Listed under the PARTNER TYPE Column

4. DAILY STOCK RECORDING:

Once you are done with the Set Up then you are good to go. Just begin to record your Stock Movement in the STOCK RECORD WORKSHEET as shown below;

In recording the Stock movement, you need to understand the various Headers in the Template. These include;

a.      Date Header: Record the date the Stock was received or taken out

b.      Inv. Number Header: Under this header you should key in the invoice numbers of Vendors or Invoice numbers to Customers

c.       Type Header: You have to select STOCK IN or STOCK OUT. STOCK IN is when you receive Items of inventory.  It depends on the Nature of your Inventory List. Are they Raw Materials? Finished Goods? Items held for Production? Do you sell wholesale? Or you retail? STOCK OUT is when you issue or sale an Item of Inventory.

d.      Partner Name Header: You should select your Partner from the drop List. If you are Purchasing goods, the Partner will be a Supplier’s Name. If you are Selling goods, the Partner will be a Customer’s Name. If you are receiving Raw Materials to be held for Production the Partner could be the Purchasing Manager’s Name. If you are issuing Raw Materials to Production, the Partner could be the Production Manager’s Name or whoever received the items.

e.      Product Name Header: You need to select the Product you want to receive or issue out from the cells under this Column. You have done the Product Set Up correctly, then all the Items of Inventory will appear on the List when you click on a Cell under this Column.

f.        Quantity (Qty) Header: You should go ahead and type in the Quantity of a particular Item you are STOCKING IN or STOCKING OUT. Be careful to choose a Quantity type. If you STOCK IN in UNITS then you need to STOCK OUT in UNITS. If you STOCK IN in PACKS then you need to STOCK OUT in PACKS. If you STOCK IN in CARTONS then you need to STOCK OUT in CARTONS. If you STOCK IN in KG or any other MEASUREMENTS then you need to STOCK OUT in KG or that MEASUREMENTS in which you STOCK IN. This is very important! It makes your STOCK BALANCE represent a true picture if what it should be. There is no point STOCKING IN in CARTONS and STOCKING OUT in PACKS.

g.      AMOUNT: This is the Unit Cost of Purchasing or the Unit Price of Selling. It could also be the Unit Cost of Production or the Unit Cost of Raw Materials. It depends on what you are STOCKING.

This is how you maintain your Stock Records from day to day. If you have done this correctly then it should look like this for a Retail Business who is in the Retail Business of Electronics.

5. DASHBOARD:

The Dashboard shows you the Stock balance for;

a.      TODAY (Real Time Balance)

b.      YESTERDAY (Balance before Today)

These Stock Balances are reported in;

a. Products Names (helps you to know the balance for each Inventory Item as at Today)

b. Product Groups (Helps you to know the balance for each Category of Stocks. E.g. the total number of LAPTOPS (HP, Dell, etc) in the Store.

We hope this article on STOCK MANAGEMENT TEMPLATE 1 was useful. Kindly Leave a Comment below. You 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 BOOK KEEPING IN BUSINESS.