How do you manage stock using Excel? (2024)

In logistics and accounting, many tasks require the use of special software, which usually has to be purchased. However, with its many features, Excel can also be used in many situations. With a solid management model, you can efficiently manage your stock in Excel.

Scallog explains how to set up a stock management table in Excel, and when you need to do this.

What is stock management?

Stock management is about having a continuous vision of the quantity of available products in your business cycle.

By “available products” we mean both finished goods intended for sale as-is and materials and elements used in a manufacturing process.

Stock management is a key concept, for the optimization of its reserves has a direct impact on the financial health of an enterprise. Excess stockis synonymous with “sleeping money”, i.e. cash flow that is unnecessarily tied up when it could be turned to account otherwise.

Then again, not holding a minimum level of stock inevitably means lost sales, and therefore lost revenue.

• Has to be inventorized as accurately as possible;
• Must have an acceptable turnover ratio.

Good stock control also allows you to trace products by closely monitoring restocking and deliveries.

Why use Excel for stock management?

There are, of course, manylogisticsmanagement solutions for optimized stock management (i.e. WMS). However, these solutions are designed for use in conjunction with an ERP. They can be expensive, cumbersome and complicated to use properly.

Excel: an easy-to-use solution for managing your stock

Microsoft Excel is a very interesting alternative to dedicated stock management software. First off because most enterprises already have it on their systems, so no additional expense is required.

Excel is a versatile management solution, too. It can be used both for accounting and stock management: or for keeping files on customers or suppliers, for example.

Also, most employees will have a command of the basic Excel functionalities. This greatly reduces training requirements and potential errors in the use of this program.

Advanced capabilities for stock management

While Excel is most often used for its basic features such as listing, adding/multiplying and revenue reporting, the possibilities it offers are actually much greater.

An Excel stock management file ensures accurate tracking of product inventory. For example:

• The SUM function allows you to automatically calculate yourstock managementmetrics based on an initial inventory and its movements.
• Calculation functions allow you to add or subtract columns to obtain a stock balance, view changes, or calculate the total value of reserve stocks.
• The SORT function allows you to classify products according to a selected criterion (EAN code, alphabetical order, increasing price etc.) for clearer presentation of the content of the file.
• The CONDITIONAL FORMATTING function enables you to highlight selected data. For example, fields with too high or too low stock levels can be highlighted in red. This action then triggers operations based on e.g. a destocking promotion or an order.
• Charts show trends in various metrics.

Useful tip: Microsoft Excel is not just about manual data input.

It can also work with a barcode reader, which takes the place of the keypad (type EAN code and Enter). This makes data entry faster, and also significantly limits the risk of error.

Excel spreadsheets and stock management

Why use a pre-existing Excel template for stock management?

Implementing an optimized stock management template in Excel requires not only time, but also considerable expertise in the functionality of this program. So going it alone is risky.

An undetected error in a calculation formula can severely disrupt the management process by distorting the outcome. This impact on the company’s finances can be significant.

Find the Excel template that's right for you

A number of templates exist for the safe and simple implementation of a stock management solution in Excel (free or not). There are templates for all needs, from e-commerce to beverage distribution.

You can download reliable, tested and approved Excel templates from the web. For example:

Zervant

The British invoicing solution experts Zervant offer free stock management and inventory templates. These include free and easy-to-use Excel stock management spreadsheet templates that can be downloaded directly from their blog. You’ll also find a stock management template with clear instructions on how to get the best out of it.

Vertex42

Vertex42, a leading online spreadsheet template provider, offers an Excel stock management template. This Excel stock template allows you to update and manage your stock, and keep track of supplier information. This Excel-only template is available for a purchase price of $9.95.

TradeGecko

TradeGecko offers an excellent stock management spreadsheet. It is free to use and its creators offer numerous tips for optimizing stock management using the Exceltemplate. This template allows you to track products, purchases, sales, and view all data in a summary report presented directly in the workbook.

Excel Skills

Excel Skills provides over 40 Excel file templates for business management, three of which are specifically for stock management and inventory. These templates are high quality and come with training resources integrated directly into the document. The stock management template is available for purchase for 20 euros.

Spreadsheet123

Spreadsheet123 offers a free stock management template for food companies. With different settings for currency, text and units of measurement, it’s suitable for use in many countries.
Spreadsheet123 also offers a template dedicated to beverages. Available for $39.95,it lets you track sales volumes of selected products to calculate profits more accurately.

Excel Champs

Excel Champsoffers an Excel file template with a beautifully designed embedded dashboard. This template is available in free (Lite) and paid (pro) versions. Both versions offer an inventory of product arrivals and departures, but the pro version also includes invoice creation, returns management, and supplier reporting functions.

Stock management in Excel is widely possible and does not require in-depth knowledge of the software. So by selecting a pre-established template, you have the assurance that you’re using a proven stock control solution.

You can then add to your set of templates with templates designed for other applications, as required.