Analytics for Description

Abstract

All analytics begin with data. The type of analytics depends on the type of data. In this chapter, we look at types of data, ways to display data graphically, and means to describe data.

1.1 Electronic Supplementary Material(s)

Compensation of Best Paid CEOs 2020 master (XLSX 114 kb)

Forbes Billionaires master (XLSX 59 kb)

Appendices

Excel 1.1: Analytics for Description

1.1.1 Describe Nominal Variables with PivotTables and a Bar Chart

Compensation of Best Paid CEOs

To see a distribution when a variable is nominal, PivotTables easily produce a frequency table.

Use a PivotTable to sort the sample of best paid CEOs by gender.

Select data and labels in columns B through E. Insert a PivotTable to compare compensation by gender. Drag Female to the Rows and to the ∑Values.

Convert the counts to percents by right clicking inside the PivotTable and choosing Show Values As % Grand Total:

Show the gender distribution in a PivotChart. Click PivotChart:

In the PivotTable, change 0 to Male and 1 to Female. In the PivotChart, add a vertical axis label in Design, Add Chart Element, Axis Title, Vertical.

Remove the unwelcome zeros (by right clicking the axis, then Format Axis, Number, Decimals).

Delete the legend, and replace the title with a stand alone title.

Description of a Quantitative Variable

Use Descriptive Statistics in excel to find the mean, median, range, standard deviation, variance and skewness of Compensation ($M).

First, activate the Analysis ToolPak: File, Options, Add Ins, Analysis toolPak.

Click Data, Data Analysis to see the Analysis ToolPak menu.

Choose Descriptive Statistics.

Produce a Box and Whiskers Plot of a Quantitative Variable

First, find the interquartile range using percentile(array, .75) and percentile(array, .25) functions for each of the industries:

Services and technology firms have the largest interquartile range.

To illustrate with a box and whiskers plot, first, move the Industry column to the left of Compensation. Select cells in these two columns, click the Insert tab, then choose the option in the lower right to See All Charts:

Choose All Charts, Box and Whisker:

Click on the chart then choose a Design that shows the boxes and medians more clearly:

In Design, Add Chart Elements, Axis Title, Vertical.

Add a stand alone title.

Produce a Histogram of the Distribution of a Quantitative Variable

To make a histogram of compensation, Excel needs to know what ranges of values to combine. The goal is to choose category ranges that are equivalent, and which show some variation. Too much information will be lost if the category ranges are too broad and most cases fall into the same category, or bin. There will be too many empty categories if the ranges are too small. Here, category ranges of 5 are used (after trying other values that were either too large or too small). Excel uses a bin value as the upper limit for a category. And so, the bin of 15 (which is the sample minimum) will include all CEOs who earn $15 M or less. Continue adding bins that are each 5 more than that last until you’ve reached the maximum, $156 M.

Request a Histogram frequency tabulation, reading in the compensation cells and the bin cells, both with labels. Choose Labels and Chart Output:

If you’d like to see proportions, instead of counts, add a column of proportions, dividing counts by the total, 107:

Produce the histogram graph of proportions by selecting data in column A, then, holding Control (or Command), select cells in column C. Insert a column chart:

From Design, Add Chart Element, Axis Title, Vertical, add the vertical axis title. Similarly, add a horizontal axis title.

Replace the chart title with a stand alone title.

Change the space between columns (the gap width) to a small number, such as 5, since these data are continuous by right clicking in a bar, and Format Data Series.

Case 1.1: Where Are the Billionaires?

71 countries in the World are home to the World’s 2868 billionaires. Data are in 2022 Forbes billionaires.xlsx.

• A. Describe the population of countries which are home to billionaires

  • 1. The distribution of the World’s billionaires is ___ positively OR ___ negatively skewed.

  • 2. Is the distribution of World’s Billionaires in a country approximately Normal? ___ yes ___ no

  • 3. The average number of billionaires per country among the 71 countries is ____.

  • 4. 50% of the 71 countries are home to ___, the median, or fewer billionaires.

  • 5. Create a histogram of the distribution of percent of billionaires in a country across the 71 countries. Add axes labels and a stand alone chart title. Paste below:

  • 6. The middle 50% of countries are home to ___ to ___ billionaires.

• B. Billionaires by Global Region

  • 1. Use a PivotTable of sums of billionaires in a country to find the percent of billionaires by global region and produce a PivotChart to illustrate your results. Add axes labels and a stand alone chart title. Paste below:

  • 2. The vast majority, ___%, of the World’s billionaires reside in one of three global regions: ________, _________ and ________.

• C. Billionaires by level of economic development

  • 1. Use a PivotTable of sums of billionaires to find the distribution of percent of billionaires by country level of development and produce a PivotChart to illustrate your results. Add axes labels and a stand alone chart title. Paste below:

  • 2. While ___ % of the World’s billionaires reside in developed countries, ___ % live in a BRIC (Brazil, Russia, India or China) country.

  • 3. Report the interquartile range for number of billionaires by level of development.

  • 4. Produce a box and whiskers plot of the distribution of billionaires by level of economic development. Choose a design that makes it easier to see the medians. Label the vertical axis and add a stand alone title.