How to keep track of Small business Inventory with Excel?

What is the most important factor to a small business? Of course it is the reduction of actual cost. With small retail business which relies the most on Inventory control, they should know about How to keep track of Inventory.

How has your small business’s inventory management panned out? Have you had the right products available when you needed them? Did you lose out on business when items were out of stock? Or did you lose money due to excess stock?

For the huge number of online small business outside (who are really in need of increasing sales), read how to keep track of Inventory in this topic.

What is Inventory Management

According to Investopedia, Inventory management refers to the process of ordering, storing and using a company’s inventory: raw materials, components and finished products.

Somehow we will believe: Inventory management is the part of supply chain management that aims to always have the right products in the right quantity for sale, at the right time. When done effectively, business reduces the costs of carrying excess inventory while maximizing sales. Good inventory management can help you track your inventory in real time to streamline this process.

What does a small business need in an Inventory Management Software

  • Keep track of Inventory (especially in real time).
  • Make the flow run smoothly.
  • Doesn’t cost much.
  • Prevent product to be out of stock.
  • Connect with some POS system
  • Be a part of Omnichannel, online-to-offline system
  • Doesn’t conflict with other website modules. For example, you should use a integrated Inventory Management with other Magento 2 plugins if you are running a Magento 2 site.
  • Connect to other mobile apps such as Sales Order App.

How to keep track of Inventory for Small business with Excel

1. Keep track of Inventory with Organizing Based on Sales Quantity

Organize the data based on sales quantity in Excel allows you to create a flexible inventory sheet. It will update when order items due to the fact that you can see it on top. It also keeps your most sold items on the top for easier tracking, so you won’t need to scroll a lot of data to find. The easiest way that won’t result in carpal tunnel from hours of manual data entry is the sort function built into Excel. Or else, you can use the Rank function.

You should use the Sort function without merging two cells in one column and three cells in another. Also, you’ll have to re-sort each time you update your numbers. You typically want to use Ascending order if you want to rank sales from lowest to highest.

excel inventory management

Use SUM function with Sort to generate income reports by adding a value for the item. Whenever you update your sold items box, Excel automatically does the math for you to tell you how much inventory you’ve sold. You can further expand on this by adding sections for profit margins, taxes, and even smaller details like packing and shipping costs, but as you use the same SUM function here rather than advanced quantum mechanics, we won’t go into detail on the process. Excel uses * as the multiplication symbol. Your sum should look like this: = Cell*CellInventory Spreadsheet: Quantity, Unit Price, Amount

The Rank function allows you to take this process a step further by adding a specific rank to your items. It’s also more dynamic because it updates automatically as you adjust numbers. This means that you won’t have to manually rank your items each time you add a new sale, so you won’t forget to update your sorting and accidentally purchase all of the wrong items.

Inventory Spreadsheet template: reorder point

This formula is slightly more complicated, but still not rocket science (if it is, we want a raise). The formula goes like this: the first number is the cell you want to use, and the second numbers are the cells you want to compare your first cells with. So =Rank (Cell, Cell:Cell). This gives you a very clear numerical ranking for most sold items that automatically updates without you having to sort. This makes it easier to see, at a glance, which items have sold the most.


2. Use Google Spreadsheet in the Drive

One of the main limitations of the traditional Microsoft Excel program is that there isn’t really a feasible way for multiple people to add to the same document at once, unless you count backseat driving. While you can solve this with multiple Excel documents, today’s technology presents another solution – Google Drive. Google Spreadsheet allows for multiple users, real time updates, live backup, and of course, you can have several users editing the same document at once. A cloud inventory management system is especially useful if you have multiple people, multiple locations, or several computers for updating the inventory sheet. The most valuable function is, of course, the automatic updates.

3. Generating Inventory Reports

Inventory reports and analysis charts are relatively easy to generate, providing you have your inventory organized properly. You can choose to create reports on a daily, weekly, bi-weekly, or monthly basis, and create quarterly and yearly reports. The easiest way to do this is by setting up your basic parameters for your inventory and then dragging and dropping the sheet to a new Excel workbook. This automatically copies all of your work to the new one, which gives you a clean inventory page for each day, week, month, or however long you choose to keep records. You can also drag and drop all of your monthly records into one file to set up a yearly inventory, with individual sheets per month for easy organization. You can also generate charts using your data, but it’s up to you to decipher them at a later date. Then you’ve done keeping track of Inventory.

Keep update for more interesting about Inventory in Lamar’s Blog.