Sales and Business Dashboard Practice Questions with Solutions

Introduction

A Sales & Business Dashboard helps convert business data into useful information such as sales performance, profit, targets, regional performance, product performance, salesperson results, and customer activity. In this chapter, you will practice building business-focused dashboards using realistic sales data. The questions cover KPI calculations, sales analysis, target comparison, regional performance, salesperson performance, product analysis, monthly trends, and interactive dashboard reporting. Sales and Business Dashboard Practice questions with solutions to help you understand the concepts.

Question 1: Create a Sales Performance KPI Dashboard

Problem Statement

A company wants to monitor its overall sales performance. Create KPI values for Total Sales, Total Expenses, Total Profit, Total Orders, and Profit Margin.

Excel Data

Excel Solution

Total Sales:

=SUM(B2:B13)

Total Expenses:

=SUM(C2:C13)

Total Profit:

=SUM(B2:B13)-SUM(C2:C13)

Total Orders:

=SUM(D2:D13)

Profit Margin:

=(SUM(B2:B13)-SUM(C2:C13))/SUM(B2:B13)

Format the Profit Margin as a percentage.

Expected Result

Concepts Covered

• Business KPIs
• Sales calculation
• Expense calculation
• Profit calculation
• Profit Margin
• Dashboard KPI cards

Question 2: Create a Monthly Sales Performance Dashboard

Problem Statement

Create a dashboard section that shows monthly sales performance and identifies the highest and lowest sales months.

Excel Data

Excel Formulas

Highest Monthly Sales:

=MAX(B2:B13)

Lowest Monthly Sales:

=MIN(B2:B13)

Average Monthly Sales:

=AVERAGE(B2:B13)

Excel Solution

Create three KPI cards:

• Highest Monthly Sales
• Lowest Monthly Sales
• Average Monthly Sales

Then create a line chart using Month and Sales.

Expected Result

The line chart should show the monthly movement in sales.

Concepts Covered

• MAX
• MIN
• AVERAGE
• Monthly sales trend
• Line chart
• Business KPI

Question 3: Build a Region-Wise Sales Dashboard

Problem Statement

Create a regional sales dashboard showing Sales, Expenses, Profit, and Orders for each region.

Excel Data

Excel Solution

Add a Profit column.

Formula in E2:

=B2-C2

Copy the formula down.

Create a column chart using Region and Sales.

Create a second chart using Region and Profit.

Expected Result

Concepts Covered

• Regional business analysis
• Profit calculation
• Column charts
• Multiple KPIs
• Regional dashboard

Question 4: Create a Product Performance Dashboard

Problem Statement

Analyze product-level business performance using Sales, Units Sold, and Profit.

Excel Data

Excel Solution

Add a Profit column:

=C2-D2

Create a bar chart for Product vs Sales.

Create another chart for Product vs Profit.

Create a KPI for total product sales:

=SUM(C2:C8)

Create a KPI for total product profit:

=SUM(E2:E8)

Expected Result

Concepts Covered

• Product analysis
• Sales vs Cost
• Product profit
• Bar charts
• Product dashboard

Question 5: Create a Salesperson Performance Dashboard

Problem Statement

Management wants to compare salespeople based on Sales, Orders, and Profit.

Excel Data

Excel Solution

Add a Profit column:

=B2-D2

Create a Salesperson vs Sales bar chart.

Create a Salesperson vs Profit chart.

Calculate total sales:

=SUM(B2:B7)

Calculate total orders:

=SUM(C2:C7)

Expected Result

Concepts Covered

• Salesperson analysis
• Sales comparison
• Profit comparison
• Business performance charts
• Dashboard reporting

Question 6: Create a Sales Target vs Actual Dashboard

Problem Statement

A company has monthly sales targets. Create a dashboard comparing Actual Sales with Target Sales and calculate the achievement percentage.

Excel Data

Excel Formulas

Total Target:

=SUM(B2:B13)

Total Actual:

=SUM(C2:C13)

Variance:

=C16-B16

Achievement Percentage:

=C16/B16

Expected Result

Create a clustered column chart comparing Target Sales and Actual Sales.

Concepts Covered

• Target analysis
• Actual vs Target
• Variance
• Achievement percentage
• Comparison charts

Question 7: Create a Customer Sales Dashboard

Problem Statement

Create a business dashboard that analyzes customer-level sales and orders.

Excel Data

Excel Solution

Add a Net Sales column:

=C2-D2

Create a bar chart using Customer and Net Sales.

Create KPI cards for:

• Total Customers
• Total Orders
• Gross Sales
• Total Returns
• Net Sales

Total Customers:

=COUNTA(A2:A9)

Total Orders:

=SUM(B2:B9)

Gross Sales:

=SUM(C2:C9)

Total Returns:

=SUM(D2:D9)

Net Sales:

=SUM(E2:E9)

Expected Result

Concepts Covered

• Customer analysis
• Returns analysis
• Net sales
• COUNTA
• SUM
• Customer dashboard

Question 8: Create a Business Expense Dashboard

Problem Statement

Management wants to understand where the company’s money is being spent. Create a dashboard showing expenses by category.

Excel Data

Excel Solution

Add a Total Expense column:

=SUM(B2:E2)

Copy the formula for all categories.

Create a bar chart using Expense Category and Total Expense.

Create a monthly expense summary:

=SUM(B2:B8)

Repeat for February, March, and April.

Expected Result

Concepts Covered

• Expense analysis
• SUM
• Business cost reporting
• Category comparison
• Expense dashboard

Question 9: Create a Monthly Business Health Dashboard

Problem Statement

Create a dashboard that combines Sales, Profit, Orders, and New Customers into a monthly business performance view.

Excel Data

Excel Solution

Create the following KPI cards:

Total Sales

=SUM(B2:B13)

Total Profit

=SUM(C2:C13)

Total Orders

=SUM(D2:D13)

New Customers

=SUM(E2:E13)

Create:

• Line chart for Sales
• Line chart for Profit
• Column chart for Orders
• Column chart for New Customers

Expected Result

Concepts Covered

• Business health dashboard
• Multiple KPIs
• Monthly analysis
• Sales trends
• Customer growth
• Dashboard visualization

Question 10: Build a Complete Interactive Sales & Business Dashboard

Problem Statement

Create a complete business dashboard using the following dataset.

The dashboard should allow management to analyze business performance by:

• Month
• Region
• Product
• Salesperson
• Sales
• Expenses
• Profit
• Orders

Excel Data

Excel Solution

Create a Profit Column

In the new Profit column:

=E2-F2

Copy the formula down.

Create KPI Cards

Total Sales

=SUM(E2:E17)

Total Expenses

=SUM(F2:F17)

Total Profit

=SUM(G2:G17)

Total Orders

=SUM(H2:H17)

Profit Margin

=SUM(G2:G17)/SUM(E2:E17)

Create Dashboard Charts

Create the following charts:

1. Sales by Month
2. Sales by Region
3. Sales by Product
4. Sales by Salesperson
5. Profit by Month

Add Interactive Slicers

Create slicers for:

• Month
• Region
• Product
• Salesperson

Suggested Dashboard Layout

----------------------------------------------------------------
                 SALES & BUSINESS DASHBOARD
----------------------------------------------------------------

 TOTAL SALES    TOTAL EXPENSES    TOTAL PROFIT    TOTAL ORDERS
 ₹________      ₹________         ₹________       ________

                         PROFIT MARGIN
                             ____%

----------------------------------------------------------------

                     SALES BY MONTH
                         [Chart]

----------------------------------------------------------------

       SALES BY REGION          SALES BY PRODUCT
           [Chart]                  [Chart]

----------------------------------------------------------------

     SALES BY SALESPERSON          PROFIT BY MONTH
            [Chart]                    [Chart]

----------------------------------------------------------------

 Month          Region          Product       Salesperson
 [Slicer]       [Slicer]        [Slicer]      [Slicer]

----------------------------------------------------------------

Expected Result

The final worksheet should contain a complete Sales & Business Dashboard with:

• Business KPI cards
• Sales analysis
• Expense analysis
• Profit analysis
• Order analysis
• Monthly trends
• Regional analysis
• Product analysis
• Salesperson analysis
• Interactive slicers

Concepts Covered

• Complete business dashboard
• KPI cards
• Sales analysis
• Profit analysis
• Expense analysis
• Regional analysis
• Product analysis
• Salesperson analysis
• Interactive slicers
• Dashboard layout
• Business reporting

Key Takeaways

• A Sales & Business Dashboard converts raw business data into a visual report.
• Sales, Expenses, Profit, Orders, and Profit Margin are useful business KPIs.
• Monthly analysis helps identify changes in business performance over time.
• Regional dashboards can be used to compare sales across different areas.
• Product dashboards help analyze sales and profitability by product.
• Salesperson dashboards can compare individual performance.
• Target vs Actual analysis helps monitor progress against planned sales.
• Customer dashboards can combine sales, orders, and returns.
• Expense dashboards help businesses understand their major cost categories.
• Slicers make dashboards more interactive.
• A business dashboard should show useful information without overcrowding the screen.
• Charts should be selected according to the type of business information being analyzed.
• A well-structured Excel Table makes dashboard maintenance easier.

FAQs

1. What is a Sales & Business Dashboard in Excel?

It is a visual Excel report that combines business KPIs, charts, tables, and filters to analyze sales and other business information.

2. Which KPIs are commonly used in a sales dashboard?

Common KPIs include Total Sales, Total Profit, Total Expenses, Total Orders, Average Order Value, Profit Margin, and Target Achievement.

3. What can be analyzed using a sales dashboard?

A sales dashboard can analyze monthly sales, regional sales, product performance, salesperson performance, customers, orders, expenses, profit, and targets.

4. Why is Profit Margin useful in a business dashboard?

Profit Margin shows the proportion of sales that remains as profit after expenses or costs represented in the calculation.

5. How can I compare sales with a target in Excel?

You can calculate Total Target, Actual Sales, Variance, and Achievement Percentage and display them using KPI cards and charts.

6. How can I make a business dashboard interactive?

Slicers, filters, PivotTables, PivotCharts, and formula-based summaries can be used to create interactive dashboards.

7. Can I create a dashboard for individual salespeople?

Yes. Salesperson-level data can be summarized and displayed using KPI cards, tables, bar charts, and filters.

8. Can an Excel dashboard analyze expenses?

Yes. Expense categories can be summarized by month or category and displayed using charts and KPI cards.

9. How many charts should a business dashboard contain?

There is no fixed number. The dashboard should contain enough visualizations to answer the important business questions without making the screen overcrowded.

10. Can the same dashboard be used every month?

Yes. If the source data is structured properly, new records can be added and the dashboard can be refreshed or updated.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar