Hi,
TIA for the help!
I have a table that I want to use to calculate commissions for client Life Insurance & annuity products. The table can increase or decrease depending on the number of cases on a given month. So on my table for Life Ins. I can ask it to add a whole column and then multiply it times a payout rate which is always fixed. So no manner how many rows I add or delete it calculates just fine. on the Annuities before it gets calculated times the payout rate it has to go threw a percentage of total dollar amount X the payout rate. the problem is that percentage varies so its not fixed and when I add or remove more rows it throws it off and wont calculate new rows.
TIA for the help!
I have a table that I want to use to calculate commissions for client Life Insurance & annuity products. The table can increase or decrease depending on the number of cases on a given month. So on my table for Life Ins. I can ask it to add a whole column and then multiply it times a payout rate which is always fixed. So no manner how many rows I add or delete it calculates just fine. on the Annuities before it gets calculated times the payout rate it has to go threw a percentage of total dollar amount X the payout rate. the problem is that percentage varies so its not fixed and when I add or remove more rows it throws it off and wont calculate new rows.
| New_Opportunities2.xlsx | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 1 | |||||||||||||
| 2 | On The Table | Net GDC | Net AUM | Net Annuity | Rate | Product Line | Carrier | Totals: | |||||
| 3 | On the Table | $0.00 | |||||||||||
| 4 | Hitting This Week | $24,000.00 | |||||||||||
| 5 | AUM | ||||||||||||
| 6 | Underwritting | $ - | |||||||||||
| 7 | FF | ||||||||||||
| 8 | Hitting This Week | Net GDC | Net AUM | Net Annuity | Rate | Product Line | Carrier | Case Open | |||||
| 9 | $10,000.00 | $200,000.00 | 2.00% | Annuities | Closes | ||||||||
| 10 | $5,000.00 | $100,000.00 | 4.00% | Annuities | Submitted | ||||||||
| 11 | $1,000.00 | $300,000.00 | 6.00% | Appointments | |||||||||
| 12 | Underwrting | Net GDC | Product Line | Carrier | |||||||||
| 13 | Commissions: | ||||||||||||
| 14 | On the Table | 0.00 | |||||||||||
| 15 | Hitting This Week | 11,520.00 | |||||||||||
| 16 | AUM | *Pending | |||||||||||
| 17 | Fee Based | 0.00 | |||||||||||
| 18 | Fee Based Planning | Ammount | Total | 11,520.00 | |||||||||
| 19 | |||||||||||||
| 20 | Payout Rate | 48% | |||||||||||
| 21 | |||||||||||||
| 22 | |||||||||||||
Numbers | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J3 | J3 | =SUM('Hidden Sheet'!B20+'Hidden Sheet'!B22) |
| J4 | J4 | =SUM('Hidden Sheet'!B15+'Hidden Sheet'!B17) |
| J6 | J6 | =SUM(Table1[Net GDC]) |
| J14 | J14 | =SUM('Hidden Sheet'!B20+'Hidden Sheet'!B22)*Numbers!J20 |
| J15 | J15 | =SUM('Hidden Sheet'!B15+'Hidden Sheet'!B17)*Numbers!J20 |
| J17 | J17 | =SUM(Table1113[Ammount]) |
| J18 | J18 | =SUM(J14:J17) |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| B3:B7 | Whole number | between -1000000000 and 1000000000 |
| C3:E7 | Whole number | between -922337203685477 and 922337203685477 |
| F3:F7 | List | ='Hidden Sheet'!$A$1:$A$10 |
| B9:B11 | Whole number | between -1000000000 and 1000000000 |
| C9:E11 | Whole number | between -922337203685477 and 922337203685477 |
| F9:F11 | List | ='Hidden Sheet'!$A$1:$A$10 |
| C13:C17 | List | ='Hidden Sheet'!$E$1:$E$4 |
| New_Opportunities2.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Life | 1.Case Open/Case Prep | 100% | ST | Life | |||
| 2 | DI | 2.Needs Analysis | 75% | LT | DI | |||
| 3 | LTC | 3.Closing | 50% | LTC | ||||
| 4 | Annuities | 4.Application(s) | 10% | Multiple | ||||
| 5 | Fee-Based Planning | 5.Signature(s) | ||||||
| 6 | P&C | |||||||
| 7 | Investment | |||||||
| 8 | Advisory | |||||||
| 9 | Brokerage | |||||||
| 10 | Multiple | |||||||
| 11 | ||||||||
| 12 | ||||||||
| 13 | ||||||||
| 14 | Hitting this Week | |||||||
| 15 | Net GDC | 16000 | ||||||
| 16 | Net AUM | 0 | ||||||
| 17 | Net Annuity | $8,000.00 | ||||||
| 18 | ||||||||
| 19 | On the Table | |||||||
| 20 | Net GDC | $0.00 | ||||||
| 21 | Net AUM | $0.00 | ||||||
| 22 | Net Annuity | $0.00 | ||||||
| 23 | ||||||||
Hidden Sheet | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B15 | B15 | =SUM(Table410[Net GDC]) |
| B16 | B16 | =SUM(Table410[Net AUM]) |
| B17 | B17 | =SUM((Numbers!D9*Numbers!E9)+(Numbers!D10*Numbers!E10)) |
| B20 | B20 | =SUM(Table39[Net GDC]) |
| B21 | B21 | =SUM(Table39[Net AUM]) |
| B22 | B22 | =SUM(Table39[Net Annuity])*0.04 |
