survey spreadsheet and calculate the relevant components.

Steps to complete this assignment:

1. Download the attached excel workbook
2. Locate your specific salary survey in the tab at the bottom of the excel workbook
3. Using the data provided calculate/compute:

• each value highlighted in blue
• respond to the questions highlighted in blue
• plot market pay lines for each of the locations

STUDENT NAME KimRogers Louisville, KY Dean Organization 1 Number Incumbents Organization 2 Number Incumbents Organization 3 Number Incumbents Organization 4 Number Incumbents Organization 5 Number Incumbents Organization 6 Number Incumbents Organization 7 Number Incumbents Organization 8 Number Incumbents Organization 9 Number Incumbents Organization 10 Number Incumbents Full Professor $175,000.00 18 $178,500.00 9 $189,000.00 43 $171,500.00 16 $162,750.00 8 $187,250.00 11 $192,500.00 3 $155,750.00 22 $173,250.00 7 $196,000.00 10 Points Per Job Job Title Dean Full Professor Chancellor Assistant Professor Associate Professor Area Coordinator Points 679 452 789 343 398 587 Chancellor $139,843.00 4 $151,030.44 5 $138,444.57 21 $135,647.71 4 $152,428.87 1 $146,835.15 3 $128,655.56 1 $125,858.70 9 $158,022.59 2 $125,299.33 3 Consult O*net general information about the jobs you identified. Additionally, select three other key jobs for the organization you hope to work for. Conduct a job evaluation for each of these 6 jobs based on your understanding of each position. Include the job evaluation with your submission Remember the point of the job evaluation is to allocate points to the job relative to the importance of the job functions. Look through the data provided above. Are there any issues with the data (outliers, missing data, other concerns). If so, how do you propose to handle it? Do you drop outliers? Why or why not? Calculate The Measures of Central Tendency $193,344.00 2 $195,277.44 1 $168,209.28 5 $177,876.48 2 $222,345.60 1 $204,944.64 2 $104,405.76 1 $178,456.51 3 $200,285.05 1 $228,145.92 2 Calculate The Measures of Central Tendency Louisville, KY Dean Simple Mean Weighted Mean Trimmed Mean (20%) Range 25th Percentile 75th Percentile Interquartile Range Median Full Professor $178,150.00 $178,000.00 $178,718.75 $40,250.00 171937.5 188562.5 16625 $176,750.00 Chancellor $140,206.59 $137,937.97 139843 $32,723.26 130403.5975 149981.6175 19578.02 $139,143.79 $187,329.07 $185,367.59 192592.375 $123,740.16 178021.4875 203779.7425 25758.255 $194,310.72 Determine which measure(s) of central tendency you will use when creating a market pay line. Note that you may decide to use one or more measure. Discuss the merits of your choice and clearly explain your rationale. Note that I used the simple aver likely not the best choic Next, plot the jobs on a market pay line for each of the localities. To do this, you will need to properly structure your data. The data need to be Job Evaluation Points ordered from lowest to highest in an array see the example to the right. 452 Your horizontal (X) axis will be the job evaluation points. Your vertical (Y) 679 axis will be the average pay rate using the measure(s) of central tendency you identified above. After you do this you will have a market pay line 789 chart for each locality (a total of 3 charts). predict pay rates for jobs. In this equation Y is the predicted pay rate for any given job. Substitute the job evaluation points for x. Subtitute the value for the y-intercept for b. To do this follow the formulas provided in y-intercept (b) slope (m) Louisville, KY New York, NY Fresno, CA 76450.2284131875 INTERCEPT (salaries, points) INTERCEPT (salaries, points) 143.924466021061 SLOPE (salaries, points) SLOPE (salaries, points) Finally, predict the pay for each of the 6 jobs you identified earlier using a regression equation Y=m(x)+b from the variables you just calculated for each locality. Points Per Job Job Title Dean Full Professor Chancellor Key Job #4 Key Job #5 Key Job #6 Predicted Salary = m(x)+b Points Louisville, KY 679 452 789 343 398 587 New York, NY $174,174.94 $141,504.09 $190,006.63 $125,816.32 $133,732.17 $160,933.89 If this information were from a real organization what would you suggest to the CEO regarding the location of a new facility? Consider the bottom line impact to the organiztion - how will the decision to locate a new facility in one location impact the company from a financial perspective. Are there potential business growth factors to consider? Consult reputable sources for information on labor trends in each of the markets and job growth projections (you might start with O*net and the Bureau of Labor Statistics). New York, NY Dean Full Professor Chancellor Dean $229,652.50 $183,515.97 $253,725.33 12 8 7 $234,245.55 $198,197.25 $256,262.58 9 5 3 $248,024.70 $181,680.81 $220,741.04 84 35 7 $225,059.45 $178,010.49 $233,427.30 22 9 2 $213,576.83 $200,032.41 $291,784.13 5 2 1 $245,728.18 $192,691.77 $268,948.85 21 9 3 $252,617.75 $168,834.69 $137,011.68 9 5 2 $204,390.73 $165,164.37 $234,188.48 37 13 5 $227,355.98 $207,373.04 $262,834.07 17 4 6 $257,210.80 $164,430.31 $299,395.89 13 7 2 Job Title Fresno $203,945.00 3 $208,023.90 1 $220,260.60 61 $199,866.10 16 $189,668.85 12 $218,221.15 16 $224,339.50 1 $181,511.05 17 $201,905.55 5 $228,418.40 12 New York, NY Dean Full Professor used the simple average. This is ely not the best choice!! Louisville, KY New York, NY 140206.59 178150 187329.07 alaries, points) ry = m(x)+b Fresno, CA Fresno Chancellor Dean Fresno, CA 190000 220000 234000 230000 250000 285000 Fresno, CA Full Professor Chancellor $162,973.03 $225,323.10 1 1 $176,010.87 $227,576.33 1 1 $161,343.30 $196,031.09 23 5 $158,083.84 $207,297.25 4 1 $177,640.61 $259,121.56 3 1 $171,121.68 $238,842.48 5 2 $149,935.19 $121,674.47 1 1 $146,675.73 $207,973.22 4 0 $184,159.53 $233,412.20 2 2 $146,023.84 $265,881.26 4 1 Chart Title 200000 180000 160000 140000 Chart Title Fresno, CA Full Professor 200000 Chancellor 180000 160000 140000 120000 100000 80000 60000 40000 20000 0 400 450 500 550 600 650 700 750 800 850 700 750 800 850 Salary Survey 200000 180000 160000 140000 120000 100000 80000 60000 40000 20000 0 400 450 500 550 600 650 hart Title hart Title 600 650 700 750 800 850 700 750 800 850 ary Survey 600 650 STUDENT NAME KimRogers Louisville, KY Organization 1 Number Incumbents Organization 2 Number Incumbents Organization 3 Number Incumbents Organization 4 Number Incumbents Organization 5 Number Incumbents Organization 6 Number Incumbents Organization 7 Number Incumbents Organization 8 Number Incumbents Organization 9 Number Incumbents Organization 10 Number Incumbents Help Desk Tech Network Sys Admin IT Project Manager $45,730.00 $72,150.00 $84,559.00 18 4 2 $46,644.60 $77,922.00 $85,404.59 9 5 1 $49,388.40 $71,428.50 $73,566.33 43 21 5 $44,815.40 $69,985.50 $77,794.28 16 4 2 $42,528.90 $78,643.50 $97,242.85 8 1 1 $48,931.10 $75,757.50 $89,632.54 11 3 2 $50,303.00 $66,378.00 $45,661.86 3 1 1 $40,699.70 $64,935.00 $78,047.96 22 9 3 $45,272.70 $81,529.50 $87,594.67 7 2 1 $51,217.60 $64,646.40 $99,779.62 10 3 2 Points Per Job Job Title Help Desk Tech Network Sys Admin IT Project Manager Key Job #4 Key Job #5 Key Job #6 Points Consult O*net general information about the jobs you identified. Additionally, select three other key jobs for the organization you hope to work for. Conduct a job evaluation for each of these 6 jobs based on your understanding of each position. Include the job evaluation with your submission Remember the point of the job evaluation is to allocate points to the job relative to the importance of the job functions. Look through the data provided above. Are there any issues with the data (outliers, missing data, other concerns). If so, how do you propose to handle it? Do you drop outliers? Why or why not? Calculate The Measures of Central Tendency Calculate The Measures of Central Tendency Louisville, KY Help Desk Tech Network Sys Admin Simple Mean AVERAGE Weighted Mean SUMPRODUCT()/SUM() Trimmed Mean (20%) TRIMMEAN() .2 Range MAX() - MIN() 25th Percentile QUARTILE(1) 75th Percentile QUARTILE(3) Interquartile Range QUARTILE3 - QUARTILE1 Median MEDIAN IT Project Manager Determine which measure(s) of central tendency you will use when creating a market pay line. Note that you may decide to use one or more measure. Discuss the merits of your choice and clearly explain your rationale. Job Evaluation Points Next, plot the jobs on a market pay line for each of the localities. To do this, you will need to properly structure your data. The data need to be position with fewest ordered from lowest to highest in an array see the example to the points right. Your horizontal (X) axis will be the job evaluation points. Your position with the next vertical (Y) axis will be the average pay rate using the measure(s) of highest points central tendency you identified above. After you do this you will have a market pay line chart for each locality (a total of 3 charts). position with the highest points and predict pay rates for jobs. In this equation Y is the predicted pay rate for any given job. Substitute the job evaluation points for x. Subtitute the value for the y-intercept for b. To do this follow the y-intercept (b) slope (m) Louisville, KY New York, NY Fresno, CA INTERCEPT (salaries, poinINTERCEPT (salaries, poinINTERCEPT (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) Finally, predict the pay for each of the 6 jobs you identified earlier using a regression equation Y=m(x)+b from the variables you just calculated for each locality. Points Per Job Job Title Help Desk Tech Network Sys Admin IT Project Manager Key Job #4 Key Job #5 Key Job #6 Predicted Salary = m(x)+b Points Louisville, KY New York, NY 0 0 0 0 0 0 If this information were from a real organization what would you suggest to the CEO regarding the location of a new facility? Consider the bottom line impact to the organiztion - how will the decision to locate a new facility in one location impact the company from a financial perspective. Are there potential business growth factors to consider? Consult reputable sources for information on labor trends in each of the markets and job growth projections (you might Job Title New York, NY Help Desk Tech Network Sys Admin IT Project Manager Help Desk Tech $60,011.48 $94,682.45 $110,966.78 $53,293.74 12 8 7 3 $61,211.71 $102,257.04 $112,076.44 $54,359.62 9 5 3 1 $64,812.40 $93,735.62 $96,541.09 $57,557.24 84 35 7 61 $58,811.25 $91,841.97 $102,089.43 $52,227.87 22 9 2 16 $55,810.68 $103,203.87 $127,611.79 $49,563.18 5 2 1 12 $64,212.28 $99,416.57 $117,624.78 $57,024.30 21 9 3 16 $66,012.63 $87,107.85 $59,922.06 $58,623.12 9 5 2 1 $53,410.22 $85,214.20 $102,422.33 $47,431.43 37 13 5 17 $59,411.36 $106,991.16 $114,950.48 $52,760.80 17 4 6 5 $67,212.86 $84,835.47 $130,940.80 $59,688.99 13 7 2 12 Job Title Fresno New York, NY Fresno Help Desk Tech Network Sys Admin IT Project Manager Louisville, KY New York, NY Fresno, CA average pay average pay average pay average pay average pay average pay average pay average pay average pay alaries, points) ary = m(x)+b Fresno, CA Help Desk Tech Fresno, CA Network Sys Admin IT Project Manager $84,083.61 $98,545.06 1 1 $90,810.30 $99,530.51 1 1 $83,242.77 $85,734.20 23 5 $81,561.10 $90,661.45 4 1 $91,651.13 $113,326.82 3 1 $88,287.79 $104,457.76 5 2 $77,356.92 $53,214.33 1 1 $75,675.25 $90,957.09 4 0 $95,014.48 $102,082.83 2 2 $75,338.91 $116,283.17 4 1 Fresno, CA Network Sys Admin IT Project Manager STUDENT NAME KimRogers Louisville, KY Organization 1 Number Incumbents Organization 2 Number Incumbents Organization 3 Number Incumbents Organization 4 Number Incumbents Organization 5 Number Incumbents Organization 6 Number Incumbents Organization 7 Number Incumbents Organization 8 Number Incumbents Organization 9 Number Incumbents Organization 10 Number Incumbents Facilities Manager Comp/Bene Manager HR Manager $79,390.00 $99,590.00 $113,580.00 18 4 2 $80,977.80 $107,557.20 $114,715.80 9 5 1 $85,741.20 $98,594.10 $98,814.60 43 21 5 $77,802.20 $96,602.30 $104,493.60 16 4 2 $73,832.70 $108,553.10 $130,617.00 8 1 1 $84,947.30 $104,569.50 $120,394.80 11 3 2 $87,329.00 $91,622.80 $61,333.20 3 1 1 $70,657.10 $89,631.00 $104,834.34 22 9 3 $78,596.10 $112,536.70 $117,657.52 7 2 1 $88,916.80 $89,232.64 $134,024.40 10 3 2 Points Per Job Job Title Facilities Manager Comp/Bene Manager HR Manager Key Job #4 Key Job #5 Key Job #6 Points Consult O*net general information about the jobs you identified. Additionally, select three other key jobs for the organization you hope to work for. Conduct a job evaluation for each of these 6 jobs based on your understanding of each position. Include the job evaluation with your submission Remember the point of the job evaluation is to allocate points to the job relative to the importance of the job functions. Look through the data provided above. Are there any issues with the data (outliers, missing data, other concerns). If so, how do you propose to handle it? Do you drop outliers? Why or why not? Calculate The Measures of Central Tendency Calculate The Measures of Central Tendency Louisville, KY Facilities Manager Comp/Bene Manager Simple Mean AVERAGE Weighted Mean SUMPRODUCT()/SUM() Trimmed Mean (20%) TRIMMEAN() .2 Range MAX() - MIN() 25th Percentile QUARTILE(1) 75th Percentile QUARTILE(3) Interquartile Range QUARTILE3 - QUARTILE1 Median MEDIAN HR Manager Determine which measure(s) of central tendency you will use when creating a market pay line. Note that you may decide to use one or more measure. Discuss the merits of your choice and clearly explain your rationale. Job Evaluation Points Next, plot the jobs on a market pay line for each of the localities. To do this, you will need to properly structure your data. The data need to be position with fewest ordered from lowest to highest in an array see the example to the points right. Your horizontal (X) axis will be the job evaluation points. Your position with the next vertical (Y) axis will be the average pay rate using the measure(s) of highest points central tendency you identified above. After you do this you will have a market pay line chart for each locality (a total of 3 charts). position with the highest points and predict pay rates for jobs. In this equation Y is the predicted pay rate for any given job. Substitute the job evaluation points for x. Subtitute the value for the y-intercept for b. To do this follow the y-intercept (b) slope (m) Louisville, KY New York, NY Fresno, CA INTERCEPT (salaries, poinINTERCEPT (salaries, poinINTERCEPT (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) Finally, predict the pay for each of the 6 jobs you identified earlier using a regression equation Y=m(x)+b from the variables you just calculated for each locality. Points Per Job Job Title Facilities Manager Comp/Bene Manager HR Manager Key Job #4 Key Job #5 Key Job #6 Predicted Salary = m(x)+b Points Louisville, KY New York, NY 0 0 0 0 0 0 If this information were from a real organization what would you suggest to the CEO regarding the location of a new facility? Consider the bottom line impact to the organiztion - how will the decision to locate a new facility in one location impact the company from a financial perspective. Are there potential business growth factors to consider? Consult reputable sources for information on labor trends in each of the markets and job growth projections (you might Job Title New York, NY Facilities Manager Comp/Bene Manager HR Manager Facilities Manager $104,183.50 $130,691.96 $149,051.03 $92,521.11 12 8 7 3 $106,267.17 $141,147.31 $150,541.54 $94,371.53 9 5 3 1 $112,518.18 $129,385.04 $129,674.40 $99,922.79 84 35 7 61 $102,099.83 $126,771.20 $137,126.95 $90,670.68 22 9 2 16 $96,890.65 $142,454.23 $171,408.69 $86,044.63 5 2 1 12 $111,476.34 $137,226.55 $157,994.10 $98,997.58 21 9 3 16 $114,601.85 $120,236.60 $80,487.56 $101,773.22 9 5 2 1 $92,723.31 $117,622.76 $137,574.10 $82,343.78 37 13 5 17 $103,141.66 $147,681.91 $154,401.97 $91,595.89 17 4 6 5 $116,685.52 $117,099.99 $175,880.22 $103,623.64 13 7 2 12 Job Title Fresno New York, NY Fresno Facilities Manager Comp/Bene Manager HR Manager Louisville, KY New York, NY Fresno, CA average pay average pay average pay average pay average pay average pay average pay average pay average pay alaries, points) ary = m(x)+b Fresno, CA Facilities Manager NY Fresno, CA Comp/Bene Manager HR Manager $116,062.19 $132,366.13 1 1 $125,347.16 $133,689.79 1 1 $114,901.56 $115,158.53 23 5 $112,580.32 $121,776.84 4 1 $126,507.78 $152,221.05 3 1 $121,865.30 $140,308.10 5 2 $106,777.21 $71,477.71 1 1 $104,455.97 $122,173.94 4 0 $131,150.27 $137,118.08 2 2 $103,991.72 $156,192.04 4 1 FRE 1.3123 1.1654 Fresno, CA Comp/Bene Manager HR Manager STUDENT NAME KimRogers Louisville, KY Organization 1 Number Incumbents Organization 2 Number Incumbents Organization 3 Number Incumbents Organization 4 Number Incumbents Organization 5 Number Incumbents Organization 6 Number Incumbents Organization 7 Number Incumbents Organization 8 Number Incumbents Organization 9 Number Incumbents Organization 10 Number Incumbents HR manager Communication manageEvent Planner $74,000.00 $56,000.00 $42,000.00 18 4 2 $75,480.00 $60,480.00 $42,420.00 9 5 1 $79,920.00 $55,440.00 $36,540.00 43 21 5 $72,520.00 $54,320.00 $38,640.00 16 4 2 $68,820.00 $61,040.00 $48,300.00 8 1 1 $79,180.00 $58,800.00 $44,520.00 11 3 2 $81,400.00 $51,520.00 $22,680.00 3 1 1 $65,860.00 $50,400.00 $38,766.00 22 9 3 $73,260.00 $63,280.00 $43,507.80 7 2 1 $82,880.00 $50,176.00 $49,560.00 10 3 2 Points Per Job Job Title Points HR manager Communication manager Event Planner Key Job #4 Key Job #5 Key Job #6 Consult O*net general information about the jobs you identified. Additionally, select three other key jobs for the organization you hope to work for. Conduct a job evaluation for each of these 6 jobs based on your understanding of each position. Include the job evaluation with your submission Remember the point of the job evaluation is to allocate points to the job relative to the importance of the job functions. Look through the data provided above. Are there any issues with the data (outliers, missing data, other concerns). If so, how do you propose to handle it? Do you drop outliers? Why or why not? Calculate The Measures of Central Tendency Calculate The Measures of Central Tendency Louisville, KY HR manager Communication manageEvent Planner Simple Mean AVERAGE Weighted Mean SUMPRODUCT()/SUM() Trimmed Mean (20%) TRIMMEAN() .2 Range MAX() - MIN() 25th Percentile QUARTILE(1) 75th Percentile QUARTILE(3) Interquartile Range QUARTILE3 - QUARTILE1 Median MEDIAN Determine which measure(s) of central tendency you will use when creating a market pay line. Note that you may decide to use one or more measure. Discuss the merits of your choice and clearly explain your rationale. Job Evaluation Points Next, plot the jobs on a market pay line for each of the localities. To do this, you will need to properly structure your data. The data need to be position with fewest ordered from lowest to highest in an array see the example to the points right. Your horizontal (X) axis will be the job evaluation points. Your position with the next vertical (Y) axis will be the average pay rate using the measure(s) of highest points central tendency you identified above. After you do this you will have a market pay line chart for each locality (a total of 3 charts). position with the highest points and predict pay rates for jobs. In this equation Y is the predicted pay rate for any given job. Substitute the job evaluation points for x. Subtitute the value for the y-intercept for b. To do this follow the y-intercept (b) slope (m) Louisville, KY New York, NY Fresno, CA INTERCEPT (salaries, poinINTERCEPT (salaries, poinINTERCEPT (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) Finally, predict the pay for each of the 6 jobs you identified earlier using a regression equation Y=m(x)+b from the variables you just calculated for each locality. Points Per Job Job Title Points HR manager Communication manage Event Planner Key Job #4 Key Job #5 Key Job #6 Predicted Salary = m(x)+b Louisville, KY New York, NY 0 0 0 0 0 0 If this information were from a real organization what would you suggest to the CEO regarding the location of a new facility? Consider the bottom line impact to the organiztion - how will the decision to locate a new facility in one location impact the company from a financial perspective. Are there potential business growth factors to consider? Consult reputable sources for information on labor trends in each of the markets and job growth projections (you might Job Title New York, NY HR manager Communication manageEvent Planner HR manager $97,110.20 $73,488.80 $55,116.60 $86,239.60 12 8 7 3 $99,052.40 $79,367.90 $55,667.77 $87,964.39 9 5 3 1 $104,879.02 $72,753.91 $47,951.44 $93,138.77 84 35 7 61 $95,168.00 $71,284.14 $50,707.27 $84,514.81 22 9 2 16 $90,312.49 $80,102.79 $63,384.09 $80,202.83 5 2 1 12 $103,907.91 $77,163.24 $58,423.60 $92,276.37 21 9 3 16 $106,821.22 $67,609.70 $29,762.96 $94,863.56 9 5 2 1 $86,428.08 $66,139.92 $50,872.62 $76,753.24 37 13 5 17 $96,139.10 $83,042.34 $57,095.29 $85,377.20 17 4 6 5 $108,763.42 $65,845.96 $65,037.59 $96,588.35 13 7 2 12 Job Title Fresno New York, NY Fresno HR manager Communication manageEvent Planner Louisville, KY New York, NY Fresno, CA average pay average pay average pay average pay average pay average pay average pay average pay average pay alaries, points) ary = m(x)+b Fresno, CA HR manager NY Fresno, CA Communication manageEvent Planner $65,262.40 $48,946.80 1 1 $70,483.39 $49,436.27 1 1 $64,609.78 $42,583.72 23 5 $63,304.53 $45,031.06 4 1 $71,136.02 $56,288.82 3 1 $68,525.52 $51,883.61 5 2 $60,041.41 $26,431.27 1 1 $58,736.16 $45,177.90 4 0 $73,746.51 $50,703.99 2 2 $58,475.11 $57,757.22 4 1 FRE 1.3123 1.1654 Fresno, CA Communication manageEvent Planner STUDENT NAME KimRogers Louisville, KY Organization 1 Number Incumbents Organization 2 Number Incumbents Organization 3 Number Incumbents Organization 4 Number Incumbents Organization 5 Number Incumbents Organization 6 Number Incumbents Organization 7 Number Incumbents Organization 8 Number Incumbents Organization 9 Number Incumbents Organization 10 Number Incumbents HR Coordinator HR Manager HR Director $43,810.00 $71,910.00 18 4 $44,686.20 $77,662.80 9 5 $47,314.80 $71,190.90 43 21 $42,933.80 $69,752.70 16 4 $40,743.30 $78,381.90 8 1 $46,876.70 $75,505.50 11 3 $48,191.00 $66,157.20 3 1 $38,990.90 $64,719.00 22 9 $43,371.90 $81,258.30 7 2 $49,067.20 $64,431.36 10 3 Points Per Job Job Title HR Coordinator HR Manager HR Director Key Job #4 Key Job #5 Key Job #6 Points Consult O*net general information about the jobs you identified. Additionally, select three other key jobs for the organization you hope to work for. Conduct a job evaluation for each of these 6 jobs based on your understanding of each position. Include the job evaluation with your submission Remember the point of the job evaluation is to allocate points to the job relative to the importance of the job functions. Look through the data provided above. Are there any issues with the data (outliers, missing data, other concerns). If so, how do you propose to handle it? Do you drop outliers? Why or why not? Calculate The Measures of Central Tendency $80,687.00 2 $81,493.87 1 $70,197.69 5 $74,232.04 2 $92,790.05 1 $85,528.22 2 $43,570.98 1 $74,474.10 3 $83,583.66 1 $95,210.66 2 Calculate The Measures of Central Tendency Louisville, KY HR Coordinator HR Manager Simple Mean AVERAGE Weighted Mean SUMPRODUCT()/SUM() Trimmed Mean (20%) TRIMMEAN() .2 Range MAX() - MIN() 25th Percentile QUARTILE(1) 75th Percentile QUARTILE(3) Interquartile Range QUARTILE3 - QUARTILE1 Median MEDIAN HR Director Determine which measure(s) of central tendency you will use when creating a market pay line. Note that you may decide to use one or more measure. Discuss the merits of your choice and clearly explain your rationale. Next, plot the jobs on a market pay line for each of the localities. To do this, you will need to properly structure your data. The data need to be ordered from lowest to highest in an array see the example to the right. Your horizontal (X) axis will be the job evaluation points. Your vertical (Y) axis will be the average pay rate using the measure(s) of central tendency you identified above. After you do this you will have a market pay line chart for each locality (a total of 3 charts). Job Evaluation Points position with fewest points position with the next highest points position with the highest points predict pay rates for jobs. In this equation Y is the predicted pay rate for any given job. Substitute the job evaluation points for x. Subtitute the value for the y-intercept for b. To do this follow the formulas provided y-intercept (b) slope (m) Louisville, KY New York, NY Fresno, CA INTERCEPT (salaries, poinINTERCEPT (salaries, poinINTERCEPT (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) Finally, predict the pay for each of the 6 jobs you identified earlier using a regression equation Y=m(x)+b from the variables you just calculated for each locality. Points Per Job Job Title HR Coordinator HR Manager HR Director Key Job #4 Key Job #5 Key Job #6 Predicted Salary = m(x)+b Points Louisville, KY New York, NY 0 0 0 0 0 0 If this information were from a real organization what would you suggest to the CEO regarding the location of a new facility? Consider the bottom line impact to the organiztion - how will the decision to locate a new facility in one location impact the company from a financial perspective. Are there potential business growth factors to consider? Consult reputable sources for information on labor trends in each of the markets and job growth projections (you might Job Title New York, NY HR Coordinator HR Manager $57,491.86 12 $58,641.70 9 $62,091.21 84 $56,342.03 22 $53,467.43 5 $61,516.29 21 $63,241.05 9 $51,167.76 37 $56,916.94 17 $64,390.89 13 HR Director $94,367.49 $105,885.55 8 7 $101,916.89 $106,944.41 5 3 $93,423.82 $92,120.43 35 7 $91,536.47 $97,414.71 9 2 $102,860.57 $121,768.38 2 1 $99,085.87 $112,238.68 9 3 $86,818.09 $57,178.20 5 2 $84,930.74 $97,732.36 13 5 $106,635.27 $109,686.84 4 6 $84,553.27 $124,944.95 7 2 Job Title New York, NY HR Coordinator HR Manager HR Director Louisville, KY New York, NY Fresno, CA average pay average pay average pay average pay average pay average pay average pay average pay average pay alaries, points) ary = m(x)+b Fresno, CA Fresno, CA HR Coordinator HR Manager $51,056.17 3 $52,077.30 1 $55,140.67 61 $50,035.05 16 $47,482.24 12 $54,630.11 16 $56,161.79 1 $45,439.99 17 $50,545.61 5 $57,182.91 12 HR Director $83,803.91 1 $90,508.23 1 $82,965.87 23 $81,289.80 4 $91,346.27 3 $87,994.11 5 $77,099.60 1 $75,423.52 4 $94,698.42 2 $75,088.31 4 $94,032.63 1 $94,972.96 1 $81,808.39 5 $86,510.02 1 $108,137.52 1 $99,674.59 2 $50,777.62 1 $86,792.12 0 $97,408.40 2 $110,958.50 1 Fresno, CA HR Coordinator HR Manager HR Director NY FRE 1.3123 1.1654 STUDENT NAME KimRogers Louisville, KY Organization 1 Number Incumbents Organization 2 Number Incumbents Organization 3 Number Incumbents Organization 4 Number Incumbents Organization 5 Number Incumbents Organization 6 Number Incumbents Organization 7 Number Incumbents Organization 8 Number Incumbents Organization 9 Number Incumbents Organization 10 Number Incumbents HR Generalist HR manager HR Director $48,814.00 $71,910.00 18 4 $49,790.28 $77,662.80 9 5 $52,719.12 $71,190.90 43 21 $47,837.72 $69,752.70 16 4 $45,397.02 $78,381.90 8 1 $52,230.98 $75,505.50 11 3 $53,695.40 $66,157.20 3 1 $43,444.46 $64,719.00 22 9 $48,325.86 $81,258.30 7 2 $54,671.68 $64,431.36 10 3 Points Per Job Job Title HR Generalist HR manager HR Director Key Job #4 Key Job #5 Key Job #6 Points Consult O*net general information about the jobs you identified. Additionally, select three other key jobs for the organization you hope to work for. Conduct a job evaluation for each of these 6 jobs based on your understanding of each position. Include the job evaluation with your submission Remember the point of the job evaluation is to allocate points to the job relative to the importance of the job functions. Look through the data provided above. Are there any issues with the data (outliers, missing data, other concerns). If so, how do you propose to handle it? Do you drop outliers? Why or why not? Calculate The Measures of Central Tendency $80,687.00 2 $81,493.87 1 $70,197.69 5 $74,232.04 2 $92,790.05 1 $85,528.22 2 $43,570.98 1 $74,474.10 3 $83,583.66 1 $95,210.66 2 Calculate The Measures of Central Tendency Louisville, KY HR Generalist HR manager Simple Mean AVERAGE Weighted Mean SUMPRODUCT()/SUM() Trimmed Mean (20%) TRIMMEAN() .2 Range MAX() - MIN() 25th Percentile QUARTILE(1) 75th Percentile QUARTILE(3) Interquartile Range QUARTILE3 - QUARTILE1 Median MEDIAN HR Director Determine which measure(s) of central tendency you will use when creating a market pay line. Note that you may decide to use one or more measure. Discuss the merits of your choice and clearly explain your rationale. Job Evaluation Points Next, plot the jobs on a market pay line for each of the localities. To do this, you will need to properly structure your data. The data need to be position with fewest ordered from lowest to highest in an array see the example to the points right. Your horizontal (X) axis will be the job evaluation points. Your position with the next vertical (Y) axis will be the average pay rate using the measure(s) of highest points central tendency you identified above. After you do this you will have a market pay line chart for each locality (a total of 3 charts). position with the highest points and predict pay rates for jobs. In this equation Y is the predicted pay rate for any given job. Substitute the job evaluation points for x. Subtitute the value for the y-intercept for b. To do this follow the y-intercept (b) slope (m) Louisville, KY New York, NY Fresno, CA INTERCEPT (salaries, poinINTERCEPT (salaries, poinINTERCEPT (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) Finally, predict the pay for each of the 6 jobs you identified earlier using a regression equation Y=m(x)+b from the variables you just calculated for each locality. Points Per Job Job Title HR Generalist HR manager HR Director Key Job #4 Key Job #5 Key Job #6 Predicted Salary = m(x)+b Points Louisville, KY New York, NY 0 0 0 0 0 0 If this information were from a real organization what would you suggest to the CEO regarding the location of a new facility? Consider the bottom line impact to the organiztion - how will the decision to locate a new facility in one location impact the company from a financial perspective. Are there potential business growth factors to consider? Consult reputable sources for information on labor trends in each of the markets and job growth projections (you might Job Title New York, NY HR Generalist HR manager HR Director HR Generalist $64,058.61 $94,367.49 $105,885.55 $56,887.84 12 8 7 3 $65,339.78 $101,916.89 $106,944.41 $58,025.59 9 5 3 1 $69,183.30 $93,423.82 $92,120.43 $61,438.86 84 35 7 61 $62,777.44 $91,536.47 $97,414.71 $55,750.08 22 9 2 16 $59,574.51 $102,860.57 $121,768.38 $52,905.69 5 2 1 12 $68,542.72 $99,085.87 $112,238.68 $60,869.98 21 9 3 16 $70,464.47 $86,818.09 $57,178.20 $62,576.62 9 5 2 1 $57,012.16 $84,930.74 $97,732.36 $50,630.17 37 13 5 17 $63,418.03 $106,635.27 $109,686.84 $56,318.96 17 4 6 5 $71,745.65 $84,553.27 $124,944.95 $63,714.38 13 7 2 12 Job Title Fresno New York, NY Fresno HR Generalist HR manager HR Director Louisville, KY New York, NY Fresno, CA average pay average pay average pay average pay average pay average pay average pay average pay average pay alaries, points) ary = m(x)+b Fresno, CA HR Generalist NY Fresno, CA HR manager 1.3123 HR Director $83,803.91 1 $90,508.23 1 $82,965.87 23 $81,289.80 4 $91,346.27 3 $87,994.11 5 $77,099.60 1 $75,423.52 4 $94,698.42 2 $75,088.31 4 FRE $94,032.63 1 $94,972.96 1 $81,808.39 5 $86,510.02 1 $108,137.52 1 $99,674.59 2 $50,777.62 1 $86,792.12 0 $97,408.40 2 $110,958.50 1 1.1654 Fresno, CA HR manager HR Director STUDENT NAME KimRogers Louisville, KY Organization 1 Number Incumbents Organization 2 Number Incumbents Organization 3 Number Incumbents Organization 4 Number Incumbents Organization 5 Number Incumbents Organization 6 Number Incumbents Organization 7 Number Incumbents Organization 8 Number Incumbents Organization 9 Number Incumbents Organization 10 Number Incumbents HR Generalist HR Specialist HR manager $39,000.00 $44,500.00 $87,000.00 18 4 2 $39,780.00 $48,060.00 $87,870.00 9 5 1 $42,120.00 $44,055.00 $75,690.00 43 21 5 $38,220.00 $43,165.00 $80,040.00 16 4 2 $36,270.00 $48,505.00 $100,050.00 8 1 1 $41,730.00 $46,725.00 $92,220.00 11 3 2 $42,900.00 $40,940.00 $46,980.00 3 1 1 $34,710.00 $40,050.00 $80,301.00 22 9 3 $38,610.00 $50,285.00 $90,123.30 7 2 1 $43,680.00 $39,872.00 $102,660.00 10 3 2 Points Per Job Job Title HR Generalist HR Specialist HR manager Key Job #4 Key Job #5 Key Job #6 Points Consult O*net general information about the jobs you identified. Additionally, select three other key jobs for the organization you hope to work for. Conduct a job evaluation for each of these 6 jobs based on your understanding of each position. Include the job evaluation with your submission Remember the point of the job evaluation is to allocate points to the job relative to the importance of the job functions. Look through the data provided above. Are there any issues with the data (outliers, missing data, other concerns). If so, how do you propose to handle it? Do you drop outliers? Why or why not? Calculate The Measures of Central Tendency Calculate The Measures of Central Tendency Louisville, KY HR Generalist HR Specialist Simple Mean AVERAGE Weighted Mean SUMPRODUCT()/SUM() Trimmed Mean (20%) TRIMMEAN() .2 Range MAX() - MIN() 25th Percentile QUARTILE(1) 75th Percentile QUARTILE(3) Interquartile Range QUARTILE3 - QUARTILE1 Median MEDIAN HR manager Determine which measure(s) of central tendency you will use when creating a market pay line. Note that you may decide to use one or more measure. Discuss the merits of your choice and clearly explain your rationale. Job Evaluation Points Next, plot the jobs on a market pay line for each of the localities. To do this, you will need to properly structure your data. The data need to be position with fewest ordered from lowest to highest in an array see the example to the points right. Your horizontal (X) axis will be the job evaluation points. Your position with the next vertical (Y) axis will be the average pay rate using the measure(s) of highest points central tendency you identified above. After you do this you will have a market pay line chart for each locality (a total of 3 charts). position with the highest points and predict pay rates for jobs. In this equation Y is the predicted pay rate for any given job. Substitute the job evaluation points for x. Subtitute the value for the y-intercept for b. To do this follow the y-intercept (b) slope (m) Louisville, KY New York, NY Fresno, CA INTERCEPT (salaries, poinINTERCEPT (salaries, poinINTERCEPT (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) Finally, predict the pay for each of the 6 jobs you identified earlier using a regression equation Y=m(x)+b from the variables you just calculated for each locality. Points Per Job Job Title HR Generalist HR Specialist HR manager Key Job #4 Key Job #5 Key Job #6 Predicted Salary = m(x)+b Points Louisville, KY New York, NY 0 0 0 0 0 0 If this information were from a real organization what would you suggest to the CEO regarding the location of a new facility? Consider the bottom line impact to the organiztion - how will the decision to locate a new facility in one location impact the company from a financial perspective. Are there potential business growth factors to consider? Consult reputable sources for information on labor trends in each of the markets and job growth projections (you might Job Title New York, NY HR Generalist HR Specialist HR manager HR Generalist $51,179.70 $58,397.35 $114,170.10 $45,450.60 12 8 7 3 $52,203.29 $63,069.14 $115,311.80 $46,359.61 9 5 3 1 $55,274.08 $57,813.38 $99,327.99 $49,086.65 84 35 7 61 $50,156.11 $56,645.43 $105,036.49 $44,541.59 22 9 2 16 $47,597.12 $63,653.11 $131,295.62 $42,269.06 5 2 1 12 $54,762.28 $61,317.22 $121,020.31 $48,632.14 21 9 3 16 $56,297.67 $53,725.56 $61,651.85 $49,995.66 9 5 2 1 $45,549.93 $52,557.62 $105,379.00 $40,451.03 37 13 5 17 $50,667.90 $65,989.01 $118,268.81 $44,996.09 17 4 6 5 $57,321.26 $52,324.03 $134,720.72 $50,904.67 13 7 2 12 Job Title Fresno New York, NY Fresno HR Generalist HR Specialist HR manager Louisville, KY New York, NY Fresno, CA average pay average pay average pay average pay average pay average pay average pay average pay average pay alaries, points) ary = m(x)+b Fresno, CA HR Generalist NY Fresno, CA HR Specialist HR manager $51,860.30 $101,389.80 1 1 $56,009.12 $102,403.70 1 1 $51,341.70 $88,209.13 23 5 $50,304.49 $93,278.62 4 1 $56,527.73 $116,598.27 3 1 $54,453.32 $107,473.19 5 2 $47,711.48 $54,750.49 1 1 $46,674.27 $93,582.79 4 0 $58,602.14 $105,029.69 2 2 $46,466.83 $119,639.96 4 1 FRE 1.3123 1.1654 Fresno, CA HR Specialist HR manager STUDENT NAME KimRogers Louisville, KY Organization 1 Number Incumbents Organization 2 Number Incumbents Organization 3 Number Incumbents Organization 4 Number Incumbents Organization 5 Number Incumbents Organization 6 Number Incumbents Organization 7 Number Incumbents Organization 8 Number Incumbents Organization 9 Number Incumbents Organization 10 Number Incumbents Section Leader Section Leader CoordinaDistrict Engineering ma $85,000.00 $105,000.00 $125,000.00 18 4 2 $86,700.00 $113,400.00 $126,250.00 9 5 1 $91,800.00 $103,950.00 $108,750.00 43 21 5 $83,300.00 $101,850.00 $115,000.00 16 4 2 $79,050.00 $114,450.00 $143,750.00 8 1 1 $90,950.00 $110,250.00 $132,500.00 11 3 2 $93,500.00 $96,600.00 $67,500.00 3 1 1 $75,650.00 $94,500.00 $115,375.00 22 9 3 $84,150.00 $118,650.00 $129,487.50 7 2 1 $95,200.00 $94,080.00 $147,500.00 10 3 2 Points Per Job Job Title Points Section Leader Section Leader Coordinator District Engineering manager Key Job #4 Key Job #5 Key Job #6 Consult O*net general information about the jobs you identified. Additionally, select three other key jobs for the organization you hope to work for. Conduct a job evaluation for each of these 6 jobs based on your understanding of each position. Include the job evaluation with your submission Remember the point of the job evaluation is to allocate points to the job relative to the importance of the job functions. Look through the data provided above. Are there any issues with the data (outliers, missing data, other concerns). If so, how do you propose to handle it? Do you drop outliers? Why or why not? Calculate The Measures of Central Tendency Calculate The Measures of Central Tendency Louisville, KY Section Leader Section Leader CoordinaDistrict Engineering ma Simple Mean AVERAGE Weighted Mean SUMPRODUCT()/SUM() Trimmed Mean (20%) TRIMMEAN() .2 Range MAX() - MIN() 25th Percentile QUARTILE(1) 75th Percentile QUARTILE(3) Interquartile Range QUARTILE3 - QUARTILE1 Median MEDIAN Determine which measure(s) of central tendency you will use when creating a market pay line. Note that you may decide to use one or more measure. Discuss the merits of your choice and clearly explain your rationale. Job Evaluation Points Next, plot the jobs on a market pay line for each of the localities. To do this, you will need to properly structure your data. The data need to be position with fewest ordered from lowest to highest in an array see the example to the points right. Your horizontal (X) axis will be the job evaluation points. Your position with the next vertical (Y) axis will be the average pay rate using the measure(s) of highest points central tendency you identified above. After you do this you will have a market pay line chart for each locality (a total of 3 charts). position with the highest points and predict pay rates for jobs. In this equation Y is the predicted pay rate for any given job. Substitute the job evaluation points for x. Subtitute the value for the y-intercept for b. To do this follow the y-intercept (b) slope (m) Louisville, KY New York, NY Fresno, CA INTERCEPT (salaries, poinINTERCEPT (salaries, poinINTERCEPT (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) Finally, predict the pay for each of the 6 jobs you identified earlier using a regression equation Y=m(x)+b from the variables you just calculated for each locality. Points Per Job Job Title Points Section Leader Section Leader Coordina District Engineering man Key Job #4 Key Job #5 Key Job #6 Predicted Salary = m(x)+b Louisville, KY New York, NY 0 0 0 0 0 0 If this information were from a real organization what would you suggest to the CEO regarding the location of a new facility? Consider the bottom line impact to the organiztion - how will the decision to locate a new facility in one location impact the company from a financial perspective. Are there potential business growth factors to consider? Consult reputable sources for information on labor trends in each of the markets and job growth projections (you might Job Title New York, NY Section Leader Section Leader CoordinaDistrict Engineering ma Section Leader $111,545.50 $137,791.50 $164,037.50 $99,059.00 12 8 7 3 $113,776.41 $148,814.82 $165,677.88 $101,040.18 9 5 3 1 $120,469.14 $136,413.59 $142,712.63 $106,983.72 84 35 7 61 $109,314.59 $133,657.76 $150,914.50 $97,077.82 22 9 2 16 $103,737.32 $150,192.74 $188,643.13 $92,124.87 5 2 1 12 $119,353.69 $144,681.08 $173,879.75 $105,993.13 21 9 3 16 $122,700.05 $126,768.18 $88,580.25 $108,964.90 9 5 2 1 $99,275.50 $124,012.35 $151,406.61 $88,162.51 37 13 5 17 $110,430.05 $155,704.40 $169,926.45 $98,068.41 17 4 6 5 $124,930.96 $123,461.18 $193,564.25 $110,946.08 13 7 2 12 Job Title Fresno New York, NY Fresno Section Leader Section Leader CoordinaDistrict Engineering ma Section Leader Louisville, KY New York, NY Fresno, CA average pay average pay average pay average pay average pay average pay average pay average pay average pay alaries, points) ary = m(x)+b Fresno, CA NY Fresno, CA Section Leader CoordinaDistrict Engineering manager $122,367.00 $145,675.00 1 1 $132,156.36 $147,131.75 1 1 $121,143.33 $126,737.25 23 5 $118,695.99 $134,021.00 4 1 $133,380.03 $167,526.25 3 1 $128,485.35 $154,415.50 5 2 $112,577.64 $78,664.50 1 1 $110,130.30 $134,458.03 4 0 $138,274.71 $150,904.73 2 2 $109,640.83 $171,896.50 4 1 FRE 1.3123 1.1654 Fresno, CA Section Leader CoordinaDistrict Engineering manager STUDENT NAME KimRogers Louisville, KY Organization 1 Number Incumbents Organization 2 Number Incumbents Organization 3 Number Incumbents Organization 4 Number Incumbents Organization 5 Number Incumbents Organization 6 Number Incumbents Organization 7 Number Incumbents Organization 8 Number Incumbents Organization 9 Number Incumbents Organization 10 Number Incumbents HR Generalist HR Supervisor Manager of Training $53,807.00 $77,000.00 $99,200.00 18 4 2 $54,883.14 $83,160.00 $100,192.00 9 5 1 $58,111.56 $76,230.00 $86,304.00 43 21 5 $52,730.86 $74,690.00 $91,264.00 16 4 2 $50,040.51 $83,930.00 $114,080.00 8 1 1 $57,573.49 $80,850.00 $105,152.00 11 3 2 $59,187.70 $70,840.00 $53,568.00 3 1 1 $47,888.23 $69,300.00 $91,561.60 22 9 3 $53,268.93 $87,010.00 $102,761.28 7 2 1 $60,263.84 $68,992.00 $117,056.00 10 3 2 Points Per Job Job Title Points HR Generalist HR Supervisor Manager of Training Development Key Job #4 Key Job #5 Key Job #6 Consult O*net general information about the jobs you identified. Additionally, select three other key jobs for the organization you hope to work for. Conduct a job evaluation for each of these 6 jobs based on your understanding of each position. Include the job evaluation with your submission Remember the point of the job evaluation is to allocate points to the job relative to the importance of the job functions. Look through the data provided above. Are there any issues with the data (outliers, missing data, other concerns). If so, how do you propose to handle it? Do you drop outliers? Why or why not? Calculate The Measures of Central Tendency Calculate The Measures of Central Tendency Louisville, KY HR Generalist HR Supervisor Simple Mean AVERAGE Weighted Mean SUMPRODUCT()/SUM() Trimmed Mean (20%) TRIMMEAN() .2 Range MAX() - MIN() 25th Percentile QUARTILE(1) 75th Percentile QUARTILE(3) Interquartile Range QUARTILE3 - QUARTILE1 Median MEDIAN Manager of Training Determine which measure(s) of central tendency you will use when creating a market pay line. Note that you may decide to use one or more measure. Discuss the merits of your choice and clearly explain your rationale. Job Evaluation Points Next, plot the jobs on a market pay line for each of the localities. To do this, you will need to properly structure your data. The data need to be position with fewest ordered from lowest to highest in an array see the example to the points right. Your horizontal (X) axis will be the job evaluation points. Your position with the next vertical (Y) axis will be the average pay rate using the measure(s) of highest points central tendency you identified above. After you do this you will have a market pay line chart for each locality (a total of 3 charts). position with the highest points and predict pay rates for jobs. In this equation Y is the predicted pay rate for any given job. Substitute the job evaluation points for x. Subtitute the value for the y-intercept for b. To do this follow the y-intercept (b) slope (m) Louisville, KY New York, NY Fresno, CA INTERCEPT (salaries, poinINTERCEPT (salaries, poinINTERCEPT (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) Finally, predict the pay for each of the 6 jobs you identified earlier using a regression equation Y=m(x)+b from the variables you just calculated for each locality. Points Per Job Job Title Points HR Generalist HR Supervisor Manager of Training Key Job #4 Key Job #5 Key Job #6 Predicted Salary = m(x)+b Louisville, KY New York, NY 0 0 0 0 0 0 If this information were from a real organization what would you suggest to the CEO regarding the location of a new facility? Consider the bottom line impact to the organiztion - how will the decision to locate a new facility in one location impact the company from a financial perspective. Are there potential business growth factors to consider? Consult reputable sources for information on labor trends in each of the markets and job growth projections (you might Job Title New York, NY HR Generalist HR Supervisor Manager of Training HR Generalist $70,610.93 $101,047.10 $130,180.16 $62,706.68 12 8 7 3 $72,023.14 $109,130.87 $131,481.96 $63,960.81 9 5 3 1 $76,259.80 $100,036.63 $113,256.74 $67,723.21 84 35 7 61 $69,198.71 $98,015.69 $119,765.75 $61,452.54 22 9 2 16 $65,668.16 $110,141.34 $149,707.18 $58,317.21 5 2 1 12 $75,553.69 $106,099.46 $137,990.97 $67,096.15 21 9 3 16 $77,672.02 $92,963.33 $70,297.29 $68,977.35 9 5 2 1 $62,843.72 $90,942.39 $120,156.29 $55,808.94 37 13 5 17 $69,904.82 $114,183.22 $134,853.63 $62,079.61 17 4 6 5 $79,084.24 $90,538.20 $153,612.59 $70,231.48 13 7 2 12 Job Title Fresno New York, NY Fresno HR Generalist HR Supervisor Manager of Training HR Generalist Louisville, KY New York, NY Fresno, CA average pay average pay average pay average pay average pay average pay average pay average pay average pay alaries, points) ary = m(x)+b Fresno, CA NY Fresno, CA HR Supervisor Manager of Training Development $89,735.80 $115,607.68 1 1 $96,914.66 $116,763.76 1 1 $88,838.44 $100,578.68 23 5 $87,043.73 $106,359.07 4 1 $97,812.02 $132,948.83 3 1 $94,222.59 $122,544.14 5 2 $82,556.94 $62,428.15 1 1 $80,762.22 $106,705.89 4 0 $101,401.45 $119,758.00 2 2 $80,403.28 $136,417.06 4 1 FRE 1.3123 1.1654 Fresno, CA HR Supervisor Manager of Training Development STUDENT NAME KimRogers Louisville, KY Admin Asst Organization 1 Number Incumbents Organization 2 Number Incumbents Organization 3 Number Incumbents Organization 4 Number Incumbents Organization 5 Number Incumbents Organization 6 Number Incumbents Organization 7 Number Incumbents Organization 8 Number Incumbents Organization 9 Number Incumbents Organization 10 Number Incumbents Manager $39,852.00 18 $40,649.04 9 $43,040.16 43 $39,054.96 16 $37,062.36 8 $42,641.64 11 $43,837.20 3 $35,468.28 22 $39,453.48 7 $44,634.24 10 Points Per Job Job Title Admin Asst Manager CEO Key Job #4 Key Job #5 Key Job #6 Points CEO $98,529.00 4 $106,411.32 5 $97,543.71 21 $95,573.13 4 $107,396.61 1 $103,455.45 3 $90,646.68 1 $88,676.10 9 $111,337.77 2 $88,281.98 3 Consult O*net general information about the jobs you identified. Additionally, select three other key jobs for the organization you hope to work for. Conduct a job evaluation for each of these 6 jobs based on your understanding of each position. Include the job evaluation with your submission Remember the point of the job evaluation is to allocate points to the job relative to the importance of the job functions. Look through the data provided above. Are there any issues with the data (outliers, missing data, other concerns). If so, how do you propose to handle it? Do you drop outliers? Why or why not? Calculate The Measures of Central Tendency $253,000.00 2 $255,530.00 1 $220,110.00 5 $232,760.00 2 $290,950.00 1 $268,180.00 2 $136,620.00 1 $233,519.00 3 $262,082.70 1 $298,540.00 2 Calculate The Measures of Central Tendency Louisville, KY Admin Asst Manager Simple Mean AVERAGE Weighted Mean SUMPRODUCT()/SUM() Trimmed Mean (20%) TRIMMEAN() .2 Range MAX() - MIN() 25th Percentile QUARTILE(1) 75th Percentile QUARTILE(3) Interquartile Range QUARTILE3 - QUARTILE1 Median MEDIAN CEO Determine which measure(s) of central tendency you will use when creating a market pay line. Note that you may decide to use one or more measure. Discuss the merits of your choice and clearly explain your rationale. Job Evaluation Points Next, plot the jobs on a market pay line for each of the localities. To do this, you will need to properly structure your data. The data need to be position with fewest ordered from lowest to highest in an array see the example to the points right. Your horizontal (X) axis will be the job evaluation points. Your position with the next vertical (Y) axis will be the average pay rate using the measure(s) of highest points central tendency you identified above. After you do this you will have a market pay line chart for each locality (a total of 3 charts). position with the highest points and predict pay rates for jobs. In this equation Y is the predicted pay rate for any given job. Substitute the job evaluation points for x. Subtitute the value for the y-intercept for b. To do this follow the y-intercept (b) slope (m) Louisville, KY New York, NY Fresno, CA INTERCEPT (salaries, poinINTERCEPT (salaries, poinINTERCEPT (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) Finally, predict the pay for each of the 6 jobs you identified earlier using a regression equation Y=m(x)+b from the variables you just calculated for each locality. Points Per Job Job Title Admin Asst Manager CEO Key Job #4 Key Job #5 Key Job #6 Predicted Salary = m(x)+b Points Louisville, KY New York, NY 0 0 0 0 0 0 If this information were from a real organization what would you suggest to the CEO regarding the location of a new facility? Consider the bottom line impact to the organiztion - how will the decision to locate a new facility in one location impact the company from a financial perspective. Are there potential business growth factors to consider? Consult reputable sources for information on labor trends in each of the markets and job growth projections (you might Job Title New York, NY Admin Asst Manager $52,297.78 12 $53,343.74 9 $56,481.60 84 $51,251.82 22 $48,636.94 5 $55,958.62 21 $57,527.56 9 $46,545.02 37 $51,774.80 17 $58,573.51 13 Fresno CEO $129,299.61 8 $139,643.58 5 $128,006.61 35 $125,420.62 9 $140,936.57 2 $135,764.59 9 $118,955.64 5 $116,369.65 13 $146,108.56 4 $115,852.45 7 Job Title Admin Asst $332,011.90 7 $335,332.02 3 $288,850.35 7 $305,450.95 2 $381,813.69 1 $351,932.61 3 $179,286.43 2 $306,446.98 5 $343,931.13 6 $391,774.04 2 $46,443.52 3 $47,372.39 1 $50,159.00 61 $45,514.65 16 $43,192.47 12 $49,694.57 16 $51,087.87 1 $41,334.73 17 $45,979.09 5 $52,016.74 12 New York, NY Fresno Admin Asst Manager CEO Louisville, KY New York, NY Fresno, CA average pay average pay average pay average pay average pay average pay average pay average pay average pay alaries, points) ary = m(x)+b Fresno, CA Admin Asst NY Fresno, CA Manager 1.3123 CEO $114,825.70 1 $124,011.75 1 $113,677.44 23 $111,380.93 4 $125,160.01 3 $120,566.98 5 $105,639.64 1 $103,343.13 4 $129,753.04 2 $102,883.82 4 FRE $294,846.20 1 $297,794.66 1 $256,516.19 5 $271,258.50 1 $339,073.13 1 $312,536.97 2 $159,216.95 1 $272,143.04 0 $305,431.18 2 $347,918.52 1 1.1654 Fresno, CA Manager CEO STUDENT NAME KimRogers Louisville, KY Teller Organization 1 Number Incumbents Organization 2 Number Incumbents Organization 3 Number Incumbents Organization 4 Number Incumbents Organization 5 Number Incumbents Organization 6 Number Incumbents Organization 7 Number Incumbents Organization 8 Number Incumbents Organization 9 Number Incumbents Organization 10 Number Incumbents Loan Officer Branch Manager $30,537.00 $73,016.00 $55,487.00 18 4 2 $31,147.74 $78,857.28 $56,041.87 9 5 1 $32,979.96 $72,285.84 $48,273.69 43 21 5 $29,926.26 $70,825.52 $51,048.04 16 4 2 $28,399.41 $79,587.44 $63,810.05 8 1 1 $32,674.59 $76,666.80 $58,816.22 11 3 2 $33,590.70 $67,174.72 $29,962.98 3 1 1 $27,177.93 $65,714.40 $51,214.50 22 9 3 $30,231.63 $82,508.08 $57,478.98 7 2 1 $34,201.44 $65,422.34 $65,474.66 10 3 2 Points Per Job Job Title Teller Loan Officer Branch Manager Key Job #4 Key Job #5 Key Job #6 Points Consult O*net general information about the jobs you identified. Additionally, select three other key jobs for the organization you hope to work for. Conduct a job evaluation for each of these 6 jobs based on your understanding of each position. Include the job evaluation with your submission Remember the point of the job evaluation is to allocate points to the job relative to the importance of the job functions. Look through the data provided above. Are there any issues with the data (outliers, missing data, other concerns). If so, how do you propose to handle it? Do you drop outliers? Why or why not? Calculate The Measures of Central Tendency Calculate The Measures of Central Tendency Louisville, KY Teller Loan Officer Simple Mean AVERAGE Weighted Mean SUMPRODUCT()/SUM() Trimmed Mean (20%) TRIMMEAN() .2 Range MAX() - MIN() 25th Percentile QUARTILE(1) 75th Percentile QUARTILE(3) Interquartile Range QUARTILE3 - QUARTILE1 Median MEDIAN Branch Manager Determine which measure(s) of central tendency you will use when creating a market pay line. Note that you may decide to use one or more measure. Discuss the merits of your choice and clearly explain your rationale. Job Evaluation Points Next, plot the jobs on a market pay line for each of the localities. To do this, you will need to properly structure your data. The data need to be position with fewest ordered from lowest to highest in an array see the example to the points right. Your horizontal (X) axis will be the job evaluation points. Your position with the next vertical (Y) axis will be the average pay rate using the measure(s) of highest points central tendency you identified above. After you do this you will have a market pay line chart for each locality (a total of 3 charts). position with the highest points and predict pay rates for jobs. In this equation Y is the predicted pay rate for any given job. Substitute the job evaluation points for x. Subtitute the value for the y-intercept for b. To do this follow the y-intercept (b) slope (m) Louisville, KY New York, NY Fresno, CA INTERCEPT (salaries, poinINTERCEPT (salaries, poinINTERCEPT (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) Finally, predict the pay for each of the 6 jobs you identified earlier using a regression equation Y=m(x)+b from the variables you just calculated for each locality. Points Per Job Job Title Teller Loan Officer Branch Manager Key Job #4 Key Job #5 Key Job #6 Predicted Salary = m(x)+b Points Louisville, KY New York, NY 0 0 0 0 0 0 If this information were from a real organization what would you suggest to the CEO regarding the location of a new facility? Consider the bottom line impact to the organiztion - how will the decision to locate a new facility in one location impact the company from a financial perspective. Are there potential business growth factors to consider? Consult reputable sources for information on labor trends in each of the markets and job growth projections (you might Job Title New York, NY Teller Loan Officer $40,073.71 12 $40,875.18 9 $43,279.60 84 $39,272.23 22 $37,268.55 5 $42,878.86 21 $44,081.08 9 $35,665.60 37 $39,672.97 17 $44,882.55 13 Branch Manager Teller $95,818.90 $72,815.59 8 7 $103,484.41 $73,543.75 5 3 $94,860.71 $63,349.56 35 7 $92,944.33 $66,990.34 9 2 $104,442.60 $83,737.93 2 1 $100,609.84 $77,184.53 9 3 $88,153.39 $39,320.42 5 2 $86,237.01 $67,208.79 13 5 $108,275.35 $75,429.67 4 6 $85,853.73 $85,922.40 7 2 Fresno Job Title $35,587.82 3 $36,299.58 1 $38,434.85 61 $34,876.06 16 $33,096.67 12 $38,078.97 16 $39,146.60 1 $31,673.16 17 $35,231.94 5 $39,858.36 12 New York, NY Fresno Teller Loan Officer Branch Manager Louisville, KY New York, NY Fresno, CA average pay average pay average pay average pay average pay average pay average pay average pay average pay alaries, points) ary = m(x)+b Fresno, CA Teller NY Fresno, CA Loan Officer Branch Manager $85,092.85 $64,664.55 1 1 $91,900.27 $65,311.20 1 1 $84,241.92 $56,258.16 23 5 $82,540.06 $59,491.39 4 1 $92,751.20 $74,364.23 3 1 $89,347.49 $68,544.42 5 2 $78,285.42 $34,918.86 1 1 $76,583.56 $59,685.38 4 0 $96,154.92 $66,986.01 2 2 $76,243.19 $76,304.17 4 1 FRE 1.3123 1.1654 Fresno, CA Loan Officer Branch Manager STUDENT NAME KimRogers Louisville, KY HR Admin Organization 1 Number Incumbents Organization 2 Number Incumbents Organization 3 Number Incumbents Organization 4 Number Incumbents Organization 5 Number Incumbents Organization 6 Number Incumbents Organization 7 Number Incumbents Organization 8 Number Incumbents Organization 9 Number Incumbents Organization 10 Number Incumbents HR Supervisor HR Manager $43,500.00 $61,500.00 $71,900.00 18 4 2 $44,370.00 $66,420.00 $72,619.00 9 5 1 $46,980.00 $60,885.00 $62,553.00 43 21 5 $42,630.00 $59,655.00 $66,148.00 16 4 2 $40,455.00 $67,035.00 $82,685.00 8 1 1 $46,545.00 $64,575.00 $76,214.00 11 3 2 $47,850.00 $56,580.00 $38,826.00 3 1 1 $38,715.00 $55,350.00 $66,363.70 22 9 3 $43,065.00 $69,495.00 $74,481.21 7 2 1 $48,720.00 $55,104.00 $84,842.00 10 3 2 Points Per Job Job Title HR Admin HR Supervisor HR Manager Key Job #4 Key Job #5 Key Job #6 Points Consult O*net general information about the jobs you identified. Additionally, select three other key jobs for the organization you hope to work for. Conduct a job evaluation for each of these 6 jobs based on your understanding of each position. Include the job evaluation with your submission Remember the point of the job evaluation is to allocate points to the job relative to the importance of the job functions. Look through the data provided above. Are there any issues with the data (outliers, missing data, other concerns). If so, how do you propose to handle it? Do you drop outliers? Why or why not? Calculate The Measures of Central Tendency Calculate The Measures of Central Tendency Louisville, KY HR Admin HR Supervisor Simple Mean AVERAGE Weighted Mean SUMPRODUCT()/SUM() Trimmed Mean (20%) TRIMMEAN() .2 Range MAX() - MIN() 25th Percentile QUARTILE(1) 75th Percentile QUARTILE(3) Interquartile Range QUARTILE3 - QUARTILE1 Median MEDIAN HR Manager Determine which measure(s) of central tendency you will use when creating a market pay line. Note that you may decide to use one or more measure. Discuss the merits of your choice and clearly explain your rationale. Job Evaluation Points Next, plot the jobs on a market pay line for each of the localities. To do this, you will need to properly structure your data. The data need to be position with fewest ordered from lowest to highest in an array see the example to the points right. Your horizontal (X) axis will be the job evaluation points. Your position with the next vertical (Y) axis will be the average pay rate using the measure(s) of highest points central tendency you identified above. After you do this you will have a market pay line chart for each locality (a total of 3 charts). position with the highest points and predict pay rates for jobs. In this equation Y is the predicted pay rate for any given job. Substitute the job evaluation points for x. Subtitute the value for the y-intercept for b. To do this follow the y-intercept (b) slope (m) Louisville, KY New York, NY Fresno, CA INTERCEPT (salaries, poinINTERCEPT (salaries, poinINTERCEPT (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) SLOPE (salaries, points) Finally, predict the pay for each of the 6 jobs you identified earlier using a regression equation Y=m(x)+b from the variables you just calculated for each locality. Points Per Job Job Title HR Admin HR Supervisor HR Manager Key Job #4 Key Job #5 Key Job #6 Predicted Salary = m(x)+b Points Louisville, KY New York, NY 0 0 0 0 0 0 If this information were from a real organization what would you suggest to the CEO regarding the location of a new facility? Consider the bottom line impact to the organiztion - how will the decision to locate a new facility in one location impact the company from a financial perspective. Are there potential business growth factors to consider? Consult reputable sources for information on labor trends in each of the markets and job growth projections (you might Job Title New York, NY HR Admin HR Supervisor HR Manager HR Admin $57,085.05 $80,706.45 $94,354.37 12 8 7 $58,226.75 $87,162.97 $95,297.91 9 5 3 $61,651.85 $79,899.39 $82,088.30 84 35 7 $55,943.35 $78,285.26 $86,806.02 22 9 2 $53,089.10 $87,970.03 $108,507.53 5 2 1 $61,081.00 $84,741.77 $100,015.63 21 9 3 $62,793.56 $74,249.93 $50,951.36 9 5 2 $50,805.69 $72,635.81 $87,089.08 37 13 5 $56,514.20 $91,198.29 $97,741.69 17 4 6 $63,935.26 $72,312.98 $111,338.16 13 7 2 Job Title Fresno $50,694.90 3 $51,708.80 1 $54,750.49 61 $49,681.00 16 $47,146.26 12 $54,243.54 16 $55,764.39 1 $45,118.46 17 $50,187.95 5 $56,778.29 12 New York, NY Fresno HR Admin HR Supervisor HR Manager Louisville, KY New York, NY Fresno, CA average pay average pay average pay average pay average pay average pay average pay average pay average pay alaries, points) ary = m(x)+b Fresno, CA HR Admin NY Fresno, CA HR Supervisor HR Manager $71,672.10 $83,792.26 1 1 $77,405.87 $84,630.18 1 1 $70,955.38 $72,899.27 23 5 $69,521.94 $77,088.88 4 1 $78,122.59 $96,361.10 3 1 $75,255.71 $88,819.80 5 2 $65,938.33 $45,247.82 1 1 $64,504.89 $77,340.26 4 0 $80,989.47 $86,800.40 2 2 $64,218.20 $98,874.87 4 1 FRE 1.3123 1.1654 Fresno, CA HR Supervisor HR Manager STUDENT NAME KimRogers Louisville, KY Organization 1 Number Incumbents Organization 2 Number Incumbents Organization 3 Number Incumbents Organization 4 Number Incumbents Organization 5 Number Incumbents Organization 6 Number Incumbents Organization 7 Number Incumbents Organization 8 Number Incumbents Organization 9 Number Incumbents Organization 10 Number Incumbents Benefits Specialist HR Generalist HR manager $47,762.00 $69,853.00 $86,985.00 18 4 2 $48,717.24 $75,441.24 $87,854.85 9 5 1 $51,582.96 $69,154.47 $75,676.95 43 21 5 $46,806.76 $67,757.41 $80,026.20 16 4 2 $44,418.66 $76,139.77 $100,032.75 8 1 1 $51,105.34 $73,345.65 $92,204.10 11 3 2 $52,538.20 $64,264.76 $46,971.90 3 1 1 $42,508.18 $62,867.70 $80,287.16 22 9 3 $47,284.38 $78,933.89 $90,107.76 7 2 1 $53,493.44 $62,588.29 $102,642.30 10 3 2 Points Per Job Job Title Benefits Specialist HR Generalist HR manager Key Job #4 Key Job #5 Key Job #6 Points Consult O*net general information about the jobs you identified. Additionally, select three other key jobs for the organization you hope to work for. Conduct a job evaluation for each of these 6 jobs based on your understanding of each position. Include the job evaluation with your submission Remember the point of the job evaluation is to allocate points to the job relative to the importance of the job functions. Look through the data provided above. Are there any issues with the data (outliers, missing data, other concerns). If so, how do you propose to handle it? Do yo