How to keep Inventory using an Excel Spreadsheet - [create your own Template]
Summary
TLDRThis tutorial walks viewers through creating a fully functional inventory control spreadsheet in Excel. It covers the creation of an inventory list and an inventory movement schedule, incorporating essential Excel functions like VLOOKUP, SUMIF, and IF statements to automate product tracking, quantity adjustments, and cost calculations. The video demonstrates setting up drop-down lists, formatting cells, and creating filters for easier data management. It concludes with guidance on how to receive the final template, offering a powerful tool for small businesses to manage inventory effectively and efficiently.
Takeaways
- đ Create an inventory list table in Excel with columns for product code, product description, cost, quantity, and value.
- đ Format headers of the inventory list with a green background, bold white text, and apply borders around the table.
- đ Set up a second table for inventory movements (sales, receipts, adjustments) next to the inventory list for easy tracking.
- đ Use data validation drop-down menus to allow the selection of transaction types (sale, receipt, adjustment) and product codes.
- đ Use VLOOKUP formulas to automatically fill in product descriptions and costs based on the selected product code.
- đ Add quantity and value columns to track inventory movements, ensuring sales are recorded as negative quantities.
- đ Use SUMIF formulas to calculate running totals for product quantities and inventory values based on inventory movements.
- đ Apply conditional formatting to hide zero values in the inventory movement table for a cleaner presentation.
- đ Test the inventory movement table by entering sample transactions to ensure that all formulas and drop-downs work correctly.
- đ Customize the spreadsheet to handle more data by using the methods demonstrated in the video to expand tables and rows.
Q & A
What is the primary purpose of the video?
-The video demonstrates how to create an inventory list and an inventory movement schedule using Excel, and provides an easy-to-use template for businesses.
How can I get a copy of the final template from the video?
-The video instructs viewers to subscribe to the channel and give the video a thumbs up to receive a copy of the spreadsheet file.
What is the first step in creating an inventory list in Excel?
-The first step is to open a new workbook, and in cell B2, type 'INVENTORY LIST' in capitals, followed by entering the column headings in row 3: 'Code', 'Product', 'Cost', 'Quantity', and 'Value'.
What is the purpose of using the 'Merge and Center' tool in the inventory list table?
-The 'Merge and Center' tool is used to merge the header cells and center the title and headings, improving the layout and design of the table.
What are the three types of inventory movements that can be recorded?
-The three types of inventory movements are 'Sale', 'Receipt', and 'Adjustment'.
How can a drop-down list be added to select inventory movement types in the schedule?
-A drop-down list can be added using the 'Data Validation' tool in Excel, where the source is defined as a named range containing the three types ('Sale', 'Receipt', 'Adjustment').
What Excel function is used to populate product details (name and cost) in the inventory movement table?
-The VLOOKUP function is used to automatically populate the product name and cost based on the code selected in the inventory movement table.
How can the formula errors be avoided when the code field is empty in the inventory movement table?
-The IF function is added to the VLOOKUP formula, which checks if the code field is empty and returns a blank value instead of an error.
What is the purpose of the SUMIF function in the inventory list?
-The SUMIF function is used to calculate the running or cumulative totals for each product, based on the movements recorded in the inventory movement schedule.
How can negative values in the inventory movements table (such as sales) be displayed correctly?
-Negative values can be displayed by entering a negative quantity in the quantity field for sales and formatting the cells to display negative numbers in red with a minus sign.
Outlines
Cette section est réservée aux utilisateurs payants. Améliorez votre compte pour accéder à cette section.
Améliorer maintenantMindmap
Cette section est réservée aux utilisateurs payants. Améliorez votre compte pour accéder à cette section.
Améliorer maintenantKeywords
Cette section est réservée aux utilisateurs payants. Améliorez votre compte pour accéder à cette section.
Améliorer maintenantHighlights
Cette section est réservée aux utilisateurs payants. Améliorez votre compte pour accéder à cette section.
Améliorer maintenantTranscripts
Cette section est réservée aux utilisateurs payants. Améliorez votre compte pour accéder à cette section.
Améliorer maintenantVoir Plus de Vidéos Connexes
Aprenda Como Funciona o CĂĄlculo de Custo MĂ©dio no TOTVS Protheus
Advanced Excel - Creating Pivot Tables in Excel
Arka Inventory Demo
Top 10 Most Important Excel Formulas - Made Easy!
TUTORIAL | CARA MENAMPILKAN KELOMPOK DATA TERTENTU | Rumus Excel
Inflow Inventory Tutorial For Beginners 2021 - How To Use InflowFor Inventory Management
5.0 / 5 (0 votes)