Automating Lean production processes using VBA in Excel

Automating Lean production processes using VBA (Visual Basic for Applications) in Excel can help improve efficiency, productivity, and workflow management in a company. Lean is a methodology aimed at reducing waste, improving quality, and increasing value across the production process.

I’ll provide a detailed example of automating a Lean process using VBA in Excel. This process could include elements like stock management, resource optimization, or performance indicator tracking (KPIs).

Scenario: Stock Monitoring with Lean Indicators

The idea here is to create VBA code that tracks stock levels, orders, and calculates Lean metrics like stock rotation, production lead time, etc.

Key Elements:

1. Stock Monitoring: You have an Excel sheet with stock, order, and restocking information.
2. Lean Indicators: Calculating stock rotation, production lead time, and service levels.
3. Restocking Automation: Generate alerts or automatic orders based on stock thresholds.

Excel Sheet Structure

Let’s assume your Excel sheet is structured as follows:

• Sheet1 (Production Data): Contains information about stock levels, orders, restocking, and alerts.
  • Column A: Product ID
  • Column B: Product Name
  • Column C: Current Stock
  • Column D: Restocking Threshold
  • Column E: Quantity Ordered
  • Column F: Planned Restocking Date
  • Column G: Stock Rotation
  • Column H: Production Lead Time
  • Column I: Alert (if stock is below threshold)
  • Column J: Service Level

Example VBA Code: Stock Monitoring and Lean Alerts

Sub AutomateLeanProduction()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim currentStock As Long
    Dim restockThreshold As Long
    Dim orderQuantity As Long
    Dim restockDate As Date
    Dim stockRotation As Double
    Dim productionLeadTime As Double
    Dim alert As String
    Dim serviceLevel As Double
    ' Reference to the production data sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Find the last row with data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Loop through each row of data
    For i = 2 To lastRow       
        ' Retrieve data for each product
        currentStock = ws.Cells(i, 3).Value ' Column C: Current Stock
        restockThreshold = ws.Cells(i, 4).Value ' Column D: Restocking Threshold
        orderQuantity = ws.Cells(i, 5).Value ' Column E: Quantity Ordered
        restockDate = ws.Cells(i, 6).Value ' Column F: Planned Restocking Date
        stockRotation = ws.Cells(i, 7).Value ' Column G: Stock Rotation
        productionLeadTime = ws.Cells(i, 8).Value ' Column H: Production Lead Time       
        ' Calculate stock rotation (Example: rotation = annual sales / average stock)
        If currentStock > 0 Then
            stockRotation = orderQuantity / currentStock
        Else
            stockRotation = 0
        End If       
        ' Calculate production lead time (Example: lead time in days to receive order)
        If restockDate > Date Then
            productionLeadTime = DateDiff("d", Date, restockDate)
        Else
            productionLeadTime = 0 ' Restock has already arrived
        End If       
        ' Update values in the sheet
        ws.Cells(i, 7).Value = stockRotation ' Stock Rotation
        ws.Cells(i, 8).Value = productionLeadTime ' Production Lead Time       
        ' Check if an alert is necessary
        If currentStock <= restockThreshold Then
            alert = "Alert: Low stock, restocking needed!"
            ws.Cells(i, 9).Value = alert ' Column I: Alert
        Else
            ws.Cells(i, 9).Value = "" ' No alert
        End If       
        ' Calculate the service level (Example: service level based on order quantity vs. current stock)
        If currentStock > 0 Then
            serviceLevel = orderQuantity / currentStock
        Else
            serviceLevel = 0
        End If       
        ' Update service level
        ws.Cells(i, 10).Value = serviceLevel ' Column J: Service Level      
    Next i
    MsgBox "Lean production process automation completed!", vbInformation
End Sub

Explanation of the Code

1. Variable Initialization:
   • Variables are set up to store data for each product, such as current stock, restocking threshold, order quantity, and others.
2. Loop through Products:
   • A For loop is used to process each row of the Excel sheet starting from row 2 (with headers in row 1).
3. Calculating Lean Metrics:
   • Stock Rotation: The formula used to calculate stock rotation is orderQuantity / currentStock, showing how many times the stock has been sold over a given period.
   • Production Lead Time: This calculates the number of days remaining until the order is restocked using DateDiff.
   • Low Stock Alert: If the current stock is below the restocking threshold, an alert is triggered.
   • Service Level: The service level is calculated as orderQuantity / currentStock, indicating how well stock levels meet demand.
4. Updating the Excel Sheet:
   • The code updates columns for each product with calculated values like stock rotation, production lead time, alerts, and service level.
5. Completion Message:
   • Once the process is completed, a confirmation message box is displayed to the user.

Using This Code in Your Excel Workbook

1. Open Excel and press Alt + F11 to access the VBA editor.
2. Insert a new module by clicking Insert > Module.
3. Copy and paste the code into this module.
4. Go back to your Excel sheet, press Alt + F8, select AutomateLeanProduction, and click Run.

Customization

• You can adjust the formulas to calculate other Lean-specific metrics relevant to your business, such as waste reduction, throughput time, or any other key performance indicator.
• The code can also be modified to automatically generate restocking orders when the stock reaches a certain threshold by sending an email or generating a new Excel file with order details.