Payroll Analysis Excel Analysis Workbook by Rabia06

VIEWS: 1,037 PAGES: 17

									                      EOWA Pay Equity Tool

        Payroll Analysis:
Excel Analysis Workbook

                                    March 2005




                   To be read in conjunction with:
              Payroll Analysis: A 7-Step Approach
      Payroll Analysis: Excel Analysis Instructions
                      The EOWA Pay Equity Tool
    EOWA Payroll Analysis: Excel Analysis Workbook

    Sample Payroll Data

    This worksheet contains hypothetical payroll data which is linked to the two pivot table templates on the
    following worksheets. To conduct a gender equity payroll analysis, make a copy of this file, and replace the
    sample data with payroll data for your own organisation. You can then use the template pivot tables to
    analyse the pay distribution in your organisation.

    Detailed instructions on conducting a payroll analysis and using this workbook are provided in the two
    accompanying documents, Payroll Analysis: A 7-Step Approach and Payroll Analysis: Excel Workbook
    Instructions .
                                             Status              Section
        ID       Gender      Grade ID                                                Section ID   Remuneration
                                           Description          Description
        191            M         4           FULL-TIME          Phone support            8        $        86,173
        957            M         3           FULL-TIME            Recreation             3        $        71,541
       1449            M         4           FULL-TIME          Phone support            8        $        86,173
       1538            M         2           FULL-TIME             Marketing             9        $        61,421
       1724            M         3           FULL-TIME             Marketing             9        $        71,541
       3689            M         3           PART-TIME             Marketing             4        $        74,429
       4855            M         3           FULL-TIME             Marketing             9        $        74,431
       5010            M         3           FULL-TIME             Marketing             9        $        74,431
       7358            F         2           PART-TIME               Other               13       $        61,423
       8516            F         4           FULL-TIME             Marketing             4        $        86,173
       9024            F         3           FULL-TIME             Marketing             4        $        66,117
       11622           M         2           FULL-TIME             Marketing             9        $        61,421
       13463           M         2           FULL-TIME             Marketing             9        $        61,421
       14621           M         4           FULL-TIME          Phone support            8        $        86,173
       15288           M         2           FULL-TIME             Marketing             9        $        61,421
       17825           M         2           FULL-TIME             Marketing             9        $        61,421
       23019           M         1           FULL-TIME             Marketing             9        $        46,068
       23507           F         1           FULL-TIME               Other               13       $        46,068
       27847           M         4           FULL-TIME              Training             5        $        86,173
       28177           M         4           FULL-TIME          Phone support            8        $        86,173
       31291           M         4           FULL-TIME             Marketing             9        $        86,173
       36129           F         3           PART-TIME             Marketing             4        $        74,426
       36307           M         3           FULL-TIME               Other               13       $        71,541
       38660           M         4           PART-TIME             Marketing             4        $        86,176
       38911           M         3           FULL-TIME             Marketing             4        $        66,117
       39624           M         3           FULL-TIME             Marketing             4        $        74,431
       39705           F         3           FULL-TIME            Head Office            1        $        66,117
       39721           M         2           FULL-TIME             Marketing             9        $        61,421
       49875           M         3           FULL-TIME             Marketing             9        $        74,431
       51691           M         3           FULL-TIME          Phone support            8        $        71,541
       52280           F         3           FULL-TIME             Marketing             9        $        71,541
       52450           M         4           FULL-TIME             Marketing             4        $        86,173
       57541           F         3           FULL-TIME               Other               13       $        74,431
       62391           F         4           PART-TIME            Head Office            1        $        86,177
       63827           M         3           FULL-TIME          Phone support            8        $        66,117
       65552           F         2           PART-TIME          Hardware sales           7        $        61,419
       66699           M         3           FULL-TIME             Marketing             9        $        71,541
       68675           M         3           FULL-TIME             Marketing             9        $        71,541
       69795           M         3           FULL-TIME             Marketing             9        $        66,117
       70645           M         3           FULL-TIME            Recreation             3        $        74,431
       80179           M         4           FULL-TIME           Software dev            6        $        97,314
       80225           M         4           FULL-TIME            Head Office            1        $        90,250
       81124           M         4           FULL-TIME             Marketing             9        $       102,238
       81159           M         4           FULL-TIME          Hardware sales           7        $       110,587
       82716           M         4           FULL-TIME               Other               13       $       118,113
       82813           M         4           FULL-TIME          Hardware sales           7        $        96,852
       83119           M         4           FULL-TIME            Recreation             3        $        90,083
       83135           M         4           FULL-TIME          Phone support            8        $       104,658
       83143           M         4           FULL-TIME               Sales               2        $       142,335
       83194           F         4           FULL-TIME             Marketing             4        $       143,998
       83399           M         4           FULL-TIME          Hardware sales           7        $       125,032
       83518           M         4           FULL-TIME             Marketing             9        $        32,585



EOWA Pay Equity Tool                     Payroll Analysis: Excel Analysis Workbook                           Sample Data
                                          Status              Section
        ID       Gender    Grade ID                                               Section ID   Remuneration
                                        Description          Description
       83879           M      4           FULL-TIME            Software dev           6        $     107,134
       83917           M      4           FULL-TIME          Hardware sales           7        $     114,786
       83976           M      4           FULL-TIME           Phone support           8        $      96,825
       83984           M      4           FULL-TIME                Other              13       $     130,914
       83992           M      4           FULL-TIME            Software dev           6        $     126,000
       84115           M      4           FULL-TIME            Software dev           6        $     110,634
       84174           M      4           FULL-TIME              Marketing            4        $      95,655
       84190           M      4           FULL-TIME          Hardware sales           7        $     112,921
       84220           M      4           FULL-TIME          Field operations         12       $      95,667
       84336           M      3           FULL-TIME                Sales              2        $      91,984
       84395           F      3           PART-TIME              Marketing            4        $      88,893
       84441           M      4           FULL-TIME           Phone support           8        $     111,895
       84468           M      4           FULL-TIME              Marketing            4        $     111,042
       84484           M      4           FULL-TIME             Recreation            3        $     125,058
       84492           M      4           FULL-TIME                Other              13       $      84,693
       84506           M      4           FULL-TIME              Marketing            4        $     110,960
       84514           M      4           FULL-TIME           Phone support           8        $     102,429
       84549           M      4           FULL-TIME          Hardware sales           7        $      89,049
       84557           M      4           FULL-TIME                Sales              2        $     145,701
       84581           F      4           FULL-TIME          Field operations         12       $      95,293
       84603           F      4           FULL-TIME             Head Office           1        $     108,274
       84611           M      4           FULL-TIME          Field operations         12       $      98,118
       84638           M      3           FULL-TIME             Head Office           1        $      83,484
       84654           M      4           FULL-TIME          Hardware sales           7        $      95,410
       84662           M      4           FULL-TIME              Marketing            4        $      99,481
       84670           M      4           FULL-TIME              Marketing            4        $      91,471
       84689           M      4           FULL-TIME                Other              13       $      47,408
       84697           M      4           FULL-TIME                Other              13       $     119,128
       84786           M      4           FULL-TIME                Sales              2        $      91,501
       84794           M      4           FULL-TIME            Software dev           6        $      97,789
       84832           M      4           FULL-TIME          Hardware sales           7        $      92,392
       84905           M      4           FULL-TIME           Phone support           8        $      96,333
       84921           M      4           FULL-TIME                Other              13       $     117,957
       84948           M      4           FULL-TIME           Phone support           8        $     105,550
       84956           M      4           FULL-TIME            Software dev           6        $     112,550
       85030           M      4           PART-TIME          Hardware sales           7        $     110,526
       85057           M      4           FULL-TIME              Marketing            4        $     104,073
       85103           M      4           FULL-TIME             Recreation            3        $     110,112
       85111           M      4           FULL-TIME          Hardware sales           7        $      85,255
       85138           M      4           FULL-TIME                Sales              2        $      91,368
       85456           M      4           FULL-TIME              Marketing            4        $      99,534
       85472           M      4           FULL-TIME              Marketing            4        $      97,325
       85480           M      4           FULL-TIME              Marketing            9        $     126,000
       85499           M      4           FULL-TIME          Hardware sales           7        $     120,408
       85529           M      4           FULL-TIME              Marketing            4        $      98,785
       85618           M      4           FULL-TIME              Marketing            9        $      92,611
       85669           M      4           FULL-TIME                Other              13       $     104,622
       85677           M      4           FULL-TIME              Marketing            9        $      93,383
       85707           M      4           FULL-TIME              Marketing            4        $     203,000
       85774           M      4           FULL-TIME              Marketing            9        $     105,000
       85812           M      4           FULL-TIME                Sales              2        $     120,749
       86037           M      4           FULL-TIME          Hardware sales           7        $     113,866
       86118           M      4           FULL-TIME              Marketing            9        $     140,000
       86223           M      4           FULL-TIME           Phone support           8        $     104,513
       86258           F      4           FULL-TIME            Software dev           6        $     175,000
       86282           M      3           FULL-TIME            Software dev           6        $      82,912
       86312           F      4           FULL-TIME            Software dev           6        $      94,549
       91405           M      2           FULL-TIME              Marketing            4        $      61,421
       92959           F      3           FULL-TIME           Phone support           8        $      71,541
       93068           M      4           FULL-TIME           Phone support           8        $      86,173
       93564           M      3           FULL-TIME             Recreation            3        $      74,431
      109282           F      3           FULL-TIME                Other              13       $      66,117
      112283           M      3           FULL-TIME             Recreation            3        $      66,117
      119385           M      3           FULL-TIME              Marketing            9        $      66,117
      120413           F      3           FULL-TIME          Hardware sales           7        $      66,117
      121312           M      2           FULL-TIME               Training            5        $      61,421
      121665           M      2           FULL-TIME             Recreation            3        $      57,446
      124354           F      3           FULL-TIME                Sales              2        $      66,117
      127205           F      4           FULL-TIME              Marketing            9        $      86,173



EOWA Pay Equity Tool                  Payroll Analysis: Excel Analysis Workbook                        Sample Data
                                          Status              Section
        ID       Gender    Grade ID                                               Section ID   Remuneration
                                        Description          Description
      134627           M      2           FULL-TIME             Marketing             9        $      61,421
      136654           M      4           FULL-TIME           Phone support           8        $      86,173
      140279           M      2           FULL-TIME          Field operations         12       $      66,117
      141844           M      3           FULL-TIME             Marketing             4        $      74,431
      143804           M      2           FULL-TIME                Legal              11       $      61,421
      144339           M      3           PART-TIME           Phone support           8        $      71,541
      144436           F      2           FULL-TIME             Marketing             9        $      53,472
      145742           M      3           FULL-TIME           Phone support           8        $      74,431
      150320           M      3           FULL-TIME           Phone support           8        $      71,541
      155632           F      3           FULL-TIME          Hardware sales           7        $      74,431
      155667           M      3           FULL-TIME            Software dev           6        $      66,117
      155861           F      2           FULL-TIME             Marketing             9        $      53,472
      163732           M      2           FULL-TIME           Phone support           8        $      61,421
      165239           F      3           FULL-TIME             Marketing             9        $      71,541
      168793           M      2           FULL-TIME             Recreation            3        $      57,446
      169498           M      3           FULL-TIME                Legal              11       $      66,117
      171344           F      3           FULL-TIME             Marketing             4        $      74,431
      171735           M      2           FULL-TIME          Hardware sales           7        $      57,446
      176745           M      4           FULL-TIME             Marketing             4        $      86,173
      181846           M      4           FULL-TIME             Marketing             9        $      94,347
      184969           M      1           FULL-TIME             Marketing             9        $      39,924
      185302           M      4           FULL-TIME           Phone support           8        $      86,173
      186120           M      3           FULL-TIME           Phone support           8        $      71,541
      187194           F      1           FULL-TIME             Marketing             9        $      39,924
      196371           F      4           FULL-TIME             Marketing             4        $      86,173
      198676           F      2           FULL-TIME                Sales              2        $      61,421
      199206           F      3           PART-TIME             Marketing             4        $      74,426
      199443           M      3           FULL-TIME             Marketing             4        $      71,541
      200352           M      3           FULL-TIME             Marketing             4        $      74,431
      200999           M      3           FULL-TIME             Marketing             4        $      74,431
      202800           F      2           FULL-TIME             Marketing             9        $      53,472
      202908           F      3           PART-TIME           Phone support           8        $      74,429
      203688           F      3           FULL-TIME          Hardware sales           7        $      71,541
      203815           F      3           PART-TIME             Marketing             4        $      74,426
      205117           M      1           FULL-TIME             Marketing             9        $      46,068
      205133           M      4           FULL-TIME           Phone support           8        $      86,173
      205168           F      2           FULL-TIME          Hardware sales           7        $      57,446
      206830           F      1           FULL-TIME                Other              13       $      46,068
      206989           M      1           FULL-TIME             Marketing             9        $      46,068
      207772           M      3           FULL-TIME             Recreation            3        $      74,431
      210552           F      4           FULL-TIME             Marketing             4        $      86,173
      211079           F      3           PART-TIME          Hardware sales           7        $      66,115
      211095           F      3           PART-TIME           Phone support           8        $      71,538
      211931           M      3           FULL-TIME           Phone support           8        $      66,117
      212539           F      3           FULL-TIME           Phone support           8        $      74,431
      213861           M      3           FULL-TIME             Marketing             4        $      71,541
      214981           M      4           FULL-TIME           Phone support           8        $      86,173
      215007           M      3           FULL-TIME           Phone support           8        $      66,117
      217255           F      2           FULL-TIME          Hardware sales           7        $      57,446
      218537           F      3           PART-TIME           Phone support           8        $      66,121
      218553           M      2           FULL-TIME             Recreation            3        $      61,421
      218561           M      1           FULL-TIME             Recreation            3        $      46,068
      218596           F      3           FULL-TIME             Marketing             4        $      71,541
      226548           F      3           FULL-TIME           Phone support           8        $      66,117
      227420           F      3           PART-TIME             Marketing             4        $      66,115
      227943           F      2           FULL-TIME          Hardware sales           7        $      57,446
      228842           M      3           FULL-TIME             Marketing             4        $      74,431
      229202           M      3           FULL-TIME           Phone support           8        $      71,541
      231320           F      3           FULL-TIME           Phone support           8        $      74,431
      231754           M      4           FULL-TIME             Marketing             4        $      86,173
      232440           M      4           FULL-TIME             Recreation            3        $      86,173
      232467           F      1           FULL-TIME             Marketing             9        $      46,068
      232947           M      4           FULL-TIME             Marketing             4        $      86,173
      233056           M      3           FULL-TIME                Sales              2        $      71,541
      234184           M      3           FULL-TIME          Hardware sales           7        $      66,117
      235296           F      3           FULL-TIME          Hardware sales           7        $      74,431
      235326           M      3           PART-TIME           Phone support           8        $      56,566
      235806           M      3           FULL-TIME           Phone support           8        $      74,431
      235962           F      3           FULL-TIME             Marketing             4        $      74,431



EOWA Pay Equity Tool                  Payroll Analysis: Excel Analysis Workbook                        Sample Data
                                          Status              Section
        ID       Gender    Grade ID                                               Section ID   Remuneration
                                        Description          Description
      236071           M      2           FULL-TIME             Marketing             9        $      57,446
      239798           M      3           FULL-TIME             Marketing             9        $      74,431
      241253           F      3           FULL-TIME           Phone support           8        $      74,431
      243396           M      3           FULL-TIME             Recreation            3        $      71,541
      243817           M      4           FULL-TIME             Marketing             4        $      86,173
      244732           M      3           FULL-TIME             Marketing             4        $      74,431
      245623           M      4           FULL-TIME           Phone support           8        $      86,173
      245682           M      3           FULL-TIME             Marketing             9        $      74,431
      247642           M      3           FULL-TIME           Phone support           8        $      74,431
      249947           F      4           FULL-TIME           Phone support           8        $      86,173
      251089           M      4           FULL-TIME           Phone support           8        $      86,173
      251763           M      2           PART-TIME             Marketing             4        $      61,419
      256323           F      3           FULL-TIME             Marketing             4        $      66,117
      256358           M      3           FULL-TIME             Marketing             4        $      74,431
      256412           F      2           FULL-TIME            Software dev           6        $      57,446
      257028           F      3           FULL-TIME             Marketing             4        $      74,431
      258520           F      1           FULL-TIME                Sales              2        $      46,068
      258555           F      3           FULL-TIME            Software dev           6        $      71,541
      258563           M      1           PART-TIME           Phone support           8        $      46,068
      258598           F      1           FULL-TIME            Software dev           6        $      46,068
      258628           M      1           FULL-TIME                Legal              11       $      46,068
      258644           M      2           FULL-TIME             Marketing             9        $      57,446
      258695           F      2           FULL-TIME             Marketing             9        $      53,472
      258725           F      1           FULL-TIME             Marketing             9        $      46,068
      258733           F      2           FULL-TIME                Other              13       $      57,446
      258806           M      3           FULL-TIME          Hardware sales           7        $      74,431
      258814           M      3           FULL-TIME                Legal              11       $      71,541
      258903           M      2           FULL-TIME             Marketing             9        $      53,472
      260339           F      3           FULL-TIME             Marketing             4        $      71,541
      260347           F      3           FULL-TIME             Marketing             4        $      71,541
      261769           F      3           FULL-TIME          Hardware sales           7        $      66,117
      269468           M      2           FULL-TIME          Hardware sales           7        $      57,446
      269646           M      4           FULL-TIME           Phone support           8        $      86,173
      270792           M      3           FULL-TIME             Marketing             4        $      74,431
      270814           M      3           FULL-TIME             Marketing             4        $      74,431
      270830           F      2           FULL-TIME             Marketing             9        $      61,421
      270970           M      3           FULL-TIME                Sales              2        $      74,431
      270997           F      2           FULL-TIME                Sales              2        $      61,421
      271004           M      2           FULL-TIME            Software dev           6        $      57,446
      271012           F      2           FULL-TIME           Phone support           8        $      61,421
      271152           F      3           FULL-TIME          Hardware sales           7        $      66,117
      271195           F      4           FULL-TIME           Phone support           8        $      86,173
      271217           F      4           FULL-TIME                Other              13       $      86,173
      271292           M      1           FULL-TIME          Hardware sales           7        $      46,068
      271314           F      1           FULL-TIME          Hardware sales           7        $      46,068
      271330           F      1           FULL-TIME          Hardware sales           7        $      46,068
      271357           M      1           FULL-TIME                Legal              11       $      46,068
      271365           F      3           FULL-TIME          Hardware sales           7        $      74,431
      275379           M      3           FULL-TIME             Marketing             4        $      74,431
      277991           F      3           FULL-TIME          Hardware sales           7        $      71,541
      282391           M      3           FULL-TIME           Phone support           8        $      74,431
      282529           F      4           PART-TIME           Phone support           8        $      66,117
      282898           F      4           FULL-TIME           Phone support           8        $      86,173
      282987           M      1           FULL-TIME             Marketing             4        $      46,068
      283010           F      4           PART-TIME             Marketing             4        $      86,171
      283045           F      3           FULL-TIME             Marketing             4        $      74,431
      283185           M      4           FULL-TIME           Phone support           8        $      86,173
      283258           F      3           FULL-TIME             Marketing             4        $      66,117
      284866           F      3           FULL-TIME                Sales              2        $      71,541
      284912           F      2           FULL-TIME          Hardware sales           7        $      57,446
      289175           M      3           FULL-TIME           Phone support           8        $      74,431
      292850           M      2           FULL-TIME                Sales              2        $      53,472
      292877           M      3           FULL-TIME             Recreation            3        $      74,431
      292907           M      1           FULL-TIME             Marketing             4        $      39,924
      292931           M      4           FULL-TIME             Marketing             4        $      86,173
      293016           M      3           FULL-TIME                Sales              2        $      74,431
      293040           M      1           FULL-TIME                Legal              11       $      39,924
      293067           F      1           FULL-TIME          Field operations         12       $      39,924
      293083           M      1           FULL-TIME                Legal              11       $      39,924



EOWA Pay Equity Tool                  Payroll Analysis: Excel Analysis Workbook                        Sample Data
                                          Status              Section
        ID       Gender    Grade ID                                               Section ID   Remuneration
                                        Description          Description
      293261           M      2           FULL-TIME             Marketing             9        $      61,421
      293326           M      3           FULL-TIME             Marketing             4        $      71,541
      293385           M      3           FULL-TIME          Hardware sales           7        $      74,431
      293393           M      3           FULL-TIME            Recreation             3        $      71,541
      293458           F      3           FULL-TIME            Head Office            1        $      74,431
      293474           M      3           FULL-TIME           Software dev            6        $      71,541
      293555           F      3           FULL-TIME               Other               13       $      71,541
      293601           M      2           FULL-TIME               Sales               2        $      57,446
      293679           M      3           FULL-TIME          Phone support            8        $      66,117
      293695           M      4           FULL-TIME             Marketing             4        $      86,173
      293717           M      2           FULL-TIME            Recreation             3        $      57,446
      293725           F      3           FULL-TIME             Marketing             4        $      71,541
      293814           M      4           FULL-TIME             Marketing             4        $      86,173
      294497           M      2           FULL-TIME             Marketing             9        $      57,446
      295159           M      1           FULL-TIME             Marketing             9        $      46,068
      297127           M      3           FULL-TIME             Marketing             9        $      74,431
      297135           M      3           FULL-TIME             Marketing             4        $      66,117
      307416           F      3           PART-TIME          Phone support            8        $      71,538
      308021           F      1           FULL-TIME             Marketing             9        $      46,068
      308692           F      3           FULL-TIME             Marketing             4        $      71,541
      312231           M      3           FULL-TIME          Hardware sales           7        $      74,431
      312967           F      3           FULL-TIME          Phone support            8        $      71,541
      312975           F      2           FULL-TIME          Phone support            8        $      57,446
      313513           F      1           FULL-TIME          Hardware sales           7        $      36,669
      313564           F      1           FULL-TIME          Hardware sales           7        $      36,669
      313610           F      1           FULL-TIME            Head Office            1        $      46,068
      313661           M      3           FULL-TIME          Phone support            8        $      71,541
      313807           M      3           FULL-TIME          Hardware sales           7        $      66,117
      313858           F      3           FULL-TIME          Phone support            8        $      66,117
      313920           F      3           FULL-TIME               Other               13       $      66,117
      314013           M      4           FULL-TIME             Marketing             4        $      86,173
      314064           M      3           FULL-TIME             Marketing             4        $      74,431
      314102           M      3           FULL-TIME          Phone support            8        $      66,117
      314153           M      3           FULL-TIME             Marketing             4        $      71,541
      314404           F      1           FULL-TIME          Hardware sales           7        $      46,068
      314579           F      2           FULL-TIME             Marketing             9        $      57,446
      314609           F      3           FULL-TIME          Phone support            8        $      66,117
      314757           M      1           FULL-TIME          Hardware sales           7        $      36,669
      314765           F      1           FULL-TIME             Marketing             4        $      36,669
      314803           F      1           FULL-TIME          Hardware sales           7        $      36,669
      314889           F      2           FULL-TIME          Phone support            8        $      57,446
      319694           M      1           FULL-TIME          Hardware sales           7        $      36,669
      320382           F      4           PART-TIME          Hardware sales           7        $      86,177




EOWA Pay Equity Tool                  Payroll Analysis: Excel Analysis Workbook                        Sample Data
    EOWA Payroll Analysis: Excel Analysis Workbook

    Pivot Table Template 1*

    Analysis Variables: Grade by Sex (with pay equity calculation)
   The pivot table below provides a basic pay equity analysis of the payroll data contained within the 'Sample
   Data' worksheet. The analysis variables are gender and grade. A pay equity calculation for each grade shows
   women's average earnings at each grade level as a percentage of the men's average earnings.

   Instructions for conducting this pivot table analysis on your own payroll data are provided in the two
   accompanying documents, Payroll Analysis: A 7-Step Approach and Payroll Analysis: Excel Workbook
   Instructions .



                                Grade ID
   Gender    Data                        1              2               3              4    Grand Total
   F         Avg. remun.         $ 43,134       $ 57,865        $ 71,067       $ 95,950     $ 68,099
             No. employees              17             19              49             17           102
   M         Avg. remun.         $ 43,357       $ 59,534        $ 72,188       $100,029     $ 80,284
             No. employees              16             27              70             87           200
   Total Avg. remun.             $ 43,242       $ 58,845        $ 71,727       $ 99,362     $ 76,169
   Total No. employees                  33             46             119            104           302


   Pay Equity: F as % of M            99.5%          97.2%          98.4%           95.9%       84.8%




EOWA Pay Equity Tool                    Payroll Analysis: Excel Analysis Workbook                  Pivot Table Template 1
  EOWA Payroll Analysis: Excel Analysis Workbook

 Pivot Table Template 2*

 Analysis Variables: Grade by Gender by Section (with pay equity calculation)
 The pivot table below provides a more complex analysis of the payroll data contained within the 'Sample Data'
 worksheet than is given in the Pivot Table Template 1 . The analysis variables are gender, grade and section (or
 business unit).

 Instructions for conducting this pivot table analysis on your own payroll data are provided in the two accompanying
 documents, Payroll Analysis: A 7-Step Approach and Payroll Analysis: Excel Workbook Instructions .



                                               Grade ID
 Section
                       Gender Data                          1               2          3              4 Grand Total
 Description
 Marketing             F      Avg. remun.       $ 42,959 $ 55,459 $ 72,376 $ 95,810 $                        69,377
                              No. employees             5        6       19        6                              36
                       M      Avg. remun.       $ 44,312 $ 60,002 $ 72,586 $ 98,117 $                        77,223
                              No. employees             7       14       29       29                              79
 Recreation            F      Avg. remun.
                              No. employees
                       M      Avg. remun.       $ 46,068 $ 58,440 $ 72,308                 $ 102,857     $   74,689
                              No. employees             1         4         8                       4             17
 Other                 F      Avg. remun.       $ 46,068 $ 59,435 $ 69,551                 $ 86,173      $   63,932
                              No. employees             2         2         4                       1              9
                       M      Avg. remun.                           $ 71,541               $ 103,262     $   99,297
                              No. employees                                 1                       7              8
 Head Office           F      Avg. remun.       $ 46,068            $ 70,274               $ 97,226      $   76,213
                              No. employees             1                   2                       2              5
                       M      Avg. remun.                           $ 83,484               $ 90,250      $   86,867
                              No. employees                                 1                       1              2
 Hardware sales F             Avg. remun.       $ 41,369 $ 58,241 $ 70,093                 $ 86,177      $   59,830
                              No. employees             6         5         9                       1             21
                       M      Avg. remun.       $ 39,802 $ 57,446 $ 71,105                 $ 105,590     $   84,405
                              No. employees             3         2         5                      12             22
 Software dev          F      Avg. remun.       $ 46,068 $ 57,446 $ 71,541                 $ 134,774     $   88,921
                              No. employees             1         1         1                       2              5
                       M      Avg. remun.                 $ 57,446 $ 73,523                $ 108,570     $   92,944
                              No. employees                       1         3                       6             10
 Sales                 F      Avg. remun.       $ 46,068 $ 61,421 $ 68,829                               $   61,314
                              No. employees             1         2         2                                      5
                       M      Avg. remun.                 $ 55,459 $ 78,097                $ 118,331 $       92,269
                              No. employees                       2         4                       5             11
 Field operations F           Avg. remun.       $ 39,924                                   $ 95,293 $        67,608
                              No. employees             1                                           1              2
                       M      Avg. remun.                 $ 66,117                         $ 96,893 $        86,634
                              No. employees                       1                                 2              3
 Legal                 F      Avg. remun.
                              No. employees
                       M      Avg. remun.       $ 42,996 $ 61,421 $ 68,829                               $   53,009
                              No. employees             4        1        2                                        7


EOWA Pay Equity Tool                    Payroll Analysis: Excel Analysis Workbook                  Pivot Table Template 2
 Total Avg. remun.                                    $ 43,154 $ 58,724 $ 72,206 $ 102,283 $                               75,899
 Total No. employees                                         32       41       90        79                                    242




 Section               Pay Equity Calculation        Grade 1         Grade 2         Grade 3         Grade 4         Grand Total
 Marketing             % Pay Equity (F/M)               96.9%           92.4%            99.7%          97.6%            89.8%
 Recreation            % Pay Equity (F/M)                0.0%            0.0%             0.0%           0.0%             0.0%
 Other                 % Pay Equity (F/M)              #DIV/0!         #DIV/0!           97.2%          83.5%            64.4%
 Head Office           % Pay Equity (F/M)              #DIV/0!         #DIV/0!           84.2%          107.7%           87.7%
 Hardware sales % Pay Equity (F/M)                     103.9%           101.4%           98.6%          81.6%            70.9%
 Software dev          % Pay Equity (F/M)              #DIV/0!          100.0%           97.3%          124.1%           95.7%
 Sales                 % Pay Equity (F/M)              #DIV/0!          110.8%           88.1%           0.0%            66.5%
 Field operations % Pay Equity (F/M)                   #DIV/0!           0.0%            #DIV/0!        98.3%            78.0%
 Legal                 % Pay Equity (F/M)                0.0%            0.0%             0.0%         #DIV/0!            0.0%

 Note: Calculation errors (#DIV/0 or 0% figures) are caused by empty fields in the pivot table, which reflect employment categories
 in the original payroll data which contain employees of only one gender. It is not possible to calculate a pay equity percentage for
 these categories.




EOWA Pay Equity Tool                         Payroll Analysis: Excel Analysis Workbook                         Pivot Table Template 2
EOWA Payroll Analysis: Excel Analysis Workbook

Example Pivot Table Results

Scenario 1 - Equal pay
The pivot table examples below show what the analysis results would look like if your organisation has pay
equity.

In Case A, the results show an equal pay distribution in the total average earnings for men and women (98%)
and in the average earnings for men and women at each grade level (101%, 101%, 99% and 98%
respectively).

In Case B, however, the results show an example of how an apparent pay equity in men and women's total
earnings may mask more complex inequities.

Case A: Total Equity for Both Wages and Distribution

                                                 Grade
                                         Low    Medium   High   TOTAL
             Average Remuneration       $26,800 $40,000 $89,500 $57,074
 Female
                         Number              20      50      45     115
             Average Remuneration       $26,580 $39,500 $90,000 $58,381
  Male
                         Number              25      50      55     130

           % Pay Equity (F/M)              101%         101%         99%         98%
           % Female                         44%          50%         45%         47%

           Data Interpretation:
           1. The percentage of women is approximately the same in all grades.
           2. The average pay is the same for men and women at each grade.
           3. Women's total earnings are essentially equal to men's (98%).


Case B: Complex Inequities
                                                 Grade
                                         Low    Medium   High   TOTAL
             Average Remuneration       $26,000 $45,000 $86,000 $57,739
 Female
                         Number              20      50      45     115
             Average Remuneration       $26,000 $39,000 $92,000 $57,478
  Male
                         Number              20      50      45     115

           % Pay Equity (F/M)              100%         115%         93%         100%
           % Female                         50%          50%         50%          50%

           Data Interpretation:
           1. The medium-level women are paid on average more than their male peers. This may
           be a case of unequal pay for men, or it may indicate that women in this grade have been
           overlooked for promotion and should in fact be in a higher grade earning an even higher
           wage.


 EOWA Pay Equity Tool                Payroll Analysis: Excel Analysis Workbook                       Scenario 1
            2. The wage inequalities between grades cancel each other out in the overall measure of
            pay equity (100%).
            3. As the percentage of women is the same for all grades (50%), there is no distribution
            effect in the overall measure of pay equity.


Note: The Scenario worksheets are intended to provide examples of possible pivot table results that may be produced by a
payroll analysis, and to provide possible interpretations of the data from a pay equity perspective. The accuracy of the
results however will depend on the quality and appropriateness of the inputted payroll data. Also, it is important to consider
that other factors may also be contributing to any observed gender pay differences. For more details, see the two
accompanying documents, Payroll Analysis: A 7-Step Approach and Payroll Analysis: Excel Workbook Instructions .




 EOWA Pay Equity Tool                     Payroll Analysis: Excel Analysis Workbook                                 Scenario 1
EOWA Payroll Analysis: Excel Analysis Workbook

Example Pivot Table Results

Scenario 2 - Pay discrimination
In this scenario, the pivot table example shows what the analysis results would look like if your
organisation has pay inequity between men and women in the same grade.

The unequal pay within grades may indicate that women are being paid less than the men for doing the
same job, or for doing an equivalent job that requires the same level of skill, effort and responsibility as
the jobs performed by men at the same grade.

                                                          Grade
                                    Low                  Medium   High   TOTAL
              Average Remuneration $25,000               $40,000 $80,000 $54,038
 Female
                          Number        25                    50      55     130
              Average Remuneration $28,000               $42,000 $92,000 $60,462
  Male
                          Number        25                    50      55     130

            % Pay Equity (F/M)                   89%         95%          87%          89%
            % Female                             50%         50%          50%          50%

            Data Interpretation:
            1. Women are employed in the same proportions to men in each grade.
            2. Unequal pay occurs within grades and also across the company as a whole.
            3. As the percentage of women is the same for all grades (50%), there is no
            distribution effect in the overall measure of pay equity. The overall inequity (89%)
            is therefore due to the inequities at each grade.


Note: The Scenario worksheets are intended to provide examples of possible pivot table results that may be
produced by a payroll analysis, and to provide possible interpretations of the data from a pay equity perspective.
The accuracy of the results however will depend on the quality and appropriateness of the inputted payroll data.
Also, it is important to consider that other factors may also be contributing to any observed gender pay differences.
For more details, see the two accompanying documents, Payroll Analysis: A 7-Step Approach and Payroll Analysis:
Excel Workbook Instructions .




EOWA Pay Equity Tool                    Payroll Analysis: Excel Analysis Workbook                                Scenario 2
EOWA Payroll Analysis: Excel Analysis Workbook

Example Pivot Table Results

Scenario 3 - Classification or grade-based pay inequity
In this scenario, the results of the pivot table analysis show that there is equal pay within grades but not
between grades.

Men and women at the same grade are on average paid identically. However, due to the fact that
women are employed unequally in lower-grade positions, their total remuneration in the
organisation is just 45% of men's.


                                                    Grade
                                            Low    Medium   High   TOTAL
            Average Remuneration           $25,000 $40,000 $80,000 $31,613
 Female
                        Number                 100      50       5     155
            Average Remuneration           $25,000 $40,000 $80,000 $70,192
  Male
                        Number                  10      50     200     260

            % Pay Equity (F/M)                100%        100%        100%            45%
            % Female                           91%         50%          2%            37%

            Data Interpretation:
            1. There is no inequity within grades. The pay equity calculation is 100% for all grades.

            2. Women's total earnings, however, are just 45% of men's total earnings.
            3. The overall gender pay inequity is due to the small proportion of women in the top
            grade category.


Note: The Scenario worksheets are intended to provide examples of possible pivot table results that may be produced by
a payroll analysis, and to provide possible interpretations of the data from a pay equity perspective. The accuracy of the
results however will depend on the quality and appropriateness of the inputted payroll data. Also, it is important to
consider that other factors may also be contributing to any observed gender pay differences. For more details, see the two
accompanying documents, Payroll Analysis: A 7-Step Approach and Payroll Analysis: Excel Workbook Instructions .




  EOWA Pay Equity Tool                    Payroll Analysis: Excel Analysis Workbook                               Scenario 3
     EOWA Payroll Analysis: Excel Analysis Workbook

     Example Pivot Table Results

     Scenario 4 - Occupation-based pay inequity
    In this scenario, the pivot table analysis shows an example of pay inequity by occupation or business unit.

    In Case A, women are paid equally within the different occupations/business units of the organisation, but are
    employed in unequal numbers in each section, creating a distribution effect on overall pay levels. When
    combined with the lower average earnings of the occupational unit employing the most women (in this case,
    administration), the result is that women's total earnings are significantly less than men's.

    In Case B, however, women are paid unequally in all occupations/business units of the organisation. As they
    are employed in equal numbers of men within each occupational unit, however, there is no distribution effect
    on the pay equity calculation.




    Case A: Distribution differences / no wage difference

                                              Occupation / Business        Unit
                                             Admin   Production            Sales    TOTAL
                Average Remuneration         $26,800    $40,000            $89,500 $32,056
      Female
                            Number               100         30                   5     135
                Average Remuneration         $26,580    $39,500            $90,000 $58,381
       Male
                            Number                25         50                  55     130

                % Pay Equity (F/M)              101%           101%             99%      55%
                % Female                         80%            38%              8%      51%

                Data Interpretation:
                1. There is no pay inequity within occupational units, as the pay equity calculation for
                each is close to 100%.
                2. Women are concentrated in 'Administration' which has a lower average wage than the
                other business units.
                3. 'Sales' employs a high proportion of men, and also attracts the highest remuneration.

                4. The pay inequity in this scenario is a result of the uneven distribution of men and
                women across occupations, not from paying men and women in the same roles


    Case B: Wage difference / No distribution difference

                                              Occupation / Business Unit
                                             Admin   Production     Sales    TOTAL
                          Average Wage       $26,800    $40,000     $89,500 $58,404
      Female
                                Number            25         50           55     130
                          Average Wage       $29,000    $45,000 $110,000 $69,423
       Male
                                Number            25         50           55     130

                % Pay Equity (F/M)                92%            89%            81%      84%
                % Female                          50%            50%            50%      50%

                Data Interpretation:



EOWA Pay Equity Tool                    Payroll Analysis: Excel Analysis Workbook                             Scenario 4
                1. Women are paid unequally to men in all occupational units.
                2. Men and women are employed in identical numbers in each occupational unit, so their
                numbers do not affect the overall pay distribution (ie, there is no distribution effect).

                3. The pay inequity in this scenario is a result of the unequal pay in each occupational
                unit.


    Note: The Scenario worksheets are intended to provide examples of possible pivot table results that may be produced by a
    payroll analysis, and to provide possible interpretations of the data from a pay equity perspective. The accuracy of the
    results however will depend on the quality and appropriateness of the inputted payroll data. Also, it is important to consider
    that other factors may also be contributing to any observed gender pay differences. For more details, see the two
    accompanying documents, Payroll Analysis: A 7-Step Approach and Payroll Analysis: Excel Workbook Instructions .




EOWA Pay Equity Tool                         Payroll Analysis: Excel Analysis Workbook                                       Scenario 4
    EOWA Payroll Analysis: Excel Analysis Workbook

    Example Pivot Table Results

    Scenario 5 - Workforce participation-based pay inequity
    In this scenario, the pivot table analysis indicates that women earn less than men overall because they are
    more likely to employed part-time or on a casual basis and less likely to be employed full-time.

    The data might also result from the fact that employees in part-time or casuals roles are more likely to be at
    lower grades within the organisation, as the requirement or choice to work part-time or casually (such as to
    meet caring commitments) can impede employees' progression at work. Cross-analysing the results by a third
    variable, grade (as in the Pivot Table Template 2) would help to verify this.

    NOTE: This analysis is based on total actual earnings, not full-time equivalent earnings (which are used for all
    other payroll analyses).



                                                          Employment Status
                                                  Full-time   Part-time    Casual    TOTAL
                 Average Remuneration               $46,800     $23,000     $26,500 $33,313
     Female
                             Number                       30          28          17     75
                 Average Remuneration               $47,000     $22,600     $27,000 $43,565
      Male
                             Number                       63            4          8     75

                % Pay Equity (F/M)                     100%             102%              98%          76%
                % Female                                32%              88%              68%          50%

                Data Interpretation:
                1. Women are disproportionately employed in part-time and casual roles.
                2. There is pay equity between men and women with the same employment status.
                3. The overall pay inequity (76%) results from the lower average pay of part-time and
                casual employees combined with the predominance of women in these categories.


    Note: The Scenario worksheets are intended to provide examples of possible pivot table results that may be produced by a
    payroll analysis, and to provide possible interpretations of the data from a pay equity perspective. The accuracy of the
    results however will depend on the quality and appropriateness of the inputted payroll data. Also, it is important to consider
    that other factors may also be contributing to any observed gender pay differences. For more details, see the two
    accompanying documents, Payroll Analysis: A 7-Step Approach and Payroll Analysis: Excel Workbook Instructions .




EOWA Pay Equity Tool                          Payroll Analysis: Excel Analysis Workbook                                       Scenario 5
Acknowledgement
EOWA would like to acknowledge the assistance of Tim
Sowerbutts, Q Social Research Consultants, who
designed the methodology for the payroll analysis.

								
To top