Quarterly Sales Report

Goal

MavenTech, a company that specializes in selling computer hardware to large businesses has the following requirement to become a data-driven organization: Building an interactive dashboard that enables sales managers to track their team's quarterly performance from their CRM data.

Data

The data consists of one fact table that holds all CRM Opportunities from five Quarters and four dimension tables: Date, Products, Accounts and Sales Teams. In the provided time period MavenTech creates around 22 Opportunities per day and has 6 sales managers with 5 sales agents each. The company has 7 products and sells to more than 80 customers.

Content

My goal was to only use two report pages to keep the report slim. The first page analyzes the sales. The second page looks at the sales pipeline.

Page 1:

Given the short goal statement, first I defined four KPI's the company MavenTech uses to analyze their sales combined with four comparison values:

• KPI: Sales in USD; Comparison Value: Sales last Quarter to see how business is evolving
• KPI: % of Won Opportunities; Comparison Value: % of Won Opportunities last Quarter to see how big the conversion rate of Opportunities is
• KPI: Average Opportunity Lead Time; Comparison Value: Average Opportunity Lead Time last Quarter to see how much time it takes to close Opportunities
• KPI: Average Sales Price; Comparison Value: Target Sales Price to see for what sales price the products are sold

Next, I defined the requirements as if I would be a sales manager by answering the following questions:

• How does my team perform in comparison to the other teams?
• How do my sales agents perform? What products are we selling?
• What accounts are buying from my sales agents? And which products do they buy?
• Do we as a team have a cluster risk: Are there only a few accounts responsible for the majority of our turnover?

Page 2:

This page is separated in three stages:

1. Input The Input shows how many Opportunities are created per Quarter and per sales agent
2. In Process This area shows how many Opportunities are Open in respect to the CRM-stage, the sales agent and the account
3. Output Analogous to the Input one can see how many Opportunities are closed per Quarter and per sales agent

The goal of this page is that the sales manager can see how many input his sales pipeline receives and what the current backlog is. In combination with the % of won Opportunities and the average sales price a forecast of future sales can be made.

Report Structure

For better overview the first page only consists of 5 visuals. For a better overview and a faster understanding of the report I repeated the four KPI's and their comparison values in all 4 table visuals on the page. However, for the sales per account table I slightly differed by inserting a new column for the total number of Opportunities. I did that for being able to better analyze the numbers because when you filter the table for one sales agent there are a lot of one time sales that show either a 0% or a 100% of Won Opportunities.

The first page works best by using cross-filtering. Cross-filtering allows to reduce the number of visuals by maintaining answering many data questions. For example in the report provided you can click on one account and see which products were sold to this account.

The reports provides a Drillthrough page to see all Opportunities per account. This enabled the sales managers to see in more detail what was sold at which price. Here, the next step would be to insert a link for each row that leads the user directly to the CRM record for further information (e.g. the reason for losing the Opportunity).

Finally, below the account table the sales manager sees the share of the TOP 10 Accounts of all sales. For example sales manager Dustin Brinkmann has a healthy share of TOP 10 Accounts of 54% in Q4/2017. However, his sales agent Versie Hillebrand has a 96% of TOP 10 Accounts in the same period.

The second page holds considerably less information that allows to show 7 visuals and still being easy to process.

Colors and Guidelines

In respect to color blindness all positive values in the report are in light blue and all negative values are in a light yellow.

Data bars are used in the table visuals for a quick visual support of the numbers. The color on the first page is grey to harmonize with the light blue and light yellow. On the second page a light purple is used to have a contrast from the first page as it presents different information.

Slicers are used instead of the filter pane to better see what is filtered. Only two slicers are available because from my point of view only the time and the sales manager are important to filter. For better filter value communication and if screenshots are exported into presentations, all table visuals have a dynamic chart title. Space would be available to add more slicers to filter for example the sector or the global accounts.

In all visuals I followed the guideline as little ink as necessary.