CRM Dashboard with Pipeline & Win-Rate Analysis

A CRM dashboard tracks leads and customers through deal stages and shows how many you win. You build it in Excel and use Copilot and ChatGPT to write the stage formulas and the insight summary.

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 records deals with stage and value, counts deals per stage, and calculates win rate.
• Where it is used: Sales and business-development teams managing pipelines.
• What you learn: COUNTIF, SUMIF, win-rate math, charts, and AI narrative writing.

Features

• Deal list with stage and value
• Count and value per stage
• Win rate percentage
• Pipeline chart
• AI-written trend summary

Prerequisites

• Microsoft Excel (Microsoft 365)
• ChatGPT account
• A stage list like Lead, Contacted, Won, Lost

Folder Structure

crm-dashboard.xlsx
│
├── Sheet: Deals    (DealID, Customer, Stage, Value)
├── Sheet: Dashboard (stage counts, win rate, chart)
└── Module: CrmMacros (optional VBA)

• Deals: the pipeline.
• Dashboard: the metrics and chart.
• CrmMacros: optional helpers.

Step-by-Step Development

Step 1 – Deal table and stages

What are we building?

A table Deal with Stage from a dropdown.

Why do we need it?

Stages drive every count.

How do we build it?

Make the table and a dropdown Lead,Contacted,Won,Lost.

=COUNTA(Deal[DealID])

• What it does: total number of deals.
• Why needed: the base for win rate.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"How do I add a dropdown with the values Lead, Contacted, Won, Lost in Excel?"

Step 2 – Count and value per stage

What are we building?

Counts and total value for each stage.

Why do we need it?

Shows where money sits in the pipeline.

How do we build it?

=COUNTIF(Deal[Stage], A2)
=SUMIF(Deal[Stage], A2, Deal[Value])

• First formula: counts deals in the stage named in A2.
• Second formula: sums their values.
• Why needed: each stage gets count and value.

Use AI (Copilot & ChatGPT)

Ask Copilot:

"Write COUNTIF and SUMIF for deals where Stage equals A2, summing the Value column."

Step 3 – Win rate

What are we building?

A percentage of won deals out of closed deals.

Why do we need it?

Win rate is the key sales health metric.

How do we build it?

=IFERROR(COUNTIF(Deal[Stage],"Won")/(COUNTIF(Deal[Stage],"Won")+COUNTIF(Deal[Stage],"Lost")), 0)

• What it does: won divided by won plus lost.
• Important line: IFERROR returns 0 when nothing is closed yet.
• Why needed: a clean percentage even with no closed deals.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write an Excel win-rate formula: won divided by won plus lost, with divide-by-zero handling."

Step 4 – Chart and AI summary

What are we building?

A bar chart of value per stage and an AI paragraph.

Why do we need it?

Charts and words make the pipeline clear to managers.

How we build it?

Select the stage value table, Insert > Bar Chart.

Use AI (Copilot & ChatGPT)

Paste stage counts into ChatGPT:

"Summarize this sales pipeline in three sentences and say the biggest risk."

Complete Source Code

Dashboard formulas

=COUNTIF(Deal[Stage], A2)
=SUMIF(Deal[Stage], A2, Deal[Value])
=IFERROR(COUNTIF(Deal[Stage],"Won")/(COUNTIF(Deal[Stage],"Won")+COUNTIF(Deal[Stage],"Lost")), 0)

AI prompts

ChatGPT: "Dropdown Lead, Contacted, Won, Lost."
Copilot: "COUNTIF and SUMIF for Stage A2 sum Value."
ChatGPT: "Win rate won/(won+lost) with error handling."
ChatGPT: "Summarize pipeline in three sentences."

Running the Project

1. Open crm-dashboard.xlsx.
2. Enter deals with stage and value.
3. Dashboard shows counts, win rate, and chart.
4. Expected output: live pipeline metrics.

Testing

Test Case 1

Input: Won 3, Lost 1, Open 6. Expected Output: Win rate 75% (3/4 closed). Explanation: Only won + lost count as closed.

Test Case 2

Input: No Won or Lost yet. Expected Output: Win rate 0. Explanation: IFERROR avoids divide-by-zero.

Test Case 3

Input: Three Won deals worth 100, 200, 300. Expected Output: Won value 600. Explanation: SUMIF adds the values.

Common Errors

Win rate over 100%

Problem: Percentage too high. Reason: Divided by total deals instead of closed. Solution: Use won/(won+lost) only.

Stage not counted

Problem: Count is zero. Reason: Stage text differs from dropdown. Solution: Use the dropdown for exact text.

Chart empty

Problem: No bars. Reason: Selected the wrong range. Solution: Select stage and value columns together.

Improvements

• Add expected close date and aging
• Use Power Automate to alert stale deals
• Connect to email for new leads
• Ask ChatGPT to rank deals by value
• Forecast revenue with a weighted pipeline

Challenge Exercises

1. Add a weighted value (stage probability × value) column.
2. Write a formula for the average deal size of Won deals.
3. Use ChatGPT to write VBA that moves a deal to Won.
4. Build a chart of deals created per month.
5. Let ChatGPT predict next quarter's win rate from history.