| Week 1. | Measurement and Description - chapters 1 and 2 |
| | The goal this week is to gain an understanding of our data set - what kind of data we are looking at, some descriptive measurse, and a |
| | look at how the data is distributed (shape). |
| 1 | Measurement issues. Data, even numerically coded variables, can be one of 4 levels - |
| | nominal, ordinal, interval, or ratio. It is important to identify which level a variable is, as |
| | this impact the kind of analysis we can do with the data. For example, descriptive statistics |
| | such as means can only be done on interval or ratio level data. |
| | Please list under each label, the variables in our data set that belong in each group. |
| | Nominal | Ordinal | Interval | Ratio |
| | Gender- | Performance Rating | | Salary |
| | ID | Gr | | Age |
| | Gender1 | Degree | | Raise |
| | | | | Midpoint |
| | | | | Compa |
| | | | | Service |
| b. | For each variable that you did not call ratio, why did you make that decision? |
| | Nominal |
| | Gender- 0 and 1 are used to identify a person as either male and female respectively, and the quantitative value is meaningless |
| | ID-This are arbitrary numbers used to identify employees and any computation with them is meanigless |
| | Gender1-M and F are used to identify a person as either male and female respectively and the quantitative value is meaningless |
| | Ordinal |
| | Degree- | The values 0 and 1 are used to rank the enducation of the employes, either 0 for BS\BA or 1 for MS and thus the order matters |
| | Performance Rating-The rating represent the rank of employees' performance and the rating is important and significant |
| | Gr- the valuable A,B,C,D,E represent the job/pay grade with work varying across the groups |
| 2 | The first step in analyzing data sets is to find some summary descriptive statistics for key variables. |
| | For salary, compa, age, performance rating, and service; find the mean, standard deviation, and range for 3 groups: overall sample, Females, and Males. |
| | You can use either the Data Analysis Descriptive Statistics tool or the Fx =average and =stdev functions. |
| | (the range must be found using the difference between the =max and =min functions with Fx) functions. |
| | Note: Place data to the right, if you use Descriptive statistics, place that to the right as well. |
| | Some of the values are completed for you - please finish the table. |
| | Overall | | Salary | Compa | Age | Perf. Rat. | Service |
| | | Mean | 44.4 | 1.1 | 35.7 | 85.9 | 9.0 |
| | | Standard Deviation | 18.9763 | 0.0815 | 8.2513 | 11.4147 | 5.7177 | Note - data is a sample from the larger company population |
| | | Range | 59.3 | 0.340 | 30 | 45 | 21 |
| | Female |
| | | Mean | 37.85 | 1.0651 | 32.5 | 84.2 | 7.9 |
| | | Standard Deviation | 18.2 | 0.0746 | 6.9 | 13.6 | 4.9 |
| | | Range | 55.7 | 0.271 | 26.0 | 45.0 | 18.0 |
| | Male |
| | | Mean | 51.0 | 1.0 | 38.9 | 87.6 | 10.0 |
| | | Standard Deviation | 17.8 | 0.0869 | 8.4 | 8.7 | 6.4 |
| | | Range | 57.6 | 0.339 | 28.0 | 30.0 | 21.0 |
| 3 | What is the probability for a: |
| | a. Randomly selected person being a male in grade E? |
| | b. Randomly selected male being in grade E? |
| | a. probability of selecting a male from the employees * being in grade E = 1/2*10/25 =0.20 |
| | b. probality of selecting a male in grade E = 10/25 =0.4 |
| | c. Why are the results different? | | | Note part b is the same as given a male, what is probabilty of being in grade E? |
| | For part a, the male in group E is to be selected from all the employees. In part b, the male in group E is to be selected from male employees only. As a result, the probability in part a is smaller than that of part b |
| 4 | A key issue in comparing data sets is to see if they are distributed/shaped the same. We can do this by looking at some measures of where |
| | some selected values are within each data set - that is how many values are above and below a comparable value. |
| | For each group (overall, females, and males) find: |
| | | | | | | | | | Overall | Female | Male |
| A | The value that cuts off the top 1/3 salary value in each group | | | | | | | | 56.8 | 41.7 | 59.2 |
| i | The z score for this value within each group? | | | | | | | | 0.7 | 0.2 | 0.5 |
| ii | The normal curve probability of exceeding this score: | | | | | | | | 0.2567684533 | 0.4161432158 | 0.3212375829 |
| iii | What is the empirical probability of being at or exceeding this salary value? | | | | | | | | 0.2292176358 | 0.5838567842 | 0.2747591868 |
| A | The value that cuts off the top 1/3 salary value in each group = 1/3(50)=33 th term for overall or 1/3(25) of each group =8th term |
| | we use the function=LARGE(array,k)
|
| i | z score = (x - μ) / σ |
| ii | We use Excel's NORM.DIST() function when cumulative is true |
| iii | We use Excel's NORM.DIST() function when cumulative is true for both the maxmum and the given value and then get the difference | | | | | | | | | | | | | | Salary | Compa value |
| | | | | | | | | | Overall | Female | Male | | | Max Overall | 80.8 | 1.207 |
| B | The value that cuts off the top 1/3 compa value in each group. | | | | | | | | 1.095 | 1.027 | 0.999 | | | Max Male | 80.8 | 1.207 |
| i | The z score for this value within each group? | | | | | | | | 0.5466728289 | -0.5111205534 | -0.4229567146 | | | Max female | 77.2 | 1.207 |
| ii | The normal curve probability of exceeding this score: | | | | | | | | 0.9887600269 | 0.9786493353 | 0.9975353498 |
| iii | What is the empirical probability of being at or exceeding this compa value? | | | | | | | | 0.00 | 0.000512044 | 9E-05 |
| C | How do you interpret the relationship between the data sets? What do they mean about our equal pay for equal work question? | | | | | | | | | | | | | | |
|
| | The z-scores obtained tell us how many standard deviations the value that cuts off the top 1/3 sale or compa value in each group is above or below the mean. |
| | A bigger the Z-score means that the value is distributed far from the mean. Equal work among the employees in a particular group can be characterized by a small Z-value. |
| | The normal curve probability gives the probability that an employee will have a salary more than this cut of pay or compa value. |
| | The higher the normal curve probability means that more employees are likely to earn above the group and thus a small the disperity in the work pay |
| | The empirical probability of being at or exceeding either a specific cut off salary or value gives use the probability of a person's salary to be between it and the maximum and has similar characteristics like the normal curve |
| | From the above result, the Z-score for the cut off value is positive (above the mean) and that of men group is widely distributed compared to that of women, meaning that the salary disparity among male employees is big compared to that of women. |
| | Furthermore, women are more likely to to earn a salary higher than the cut of value compared to men. |
| | For the compa result, the cut off value is predicted to be below the mean for both male and female and employees are unlikely to earn a salary beyond this value. |
| 5. | What conclusions can you make about the issue of male and female pay equality? Are all of the results consistent? |
| | What is the difference between the sal and compa measures of pay? |
| | Salary results show that male are receiving a higher salary than female when assuming that different grades have equal pay. On the other hand, compa show that female are receiving a higher salary than men when the difference in work grade is accounted for. |
| | The compa pay represent the salary divided by the midpoint (salary grade midpoint) to give a ratio that relate an employee's salary to work his or her working grade. |
| | From the data, it is clear that the higher the grade(i.e toward E) the higher the salary. Compa is a better measure of pay than salary as it account for the difference in job grades between employees |
| | Conclusions from looking at salary results: |
| | The value that cuts off the top 1/3 salary value in male group was the highest (59.2) and above the average value (56.8), whereas the value fo cut off salary for the third of top paid female was the lowest. |
| | This anticipate that the salary received by women who are in the high paying work blacket is lower compared to that of men in the same work blacket. Besides, the mean for male is hire than that of female. |
| | Do both salary measures show the same results? |
| | The two result are not the same. Salary data predict that men are paid higher than women whereas Compa result show that women in a given work grade are paid more than men in the same grade. |
| | Can we make any conclusions about equal pay for equal work yet? |
| | Since the two results are contradicting each other, there is no sufficient evidence to make a stand about equal pay for equal work. |
cangledeters
excel_week_one_assignment.xlsx
