Compensation Geek, hero of the Recruitment team !

Compensation Geek, hero of the Recruitment team

Imagine the situation : your colleague in Recruitment is looking to prepare the package offer for a candidate.

This candidate is already working in the same GCC country as your company, but their pay is composed differently than yours. Their current package is composed of basic and a single living allowance, as well as a sales incentive. Your package is typically composed of basic, housing, transportation as well as a (different) sales incentive.

The colleague is struggling to establish the range of salary that your company would deem appropriate for this candidate. You normally try to hire within 8 to 12% max of the candidate current package, if that fits with your salary range.

So what do you do ?

Of course, this is not the first time this kind of request is coming your way, so you have prepared your secret weapon: an Excel sheet that you use to establish, in an instant, the “ideal” offer for the candidate.

The sheet is very simple. You have built in the different components of pay to come to the overall gross on-target package : basic + housing + transportation + sales incentive = total pay

You then easily calculate the pay level you want to target, based on the candidate’s current gross + 8% or 12% in our example. Let’s say the amount is 26, 782.

You position your mouse on the cell displaying total pay, and then select Data> What-If Analysis > Goal Seek.

In the pop-up wizard, you enter the following :

  • Set cell “B5” (the cell with the end result – here, total pay of 26 782)
  • To value : “26782”
  • By changing cell : click on “B1” (the cell with basic pay – which you want to find out)

Click OK and magic Excel tells you it found a solution for a target value of 26, 782 :-).

Click OK again and you can quietly announce to your colleague that the basic pay should be at 15, 916 as the minimum value (current gross + 8%).

Fifteen seconds and you the Compensation Geek are a hero in the eyes of the recruitment team !

Now, the Goal Seek function works just as well if you use your variable in multiple cells, for example if housing allowance is not a fixed number but a % of basic pay.

For example :

  • Housing = 25% of basic
  • Transport = 200 + 10%of basic
  • Sales incentive = 40% of (basic + housing).

As you will see below, Goal Seek will easily identify what the target basic pay should be even if the variable is used in multiple cells (email readers may have to click through) :


Goal Seek wizard - for compensation purposes


 Goal seek results for compensation purposes


I have used Goal Seek for many years, for reverse-engineering :

  • basic pay, on-target bonus, maximum or minimum incentive,
  • or for calculating necessary voluntary contributions in order to reach certain levels of income in a savings plan
  • or even for managing international mobility when making Net-to-Net calculations across countries (what is the current theoretical net income of the employee in the UK, (maybe) apply some cost-of-living differential, and then figure out what the gross salary in, say, Italy should be in order to maintain the same standard of living)
  • etc

I hope you enjoyed this quick tip and will use as often as I do !


Related posts :

Print Friendly, PDF & Email


  1. […] Compensation Geek, hero of the Recruitment team ! […]

Speak Your Mind