This case is adapted from Taxes for the Masses by Bridget Stomberg and Lisa De Simone. Before
Question:
This case is adapted from Taxes for the Masses by Bridget Stomberg and Lisa De Simone. Before beginning the data analytics portion of this assignment, please listen to the May 1, 2022 episode of Taxes for the Masses titled "On Zero Tax Firms". The episode discusses how firms that report profit to their shareholders under U.S. GAAP can pay little or no federal income tax. It is directly relevant to Chapter 3 and Chapter 14 which we cover in class, and it will help with your write-up of the case. The link to the episode is as follows:
https://www.taxes-for-the-masses.com/
Case Instructions
Project Overview: The purpose of this project is to practice concepts and tax law that we are learning in class, and to gain work-like experience with new tools and resources. Many accounting firms currently desire more specific analytical skills from new applicants. Data analytics is a powerful skill that is rapidly gaining popularity in the business world. Accounting professionals have specifically mentioned Tableau as a powerful and useful platform for performing data analytics as a way to add value to current and prospective clients. Excel is another tool professionals have identified as being a generally useful platform. This project will provide exposure to Tableau and Excel and practice with cleaning, presenting, and analyzing data.
The manager on this engagement assigned you, the staff accountant, the task of analyzing data for a broad set of public corporations to gain an understanding of the Cash effective tax rate and its behavior across time, firm size, and industries. The manager needs this information to prepare for a presentation to prospective clients. Within each step, there are specific questions that you should think about as you work with the data. Your manager will need succinct answers to these questions to share with prospective clients who are not familiar with financial or tax accounting.
Complete the following steps in order:
- Save the Excel file, "ETR data".
- Open Tableau. On the left-hand side menu, select to connect to a Microsoft Excel file. Find where "ETR data" is stored and click on it.
After clicking the file, a new menu should pop up that shows the various worksheets in the file. Click "Financial Data" to load that as the main database and drag to the top box/area. The data should populate in Tableau.
Drag the "SIC" worksheet into the top box to allow a connection between the two sheets. In "Financial Data", the field SIC 2 is the company's two-digit Standard Industrial Classification code. In "SIC", these numeric codes are linked to descriptions.
- Right click on "Sheet 1" at the bottom of the screen and rename it "Stats Full Sample." In this sheet, create a new variable - Cash ETR. To do so:
- Select "Analysis" from the top bar and select "Create Calculated Field".
- Name the variable "Cash ETR" and in the box type [Income Taxes Paid]/[Pretax Income]. This will create a ratio of taxes paid to pretax income. This variable is the focus of this assignment.
- How is the Cash ETR similar to / different from the GAAP ETR [(current + deferred tax expense) / Pretax Income] that we calculated in Chapter 14?
Objective: Practice getting familiar with Excel and Tableau. Identifying and understanding what information is necessary to calculate cash effective tax rates.
- Compute descriptive statistics for Cash ETR in the full sample: average, minimum, 25th percentile, median, 75th and maximum. Be sure the statistics are in this order. To do this:
- Drag "Cash ETR" from the list of variables in the left-hand side menu to "rows" on top. The default is to SUM Cash ETR.
- Select the dropdown arrow and change the measure to "Average".
- Repeat these steps for all requested statistics.
- Using the "Show Me" menu on the top right, select "Text Table". This is TABLE STEP 4.
- Within Tableau, drag the width of each column to see the full label for each descriptive statistic.
- What information do these statistics provide? Where can you get additional information needed to explain any anomalies in the data? How do these statistics help when analyzing the Cash ETR over time (step 5), by size (step 6), and across industry (step 7)?
- Plot average and median Cash ETR over time.
- Predict whether there will be a trend. Based on your understanding of the tax law and tax rates across time, do you expect mean and median Cash ETRs to increase, decrease or remain stable over time? Why?
- Add a new sheet at the bottom left using the + sheet button. Rename this sheet "Average by Year" and do the following:
- Plot a graph of average and median Cash ETR over time. Drag "Data Year-Fiscal" to "Columns". Be sure to change the measure of Cash ETR to "Average". Repeat to add medians. Select "dual lines" from "Show Me" to be sure the two lines are on the same graph. Add trend lines. This is GRAPH STEP 5.
- Describe and explain the pattern in Cash ETRs over time. Was your prediction correct?
- click on 2015 and "Explain the data". Which observation is contributing to the low average Cash ETR in 2015? How does the trend in average Cash ETR change when this observation is excluded? Why does this observation have such an extreme value of Cash ETR?
- Examine how size affects Cash ETR.
- Predict whether larger firms have higher or lower Cash ETRs. Explain your prediction briefly.
- Add a new sheet called "Size Effects". Create a new variable called "Size" calculated as the natural log of Assets. Drag "Cash ETR" to Rows and "Size" to Columns. Under "Analysis", uncheck aggregate measures. You now have a scatter plot of Cash ETR and Size. Describe how this graph looks.
- Click on Cash ETR and filter the variable so only values between 0 and 100% appear. Filter Size to positive values. In the "Analytics" tab, add a trend line by dragging it into the scatter plot. Right click on the trend line to "Describe Trend Model". This is GRAPH STEP 6. Answer the following:
- In general, do firms' Cash ETRs increase or decrease as they become larger?
- Is Size a significant predictor of Cash ETR? What statistic on the output provides you with this information?
- Does the model explain a lot of the variation in Cash ETR? How can you tell? What statistic on the output provides you with this information?
- Examine Cash ETR by industry.
- Predict which industry will have the lowest average Cash ETR. Explain your prediction. Think of one company in this industry that you predict will have a low ETR.
- Add a new sheet called "Industry". Drag "SIC Description" to Columns and Avg(Cash ETR) to Rows. Filter Cash ETR to be between 0 and 100%. Drag "Data Year - Fiscal" to Filters shelf and filter to 2018-2022.
- Select a horizontal bar chart and make sure the industries are across the bottom (You may need to switch rows and columns on the top toolbar to do this). You now have a graph of average ETR by industry after the TCJA for all observations with a Cash ETR between 0% and 100%. Select markers to show the average Cash ETR in each industry and drag "SIC Description" to "Color" to make the chart multicolored.
- Click on "Analytics" and add the average line. This is GRAPH STEP 7.
- Click on Services to Explain data.
- Which observation has an extreme value?
- After you exclude this observation from the Services industry, is your prediction correct? Identify the name of the company you selected in "a" above. Does this company have an average Cash ETR below the full-sample average? Explain.
Objective: Practice analysing data and the related statistics to understand what information the data is conveying, as well as the importance of each statistic.
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill