6. Measuring management practices
6. 衡量管理实践
Working in Excel  在 Excel 中工作

Learning objectives for this part
本部分的学习目标

  • Explain how survey data is collected, and describe measures that can increase the reliability and validity of survey data.
    说明如何收集调查数据,并描述可以提高调查数据的可靠性和有效性的措施。
  • Use column charts and box and whisker plots to compare distributions.
    使用柱状图和箱形图比较分布。
  • Calculate conditional means for one or more conditions, and compare them on a bar chart.
    计算一个或多个条件的条件均值,并在条形图上比较它们。
  • Use line charts to describe the behaviour of real-world variables over time.
    使用折线图来描述实际变量随时间的变化。

First download the data used in the paper to understand how this information was collected. The dataset is publicly available and free of charge.
首先下载论文中使用的数据,以了解这些信息是如何收集的。该数据集是公开可用的,并且是免费的。

  1. To learn about how Bloom et al. (2012) conducted their survey, read the sections ‘How Can Management Practices Be Measured?’ and ‘Validating the Management Data’ (pages 5–9) of their paper.
    要了解 Bloom 等人(2012 年)如何进行调查,请阅读他们论文的“如何衡量管理实践”和“验证管理数据”(第 5-9 页)部分。
  • Briefly describe how the interviews with managers were conducted, and explain some of the methods that the researchers used to improve the reliability and validity of their data. (There are a few technical terms that you may not understand, but these are not necessary for answering this question.)
    简要描述如何对经理进行访谈,并解释研究人员用来提高其数据可靠性和有效性的一些方法。(有一些技术术语您可能不理解,但这些不是回答这个问题所必需的。
  • Three aspects of management practices were evaluated: monitoring, targets, and incentives. Do you think that these are the best criteria for assessing management practices? What (if any) important aspects of management are not included in this assessment? (You may also find it helpful to refer to the ‘Contingent Management’ section on pages 23–25 of the paper.)
    评估了管理实践的三个方面:监测、目标和激励措施。您认为这些是评估管理实践的最佳标准吗?本次评估不包括哪些(如果有的话)管理的重要方面?(您可能还会发现,参考论文第 23-25 页的“应急管理”部分也会有所帮助。

Now we will create some charts to summarize the data and make comparisons across countries, industries (manufacturing, healthcare, retail, and education), and firm characteristics.
现在,我们将创建一些图表来汇总数据,并在国家/地区、行业(制造业、医疗保健、零售和教育)和公司特征之间进行比较。

  1. In ‘Manufacturing: 2004–2010 combined survey data (AMP)’, open the Excel document ‘AMP_graph_manufacturing.csv’. Use this data on manufacturing firms to do the following:
    在“制造业:2004-2010 年合并调查数据 (AMP)”中,打开 Excel 文档“AMP_graph_manufacturing.csv”。使用制造公司的此数据执行以下操作:
  • In a new tab, create a table like Figure 6.2a, that shows the average management scores for all the firms in each of the twenty countries, and fill it in with the required values. The variables for the overall score and three individual criteria are ‘management’, ‘monitor’, ‘target’, and ‘people’. You may find it helpful to use Excel’s PivotTable option—see Excel walk-through 3.1 if you need guidance. For each criterion, rank countries from highest to lowest, then create and fill in a table like Figure 6.2b (see Excel walk-through 4.4 for help on how to assign ranks). Do countries with a high overall rank also tend to rank highly on individual criteria?
    在新选项卡中,创建一个类似于图 6.2a 的表格,该表格显示了 20 个国家/地区中每个国家/地区所有公司的平均管理分数,并填写所需的值。总分和三个单独标准的变量是 “管理”、“监控”、“目标” 和 “人员”。您可能会发现使用 Excel 的数据透视表选项很有帮助 - 如果您需要指导,请参阅 Excel 演练 3.1。对于每个标准,从最高到最低对国家/地区进行排名,然后创建并填写一个表格,如图 6.2b 所示(有关如何分配排名的帮助,请参阅 Excel 演练 4.4)。总体排名较高的国家是否也倾向于在单个标准上排名靠前?
Country  国家 Overall management (mean)
整体管理(平均值)
Monitoring management (mean)
监控管理 (mean)
Targets management (mean)
目标管理 (平均值)
Incentives management (mean)
激励管理 (mean)
         
         

Figure 6.2a Mean of management scores.
图 6.2a 管理分数的平均值。

Country  国家 Overall management (rank)
整体管理(等级)
Monitoring management (rank)
监控管理 (等级)
Targets management (rank)
目标管理 (等级)
Incentives management (rank)
激励管理 (等级)
         
         

Figure 6.2b Rank according to management scores.
图 6.2b 根据管理分数进行排名。

  • Create a bar chart that shows the average overall management score (the variable ‘management’) for each country, ordered from highest to lowest. (Hint: You will need to sort your data from highest to lowest, so that it appears correctly in the chart.) Your chart should look similar to Figure 6.1.
    创建一个条形图,显示每个国家/地区的平均总体管理评分(变量 'management'),从高到低排序。(提示:您需要从最高到最低对数据进行排序,以便数据正确显示在图表中。您的图表应类似于图 6.1
  • Compare your chart with Figure 1 in Bloom et al. (2012). Can you explain why your chart is slightly different? (Hint: See the note at the bottom of Figure 1.)
    将您的图表与 Bloom et al. (2012) 中的图 1 进行比较。您能解释一下为什么您的图表略有不同吗?(提示:请参阅图 1 底部的注释。

To look at how management quality varies within countries, instead of just looking at the mean, we can use column charts to visualize the entire distribution of scores (as in Empirical Project 1). To compare distributions, we have to use the same horizontal axis, so we will first need to make a frequency table for each distribution to be used. Also, since each country has a different number of observations, we will use percentages instead of frequencies as the vertical axis variable.
为了了解国家内部管理质量的变化,我们可以使用柱状图来可视化分数的整个分布,而不仅仅是查看平均值(如实证项目 1)。为了比较分布,我们必须使用相同的横轴,因此我们首先需要为要使用的每个分布制作一个频率表。此外,由于每个国家/地区的观测值数量不同,因此我们将使用百分比而不是频率作为纵轴变量。

  1. For three countries of your choice and for the US, carry out the following:
    对于您选择的三个国家/地区和美国,请执行以下操作:
  • Using the overall management score (variable ‘management’), create and fill in a frequency table similar to Figure 6.3 below for the US, and separately for each chosen country. The values in the first column should range from 1 to 5, in intervals of 0.2. (Hint: To count observations for a specific country only, you will need to use the IF function and FREQUENCY function together, as shown in Excel walk-through 6.1).
    使用总体管理分数(变量 'management'),为美国创建并填写类似于下图 6.3 的频率表,并为每个选定的国家单独填写。第一列中的值应介于 1 到 5 之间,间隔为 0.2。(提示:要仅计算特定国家/地区的观测值,您需要同时使用 IF 函数和 FREQUENCY 函数,如 Excel 演练 6.1 中所示。
Range of management score
管理评分范围
Frequency  频率 Percentage of firms (%)  公司百分比 (%)
1.00
1.20
4.80
5.00

Figure 6.3 Frequency table for overall management score.
图 6.3 总体管理分数的频率表。

  • Plot a column chart for each country to show the distribution of management scores, with the percentage of firms on the vertical axis and the range of management scores on the horizontal axis. On each country’s chart, plot the distribution of the US on top of that country’s distribution, as shown in Excel walk-through 6.2.
    为每个国家绘制柱状图以显示管理分数的分布,纵轴上是公司的百分比,横轴上是管理分数的范围。在每个国家/地区的图表上,将美国的分布绘制在该国家/地区的分布之上,如 Excel 演练 6.2 中所示。
  • Describe any visual similarities and differences between the distributions of your chosen countries and that of the US. (Hint: For example look at where the distribution is centred, the percentages of observations on the left tail or the right tail of the distribution, and how spread out the scores are.)

Excel walk-through 6.1 Using Excel’s IF function

Follow the walk-through in the CORE video, or in Figure 6.4, to find out how to use Excel’s IF function.

The data
: In this example, we will make a frequency table for the US data in Columns A to C. It’s a good idea to put all the tables in a separate place from the data.
Fullscreen

The data

In this example, we will make a frequency table for the US data in Columns A to C. It’s a good idea to put all the tables in a separate place from the data.

Calculating frequencies for a particular country
: We want to calculate the frequency but only for firms in the US. To do this, add an IF condition within the FREQUENCY function to tell Excel which data to use.
Fullscreen

Calculating frequencies for a particular country

We want to calculate the frequency but only for firms in the US. To do this, add an IF condition within the FREQUENCY function to tell Excel which data to use.

Calculating frequencies for a particular country
: By putting the IF function inside the FREQUENCY function, Excel will only use the data that satisfies the condition we specified (firms in the US).
Fullscreen

Calculating frequencies for a particular country

By putting the IF function inside the FREQUENCY function, Excel will only use the data that satisfies the condition we specified (firms in the US).

Calculating frequencies for a particular country
: The formula that is completed in step 4 is: ‘(=FREQUENCY(IF(AMP_graph_manufacturing!N:N=”United States”,AMP_graph_manufacturing!A:A),Sheet2!A2:A22))’. After step 4, all the frequency values will be filled in.
Fullscreen

Calculating frequencies for a particular country

The formula that is completed in step 4 is: ‘(=FREQUENCY(IF(AMP_graph_manufacturing!N:N=”United States”,AMP_graph_manufacturing!A:A),Sheet2!A2:A22))’. After step 4, all the frequency values will be filled in.

Using frequencies to calculate percentages
: The $ symbol before the row numbers in the formula tells Excel to keep these row numbers the same when copying the formula to other cells. We used it here because we are dividing the frequency value by the total number of observations (cells B2 to B22).
Fullscreen

Using frequencies to calculate percentages

The $ symbol before the row numbers in the formula tells Excel to keep these row numbers the same when copying the formula to other cells. We used it here because we are dividing the frequency value by the total number of observations (cells B2 to B22).

Figure 6.4 How to use Excel’s IF function within another function.

Excel walk-through 6.2 Overlaying one column chart over another

The data
: In this example, we use data for the US (Columns A to C) and Chile (Columns F to H), and plot a column chart of the percentages in Columns C and H (see the steps in Figure 6.4 for how to calculate these).
Fullscreen

The data

In this example, we use data for the US (Columns A to C) and Chile (Columns F to H), and plot a column chart of the percentages in Columns C and H (see the steps in Figure 6.4 for how to calculate these).

Plot a column chart
: After step 3, the column chart will look like the one shown above.
Fullscreen

Plot a column chart

After step 3, the column chart will look like the one shown above.

Change the appearance of the columns
: First, we will remove the gaps between the columns, and make the columns overlap (rather than being plotted side-by-side). After step 7, there will be a vertical axis on the left and right side of the chart.
Fullscreen

Change the appearance of the columns

First, we will remove the gaps between the columns, and make the columns overlap (rather than being plotted side-by-side). After step 7, there will be a vertical axis on the left and right side of the chart.

Change the vertical axis values
: In order to compare the distributions, make sure the left and right vertical axis have the same labels.
Fullscreen

Change the vertical axis values

In order to compare the distributions, make sure the left and right vertical axis have the same labels.

Change the horizontal axis values and series names
: Now, change the horizontal axis values to match the data in our frequency tables.
Fullscreen

Change the horizontal axis values and series names

Now, change the horizontal axis values to match the data in our frequency tables.

Change the horizontal axis values and series names
: After step 12, the horizontal axis value for the selected columns will change.
Fullscreen

Change the horizontal axis values and series names

After step 12, the horizontal axis value for the selected columns will change.

Change the horizontal axis values and series names
: Now, we will change the series names to country names.
Fullscreen

Change the horizontal axis values and series names

Now, we will change the series names to country names.

Change the horizontal axis values and series names
: After step 14, the legend entry for that country will now be the country name.
Fullscreen

Change the horizontal axis values and series names

After step 14, the legend entry for that country will now be the country name.

Change the horizontal axis values and series names.
: Since we have two vertical axes on our chart (one for each data series), we need to change the horizontal axis labels for both axes before Excel will update the chart.
Fullscreen

Change the horizontal axis values and series names.

Since we have two vertical axes on our chart (one for each data series), we need to change the horizontal axis labels for both axes before Excel will update the chart.

Change the horizontal axis values and series names.
: Finally, change the shading of the columns so we can see the distributions of both countries. After step 19, the distributions of both countries should be clearly visible.
Fullscreen

Change the horizontal axis values and series names.

Finally, change the shading of the columns so we can see the distributions of both countries. After step 19, the distributions of both countries should be clearly visible.

Figure 6.5 How to overlay one column chart over another.

Example of two overlapping distributions on the same column chart.
Fullscreen

Figure 6.6 Example of two overlapping distributions on the same column chart.

box and whisker plot
A graphic display of the range and quartiles of a distribution, where the first and third quartile form the ‘box’ and the maximum and minimum values form the ‘whiskers’.

Another way to visualize distributions is a box and whisker plotbox and whisker plot A graphic display of the range and quartiles of a distribution, where the first and third quartile form the ‘box’ and the maximum and minimum values form the ‘whiskers’.⁠, which shows some parts of a distribution rather than the whole distribution. We can use box and whisker plots to compare particular aspects of distributions more easily than when looking at the entire distribution.

As shown in Figure 6.7, the ‘box’ consists of the first quartile (value corresponding to the bottom 25 per cent, or 25th percentile, of all values), the median, and the third quartile (75th percentile). The ‘whiskers’ are the minimum and maximum values. (In Excel, the ‘whiskers’ may not be the actual maximum or minimum, since any values larger than 1.5 times the width of the box are considered outliers and are shown as separate points.)

Example of a box and whisker plot. (Note: In Excel, the mean value is shown by X. In general, the median may not be in the centre of the box, and can differ greatly from the mean. Using the data shown in Figure 6.7 for a variable from the dataset, the mean and median are very similar.)
Fullscreen

Figure 6.7 Example of a box and whisker plot.
(Note: In Excel, the mean value is shown by X. In general, the median may not be in the centre of the box, and can differ greatly from the mean. Using the data shown in Figure 6.7 for a variable from the dataset, the mean and median are very similar.)

  1. Using the same countries you chose in Question 3:
  • Make a box and whisker plot for each country and the US, showing the distribution of management scores. You can either make a separate chart for each country or show all countries in the same plot. To check that your plots make sense, compare your box and whisker plots to the distributions from Question 3.
  • Use your box and whisker plots to add to your comparisons from Question 3(c).

Excel walk-through 6.3 Drawing box and whisker plots

Follow the walk-through in the CORE video, or in Figure 6.8, to find out how to draw a box and whisker plot in Excel.

The data
: In this example, we will use data for the US (Column A) and Chile (Column B). To create a box and whisker plot of more than one variable, each variable needs to be in a separate column. You will need to filter, then copy and paste the required data into a new tab in Excel.
Fullscreen

The data

In this example, we will use data for the US (Column A) and Chile (Column B). To create a box and whisker plot of more than one variable, each variable needs to be in a separate column. You will need to filter, then copy and paste the required data into a new tab in Excel.

Create box and whisker plots
: After step 3, your box and whisker plot will look like the one above. The plots are ordered according to the columns in Excel, so the first plot corresponds to Column A, the second plot corresponds to Column B, and so on.
Fullscreen

Create box and whisker plots

After step 3, your box and whisker plot will look like the one above. The plots are ordered according to the columns in Excel, so the first plot corresponds to Column A, the second plot corresponds to Column B, and so on.

Add a chart legend
: First, we will add a legend to indicate to which country each plot corresponds.
Fullscreen

Add a chart legend

First, we will add a legend to indicate to which country each plot corresponds.

Change the legend entries
: Now, we will change the labels in the legend. If your legend is already correctly labelled, skip Steps 6–9.
Fullscreen

Change the legend entries

Now, we will change the labels in the legend. If your legend is already correctly labelled, skip Steps 6–9.

Change the legend entries
: After changing the name of the data series, the label in the legend will change.
Fullscreen

Change the legend entries

After changing the name of the data series, the label in the legend will change.

Change the legend entries
: After step 9, your data should now be correctly labelled in the chart legend.
Fullscreen

Change the legend entries

After step 9, your data should now be correctly labelled in the chart legend.

Remove the outliers (optional)
: Excel shows any observations that are greater than 1.5 times the box width (in absolute value) separately as outliers. If your chart is too cluttered with outliers, you can remove them to make your chart more readable.
Fullscreen

Remove the outliers (optional)

Excel shows any observations that are greater than 1.5 times the box width (in absolute value) separately as outliers. If your chart is too cluttered with outliers, you can remove them to make your chart more readable.

Figure 6.8 How to create box and whisker plots.

From the manufacturing data, firms in the US seem to be managed better (on average) than firms in other countries. To investigate whether this is the case in other sectors, we will use data gathered on hospitals and schools.

  1. Using the data for hospitals and schools (AMP_graph_public.csv):
  • Create a table for hospitals and schools, showing the mean management score and criteria score (monitoring, targets, incentives) for each country, as in Figure 6.2a. (Hint: You may find it helpful to use Excel’s PivotTable option—see Excel walk-through 3.1.)
  • Make separate bar charts for hospitals and schools showing the mean overall management score for each country, sorted from highest to lowest, as in Figure 6.1. Are the country rankings similar to those in manufacturing?
  • Using your average criteria scores from Question 5(a), suggest some explanations for the observed rankings in either hospitals or schools. (You may find it helpful to research healthcare or educational policies and reforms in those countries to support your explanations.)