# Thread: What-If Analysis Data Table

1. ## 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

2. ## 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. 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

4. 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. 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. 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

7. 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. And there you have it! Not an accountant, just a computer guy.

9. And I am neither an accountant, nor a computer guy. I am a chemist

Steve

10. We need chemists.

zeddy
(just kidding)

11. Are you kidding that "We need chemists" or that you are "Not so sure about accountants" or both?

Steve

12. 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. 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. 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. what's the chemical symbol for the element of surprise
Ah

Steve

Page 1 of 2 12 Last

#### Posting Permissions

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