VBA tricks for forecasting operations in a nutshell

 

Hi All

 

Another day at work and I am back again with a magic VBA trick!! It reduces your operations burden of keeping track on your luck aka sales by individual customers. So here’s a video I’m presenting today that I’ve compiled for 120 customers. Yes, through this you’ll never miss track of a single customer. Whilst it’s easy to code and execute, the benefits we reap is hard to achieve in our daily business real times.

 

Firstly, here’s the code: (I generated it from Bing AI and also I’m the first person probably in the world to have been answering grind questions to students on Accounting discussion forums globally by using it lately. Feels pretty good!)

 Private Sub Worksheet_Change(ByVal Target As Range)

    Dim selectedCustomer As String

    Dim salesDataRange As Range

    Dim salesData As Range

    Dim i As Long

   

    ' Define the range containing customer names (Sheet1 B1:DQ1)

    Set salesDataRange = Worksheets("Sheet1").Range("B1:DQ1")

   

    ' Check if the change occurred in cell B1 (where the dropdown list is)

    If Target.Address = "$B$1" Then

        ' Get the selected customer name

        selectedCustomer = Target.Value

       

        ' Find the corresponding sales data for the selected customer

        For i = 1 To salesDataRange.Columns.Count

            If salesDataRange.Cells(1, i).Value = selectedCustomer Then

                ' Set the sales data range (Sheet1 B2:DQ13)

                Set salesData = salesDataRange.Offset(1, i - 1).Resize(13)

                Exit For

            End If

        Next i

       

        ' Populate the sales data in Sheet2 B2:B13

        Worksheets("Sheet2").Range("B2:B13").Value = salesData.Value

    End If

End Sub

 

Now that this is over, Ill talk you into steps:

 

Step 1: Download a forecasting template either from here: Perform Holt-Winters Exponential Smoothing in Excel (exceldemy.com) or from some where else from the World Wide Web. It’s even better if you create your own forecasting model using your own material. Since morning, I am lazy and didn’t want to construct my own model all over again. It took me 20 minutes to download and install this code and finally to make it work exactly like I wanted to as my apropos wish.

Step 2: Insert customer data like in Sheet 1. Remember to make sure both sheets columns are of same Cell numbers.

Step3: Get back into forecasting page, Sheet 2, insert a dropdown list through Data validation like the video mentions.

Step4:  Then Alt+F11 to open VBA editor and insert the code plus don’t forget to save it as a macro enabled worksheet.

Step 5: Test it by changing the names to different customers from the dropdown. You’ll get your forecast for all 120 customers separately in a nutshell!!

 This forecast will work as good as the formula can!

Hope you enjoyed this trick as well and have a nice day……

 

Excel workings