Automating demand planning in Excel using VBA

Automating demand planning in Excel using VBA can streamline tasks such as analyzing past demand, forecasting future needs, and managing stock based on historical data. The goal is to optimize resources and ensure that products or services are available according to demand forecasts.

In this example, I’ll guide you through a process that includes the following steps:

1. Collecting historical demand data.
2. Calculating demand forecasts.
3. Analyzing the gap between forecasted demand and available stock.
4. Automating the update of forecasts in a table.

Step 1: Organize Your Data in Excel

Ensure that your historical demand data is organized in a worksheet. For example, here’s a simple layout:

In this example, the « Date » column represents the date of the demand, and the « Actual Demand » column represents the quantity demanded for each day.

Step 2: The VBA Code to Automate Demand Planning

Here’s the detailed VBA code that automates this process:

Sub AutomateDemandPlanning()
    Dim wsData As Worksheet
    Dim wsForecast As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim currentDate As Date
    Dim actualDemand As Double
    Dim forecastDemand As Double
    Dim avgDemand As Double
    Dim demandGap As Double
    Dim availableStock As Double
    Dim reorderThreshold As Double
    Dim planningColumn As Range   
    ' Define the worksheets
    Set wsData = ThisWorkbook.Sheets("DemandHistory") ' Historical demand data
    Set wsForecast = ThisWorkbook.Sheets("DemandForecast") ' Forecast data   
    ' Find the last row of data in DemandHistory
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row   
    ' Initialize the planning column in the Forecast sheet
    Set planningColumn = wsForecast.Range("B2:B" & lastRow)   
    ' Calculate the average demand for forecasting
    Dim totalDemand As Double
    totalDemand = 0
    For i = 2 To lastRow
        totalDemand = totalDemand + wsData.Cells(i, 2).Value
    Next i
    avgDemand = totalDemand / (lastRow - 1)   
    ' Define the reorder threshold (based on stock policy)
    reorderThreshold = 200 ' Value to adjust based on your business   
    ' Fill in the forecast data into the "DemandForecast" sheet
    For i = 2 To lastRow
        ' Get the date and actual demand
        currentDate = wsData.Cells(i, 1).Value
        actualDemand = wsData.Cells(i, 2).Value       
        ' Calculate the forecast demand (using the average for simplicity)
        forecastDemand = avgDemand       
        ' Calculate the gap between actual and forecast demand
        demandGap = actualDemand - forecastDemand       
        ' Get the available stock (assuming this is in column C of the Forecast sheet)
        availableStock = wsForecast.Cells(i, 3).Value       
        ' Add forecast, demand gap, and stock status to the Forecast sheet
        wsForecast.Cells(i, 1).Value = currentDate ' Date
        wsForecast.Cells(i, 2).Value = forecastDemand ' Forecasted demand
        wsForecast.Cells(i, 3).Value = availableStock ' Available stock
        wsForecast.Cells(i, 4).Value = demandGap ' Gap between actual and forecast demand       
        ' Check if stock is insufficient and flag for reorder
        If availableStock < reorderThreshold Then
            wsForecast.Cells(i, 5).Value = "Reorder Required"
        Else
            wsForecast.Cells(i, 5).Value = "Sufficient"
        End If
    Next i   
    MsgBox "Demand planning automated successfully!", vbInformation
End Sub

Detailed Explanation of the Code:

1. Define Variables and Worksheets:
   • wsData: The worksheet containing historical demand data.
   • wsForecast: The worksheet where forecasted demand will be stored.
   • lastRow: Identifies the last row of data in the « DemandHistory » sheet.
2. Calculate the Average Historical Demand:
   • This average serves as the basis for the demand forecast.
3. Fill the Forecast Sheet:
   • The code populates the « DemandForecast » sheet with the following information:
     • Date: The date of the demand.
     • Forecasted Demand: The forecast value (based on the average).
     • Actual Demand: The actual historical demand.
     • Demand Gap: The difference between actual and forecasted demand.
     • Available Stock: The stock on hand as of the forecast date (assumed to be in the forecast sheet).
     • Reorder Required: Indicates whether stock is below a reorder threshold.
4. Reorder Alert:
   • The code checks if the available stock is below the reorder threshold and flags it as « Reorder Required » if necessary.

Step 3: Using the Code in Excel

1. Open Excel and create two sheets:
   • DemandHistory: Contains the historical demand data.
   • DemandForecast: Will contain the forecast results, including planning and reorder alerts.
2. Open the VBA editor (Alt + F11), create a new module, and paste the code into it.
3. To run the macro, go to Tools > Macro > Macros, select AutomateDemandPlanning, and click Run.

Customization of the Code

• Forecasting Method: This code uses a simple average of past demand for forecasting. You can replace it with more advanced techniques such as exponential smoothing, regression analysis, or more detailed forecasting models.
• Reorder Threshold: Modify the reorder threshold value based on your specific business requirements.

This process will automate a part of the demand planning work, but you can complement it with more in-depth analysis and inventory management tools to optimize your forecasts.