Inbound Goods & Inventory VBA Excel Template

Inventory Management Spreadsheet Template

This inventory management tool is designed to help you track stock levels, monitor reorder points, and maintain accurate records of your incoming and outgoing goods. Use this template to organize your supply chain data and ensure your operations remain efficient and well-stocked.

1. Inventory Master Data

2. Stock Inbound Log

3. Stock Outbound Log

4. Reconciliation and Adjustments

Pro Tips

• Automate Calculations: Use Excel formulas like =SUMIF to automatically calculate current stock levels based on your Inbound and Outbound logs.
• Conditional Formatting: Apply conditional formatting to the "Current Stock" column to highlight items that fall below your "Reorder Level" in red.
• Data Validation: Use the Data Validation feature to create dropdown lists for Categories and Supplier names to ensure data consistency.
• Regular Audits: Perform physical inventory counts at least once per month to reconcile your spreadsheet data with actual stock on hand.

Inventory Tracker: Spreadsheet Template

Copy this grid into Google Sheets or Excel and fill in the blank cells with your own figures. Subtotals, totals and calculated cells carry live spreadsheet formulas, so the sheet updates itself as you type.

VBA Code: Inventory Macros

VBA code for this template

1. Press Alt + F11 to open the Visual Basic Editor, go to Insert > Module, and paste the code below.
2. Return to Excel and run the macros via the Developer tab by clicking Macros and selecting the desired procedure.

Sub AddInventoryItem()
    Dim ws As Worksheet
    Dim lastRow As Long
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Prompt for values and add to the next available row
    ws.Cells(lastRow, 1).Value = InputBox("Enter Item ID")
    ws.Cells(lastRow, 2).Value = InputBox("Enter Product Name")
    ws.Cells(lastRow, 3).Value = InputBox("Enter Category")
    ws.Cells(lastRow, 4).Value = InputBox("Enter Unit Cost")
    ws.Cells(lastRow, 5).Value = InputBox("Enter Current Stock")
    ws.Cells(lastRow, 6).Value = InputBox("Enter Reorder Level")
    ws.Cells(lastRow, 7).Value = InputBox("Enter Supplier")
End Sub

Sub UpdateStock()
    Dim ws As Worksheet
    Dim id As String, change As Long
    Dim rng As Range
    Set ws = ActiveSheet
    
    id = InputBox("Enter Item ID to update")
    change = CLng(InputBox("Enter quantity change (use negative for reduction)"))
    
    ' Find ID in column A
    Set rng = ws.Columns("A").Find(id, LookIn:=xlValues, LookAt:=xlWhole)
    If Not rng Is Nothing Then
        ws.Cells(rng.Row, 5).Value = ws.Cells(rng.Row, 5).Value + change
    End If
End Sub

Sub HighlightLowStock()
    Dim ws As Worksheet
    Dim i As Long, lastRow As Long
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through rows and highlight if stock <= reorder level
    For i = 2 To lastRow
        If ws.Cells(i, 5).Value <= ws.Cells(i, 6).Value Then
            ws.Rows(i).Interior.ColorIndex = 6
        Else
            ws.Rows(i).Interior.ColorIndex = 0
        End If
    Next i
End Sub

If a macro does not run, check your Excel Macro Security settings in the Trust Center to ensure that macros are enabled.

FAQ

How often should I update this inventory spreadsheet?

You should update the spreadsheet in real-time or at the end of every business day to ensure your stock levels remain accurate and reliable for decision-making.

What should I do if my physical stock does not match the spreadsheet?

If a discrepancy is found, use the Reconciliation and Adjustments section to record the difference. Investigate the cause—such as shipping errors or theft—and update the count to reflect the true physical quantity.

Can I track multiple warehouse locations in this template?

Yes, you can add a "Location" column to the Master Data and log sheets to distinguish between different storage areas or retail branches.