Hi all,
Thanks for reading and helping me out. I am trying to get a better sense on income/expense management and pipeline revenue for my real estate transactions. I have built the attached sheet to be able to track the status of the deals I am working on and provide insight on potential, pending and earned commissions. I have been able to get everything working except for accurately calculating the split between brokerage vs broker commission from the deal. The split is based around reaching a $ split amount at which point the percentage share changes, it also resets on a given date each year. These parameters are indicated on the calculation page (note these numbers are not accurate for the industry but I am not allowed to accurately explain the exact split per the brokerage).
Where I need help.
On the transaction tab, under the brokerage section. Columns AL-AN are fine as they are just a simple percentage calculation that doesnt change against gross commission. Columns AO-AQ is where I get stuck. These calculations change depending on the selected status in column B, and need to run against the representative dates of the transaction. Ie potential commission is against list date, Pending is against the contract pending date and closed is against the contract close date. Right now row 4 is just a simple calculation using the before split % found on the calculation tab as this transaction won't impact the split amount ($48,025 in this example). Subsequent rows however need to have a split calculation that assesses whether the brokerage split hits that target in a given transaction and then changes the revenue above that split from the before split % to the after split percentage. It also needs to take into account whether the transaction moves past the split date (found in yearly summary page) and if it does reset the split amount.
I'm sure running the calculation three times like this makes it much more difficult but it would provide me with very useful insight on my income based upon transactions closing near the split date.
https://docs.google.com/spreadsheets...it?usp=sharing
Thank you
Thanks for reading and helping me out. I am trying to get a better sense on income/expense management and pipeline revenue for my real estate transactions. I have built the attached sheet to be able to track the status of the deals I am working on and provide insight on potential, pending and earned commissions. I have been able to get everything working except for accurately calculating the split between brokerage vs broker commission from the deal. The split is based around reaching a $ split amount at which point the percentage share changes, it also resets on a given date each year. These parameters are indicated on the calculation page (note these numbers are not accurate for the industry but I am not allowed to accurately explain the exact split per the brokerage).
Where I need help.
On the transaction tab, under the brokerage section. Columns AL-AN are fine as they are just a simple percentage calculation that doesnt change against gross commission. Columns AO-AQ is where I get stuck. These calculations change depending on the selected status in column B, and need to run against the representative dates of the transaction. Ie potential commission is against list date, Pending is against the contract pending date and closed is against the contract close date. Right now row 4 is just a simple calculation using the before split % found on the calculation tab as this transaction won't impact the split amount ($48,025 in this example). Subsequent rows however need to have a split calculation that assesses whether the brokerage split hits that target in a given transaction and then changes the revenue above that split from the before split % to the after split percentage. It also needs to take into account whether the transaction moves past the split date (found in yearly summary page) and if it does reset the split amount.
I'm sure running the calculation three times like this makes it much more difficult but it would provide me with very useful insight on my income based upon transactions closing near the split date.
https://docs.google.com/spreadsheets...it?usp=sharing
Thank you
