Goal: To efficiently give an overview of cases that has an expected revenue of $10,000.

Outcome: I created a dashboard that turned hours of manual work into 10 minutes of updating. 

The Problem

The firm partners reviewed their Impact Cases during their monthly meetings. Impact Cases were defined as cases in the Sales Pipeline where the Total Expected Revenue was at least $10,000.

The report was created manually by one partner in an Excel spreadsheet and was fairly labor intensive. He would take redundant notes during the weekly Sales Pipeline meeting. The notes were redundant because they were already captured in their CRM system, which is where the Sales Pipeline lived. The partner would also meet with an employee 1-2 times a month review the list, going down each record, to learn where they were in the pipeline.

The Design

Since the data lived in the CRM database, I created an SQL query to pull all the Sales Opportunities in the current year that had a Total Expected Revenue greater than or equal to $10,000. To make the report as automatic as possible, I used Excel's Power Query to establish a connection to a folder where the raw data could be downloaded from the CRM.

The raw data had modal revenue for the firm and agents. However, I would need annualized revenue to maintain consistent values. To do this inside Power Query,

1. I created a helper column ("Mode") to cast the "Premium Mode" column from a string to the corresponding integer. Because we're only looking at First Year Commission, this translates as Monthly = 12, Quarterly = 4, Semi-Annual = 2, and everything else = 1.

2. I used "Mode" to calculate the Annualized Revenue for each person/group.

The Solution

To be able to show all the desired information, I decided to break it up between two tabs.

The first tab is the dashboard showing a broad overview of the Total Expected Revenue broken down in three important ways:

1. Total Expected Revenue by Sales Pipeline Stage

2. Total Expected Revenue by Policy Type

3. Total Expected Revenue by Agent

The second tab allows the partners to drill down to see specific client information in two ways:

• By Sales Pipeline Stage

• By Referring Advisor

The second tab also importantly breaks down the Total Expected Revenue to where the commission goes. The partners' commissions go to the firm. There are two non-partner agents who have different commission splits. This allows the partners to see with a high degree of accuracy how much the firm can expect to get paid on each case.