Each year, the IRS audits a large number of individual income tax returns in the hopes...

Transcribed Image Text:

Each year, the IRS audits a large number of individual income tax returns in the hopes of collecting additional revenue for the federal government and encouraging all income tax filers to honestly complete their income tax returns. To decide which returns to audit, the IRS uses a complex (and highly secret!) algorithm to determine which returns are most likely to have been filed incorrectly/fraudulently. The algorithm uses information from the return (income of various types, deductions of various types, etc.) to calculate a DIF (Discriminant Inventory Function) score to each return. Those returns with the highest DIF scores are deemed most likely to generate additional tax liabilities if they are audited. To gain some insight into the effectiveness of their audit process, the IRS has audited a randomly generated sample of income tax returns. The sample included 200 returns from each of three different audit classes (see below for definition of audit class), for a total of 600 returns. Information pertaining to this study can be found in the database "IRS Data.accdb". This database consists for three tables as described below: Tax Return (This table has information pertaining to each return in the sample) Soc Sec Number - The social security number of the person filing the return Income - The gross income of the person filing the return Audit Class - The audit class of the income tax return. Class 1 are the simplest returns (Form 1040A or 1040EX), Class 2 are medium complexity (Form 1040 without schedule C or F), and Class 3 are high complexity (Form 1040 with schedule C or F) DIF - The DIF score calculated for this return • • Auditor (This table has information on the IRS employees conducting the audits) Auditor ID - A unique employee number for each auditor Auditor Name - The name of the auditor • • Hourly Cost - The hourly cost (including benefits) of the auditor Audit (This table has information regarding the outcome of each audit that was performed) • Social Security Number - The social security number of the person filing the return Auditor ID - The ID number of the auditor (IRS employee) performing the audit ATR - The additional tax recommended as a result of the audit (i.e., the difference between the tax liability determined by the audit and the tax reported on the return, which could be negative if the person paid more taxes than they owed). Hours - The number of hours spent by the auditor performing the audit • Please use this database to perform the following activities 1. (10 points) What is the Primary Key for each of the three tables? What fields do you need to link as Relationships to make the tables work together? Be sure to create these links in Access before doing any further work. 2. (15 points) Create a list (sorted from highest income to lowest income) showing the Social Security Number, Income, Audit Class and ATR for all the tax returns filed by people who earned more than $1,000,000. How many records are there? Include an image to show the first several rows of this list. 3. (15 points) Create a list (sorted from highest audit cost to lowest audit cost) showing the Social Security Number, ATR, Hours, Hourly Cost and Audit Cost of all the tax returns that cost over $2,000 to audit. How many records are there? Include an image to show the first several rows of this list. 4. (20 points) Calculate the total gross proceeds to the IRS (total of all the additional tax recommended) if they were to audit all returns from this sample with a DIF score greater than 25,000. Include an image of your results that shows the total proceeds and the number of returns that would be audited. 5. (20 points) Calculate the average cost for performing an audit by Audit Class (i.e., you should have one average cost for Class 1, one for Class 2, and one for Class 3). Include an image of your results. 6. (20 points) Within Audit Class 3, if you only audit the 100 returns with the highest DIF score (i.e., above the median) what would be the total net proceeds (additional tax recommended less cost of performing the audit) to the IRS? What is the average cost of performing the audit? (Note: you may have to run multiple queries, where the results of one query is the input for another query, to answer this question.) Include an image of your results that shows both the net proceeds and the average cost of performing the audit. Each year, the IRS audits a large number of individual income tax returns in the hopes of collecting additional revenue for the federal government and encouraging all income tax filers to honestly complete their income tax returns. To decide which returns to audit, the IRS uses a complex (and highly secret!) algorithm to determine which returns are most likely to have been filed incorrectly/fraudulently. The algorithm uses information from the return (income of various types, deductions of various types, etc.) to calculate a DIF (Discriminant Inventory Function) score to each return. Those returns with the highest DIF scores are deemed most likely to generate additional tax liabilities if they are audited. To gain some insight into the effectiveness of their audit process, the IRS has audited a randomly generated sample of income tax returns. The sample included 200 returns from each of three different audit classes (see below for definition of audit class), for a total of 600 returns. Information pertaining to this study can be found in the database "IRS Data.accdb". This database consists for three tables as described below: Tax Return (This table has information pertaining to each return in the sample) Soc Sec Number - The social security number of the person filing the return Income - The gross income of the person filing the return Audit Class - The audit class of the income tax return. Class 1 are the simplest returns (Form 1040A or 1040EX), Class 2 are medium complexity (Form 1040 without schedule C or F), and Class 3 are high complexity (Form 1040 with schedule C or F) DIF - The DIF score calculated for this return • • Auditor (This table has information on the IRS employees conducting the audits) Auditor ID - A unique employee number for each auditor Auditor Name - The name of the auditor • • Hourly Cost - The hourly cost (including benefits) of the auditor Audit (This table has information regarding the outcome of each audit that was performed) • Social Security Number - The social security number of the person filing the return Auditor ID - The ID number of the auditor (IRS employee) performing the audit ATR - The additional tax recommended as a result of the audit (i.e., the difference between the tax liability determined by the audit and the tax reported on the return, which could be negative if the person paid more taxes than they owed). Hours - The number of hours spent by the auditor performing the audit • Please use this database to perform the following activities 1. (10 points) What is the Primary Key for each of the three tables? What fields do you need to link as Relationships to make the tables work together? Be sure to create these links in Access before doing any further work. 2. (15 points) Create a list (sorted from highest income to lowest income) showing the Social Security Number, Income, Audit Class and ATR for all the tax returns filed by people who earned more than $1,000,000. How many records are there? Include an image to show the first several rows of this list. 3. (15 points) Create a list (sorted from highest audit cost to lowest audit cost) showing the Social Security Number, ATR, Hours, Hourly Cost and Audit Cost of all the tax returns that cost over $2,000 to audit. How many records are there? Include an image to show the first several rows of this list. 4. (20 points) Calculate the total gross proceeds to the IRS (total of all the additional tax recommended) if they were to audit all returns from this sample with a DIF score greater than 25,000. Include an image of your results that shows the total proceeds and the number of returns that would be audited. 5. (20 points) Calculate the average cost for performing an audit by Audit Class (i.e., you should have one average cost for Class 1, one for Class 2, and one for Class 3). Include an image of your results. 6. (20 points) Within Audit Class 3, if you only audit the 100 returns with the highest DIF score (i.e., above the median) what would be the total net proceeds (additional tax recommended less cost of performing the audit) to the IRS? What is the average cost of performing the audit? (Note: you may have to run multiple queries, where the results of one query is the input for another query, to answer this question.) Include an image of your results that shows both the net proceeds and the average cost of performing the audit.

Answer rating: 100% (QA)

lets tackle each part step by step 1 Primary Keys and Relationships Primary Key for Tax Return table Soc Sec Number Primary Key for Auditor table Audi... View the full answer