Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

ACCY 451: Advanced Income Tax Problems

Fall 2022

Graduate Case #2

Required: Provide your manager with a memo responding to the following inquiries. There will be several charts that should be included directly in the memo. Also upload your Excel file to show how you created the charts (i.e., to show your work).

Overview

You have just started a job at the IRS in the Large Business and International Division. As part of routine planning activities, your manager has asked you to analyze financial reporting and tax return information for companies included in a large dataset, discussed below. Your analysis should answer the specific inquiries included at the end of this case, and will provide insights into recent trends and a starting point for selecting companies and industries for further investigation.

The Dataset

The dataset includes simulated financial reporting and tax return information for 16,589 fictitious companies across 23 years (2000-2022). The unbalanced panel of 380,705 firm-year observations features income statement, balance sheet, statement of cash flows, and IRS Schedule M-3 data fields. The dataset was developed by Michael Donohoe, Head and Professor of Accountancy at the University of Illinois at Urbana-Champaign (see Case Studies, Large Tax Data for Use in Cases (illinois.edu)).

The first tab (“Data Description”) includes the list of variables available in the dataset and descriptions.

The second tab (“Full Data”) includes the full dataset for the fictitious companies You will not need all variables for this case, but having the entire dataset available will provide practice identifying relevant information. You will use this tab for questions 1 – 3 below.

The third tab (“Manufacturing”) includes data on manufacturing firms (SIC codes 20-39). You will use this tab for questions 4 – 8 below. Because we will be analyzing effective tax rates (ETRs), this data pull is from the full database also included a requirement that the firm-year must have positive pre-tax income (variable ptit).  

The fourth tab (“Retail”) includes data on retail firms (SIC codes 52-59). You will use this tab for questions 9 – 11 below. This data pull only includes retail firms in 2022 and required that the firm had positive pre-tax income (variable ptit) and GAAP ETR (getr) and Cash ETR (cetr) be between [0, 1].

General Comments/Formatting

Be sure to read the inquiries below carefully to ensure you are analyzing the correct variables/data. Generally format numbers in a readable form (comma separators for large dollar figures, no decimals) and clearly label any charts or figures. For each inquiry below, in addition to answering the question, please provide a concise description of any trends or observations, particularly if something stands out in the data worth mentioning to your manager. At this point you are not required to dig in to explain any anomalies or outliers in the data, but you should identify them to bring them to your manager’s attention.

Inquiries:

[use “Full Data” tab for inquiries 1-3]

1. What is the total federal tax expense by year for companies in the dataset? What is the total cash tax paid by year for companies in the dataset? For this question, include both a chart and a line graph.

2. What is the total federal tax expense by industry (2-digit Standard Industry Classification Code) in the dataset? What is the total cash tax paid by industry for companies in the dataset? For this question, include a chart sorted from highest to lowest cash tax paid, as well as a bar chart for all industries sorted by 2-digit SIC).

3. What is the average ending UTB balance by year for companies in the dataset? Include both a chart and a line graph.

[use “Manufacturing” tab for the following inquiries]

4. For manufacturing firms, what is their average GAAP effective tax rate (ETR) and cash ETR from 2000 to 2022? These variables are not directly pulled from the dataset, so you will need to construct them using formulas. GAAP ETR = tetot / ptit. Cash ETR = citp / ptit. Are the average GAAP and Cash ETRs in line with your expectations? Why or why not?

5. Provide a histogram for GAAP and Cash ETRs for manufacturing firms over the sample period. Are these histograms in line with your expectations?

6. What is largest GAAP ETR and Cash ETR for manufacturing firms?

7. Remove any manufacturing firms that have GAAP or Cash ETRs that are less than zero or greater than 1. Copy this dataset into a new tab in your Excel workbook. (Hint: you likely want to use an “=if( )” statement to identify firms with ETRs outside the [0, 1] boundary.

8. Provide a histogram for GAAP and Cash ETRs for manufacturing firms that meet the requirements in #7.

[use “Retail” tab for the following inquiries]

9. What is the average GAAP ETR (getr) for retail firms in 2022, rounded to the nearest percent?

10. Which retail firms have low ETRs in 2022? Create a column with a “flag” for retail firms in 2022 if their GAAP ETR is below the average you identified in #10 (label the column “lowetr”). The flag should be equal to 1 if the GAAP ETR is below the average GAAP ETR and 0 otherwise.

11. Which firms have low ETRs in 2022 and also have high additions to uncertain tax benefits in the current year? These are firms we may want to examine further. Among firms with low ETRs from #10, sort based on Additions Due to UTBs in Current Year. Provide a chart with the top 10 from this list. Include compid, getr, and utbaddc in the chart.