Using a properly designed worksheet that allows users to easily change the assumption values, calculate the revenues and expenses for the next year using the proposed increases in the case. Format all answers as Currency (2 decimals).
What is the sum of the projected 2020 revenues?
What is the sum of the project 2020 expenses?
What is the size of the surplus or deficit?
Description BACKGROUND Municipal budgets are required by provincial law to balance cities must take in enough revenue through property taxes and other sources, like fees for services, to finance all of the city's operations in any given year. You have been hired by the City of Toronto to help prepare next year's 2020 operating budget. Your starting point is to review the 2019 approved operating budget, conveniently in the format of an Excel worksheet, and review the revenues and expenses of each city program. As per decisions made by the city council, the following increases for 2020 were agreed upon (prior to the COVID-19 shutdown): On the revenue side, the city budget committee proposes the following increases: - an overall property tax increase of 2.5% which represents a weighted average of the following: 3.5% increase for residential properties, 1.75% increase for commercial properties , 1.17% for industrial properties, and 0% increase for multi-residential/apartment buildings as per provincial legislation - a water rate increase of 3.0% - a solid waste rate increase of 3.0% - a Toronto Transit Commission (Conventional) fare increase by 10 per ride, from $3.10 to $3.20 for an adult Presto fare. - a 0% increase for all other categories of revenue On the expense side, the city budget committee proposes to increase each expense category by an average of 1.43% except for the following: an increase of 4% to the Toronto Police Service - a 4.7% increase to Toronto Public Health CATEGORY 311 Toronto Affordable Housing Office Arena Boards of Management Association of Community Centres Auditor General's Office Capital & Corporate Financing Children's Services City Clerk's Office City Council City Manager's Office City Planning Court Services CreateTO Economic Development ulture Engineering & Construction Services Exhibition Place Facilities, Real Estate, Environment & Energy Fire Services Fleet Services Heritage Toronto Information & Technology Integrity Commissioner's Office Legal Services Long-Term Care Homes & Services Mayor's Office Municipal Licensing & Standards Non-Program Expenditures Non-Program Revenues Office of the Chief Financial Officer Office of the Controller Office of the Lobbyist Registrar Office of the Ombudsman Parks, Forestry & Recreation Policy, Planning, Finance & Administration 2019 ACTUAL REVENUES 2019 ACTUAL EXPENSES $ 9,180,581.85 $ 19,328,094.54 $ 9,906,849.96 $ 11,076,975.11 $ 9,800,116.22 $ 9,782,442.09 $ 316,140.00 $ 8,596,890.69 0 $ 6,638,622.88 $ 66,657,120.56 $ 998,824,362.21 $ 570,637,896.50 $ 657,393,650.10 $ 20,306,059.62 $ 52,625,338.63 $ 585,150.00 $ 22,180,603.99 $ 9,906,355.95 $ 58,914,131.91 $ 39,508,953.49 $ 54,946,536.17 $ 58,802,562.30 $ 52,366,473.16 $ 12,841,142.35 $ 12,841,142.35 $ 12,442,751.11 $ 83,188,442.01 $ 72,234,705.76 $ 76,337,381.14 $ 56,754,648.00 $ 56,594,648.00 $ 134,246,548.16 $ 206,795,394.93 $ 18,286,210.60 $ 494,069,437.45 $ 58,414,399.02 $ 58,582,916.90 $ 710,791.01 $ 1,112,035.58 $ 54,568,519.25 $ 133,708,494.53 0 $ 575,725.34 $ 46,066,744.24 $ 65,668,695.35 $ 219,070,787.37 $ 268,499,714.10 0 $ 2,567,004.65 $ 41,984,611.14 $ 62,040,534.82 $ 172,015,455.44 $ 618,165,474.57 $ 1,524,354,507.52 $ 98,621,202.51 $ 9,500,500.30 $ 20,883,515.52 $ 52,835,169.95 $ 81,049,127.32 0 $ 1,235,081.74 0 $ 1,990,894.98 $ 151,590,104.24 $ 476,625,094.38 $ 16,057,575.41 $ 25,264,616.08 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 37 Parks, Forestry & Recreation 38 Policy, Planning, Finance & Administration 39 Property Taxes 40 Shelter, Support & Housing Administration 41 Social Development, Finance & Administration 42 Solid Waste Management Services 43 Theatres 44 Toronto & Region Conservation Authority 45 Toronto Building 46 Toronto Employment & Social Services 47 Toronto Paramedic Services 48 Toronto Parking Authority 49 Toronto Police Service 50 Toronto Police Services Board 51 Toronto Public Health 52 Toronto Public Library 53 Toronto Transit Commission - Conventional 54 Toronto Transit Commission - Wheel Trans 55 Toronto Zoo 56 Transportation Services 57 Water 58 Yonge-Dundas Square 59 60 TOTAL $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 151,590,104.24 $ 16,057,575.41 $ 4,397,555,750.60 $ 540,740,141.33 $ 25,907,284.01 $ 411,620,175.10 $ 32,085,548.96 $ 47,531,924.00 $ 75,695,321.76 $ 1,056,579,612.17 $ 147,820,803.54 $ 167,137,597.78 $ 145,976,800.00 $ 2,300,000.04 $ 190,780,409.57 $ 19,221,083.00 $ 1,288,582,600.01 $ 8,110,700.00 $ 40,630,589.54 $ 170,868,751.00 $ 1,331,389,690.37 $ 3,442,427.99 $ 476,625,094.38 25,264,616.08 84,586,103.60 1,003,701,845.11 74,324,785.17 411,620,174.58 37,360,008.46 51,364,738.85 59,801,113.73 1,147,582,760.21 233,556,304.24 167,137,597.78 1,172,748,296.39 4,761,000.00 255,279,231.74 206,880,105.49 1,910,540,399.22 149,018,897.38 53,084,916.23 396,289,075.76 1,331,389,690.38 3,442,428.04 $ 13,553,560,168.09 $ 13,553,560,168.09 61 Description BACKGROUND Municipal budgets are required by provincial law to balance cities must take in enough revenue through property taxes and other sources, like fees for services, to finance all of the city's operations in any given year. You have been hired by the City of Toronto to help prepare next year's 2020 operating budget. Your starting point is to review the 2019 approved operating budget, conveniently in the format of an Excel worksheet, and review the revenues and expenses of each city program. As per decisions made by the city council, the following increases for 2020 were agreed upon (prior to the COVID-19 shutdown): On the revenue side, the city budget committee proposes the following increases: - an overall property tax increase of 2.5% which represents a weighted average of the following: 3.5% increase for residential properties, 1.75% increase for commercial properties , 1.17% for industrial properties, and 0% increase for multi-residential/apartment buildings as per provincial legislation - a water rate increase of 3.0% - a solid waste rate increase of 3.0% - a Toronto Transit Commission (Conventional) fare increase by 10 per ride, from $3.10 to $3.20 for an adult Presto fare. - a 0% increase for all other categories of revenue On the expense side, the city budget committee proposes to increase each expense category by an average of 1.43% except for the following: an increase of 4% to the Toronto Police Service - a 4.7% increase to Toronto Public Health CATEGORY 311 Toronto Affordable Housing Office Arena Boards of Management Association of Community Centres Auditor General's Office Capital & Corporate Financing Children's Services City Clerk's Office City Council City Manager's Office City Planning Court Services CreateTO Economic Development ulture Engineering & Construction Services Exhibition Place Facilities, Real Estate, Environment & Energy Fire Services Fleet Services Heritage Toronto Information & Technology Integrity Commissioner's Office Legal Services Long-Term Care Homes & Services Mayor's Office Municipal Licensing & Standards Non-Program Expenditures Non-Program Revenues Office of the Chief Financial Officer Office of the Controller Office of the Lobbyist Registrar Office of the Ombudsman Parks, Forestry & Recreation Policy, Planning, Finance & Administration 2019 ACTUAL REVENUES 2019 ACTUAL EXPENSES $ 9,180,581.85 $ 19,328,094.54 $ 9,906,849.96 $ 11,076,975.11 $ 9,800,116.22 $ 9,782,442.09 $ 316,140.00 $ 8,596,890.69 0 $ 6,638,622.88 $ 66,657,120.56 $ 998,824,362.21 $ 570,637,896.50 $ 657,393,650.10 $ 20,306,059.62 $ 52,625,338.63 $ 585,150.00 $ 22,180,603.99 $ 9,906,355.95 $ 58,914,131.91 $ 39,508,953.49 $ 54,946,536.17 $ 58,802,562.30 $ 52,366,473.16 $ 12,841,142.35 $ 12,841,142.35 $ 12,442,751.11 $ 83,188,442.01 $ 72,234,705.76 $ 76,337,381.14 $ 56,754,648.00 $ 56,594,648.00 $ 134,246,548.16 $ 206,795,394.93 $ 18,286,210.60 $ 494,069,437.45 $ 58,414,399.02 $ 58,582,916.90 $ 710,791.01 $ 1,112,035.58 $ 54,568,519.25 $ 133,708,494.53 0 $ 575,725.34 $ 46,066,744.24 $ 65,668,695.35 $ 219,070,787.37 $ 268,499,714.10 0 $ 2,567,004.65 $ 41,984,611.14 $ 62,040,534.82 $ 172,015,455.44 $ 618,165,474.57 $ 1,524,354,507.52 $ 98,621,202.51 $ 9,500,500.30 $ 20,883,515.52 $ 52,835,169.95 $ 81,049,127.32 0 $ 1,235,081.74 0 $ 1,990,894.98 $ 151,590,104.24 $ 476,625,094.38 $ 16,057,575.41 $ 25,264,616.08 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 37 Parks, Forestry & Recreation 38 Policy, Planning, Finance & Administration 39 Property Taxes 40 Shelter, Support & Housing Administration 41 Social Development, Finance & Administration 42 Solid Waste Management Services 43 Theatres 44 Toronto & Region Conservation Authority 45 Toronto Building 46 Toronto Employment & Social Services 47 Toronto Paramedic Services 48 Toronto Parking Authority 49 Toronto Police Service 50 Toronto Police Services Board 51 Toronto Public Health 52 Toronto Public Library 53 Toronto Transit Commission - Conventional 54 Toronto Transit Commission - Wheel Trans 55 Toronto Zoo 56 Transportation Services 57 Water 58 Yonge-Dundas Square 59 60 TOTAL $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 151,590,104.24 $ 16,057,575.41 $ 4,397,555,750.60 $ 540,740,141.33 $ 25,907,284.01 $ 411,620,175.10 $ 32,085,548.96 $ 47,531,924.00 $ 75,695,321.76 $ 1,056,579,612.17 $ 147,820,803.54 $ 167,137,597.78 $ 145,976,800.00 $ 2,300,000.04 $ 190,780,409.57 $ 19,221,083.00 $ 1,288,582,600.01 $ 8,110,700.00 $ 40,630,589.54 $ 170,868,751.00 $ 1,331,389,690.37 $ 3,442,427.99 $ 476,625,094.38 25,264,616.08 84,586,103.60 1,003,701,845.11 74,324,785.17 411,620,174.58 37,360,008.46 51,364,738.85 59,801,113.73 1,147,582,760.21 233,556,304.24 167,137,597.78 1,172,748,296.39 4,761,000.00 255,279,231.74 206,880,105.49 1,910,540,399.22 149,018,897.38 53,084,916.23 396,289,075.76 1,331,389,690.38 3,442,428.04 $ 13,553,560,168.09 $ 13,553,560,168.09 61
