Commission Reporting System – Edge Point Technology

Overview

Client: Cybersecurity company selling hardware & software solutions, technical support, and staffaugmentation through a field sales organization. Commissions are calculated on profit (margin) and paid only after full customer payment.

Situation

Key data lived in multiple subsystems of the client’s Costpoint ERP: order entry (what sold and by whom), purchase orders (actual cost), ERP margin, and cash receipts (paid status). Manual reconciliation delayed payouts and introduced risk.

Objectives

Centralize data needed for profit-based commission calculations.
Enforce the paid-in-full rule before commissions are recognized.
Deliver accurate, monthly per-rep reports for approval and AP processing.

Approach

Power Query Integration: Extracted from Costpoint (order entry, PO, ERP, cash receipts) and merged with the commission rate schedule by rep
Margin Computation: Matched sold units to procurement costs to compute per-order profit.
Business Rules: Applied rep-specific commission rates; enforced paid-status gate prior to payout.
Monthly Reporting: Generated approval-ready summaries for each rep with drill-through to transactions.

Days → Hours

Close cycle acceleration

↑ Accuracy

Profit & paid-status logic

Faster pay

Quicker approvals & Ap vouchering

Tech stack: Excel (Power Query), Costpoint ERP data, commission rate tables, CSV/Excel outputs for AP vouchering. Optional VBA for one-click refresh.

Process Flow

From source system to approved commission check.

Source System

Business Rules

Transform & Match

Outputs

Order Entry → Product, Rep, Sale Price
Purchase Orders → Unit Cost per item
ERP Merge (Power Query) → Match Sales ↔ Costs; compute profit
Cash Receipts → Paid-in-Full Gate
Commission Rules → Apply Rep Schedules to Profit
Monthly Reports → Per-Rep Summary for Approval
Accounts Payable → Voucher & Check Issuance

Highlights

Single source of truth for commission eligibility.
Auditability from raw transactions to payout.