Capstone Project 4 Instructions
Deliberto Tax Analysis
Capstone_4_Instructions.docx 1
Deliberto Data LLC., 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 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 DBdata.xlsx has been set up listing sample tax
return data and the actual amount of taxes owed based on the current tax rates. The worksheet also includes schedules for the proposed flat tax rates,
penalties, and state allowances.
To complete this study, our 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.
Capstone Project 4 Instructions
Deliberto Tax Analysis
Capstone_4_Instructions.docx 2
You are to complete the following:
1. Download the starter Excel data file named Capstone_4_Starter_Data_File and
save it as a new Excel workbook with the name Deliberto_Tax_Analysis_nn.xlsx
where nn are your first and last initials.
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 , 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; income of at least $80,000 but less than $180,000 pay 14% of
the income in taxes; and incomes of $180,000 pay a 22% tax rate. Write a
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 Alternate 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 , 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; income of at least $80,000 but less than $180,000 pay
16% of the income in taxes; and incomes of $180,000 pay a 26% tax rate. Write a
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
Alternate 1 and Alternate 2 flat tax calculations.
*Copy this formula to cells I13:I21 and to cells J12:K21 using proper absolute
referencing in the formula.
Capstone Project 4 Instructions
Deliberto Tax Analysis
Capstone_4_Instructions.docx 3
5. In cell L12, write a formula that determines the actual penalty owed based on
the penalty schedule. 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. 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 alternate tax scheme.
6. In row 23, calculate the total value for each category for all 10 tax
returns.
7. As part of the flat-tax rate, 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 #NA should be displayed. In cell O12, write a formula that determines
the state allowance for this taxpayer. Write the formula so that is can be copied
down the column, and then copy it to cells O13:O21.
8. Highlight in the standard green color the column of the tax scheme that is most
favorable to very high income taxpayers.
9. Format the worksheet to make it easy to read and understand.
10. Save the workbook as Deliberto_Tax_Analysis_nn.xlsx where nn are the initials
of our first and last name and submit to Blackboard per the instructions of your
instructor.
