disregard question 5-8 but i just uploaded the other questions i do have

Chapter 5- Directions: Questions 1-5 and 8 should be completed in Excel. You can complete question 6 and 7 in a word processing program. Round all of your data in the same fashion as it is done in the text. Unless noted otherwise, dollar amounts should be rounded to two digits to the right of the decimal. See the appendix for Excel templates. 1. You are an intern at the Tax Assessor's Office for Jefferson City and your supervisor has asked you to prepare the following: A. A property tax estimate for a newly built subdivision (Walters) after she conducted her assessment of the property. Apply a 12% millage rate on the assessed value of each house and the park. The land that the house sits on is included in the market value of the property. Also, what is the total assessed value of all the units/properties and the total estimated tax? (See Appendix 5A) The new subdivision has 100 units and Park. The prices provided below are based on assessed value. . There are 15 two-bedroom houses on 5 acres of land valued at $80,000.00 each. There are 15 two-bedroom houses on 20 acres of land valued at $85,000.00 each. There are 60 three-bedroom houses with a two-car garage on 75 acres of land. The houses are valued at $210,000.00 each. The last 15 houses are four-bedroom units with a two-car garage on 15 acres of land. They are valued at $170,000.00 . Lastly, the subdivision has a park with a swimming pool, tennis court, and a basketball court. The park and green space is valued at $85,000.00 Note: Assessed value is: Individual Unit Value X the Number of Units B. The city also needs $1,600,000.00 in individual property taxes and the tax assessor indicated that the total assessed value of individual property is $20,000,000.00. What should the fixed or nominal tax rate be set at to collect the $1,600,000.00 in property taxes? Tax rate- C. The city has set the millage rate at 4% for all businesses. The spreadsheet in Appendix 5A has a partial listing of the city's businesses. Calculate the amount of taxes that can be expected along with the most effective tax rate (METR) for each business and an average tax rate using the partial list of businesses provided. (See Appendix 5A) The millage rate for each of the seven properties is 4%. Franklin's Clothing has a market value of $625,000 and an assessed value of $525,000. Payton's Tax Service has a market value of $575,000 and an assessed value of $475,000. . Stacie's Lawn Care has a market value of $285,000 and an assessed value of $175,000. Eva's Finishing School has a market value of $400,000 and an assessed value of $325,000. Tiffany's Day Care has a market value of $250,000 and an assessed value of $175,000. Yiesha's Hair Care has a market value of $370,000 and assessed value of $300,000. Myron's Boys Club has a market value of $650,000 and assessed value of $575,000. 2. Calculate the coefficient of dispersion using the following information. Should the assessor reapportion the assessed property values (use average assessment)? Explain. A. There are five pieces of property that sold for $175,000.00 each and were assessed at $125,000.00, $130,000.00, $125,000.00, $110,000.00 and $95,000.00. B. There are ten pieces of property that sold for $125,000.00 each and were assessed at $39,500.00, S90,900.00, $68,000.00, $65,000.00, $92,000.00, $90,000.00, $85,000.00, $75,000.00, $89,250.00, and $65.000.00. 3. Using the price related differential test, calculate the aggregate assessment-sales ratio using the data provided below. Are the higher priced properties over assessed or are the lower priced properties under assessed? (See Appendix 5C) Sales Price Assessed Value Assessment Ratio Property 1 $165,000.00 $190,000.00 Property 2 145,000.00 125,000.00 Property 3 25,000.00 20,000.00 Property 4 30,000.00 25,000.00 Property 5 25,000.00 18,000.00 Property 6 200,000.00 170,000.00 Property 7 150,000.00 125,000.00 Property 8 135,000.00 135,000.00 Property 9 30,000.00 22,000.00 Property 10 25,000.00 15,000.00 Property 11 25,000.00 19,000.00 Property 12 35,000.00 28,000.00 $990,000.00 $892,000.00 4. Calculate the revenue collected from federal income taxes, state income taxes, and the local pay roll tax for FY 2018 and prepare a revenue estimate for Jefferson City's Public School System for FY 2019 based on the following information. Assume that no other deductions came from the employee's salary other than what is listed in the spreadsheet. Hint: New employees are not eligible for raises, which are based on performance from the previous fiscal year. (See Appendix 5D) FY 2018 Facts: 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 $99,000.00 Each principal has a salary of $75,000.00 . Four of the teachers (Teacher A) have salaries of $55,000.00; six teachers (Teacher B) have salaries of $45,000.00; and six teachers (Teacher C) have salaries of $40,000.00 The remaining two teachers (A Level) are also coaches. The football coach receives an additional $5,000.00 in salary and the basketball coach receives an additional $7,000.00 in salary each year. Mrs. Jones manages the kitchen. Her FY 2018 salary is $45,000.00. The remaining kitchen staff make $28,000.00 each in FY 2018. The three janitors make $25,000.00 each in FY 2018. The federal income tax rates are. Salary Range Calculations $0-$9,325 = $9,325 x 10% $9,325.01-$37.950 - $28,624.99 x 15% $37.950.01-$91,900 = $53,949.99 x 25% $91,900.01-$191,650 - $99,749.99 x 28% The tax rates are the same in FY 2018 & FY 2019. The state income tax rate is 4% for the first $3,000.00 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 2018 and 1.85% in FY 2019. FY 2019 Facts: . In FY 2019, the school system hired 2 more teachers at the Teacher D level. They will begin work in FY 2019 at a salary of $35,000.00. . In FY 2019, each school employee received a 5% raise except the principals and superintendent. They received a 2% raise. Note, new employees do not receive a raise