Using EXCEL

1. Suppose that the data below are the results of your job evaluation. Develop a job classification plan following the prescribed format. Show computations of Range, k(no. of classes), and Class Interval.

2. Develop a salary plan following the prescribed format. Show computation/regression values for b, a, RP and k(no of classes).

I already followed the format but my prof said I was wrong in a and b values

This my classification plan. Is my Range correct?

This is my Salary Plan. My professor said that my values in REGRESSION a (coefficient - x variable 1) and b (coefficient - intercept) are wrong. Please help !

Job Title Secretary Job Analyst Assembler Supervisor Quality Control Inspector Material Planner Machine Operator Administrative Assistant Job Rating (Points) 455 720 370 775 680 Salary Range 7,500 - 8,200 13,000 14,000 5,800 6,200 14, 300 15,000 10, 200 11,200 9,200 10,000 6,500 7,200 8,200 -8,800 625 500 530 Jobs Example 1 Encoder y - ( x)( y) b = ? - ()2 2 Messenger 3700 24(43,657,200)-8,823(106,400) 24 (3,704,853)-(8,823)2 3 Timekeeper 340 b = 9.8459 4 Guard y -b 106,400 -9.8459(8,823) Rating (x) Salary (y) X2 151 2500 377.500 22,801 151 2300 347.300 22,801 151 2400 362,400 22,801 235 3200 752,000 55,225 235 3500 822,500 55,225 235 869.500 55,225 300 3700 1,110,000 90,000 300 3900 1,170,000 90,000 300 3800 1,140,000 90,000 3900 1,326,000 115,600 340 4000 1,360,000 115,600 340 4100 1,394,000 115,600 365 4300 1,569,500 133,225 365 4500 1,642,500 133,225 365 4400 1,606,000 133,225 440 4600 2,024,000 193,600 440 4500 1,980,000 193,600 440 4800 2,112,000 193,600 490 5000 2,450,000 240,100 490 5300 2,597,000 240,100 490 5500 2,695,000 240,100 620 7000 4,340,000 384,400 620 7500 4,650,000 384,400 620 8000 4,960,000 384,400 8,823 106,400 43,657,200 3,704,853 a = 24 n 5 Utility Man a = 813-7266 6 Janitor k=1+3.322 log 24 = 5.585-6 7 Operator RP = k-1 (Highest Salary Lowest Salary 5 8,000 2,300 1.283 8 Bookkeeper Total A E F G H 1 J K 1 JOB x^2 2 136900 R= 775-370 405 3 k= 1+3.322log8 5 4 CI= 405/5 81 5 6 ASSEMBLER 7 8 CLASS 9 1 II 10 11 JOB CLASSIFICATION PLAN JOB POINTS ASSEMBLER 370-450 SECRETARY 451-531 MACHINE OPERATOR, ADMINISTRATIVE ASSISTANT, MATERIAL PLANNER 532-612 QUALITY CONTROL, JOB ANALYST 613-693 SUPERVISOR 694-775 IV 12 13 14 V B D RATING (X) SALARY (y) 370 5,800 2146000 370 5,900 2183000 370 6,000 2220000 370 6,100 2257000 370 6,200 2294000 455 7,500 3412500 455 7,600 3458000 455 7,700 3503500 455 7,800 3549000 455 7,900 3594500 455 8,000 3640000 455 8,100 3685500 455 8,200 3731000 500 6,500 3250000 500 6,600 3300000 500 6,700 3350000 500 6,800 3400000 500 6,900 3450000 500 7,000 3500000 500 7,100 3550000 500 7,200 3600000 530 8,200 4346000 530 8,300 4399000 530 8,400 4452000 530 8,500 4505000 530 8,600 4558000 SECRETARY 15 136900 136900 136900 136,900 207,025 207,025 207,025 207,025 207,025 207,025 207,025 207,025 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 280,900 280,900 280,900 280,900 280,900 16 17 18 19 20 21 22 MACHINE OPERATOR 23 24 25 26 27 CLASSIFICATION PLAN SALARY PLAN # D F G H 1 J K L M N O E x^2 JOB CLASS POSITIONS ASSEMBLER 136900 b= II 2146000 2183000 2220000 2257000 2294000 136900 136900 21.43168968 - 2828.000825 a= RP- 1.203 SECRETARY MACHINE OPERATOR ADMINISTRATIVE ASSISTANT MATERIAL PLANNER QUALITY CONTROL JOB ANALYST SUPERVISOR BASE SALARY MIN. RATE MAX RATE HIRING RATE 5101.724356 4240.834876 6137.374401 3816.751389 6923.417979 5755.127165 8328.871829 8328.871829 5179.614448 7887.844015 6556.811317 9489.076349 5901.130185 8530.794705 7091.267419 10262.54603 6382.140677 10566.80522 8783.711741 12711.86668 7905.340567 11745.54816 9763.547927 14129.89443 8787.193135 12602.81574 10476.15606 15161.18734 9428.540457 13781.55868 11455.99225 16579.21509 10310.39302 IV BS = MIN. RATE = MAX RATE = HIRING RATE = a+bx BS/RP BS X RP Min. Rate *0.90 V B C 1 RATING (x) SALARY (y) 2 370 5,800 3 370 5,900 4 370 6,000 5 370 6,100 6 370 6,200 7 455 7,500 8 455 7,600 9 455 7,700 10 455 7,800 11 455 7,900 12 455 8,000 13 455 8,100 14 455 8,200 15 500 6,500 16 500 6,600 17 500 6,700 18 500 6,800 19 500 6,900 20 500 7,000 21 500 7,100 22 500 7,200 23 530 8,200 24 530 8,300 25 530 8,400 26 530 8,500 27 530 8,600 3412500 3458000 3503500 3549000 3594500 3640000 3685500 3731000 3250000 3300000 3350000 3400000 3450000 3500000 3550000 3600000 4346000 4399000 4452000 4505000 4558000 136900 136,900 207,025 207,025 207,025 207,025 207,025 207,025 207,025 207,025 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 280,900 280,900 280,900 280,900 280,900 Regression Statistics Multiple R 0.942450608 R Square 0.888213149 Adjusted R Square 0.886493351 Standard Error 955.0809795 Observations 67 CLASSIFICATION PLAN SALARY PLAN + B D E F G H J K L M M N O 16 17 18 500 500 500 500 500 500 500 530 19 20 21 22 23 24 25 26 27 28 29 530 530 Regression Statistics Multiple R 0.942450608 R Square 0.888213149 Adjusted R Square 0.886493351 Standard Error 955.0809795 Observations 67 530 530 530 6,600 6,700 6,800 6,900 7,000 7,100 7,200 8,200 8,300 8,400 8,500 8,600 8,700 8,800 9,200 9,300 9,400 9,500 9,600 9,700 9,800 9,900 10,000 10,200 10,300 10,400 10,500 530 3300000 3350000 3400000 3450000 3500000 3550000 3600000 4346000 4399000 4452000 4505000 4558000 4611000 4664000 5750000 5812500 5875000 5937500 6000000 6062500 6125000 6187500 6250000 6936000 7004000 7072000 7140000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 280,900 280,900 280,900 280,900 280,900 280,900 280,900 390,625 390,625 390,625 390,625 390,625 390,625 390,625 390,625 390,625 462,400 462,400 462,400 462,400 ANOVA 625 df SS F Significance F 1.23436E-32 1 471107724 625 625 MS 471107724 912179.6774 516.4637358 Regression Residual Total 30 31 32 33 34 35 36 59291679.03 65 66 625 530399403 625 625 625 Intercept X Variable 1 Coefficients -2828.000825 21.43168968 Standard Error t Stat 580.8397458 -4.868814239 0.943053858 22.72583851 P-value 7.50578E-06 1.23436E-32 Lower 95% Upper 95% Lower 95.0% Upper 95.0% -3988.0179 -1667.98375 -3988.0179 -1667.98375 19.54828107 23.31509829 19.54828107 23.31509829 37 625 38 625 39 680 40 680 41 680 42 680 CLASSIFICATION PLAN SALARY PLAN + Job Title Secretary Job Analyst Assembler Supervisor Quality Control Inspector Material Planner Machine Operator Administrative Assistant Job Rating (Points) 455 720 370 775 680 Salary Range 7,500 - 8,200 13,000 14,000 5,800 6,200 14, 300 15,000 10, 200 11,200 9,200 10,000 6,500 7,200 8,200 -8,800 625 500 530 Jobs Example 1 Encoder y - ( x)( y) b = ? - ()2 2 Messenger 3700 24(43,657,200)-8,823(106,400) 24 (3,704,853)-(8,823)2 3 Timekeeper 340 b = 9.8459 4 Guard y -b 106,400 -9.8459(8,823) Rating (x) Salary (y) X2 151 2500 377.500 22,801 151 2300 347.300 22,801 151 2400 362,400 22,801 235 3200 752,000 55,225 235 3500 822,500 55,225 235 869.500 55,225 300 3700 1,110,000 90,000 300 3900 1,170,000 90,000 300 3800 1,140,000 90,000 3900 1,326,000 115,600 340 4000 1,360,000 115,600 340 4100 1,394,000 115,600 365 4300 1,569,500 133,225 365 4500 1,642,500 133,225 365 4400 1,606,000 133,225 440 4600 2,024,000 193,600 440 4500 1,980,000 193,600 440 4800 2,112,000 193,600 490 5000 2,450,000 240,100 490 5300 2,597,000 240,100 490 5500 2,695,000 240,100 620 7000 4,340,000 384,400 620 7500 4,650,000 384,400 620 8000 4,960,000 384,400 8,823 106,400 43,657,200 3,704,853 a = 24 n 5 Utility Man a = 813-7266 6 Janitor k=1+3.322 log 24 = 5.585-6 7 Operator RP = k-1 (Highest Salary Lowest Salary 5 8,000 2,300 1.283 8 Bookkeeper Total A E F G H 1 J K 1 JOB x^2 2 136900 R= 775-370 405 3 k= 1+3.322log8 5 4 CI= 405/5 81 5 6 ASSEMBLER 7 8 CLASS 9 1 II 10 11 JOB CLASSIFICATION PLAN JOB POINTS ASSEMBLER 370-450 SECRETARY 451-531 MACHINE OPERATOR, ADMINISTRATIVE ASSISTANT, MATERIAL PLANNER 532-612 QUALITY CONTROL, JOB ANALYST 613-693 SUPERVISOR 694-775 IV 12 13 14 V B D RATING (X) SALARY (y) 370 5,800 2146000 370 5,900 2183000 370 6,000 2220000 370 6,100 2257000 370 6,200 2294000 455 7,500 3412500 455 7,600 3458000 455 7,700 3503500 455 7,800 3549000 455 7,900 3594500 455 8,000 3640000 455 8,100 3685500 455 8,200 3731000 500 6,500 3250000 500 6,600 3300000 500 6,700 3350000 500 6,800 3400000 500 6,900 3450000 500 7,000 3500000 500 7,100 3550000 500 7,200 3600000 530 8,200 4346000 530 8,300 4399000 530 8,400 4452000 530 8,500 4505000 530 8,600 4558000 SECRETARY 15 136900 136900 136900 136,900 207,025 207,025 207,025 207,025 207,025 207,025 207,025 207,025 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 280,900 280,900 280,900 280,900 280,900 16 17 18 19 20 21 22 MACHINE OPERATOR 23 24 25 26 27 CLASSIFICATION PLAN SALARY PLAN # D F G H 1 J K L M N O E x^2 JOB CLASS POSITIONS ASSEMBLER 136900 b= II 2146000 2183000 2220000 2257000 2294000 136900 136900 21.43168968 - 2828.000825 a= RP- 1.203 SECRETARY MACHINE OPERATOR ADMINISTRATIVE ASSISTANT MATERIAL PLANNER QUALITY CONTROL JOB ANALYST SUPERVISOR BASE SALARY MIN. RATE MAX RATE HIRING RATE 5101.724356 4240.834876 6137.374401 3816.751389 6923.417979 5755.127165 8328.871829 8328.871829 5179.614448 7887.844015 6556.811317 9489.076349 5901.130185 8530.794705 7091.267419 10262.54603 6382.140677 10566.80522 8783.711741 12711.86668 7905.340567 11745.54816 9763.547927 14129.89443 8787.193135 12602.81574 10476.15606 15161.18734 9428.540457 13781.55868 11455.99225 16579.21509 10310.39302 IV BS = MIN. RATE = MAX RATE = HIRING RATE = a+bx BS/RP BS X RP Min. Rate *0.90 V B C 1 RATING (x) SALARY (y) 2 370 5,800 3 370 5,900 4 370 6,000 5 370 6,100 6 370 6,200 7 455 7,500 8 455 7,600 9 455 7,700 10 455 7,800 11 455 7,900 12 455 8,000 13 455 8,100 14 455 8,200 15 500 6,500 16 500 6,600 17 500 6,700 18 500 6,800 19 500 6,900 20 500 7,000 21 500 7,100 22 500 7,200 23 530 8,200 24 530 8,300 25 530 8,400 26 530 8,500 27 530 8,600 3412500 3458000 3503500 3549000 3594500 3640000 3685500 3731000 3250000 3300000 3350000 3400000 3450000 3500000 3550000 3600000 4346000 4399000 4452000 4505000 4558000 136900 136,900 207,025 207,025 207,025 207,025 207,025 207,025 207,025 207,025 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 280,900 280,900 280,900 280,900 280,900 Regression Statistics Multiple R 0.942450608 R Square 0.888213149 Adjusted R Square 0.886493351 Standard Error 955.0809795 Observations 67 CLASSIFICATION PLAN SALARY PLAN + B D E F G H J K L M M N O 16 17 18 500 500 500 500 500 500 500 530 19 20 21 22 23 24 25 26 27 28 29 530 530 Regression Statistics Multiple R 0.942450608 R Square 0.888213149 Adjusted R Square 0.886493351 Standard Error 955.0809795 Observations 67 530 530 530 6,600 6,700 6,800 6,900 7,000 7,100 7,200 8,200 8,300 8,400 8,500 8,600 8,700 8,800 9,200 9,300 9,400 9,500 9,600 9,700 9,800 9,900 10,000 10,200 10,300 10,400 10,500 530 3300000 3350000 3400000 3450000 3500000 3550000 3600000 4346000 4399000 4452000 4505000 4558000 4611000 4664000 5750000 5812500 5875000 5937500 6000000 6062500 6125000 6187500 6250000 6936000 7004000 7072000 7140000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 280,900 280,900 280,900 280,900 280,900 280,900 280,900 390,625 390,625 390,625 390,625 390,625 390,625 390,625 390,625 390,625 462,400 462,400 462,400 462,400 ANOVA 625 df SS F Significance F 1.23436E-32 1 471107724 625 625 MS 471107724 912179.6774 516.4637358 Regression Residual Total 30 31 32 33 34 35 36 59291679.03 65 66 625 530399403 625 625 625 Intercept X Variable 1 Coefficients -2828.000825 21.43168968 Standard Error t Stat 580.8397458 -4.868814239 0.943053858 22.72583851 P-value 7.50578E-06 1.23436E-32 Lower 95% Upper 95% Lower 95.0% Upper 95.0% -3988.0179 -1667.98375 -3988.0179 -1667.98375 19.54828107 23.31509829 19.54828107 23.31509829 37 625 38 625 39 680 40 680 41 680 42 680 CLASSIFICATION PLAN SALARY PLAN +