This is a multiple part question, i didnt want to separate and submit it multiple times, if you'd like you can count it as multiple questions and take credits from my account.

The Arlington 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 Arlington 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 data file is provided that includes schedules for the proposed 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 alternate. The residual Unpaid Tax owed on April 15 is calculated as follows: tax owed (withholding taxes paid + estimated taxes paid).

Depending on the amount owed, penalties might be applied to the residual amount owed. Complete the following: (Show all dollar values & percentages to 0 decimal places.)

1. Open the data file taxes.txt and put the data from the file in a worksheet named Taxes. (Please remember to include a Documentation sheet that includes a hyperlink to your Taxes worksheet.) Note: Hyperlinks to text files often open directly within web browsers rather than prompting you to download the file. You may copy the data from the web browser, paste it into Excel and then use the Text to Columns feature (Alt, a, e) to parse the data into separate columns... (You will need to pick the correct delimiter to correctly parse the data into separate columns.)

taxes.txt:

Evaluating Tax Rates for the Arlington Group,,,,,,,,,,,,,,  
2. Format your worksheet like the image shown. (Although you should not include the yellow callouts shown in the screenshot.) Your spreadsheet should use the same rows/columns and cell placement as shown in the image. Do NOT insert columns or rows, or otherwise rearrange the design of the spreadsheet. Since the spreadsheet should be able to accommodate 1,000 tax ID #'s as easily as 10, make sure the column headings and ID #'s can still be seen, even when scrolling. Format the dollar values such that they align on the left of the column. The dollar signs should show on only the top value in each list of consecutive dollar values. Also show the dollar sign on dollar values that are Totals. Decimal places should line up for all values. Show 0 decimal places for all dollar values.
 
3. All four digits of Tax ID# should be shown, even if the Tax ID# begins with a 0.
 
4. Make it so the ID# corresponding with the highest Actual Tax Owed is automatically formatted with a bold green font color, and the ID# corresponding with the lowest Actual Tax Owed is automatically formatted with a bold red font. This formatting should be flexible, so that if any of the Incomes and/or Actual Taxes Owed change, the formatting will automatically adjust to indicate the correct highest and lowest values.
 
5. Use the information below to construct a Flat Tax Rate table, which will be used to calculate Flat Taxes owed for various incomes. Show both the min and max values in the table you create. For Flat Tax Alternative 1, the following tax rates are applied: 22% tax on incomes of $122,000 or more 15% tax on incomes of at least $72,000 but less than $122,000; 10% tax on incomes of at least $32,000 but less than $72,000; 3% tax on incomes of at least $15,000 but less than $32,000; no tax on incomes below $15,000. For Flat Tax Alternative 2, the following tax rates are applied: 20% tax on incomes of $122,000 or more 11 tax on incomes of at least $72,000 but less than $122,000; 7% tax on incomes of at least $32,000 but less than $72,000; 5% tax on incomes of at least $15,000 but less than $32,000; no tax on incomes below $15,000.
 
6. Ensure that the Flat Tax Rate table is clear so that anyone viewing the spreadsheet can easily determine the flat tax % rates that are used for each range of income. (Also, a user should be able to edit the values in the table at any time and the table should remain clear and accurate.) Note: Income is rounded to the nearest dollar, so there is no need to show cents.
 
7. Any dollar values within the data that are 0 should show as a dash. However, in the assumptions (in your reference tables), it makes more sense for 0s to show rather than dashes.
 
8. In the Flat Tax Owed Alternate 1 column, write a formula that uses the Alternate 1 flat tax rate to determine the total dollar value of the tax for the respective Taxable Income. Using the Flat Tax Rate table, this tax scheme calculates taxes by multiplying the total income by the corresponding rate. Use one of the lookup functions (HLOOKUP or VLOOKUP) as part of your formula for this part. Your formula should be flexible, so that additional rows can be added for additional income brackets. (Although only 2 Flat Tax Alternates currently exist, there could be up to 10 Flat Tax Alternatives.)
 
9. In the Flat Tax Owed Alternate 2 column, write a formula that uses the Alternate 2 flat tax rate to determine the total dollar value of the tax for the respective Taxable Income. Using the Flat Tax Rate table, this tax scheme calculates taxes by multiplying the total income by the corresponding rate. Use one of the lookup functions (HLOOKUP or VLOOKUP) as part of your formula for this part. Your formula should be flexible, so that additional rows can be added for additional income brackets.
 
10. In the first cell under the Actual Unpaid Taxes column heading, write a formula that calculates the amount of taxes the first taxpayer still owes on April 15. The residual Unpaid Tax owed on April 15 is calculated as follows: tax owed  (withholding taxes paid + estimated taxes paid). Write the formula so that it can be copied down the column to calculate this amount for each taxpayer (ID #). 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 tax calculations. (Assume the same withholding and estimated taxes paid.) You should be able to enter one formula, then copy it across to the other two columns of Unpaid Taxes, and copy the same formula(s) down to the other rows within all three Unpaid Tax columns. Some taxpayers will show negative numbers for their Unpaid Tax. If a "Tax Owed" value is negative, then you paid more than you really owe, and therefore the government owes you a refund. Therefore leave the negative numbers to show how much refund is owed.
 
11. Use the info below to construct a table that will be used as a Penalty Schedule. Use the range shown in the image provided, where the @ symbols are the headings, and the cells with #'s are for the rest of the data. This table will not show both the min and max values. Instead, it uses only the breakpoint values required for the lookup function to work properly. Penalty Schedule Info: a penalty of 25% is charged on an Unpaid Tax balance of $50,000 or more; a penalty of 20% is charged on an Unpaid Tax balance of at least $10,000 but less than $50,000; a penalty of 15% is charged on an Unpaid Tax balance of at least $5,000 but less than $10,000; a penalty of 10% is charged on an Unpaid Tax balance of at least $1,000 but less than $5,000; a penalty of 5% is charged on an Unpaid Tax balance of at least $100 but less than $1,000; and unpaid tax balances of less than $100 owe no penalty. 
 
12. Ensure that the Penalty Schedule table is clear so that anyone viewing the spreadsheet can easily determine the penalty % charged depending on the amount of unpaid tax owed. (Also, a user should be able to edit the values in the table at any time and the table should remain clear and accurate.) Note: Do not insert an additional column in order to display both the lower & upper boundaries of the ranges. The @ symbols at the top of each column of data should be replaced with text labels describing the numbers below. The # symbols should be replaced with numbers.
 
13. In the Actual Penalty Owed column, use the Penalty Schedule to determines the actual penalty owed. Please use one of the lookup functions (HLOOKUP or VLOOKUP) as part of your formula for this part. 
 
14. 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 per state is listed as follows: a $600 allowance for OK, a $900 allowance for GA, a $400 for TN, a $400 allowance for SC, a $250 allowance for NC, and an $800 allowance for FL. Construct a table of "State Allowances" to be used for finding the allowance for each taxpayer's ID#. (Use the range shown for the State Allowances in the image provided, where the @ symbols should be replaced with headings describing the data in the column below. The cells with ! should contain either the state abbreviations or the state allowance dollar values.) Note: Your data currently does not include any employees from NC, but make sure your spreadsheet formulas can accommodate employees from this state as well.
 
15. In the State Allowances column, use formulas to determine the state allowance for each taxpayer. 16. If the "State of Residence" data contains a state not listed in the State Allowances table (e.g. NY), the State Allowance for that ID# should show the word None (not #N/A). 17. Make it so that the only States that can be entered in the "State of Residence" column are states shown in the State Allowances table (in the assumptions section at the top). When a user wants to enter a state in the State of Residence column, he/she should be able to use "pull-down arrows" to select from the list of states in the State Allowance table. Your spreadsheet should prevent future entry of invalid data, but it will not affect data that has already been entered. (Your data could already contain some invalid data that will remain within your data.)
 
18. Skip a row beneath the last ID# and include Totals for numeric data above, as shown in the screenshot. (Ensure that your formulas are flexible and can always easily accommodate additional rows of data.) The Total at the bottom of the State of Residence column should count the number of States. However, do not worry about counting the unique number of statesjust count the number of rows.
  1/2 A B C D E M KL NO Evaluating Tax Rates for the Arlington Group One of these columns should contain text & one should contain numbers @'s indicates text Flat Tax Rate Penalty Schedule State Allowances Numbers or formulas in these columns #'s indicate numbers ?'s indicate formulas Taxable State of Withholding Income Residence Taxes Paid Estimated Taxes Paid Actual Tax Owed Flat Tax Owed Alternate 1 Alternate 2 Unpaid Tax Alternate 1 Penalty Owed Alternate 1 Actual Alternate 2 Actual Alternate 2 State Allowance ? $ ? $ @@@@@@@@@ # Totals $ ? ? $ ? $ ? $ ? $ ? $ ? $ ? $ ? $ ? $ ? $ ? $ ? $ ?  1/2 A B C D E M KL NO Evaluating Tax Rates for the Arlington Group One of these columns should contain text & one should contain numbers @'s indicates text Flat Tax Rate Penalty Schedule State Allowances Numbers or formulas in these columns #'s indicate numbers ?'s indicate formulas Taxable State of Withholding Income Residence Taxes Paid Estimated Taxes Paid Actual Tax Owed Flat Tax Owed Alternate 1 Alternate 2 Unpaid Tax Alternate 1 Penalty Owed Alternate 1 Actual Alternate 2 Actual Alternate 2 State Allowance ? $ ? $ @@@@@@@@@ # Totals $ ? ? $ ? $ ? $ ? $ ? $ ? $ ? $ ? $ ? $ ? $ ? $ ? $