Sales Rating Excel Format

Document Sample
Sales Rating Excel Format Powered By Docstoc
					               Descriptive Statistics Worksheet
                           using Excel 2000

                          Employee Satisfaction Survey

The medical sales industry is highly competitive with high levels of staff turnover and so
companies must be sure that the salaries and bonus payments they offer are competitive.
Since 2001, a company called Pharmaceutical Field has commissioned the Royal Statistical
Society Centre for Statistical Education to investigate the remuneration, motivation,
satisfaction, perception and recruitment of the sales force. This worksheet, based on the
data from this survey, is concerned with choosing the best summary statistics to describe the
salary and bonus payments for sales representatives and managers in medical sales.

Getting started
The data are supplied in Excel format in the file EmploySat.xls.
     •   Start Excel and from the main menu select File > Open;
     •   in the dialog box that appears click in the Look in box and navigate to the folder
         containing EmploySat.xls;
     •   click on this file and then Open.

The worksheet contains 17 columns of data, 5 of which we shall use in this worksheet:
C2       Gender            1 = female, 2 = male
C5       Relative_sales    Self rating of sales achievement relative to others in the same
                           1 = lowest sales, 2 = below average sales, 3 = average sales,
                           4 = above average sales, 5 = highest sales
C10      Fulltime          ft = full-time, pt = part-time
C11      Gross_salary      Basic gross salary excluding bonus and incentive payments (£)
C13      Monthly_avg_bonus Average monthly bonus (£)

The salary survey included both full-time and part-time employees. To study the salary and
bonus payments we are going to focus exclusively on the full-time workers. To restrict the
analysis to their data:
     •   Click on cell A1 to identify the data region.
     •   From the main menu choose Data > Sort and choose to Sort by Fulltime in
         Ascending order, then OK.
     •   The data for part-time employees are in rows 1720 to 1759 inclusive. Highlight these
         rows and press the Delete key to remove them.
     •   From the main menu choose Format > Sheet > Rename to rename the worksheet
         as Fulltime.

Descriptive Statistics                                                       EmploySat / Excel
                                              1 of 6
To begin the analysis, we shall draw a histogram of gross salaries to determine which
statistics are best for summarising the salary data. In Excel this involves a lot of work!

There are a large number of missing entries (denoted by a full-stop) in the Gross_salary
column, which will cause problems later. If the missing values are left as non-numeric then
Excel will refuse to group the data, so it will be best to recode the missing values as a value
outside the normal range of the data, e.g. 99999. To do this:
   •   Click on the header K at the top of the Gross_salary column to highlight the column.
   •   From the main menu select Edit > Replace.
   •   Ubder Find what enter . (i.e. full-stop) and under Replace with enter 99999, then
       click on Replace All to complete the operation.

Now you need to use a pivot table to tally the Gross_salary data:
   •   Click on cell A1 to identify the area of the spreadsheet containing your data.
   •   From the main menu select Data > PivotTable and PivotChart Report.
   •   At Step 1 accept the default settings, then Next.
   •   At Step 2 check that the data range is A1:Q1719, and amend if necessary, then
   •   At Step 3 accept the default setting of putting your table on a new worksheet. Click
       on Finish to produce a skeleton table outlined in blue. The PivotTable toolbar should
       be visible. (If it is not, select View > Toolbars > PivotTable to retrieve it.)
   •   Click anywhere inside the skeleton table and you should see on the toolbar the
       names of all your variables (i.e. the column headings in your data spreadsheet).
       These are live buttons that can be dragged and dropped onto the skeleton table.
   •   Drag the Gross_salary button and drop it in the Drop Row Fields Here area.
   •   Drag the Gross_salary button from the toolbar again and drop it in the Drop Data
       Items Here area.

For a histogram the data need to be grouped.
   •   Click on one of the salaries in the first column of the pivot table.
   •   From the main menu select Data > Group and Outline > Group.
   •   Choose intervals to start from 10,000, finish at 89,999 in steps of 5000.
   •   The last interval contains the missing data. To omit it from the table, click on the
       drop-down at the head of the first column of the pivot table, and de-select the interval
       labelled >90,000.
   •   Click on OK to complete the table.

To chart the table:
   •   Click anywhere inside the pivot table.
   •   From the main menu select Insert > Chart. This will produce a bar chart on a new
       worksheet. Some further work is needed.
   •   To remove the gaps between the bars, right-click on one of the bars and select
       Format Data Series > Options and set the Gap width to zero.
   •   To remove the unsightly grey field buttons, right-click on one of them and select Hide
       PivotChart Field Buttons.
   •   After some further formatting your chart should look similar to the one below.

Descriptive Statistics                                                       EmploySat / Excel
                                             2 of 6
                                                               Distribution of Salaries










                         10000-   15000-   20000-   25000-   30000-   35000-   40000-   45000-   50000-   55000-   60000-   65000-   70000-   75000-   85000-
                         14999    19999    24999    29999    34999    39999    44999    49999    54999    59999    64999    69999    74999    79999    89999

                                                                               Gross Salary (£)

Q1.        Given the shape of the histogram, what statistic should be used to
           represent a typical salary in medical sales? Explain.
           The histogram has a positive skew. This means that the median is the best statistic
           for representing the typical salary.

We will now calculate the summary statistics. The 99999 values must now be excluded from
the calculations.
   •       Return to the Fulltime worksheet and scroll down to the foot of the data.
   •       In cell R1 enter the text heading G_Salary.
   •       In cell R2 enter the formula =IF(K2<99999, K2, “”) and Copy this down to row 1719.
   •       In cell R1721 enter the formula =MEDIAN (R2:R1719) .
   •       In cell R1722 enter the formula =QUARTILE(R2:R1719,1) to calculate the lower
           quartile. Similarly, in cell R1723 enter the formula = QUARTILE(R2:R1719,3), to
           calculate the upper quartile, and in R1724 enter a suitable formula to calculate the
           inter-quartile range.
   •       In cells R1725:R1726 enter the formulae =MIN(R2:R1719) and =MAX(R2:R1719)
           and in R1727 enter a suitable formula to calculate the range of the data.
   •       Enter suitable labels next to each of these statistics, as shown below.
                                                                 median                          33000
                                                                 q1                              27500
                                                                 q3                              40000
                                                                 IQR                             12500
                                                                 min                             12000
                                                                 max                             86000
                                                                 range                           74000

Q2.     What is the median gross salary? What does this tell you?
           The median salary is £33,000. This is the typical salary in this sample as half of
           respondents had higher salaries and half had salaries that were lower than this

Descriptive Statistics                                                                                                                EmploySat / Excel
                                                                                3 of 6
The median salary tells us something about the earnings of this people in medical sales, but
to get a clear picture of salaries in this area of employment, we should also look at how
much salaries vary and what the highest and lowest earners are paid. Look again at the
tables of statistics and pick out the results that can help to answer the following questions.

Q3.    How much do salaries vary amongst this sample of people?
       The salaries range from £12,000 to £86,000. The Inter-quartile range is £12,500.
       (Q3 – Q1, i.e. £40,000 -£27500) so the middle 50% of the salaries vary by £12,500.

We want to see what salaries the top 10% of earners have.
   •   In R1728 enter the formula =PERCENTILE(R2:R1719, 90%) to calculate the 90th

Q4.    How much do the top 10% of earners receive as their gross salary?
       What statistic is needed to answer this question?
       The 90th percentile (i.e. where the cumulative percentage is 90) tells you how large a
       salary is earned by the people whose salary puts them in the top 10% of values. In
       this case the top 10% of salaries are £46,000 or above.

Monthly bonuses
Now use the same approach (i.e. draw the histogram, and produce descriptive statistics) to
look at the monthly bonuses (Monthly_avg_bonus) paid to staff in medical sales.

Q5.    What shape is the distribution of average monthly bonus payments?
       The distribution has an extreme positive skew and one outlier corresponding to an
       individual reporting an average monthly bonus payment of £40,000.

Q6.    What effect does the shape of the distribution have on the difference
       between the mean and the median of the average bonus payments?
       The extreme positive skew leads to a mean of £497, much higher than the median of

Q7.    What level of monthly bonus is typical of the sample? How much do
       bonuses vary? Which statistics are better for answering these
       questions and why?
       The median monthly bonus is £300. Average monthly bonuses vary, ranging from 0
       to £40,000, with an IQR of £378. The median and IQR are the most suitable
       statistics for describing this sample because of the skewed distribution.

Descriptive Statistics                                                     EmploySat / Excel
                                            4 of 6
Q8.    What level of bonus payments are paid to those in the lowest 10% of the
       At the lower end of the scale, people in the lowest 10% of values received bonuses
       between 0, the minimum, and the 10th percentile of £8.33.

Comparing salaries of men and women
To compare the salaries paid to men and women requires some modification of the
preceding analysis. First the data must be sorted by Gender (Column B).
   •   Select the Fulltime worksheet click in cell A1.
   •   From the main menu select Data > Sort and choose to Sort by Fulltime and Then
       by Gender, both in Ascending order. The female (Gender=1) data are now in rows
       2 to 876.
   •   Modify the formulae in R1721 to R1728 to calculate the statistics on the range
   •   Add a suitable heading to show that these are the Female salary statistics.
   •   Below these statistics add further formulae to calculate the Male salary statistics.

Q9.    How do the salaries of men and women in medical sales differ in terms
       of the level of the typical salary, the variation in salaries and the salary
       needed to be in the top 10% of earners?
       Typical salaries are higher for men, whose salaries are also more variable. Median
       salaries are £31,500 for women and £35,000 for men, IQR £11,794 for women and
       £12,500 for men.

       The top 10% of women earners were paid at least £43,000 while the top 10% of men
       were paid at least £48,500.

Now use the same approach to compare the salaries of people who rate their sales
performance differently. You can do this by repeating the instructions used above, but
replacing Gender with Relative_sales.

Q10. Are better sales ratings associated with higher salaries? How much data
     is available in each group?
       Those in the lowest sales category had the lowest median salary of £27,000, those
       who rated their sales as ‘below average’ had a median salary of £31,250 and those
       who rated their sales performance as ‘average’,‘above average’ or ‘highest’ had
       median salaries of £30,000, £34,000 and £35,000 respectively

       So, with the exception of those rating their sales as ‘below average’ whose median
       salary was higher that of those with ‘average sales’, higher sales ratings are
       associated with higher typical salaries. Note that the sample sizes for ‘lowest’ and a
       ’below average sales’ (13 and 60) were much lower than for the other groups
       (307,780 and 222).

Descriptive Statistics                                                     EmploySat / Excel
                                            5 of 6
Changing the shape of the distribution
Sometimes in statistics, it can be helpful to transform a variable, so that instead of analysing
the data in its original form, analyses are carried out on transformed values instead.
Transformations such as taking logs can produce data that approximates to the Normal
distribution even if the original data does not have a symmetric distribution. The next
exercises will use a logarithmic transform of salary. The first step is to take natural logs of
the salaries:
   •       In cell S2 enter the formula =IF(ISNUMBER(R2)=TRUE, LN(R2), 99999) and Copy
           down to row 1719.
   •       In cell S1 enter the text heading Ln(Salary).

Now, using the same method as in earlier exercises, draw a histogram of the new variable,
Ln(salary). You should obtain something similar to the chart shown below.

                                                       Distribution of Ln(salary)











                         9.2-9.4   9.4-9.6   9.6-9.8    9.8-10   10-10.2     10.2-10.4   10.4-10.6   10.6-10.8   10.8-11    11-11.2   11.2-11.4


Q11. What effect has the transformation had on the shape of the distribution?
           The original data had a strong positive skew but the transformed data is quite
           symmetric and now approximates the Normal distribution quite well.

Further investigation
Try using the methods used earlier to compare the salaries paid to people in different age

Average monthly bonus had a strongly skewed distribution, with one very extreme outlier.
Transform this variable by taking logs then plot the results to see if the transformed data
appears to be Normally distributed. Does the transformation work as well as for the salary

Descriptive Statistics                                                                                                     EmploySat / Excel
                                                                       6 of 6

Shared By:
Description: Sales Rating Excel Format document sample