Empirical Project 12 Working in Excel

Excel-specific learning objectives

In addition to the learning objectives for this project, in this section you will learn how to convert cells from text to number format.

Part 12.1 Inequality

Learning objectives for this part

  • draw Lorenz curves and calculate Gini coefficients
  • assess the effect of a policy on income inequality
  • convert nominal values to real values (extension).

One reason cited for Scheme $6,000 was to share the gains from economic growth among everyone in the society. We will be using household income data collected by the Hong Kong government to assess the potential effects of this scheme.

Download the data. The spreadsheet contains information about household incomes for certain percentiles of the population. These incomes are ‘pre-intervention’, meaning that they do not include the effects of handouts or policy interventions from the government. (If you are curious about the difference between the two tables in this spreadsheet, see the extension section ‘Nominal and real values’ at the end of Part 12.1.)

  1. Using the table ‘Monthly real household income (pre-tax, $HKD)’, plot a separate line chart for each percentile, with year on the horizontal axis and income on the vertical axis. Describe any patterns you see over time.

Now we will use this data to draw Lorenz curves and compare changes in the income distribution for 2011–2012. One way to do this is to make the following simplifying assumptions:

  1. Draw Lorenz curves for 2011 and 2012 by carrying out the following:
Cumulative share of the population (%) Perfect equality line Cumulative share of income in 2011 (%) Cumulative share of income in 2012 (%)
0 0 0 0
15 15 0
25 25
50 50
75 75
85 85
100 100 100 100

Figure 12.1 Cumulative share of income, for some percentiles of the population.

Now we will compare the Gini coefficients in 2011, 2012 (with the cash handout), and 2013 (a year after the policy came into effect). Instead of calculating the coefficients manually, we will use an online Gini coefficient calculator, which takes a list of values and calculates the Gini coefficient.

  1. Calculate the Gini coefficient by carrying out the following:
Percentile 2011 2012 2013
1 0.00
2 0.00
3 0.00
98 44,516.67
99 44,516.67
100 44,516.67

Figure 12.2 Incomes earned by each percentile of the population.

Hint: The CONCATENATE function can combine the contents of cells and/or text together. The formula =CONCATENATE(cell1, “,”) adds a comma after the contents of cell1.

  1. In our analysis we assumed that the $6,000 handout was the only policy that affected households in 2012. In reality a household’s disposable income will also depend on taxes and transfers. Without doing additional calculations, explain what would happen to the shape of the Lorenz curve and inequality in 2012 if:

Extension Nominal and real values

In this extension section, we will discuss how the table ‘Monthly nominal household income (pre-tax, $HKD)’ taken from the ‘Hong Kong poverty situation report 2016’ was used to create the table ‘Monthly real household income (pre-tax, $HKD)’, and why we needed to make this conversion.

An increase in the general price level in the economy. Usually measured over a year. See also: deflation, disinflation.

The difference between real and nominal income is that real income takes inflation into account. You may be familiar with the concept of inflation, which is an increase in the general price level in the economy. Usually inflation is measured by taking a fixed bundle of goods and services and looking at how much it would cost to buy that bundle, compared to a reference year. (For more details about real vs nominal variables, see the Einstein ‘Comparing income at different times, and across different countries’ in Section 1.2 of The Economy). If the bundle of goods and services has become more expensive, then we conclude that the price level in the economy has increased.

In this case, the values from 2010 onwards have been adjusted to account for the fact that prices have increased since 2009, so the same income would be able to purchase fewer goods and services. Without making this adjustment, we would conclude that households in the 15th percentile had the same purchasing power in 2009 and 2010, when in fact they do not as they can buy fewer goods and services in 2010 because of the overall price increase.

  1. Convert nominal values to real values, using 2009 as the reference year:
  • To understand what happens to a given nominal value over time due to inflation, create a table as in Figure 12.3 below, and fill it in according to the percentage increases shown. (These percentages were taken from the Monthly Digest of Statistics.) (For example, $1 in 2009 would be $1 × (1 + (2.40/100)) = $1.024 in 2010.) For greater accuracy, round your answers to three decimal places. With a starting value of $1 in 2009, what would the value be in 2016?
Year Percentage increase (from previous year) Inflation index
2009   1.000
2010 2.40 1.024
2011 5.30  
2012 4.10  
2013 4.30  
2014 4.40  
2015 3.00  
2016 2.40  

Figure 12.3 Creating an index-based series from percentage increases.

  • Use this table to convert nominal incomes to real incomes by dividing the nominal income by the corresponding value in the third column (for example, divide nominal incomes in 2010 by 1.024 to get the value in 2009 terms). You should get the same values as in the ‘real household income’ table.

Part 12.2 Government popularity

Learning objectives for this part

  • assess the effect of a policy on government popularity.

One possible reason why the government implemented Scheme $6,000 was to gain public approval, since there was some pressure on the government to spend the surplus on alleviating current social issues rather than reinvesting it (for example, in pension schemes). We will use a public opinion poll conducted by the University of Hong Kong to assess whether this scheme could have improved public satisfaction with the government.

  1. Think about the groups who would be affected by this scheme (for example, the government or members of the public). Who would be likely to support or oppose this scheme, and why?

Download the data:

Excel walk-through 12.1 Using SUBSTITUTE to clean text in cells

Figure 12.4 How to use SUBSTITUTE to clean text in cells by removing spaces before and after the text.

The data

We will use the data in Column I as an example.

Numbers stored as text

Excel currently recognizes the values in Column I as text, so when you try to plot a line chart, it will come out like this, with no actual data being plotted.

Remove unnecessary spaces

Excel thinks the numbers are text because there is a space before and after the number. To remove the spaces, we use the SUBSTITUTE function (to remove non-breaking spaces, i.e. spaces that do not result in a new line), then the TRIM function (which removes spaces before and after text). Note: For Mac users, type ‘CHAR(202)’ in the formula instead of ‘CHAR(160)’.

Copy and paste into a new column

The numbers in Column J are still stored as text, but we cannot convert Column J to numbers because there is a formula in those cells. Instead, we need to copy and paste (values only) into a new column, then convert the pasted data to numbers.

Convert text to numbers

Now Excel will recognize that numbers are stored as text, and will give you the option to convert them to numbers.

Numbers correctly stored in Excel

Now Excel recognizes the values in Column K as numbers, so you can use them to plot a line chart.

  1. Assess public satisfaction with the government by carrying out the following:
  1. In 2018, the government decided to do another cash handout. Read the article ‘Hong Kong cash handout scheme will cost government HK$330 million to administer’ and discuss how this scheme differs from the 2011 scheme. Explain whether you think this policy is an improvement over the 2011 scheme.
  1. Suppose you are a policymaker in a developed country with a large budget surplus, and one of the government’s aims is to reduce income inequality. Would you recommend that the government implement a scheme similar to either the 2011 or 2018 scheme? If you recommend a cash handout, suggest some modifications that could make the scheme more effective. If not, suggest other policies that may be more effective in reducing inequality. (You may find it helpful to research policies aimed at reducing income inequality, for example Universal Basic Income, which some countries have tried.)