Business Analytics Dashboard with KPIs & AI Narrative

A business analytics dashboard combines several metrics — sales, profit, customers, regions — into one interactive view. You build it in Excel and use Copilot and ChatGPT for the measures and the trend narrative.

Table of Contents

• Project Overview
• Features
• Prerequisites
• Folder Structure
• Step-by-Step Development
• Complete Source Code
• Running the Project
• Testing
• Common Errors
• Improvements
• Challenge Exercises

Project Overview

• What it does: It computes KPIs (revenue, profit, customers, region mix) from a transaction table and shows them with PivotTables, slicers, and charts.
• Where it is used: Business reviews, dashboards, and strategy meetings.
• What you learn: SUMIFS, PivotTables, slicers, KPI cards, and AI trend writing.

Features

• KPI cards (revenue, profit, customers)
• Region and product breakdowns
• Interactive slicers
• Trend charts
• AI-generated narrative of trends

Prerequisites

• Microsoft Excel (Microsoft 365)
• ChatGPT account
• A transaction table with Region, Product, Revenue, Cost, Customer

Folder Structure

business-analytics-dashboard.xlsx
│
├── Sheet: Data     (Date, Region, Product, Customer, Revenue, Cost)
├── Sheet: Dashboard (KPIs, pivot, charts, slicers)
├── Sheet: Narrative (AI text)
└── Module: BizMacros (optional VBA)

• Data: the source transactions.
• Dashboard: the visual layer.
• Narrative: the AI-written story.
• BizMacros: optional helpers.

Step-by-Step Development

Step 1 – KPI cards

What are we building?

Cards for total revenue, profit, and customer count.

Why do we need it?

Headline metrics first.

How do we build it?

Name the table Biz.

=SUM(Biz[Revenue])
=SUM(Biz[Revenue]) - SUM(Biz[Cost])
=COUNTA(Biz[Customer])

• First formula: total revenue.
• Second formula: revenue minus cost is profit.
• Third formula: counts customer rows (unique enough for a small set).
• Why needed: the top-line KPIs.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write three Excel formulas: sum Revenue, profit as Revenue minus Cost, and count of Customer rows."

Step 2 – Breakdown by region

What are we building?

Revenue and profit per region.

Why do we need it?

Shows strongest markets.

How do we build it?

=SUMIFS(Biz[Revenue], Biz[Region], A2)
=SUMIFS(Biz[Revenue], Biz[Region], A2) - SUMIFS(Biz[Cost], Biz[Region], A2)

• What it does: sums revenue, then revenue minus cost, for the region in A2.
• Why needed: each region gets revenue and profit.

Use AI (Copilot & ChatGPT)

Ask Copilot:

"Write SUMIFS for revenue by region, and profit as revenue minus cost by region."

Step 3 – PivotTable and slicers

What are we building?

A PivotTable of revenue by region and product with slicers.

Why do we need it?

Clickable filtering for meetings.

How do we build it?

1. Insert > PivotTable from Biz.
2. Region in Rows, Product in Columns, Revenue in Values.
3. Insert > Slicer for Region and Month.

No formula; built from the ribbon.

• What it does: groups revenue interactively.
• Why needed: slicers filter the whole dashboard.

Use AI (Copilot & ChatGPT)

Ask Copilot:

"Create a PivotTable from Biz with Revenue by Region and Product, and add slicers for Region and Month."

Step 4 – AI narrative

What are we building?

A written summary of the trends.

Why do we need it?

A paragraph explains the numbers to readers.

How we build it?

Copy the KPIs and region table into ChatGPT.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write a 4-sentence business narrative from these KPIs and region profits. Name the top region and the main risk."

Paste the answer into the Narrative sheet.

Complete Source Code

Dashboard formulas

=SUM(Biz[Revenue])
=SUM(Biz[Revenue]) - SUM(Biz[Cost])
=COUNTA(Biz[Customer])
=SUMIFS(Biz[Revenue], Biz[Region], A2)
=SUMIFS(Biz[Revenue], Biz[Region], A2) - SUMIFS(Biz[Cost], Biz[Region], A2)

AI prompts

ChatGPT: "Sum Revenue, profit Revenue-Cost, count Customer."
Copilot: "SUMIFS revenue and profit by region."
Copilot: "PivotTable Revenue by Region/Product with slicers."
ChatGPT: "4-sentence narrative from KPIs and region profits."

Running the Project

1. Open business-analytics-dashboard.xlsx.
2. Make sure Data is a table named Biz.
3. Click slicers; KPIs, pivot, and chart update.
4. Expected output: a live analytics dashboard with a written summary.

Testing

Test Case 1

Input: Revenue 1000, Cost 600. Expected Output: Profit 400. Explanation: 1000 − 600.

Test Case 2

Input: Region North revenue 500 cost 200; South 300 cost 100. Expected Output: North profit 300, South profit 200. Explanation: SUMIFS per region.

Test Case 3

Input: Empty table. Expected Output: Revenue 0, Profit 0, Customers 0. Explanation: SUM/COUNTA find nothing.

Common Errors

Profit negative

Problem: Shows loss. Reason: Cost exceeds revenue somewhere. Solution: Check cost entries; profit is revenue minus cost.

Slicer filters nothing

Problem: No effect. Reason: Wrong PivotTable connection. Solution: Right-click slicer > Report Connections and tick the pivot.

Customer count inflated

Problem: Too many customers. Reason: Same customer on many rows counted each time. Solution: Use COUNTA(UNIQUE(Biz[Customer])) if available.

Improvements

• Add a profit-margin % KPI
• Connect to Power BI for sharing
• Power Automate weekly email
• Ask ChatGPT for a forecast
• Export to PDF

Challenge Exercises

1. Add a profit margin % (profit / revenue).
2. Write a formula for the top region by profit using XLOOKUP.
3. Use ChatGPT to write VBA that refreshes all pivots.
4. Build a customer trend chart by month.
5. Let ChatGPT predict next quarter from the region data.