Ms Excel Auto Sales Forecasting Report

Here is a detailed Excel macro code that accomplishes your task of automating the generation of a sales forecasting report based on the "2023 sales data": ### Steps in the Macro: 1. **Prompts user** to select the data range (which includes the current and previous year's sales columns). 2. **Forecasts the next year's sales** based on the current and previous year's sales data. 3. **Breaks down the forecast by monthly periods**, showing sales trends. 4. **Generates a formatted report** in a new worksheet with conditional formatting. 5. **Provides a summary** of total sales and forecasted growth for the selected period. 6. **Offers an option to save** the report as a PDF. ### Excel Macro Code: ```vba Sub SalesForecastReport() Dim wsData As Worksheet Dim wsReport As Worksheet Dim dataRange As Range Dim cell As Range Dim monthRange As Range Dim startRow As Long Dim lastRow As Long Dim colCurrentYear As Range Dim colPreviousYear As Range Dim colNextYear As Range Dim nextYearSales As Double Dim totalCurrentYearSales As Double Dim totalPreviousYearSales As Double Dim totalForecastSales As Double Dim growthRate As Double Dim savePath As String ' Prompt user to select the data range including headers On Error Resume Next Set dataRange = Application.InputBox("Please select the data range (including headers):", Type:=8) On Error GoTo 0 If dataRange Is Nothing Then Exit Sub ' Exit if no range selected ' Identify the worksheet containing the data Set wsData = dataRange.Worksheet ' Find the last row of data startRow = dataRange.Row + 1 ' Data starts from the row below the headers lastRow = dataRange.Rows.Count + startRow - 1 ' Define columns for current year, previous year, and forecast Set colCurrentYear = wsData.Range("B" & startRow & ":B" & lastRow) Set colPreviousYear = wsData.Range("C" & startRow & ":C" & lastRow) Set colNextYear = wsData.Range("D" & startRow & ":D" & lastRow) ' Add a new worksheet for the report Set wsReport = Worksheets.Add wsReport.Name = "Sales Forecast Report" ' Add report headers wsReport.Range("A1").Value = "Sales Forecast Report" wsReport.Range("A1:F1").Merge wsReport.Range("A1").Font.Bold = True wsReport.Range("A1").Font.Size = 14 wsReport.Range("A2").Value = "Month" wsReport.Range("B2").Value = "Previous Year Sales" wsReport.Range("C2").Value = "Current Year Sales" wsReport.Range("D2").Value = "Forecasted Next Year Sales" wsReport.Range("E2").Value = "Sales Growth (%)" ' Loop through each row of the data to calculate the next year's forecast and sales growth Dim i As Long For i = startRow To lastRow ' Copy month wsReport.Cells(i - startRow + 3, 1).Value = wsData.Cells(i, 1).Value ' Copy previous year and current year sales wsReport.Cells(i - startRow + 3, 2).Value = wsData.Cells(i, 3).Value wsReport.Cells(i - startRow + 3, 3).Value = wsData.Cells(i, 2).Value ' Forecast next year's sales (simple linear forecast based on previous and current year's sales) nextYearSales = wsData.Cells(i, 2).Value * (1 + ((wsData.Cells(i, 2).Value - wsData.Cells(i, 3).Value) / wsData.Cells(i, 3).Value)) wsReport.Cells(i - startRow + 3, 4).Value = nextYearSales ' Calculate sales growth percentage growthRate = ((wsData.Cells(i, 2).Value - wsData.Cells(i, 3).Value) / wsData.Cells(i, 3).Value) * 100 wsReport.Cells(i - startRow + 3, 5).Value = growthRate ' Apply conditional formatting (green for growth, red for decline) If growthRate >= 0 Then wsReport.Cells(i - startRow + 3, 5).Interior.Color = RGB(144, 238, 144) ' Green Else wsReport.Cells(i - startRow + 3, 5).Interior.Color = RGB(255, 99, 71) ' Red End If Next i ' Calculate totals and summary totalCurrentYearSales = WorksheetFunction.Sum(colCurrentYear) totalPreviousYearSales = WorksheetFunction.Sum(colPreviousYear) totalForecastSales = WorksheetFunction.Sum(wsReport.Range("D3:D" & lastRow - startRow + 2)) ' Display total sales and growth summary wsReport.Cells(lastRow - startRow + 4, 1).Value = "Total Sales" wsReport.Cells(lastRow - startRow + 4, 2).Value = totalPreviousYearSales wsReport.Cells(lastRow - startRow + 4, 3).Value = totalCurrentYearSales wsReport.Cells(lastRow - startRow + 4, 4).Value = totalForecastSales wsReport.Cells(lastRow - startRow + 5, 1).Value = "Overall Sales Growth" wsReport.Cells(lastRow - startRow + 5, 5).Value = (totalCurrentYearSales - totalPreviousYearSales) / totalPreviousYearSales * 100 If wsReport.Cells(lastRow - startRow + 5, 5).Value >= 0 Then wsReport.Cells(lastRow - startRow + 5, 5).Interior.Color = RGB(144, 238, 144) ' Green Else wsReport.Cells(lastRow - startRow + 5, 5).Interior.Color = RGB(255, 99, 71) ' Red End If ' Formatting the report wsReport.Range("A2:E2").Font.Bold = True wsReport.Range("A2:E2").Interior.Color = RGB(169, 169, 169) ' Gray background for header wsReport.Columns("A:E").AutoFit ' Prompt to save the report as PDF savePath = Application.GetSaveAsFilename(FileFilter:="PDF Files (*.pdf), *.pdf", Title:="Save Sales Forecast Report") If savePath <> "False" Then wsReport.ExportAsFixedFormat Type:=xlTypePDF, Filename:=savePath MsgBox "Sales Forecast Report saved as PDF: " & savePath, vbInformation End If End Sub ``` ### Explanation: - **Data Selection**: The macro prompts the user to select a data range that includes the current year's sales (Column B) and the previous year's sales (Column C). - **Forecast Calculation**: It calculates the next year's forecast based on simple growth from the current and previous year. - **Sales Growth**: The growth percentage is calculated and highlighted using conditional formatting (green for growth, red for decline). - **Summary**: It adds a summary of total sales and overall growth at the end of the report. - **Save as PDF**: The macro offers the option to save the report as a PDF file. ### How to Use: 1. Open Excel and press `Alt + F11` to open the VBA editor. 2. Insert a new module by clicking `Insert > Module`. 3. Paste the code into the module. 4. Close the editor and run the macro by pressing `Alt + F8`, selecting `SalesForecastReport`, and pressing `Run`. This macro will generate a forecast report based on the selected data and allow you to save it as a PDF.