The following imports are available to utilize Mercer data:

Mercer Cost of Living Index Import
Mercer Home Housing Norm Percentages

Mercer Host Country Housing Cost Import

Mercer Index Related Spendable Percentages
Mercer Index Related Spendable Values *See note below

Mercer International Spendable Tier 2 Values

Mercer Hypothetical Tax Import

Mercer Quality of Living Index Import

These imports can be accessed from the Company Menu in the Processes section under Import Data.

Once the imports are complete you will see the status change to Processed. You can then proceed to using this data in Compensation Worksheets or to view the Compensation Worksheet Data Provider Data you imported.

​* The Mercer Index Related Spendable Values import is shipped as inactive and will not show in the list of System Imports on the Company Imports screen without first activating the import under Company Configuration/ Import Settings. Documentation for using the Mercer Index Related Spendable Values is excluded from this User Manual until support is provided for the calculation.

Importing Mercer Cost of Living Index Data

Click on the Process link next to Mercer Cost of Living Index Import to import the cost of living and exchange rate data from the Mercer File.

Browse to the appropriate file and then click on the Upload File or Save button. When the file has uploaded you will notice a View Flat File link underneath the Failure Condition then you can click on the Process Import button.

Importing Mercer Home Housing Norm Percentages Data

Click on the Process link next to Mercer Home Housing Norm Percentages to import the home housing norm data from the Mercer File.

There is one parameter for this import called Mercer Effective Date. This parameter should be the effective date of the data. Mercer typically provides this file twice a year, in May and November. An example effective date would be May 1, 2010.

Browse to the appropriate file and then click on the Upload File or Save button. When the file has uploaded you will notice a View Flat File link underneath the Failure Condition then you can click on the Process Import button.

This file format will have a M+5 column that will always be NULL per Mercer documentation. This column will not be imported into into Equus Platform™ but should be included on the file.

Importing Mercer Host Country Housing Cost Data

Click on the Process link next to Mercer Host Country Housing Cost to import the host housing cost data from the Mercer File.

Browse to the appropriate file and then click on the Upload File or Save button. When the file has uploaded you will notice a View Flat File link underneath the Failure Condition then you can click on the Process Import button.

Importing Mercer Index Related Spendable Percentages Data

Click on the Process link next to Mercer Index Related Spendable Percentages to import the spendable data from the Mercer File.

There is one parameter for this import called Mercer Effective Date. This parameter should be the effective date of the data. Mercer typically provides this file twice a year, in May and November. An example effective date would be May 1, 2010.

Browse to the appropriate file and then click on the Upload File or Save button. When the file has uploaded you will notice a View Flat File link underneath the Failure Condition then you can click on the Process Import button.

This file format will have a M+5 column that will always be NULL per Mercer documentation. This column will not be imported into into Equus Platform™ but should be included on the file.

Importing Mercer International Spendable Tier 2 Values Data

Click on the Process link next to Mercer International Spendable Tier 2 Values to import the spendable data from the Mercer File.

There is one parameter for this import called Mercer Effective Date. This parameter should be the effective date of the data. Mercer typically provides this file twice a year, in May and November. An example effective date would be May 1, 2010.

Browse to the appropriate file and then click on the Upload File or Save button. When the file has uploaded you will notice a View Flat File link underneath the Failure Condition then you can click on the Process Import button.

This file format will have a M+5 column that will always be NULL per Mercer documentation. This column will not be imported into into Equus Platform™ but should be included on the file.

Importing Mercer Hypothetical Tax Data

Click on the Process link next to Mercer Hypothetical Tax Import to import the hypothetical tax data from the Mercer File.

There is one parameter for this import called Mercer Effective Date. This parameter should be the effective date of the data. Mercer typically provides this file twice a year, in May and November. An example effective date would be May 1, 2010.

Browse to the appropriate file and then click on the Upload File or Save button. When the file has uploaded you will notice a View Flat File link underneath the Failure Condition then you can click on the Process Import button.

Importing Mercer Quality of Living Index Import Data

Click on the Process link next to Mercer Quality of Living Index Import to import the spendable data from the Mercer File.

Browse to the appropriate file and then click on the Upload File or Save button. When the file has uploaded you will notice a View Flat File link underneath the Failure Condition then you can click on the Process Import button.

Important Notes Regarding the Mercer Import

The Compensation Worksheet Data Provider Data can be viewed on the Compensation Worksheet Data Provider Data screen that is accessed from the Company Menu under Company Configuration.
​
To view the Mercer data select Mercer as the Data Provider.
​

Viewing the Compensation Worksheet Data Provider Data

As of Version 4.15, no Mercer data is available to be viewed. This feature will be released in a future version.

Set Effective Data Override

As of Version 4.15, no Mercer effective dates can be overridden. This feature will be released in a future version.

This section will cover some details related to using the Mercer data.

The following Mercer calculations are available for use:

Mercer Cost of Living Adjustment (COLA)

Mercer Cost of Living Adjustment (COLA) using Fixed Spendable

Mercer Family Allowance
Mercer Home Country Hypothetical Personal Income Tax

Mercer Home Country Hypothetical Social Tax

Mercer Home Country Housing Norm

Mercer Host Country Housing Cost

Mercer Index Related Spendable-Percentage of Gross Salary
Mercer International Spendable
Mercer Quality of Living Allowance
Mercer Quality of Living Allowance Net on Salary

Mercer Percent of Net Salary A*

Mercer Percent of Net Salary B*

*A or B can be renamed to a specific type of calculation. IE – Mobility Premium

Each of the above are provided as a System Compensation Worksheet Dictionary Line Item.

Mercer Cost of Living Adjustment (COLA)

The Mercer Cost of Living Adjustment calculation requires the following imports:

Mercer Index Related Spendable Percentages

or Mercer International Spendable Tier 2 Values

Mercer Cost of Living Index Import

The Mercer Cost of Living Adjustment calculation has the following formula:

(Spendable Amount * [(Mercer Cost of Living Index – 100) / 100]) / Number of Pay Periods
​
The Mercer Cost of Living Index is looked up in the Mercer data tables. It looks up the correct value for the given index type, home and host location, and index exclusion.

The Mercer Cost of Living Adjustment calculation requires the following Equus Platform™ inputs:

From Equus Platform™ Comp Worksheet screen:

Home Country

Host Country

Home Country Currency

Gross Salary

Home Family Size when using the COLA calculation with Mercer International Spendable or Host Family Size when using the COLA calculation with Mercer Index Related Spendable. (See Family Size Mapping to Mercer Data section for further explanation)

From Equus Platform™ Comp Worksheet Line Item screen:

Spendable Type

Spendable Region/City

From City/To City/Index Type

Index Exclusion

The Spendable Type, Spendable Region/City, From City/To City/Index Type, and Index Exclusion are available on the line item screen in the Mercer Inputs section when a Mercer Cost of Living Adjustment line item is selected and saved.

The COLA calculation will calculate the Spendable Amount based on the inputs within the Mercer Cost of Living Adjustment line item, regardless if a Spendable line item exists on the balance sheet or not; UNLESS a Spendable line item exists and is set with a Calculation Method of Fixed Amount in Home Currency, in that case the fixed amount will be used in the COLA calculation.

The “Allow Negative G&S Differential for Mercer, ORC & AIRINC compensation calculations” System Preference will default to only producing a COLA calculation when the COL index is greater than 100; it will set the COLA to 0 if the calculated amount is less than 0. If you would like a negative COLA to be calculated this preference can be changed to Yes. This preference is used by comp worksheets & cost estimates.

Mercer Cost of Living Adjustment supports the use of the “Use Max Cap if Salary Range Exceeds the Max Range Mercer Salary-Based Calculations” system preference. Setting this preference to “Yes” will use the Mercer Index Related Spendable Percentages or Mercer International Spendable Tier 2 values for the maximum salary range for calculations that contain a salary beyond the maximum salary range provided.

Mercer Cost of Living Adjustment (COLA) using Fixed Spendable

Note: This calculation is currently only available in the Tax Cost Estimate feature.

The Mercer Cost of Living Adjustment calculation using Fixed Spendable requires the following imports:

Mercer Cost of Living Index Import

The Mercer Cost of Living Adjustment calculation has the following formula:

(Fixed Spendable Amount * [(Mercer Cost of Living Index – 100) / 100]) / Number of Pay Periods
​
The Mercer Cost of Living Index is looked up in the Mercer data tables. It looks up the correct value for the given index type, home and host location, and index exclusion.

The Mercer Cost of Living Adjustment calculation requires the following Equus Platform™ inputs:

From Equus Platform™ Comp Worksheet screen:

Home Country

Host Country

Home Country Currency

Home Family Size when using the COLA calculation with Mercer International Spendable or Host Family Size when using the COLA calculation with Mercer Index Related Spendable. (See Family Size Mapping to Mercer Data section for further explanation)

From Equus Platform™ Calculation Inputs Line Item screen:

Fixed Spendable Amount

From City/To City/Index Type

Index Exclusion

The Fixed Spendable Amount, From City/To City/Index Type, and Index Exclusion are available on the line item screen in the Mercer Inputs section when a Mercer Cost of Living Adjustment line item is selected and saved.

Mercer Family Allowance

The Mercer Family Allowance calculation requires the following imports:

Mercer Hypothetical Tax Import

The Mercer Family Allowance calculation has the following formula:

Mercer Annual Family Allowance Value / Number of Pay Periods

The Mercer Annual Family Allowance Value is looked up in the Mercer data tables. It looks up the correct value for the given gross salary range in home currency and home family size.

The Mercer Family Allowance calculation requires the following inputs:

From Equus Platform™ Comp Worksheet screen:

Home Country

Home Country Currency

Gross Salary

Home Family Size (see Family Size Mapping to Mercer Data section for further explanation)

From Equus Platform™ Comp Worksheet Line Item screen:

Hypo Tax Region

The Hypo Tax Region is available on the line item screen in the Mercer Inputs section when a Mercer Family Allowance line item is selected and saved.

Mercer Home Country Hypothetical Personal Income Tax
The Mercer Home Country Hypothetical Personal Income Tax calculation requires the following import:

Mercer Hypothetical Tax Import

The Mercer Home Country Hypothetical Personal Income Tax calculation has the following formula:

(Gross Salary * [Mercer Hypothetical Personal Income Tax Percent / 100]) / Number of Pay Periods
​
The Mercer Hypothetical Personal Income Tax Percent is looked up in the Mercer data tables. It looks up the correct value for the given gross salary range in home currency and home family size.

The Mercer Home Country Hypothetical Personal Income Tax calculation requires the following Equus Platform™ inputs:

From Equus Platform™ Comp Worksheet screen:

Home Country

Home Country Currency

Gross Salary

Home Family Size (see Family Size Mapping to Mercer Data section for further explanation)

From Equus Platform™ Comp Worksheet Line Item screen:

Hypo Tax Region

The Hypo Tax Region is available on the line item screen in the Mercer Inputs section when a Mercer Home Country Hypothetical Personal Income Tax line item is selected and saved.

Mercer Home Country Hypothetical Social Tax
The Mercer Home Country Hypothetical Social Tax calculation requires the following import:

Mercer Hypothetical Tax Import

The Mercer Home Country Hypothetical Social Tax calculation has the following formula:

(Gross Salary * [Mercer Hypothetical Social Tax Percent / 100]) / Number of Pay Periods
​
The Mercer Index Hypothetical Social Tax Percent is looked up in the Mercer data tables. It looks up the correct value for the given gross salary range in home currency and home family size.

The Mercer Home Country Hypothetical Social Tax calculation requires the following Equus Platform™ inputs:

From Equus Platform™ Comp Worksheet screen:

Home Country

Home Country Currency

Gross Salary

Home Family Size (see Family Size Mapping to Mercer Data section for further explanation)

From Equus Platform™ Comp Worksheet Line Item screen:

Hypo Tax Region

The Hypo Tax Region is available on the line item screen in the Mercer Inputs section when a Mercer Home Country Hypothetical Social Tax line item is selected and saved.

Mercer Home Country Housing Norm
The Mercer Home Country Housing Norm calculation requires the following import:

Mercer Home Housing Norm Percentages

The Mercer Home Country Housing Norm calculation has the following formula:

(Gross Salary * [Mercer Home Country Housing Norm Percent / 100]) / Number of Pay Periods
​
The Mercer Home Country Housing Norm Percent is looked up in the Mercer data tables. It looks up the correct value for the given gross salary range in home currency and host family size.

The Mercer Home Country Housing Norm calculation requires the following Equus Platform™ inputs:

From Equus Platform™ Comp Worksheet screen:

Home Country

Home Country Currency

Gross Salary

Host Family Size (see Family Size Mapping to Mercer Data section for further explanation)

From Equus Platform™ Comp Worksheet Line Item screen:

Home Housing Norm Region

The Home Housing Norm Region is available on the line item screen in the Mercer Inputs section when a Mercer Index Related Spendable Percentages line item is selected and saved.

Mercer Home Housing Norm supports the use of the “Use Max Cap if Salary Range Exceeds the Max Range Mercer Salary-Based Calculations” system preference. Setting this preference to “Yes” will use the Mercer Home Housing Norm Percentages values for the maximum salary range for calculations that contain a salary beyond the maximum salary range provided.

Mercer Host Country Housing Cost
The Mercer Host Country Housing Cost calculation requires the following import:

Mercer Host Country Housing Cost

The Mercer Host Country Housing Cost calculation has the following formula:

([Mercer Host Housing Type Value * 12]) / Number of Pay Periods
​
The Mercer Host Housing Type Value is looked up in the Mercer data tables. It looks up the correct value for the given host country, host city, and host housing type.

The Mercer host housing costs are based on monthly rental costs in host dollars. The amount lookup up in the Mercer data tables will be multiplied by 12 and converted to home dollars using the Comp Worksheet’s exchange rate in order to calculate the annual host housing cost in home dollars. The annual host housing costs will then be divided by the number of pay periods to calculate the pay period host housing costs in home dollars.

The Mercer Host Country Housing Cost calculation requires the following Equus Platform™ inputs:

From Equus Platform™ Comp Worksheet screen:

Host Country

Host Country Currency

From Equus Platform™ Comp Worksheet Line Item screen:

City

Housing Type

The City and Housing Type are available on the line item screen in the Mercer Inputs section when a Mercer Host Country Housing Cost line item is selected and saved.

Mercer Index Related Spendable-Percentage of Gross Salary
The Mercer Index Related Spendable-Percentage of Gross Salary calculation requires the following import:

Mercer Index Related Spendable Percentages

The Mercer Index Related Spendable-Percentage of Gross Salary calculation has the following formula:

(Gross Salary * [Mercer Index Related Spendable Percent / 100]) / Number of Pay Periods
​
The Mercer Index Related Spendable Percent is looked up in the Mercer data tables. It looks up the correct value for the given gross salary range in home dollars and host family size.

The Mercer Index Related Spendable-Percentage of Gross Salary calculation requires the following Equus Platform™ inputs:

From Equus Platform™ Comp Worksheet screen:

Home Country

Home Country Currency

Gross Salary

Host Family Size (see Family Size Mapping to Mercer Data section for further explanation)

From Equus Platform™ Comp Worksheet Line Item screen:

Spendable Region Name

The Spendable Region is available on the line item screen in the Mercer Inputs section when a Mercer Index Related Spendable Percentages line item is selected and saved.

Mercer Index Related Spendable supports the use of the “Use Max Cap if Salary Range Exceeds the Max Range Mercer Salary-Based Calculations” system preference. Setting this preference to “Yes” will use the Mercer Index Related Spendable Percentages values for the maximum salary range for calculations that contain a salary beyond the maximum salary range provided.

Mercer International Spendable

The Mercer International Spendable calculation requires the following import:

Mercer International Spendable Tier 2 Values

The Mercer International Spendable calculation has the following formula:

Mercer Annual International Spendable Value / Number of Pay Periods

The Mercer Annual International Spendable Value is looked up in the Mercer data tables. It looks up the correct value for the given gross salary range in home dollars and home family size.

The Mercer International Spendable calculation requires the following inputs:

From Equus Platform™ Comp Worksheet screen:

Home Country

Home Country Currency

Gross Salary

Home Family Size (see Family Size Mapping to Mercer Data section for further explanation)

From Equus Platform™ Comp Worksheet Line Item screen:

Spendable City Name

The Spendable City is available on the line item screen in the Mercer Inputs section when a Mercer Index Related Spendable Percentages line item is selected and saved.

Mercer International Spendable supports the use of the “Use Max Cap if Salary Range Exceeds the Max Range Mercer Salary-Based Calculations” system preference. Setting this preference to “Yes” will use the Mercer International Spendable Tier 2 values for the maximum salary range for calculations that contain a salary beyond the maximum salary range provided.

Mercer Quality of Living Allowance

The Mercer Quality of Living Allowance calculation requires the following import:

Mercer Quality of Living Index Import

The Quality of Living Allowance calculation has the following formula:

(Gross Salary * [Mercer Quality of Living Percent / 100]) / Number of Pay Periods
​
The Mercer Quality of Living Percent is looked up in the Mercer data tables. It looks up the index rating for the given home and host location. It then applies the index rating to the Quality of Living Index Grading table to determine the Quality of Living Percent.

The Mercer Quality of Living Allowance calculation requires the following Equus Platform™ inputs:

From Equus Platform™ Comp Worksheet screen:

Home Country

Host Country

Home Country Currency

Gross Salary

From Equus Platform™ Comp Worksheet Line Item screen:

From City/To City

The From City/To City is available on the line item screen in the Mercer Inputs section when a Mercer Quality of Living Adjustment line item is selected and saved.

Mercer Quality of Living Allowance on Net Salary

The Mercer Quality of Living Allowance on Net Salary calculation requires the following imports:

Mercer Quality of Living Index Import

Mercer Hypothetical Tax Import

The Quality of Living Allowance calculation has the following formula:

(Net Salary * [Mercer Quality of Living Percent / 100]) / Number of Pay Periods
​
The Mercer Quality of Living Percent is looked up in the Mercer data tables. It looks up the index rating for the given home and host location. It then applies the index rating to the Quality of Living Index Grading table to determine the Quality of Living Percent.

Net salary is defined as annual base pay minus Mercer Hypothetical Tax minus Mercer Hypothetical Social Tax plus Family Allowance.

The Mercer Quality of Living Allowance on Net Salary calculation requires the following Equus Platform™ inputs:

From Equus Platform™ Comp Worksheet screen:

Home Country

Host Country

Home Country Currency

Gross Salary

From Equus Platform™ Comp Worksheet Line Item screen:

From City/To City

The From City/To City is available on the line item screen in the Mercer Inputs section when a Mercer Quality of Living Adjustment on Net Salary line item is selected and saved.

Mercer Percent of Net Salary A and B

The Mercer Percent of Net Salary A and B calculations require the following import:

Mercer Hypothetical Tax Import

The Mercer Percent of Net Salary A and B calculations have the following formula:

(Net Salary * [Input Percentage / 100]) / Number of Pay Periods
​
The Input Percentage is manually input on the Equus Platform™ Comp Worksheet Line Item screen.

Net salary is defined as annual base pay minus Mercer Hypothetical Tax minus Mercer Hypothetical Social Tax plus Family Allowance.

The Percent of Net Salary A and B calculations requires the following Equus Platform™ inputs:

From Equus Platform™ Comp Worksheet screen:

Home Country

Host Country

Home Country Currency

Gross Salary

From Equus Platform™ Comp Worksheet Line Item screen:

Hypo Tax Region

Percentage %

The Hypo Tax Region is available on the line item screen in the Mercer Inputs section when a Mercer Home Country Hypothetical Social Tax line item is selected and saved.

The Percentage % is available on the line item screen in the Custom Inputs section.

Family Size Mapping to Mercer Data

Mercer classifies family sizes as follows:

Single

Married

Married + 1 Child

Married + 2 Children

Married + 3 Children

Married + 4 Children

In the Equus Platform™ family size is entered in as a number. Here is a legend of how family size will be used:

Family Size = 1 then Single

Family Size = 2 then Married

Family Size = 3 then M+1

Family Size = 4 then M+2

Family Size = 5 then M+3

Family Size = 6+ then M+4

Mercer Data Version Selection

The latest Mercer data is always retrieved even for retro comp worksheets. However, it is import to understand that the ‘latest’ is relative to the calculation period of the Comp Worksheet. Here is an example:

Mercer data imported for November 1, 2008, May 1, 2009, and November 1, 2009.

Jan-09 Comp Worksheet will use November 1, 2008 data

May-09 Comp Worksheet will use May 1, 2009 data

Nov-09 Comp Worksheet will use November 1, 2009 data

May-09 Comp Worksheet Retro created December 1, 2009 will use May 1, 2009 data