Rows137K
Columns15
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:
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%
