Sales & Marketing Dashboard

A Sales & Marketing dashboard tracks the full revenue funnel: from leads and campaigns through to closed deals and ROI. Sales managers use it to spot slipping deals and top performers; marketing teams use it to shift budget to the channels that pay back. We build it with the foundation method, then add sales pipeline and marketing funnel metrics.

Table of Contents

• 1. Who Uses It and Why
• 2. Data You Need
  • Sales Data
  • Marketing Data
• 3. Key KPIs
  • Sales KPIs
  • Marketing KPIs
• 4. Build KPIs with Copilot
• 5. Charts to Include
• 6. Sales Pipeline Funnel
• 7. Marketing Conversion Funnel
• 8. ChatGPT Prompt to Plan It

1. Who Uses It and Why

Sales teams use this to answer: Are we hitting target? Which region or rep is lagging? Where are deals stalling?

Marketing teams use this to answer: Which channel pays back? Where does the funnel leak? Is our spend efficient?

Together, the dashboard connects marketing investment to sales outcome so both teams align on what drives revenue.

2. Data You Need

Sales Data

Load a table named SalesData with these columns:

Marketing Data

Load a table named MktgData with these columns:

3. Key KPIs

Sales KPIs

• Total Revenue — money in.
• Total Profit — revenue minus cost.
• Win Rate — won ÷ (won + lost).
• Average Deal Size — revenue ÷ number of deals.
• Sales per Rep — for the leaderboard.

Marketing KPIs

• CTR — clicks ÷ impressions.
• CPC — spend ÷ clicks.
• CPL — spend ÷ leads.
• Conversion Rate — conversions ÷ clicks.
• ROAS — revenue ÷ spend.
• Total Marketing Revenue — attributed revenue from campaigns.

4. Build KPIs with Copilot

Ask Copilot to add profit and ratio columns:

Add a column called Profit to SalesData as Revenue minus Cost.
Add columns CTR and ROAS to MktgData as Clicks/Impressions and Revenue/Spend.

Then drop KPI cards using:

Sales cards

=SUM(SalesData[Revenue])
=SUM(SalesData[Profit])
=AVERAGE(SalesData[Revenue])
=COUNTIFS(SalesData[Stage],"Won")/
 (COUNTIFS(SalesData[Stage],"Won")+COUNTIFS(SalesData[Stage],"Lost"))

Marketing cards

=AVERAGE(MktgData[CTR])
=AVERAGE(MktgData[ROAS])
=SUM(MktgData[Revenue])/SUM(MktgData[Spend])
=SUM(MktgData[Spend])/SUM(MktgData[Leads])

The win-rate formula divides closed-won deals by all closed deals. The ROAS card divides total marketing revenue by total spend for a portfolio view.

5. Charts to Include

Sales charts

• Column — Revenue by Region.
• Line — Revenue trend by Month.
• Bar — Top 10 Sales Reps.

Marketing charts

• Column — Spend vs Revenue by Channel.
• Line — CTR trend over time.
• Bar — ROAS by Campaign.

Combined view

• Combo — Marketing Spend (column) and Sales Revenue (line) by month to show the lag between investment and return.

Copilot prompts:

Insert a column chart of revenue by region from SalesData.
Insert a column chart of spend versus revenue by channel from MktgData.

6. Sales Pipeline Funnel

Show how many deals sit in each stage so managers can push the stuck ones.

=COUNTIFS(SalesData[Stage],"Lead")
=COUNTIFS(SalesData[Stage],"Qualified")
=COUNTIFS(SalesData[Stage],"Proposal")
=COUNTIFS(SalesData[Stage],"Won")

Plot these as a bar chart ordered Lead → Won to read like a funnel.

7. Marketing Conversion Funnel

Show drop-off from impression to conversion so marketers can fix the leakiest step.

=SUM(MktgData[Impressions])
=SUM(MktgData[Clicks])
=SUM(MktgData[Leads])
=SUM(MktgData[Conversions])

Plot as a bar chart Impressions → Clicks → Leads → Conversions to see where users fall out.

8. ChatGPT Prompt to Plan It

Design a combined sales and marketing dashboard in Excel.
Sales table SalesData has columns OrderID, Date, Region, SalesRep, Revenue, Cost, Stage.
Marketing table MktgData has columns Campaign, Channel, Spend, Impressions, Clicks, Leads, Conversions, Revenue.
Give me the KPIs for each, the best chart for each, a combined spend-vs-revenue view, and Copilot prompts to build it.