Tutorial Overview: Creating a Customer Engagement and Sales Metrics Report in Excel

Introduction

• This tutorial demonstrates how to create a customer engagement and sales metrics report using Microsoft Excel.
• Visit otherdls.com to download templates and data sets used in this tutorial.

Preparing the Data Set

1. Table Adjustments:

   • Format fonts and colors for better readability.
   • Insert three rows at the top and one column on the left to structure data.
   • Remove grid lines for a cleaner look.

2. Conditional Formatting:

   • Apply a formatting rule to highlight each year in a different color for easier trend analysis.

Data Analysis

1. Pivot Table:

   • Insert a pivot table from the main data set.
   • Add the amount to the values field to obtain total sales.
   • Link total sales value to a separate cell for dynamic updates.

2. Top 20 Customers:

   • Duplicate pivot table to include orders and offers in values field.
   • Add customer name to rows field.
   • Sort sales amounts from highest to lowest.
   • Calculate total sales of these 20 customers using the sum formula.

Visualization

1. Pie Chart:

   • Visualize the percentage contribution of top 20 customers to total sales.
   • Move pie chart to a new sheet named 'Dashboard'.

2. Column Chart:

   • Create a table displaying top 20 customer orders and offers.
   • Sum up total number of orders and offers; calculate success rate.

Dashboard Design

1. Arrangement:

   • Arrange charts, create text boxes, and link title values for a structured layout.

2. Background Design:

   • Insert and style shapes:
     • Rounded rectangular shape with white color and shadow for depth.
     • Gradient filled rectangular shape for modern look.
     • Triangle shape with 85% transparency for design enhancement.
     • Colorful shapes in top left corner for visual appeal.

Conclusion

• Download the dashboard template from otherdls.com.
• If the tutorial was helpful, like, share, and subscribe for more content.

Call to Action

• Subscribe for the next video and stay updated with more tutorials.