Please include Formulas: 1. On the Flat Tax sheet, beginning in cell G12, write a formulathat uses t

Please include Formulas: 1. On the Flat Tax sheet, beginning in cell G12, write a formulathat uses the Alternate 1 flat tax rate to determine the totaldollar value of the tax for the income in cell B12, rounded to thenearest dollar. As detailed in the Flat Tax Rate table (cellsA1:F4), this tax scheme calculates taxes by multiplying the totalincome 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%, etc. Write a formula so that it can be copied downthe column, and then copy it to cells G13:G21. 2. In cell H12, write a formula that uses the Alternative 2 flatrate to determine the total dollar value of the tax for the incomein cell B12, rounded to the nearest dollar. As detailed in the sameFlat Tax Rate table (Cells A1:F4), this scheme also calculatestaxes by multiplying the total income by the corresponding rate.Again, for example, incomes below $30,000 pay no tax; incomes of atleast $30,000 but less than $50,000 pay 6%, etc. Write the formulaso that it can be copied down the column, and then copy it to cellsH13:H21. 3. In cell I12, write a formula that calculates the amount ofunpaid taxes the first taxpayer still owes on April 15, rounded tothe nearest dollar. The unpaid taxes are based on the actual amountof taxes owed, the actual withholding taxes paid, and estimatedtaxes paid. Write the formula so that it can be copied down thecolumn to calculate this amount for each taxpayer. Also, write theformula so that it can be copied across the row to determine theunpaid amount based on the taxes owed for the Alternative 1 andAlternative 2 flat tax calculations. (Assume the same withholdingand estimated taxes paid.) Copy the formula to cells I13:I21 and tocells J12:K21. 4. In cell L12, write a formula that determines the actual penaltyowed on the unpaid tax amounts based on the penalty schedule table,rounded to the nearest dollar. For example, unpaid tax balances ofless 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 unpaidtax amount. [Keep in mind that if the unpaid tax amount isnegative, indicating that the IRS owes the taxpayer a refund, nopenalty applies. Develop an algorithm that will not charge apenalty should the unpaid tax amount be negative and place in thecell the words ‘refund’.] Copy this formula both down the column tocalculate the penalty for the corresponding ID# and across the rowto determine the penalties based on each alternative taxscheme. 5. In row 23 calculate the total values for each category for all10 tax returns. 6. As part of the flat-rate tax, one possible scheme would includestate allowance to balance the high and low cost of living. Theamount of the allowance is listed by state in cells A7:B9. In cellO12, write a formula that determines the state allowance for thistaxpayer, rounded to the nearest dollar. Write the formula so thatit can be copied down the column, and then copy it to cellsO13:O21. Attached

 

Looking for a Similar Assignment? Let us take care of your classwork while you enjoy your free time! All papers are written from scratch and are 100% Original. Try us today! Use Code FREE15