Sales Commission Calculator Builder

Prompt

Build a sales commission calculator in Excel for [COMPANY NAME]'s [ROLE: AE / SDR / CSM] compensation plan.

Commission plan details:
- On-target earnings (OTE): [AMOUNT]
- Base salary: [AMOUNT]
- Target variable (commission): [AMOUNT]
- Quota: [AMOUNT]
- Payout structure: [DESCRIBE — % OF REVENUE / FLAT PER DEAL / TIERED]
- Accelerators: [RATE AND THRESHOLD — E.G., 150% AT 100% QUOTA]
- Clawback provisions: [YES/NO — DESCRIBE]
- Pay period: [MONTHLY / QUARTERLY]

**CALCULATOR BUILD**

1. **Input Section** — Rep name, period, quota, deals closed with amount and close date.

2. **Attainment Calculation** — Total bookings ÷ quota = attainment %.

3. **Commission Calculation** — Tiered rate table with VLOOKUP or IFS formula.

4. **Accelerator Logic** — IF/THEN formula for above-quota multipliers.

5. **YTD Tracker** — Running total of earnings vs. OTE for the year.

6. **Scenario Planner** — Slider or input cell showing "what would I earn if I close [X] more deals?"

7. **Manager View** — Summary tab showing all reps, attainment, and commission earned.

Provide all formulas with explanations. Make the model understandable to a sales rep who is not technical.