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.
8. Highlight in pink the column of the tax scheme that is most favorable to very high- income taxpayers (Actual, Alternative 1, or Alternative 2). Format the worksheet to make it easy to read and understand.