How to create your own Inventory Management System
By Shaila Humphrey and Asma Pervez
Why we built this product:
This product came from a consulting project done in our Entrepreneurship class previously. The business it was designed for is a giftware and liquor store in a small town. The business is mostly based on the need to have many products available for the surrounding community. The business in mind, currently has no product scanning ability and no current inventory management system. They currently count beer and liquor inventory by hand and do not count their other inventory. By integrating a simple phone app the company does not have to spend a lot of money on a scanning software nor do they have to change their current till set up. This integration would be very beneficial for other small companies or businesses. It allows the business to easily scan and track their inventory sold as well as instock inventory. Profit made and gross profit can be easily customized based on what the business owner would like to see. The company is currently working to integrate this product into their already established store. Ideally this product would be set up when the business/store is first opening so inventory does not have to be tracked after the store is stocked (taking an inventory count of a stocked store can take a fair amount of time). Once integrated this product does not have to be updated but the spreadsheets can be easily changed based on the businesses changing personal needs.
What we built:
A connection between the Netkiller Barcode scanner app and a google spreadsheet to manage inventory. The inventory system made allows small business to add inventory or subtract inventory and keep track of it in an affordable and easy way. We also connected this with mailchimp using Zapier. This allows user to get a notification when inventory is low.
Functionality of what we built:
This inventory management system can be extremely helpful to small businesses because it helps them keep track of their inventory online whereas they might be doing their inventory manually with notebooks, etc. An important thing for shops to do is to keep track of how much inventory you currently have so that you can order more before you run out. This is really hard to do with a manual inventory counting system because sometimes you get tired or miss counting a certain item and then you may run out of inventory before your next shipment comes in. With this system, you can use a scanning app on your phone that sends your inventory of items to a google spreadsheet that contain certain rows such as barcode, product name, current inventory, etc which you can change to your personal preferences. This inventory management system we created is very helpful to small businesses but you do have to pay a fee to use the barcode scanning app for more than 5 items. I would recommend testing out the free version for up to 5 items first to make sure that the app works well for you, then you can upgrade to Netkiller Barcode Scanner (NBS) Pro. With NBS Pro, for $6–12 a month, you can have up to 10,000 items in your inventory, can scan items an unlimited amount of times, have access to 24/7 customer email/ phone/ chat support and a service level agreement with a 99.9% uptime guarantee.
If you want to receive emails as your inventory levels reach a certain amount, you can add a mailchimp connection to your google spreadsheet so that it keeps track of your inventory levels and notifies you when you are running low.
The set-up is pretty easy and we will show the steps on how to complete set-up for your own company below.
Set-up:
Step one: Set Up NetKiller on businesses iPhone.
This will be the scanner for the business so it needs to be on a device that can stay at the business and be close to the till at all times.
The first thing that needs to be done is the company will have to go to https://www.bc-scanner.netkiller.com and download the application as well as connect it to a google drive. If the company does not already have a gmail account they will have to make one before proceeding with the creation of this product. This can be easily done by going to google, gmail, and creating an account. The NetKiller app will automatically connect to a google sheets page which will have to be connected to a gmail account. The scanner (pic2shop) application can be downloaded to a Iphone, Ipod, or android device through the website previously mentioned.
Step two: Design a spreadsheet for the specific company
This is the inventory spreadsheet that the company will be able to track their incoming and outgoing products with.
The first thing you have to do is go to https://docs.google.com/spreadsheets/u/0/ to create a spreadsheet.
As you can see from the image below we have set up a spreadsheet based on the needs that the consulting project company was looking to satisfy. The barcode for each product is in a column beside the product name for easy reference in the case of manual entry. These column can be easily customized based on the clients needs. The spreadsheet should be made in google sheets for easy integration in future steps. The starting monthly or yearly can be added manually or update whenever needed. The inventory columns (added, sold, and current) will be linked in future steps. The cost to buy and sell the product is a manual entry and can be changed based on products price changes. Finally the total gross profit formula used was =(E4*H4)-(G4*(C4+D4)) and the formula for the profit from sold inventory is =(E4*H4)-(E4*G4). Below is a screenshot of the current spreadsheet we designed for this small business. A different sheet (using the same layout) can be set up in the same google sheets document for each of the inventory categories. Below is a screenshot of the overall page we set up in google sheets.
Step three: connect the inventory spreadsheet to the NetKiller spreadsheet
NetKiller will automatically send information into a ‘history’ sheet within a google sheets. This google sheet page should be connected to the inventory spreadsheet to insure that a cumulative inventory total is kept.
The first thing that needs to be done is to connect the previously made spreadsheet (made in step two) to the NetKiller ‘history’ sheet which will be automatically added when the application is integrated. In order to get the inventory numbers to move from the history sheet (where they are initially scanned into the system) to decrease the current inventory you will have to use a ‘=sumif’ function in google sheets. In the column where you want the inventory to decrease as an item is sold or increase as an item is stocked you will use this formula =SUMIFS(History!B3:B109,History!A3:A109,”barcode”). For our purpose this formal is placed in every inventory category for under the current inventory column. The orange meaning the where the inventory is scanned, called number count in the history sheet, (where the +/- shows up), the purple being where the ‘if’ is going to show up and the green being the specific if you are looking for, the barcode for the specific product. This history sheet is a cumulative look at everything that has been sold since setting up the spreadsheet. Do not delete this sheet from your google sheets. If you have made a mistake and said you sold 1 item when you really sold 2 you can manually change this in the number count column. Below are two screenshots of the google sheets page. The first being the screenshot of the history page, as well as where the inventory is added as subtracted. The second is the individual inventory category where overall inventory management is looked at.
Step four: Start scanning inventory
Begin using the product
Once you have your initial inventory listed you can start scanning your inventory as it sells. If you have incoming inventory you can scan this is in as well (as a negative) or you can manually add this inventory into your google sheets and it will update the inventory. When you scan the inventory you have an option to have multiples of the inventory. For example, if you are selling 2 items you can change the +/- to 2 or if you have 6 items coming in then you can add that to -6. Data can be added manually through the application as well by going to the keyboard and entering the barcode manually. Below is the scanner screen as well as the manual barcode entry screen.
Step five: Connect google sheets to mailchimp
Low inventory will trigger a notification email to be sent to the company email notifying them to order more.
In Zapier we linked Mailchimp to our google sheets inventory sheet. We set the trigger column in the google sheet to be the current inventory level as that is what is important to know before ordering more inventory. When the current inventory column reached a set level (we set it at 4) Mailchimp would send out an email notifying the company about the low inventory level. This could further be enhanced by having a draft email be saved for the supplier as well to make the ordering easier. Below is the integration we used as well as the email that was set from Mailchimp when the inventory level hit the set level.
Step six: Visualization of the inventory
Knowing what you have sold is a big factor in a company’s success. We have a sheet set up for each different category of inventory that the store has. The data in the inventory sold column can be combined to show what parts of the business are seeing success.
As you can see in the figures below, we created a sheet called “Sold Inventory” that is connected to the other sheets in our document. We included a list of the 5 types of inventory we have and added two columns: One for total amount of inventory sold and one for total profit. We connected these to each of the 5 inventory sheets to bring up all the totals on one sheet. WIth these totals we selected the 5 inventory type cells (Cells A2 to A6) and the total sold cells (B2 to B6) to create a pie chart for “Total Items Sold”. We also created a second pie chart to show the total for profit on each of the inventory items. This was created by selecting cells A2 to A6 and cells C2 to C6. You can select these at the same time (without selected row B) by first selecting cells A2 to A6 and then holding command or ctrl (Depending on whether you have a mac or PC) and then selecting the other four cells while still holding the button. You then should go to the Insert tab and select “Chart”. Once a chart is created you can go into the options to customize it. Click on the chart and a sidebar should appear that gives you the option to change your chart type (we changed it to a pie chart) and legend (we added a legend in the position “labelled”. This pie chart is helpful to your company because you can see the totals in percentages and compared to other items. We need the profit chart as well because although you may sell a lot of items, that item may not be worth a lot so you might lose money in the end rather than gain profit.
