ajayifayokemi24@gmail.com

+234 806 072 8626

  1. Project Overview
  • Title: Desktop and Mobile Dashboard for Sales managers(Sales Cycle)
  • Objective: To develop a dedicated dashboard for PQP sales managers, providing real-time insights into the sales cycle performance, revenue trends, and cost-benefit analysis. The dashboard aims to track marketing expenses, revenue, and outstanding balances to optimize decision-making.

2. Description

  • Before this dashboard, the Executive Dashboard only provided high-level revenue tracking but lacked real-time insights into sales cycle performance and marketing cost efficiency. This new Sales Cycle Dashboard bridges the gap by offering real-time tracking of sales cycle revenue, cost, and conversion rates.

3. Problem Statement

Key challenges faced by the sales team included:

  • No clear breakdown of revenue per sales cycle.
  • Inability to track the cost-effectiveness of marketing strategies (ads, emails, follow-ups).
  • Lack of real-time visibility into outstanding balances per manager.
  • Limited insights into revenue trends across multiple sales cycles.

To address these, a dashboard was developed to track revenue per sales cycle, analyze cost-benefit ratios, and monitor outstanding balances.

4. Challenges

  • Extracting revenue per sales cycle from raw transaction data.
  • Integrating sales cycle cost data with legacy tables for seamless analysis.
  • Tracking unpaid balances and balance DAC (Debt After Collection).
  • Distinguishing between old business revenue (pre-Dec 2023) and new sales cycle revenue.

5. Goal

The primary goal is to analyze the cost-to-benefit ratio of marketing expenses and track:

  • Revenue per sales cycle.
  • Sales cycle cost vs. revenue generated.
  • Outstanding balance per sales manager.
  • Conversion rates and effectiveness of lead follow-ups.

6. Methodology

To achieve these objectives, the following steps were taken:

  • Sales Cycle Data Integration:
    • The sales team provided a schedule containing Sales Cycle Name, Start Date, and End Date.
    • A DAX-calculated table was created to track revenue per sales cycle based on these dates.
  • Cost Data Import & ETL Process:
    • Sales cycle cost data was provided separately by the team.
    • The cost data was imported into the legacy table using ETL processes, ensuring seamless integration with existing sales data.
  • DAX Calculation for Revenue Per Sales Cycle:
    • Using the sales cycle start and end dates, DAX formulas were used to dynamically calculate total revenue per cycle.
  • Sales Cycle Cost-to-Benefit Analysis:
    • The total marketing spend per cycle was compared against revenue generated to derive the cost-to-benefit ratio.
  • Balance and Bad Debt Ratio Calculation:
    • Balance = Total Amount Owed by Customers.
    • Bad Debt Ratio = outstanding balance against total revenue

7.Key Performance Indicators (KPIs)

  • Total Registrations: 2,286 (All-time)
  • Total Activations: 981 (All-time)
  • Old Business Revenue (Before Dec 2023): ₦49.64M
  • Sales Cycle Revenue (Since Dec 2023): ₦85.04M
  • Sales Cycle Cost (Marketing Spend): ₦8.95M
  • Conversion Rate: 42.9%
  • Leads: 790
  • Bad Debt Percentage: 5.68%

8.Cost-to-Benefit Ratio Analysis

Insights:

  • For every ₦1 spent on advertising, PQP generates ₦9.50 in revenue.
  • The cost-to-benefit ratio of 10.5% indicates a strong return on marketing investment.
  • This confirms that advertising, email campaigns, and follow-up calls are effectively driving revenue.

9.Recommendations

  • Increase marketing spend slightly (if cost-to-benefit remains under 15%) to further boost revenue.
  • Improve debt collection efforts among managers with high outstanding balances (Kezi, Deborah, Rita).
  • Monitor low-performing sales cycles to adjust sales strategies and improve revenue.
  • Assess marketing channels (social media vs. email vs. calls) to optimize advertising cost distribution.
  • Target low-revenue months (e.g., Nov 2024) with strategic promotions to boost conversions.

10. Impact Insights

  • Better ROI Analysis: Sales managers can now track how marketing costs impact revenue growth.
  • Improved Debt Recovery Strategy: Identifying managers with high balances allows for focused payment follow-ups.
  • Sales Performance Optimization: Managers can now adjust sales strategies in real-time based on dashboard insights.
  • More Efficient Budget Allocation: Marketing spend can be optimized based on cost-to-benefit analysis.

11. Conclusion

  • The Sales Cycle Dashboard has transformed how PQP monitors sales performance, marketing ROI, and outstanding balances. With a cost-to-benefit ratio of 10.5%, PQP’s marketing investment is delivering strong returns.
  • Moving forward, reducing outstanding balances, optimizing marketing efficiency, and improving bad debt collection will be key focus areas to further enhance revenue growth and sales effectiveness.
  • This report provides actionable insights into the efficiency of PQP’s sales cycle, ensuring that every marketing dollar spent generates measurable returns

Interactive Dashboard: Below, you can explore the interactive Power BI dashboard to test the full functionality and experience the KPIs, filters, and visualizations in real time.