The Freedom Group is a "think tank" in Washington, D.C., that provides research data to lobbyists and members of the federal government. As a tax analyst for the Freedom Group, your job is to report how tax policies affect federal, state, and local revenues. A lobbyist asks you to study how alternate flat tax rate proposals could affect
the total taxes owed and residual amount of taxes owed on April 15, based on a list of sample individual tax return information. A flat tax rate is a rate applied to the total income earned. For example, a flat tax rate of 10% on $20,000 in income is $2,000. The current graduated system applies different percentages to ranges of income for each taxpayer. For example, a graduated tax on a $20,000 income might be as follows: 0% of the first $5,000, 10% of the amounts between $5,001 and $10,000, and $15% of the amounts over $10,000.
A worksheet in an Excel workbook named Freedom.xlsx has been set up listing sample tax return data and the actual amount of taxes owed based on the current tax rates (column F). The worksheet also includes schedules for the proposed flat tax rates, penalties, and state allowances.
To complete this study, your task is to analyze the total taxes owed based on two new flat-rate tax alternatives. In addition, you will compare the residual amount owed and penalties applied to that residual amount for each alternative. The residual amount owed on April 15 is calculated as follows: actual total tax owed minus the sum of the actual withholding taxes paid and the estimated taxes paid.
Withholding taxes are those amounts withheld from an employee's paycheck each pay period and remitted to the IRS by the employer. Estimated taxes are direct tax payments made by taxpayers to the IRS each quarter. Each April 15, taxpayers calculate the amount they owe in taxes and then subtract out any payments (withholding and estimated) to determine the actual unpaid tax. Depending on the amount of this residual unpaid tax, penalties might be applied. Complete the following:
1. Open the workbook named Freedom.xlsx located in the Chapter 5 folder, and then save the file as Freedom Tax Analysis.xlsx.
2. In cell G12, write a formula that uses the Alternate 1 flat tax rate to determine the total dollar value of the tax for the income in cell B12. As detailed in the Flat Tax Rate table (cells A1:F4), this tax scheme calculates taxes by multiplying the total income by the corresponding rate. For example, incomes below $30,000 pay no tax; incomes of at least $30,000 but less than $50,000 pay 5% of the income in taxes; incomes of at least $50,000 but less than $80,000 pay 9% of the income in taxes; incomes of at least $80,000 but less than $180,000 pay 14% of the income in taxes; and incomes of $180,000 or more pay a 22% tax rate. Write the formula so that it can be copied down the column, and then copy it to cells G13:G21.
3. In cell H12, write a formula that uses the Alternative 2 flat tax rate to determine the total dollar value of the tax for the income in cell B12. As detailed in the Flat Tax Rate table (cells A1:F4), this tax scheme also calculates taxes by multiplying the total income by the corresponding rate. For example, incomes below $30,000 pay no tax; incomes of at least $30,000 but less than $50,000 pay 6% of the income in taxes; incomes of at least $50,000 but less than $80,000 pay 10% of the income in taxes; incomes of at least $80,000 but less than $180,000 pay 16% of the income in taxes; and incomes of $180,000 or more pay a 26% tax rate. Write the formula so that it can be copied down the column, and then copy it to cells H13:H21.
4. In cell I12, write a formula that calculates the amount of unpaid taxes the first taxpayer still owes on April 15. The unpaid taxes are based on the actual amount of taxes owed, the actual withholding taxes paid, and estimated taxes paid. Write the formula so that it can be copied down the column to calculate this amount for each taxpayer. Also, write the formula so that it can be copied across the row to determine the unpaid amount based on the taxes owed for the Alternative 1 and Alternative 2 flat tax calculations. (Hint: Assume the same withholding and estimated taxes paid.) Copy the formula to cells I13:I21 and to cells J12:K21.
5.In cell L12, write a formula that determines the actual penalty owed based on the penalty schedule (cells H1:I8). For example, unpaid tax balances of less than $100 owe no penalty, and unpaid tax balances of at least $100 but less than $1,000 are charged a penalty of 3% of the unpaid tax amount. The range H3:I8 is named Penalty. (Hint: Use an IF function to determine if the unpaid tax amount is negative, indicating that the IRS owes the taxpayer a refund and, therefore, no penalty applies.) Copy this formula both down the column to calculate the penalty for the corresponding ID# and across the row to determine the penalties based on each alternative tax scheme.
6. In row 23, calculate the total values for each category (F23:N23) for all 10 tax returns.
7. As part of the flat-rate tax, one possible scheme would include a state allowance to balance the high and low cost of living. The amount of the allowance is listed by state in cells A7:B9. If the list does not contain the appropriate state, the error message #N/A should be displayed. In cell O12, write a formula that determines the state allowance for this taxpayer. Write the formula so that it can be copied down the column, and then copy it to cells O13:O21.