1. 8. As part of the flat rate tax, one possible scheme would provide a state allowance based on the cost of living. The amount of the allowance is provided in cells K3:L5. a. Instead of using a lookup function, in N12 use a form of the IF to display YES if the individuals State of Residence is on the list from K3:K5 otherwise display NO. [Do not hard-code state codes, use cell references]. Copy down through row 21.
2. b. In cell O12, use a function (not an IF function) to determine 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. You will note that the State Allowance for NY is #N/A since it is not in the list. To correct this problem, rewrite the formula in O12 using the IFERROR() function place a zero in cells with an error message and copy through row 21. Format column O as currency. (Check total = $5,500)
4. 9. Some have suggested an extra tax on those with large incomes. Using the formatted area labeled Tax On Rich (O1:P5) create a lookup table where Base Incomes under $250,000 have 0% Extra Tax, Base Incomes from $250,000 but less than 350,000 have 4% Extra Tax and those with a Base Income $350,000 and greater have an 8% Extra Tax. Refer to notes from todays lecture, or the Day21_ApproxLookup.pdf in your download folder that explains how to build a lookup table. In cell P12, write a formula that uses your table and computes the incremental Tax on the rich. Copy this formula through row 21. (Check total = $42,197.80)
5. 10. In Q11 add another heading Minimum Option and format like the other headings. a. In Q12 create a formula to determine the minimum amount of E12, F12 and G12, and if E12 is the minimum to display Actual, if the F12 is the minimum to display Alt1 and if G12 is the minimum to display Alt2. [Hint: you will use the IFS for this]
7. 11. Based on the results of the above step indicate the IDs and the Taxable Incomes for which the Actual Tax is the most favorable approach. Create a textbox to enter your response. [Only two IDs meet the criteria]

A B C D E F G H J L M N 0 Q Tax On Rich Taxable income Extra Tax $ 0 $ $ Flat Tax Rate 15,000 $ 32,000 $ 3% 10% 5% 7% 0% 0% 72,000 15% 11% 122,000 22% 20% 1 2 Taxable income 3 Alternate 1 4. Alternate 2 5 6 7 8 9 10 State Allowance State Allowance OK $ 600 GA $ 900 TN $ 400 Penalty Amount Owed Penalty % $ 0 0 100 5% 1,000 10% 5,000 15% 10,000 20% 50,000 25% Tax On Rich Minimum Option ID # 1 2 3 Penalty Actual $832.05 $812.85 11 12 13 14 15 16 17 18 19 20 21 22 Taxable Income $ 81,865.00 $ 85,263.00 $ 65,794.00 $ 369,209.00 $ 74,468.00 $ 23,500.00 $ 316,527.00 $ 52,935.00 $ 99,393.00 $ 17,437.00 4 5 6 State of Residence OK GA TN NY OK GA TN TN TN Actual Tax Actual Paid Tax Due $ 8,875.00 $ 14,422.00 $ 9,115.00 $ 14,534.00 $ 6,384.00 $ 6,259.00 $ 93,225.00 $ 103,629.00 $ 9,402.00 $ 9,594.00 $ 1,254.00 $ 700.00 $ 100,493.00 $ 90,534.00 $ 6,161.00 $ 6,287.00 $ 20,844.00 $ 17,370.00 $ 227.00 $ 244.00 Flat Tax Alternate 1 $12,279.75 $12,789.45 $6,579.40 $81,225.98 $11,170.20 $705.00 $69,635.94 $5,293.50 $14,908.95 $523.11 Flat Tax Alternate 2 $9,005.15 $9,378.93 $4,605.58 $73,841.80 $8,191.48 $1,175.00 $63,305.40 $3,705.45 $10,933.23 $871.85 Tax Owed/ Refund Due Actual $5,547 $5,419 $125 $10,404 $192 -$554 $9,959 $126 -$3,474 $17 Tax Owed/ Refund Due Alternate 1 $3,405 $3,674 $195 -$11,999 $1,768 -$549 -$30,857 -$868 $5,935 $296 Tax Owed/ Refund Due Alternate 2 $130 $264 -$1,778 -$19,383 $1,211 -$79 -$37,188 -$2,456 -$9,911 $645 Penalty Alternate 1 $340.48 $367.45 $9.77 0 $176.82 0 Penalty State Alternate 2 Allowance $6.51 Yes $13.20 Yes 0 Yes O No 0 Yes 0 Yes 0 Yes o Yes 0 Yes $32.24 Yes $2,080.80 $9.60 0 0 $6.30 0 $0.00 State Allowance Amount $900.00 $900.00 $400.00 $900.00 $900.00 $900.00 $400.00 $400.00 $400.00 $900.00 7 0 0 9 10 0 $14.81 GA 23 Check your work totals: $ 215,111.28 $ 185,013.87 $ 7,593.00 $ (40,868.72) $ (70,966.13) $ 3,741.60 $ 909.32||$ 51.95 7,000.00 $ 24 25 26 A B C D E F G H J L M N 0 Q Tax On Rich Taxable income Extra Tax $ 0 $ $ Flat Tax Rate 15,000 $ 32,000 $ 3% 10% 5% 7% 0% 0% 72,000 15% 11% 122,000 22% 20% 1 2 Taxable income 3 Alternate 1 4. Alternate 2 5 6 7 8 9 10 State Allowance State Allowance OK $ 600 GA $ 900 TN $ 400 Penalty Amount Owed Penalty % $ 0 0 100 5% 1,000 10% 5,000 15% 10,000 20% 50,000 25% Tax On Rich Minimum Option ID # 1 2 3 Penalty Actual $832.05 $812.85 11 12 13 14 15 16 17 18 19 20 21 22 Taxable Income $ 81,865.00 $ 85,263.00 $ 65,794.00 $ 369,209.00 $ 74,468.00 $ 23,500.00 $ 316,527.00 $ 52,935.00 $ 99,393.00 $ 17,437.00 4 5 6 State of Residence OK GA TN NY OK GA TN TN TN Actual Tax Actual Paid Tax Due $ 8,875.00 $ 14,422.00 $ 9,115.00 $ 14,534.00 $ 6,384.00 $ 6,259.00 $ 93,225.00 $ 103,629.00 $ 9,402.00 $ 9,594.00 $ 1,254.00 $ 700.00 $ 100,493.00 $ 90,534.00 $ 6,161.00 $ 6,287.00 $ 20,844.00 $ 17,370.00 $ 227.00 $ 244.00 Flat Tax Alternate 1 $12,279.75 $12,789.45 $6,579.40 $81,225.98 $11,170.20 $705.00 $69,635.94 $5,293.50 $14,908.95 $523.11 Flat Tax Alternate 2 $9,005.15 $9,378.93 $4,605.58 $73,841.80 $8,191.48 $1,175.00 $63,305.40 $3,705.45 $10,933.23 $871.85 Tax Owed/ Refund Due Actual $5,547 $5,419 $125 $10,404 $192 -$554 $9,959 $126 -$3,474 $17 Tax Owed/ Refund Due Alternate 1 $3,405 $3,674 $195 -$11,999 $1,768 -$549 -$30,857 -$868 $5,935 $296 Tax Owed/ Refund Due Alternate 2 $130 $264 -$1,778 -$19,383 $1,211 -$79 -$37,188 -$2,456 -$9,911 $645 Penalty Alternate 1 $340.48 $367.45 $9.77 0 $176.82 0 Penalty State Alternate 2 Allowance $6.51 Yes $13.20 Yes 0 Yes O No 0 Yes 0 Yes 0 Yes o Yes 0 Yes $32.24 Yes $2,080.80 $9.60 0 0 $6.30 0 $0.00 State Allowance Amount $900.00 $900.00 $400.00 $900.00 $900.00 $900.00 $400.00 $400.00 $400.00 $900.00 7 0 0 9 10 0 $14.81 GA 23 Check your work totals: $ 215,111.28 $ 185,013.87 $ 7,593.00 $ (40,868.72) $ (70,966.13) $ 3,741.60 $ 909.32||$ 51.95 7,000.00 $ 24 25 26