Results 1 to 15 of 19
Thread: WhatIf Analysis Data Table

20130822, 20:47 #1
 Join Date
 Jul 2002
 Location
 Ankeny, Iowa, USA
 Posts
 344
 Thanks
 8
 Thanked 0 Times in 0 Posts
WhatIf 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 WhatIf 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

20130822, 22:37 #2
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,752
 Thanks
 129
 Thanked 691 Times in 627 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

20130823, 04:21 #3
 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

20130823, 04:24 #4
 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

20130823, 06:32 #5
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,752
 Thanks
 129
 Thanked 691 Times in 627 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

20130823, 08:58 #6
 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 4times (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"
SteveLast edited by sdckapr; 20130823 at 09:14.

20130823, 09:07 #7
 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

20130823, 09:52 #8
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,752
 Thanks
 129
 Thanked 691 Times in 627 Posts
And there you have it! Not an accountant, just a computer guy.

20130823, 10:17 #9
 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

20130823, 16:02 #10
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,128
 Thanks
 149
 Thanked 573 Times in 545 Posts
We need chemists.
Not so sure about accountants.
zeddy
(just kidding)

20130823, 17:23 #11
 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

20130824, 16:50 #12
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,128
 Thanks
 149
 Thanked 573 Times in 545 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.

20130825, 06:13 #13
 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...

20130825, 08:22 #14
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,128
 Thanks
 149
 Thanked 573 Times in 545 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)

20130825, 12:37 #15
 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
Steve