6.5.D Chapter Practice 4: Taxpayer Returns Data Analysis

This chapter is a collaboration with Professor Mary Sykes from the Department of Accountancy and Taxation at the C.T. Bauer College of Business. Prof. Sykes holds an MBA and is a licensed CPA in the state of Texas. Her teaching areas include financial, managerial, and international accounting; accounting ethics, and oil & gas accounting.
One could argue that Accounting is the language of business. It is a field is relevant to for-profit, nonprofit, and government entities. The Texas Comptroller of Public Accounts is a significant entity in the state of Texas. They keep the books for the Texas State government, which includes the collection of taxes and fees owed to the state. The Texas Comptroller of Public Accounts safeguards and ensures that the taxes collected are accurate and are handled properly.
Dealing with a large number of data points can require and also result in a large qualitative analysis effort. The most common accounting data manipulation tool to accomplish this is to complete analyses is Microsoft Excel. Excel is a flexible and integral part of organizations, it is a popular and very user-friendly application for accountants, too. A comptroller may use it to import and process the data or create charts and PivotTables using it.
In this chapter practice, we will process open data from data.gov regarding Taxpayer Returns.
The Comptroller of Public Accounts is charged by statute, Tex. Gov’t Code § 403.0142, with reporting and posting the amounts of revenue remitted from each Texas municipality and county for taxes whose location information is available from tax returns. The revenue is presented by county only because specific cities could not be definitively determined from the report data. Returns submitted directly by local governments are open records and include their names and addresses. Due to confidentiality restrictions, amounts reported by businesses cannot be provided when less than four businesses report for a specific county. This data is posted quarterly, six months after the end of the quarterly data period to allow for collection actions when needed.
Our data source:
https://data.texas.gov/Government-and-Taxes/Taxpayer-Returns/
Before we start manipulating the data, let’s look at what the Comptroller does.

Now, let’s look at what the Data Analysis and Transparency Division does:

The following variables describe our data set:

What’s in this Dataset?

Columns in this Dataset

Column Name Description Type
Tax ID
A number that uniquely identifies a tax that the Comptroller administers.
Plain Text
Sub Tax
A number that Identifies the class type of a Tax ID. This number helps to identify what class of taxing authority that is empowered to assess taxes, fees, or funds that are collected by the Comptroller.
Plain Text
Tax Description
A short description of the tax type.
Plain Text
Taxpayer Number
Unique 11-digit ID assigned by the Texas Comptroller.
Plain Text
Year
The year of the return.
Number
Month
The month of the return (1-12). If blank the return is not processed monthly.
Number
Quarter
The quarter of the return (1-4). If blank the return is not processed quarterly.
Number
Name
Name of the taxpaying entity.
Plain Text
Address
Address of the taxpaying entity.
Plain Text
City
City of the taxpaying entity.
Plain Text
Zip
Zip code of the taxpaying entity.
Plain Text
State
State of the taxpaying entity.
Plain Text
County
County of the taxpaying entity.
Plain Text
Total Due
The total amount due for a tax return.
Number
Report Period Type
The frequency the return is filled: either monthly, quarterly, or yearly.
Plain Text

 

 

Fa22_Texas_Top5Cities_Taxpayer

 

 

1. Insert a PivotTable and add Tax Descriptions under Rows. How many types of Tax Descriptions (Boat, Child Safety, etc.) are there?
a) 16
b) 276
c) 355
d) 3514

 

2. Insert a PivotTable and add Tax Descriptions under Rows. Add Tax Descriptions under Values (Count) as well. How many Counts of Tax Descriptions are there for CITY CRIMINAL COSTS & FEES?
a) 16
b) 276
c) 355
d) 640

 

3. Insert a PivotTable and add Tax Descriptions under Rows. Add Tax Descriptions under Values (Count) as well. How many Counts of Tax Descriptions are there for PHOTOGRAPHIC TRAFFIC ENFORCEMENT SYSTEMS?
a) 16
b) 35
c) 127
d) 985

 

4. Insert a PivotTable and add Tax Description under Rows. Add Total Due (SUM) under Values. How much is due for CITY CRIMINAL COSTS & FEES?
a) $73,362,526.00
b) $94,376,232.00
c) $315,326,346.00
d) $325,456,625.00

 

5. Insert a PivotTable and add Tax Description under Rows. Add Total Due (SUM) under Values, change the Value Field Settings to show value as “% of Grand Total”. Filter for the 2016 and Quarter 3. What percentage is due for DRUG COURT PROGRAM ACCOUNT?
a) 0.68%
b) 27.78%
c) 28.06%
d) 43.04%

 

6. Insert a PivotTable and add Tax Description under Rows. Add Total Due (SUM) under Values, change the Value Field Settings to show value as “% of Grand Total”. Filter for the 2016 and Quarter 3. What percentage is due for CITY CRIMINAL COSTS & FEES?
a) 0.68%
b) 27.78%
c) 28.06%
d) 43.04%

 

License

Icon for the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License

Business Computer Information Systems Copyright © 2020 by Emese Felvegi; Barbara Lave; Diane Shingledecker; Julie Romey; Noreen Brown; Mary Schatz; OpenStax; Saylor Academy; University of Minnesota Libraries; and Robert McCarn is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License, except where otherwise noted.