The interactive worksheet below is made up of tables. By entering data in designated cells of each table on this page, you will calculate Proportional Value job rates for unmatched female job classes. You must complete the following steps to achieve Proportional Value rates.
Please Read Before You Begin:
- You must complete Step 1 before continuing on to Steps 2 and 3.
At Step 1, you enter the number of male and female jobs you'll be working with in this exercise - this will give you the number of rows per table you need for your calculation.
- At Step 2, you enter the male job data (in Table # 1): the representative male job classes are entered in the first column; job values in column A; and job rates in column B (enter this data in descending order). Each cell in columns A and B must contain data.
Click "Compute" at the bottom of the table to activate the regression formula. If you click "Reset", the data in each cell in this table will be erased.
The regression program will generate "output" numbers: Columns C - G will be filled in. The Constant and the Slope values will appear under their own headings below Table # 1. And the R-squared numbers will appear in Table # 2.
- At Step 3, you enter the female job data (in Table # 3): unmatched female job classes are entered in the first column; job values in column A; and job rates in column B. Each cell in columns A and B must contain data.
Click "Compute" at the bottom of the table to apply the formula to the female job data. If you click "Reset", the data in each cell in this table will be erased.
The Proportional Value job rate results for the unmatched female job classes will appear in Column C and the required pay equity adjustment in Column D.
- You can print your final calculations by using the 'Print' icon on your browser menu bar.
- To produce new Proportional Value results with new data, begin again from Step # 1.
Step 1: Enter the Number of Male and Female Jobs You'll be Working With
You cannot skip this step. You must enter the number of male jobs (for example, 3) and female jobs (for example, 3) in the cells below, then click "Load the tables". This will give you the correct number of rows you need in each table for your calculation.
Number of male jobs:;
Step 2: Enter the Male Job Data
Enter the representative male job classes in the Male Job Classes column, and the job values and job rates in Columns A and B (in descending order). Click "Compute" at the bottom of the table to activate the regression formula, then move down the page to view your output results. Remember that if you click "Reset", all the data in the table will be erased. Go to Step 3.
The values for the slope and constant are used to determine the pay equity job rate:
pay equity job rate = + ( x job value)
The table below (Table # 15) contains the calculation that measures how well the job rate line fits the given set of male job data. A higher value of R-squared indicates a better fit.
The R-squared calculation for the male job data is:
Table 14. Worksheet to Calculate the Regression Line
|Male Job Classes||Job Value(X)||Job Rate $ (Y)||Deviation from Average Job Value (X-XM)||Deviation from Average Job Rate $ (Y-YM)||Square of(X-XM)||Square of(Y-YM)||Product of (X-XM) (Y-YM)|
The R-squared result:
Step 3: Enter the Female Job Data
The final step in this interactive exercise is to enter the female job data. Enter the female job classes in the Female Job Classes column, and the job values and job rates in Columns A and B. Click "Compute" and your Proportional Value Job Rate results will automatically appear in Column C and the pay equity adjustment(if any) in Column D. Remember that if you click "Reset", all the data in the table will be erased.
Table 15. Worksheet to Calculate R-Squared
|Male Job Class||Job Value (X)||Job Rate $ Y)||Predicted Job Rate $ (Pred. Y)||Error in Prediction $ (E)||Square of error (SQE)|
Table 16. PV Job Rate Results For Unmatched Female Job Classes
|Unmatched Female Job Classes||Job Value (X)||Job Rate $ (Y)||PV Rate||Adjustment|
You now have Proportional Value job rates for the female job classes that were unmatched under the Job-to-Job approach.
Before you exit this overview on Proportional Value and Regression Analysis, you may wish to view these last few pages: