Payroll Analysis Excel Analysis Workbook

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 . ID 191 957 1449 1538 1724 3689 4855 5010 7358 8516 9024 11622 13463 14621 15288 17825 23019 23507 27847 28177 31291 36129 36307 38660 38911 39624 39705 39721 49875 51691 52280 52450 57541 62391 63827 65552 66699 68675 69795 70645 80179 80225 81124 81159 82716 82813 83119 83135 83143 83194 Gender M M M M M M M M F F F M M M M M M F M M M F M M M M F M M M F M F F M F M M M M M M M M M M M M M F Grade ID 4 3 4 2 3 3 3 3 2 4 3 2 2 4 2 2 1 1 4 4 4 3 3 4 3 3 3 2 3 3 3 4 3 4 3 2 3 3 3 3 4 4 4 4 4 4 4 4 4 4 Status Description FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME PART-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME PART-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME Section Description Phone support Recreation Phone support Marketing Marketing Marketing Marketing Marketing Other Marketing Marketing Marketing Marketing Phone support Marketing Marketing Marketing Other Training Phone support Marketing Marketing Other Marketing Marketing Marketing Head Office Marketing Marketing Phone support Marketing Marketing Other Head Office Phone support Hardware sales Marketing Marketing Marketing Recreation Software dev Head Office Marketing Hardware sales Other Hardware sales Recreation Phone support Sales Marketing Section ID 8 3 8 9 9 4 9 9 13 4 4 9 9 8 9 9 9 13 5 8 9 4 13 4 4 4 1 9 9 8 9 4 13 1 8 7 9 9 9 3 6 1 9 7 13 7 3 8 2 4 Remuneration $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 86,173 71,541 86,173 61,421 71,541 74,429 74,431 74,431 61,423 86,173 66,117 61,421 61,421 86,173 61,421 61,421 46,068 46,068 86,173 86,173 86,173 74,426 71,541 86,176 66,117 74,431 66,117 61,421 74,431 71,541 71,541 86,173 74,431 86,177 66,117 61,419 71,541 71,541 66,117 74,431 97,314 90,250 102,238 110,587 118,113 96,852 90,083 104,658 142,335 143,998 EOWA Pay Equity Tool Payroll Analysis: Excel Analysis Workbook Sample Data ID 83399 83518 Gender M M Grade ID 4 4 Status Description FULL-TIME FULL-TIME Section Description Hardware sales Marketing Section ID 7 9 Remuneration $ $ 125,032 32,585 EOWA Pay Equity Tool Payroll Analysis: Excel Analysis Workbook Sample Data ID 83879 83917 83976 83984 83992 84115 84174 84190 84220 84336 84395 84441 84468 84484 84492 84506 84514 84549 84557 84581 84603 84611 84638 84654 84662 84670 84689 84697 84786 84794 84832 84905 84921 84948 84956 85030 85057 85103 85111 85138 85456 85472 85480 85499 85529 85618 85669 85677 85707 85774 85812 86037 86118 86223 86258 86282 86312 91405 92959 93068 93564 109282 112283 119385 120413 121312 121665 124354 Gender M M M M M M M M M M F M M M M M M M M F F M M M M M M M M M M M M M M M M M M M M M M M M M M M M M M M M M F M F M F M M F M M F M M F Grade ID 4 4 4 4 4 4 4 4 4 3 3 4 4 4 4 4 4 4 4 4 4 4 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 3 4 2 3 4 3 3 3 3 3 2 2 3 Status Description FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME Section Description Software dev Hardware sales Phone support Other Software dev Software dev Marketing Hardware sales Field operations Sales Marketing Phone support Marketing Recreation Other Marketing Phone support Hardware sales Sales Field operations Head Office Field operations Head Office Hardware sales Marketing Marketing Other Other Sales Software dev Hardware sales Phone support Other Phone support Software dev Hardware sales Marketing Recreation Hardware sales Sales Marketing Marketing Marketing Hardware sales Marketing Marketing Other Marketing Marketing Marketing Sales Hardware sales Marketing Phone support Software dev Software dev Software dev Marketing Phone support Phone support Recreation Other Recreation Marketing Hardware sales Training Recreation Sales Section ID 6 7 8 13 6 6 4 7 12 2 4 8 4 3 13 4 8 7 2 12 1 12 1 7 4 4 13 13 2 6 7 8 13 8 6 7 4 3 7 2 4 4 9 7 4 9 13 9 4 9 2 7 9 8 6 6 6 4 8 8 3 13 3 9 7 5 3 2 Remuneration $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 107,134 114,786 96,825 130,914 126,000 110,634 95,655 112,921 95,667 91,984 88,893 111,895 111,042 125,058 84,693 110,960 102,429 89,049 145,701 95,293 108,274 98,118 83,484 95,410 99,481 91,471 47,408 119,128 91,501 97,789 92,392 96,333 117,957 105,550 112,550 110,526 104,073 110,112 85,255 91,368 99,534 97,325 126,000 120,408 98,785 92,611 104,622 93,383 203,000 105,000 120,749 113,866 140,000 104,513 175,000 82,912 94,549 61,421 71,541 86,173 74,431 66,117 66,117 66,117 66,117 61,421 57,446 66,117 Sample Data EOWA Pay Equity Tool Payroll Analysis: Excel Analysis Workbook ID 127205 Gender F Grade ID 4 Status Description FULL-TIME Section Description Marketing Section ID 9 Remuneration $ 86,173 EOWA Pay Equity Tool Payroll Analysis: Excel Analysis Workbook Sample Data ID 134627 136654 140279 141844 143804 144339 144436 145742 150320 155632 155667 155861 163732 165239 168793 169498 171344 171735 176745 181846 184969 185302 186120 187194 196371 198676 199206 199443 200352 200999 202800 202908 203688 203815 205117 205133 205168 206830 206989 207772 210552 211079 211095 211931 212539 213861 214981 215007 217255 218537 218553 218561 218596 226548 227420 227943 228842 229202 231320 231754 232440 232467 232947 233056 234184 235296 235326 235806 Gender M M M M M M F M M F M F M F M M F M M M M M M F F F F M M M F F F F M M F F M M F F F M F M M M F F M M F F F F M M F M M F M M M F M M Grade ID 2 4 2 3 2 3 2 3 3 3 3 2 2 3 2 3 3 2 4 4 1 4 3 1 4 2 3 3 3 3 2 3 3 3 1 4 2 1 1 3 4 3 3 3 3 3 4 3 2 3 2 1 3 3 3 2 3 3 3 4 4 1 4 3 3 3 3 3 Status Description FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME PART-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME PART-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME Section Description Marketing Phone support Field operations Marketing Legal Phone support Marketing Phone support Phone support Hardware sales Software dev Marketing Phone support Marketing Recreation Legal Marketing Hardware sales Marketing Marketing Marketing Phone support Phone support Marketing Marketing Sales Marketing Marketing Marketing Marketing Marketing Phone support Hardware sales Marketing Marketing Phone support Hardware sales Other Marketing Recreation Marketing Hardware sales Phone support Phone support Phone support Marketing Phone support Phone support Hardware sales Phone support Recreation Recreation Marketing Phone support Marketing Hardware sales Marketing Phone support Phone support Marketing Recreation Marketing Marketing Sales Hardware sales Hardware sales Phone support Phone support Section ID 9 8 12 4 11 8 9 8 8 7 6 9 8 9 3 11 4 7 4 9 9 8 8 9 4 2 4 4 4 4 9 8 7 4 9 8 7 13 9 3 4 7 8 8 8 4 8 8 7 8 3 3 4 8 4 7 4 8 8 4 3 9 4 2 7 7 8 8 Remuneration $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 61,421 86,173 66,117 74,431 61,421 71,541 53,472 74,431 71,541 74,431 66,117 53,472 61,421 71,541 57,446 66,117 74,431 57,446 86,173 94,347 39,924 86,173 71,541 39,924 86,173 61,421 74,426 71,541 74,431 74,431 53,472 74,429 71,541 74,426 46,068 86,173 57,446 46,068 46,068 74,431 86,173 66,115 71,538 66,117 74,431 71,541 86,173 66,117 57,446 66,121 61,421 46,068 71,541 66,117 66,115 57,446 74,431 71,541 74,431 86,173 86,173 46,068 86,173 71,541 66,117 74,431 56,566 74,431 Sample Data EOWA Pay Equity Tool Payroll Analysis: Excel Analysis Workbook ID 235962 Gender F Grade ID 3 Status Description FULL-TIME Section Description Marketing Section ID 4 Remuneration $ 74,431 EOWA Pay Equity Tool Payroll Analysis: Excel Analysis Workbook Sample Data ID 236071 239798 241253 243396 243817 244732 245623 245682 247642 249947 251089 251763 256323 256358 256412 257028 258520 258555 258563 258598 258628 258644 258695 258725 258733 258806 258814 258903 260339 260347 261769 269468 269646 270792 270814 270830 270970 270997 271004 271012 271152 271195 271217 271292 271314 271330 271357 271365 275379 277991 282391 282529 282898 282987 283010 283045 283185 283258 284866 284912 289175 292850 292877 292907 292931 293016 293040 293067 Gender M M F M M M M M M F M M F M F F F F M F M M F F F M M M F F F M M M M F M F M F F F F M F F M F M F M F F M F F M F F F M M M M M M M F Grade ID 2 3 3 3 4 3 4 3 3 4 4 2 3 3 2 3 1 3 1 1 1 2 2 1 2 3 3 2 3 3 3 2 4 3 3 2 3 2 2 2 3 4 4 1 1 1 1 3 3 3 3 4 4 1 4 3 4 3 3 2 3 2 3 1 4 3 1 1 Status Description FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME Section Description Marketing Marketing Phone support Recreation Marketing Marketing Phone support Marketing Phone support Phone support Phone support Marketing Marketing Marketing Software dev Marketing Sales Software dev Phone support Software dev Legal Marketing Marketing Marketing Other Hardware sales Legal Marketing Marketing Marketing Hardware sales Hardware sales Phone support Marketing Marketing Marketing Sales Sales Software dev Phone support Hardware sales Phone support Other Hardware sales Hardware sales Hardware sales Legal Hardware sales Marketing Hardware sales Phone support Phone support Phone support Marketing Marketing Marketing Phone support Marketing Sales Hardware sales Phone support Sales Recreation Marketing Marketing Sales Legal Field operations Section ID 9 9 8 3 4 4 8 9 8 8 8 4 4 4 6 4 2 6 8 6 11 9 9 9 13 7 11 9 4 4 7 7 8 4 4 9 2 2 6 8 7 8 13 7 7 7 11 7 4 7 8 8 8 4 4 4 8 4 2 7 8 2 3 4 4 2 11 12 Remuneration $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 57,446 74,431 74,431 71,541 86,173 74,431 86,173 74,431 74,431 86,173 86,173 61,419 66,117 74,431 57,446 74,431 46,068 71,541 46,068 46,068 46,068 57,446 53,472 46,068 57,446 74,431 71,541 53,472 71,541 71,541 66,117 57,446 86,173 74,431 74,431 61,421 74,431 61,421 57,446 61,421 66,117 86,173 86,173 46,068 46,068 46,068 46,068 74,431 74,431 71,541 74,431 66,117 86,173 46,068 86,171 74,431 86,173 66,117 71,541 57,446 74,431 53,472 74,431 39,924 86,173 74,431 39,924 39,924 Sample Data EOWA Pay Equity Tool Payroll Analysis: Excel Analysis Workbook ID 293083 Gender M Grade ID 1 Status Description FULL-TIME Section Description Legal Section ID 11 Remuneration $ 39,924 EOWA Pay Equity Tool Payroll Analysis: Excel Analysis Workbook Sample Data ID 293261 293326 293385 293393 293458 293474 293555 293601 293679 293695 293717 293725 293814 294497 295159 297127 297135 307416 308021 308692 312231 312967 312975 313513 313564 313610 313661 313807 313858 313920 314013 314064 314102 314153 314404 314579 314609 314757 314765 314803 314889 319694 320382 Gender M M M M F M F M M M M F M M M M M F F F M F F F F F M M F F M M M M F F F M F F F M F Grade ID 2 3 3 3 3 3 3 2 3 4 2 3 4 2 1 3 3 3 1 3 3 3 2 1 1 1 3 3 3 3 4 3 3 3 1 2 3 1 1 1 2 1 4 Status Description FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME FULL-TIME PART-TIME Section Description Marketing Marketing Hardware sales Recreation Head Office Software dev Other Sales Phone support Marketing Recreation Marketing Marketing Marketing Marketing Marketing Marketing Phone support Marketing Marketing Hardware sales Phone support Phone support Hardware sales Hardware sales Head Office Phone support Hardware sales Phone support Other Marketing Marketing Phone support Marketing Hardware sales Marketing Phone support Hardware sales Marketing Hardware sales Phone support Hardware sales Hardware sales Section ID 9 4 7 3 1 6 13 2 8 4 3 4 4 9 9 9 4 8 9 4 7 8 8 7 7 1 8 7 8 13 4 4 8 4 7 9 8 7 4 7 8 7 7 Remuneration $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 61,421 71,541 74,431 71,541 74,431 71,541 71,541 57,446 66,117 86,173 57,446 71,541 86,173 57,446 46,068 74,431 66,117 71,538 46,068 71,541 74,431 71,541 57,446 36,669 36,669 46,068 71,541 66,117 66,117 66,117 86,173 74,431 66,117 71,541 46,068 57,446 66,117 36,669 36,669 36,669 57,446 36,669 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 Data Avg. remun. No. employees M Avg. remun. No. employees Total Avg. remun. Total No. employees Gender F 1 $ 43,134 17 $ 43,357 16 $ 43,242 33 2 $ 57,865 19 $ 59,534 27 $ 58,845 46 3 4 Grand Total $ 71,067 $ 95,950 $ 68,099 49 17 102 $ 72,188 $ 100,029 $ 80,284 70 87 200 $ 71,727 $ 99,362 $ 76,169 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 Description Marketing Gender F M Recreation F M Other F M Head Office F M Hardware sales F M Software dev F M Sales F M Field operations F M Legal F M Data Avg. remun. No. employees Avg. remun. No. employees Avg. remun. No. employees Avg. remun. No. employees Avg. remun. No. employees Avg. remun. No. employees Avg. remun. No. employees Avg. remun. No. employees Avg. remun. No. employees Avg. remun. No. employees Avg. remun. No. employees Avg. remun. No. employees Avg. remun. No. employees Avg. remun. No. employees Avg. remun. No. employees Avg. remun. No. employees Avg. remun. No. employees Avg. remun. $ $ 1 42,959 $ 5 44,312 $ 7 2 55,459 $ 6 60,002 $ 14 3 72,376 $ 19 72,586 $ 29 4 Grand Total 95,810 $ 6 98,117 $ 29 69,377 36 77,223 79 $ $ 46,068 $ 1 46,068 $ 2 58,440 $ 4 59,435 $ 2 $ $ $ $ 46,068 1 $ $ $ 41,369 $ 6 39,802 $ 3 46,068 $ 1 $ 46,068 $ 1 $ 39,924 1 $ $ 58,241 5 57,446 2 57,446 1 57,446 1 61,421 2 55,459 2 $ $ $ $ $ $ 72,308 8 69,551 4 71,541 1 70,274 2 83,484 1 70,093 9 71,105 5 71,541 1 73,523 3 68,829 2 78,097 4 $ 102,857 4 $ 86,173 1 $ 103,262 7 $ 97,226 2 $ 90,250 1 $ 86,177 1 $ 105,590 12 $ 134,774 2 $ 108,570 6 $ $ $ $ $ $ $ $ $ $ $ 66,117 1 $ 118,331 $ 5 $ 95,293 $ 1 $ 96,893 $ 2 74,689 17 63,932 9 99,297 8 76,213 5 86,867 2 59,830 21 84,405 22 88,921 5 92,944 10 61,314 5 92,269 11 67,608 2 86,634 3 $ 42,996 $ 61,421 $ 68,829 $ 53,009 EOWA Pay Equity Tool Payroll Analysis: Excel Analysis Workbook Pivot Table Template 2 Legal M No. employees 4 1 2 7 EOWA Pay Equity Tool Payroll Analysis: Excel Analysis Workbook Pivot Table Template 2 Total Avg. remun. Total No. employees $ 43,154 $ 32 58,724 $ 41 72,206 $ 102,283 $ 90 79 75,899 242 Section Pay Equity Calculation Grade 1 Grade 2 Grade 3 Grade 4 Grand Total Marketing Recreation Other Head Office Software dev Sales Legal % Pay Equity (F/M) % Pay Equity (F/M) % Pay Equity (F/M) % Pay Equity (F/M) % Pay Equity (F/M) % Pay Equity (F/M) % Pay Equity (F/M) 96.9% 0.0% #DIV/0! #DIV/0! 103.9% #DIV/0! #DIV/0! #DIV/0! 0.0% 92.4% 0.0% #DIV/0! #DIV/0! 101.4% 100.0% 110.8% 0.0% 0.0% 99.7% 0.0% 97.2% 84.2% 98.6% 97.3% 88.1% #DIV/0! 0.0% 97.6% 0.0% 83.5% 107.7% 81.6% 124.1% 0.0% 98.3% #DIV/0! 89.8% 0.0% 64.4% 87.7% 70.9% 95.7% 66.5% 78.0% 0.0% Hardware sales % Pay Equity (F/M) Field operations % Pay Equity (F/M) 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 $26,800 $40,000 $89,500 $57,074 20 50 45 115 $26,580 $39,500 $90,000 $58,381 25 50 55 130 101% 44% 101% 50% 99% 45% 98% 47% Female Male Average Remuneration Number Average Remuneration Number % Pay Equity (F/M) % Female 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 $26,000 $45,000 $86,000 $57,739 20 50 45 115 $26,000 $39,000 $92,000 $57,478 20 50 45 115 100% 50% 115% 50% 93% 50% 100% 50% Female Male Average Remuneration Number Average Remuneration Number % Pay Equity (F/M) % Female 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