STOCKSON

inventory management, How to do it in Excel

When you first start managing inventory, Excel is an easy way to access it. Even without learning a separate program, you can get started right away by organizing your product list and quantity in a table.

If the number of products is not large, Excel can be sufficient to manage them. However, as the number of products increases or the number of receiving and shipping records increases, handling tables and files can become increasingly complicated.

In this article, we will summarize the basic structure of starting inventory management in Excel—product list, receiving and shipping records, and current inventory calculation.

Why start inventory management with Excel?

There is a practical reason why many business owners start their inventory management with Excel.

  • You can start right away without any additional programs.
  • Most of these are tools you are already familiar with.
  • You can manually add or replace the items you need.
  • Small businesses have low initial costs.

Rather than introducing a large system from the beginning, it is a natural choice to start by building up the necessary records in Excel.

Basic structure of inventory management Excel

The starting point for Excel inventory management is viewing product lists and current quantities in one place. Below is a simple example.

  • Product A (A001)

    Category
    daily necessities
    Current inventory
    25 units

  • Product B (A002)

    Category
    food
    Current inventory
    18 units

  • Product C (A003)

    Category
    phrase
    Current inventory
    42 units
  • Product code: A unique value that identifies the same product.
  • Product name: This is the name used in actual work.
  • Categories: Group products together to make them easier to find.
  • Current stock: This is the quantity currently remaining.
  • Unit: This is the standard for counting, such as units or boxes.

The key is to manage your product lists and inventory quantities on a single basis. When the votes are scattered, it becomes difficult to trust the current quantities.

What to check in an Excel template

When choosing or building a template, check these before worrying about formatting.

  • Product codes and names to identify items
  • A place to record receive and issue by date
  • On-hand stock linked to those movements
  • Space for a short memo or reason
  • One clear master sheet so files do not multiply

How to record receipts and releases

If you only write down the current inventory, it is difficult to check later why the number was that number. Keeping separate records of receipts and shipments makes it easier to track reasons for changes.

  • 9/1 · Product A

    Category
    receiving
    quantity
    +30
    memo
    New in stock

  • 9/3 · Product A

    Category
    Delivery
    quantity
    -5
    memo
    sale

  • 9/5 · Product A

    Category
    Delivery
    quantity
    -3
    memo
    sale

basic principles

Current stock = Previous stock + Goods received − Goods shipped ± Stock adjustments

Inventory adjustment is used when quantities need to be adjusted in addition to stocking and shipping. For example, if there is a difference when counting the actual quantity, it is time to correct damage, loss, or input errors.

If you leave the date, product, quantity, and reason for adjustment at least briefly, it is easy to find the reason why the number changed later.

Calculate current inventory in Excel

By accumulating receipt and shipment records, you can calculate current inventory. Complex functions are not the goal; it is important to understand the relationships between records and quantities.

basic calculations

Current inventory = Beginning inventory + Quantity received − Quantity shipped

When adding up receipts and shipments for each product, you can use a conditional sum like SUMIF. Below is a simple example to help you understand.

SUMIF example (concept)

Total receipt = SUMIF(Product column, "Product A", Quantity column)
Total of shipments = SUMIF(Product column, "Product A", Quantity column)
Current inventory = Beginning inventory + Total receipts - Total shipments

The formula may vary depending on the sheet structure and classification (receiving and shipping) notation method. The important thing is that you can recalculate the current quantity by leaving an inventory movement note.

When it is best to manage with Excel

If the management scale is small, as shown below, Excel may be sufficient.

  • There are few products
  • There is not much entry and exit.
  • There is one person in charge
  • Manage only one location
  • No need for complicated permission management
  • Simply check current inventory

Under these conditions, there is no need to introduce a program. Consistently accurate records are more important than the tools.

The moment when Excel inventory management becomes inconvenient

Conversely, if the following changes continue, it may become difficult to survive with Excel alone.

  • Product types continue to increase
  • Increasing receiving and shipping records
  • Multiple people edit files
  • It is difficult to immediately check current inventory
  • Finding past entry and exit records is cumbersome.
  • Data is divided into multiple files
  • Inventory quantity and actual quantity often differ

If management becomes this complicated, you can consider whether to continue using Excel or switch to an inventory management program. To further compare the differences between Excel and the program Inventory Management Excel vs Program Please refer to the guide.

What should you do next?

That covers Excel inventory basics. Your next step depends on scale and friction.

If you need an inventory program, STOCKSON is currently free to use.

Should you keep managing in Excel?

Compare when Excel is enough and when a program is a better fit.

Compare Excel vs program

If you want to manage with a program

See STOCKSON: register products, receive, issue, then check on-hand stock.

Explore the inventory management program

What to check when changing from Excel to an inventory management program

When choosing a program, it is better to check whether it fits the tasks you use every day rather than a list of flashy features.

  • Product registration — Organize product names, codes, and categories and make sure they are easy to edit.
  • Receiving records — Check whether the method of leaving a receipt is simple.
  • Shipping records — Make sure you can immediately record the quantity sold, used, etc.
  • Check current stock — You should be able to quickly find the remaining quantities.
  • Inventory change history — Make sure you can see back what changes occurred and when.
  • User/Employee Permissions — If you use it with employees, it is important to be able to divide authority according to role.
  • Migrate existing data — Check if you can move the product from Excel. STOCKSON can import products as CSV.

For a fuller Excel vs program comparison, continue to Excel vs program comparison .

Inventory management required for small businesses

Small businesses do not need complex systems from the start.

Although it is cumbersome to manage with Excel, there are many cases where a complex ERP including accounting, human resources, and purchasing is not absolutely necessary.

Between complex ERP and inconvenient Excel.Manage inventory just as needed.

Being able to register products, leave receipts and shipments, and check current inventory and history can make inventory operations for a small business much simpler.

GET STARTED WITH STOCKSON

If you want to change from Excel to an inventory management program, STOCKSON can also be an option.

STOCKSON is an inventory management service that allows you to manage products, receipt, shipment, inventory adjustment, current inventory, inventory change history, sales and shipment statistics, employees, and authorities on the web. Existing Excel products can be imported as CSV.

  • product management
  • receiving
  • Delivery
  • inventory adjustment
  • current stock
  • Inventory change history
  • Sales/delivery statistics
  • Employee and permission management
  • CSV product registration

For more detailed product introduction, Inventory management program You can check it out on the page.

STOCKSON Free Plan

You can start with STOCKSON with the Free plan.

Frequently Asked Questions

Is it okay to manage inventory using Excel?

Yes. If you are a small business with not many products and incoming and outgoing items, Excel may be sufficient. If there are no major inconveniences with the current method, you can use it as is.

What items are needed in inventory management Excel?

Basically, it is easy to manage if you have receiving and shipping records along with product code, product name, category, and current quantity. You can add units and notes as needed.

How do I calculate current inventory in Excel?

Add receipts and subtract shipments from the starting inventory, then reflect inventory adjustments if necessary. By accumulating receipts and shipments, you can recalculate the current quantity.

When should you change Excel inventory management to a program?

You can review it when file management becomes complicated as the number of products, shipments, and users increases, or when it is difficult to immediately check current inventory and history.

Can I transfer my existing Excel product to STOCKSON?

Yes. STOCKSON supports CSV product import. You can save and register the product list organized in Excel as CSV.

Start with Excel and take the next steps as needed

Starting inventory management with Excel is a reasonable choice. If your records are good and small, you can keep them as is.

If management becomes cumbersome, consider a dedicated inventory management program. STOCKSON has made it simple for small businesses to use the inventory management they need on the web.

STOCKSON START FREEInventory management program