- This topic has 0 replies, 1 voice, and was last updated 8 months ago by Aruorihwo.
- January 31, 2020 at 11:35 am #84887Participant@aruorihwo
If you are a small business owner and are really serious about building and managing your brand properly, then getting a good and easy to use inventory system should be at the top of your list. But since you are a small business, probably just starting out, it might be above your budget to pay for an inventory system. There are a lot of free templates available online but you might not be able to tweak them to fit your business properly.
For those that don’t know, an inventory system is a tool used for managing and keeping track of stock information. This means that it tracks every product in your warehouse or store, their prices and their quantities. It also keeps track of inventory related tasks which include shipping, purchasing, receiving, storage, turnover, tracking and recording of stock
Most people don’t realize how powerful Excel is as a business tool. It’s capabilities range from simple things like keeping information in a tabular format to complex things like building management systems. An inventory system is one of those management system Excel excels at (pun intended). Building an inventory management system with Excel is easier than you think, more so if you have a little knowledge of how to use Excel. But even if you don’t, after reading this article you should be able to build a simple system to use for your business. Here are the steps to building your own inventory system.
Creating a product list (Stock IN)
The first thing you need to do is create a list of all the items you plan to sell, including things like prices, name, quantity, you can decide to give each item a unique code. Follow the steps below to create your product list;
- Enter headers in cell A1 to H1. You need to enter headers that would help track your products. The headers that we would be using are Product Id, Product, Supplier, Product Category, Unit Cost, Quantity, Total Cost, and Date respectively.
- Insert the appropriate details into each cell matching the headers
- Enter the formula for calculating total cost. To calculate your total cost, in the G2 cell enter
=PRODUCT(E2,F2). To replicate this formula for the other cells, put your cursor at the edge of the cell on the dot, and drag down to where you want it to stop or just double click, all the data in that cell with replicate itself all the way down.
- Add Currency to your money fields. To put symbols such as currencies, click the drop-down and go to “more number formats” and choose what currency you want, adjust your decimal points. The same process goes for the date.
Creating Sales List (Stock OUT)
Your inventory would be useless if you don’t take note of all the sales you make. To do this follow the steps below to create a list of all sales made.
- First, you need to create a new sheet, this can be done by clicking the plus sign at the very bottom of your current sheet. To rename each sheet, just double click on it and type.
- Enter headers in cell A1 to H1. This time, the headers have to be relating to stock going out, i.e. Sales. The following headers that we would be using are Product Id, Product, Customer, Unit Cost, Discount, Payment Mode, Amount, and Date respectively.
- Insert the appropriate details into each cell matching the headers
- Calculate total sales amount. To calculate your total amount including discount, enter formula, =D2*E2*(1-F2) in the G2 cell. Leave the space for discount blank if you didn’t give your customer any discount. Ensure you include the percentage sign to enable it to calculate correctly. To do this just click on the column to highlight it and then click on the % sign in the home toolbar.
Creating a Report Sheet
Now that you know all the products coming in and going out, you would need a report sheet to help you quickly know how many of a certain product you have left, how much you have made off a product, and more.
- Create a new sheet and change the name of the sheet to Report
- Enter details you would like to be able to lookup
- We are going to be using the product ID to search and get details about each product. So once you enter a product ID all the other details would turn up.
- The first thing to do is to name the tables on each sheet. This would make entering your arguments easier as you wouldn’t go back and forth on each sheet. To do this go to the first sheet (Stock IN), select all and at the top left corner of your sheet just beside the formula bar, you would see a small box with a cell number, click on the number and rename. Do not click on the arrow.
- Repeat step 1 for the Stock OUT Sheet.
- Take note of the column each of the items you want to pull up is. For example, Product is in the second column
From our table, the product names are in the second column. The item code we want to lookup is whatever code in cell B1 so our argument would be;
=Vlookup(B1,IN,2,False).In case of an error or if we enter an invalid product, we don’t want it to come up as #N/A or #VALUE! So we would use the
IFERRORargument, that way we can control what pops up when an invalid item is entered,
=IFERROR(Vlookup(B1,IN,2,False),“whatever you want to appear”).
Total Stock In
To find the total stock in, enter into cell B3,
=IFERROR(Vlookup(B1,IN,6,False),“whatever you want to appear”).
Total Stock out
Since you are probably not going to sell your stock all at the same time to the same person, you are most likely going to be entering each product multiple times in the stock out sheet. If you use the argument for stock in, it would just give you value for just the first time the item appeared, so we would be using the SUMIF entry to enable us to pull up all the times the item appeared and add them together so we can get the total amount sold. In column B4 enter,
=IFERROR(SUMIF(Sales!A:A,Report!B1,Sales!E:E),"whatever you want to appear").
Current Stock Remaining
To get the current stock remaining, all you need do is subtract the number of items sold from the total item bought, so enter in cell B5,
=IFERROR(B3-B4),“whatever you want to appear”).
Value of Remaining Stock
In our case, it refers to the total amount of stock remaining based on the cost price, to get this, you have to multiply the stock remaining by the cost price of the item. If you want to get the value of the item based on the selling price then multiply the stock remaining by the amount you are selling the item. We have to use VLOOKUP to search for the item and to find out the price then multiply by the current stock remaining in cell B5. So in cell B6 enter,
=IFERROR(B5*(VLOOKUP(B1,IN,5,FALSE)),whatever you want to appear”)
In conclusion, if you follow this tutorial to the letter, you should be able to build a very simple inventory management system for your business. In another article, I would be attempting to explain in detail how to properly use VLOOKUP, COUNTIF, SUMIF, etc. Let us know how it goes!
- You must be logged in to reply to this topic.