Sales Data Summary Reports

Creating sales reports—just thinking about it gives you a headache, right? Organizing hundreds or thousands of transaction data, aggregating by region and product, calculating month-over-month growth rates... Doing it all manually takes an entire day.

With AI, you can automate this entire process. Just describe the report format you want, and AI creates all the necessary formulas. It tells you how to set up pivot tables and even suggests charts.

In this post, we'll learn step by step how to analyze and summarize actual sales data with AI. We'll see how to automatically extract key metrics needed for executive reporting.

1. Original Data Structure

Sample Sales Data

Column A: Date (2025-01-01)
Column B: Region (Seoul, Busan, Daegu...)
Column C: Product (Product A, Product B, Product C)
Column D: Quantity (10, 25, 30...)
Column E: Unit Price (50000, 30000, 45000...)
Column F: Sales Amount (Quantity × Unit Price)
Column G: Sales Rep (Kim Cheolsu, Lee Younghee...)

2. Generating Basic Summary Metrics

AI Prompt

"Please summarize the sales data and calculate these metrics:
1. Total sales
2. Average transaction amount
3. Highest sales transaction
4. Number of transactions
5. Total sales by product"

AI Generated Formulas

// 1. Total sales
=SUM(F2:F1000)

// 2. Average transaction amount
=AVERAGE(F2:F1000)

// 3. Highest sales transaction
=MAX(F2:F1000)

// 4. Number of transactions
=COUNTA(F2:F1000)

// 5. Sales by product (Pivot table alternative)
// Product A sales
=SUMIF(C2:C1000, "Product A", F2:F1000)

// Or with dynamic range
=SUMIF($C$2:$C$1000, I2, $F$2:$F$1000)

3. Regional Sales Analysis

AI Prompt

"Please create a regional sales analysis table:
- Total sales for each region
- Market share percentage
- Month-over-month growth rate
- Goal achievement rate (reference separate sheet for goals)"

Generated Report Structure

// Sheet: Regional Analysis

   A          B              C           D              E
Region    Total Sales    Share(%)    MoM Change(%)  Goal Rate(%)
Seoul     50,000,000     35.5%       +12.3%         105%
Busan     30,000,000     21.3%       -5.2%          92%
Daegu     25,000,000     17.7%       +8.1%          110%

// Column B formula: Regional total sales
=SUMIF(Sales!$B:$B, A2, Sales!$F:$F)

// Column C formula: Market share
=B2/SUM($B$2:$B$10)*100&"%"

// Column D formula: MoM growth rate
=IFERROR((B2-VLOOKUP(A2,PrevMonthData!$A:$B,2,0))
  /VLOOKUP(A2,PrevMonthData!$A:$B,2,0)*100, 0)&"%"

// Column E formula: Goal achievement rate
=B2/VLOOKUP(A2,Goals!$A:$B,2,0)*100&"%"

4. Monthly Trend Analysis

AI Prompt

"Show monthly sales trends from January 2025 to present.
- Total sales for each month
- Month-over-month change amount
- Month-over-month change rate
- Cumulative sales"

Generated Formulas

// Sheet: Monthly Trends

   A        B              C            D          E
Month    Total Sales    MoM Change    Rate(%)    Cumulative

// Column B: Monthly total sales
=SUMIFS(Sales!$F:$F,
        Sales!$A:$A, ">="&DATE(2025,MONTH(A2),1),
        Sales!$A:$A, "<"&DATE(2025,MONTH(A2)+1,1))

// Column C: MoM change amount
=IF(ROW()=2, "", B2-B1)

// Column D: Change rate
=IF(C2="", "", TEXT(C2/B1, "0.0%"))

// Column E: Cumulative sales
=SUM($B$2:B2)

5. Product Portfolio Analysis

AI Prompt

"Classify product performance analysis by ABC grade:
- A grade: Top 20% (Core products)
- B grade: Middle 30% (Main products)
- C grade: Bottom 50% (Supporting products)

Include sales, quantity, and average unit price for each product."

Generated Formulas

// Sheet: Product Analysis

   A        B         C        D         E        F
Product  Total Sales  Quantity  Avg Price  Share    Grade

// Column B: Product total sales
=SUMIF(Sales!$C:$C, A2, Sales!$F:$F)

// Column C: Sales quantity
=SUMIF(Sales!$C:$C, A2, Sales!$D:$D)

// Column D: Average unit price
=B2/C2

// Column E: Market share
=B2/SUM($B:$B)

// Column F: ABC grade
=IF(E2>=PERCENTILE($E:$E,0.8), "A Grade",
   IF(E2>=PERCENTILE($E:$E,0.5), "B Grade", "C Grade"))

6. Sales Rep Performance Report

AI Prompt

"Create a performance table by sales rep:
- Total sales
- Number of transactions
- Average transaction amount
- Overall ranking
- Incentive (2% of sales)"

Generated Formulas

// Column B: Sales rep total sales
=SUMIF(Sales!$G:$G, A2, Sales!$F:$F)

// Column C: Number of transactions
=COUNTIF(Sales!$G:$G, A2)

// Column D: Average transaction amount
=B2/C2

// Column E: Ranking
=RANK(B2, $B:$B, 0)

// Column F: Incentive
=B2*0.02

// Conditional formatting to highlight top 3
// Rule: =E2<=3

7. Advanced Analysis: Cohort Analysis

AI Prompt

"I want to track the repurchase rate of new customers monthly.
Show how many months after first purchase each month's new customers repurchased."

Generated Formulas

// Find first purchase month
=TEXT(MIN(IF(Sales!$H:$H=A2, Sales!$A:$A)), "YYYY-MM")

// Repurchase rate N months later
=COUNTIFS(Sales!$H:$H, A2,
          Sales!$A:$A, ">="&DATE(year,month+N,1),
          Sales!$A:$A, "<"&DATE(year,month+N+1,1))
  /COUNTIF(Sales!$H:$H, A2)

8. Creating Dynamic Dashboards

AI Prompt

"Create an executive dashboard.
Place filters at the top (region, period, product)
and display summary metrics that update automatically based on selection."

Generated Structure

// Filter cell setup
B1: Region selection (dropdown)
B2: Start date
B3: End date
B4: Product selection

// Dynamic formulas
// Total sales
=SUMIFS(Sales!$F:$F,
        Sales!$B:$B, B1,
        Sales!$A:$A, ">="&B2,
        Sales!$A:$A, "<="&B3,
        Sales!$C:$C, B4)

// Transaction count
=COUNTIFS(Sales!$B:$B, B1,
          Sales!$A:$A, ">="&B2,
          Sales!$A:$A, "<="&B3,
          Sales!$C:$C, B4)

// Average transaction amount
=Sales/TransactionCount

9. Automated Weekly Reports

VBA Code Generation Request

AI Prompt:

"Create VBA code that automatically runs every Monday morning
and emails last week's sales summary."

AI Generated VBA:

Sub WeeklySalesReport()
    Dim lastWeekStart As Date
    Dim lastWeekEnd As Date
    Dim totalSales As Double
    Dim reportBody As String

    ' Calculate last week dates
    lastWeekEnd = Date - Weekday(Date)
    lastWeekStart = lastWeekEnd - 6

    ' Calculate last week sales
    totalSales = Application.WorksheetFunction.SumIfs( _
        Range("Sales[Amount]"), _
        Range("Sales[Date]"), ">=" & lastWeekStart, _
        Range("Sales[Date]"), "<=" & lastWeekEnd)

    ' Create report body
    reportBody = "Last Week Sales Summary" & vbCrLf & vbCrLf
    reportBody = reportBody & "Period: " & lastWeekStart & " ~ " & lastWeekEnd & vbCrLf
    reportBody = reportBody & "Total Sales: " & Format(totalSales, "#,##0") & " won" & vbCrLf
    reportBody = reportBody & "Daily Average: " & Format(totalSales / 7, "#,##0") & " won"

    ' Send email (Outlook)
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = "manager@company.com"
        .Subject = "Weekly Sales Report - " & Format(lastWeekEnd, "yyyy-mm-dd")
        .Body = reportBody
        .Send
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing

    MsgBox "Weekly report has been sent.", vbInformation
End Sub

10. Exception Monitoring

AI Prompt

"Automatically detect and flag these anomalies:
- 30% or more decline from previous month
- Daily sales below 50% of average
- 3 consecutive days of sales decline
- Sales halt due to specific product stockout"

Generated Formulas

// Detect sharp decline
=IF((PrevMonthSales-CurrentMonthSales)/PrevMonthSales > 0.3,
   "⚠️ 30%+ decline", "")

// Abnormally low
=IF(DailySales < AverageSales*0.5,
   "⚠️ Below average", "")

// Consecutive decline detection
=IF(AND(
   Sales!F2<Sales!F1,
   Sales!F1<Sales!F0,
   Sales!F0<Sales!F-1),
   "⚠️ 3-day consecutive decline", "")

11. Practical Template

Comprehensive Sales Report Structure

Sheet1: RawData
  - All transaction records

Sheet2: SummaryDashboard
  - Key KPIs
  - Period filters
  - Visualization charts

Sheet3: RegionalAnalysis
  - Regional detailed sales
  - Regional rankings

Sheet4: ProductAnalysis
  - Sales by product
  - ABC classification

Sheet5: RepPerformance
  - Individual performance
  - Incentive calculation

Sheet6: TrendAnalysis
  - Monthly/weekly trends
  - Year-over-year comparison

Conclusion

Creating sales reports with AI:

• ✅ Request complex formulas in natural language
• ✅ Automate diverse analytical perspectives
• ✅ Real-time updating dashboards
• ✅ Automatic exception detection

In the next post, we'll learn how to visually represent this data through automatic chart generation.