| Active/Inactive | Customer Last Name | Customer First Name | Insurance Co | PolicyType | Policy # | Written Date | Effective Date | Renewal Date | Policy Pays Every | Premium Amount | Net Commission | |
| Active | Doe | John | Ins ABC | Auto | 123 | 02/02/2013 | 02/07/2013 | 08/07/2013 | 6 Months | 500.00 | 60.00 | |
| Active | Smith | David | Ins ABC | Home | 456 | 08/21/2013 | 08/25/2013 | 08/25/2014 | 12 Months | 1000.00 | 150.00 | |
| Active | Jensen | Sarah | Ins GHI | Life-Term | xyz | 02/15/2014 | 02/17/2014 | 02/17/015 | Month | 50.00 | 5.00 | |
<tbody>
</tbody>
Hopefully someone out there can help . A friend has asked me to build him a spreadsheet that will help confirm his commission checks he receives from an insurance broker are correct. He's paid through a broker each month and receives commission on the various policies s he has signed for various insurance companies. I've already built him a spreadsheet that has the columns shown in the attached table that will compute the accurate commission amount based on numerous variables (I have vlookup formulas that reference a table on sheet 2 that take into consideration the insurance company, policy type, premium amount, etc., and then deliver the correct dollar amount in the last column "Net Commision". Here's where I'm struggling: I don't know how to get this spreadsheet to report back the policies that will pay out in any given month. As you can see, I've got a column for how often a policy pays out (it's either, monthly, every 6 months or every year and this is based on the date the policy went effective). So for example, based on the data I've provided, John Doe's Auto policy would pay out$ 60 every February and August, David Smith policy would pay out $150 every February and Sarah Jensen's policy will pay $5 every month starting in February 2014. I was thinking it would be nice if there was a cell he could put a month and year in and then just refresh a pivot table that would report back what each insurance company owes for the month and for what policies they owe for. Is this even possible?I'm not completely sure. But here's what I need:
If he "ran" a commission report for February 2013, it would return that Ins ABC owes him $60 for John Doe.
If he ran it for August 2013, it would show that Ins ABC owes him $210 as it would pay out on both John Doe and David Smith. that month.
Lastly, if he ran it for February 2014, it would show Ins ABC owes $60 for John Doe and Ins GHI owes $5 for Sarah Jensen.
If he ran it again for August 2014 it would report the same $210 as it did for August 2013 (he would like to be able to estimate his commission checks for future months if possible)
Please note that If a policy terminates, I've told him to just change the first column to say "Inactive" and then it won't compute into the commission. But now that I write this I realize I need to add some type of termination date column if he's going to run historical reports and have it add/remove the policies based on their effective and term dates, in case he wants to run historical reports to see what he made this Feb vs last Feb.
I will add a "termination date" column right after the "renewal date" column on the original document
So does anyone has any suggestions ? Any help with this would be greatly appreciated!
