The Challenge

Snake Corp. needed a clear and scalable way to calculate sales commissions using CRM data across multiple dimensions—target achievement, account type (with “boosted” MRR logic), and rep-specific monthly targets. Their goal was to fairly compensate reps, analyze payouts by team and region, and consider incentive structure changes—without increasing total payout costs.

My Approach

Using Excel and the provided CRM-like data, I:

• Enriched the raw data by joining tables to include sales rep names and account sectors for each deal using the XLOOKUP function.
• Applied business rules to calculate a boosted MRR per deal based on account type (e.g., 1.2x for specialized centers, 1.5x for hospital centers) using IFS statements.
• Filtered and aggregated deal data to calculate each rep’s February MRR performance using SUMIFS with date logic and deal type/status conditions.
• Pulled monthly targets from a separate sheet using SUMIFS and aligned them to the performance data.
• Calculated commission payouts based on tiered achievement brackets using IFS statements and clearly defined thresholds.
• Sorted and cleaned the dashboard for easy presentation and analysis.
• Built pivot tables to summarize total and average commissions by country and team, along with average target achievement rates.
• Proposed incentive strategy adjustments to better reward top performers without increasing the overall payout budget—e.g., reallocating bonuses from underperformers.

The Outcome

My analysis produced a clean, automated system for calculating commission payouts that could easily scale across months and geographies. The deliverable demonstrated:

• Mastery of Excel functions including SUMIFS, XLOOKUP, IFS, and pivot tables.
• A deep understanding of sales performance metrics and CRM-style data.
• Strategic thinking around sales incentive structures.

The accuracy, clarity, and thoughtfulness of the final submission helped me secure the role—showcasing both technical and business acumen.