lesliebelton
New Member
- Joined
- Sep 17, 2013
- Messages
- 1
So I'd like Excel to help me create the following breakdowns, I've been doing it manually (which leads to errors). Tried "if" and "left" and such in Excel and can't seem to get the results I need. Please help
Fields All Calculations are based on:
Sales Price: cost of home
Total % of Commission: pre determined by individual contract with seller
Total Commission: dollar amount based on % of sales price
Agent: each agent is given a different split for the year
spreadsheet desired functions
Agent Commission= their commission % (example, if it's Michelle she get's 85% of the total commission- less any admin fee)
admin fee= only deducted from agent portion of commission (variable value not a %)
Gross Company dollar= the difference between agent commission % and 100%
Net Company dollar= the difference between agent commission % and 100% - less 10% manager override (example if Michelle is the agent, it would be 15% less 10% of the 15%)
Manager Override=10% of the "company dollar" divided by 2 Managers (50% and 50% of 10% of the Company Dollar)
Misc. Credits= deducted from "total commission" prior to any calculations
EXAMPLE OF DESIRED RESULTS
A. Sales Price: 700,000
B. Total % of commission: 2.5%
C. Total Commission: $17,500.00
D. Misc Credits: $1,000(referral fee paid to another brokerage, TAKEN FROM "TOTAL COMMISSION" PRIOR TO CALCS)
E. Agent: Michelle
F. Agent split %: 85% (CONSTANT FOR EACH AGENT THROUGHOUT THE YEAR)
G. Admin Fee: $800.00 (VARIES PER TRANSACTION BASED ON INDIVIDUAL AGREEMENT, DIFFERENT FOR EACH TRANSACTION )
H. Agent Commission: $13,225 (% , TAKEN OFF TOTAL COMMISSION AFTER MISC CREDITS, AND SUBJECT TO "ADMIN FEE DEDUCTION")
I. Net Company Dollar: $1,485 (GROSS COMPANY DOLLAR+ DIFFERENCE BETWEEN AGENT SPLIT % AND 100%= NET COMPANY $, DIFFERENCE BETWEEN AGENT SPLIT % AND 100%, LESS 10% MANAGER OVERRIDES)
J. Manager 1 Override: $82.50 (50% OF 10% OF Gross Company Dollar )
K. Manager 2 Override: $82.50 (50% OF 10% OF Gross Company Dollar )I hope that makes sense
Fields All Calculations are based on:
Sales Price: cost of home
Total % of Commission: pre determined by individual contract with seller
Total Commission: dollar amount based on % of sales price
Agent: each agent is given a different split for the year
spreadsheet desired functions
Agent Commission= their commission % (example, if it's Michelle she get's 85% of the total commission- less any admin fee)
admin fee= only deducted from agent portion of commission (variable value not a %)
Gross Company dollar= the difference between agent commission % and 100%
Net Company dollar= the difference between agent commission % and 100% - less 10% manager override (example if Michelle is the agent, it would be 15% less 10% of the 15%)
Manager Override=10% of the "company dollar" divided by 2 Managers (50% and 50% of 10% of the Company Dollar)
Misc. Credits= deducted from "total commission" prior to any calculations
EXAMPLE OF DESIRED RESULTS
A. Sales Price: 700,000
B. Total % of commission: 2.5%
C. Total Commission: $17,500.00
D. Misc Credits: $1,000(referral fee paid to another brokerage, TAKEN FROM "TOTAL COMMISSION" PRIOR TO CALCS)
E. Agent: Michelle
F. Agent split %: 85% (CONSTANT FOR EACH AGENT THROUGHOUT THE YEAR)
G. Admin Fee: $800.00 (VARIES PER TRANSACTION BASED ON INDIVIDUAL AGREEMENT, DIFFERENT FOR EACH TRANSACTION )
H. Agent Commission: $13,225 (% , TAKEN OFF TOTAL COMMISSION AFTER MISC CREDITS, AND SUBJECT TO "ADMIN FEE DEDUCTION")
I. Net Company Dollar: $1,485 (GROSS COMPANY DOLLAR+ DIFFERENCE BETWEEN AGENT SPLIT % AND 100%= NET COMPANY $, DIFFERENCE BETWEEN AGENT SPLIT % AND 100%, LESS 10% MANAGER OVERRIDES)
J. Manager 1 Override: $82.50 (50% OF 10% OF Gross Company Dollar )
K. Manager 2 Override: $82.50 (50% OF 10% OF Gross Company Dollar )I hope that makes sense
