You work at a firm which provides consulting services to lobby groups and government agencies.

Kelly is a client who represents an organization concerned with social justice issues, particularly those relating to income inequality. Kellys organization is convinced the Australian tax system is promoting income inequality due to concessional treatments for high income earners. Lee, your manager, asked Kelly how they came to this conclusion. Kelly replied they analyzed data in Table 16 of the Australian Taxation Statistics (see below) and found the Gini index (or coefficient) for taxable income was greater than for total income. This surprised Lee, as the Australian tax system is supposed to be designed to reduce income inequality. Lee agreed to investigate this issue.

Download the spreadsheet version of Table 16 from the link below. This table presents data on the percentile distribution of tax payable by individuals for the 2017-2018 tax year.

Individuals detailed tables | Australian Taxation Office (ato.gov.au)

Save a copy on your drive, copy this file and use this as the working copy. Save this using the student id number of the student who will submit the file.

Lee suggested that before you commence the analysis and tasks below, you spend some time familiarizing yourself with the data. Try to work out what it is showing. For example, ensure you understand the relationship between taxable income, total deductions, net tax, and total income. Lee also suggested you do a bit of research to familiarize yourself with the Gini index (or coefficient) and the Lorenz curve. In the tasks that follow, unless told otherwise, you need to combine the figures for males and females for each percentage cohort and use the aggregated figure. For example, for the first cohort ($20,560 or less), you would use the sum of 55,187 and 53587 = 108,774.

There are three tabs on the original spreadsheet. Please use the second tab (Table 16A), unless told otherwise.

Lee conducted some research into how to create charts of Lorenz curves using Excel and likes this explanation. https://www.youtube.com/watch?v=vDtmaK3E_C0. You are welcome to use other sources if you wish. Lee mumbled something about having to make some adjustments.

Lee stated the Gini index or coefficient must be calculated using the method in Chapter 1 of the Microsoft Excel book by Winston, (see the last problem in that chapter) and the method in the video referred to above. Use the method in the video to check your result using the formula in Winstons book. There may be minor differences in the Gini indexes or coefficients produced under the two methods. Do not get too worried about this. Use your judgement to determine whether a difference is minor.

Required

1. Part A In the working copy workbook, copy the content of the first tab to a new tab called something like Check of 1%. Lee does not understand the numbers in column d, as they seem to be all over the place. Select column e in this tab and insert a column. Use this column to see if you can establish whether the table is arranged in 1% increments.

Part B Prepare a brief explanation for Lee. Do this in a Word document.

2. In the same tab (Check of 1%), insert a column and use a formula to calculate the total taxable income per percentile. Repeat for the total income per percentile. As stated above, these figures will present the combined income of males and females in each percentile.

3. Part A

Lee wants to replicate the analysis conducted by Kelly and asked you to prepare a model which will calculate the Gini index or coefficient for taxable income. This model will also provide the data for a chart showing the Lorenz curve for taxable income. This model will use the combined figure for males and females in each percentile.

Lee wants this model to be built in a new tab, called something like Kelly taxable income.

Before you build the model in the Kelly taxable income tab, do the following. Copy and paste relevant data from the Check of 1% tab to a tab called Copy of Check of 1%. You will use this tab as the data tab for the models that are described below (and the Kelly-taxable income tab). When developing the models, you will link to this data tab to pick up variables you need in these models. Please note, very heavy penalties will apply to any models which contain dead data. Cells will either be linked to the tab Copy of check of 1% or they will contain formulae.

Part B Lee also wants to build a model which calculates Gini and Lorenz for total income. Lee said you should be able to use the same model structure for both types of income. Lee wants these models and data to be presented on separate tabs. Open a new tab and call it something like Kelly - total income.

Part C Compare the Gini index for taxable income to the index for total income. Discuss briefly if your results indicate the tax system is reducing income inequality. Do this in a Word document.

Part D You discuss your findings with a colleague. That person gave you a very strange look. (It is the sort of look you would get if you stepped on something nasty deposited by a dog on the street, then walked onto a lovely white carpet in an expensive house, owned by very fussy people.) You have a feeling something is very wrong. Identify the issue. Prepare a brief explanation for Kelly in a Word document.

4. Open a new tab and name it something like Kelly adjusted. Build a model that addresses Kellys needs. Remember, Kelly wanted to measure the impact of the tax system on income inequality. Draw on models from above so that you focus on variables that are relevant.

5. Lee wants to develop a more refined model. Lee is particularly interested in how the Gini indexes change as the top brackets of taxpayers change. That is, the model needs to be designed so that the user can exclude the top x percent, by selecting a figure. For example, if Kelly wanted to see what the Gini index is for all taxpayers excluding the top 10 percent, the model would be designed so that only the bottom 90 percent of the population were included in the analysis. In this case, the model would need to be designed so that the target population can be selected, simply by entering 90 in a certain cell.

Part A Open two new tabs called something like Refined taxable income and Refined after-tax income and build the models Lee wants.

Part B

Open a new tab. Label it something like Summary. Near the top, type in text similar to Target number of percentiles. Near this, enter a positive number less than 100. This figure will be used to set the targets referred to in the paragraph above. For example, enter 90 in this cell and it will be linked to the sheets with the two refined models. Staying on the Summary tab, create links from the refined sheets and the original models, so the user can see the Gini indexes for each of the refined models and the original models.

Part C

Prepare some talking points for Lee to discuss with Kelly regarding the analysis conducted by Kellys organization, compared to yours. Also include a discussion of anything relevant you observe in the refined models, if you observe anything relevant.

6. Lee wants you to build a model that will allow Kelly to play around with the tax rates and tax thresholds, in order to see which combination of rates and thresholds are likely to achieve their goal of reduced income inequality. Kelly is aware that this could be achieved by applying very high tax rates to individuals in the highest percentiles. However, this is likely to create a lot of political heat.

Part A Open a new tab, call it something like Modified taxes. Populate this tab with relevant data from other tabs. Lee suggests you use the taxable income figures per percentile. You do not want to use the after-tax figures, as you will be calculating these and using them to calculate Gini indexes. This time you do not have to worry about plotting Lorenz curves.

Get the tax rates for individuals for 2017-18 and place them in the Summary tab. Keep an original copy in the Summary tab. Staying in the Summary tab, extract the relevant data so that you can use it in a model. You will use the extracted material in the Summary tab to calculate the after-tax income for each percentage group in the model in the Modified taxes tab.

In the Summary tab, show the Gini index for the modified taxes. Also, in the Summary tab, show the impact on aggregate individual income taxes collected by the Government, if the tax rates and thresholds are modified.

Part B Provide a brief report to Kelly, explaining any weaknesses or assumptions in the modified taxes model. Also discuss whether the most effective ways of raising income are likely to politically attractive or unattractive. Lee indicated Kelly had mentioned attempting to achieve a Gini index of 27. Discuss whether this is technically possible and whether it is likely to be politically achievable.

7. A few days after you provided your advice to Kelly, another client, Micki, came in. Micki represents a lobby group concerned with income inequality, particularly if it is created or supported by the tax system. Mickis group has been lobbying the government to remove all capital gains tax (CGT) concessions. Briefly, if a taxpayer sells certain assets for a gain, the tax payable will be less than would ordinarily be the case. There is a simple example of one of the concessions in the Attachment below. You do not need to have a detailed understanding of CGT legislation, thankfully (it is like a bowl of madness, on steroids).

Predictably, the lobbying by Mickis group created a hornets nest of opposition. One of the main claims by their opponents is that removal of the removal of this concession will have a severely negative impact on people in the lower and middle tax brackets, such as teachers and nurses etc, as the CGT concession helps these taxpayers build their wealth. The opponents have suggested it is a lie to argue that the CGT treatment provides an unfair advantage to wealthy taxpayers. Further, those who want to change the CGT treatment are engaging in a form of class warfare, trying to create envy and unfairly tax the hard-working middle class who are trying to build their wealth.

Required

Lee wants you to use the data you collected for Kelly to see if you can provide any evidence to support or contradict Mickis opinion. Lee mentioned a couple of interesting articles relating to the Palma ratio that may be relevant (see Attachment, below). Lee suggests you use the last two columns in Table 16A (columns AD and AE (in the original spreadsheet) - Total current year capital gain no., and, Total current year capital gain $). Lee advised you to be careful, as you need to use a single figure for each of these columns for each percentile, similar to what you did with Kellys models. These figures should be copied and pasted to the tab Copy of check of 1%.

Part A Open a new tab, called Palma. Using links and formulas, build a model to address Mickis concerns, using relevant data that relate to the Palma ratio for taxable income, after-tax income, total income. Also do this for the CGT columns referred to above.

Part B Prepare a brief response for Micki, addressing the comments made by opponents of their proposal relating to CGT. Also include discussion of the impact income taxes (excluding CGT) have on income inequality in Australia. A strong response will incorporate an analysis of Palmas main findings and a detailed analysis of the data.

Part C

Lee also wants you to estimate the average capital gain for each relevant taxpayer in a percentile, if the concessional treatment was terminated. (Note, not all taxpayers in a percentile had a capital gain in that year.) Use the tax rates for individuals for the year 2017-18 to estimate the marginal amount of CGT paid by a taxpayer in each percentile, assuming there were no concessions for CGT. Remember, do not hard code in models. Use formulas to convert the income percentiles to data you can use in this model. Lee requested you place this in the Palma tab.

Part D

Discuss the implications of your findings in Part C for Mickis proposal.