Question

Using an excel spreadsheet to present your work, calculate the projected revenue collected from federal income taxes, state income taxes, and the local payroll tax for FY 2014 and prepare a revenue estimate for the City of Newark’s Public School System for FY 2015 based on the following information. Assume that no other deductions came from the employee’s salary other than what is listed here. Hint: New employees are not eligible for raises, which are based on performance from the previous fiscal year. (See Table below for format. You are to prepare two spreadsheets (projected revenue and revenue estimate))...Show formulas for calcualtions

FY 2014 Facts:

1 The school system currently has 34 full-time employees. There is one superintendent, two principals, three janitors, ten kitchen staff, and eighteen teachers (including coaches).

The superintendent has a salary of $95,000

Each principal has a salary of $70,000

Four of the teachers (Teacher A) had salaries of $55,000; six teachers (Teacher B) had salaries of $45,000 and six teachers (Teacher C) had salaries of $40,000

The remaining two teachers (A Level) are also coaches. The football coach receives and additional $5,000 in salary and the basketball coach receives and additional $7,000 in salary each year.

Mrs. Jones manages the kitchen. Her FY 2014 salary was $45,000

The remaining kitchen staff made $28,000 each in FY 2014

The three janitors made $25,000 each in FY 2014

Use these federal income tax rates: $0 - $8,700.99 = 10%; $8,701 - $35350.99 = 15%; $35,351 - $85,650.99 = 25%; $85,651 - $178,650.99 = 28%; $178,651 - $388,350.99 = 33%; >$388,351 = 35%. The tax rates were the same in FY 2014 and FY 2015.

The state income tax rate is 4% for the first $3,000 of employee salary and 5.5% on everything above that amount. The rate is the same in both years.

The local payroll tax is 1.75% in FY 2014 and 1.85% in FY 2015.

FY 2015 Facts:

In FY 2015, the school system hired two more teachers at the Teacher D level. They will begin work in FY 2015 at a salary of $35,000.

In FY 2015, each school employee received a 5% raise except the principals and superintendents. They received a 2% raise. Note, new employees, do not receive a raise.

Position Description # in Grade FY 2014 Salary Fed Inc. Tax State Inc. Tax Payroll Tax Total Taxes

Superintendent 1

Principal 2

Teacher (A) 4

Teacher (B) 6

Teacher (C) 6

Janitor 3

Kitchen Manager 1

Kitchen Staff 9

Football Coach 1

Basket Ball Coach 1

Total 34

Answer 1