Business Analytics

• Produce frequency distributions and histograms for numerical data
• Calculate by pen-and-paper some of the standard summary measures used to describe single quantitative variables.
• Produce summary measures using Microsoft Excel.
• Practise choosing the most appropriate summary measures to describe a variable.
• Interpret the results from your analysis in regard to the following scenario.
Scenario

We continue with the analysis of the Conrobar employee data set that we started in Part 1 of this tutorial.

Conrobar is a manufacturing company which employs over 3,000 people. Management is concerned about the wide variation in productivity between employees and whether employee job satisfaction and their sense of job security plays any part in this problem. The organisation also has staff planning issues that need addressing, including planning to replace workers who retire, undertaking a recruitment drive for new employees and providing adequate promotion opportunities within the company.

The company has conducted a survey of 48 of their staff, collecting data on these and related issues. You are required to provide Conrobar management with a report on (a) the age profile of staff and (b) the productivity of staff. In particular, are they meeting the target of 100% productivity?
1. Open the data file and install the Data Analysis Toolpak

• Open the file Conrobar_Tut02.xlsx in Excel.
• To perform a number of statistical calculations in Excel, the Analysis Toolpak needs to be installed.

From the top of Excel (Microsoft Office Ribbon, click on File tab, select Options (see Figure 1a), choose Add-Ins (see Figure 1b), and then press the ‘Go…’ button to manage Excel Add-Ins (see Figure 1c). Finally, select Analysis Toolpak and press OK (see Figure 1d).

Figure 1a

2. The variable Age

Below are the ages of the 48 employees included in the sample of Conrobar employees.

49 21 32 34 18 50 36 43 49 34 48 37
41 31 41 33 51 36 40 53 33 43 48 21
29 25 41 40 25 24 45 29 30 17 58 51
52 50 43 33 22 31 27 44 55 20 18 19

(a) Is the variable Age nominal, ordinal, interval or ratio?

(b) What tables and graphs would be suitable to summarise the variable Age?

3. Tabular and Graphical Summaries of Age (by hand)

Using the data given above, complete the following frequency distribution:

Age Frequency Percent
10 to 20 5 10%
> 20 to 30 10 21%
> 30 to 40
> 40 to 50 14 29%
> 50 to 60
Total 48 100%

Using the table above, draw a histogram in the space provided below:
4. Tabular and Graphical Summaries of Age (use the computer)

(a) Would a frequency table similar to that done in last week’s tutorial for job security be suitable for the variable Age?
(b) Construct a frequency distribution of Age and a corresponding histogram with intervals of width 10.

In Excel you need to set up ‘Bins’. See the ‘Working’ worksheet, cells C5 to C9. Each bin value is the upper limit of a class. In this example, the first class consists of all values up to and including 20; the next class includes values above 20 but less than or equal to 30; and so on for the remaining classes.

Select the Data tab and then press Data Analysis (see Figure 3a). From the pop-up window select Histogram and press OK (see Figure 3b). Then enter the Input Range, Bin Range, Output Range and select Chart Output (as shown in Figure 3c). Press OK.

Figure 3a: Running the Data Analysis Toolpak. Note that if you do not see the ‘Data Analysis’ option then the Analysis Toolpak has not been installed (see step 1 for how to do this).

Figure 3b Figure 3c
The histogram produced by Excel contains ‘gaps’. To remove the gaps, right-click on one of the bars in your graph and choose ‘Format Data Series’. From the pop-up window change the Gap Width to No Gap.
Answer the following questions:
? What percentage of employees are younger than or equal to 20?
? What percentage are older than 30 but younger than or equal to 50?
? What percentage are close to retirement, that is, older than 50?
5. Report to Management on the AGE of employees

Using your answers from Tasks 3 and 4, summarise the most important features of the variable Age. Remember that the firm has approximately 3,000 employees but we only analysed 48 employees. Write your answers in the ‘Results’ worksheet.
6. Numerical Summaries of AGE (by hand)

For the purpose of this task we will only look at 12 of the 48 data values.

29 25 41 40 25 24 45 29 30 17 58 51

__ __ __ __ __ __ __ __ __ __ __ __

(a) Calculate the mean and median.

(b) Calculate some measures that show the location of data (Minimum, Maximum, Q1, Q3).

(d) Draw a box plot and comment on the shape of the data.

(e) Calculate ‘fences’ and draw them on your box-plot. Are there any potential outliers in this group of 12 employees?

(f) Would the mean or median be the most appropriate measure of average in this case?

(g) Is the mode a useful measure in this case?

7. Summarising PRODUCTIVITY (use the computer)

The variable Productivity is a quantitative measure of productivity performance with 100% as the base:

105.0 103.2 102.1 92.5 95.0 99.0 97.2 98.6 96.4 104.3 104.0 98.0
98.2 102.1 106.2 95.4 101.0 93.0 96.6 91.4 95.4 105.9 98.3 102.5
94.3 92.4 97.6 98.1 98.0 98.0 102.5 103.4 105.1 96.5 107.0 101.3
91.2 95.3 97.2 102.9 97.0 94.5 93.5 103.2 107.0 96.9 95.0 96.1

Using all 48 data values, complete the following questions by computer:

(a) Produce the standard summary measures.

Select the ‘Workings’ worksheet. Click on the Data tab and then press Data Analysis. From the pop-up window select Descriptive Statistics and press OK (see Figure 1a). Then enter the Input Range, Output Range and select Summary Statistics (as shown in Figure 1b). Press OK.

Figure 1a: Data Analysis window Figure 1b. Descriptive Statistics window

Your output should include a number of summary measures such as the mean, standard deviation, etc. There are others, such as the quartiles and IQR which are not included. We can calculate them explicitly using Excel functions and formulas.

In cell D30, calculate the 1st quartile with the function =QUARTILE(Productivity,1)
In cell D31, calculate the 3rd quartile with the function =QUARTILE(Productivity,3)
In cell D32, calculate the interquartile range with the formula =D31 – D30
(b) Determine the productivity range of the top 10% of employees.

In cell D35, calculate the 90th percentile with the function =PERCENTILE(Productivity,0.9)
(c) Check if there are any potential outliers.

In cell D39, calculate the lower fence with the formula =D30-1.5*D32

Your turn – enter a formula for an upper fence in cell D40. Based on your fences are there any potential outliers?
(d) Draw a histogram of productivity.

Refer to the notes in Task 4 (b) on how to create a histogram. Use the ‘Bins’ in cells C44 to C48 on the ‘Working’ worksheet.
(e) Draw a box plot of productivity.

There is no simple or direct way to produce box plots in Excel. Some Add-In programs such as PHStat, PaceXL and XLStatistics will allow you to do boxplots in Excel. However, we will simply hand draw our box plots in the space provided below.
Based on your graphs in (d) and (e) how would you describe the shape of the data?
(f) Would the mean or median be the most appropriate measure of average in this case?
(g) Is the mode a useful measure in this case?

8. Report to management on PRODUCTIVITY

Using your answers from Task 7, briefly summarise the most important features of the variable Productivity. In particular, address any issues raised in the scenario given earlier. Write your answer in the ‘Results’ worksheet.
ORDER THIS ESSAY HERE NOW AND GET A DISCOUNT !!!