Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    What-If Analysis Data Table

    Hello!

    I'm in need of an Excel tool that will show me different Pay Grade %'s at different levels of income. If attached an Excel file to show my example. I'm assuming I'd use the What-If Analysis Data Table... unless someone has a better idea.

    In my example, if we achieve $4,000,000 of profit, then we would pay out $400,000 in bonuses to the 3 employees. What would the 3 different pay grades need to be in order to pay out as close to the $400,000 as possible (without going over). Keep in mind that PG2 = PG1 x 2 and PG3 = PG1 X 4. To complicate it further, all 3 employees make a different salary, and their bonuses are calculated by multiplying their salary x their pay grade % in the chart.

    Hope this makes sense... in reality, there are hundreds of employees, not just 3.

    Thanks!
    Lana
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts

    Using Goal seeking

    LJM,

    Yes, goal seeking was easy to use in this example. Using the pay grade ratio of:

    Jack=x
    Jill=2x
    Jane=4x
    total 7x

    seek.png

    Using a bonus pool of $400,000= 7x ; $57,143= x

    Jack = $57,143 Jill = $114,286 Jane = $228,572

    To use goal seeking for this Bonus Pool for Jack, (make sure M6 is blank before starting )
    1. Select cell N6
    2. Data> What If Analysis> Goal SeeK...
    3. N6 will be in the Set Cell
    4. To value: enter 57143
    5. By changing cell: enter $M$6
    6. Click OK twice
    7. Bonus % will be calculated into cell M6

    seek2.png

    Repeat this procedure for Jill and Jane using their bonus amount. Then repeat for every Bonus pool value.

    HTH,
    Maud

    Note: These values are for Goal seeking demo only. Please refer to Financial Specialist

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In your 400,000 example I get 148% for PG1, 296% for PG2, and 593% for PG3. You give the employees huge bonuses!

    There is no need for a solver, it can be solved directly.
    Bonus1 = Salary1 * PG1
    Bonus2 = Salary2 * PG2
    Bonus2 = Salary3 * PG3

    But we know:
    PG2=2*PG1
    PG3=4*PG1

    Thus:
    Bonus1 = Salary1 * PG1
    Bonus2 = Salary2 * PG1*2
    Bonus2 = Salary3 * PG1*4
    Total Bonus = Bonus1+Bonus2+Bonus3
    Total Bonus = Salary1 * PG1 + Salary2 * PG1*2 + Salary3 * PG1*4
    Total Bonus = PG1 * (Salary1 * 1 + Salary2 * 2 + Salary3 * 4)
    Total Bonus = PG1 * SUMPRODUCT({Salary1,Salary2,Salary3},{1,2,4})
    Therefore:
    PG1 = Total Bonus / SUMPRODUCT({Salary1,Salary2,Salary3},{1,2,4})

    The attached spreadsheet fills in the table and also does the individual calculation. With more than 3 employees, you can replace the SalaryX with the "TotalSalaryX", that is the sum of all employees making that particular paygrade.

    Steve
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Maudibe,
    I think you need to recheck your calculations. Your PG2s are not equal to 2* PG1 and your PG3s are not equal to 4*PG1, which seemed to be a requirement.

    Steve

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Steve,

    I think I did take the bonus ratios into consideration as I plugged them into goal seek.

    Jack = $57,143 Jill = $114,286 Jane = $228,572

    The final percentages take their salary into consideration which may be the reason they do not end up in multiples of 2 as you suggest. Even though the amounts paid out total the bonus bonus pool and the percentages sound a bit more realistic than the percentages that you calculated, I am sure you are more knowledgeable in this than I will ever be, so I will succeed to your results.

    The point made is that goal seeking can be used, whether or not I plugged in the correct numbers is a different story.

    Thanks,
    Maud

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You have the BONUSES be factored Bonus2 = 2*Bonus1, Bonus3 = 4*Bonus3, when the PGs are supposed to be factored. With the PG higher and the salary higher the Bonuses are also even higher. The bonuses come to 1 : 3.5 : 9.

    Your percentages are not that different than mine. You reported 286%, 327%, and 508% which are the same order of magnitude as mine: 148% for PG1, 296% for PG2, and 593% for PG3. My point is that my percentages are in the ratios 1,2,4 as I beleive was requested of the OP. The percentages are so high since the Bonus is 4-times (400%) their total annual salary of 100,000.

    While Goal seek can be used, it must be set up correctly and I believe your setup is incorrect. To set it up change N9 to the formula:
    =SUM(N6:N8)

    Then in M6 enter:
    =C3
    Then in M7 enter:
    =D3
    Then in M8 enter:
    =E3

    Now set the GoalSeek to change C3 so that N9 equals 400,000

    But GoalSeek is very cumbersome if you are going to fill in a table. Better to calculate them with formulas. They are "live"

    Steve
    Last edited by sdckapr; 2013-08-23 at 10:14.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The simple generic formula for the solution is (does not matter about the number of employees):
    PG1% = Total Bonus / ((TotalSalariesPG1)+2*(TotalSalariesPG2)+4*(TotalS alariesPG3))
    PG2% = 2* PG1%
    PG3% = 4* PG1%

    Steve

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    And there you have it! Not an accountant, just a computer guy.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    And I am neither an accountant, nor a computer guy. I am a chemist

    Steve

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    We need chemists.
    Not so sure about accountants.


    zeddy
    (just kidding)

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Are you kidding that "We need chemists" or that you are "Not so sure about accountants" or both?

    Steve

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Steve

    I wasn't expecting that reaction.
    We need chemists.
    They have all the solutions.
    And if you're not part of the solution, you're part of the precipitate.
    The most important lesson I learnt in Chem Lab was 'never lick the spoon'.

    zeddy
    (..The name's Bond. Ionic Bond. Taken, not shared).
    ..that's enough of my favourite chem jokes for now. It takes alkynes of people.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Nobody expects that reaction! Our chief tool is a reaction... reactions and solutions... solutions and reactions... our two tools are reactions and solutions...and ruthless efficiency.... Our three weapons are reactions, solutions, and ruthless efficiency...and an almost fanatical devotion to the Periodic Table.... Our four...no... amongst our tools.... are such diverse elements as reactions, solutions... Oh I forgot, moles ...

    Perhaps I should start again....

    Steve
    PS in addition to not licking the spoon, another important lesson, it is far more important to wash your hands BEFORE going to the bathroom rather than after...

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    You caught me out there.
    So, what's the chemical symbol for the element of surprise?

    zeddy
    (as a maths person, I'm putting H20 cubed in my drink)

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    what's the chemical symbol for the element of surprise
    Ah

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •