Davidson College Mark C. Foley
Department of Economics Fall
2002
Principles
of Economics
Data
Collection and Analysis Assignment
Due Friday,
October 25th, in class[1]
Educated discourse requires us to become
informed of at least the basic facts surrounding an issue. This doesn’t mean that only experts can
participate in discussion; but it does mean we cannot fail to make simple
attempts to figure out what is easily found.
The first problem many of us face in this effort is finding a data source. Where do we get such basic numbers? In order to make you familiar with some of
the basic sources that students of economics rely on, I will assign you to go
and find basic facts surrounding international income inequality, income
inequality across U.S. states, and the rules which the Department of Justice
uses to evaluate potential mergers of companies.
In this assignment you will be asked to do a variety
of tasks using Excel. There is no one way to do any of these exercises,
but I wouldn’t be surprised if you had to make good use of some fairly well
hidden tools in Excel. For example, you might check out the “Paste
Special” command. In particular, the “Paste Values” and “Transpose” commands
can be pretty helpful at times. Also, in making Charts, the “Chart – Source
Data” can be a very helpful tool when putting multiple series into one Chart.
Lastly, if you want to do something but do not know how, use the “help” feature
in Excel. It can be quite good.
Your final document should be a Word document
with each section clearly identified, the appropriate graphs and tables from
your Excel work (copy and paste them into Word), and the
paragraphs you write analyzing the data you’ve collected.
Part A
Go to the Internal Revenue Service Web site and find
income tax return data for North Carolina and another state of your choice ( http://www.irs.gov/tax_stats/soi/ind_st.html
). Click on “Individuals” and then data by “State Income.” Look for the file “00IN34NC.XLS” and an
analogous file from the state of your choice.
Download these files and save them (right-click on the link and choose
Save Target As ... ). These data
include information on income tax returns filed for different income categories
($0.01 to $10,000, $10,000-$20,000, etc.).
Using Excel,
1.
Calculate the proportion of all returns that are filed for each income class in
both states. For example, if a total of 100 returns were filed and 22 of then
came from households with annual income less than $20,000, this value would be
22%.
2.
Create a well-labeled pie chart for North Carolina that shows the proportion of
returns that come from each income category.
3.
Create a well-labeled pie chart for your other state that shows the proportion
of returns that come from each income category.
4.
Create a well-labeled column chart that compares the proportion of returns that
come from each income category in both states.
5.
In no more than one brief paragraph, on the basis of this information, compare
and contrast North Carolina to the other state.
6. Calculate the proportion of all adjusted gross
income attributable to each income class in both states. For example, if total
adjusted gross income for North Carolina were $12,000,000 and $9,000,000 of
this came from households with annual income more than $200,000 this value
would be 75%.
7. Create two columns of numbers for each state. The
first column is the cumulative percentage of households filing returns with
adjusted gross income less than or equal to a specific income category. The
second column is the cumulative proportion of total adjusted gross income
attributable to households with adjusted gross income less than or equal to the
specific income category. Order the income categories so they go from lowest to
highest. For example, suppose that in North Carolina the values associated with
part A-1 and the values associated with part A-5 as follows.
|
Income Category |
Percent of Returns filed
in Income Category |
Percent of Total AGI
Attributable to Income Category |
|
Less
than $20,000 |
20% |
3% |
|
$20,000-$30,000 |
15% |
7% |
|
$30,000-$50,000 |
25% |
15% |
|
$50,000-$75,000 |
13% |
10% |
|
$75,000-$100,000 |
12% |
15% |
|
$100,000-$200,000 |
10% |
20% |
|
Over
$200,000 |
5% |
30% |
Then
the answer to this question would be the following table.
|
Income Category |
Percent of Returns filed
in Income Category |
Percent of Total AGI
Attributable to Income Category |
|
Less
than $0 |
0% |
0% |
|
Less
than $20,000 |
20% |
3% |
|
Less
than $30,000 |
35% |
10% |
|
Less
than $50,000 |
60% |
25% |
|
Less
than $75,000 |
73% |
35% |
|
Less
than $100,000 |
85% |
50% |
|
Less
than $200,000 |
95% |
70% |
|
All |
100% |
100% |
Plot
the percent of the population with income less than a value in the income
category against the associated percent of total adjusted gross income for both
states. I have plotted the pretend data above. You should end up with two
relationships, one for each state, on the same diagram (properly labeled).
This relationship is called a Lorenz
Curve and is commonly used as a way of summarizing the distribution of
income for some group of people.
8.
What would a Lorenz Curve look like if there were a very equal distribution of
income?
9.
What would a Lorenz Curve look like for a very unequal distribution of income?
10.
In no more than one short paragraph, compare the Lorenz Curve for North
Carolina with the Lorenz Curve for the other state.
Part B: International Income Inequality
1.
Create a table in Excel, which updates Table 20-3 from Principles of
Economics, 2nd edition, by N. Gregory Mankiw with the latest
data from the 2002 World Development Report.
Feel free to add other countries of interest to you, but include at
least those in Table 20-3.
2.
Add another column called “Gini Coefficient,” after the “Top Fifth”
column. Calculate the Gini
Coefficients for each country in 1998 and 2002.
3.
Copy and paste both tables into your main Word document.
4.
In no more than one short paragraph, compare the Gini coefficients across
countries and over time.
Part C: U.S. Department of Justice Horizontal Merger Guidelines
1. Go to this website http://www.usdoj.gov/atr/public/guidelines/horiz_book/hmg1.html
and find the screening rules which DOJ uses to assess the anti-competitive
implications of potential mergers (section 1.5).
2.
Pick two industries from this website http://www.activemedia-guide.com/mrksh_profile.htm
and click on them to find the market shares of companies in that industry.
4. Using Excel, calculate the
Herfindahl-Hirschman Index (HHI) for each industry. If there is an “Other” and/or “Group” category (i.e., a market
share not assigned to a specific company, but a group of smaller companies),
then assume that each small company has 1% of the market. For example, consider the following table.
|
Potato Chip Market Shares
in 2000 |
|
|
Company |
Market Share (%) |
|
Frito Lay |
67.8 |
|
Wise |
3.2 |
|
Utz |
2.9 |
|
Jays |
2.0 |
|
Private Label |
6.1 |
|
Other |
18 |
The HHI for this market would be equal to 67.82
+ 3.22 + 2.92 + 22 + (12 + 12
+ 12 + 12 + 12 + 1.12) + (18*12)
= 4643.7. Highly concentrated. Note that we’ve divided up “Private Label”
into 6 companies, 5 with market share of 1% and one with 1.1%. And “Other” is assumed to be 18 small
companies, also with 1% market share each.
5. Copy and paste your table into Word. Would your industries be characterized as
“unconcentrated,” “moderately concentrated,” or “highly concentrated”?
6. In no more than one double-spaced page, compare and contrast the concentrations in your industries given what you know about them and their history and discuss possible mergers which would raise DOJ’s eyebrows.