Automate the demand forecasting process in Excel using VBA

Automating the demand forecasting process in Excel using VBA can be a complex project, but here’s a detailed example to get you started. We’ll create a simple demand forecasting model using VBA to automate data import, model creation, and displaying results.

Main Steps:

1. Import Historical Sales Data into Excel.
2. Prepare Data for modeling (cleaning and structuring the data).
3. Create a simple forecasting model (e.g., moving average or linear regression).
4. Display forecasts in a table or graph.

Below is an example of automating a demand forecasting process using a moving average model. We’ll use VBA to automate these steps.

VBA Code Example for Demand Forecasting

1. Create a macro to import data

Let’s assume you have a CSV file with historical sales data. Here is the VBA code to import that data into an Excel worksheet.

Sub ImportSalesData()
    Dim ws As Worksheet
    Dim filePath As String
    Dim lastRow As Long   
    ' Set the target worksheet where data will be imported
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Prompt the user to select a CSV file
    filePath = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , "Select a CSV file")   
    If filePath = "False" Then Exit Sub ' Exit if the user cancels   
    ' Import the CSV file into the active worksheet
    With ws.QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=ws.Range("A1"))
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With  
    ' Find the last row of the imported data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    MsgBox "Data imported up to row " & lastRow
End Sub

2. Create a macro to calculate demand forecasts

Let’s assume your sales data is in column A, and you want to calculate the forecast using a 3-period moving average. Here is the code to calculate that:

Sub CalculateDemandForecasts()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim period As Integer
    Dim i As Long
    Dim sum As Double
    Dim forecast As Double   
    ' Set the target worksheet where data is located
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Set the period for the moving average (here 3 months)
    period = 3   
    ' Add a header for the forecasted demand
    ws.Cells(1, 2).Value = "Demand Forecast"   
    ' Calculate the moving average for the last 3 months
    For i = period To lastRow
        sum = 0
        ' Sum the last 3 months
        For j = 0 To period - 1
            sum = sum + ws.Cells(i - j, 1).Value
        Next j
        ' Calculate the moving average
        forecast = sum / period
        ' Display the forecast in column B
        ws.Cells(i, 2).Value = forecast
    Next i   
    MsgBox "Forecast calculation completed!"
End Sub

3. Create a macro to plot a chart

After calculating the forecasts in column B, you can add a chart to visualize the actual demand vs the forecasted demand. Here is a code to create a simple line chart:

Sub CreateForecastChart()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim chartObj As ChartObject   
    ' Set the target worksheet where data is located
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Create a chart from the sales and forecast data
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
    With chartObj.Chart
        .SetSourceData Source:=ws.Range("A1:B" & lastRow)
        .ChartType = xlLine ' Line chart type
        .HasTitle = True
        .ChartTitle.Text = "Sales and Demand Forecast"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Period"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Quantity"
    End With   
    MsgBox "Chart created successfully!"
End Sub

Explanation of the Code:

1. ImportSalesData: This macro imports sales data from a CSV file into Excel. It prompts the user to select the file and then imports it into the active worksheet.
2. CalculateDemandForecasts: This macro calculates the demand forecast using a 3-period moving average. It sums the sales of the last 3 periods and then calculates the average to generate the forecast. The forecasts are placed in column B.
3. CreateForecastChart: This macro creates a line chart comparing actual sales data (from column A) and the demand forecast (from column B). The chart is created on the same worksheet, and it provides a visual representation of the sales and forecast trends.

Customization:

• You can adjust the period for the moving average by changing the period variable in the CalculateDemandForecasts macro.
• If you want to use a more sophisticated forecasting model (e.g., linear regression or ARIMA), you would need to integrate additional functions in VBA or call external models via an R or Python library.

Conclusion:

This example demonstrates how to automate a basic demand forecasting process using VBA in Excel. You can adapt the model to suit your needs by adjusting the forecasting method, period, or adding more advanced statistical models to improve the accuracy of your fo