Create an excel formula that calculates the federal withholding tax

Instructions
Taxes
Project Description:
In this problem, you will calculate the federal income tax for married couples filing jointly in 2018 with different income levels.
Use a cell reference or a single formula where appropriate in order to receive full credit. Do not copy and paste values or type values, as you will not receive full credit for your answers.
Steps to Perform:
Step Instructions Points Possible
1 Start Excel. Download and open the workbook named: 0
Chapter_1-6_Taxes_Start
2 In cell H6, find the base tax for Arah and Beto according to their income in cell G6. 2
3 In cell I6, calculate the amount their income is over the appropriate cutoff value. 2
4 In cell J6, find the correct rate according to their income and base rate. 2
5 In cell K6, calculate the federal tax for Arah and Beto. 2
Note: Use only cell references to the values calculated in parts a-c.
6 In cell H18, find the base tax according to the income in cell G18. Fill cell H18 down the column to cell H105. 4
Note: The base rate doesn't change with the income levels in cells G18:G105. Be sure to use the correct type of cell references.
7 In cell I18, calculate the amount the income in cell G18 is over the appropriate cutoff value. Fill cell I18 down the column to cell I105. 4
Note: Be sure to use absolute, mixed, and relative cell references.
8 In cell J18, find the correct rate according to the income in cell G18. Fill cell J18 down the column to cell J105. 4
Note: The rate doesn't change with the income levels in cells J18:J105. Be sure to use the correct type of cell references.
9 In cell K18, calculate the federal tax for income in cell G18. Fill cell K18 down the column to cell K105. 4
Note: Use only cell references to the values calculated in part e.
10 In cells M36:Z55, insert a Scatter Chart to show the relationship between the income and federal rate. Select ranges G18:G105 and K18:K105. On the Insert tab, click Recommended Charts, and then click Scatter. Apply Style 7 on the Design tab. Add a chart title and choose the Above Chart option. Replace Chart Title with Federal Income Taxes for Filing Jointly Married. Add axis titles. Replace Axis Title for the horizontal axis with 2018 Income and Axis Title for the vertical axis with Federal Income Taxes. Double click on the horizontal axis of the chart. In Format Axis window, set 78000 as the minimum bound. 4
11 Save your file and submit for grading. 0
Total Points 28
Questions
Taxes
Use a cell reference or a single formula where appropriate in order to receive full credit. Do not copy and paste values or type values, as you will not receive full credit for your answers.
a.) b.) c.) d.)
Income Base Tax Amt Over Rate TAX
$123,000
If Taxpayer's Income Is... Then Estimated Taxes Are...
Between But Not Over Base Tax + Rate Of the Amount Over
$0 $19,050 $0 10% $0
$19,050 $77,400 $1,905 12% $19,050
$77,400 $165,000 $8,907 22% $77,400
$165,000 $315,000 $28,179 24% $165,000
$315,000 $400,000 $64,179 32% $315,000
$400,000 $600,000 $91,379 35% $400,000 e.)
$600,000 - - - - - $161,379 37% $600,000
Income Base Tax Amt Over Rate Tax
$78,000
$79,000
$80,000
$81,000
$82,000
$83,000
$84,000
$85,000
$86,000
$87,000
$88,000
$89,000
$90,000
$91,000
$92,000
$93,000
$94,000 f.)
$95,000
$96,000
$97,000
$98,000
$99,000
$100,000
$101,000
$102,000
$103,000
$104,000
$105,000
$106,000
$107,000
$108,000
$109,000
$110,000
$111,000
$112,000
$113,000
$114,000
$115,000
$116,000
$117,000
$118,000
$119,000
$120,000
$121,000
$122,000
$123,000
$124,000
$125,000
$126,000
$127,000
$128,000
$129,000
$130,000
$131,000
$132,000
$133,000
$134,000
$135,000
$136,000
$137,000
$138,000
$139,000
$140,000
$141,000
$142,000
$143,000
$144,000
$145,000
$146,000
$147,000
$148,000
$149,000
$150,000
$151,000
$152,000
$153,000
$154,000
$155,000
$156,000
$157,000
$158,000
$159,000
$160,000
$161,000
$162,000
$163,000
$164,000
$165,000
a.) Assume Arah and Beto made $123,000 in 2018, and the married couple will file a joint tax return. Determine their base tax in cell H6 by finding the appropriate row in the table using the Between and But Not Over columns for their income. Enter a formula which links to the cell in the table with the correct base tax. So, if the correct base tax is in cell A2, you would enter =A2.

b.) Determine in cell I6 the amount their income is over the appropriate cutoff value given in the Of the Amount Over column. Use the appropriate cell reference for the couple’s income in your calculation.

c.) Determine the correct rate in the table associated with their income and link to it in cell J6.

d.) Compute their federal tax in cell K6 using the values from parts a-c.

e.) Complete the table which computes the federal income tax for married couples filing jointly in 2018 for every income from $78,000 to $165,000 (in $1,000 increments). You must recreate the formulas used in parts a-d, but make sure the formulas can be filled down, so use proper absolute/mixed/relative cell references.

f.) Create a well-labeled scatter chart of the income taxes from this table in part e as a function of income. Axis labels and the title should be chosen from the list: a. Federal Income Taxes b. 2018 Income c. Federal Income Taxes for Filing Jointly Married

The table below for computing 2018 federal income tax (using new 2017 tax law) in the married filing jointly bracket can be found at: https://fa.morganstanley.com/balog/mediahandler/media/117520/2018_Tax_Tables.pdf