Many businesses are looking for methods simple, effective, economical way to manage inventory of yourself. Excel, with flexibility, ease of use, is one of the tools to be more small and medium business choice. Let's Accnet learn how effective inventory management in Excelfrom creating a basic spreadsheet to the tip automation right under article later!
1. How effective inventory management in excel
Here are detailed instructions on how effective inventory management in Excel with the step by step tool to help you easily make.
Step 1: Create table inventory management basic
Open Microsoft Excel and start with a blank workbook (Blank Workbook).
In Excel sheet, enter the column headings are as follows (starting from line 1):
- STT: Number order products.
- Product name: Name, details of the product/merchandise.
- Code: the unique identifier of each product (for example: SP001, SP002).
- Unit: the unit of measurement of products (for example: a, barrels, boxes, kg).
- Number of entry: the Number of goods to be warehoused.
- Number: the Number of goods is export warehouse.
- Number of inventory: the quantity Of goods is still in stock.
- Notes: additional information (for example: damaged goods are preserved).
For example:
| STT | Product name | Item code | Unit | Amount of income | Number manufacturer | Number of inventories | Notes |
| 1 | Product A | SP001 | The | 100 | 20 | 80 | New |
| 2 | Product B | SP002 | Box | 200 | 50 | 150 | Restaurant old |
DOWNLOAD FILE INVENTORY MANAGEMENT IN EXCEL SIMPLE - FOR FREE NOW!
Step 2: How effective inventory management in excel thanks to the automatic calculation of the number of inventory
You need to use the recipe except simple in Excel to automatically calculate the number of inventory .
- Suppose cell D2 is the column "Number entered", and E2 is the column "Number", enter the following formula into the column Number of inventories (cell F2): = D2 - E2
- Hover your mouse to the bottom right corner of cell F2 until the appearance plus sign (+).
- Pull down to the bottom to apply the formula for all the products in the list.
- Excel will automatically calculate the amount of inventory based on data import - export.
Step 3: Use the formula SUM to calculate the total number of
To calculate the total amount of import and export for all the items in the way of effective inventory management in excel, you need to use the formula SUM. Suppose you want to calculate the total amount of income from cell D2 to D100 and the total amount of export from cell E2 to E100.
- Select the empty cell below the column Number entered (for example: car D101).
- Enter the following formula: =SUM(D2:D100)
- Press Enter, Excel will calculate the total number of rows entered.
- Select the empty cell below a column Number (for example: car E101).
- Enter the following formula: =SUM(E2:E100)
- Press Enter to calculate the total amount of export.
Step 4: Use the Filter function (Filter) and sort (Sort) in Excel
Excel offers features to filter, sort helps you easily search/classification of goods in the warehouse.
- Select the entire data table (including the header).
- Input Data on the tool bar, then choose Filter. You will see the icon, small arrows appear in each column header.
- Click the little arrow in the header of the column you want to filter (for example: the column Number conservation).
- Select Number Filters (filter number) to the filter according to the quantity. For example, you can choose the "larger" or "Smaller" to find the product there are number of inventory exceeds a specific threshold.
- Click the arrow in the column header (for example: the column Number conservation).
- Select Sort A to Z to sort the data from low to high, or Sort Z to A to sort from high to low.
Step 5: How effective inventory management in excel from creating inventory reports, chart illustrations
Create a PivotTable (PivotTable is a powerful tool that helps you create a summary report from the data table):
- Select the entire data table.
- On Insert on the toolbar, then choose PivotTable.
- Select the location you want to place the PivotTable (in the same sheet or new sheet).
- In the window PivotTable, you can pull the market such as product Name, Number, email, Number, Number conservation to create aggregate reports.
After the table, PivotTable, you easily create a chart to visually track trends, import - export inventory in the way of effective inventory management in excel:
- Select the table PivotTable.
- On Insert, select Column Chart or Line Chart to create a column chart or line graph showing the number of import - export under each product.
Step 6: Create alerts amount of inventory low by Conditional Formatting
Select the entire column Number of inventories from cell F2 to the last cell. On the Home tab, select Conditional Formatting.
Select New Rule (new rule), then select Format cells that contain (format cells containing) and set condition:
- In the Cell Value, select "less than" (smaller), type the value the minimum threshold (e.g., 10).
- Choose the color to Excel automatically changes the color for the cells with numbers below 10 (for example: red).
Step 7: How effective inventory management in excel, thanks to store, share table/file inventory management
Press Ctrl + S to save the table inventory management with Excel format (.xlsx).
- You can save the file on your computer or upload to cloud services like Google Drive/Dropbox for easy access.
- You can send files via email or share through Google Drive to stakeholders can view, edit directly.
2. Benefits and drawbacks when the application how to manage inventory in excel
| Benefits of inventory management in Excel | Restrictions when inventory management in Excel |
|
|
3. Effective inventory management - quickly with warehouse management software lacviet
Software inventory management, Lac Viet is the ideal solution to help businesses track inventory quickly - exactly.
- The software allows you to update data import - export warehouse in real time. Any change would also be recorded immediately
- Support automatically generate the detailed report about the amount of inventory, goods import/export, help managers take the right decision without the need for calculations.
- Automatically send alerts when the number of inventory falls below the minimum, help businesses timely complementary goods, avoid the influence to the production process/business.
- With automation capabilities - integrated comprehensive management software helps reduce time inventory crafts, operating costs
Excel brings a simple solution for inventory management, in particular for individuals, small business and retail. Hope through this article, you now have deeper insights how effective inventory management in Excel to inventory management science, save time. Thank you for reading the spent time track, have you successfully apply this knowledge to the management of your inventory!
CONTACT INFORMATION:
- THE COMPANY SHARES INFORMATION, LAC VIET
- 🏢 Head office: 23 Nguyen Thi huynh, Ward 8, Phu Nhuan District, ho chi minh CITY.CITY
- ☎️ Hotline: 0901 555 063 | (+84.28) 3842 3333
- 📧 Email: info@lacviet.com.vn
- 🌐 Website: https://accnet.vn/
Theme: