代写PUBLIC ADMINISTRATION 532-01 Fall 2024 Semester CASE STUDY #1: ANALYZING A BUDGET帮做R编程
- 首页 >> DatabasePUBLIC ADMINISTRATION 532-01
Fall 2024 Semester
CASE STUDY #1: ANALYZING A BUDGET
Work individually or in groups of two to prepare an analysis of the proposed FY2024 proposed budget of City XYZ. This assignment is available on Blackboard in the "Assignments" section. The revenue, expenditure and personnel data for this exercise are contained in a separate Excel Spreadsheet called "Fall2024BudgetCaseData" in the “Assignments” section of Blackboard.
Read the instructions carefully to be sure you fully complete the assignment. The assignment is due on October 28th before class on by email to me at rcalia@stuart.iit.edu. Do not pdf the assignment. It is worth 30 points.
Assignments must:
· Include your names, the date, class name and title of assignment.
· Include page numbers;
· Use 1" or 1.25" margins; and
· Use ragged right margins – do not right justify the margins.
· Copy each subheading and provide your answer below. For example, you would input a section subheading of:
A. Prepare and Analyze Resource Trends
Then input the various subheadings under each and provide an answer (including a chart) under each.
· General Operating Fund Resource Trends
· General Operating Fund Appropriation Trends
You must use Excel for tables, charts and graphs. Tables, charts or graphs must include a heading. Tables and charts must be included in the text, not as attachments after the text.
· If you are pasting Excel tables, charts or graphs into a Word document, please use the Paste Special, Picture (Enhanced Metafile) command to paste the document
· Be sure that Word tables or charts are on one page.
Here are some links to information about how to paste excel tables and charts into Word. You can always ask me as well.
How to Copy and Paste a Chart From Microsoft Excel (howtogeek.com)
(23) How to Insert Microsoft Excel Data and Charts into Microsoft Word and PowerPoint - YouTube
Failure to comply with these style. requirements will results in a loss of points toward the final grade.
For content reference, refer to:
1) Sanders, Kenneth D. and Tyler, Charlie, “Local Government Financial and Budgetary Analysis,” in Rabin, editor. Budgeting: Formulation and Execution (Athens: Carl Vinson Institute of Government at the University of Georgia, 1996). (Blackboard)
2) Class PowerPoint presentations on:
· September 6th - Best Practices and Personnel Issues
· September 13th – Revenues Part One;
· September 20th - Revenues Part Two; and
· September 27th - Budget Analysis.
The Case
City XYZ is a suburban home rule municipality in northeastern Illinois. The estimated population in FY2024 will be 45,355. The City’s population has grown only slightly over the past 5 years. The municipality is “landlocked,” that it is surrounded by other communities so there are no opportunities for growth through annexation. It is an older but desirable community to live in. School property taxes, levied by two overlapping school districts, are considered high. The region is in an economic recession that will likely continue for several more years.
The City has a well developed downtown commercial area and many businesses including several auto dealerships. It also has a downtown casino. The current casino license expires in 2024 and it will be re-bid in a competitive process. The City hopes to get at least $1.225 million in one-time revenue for the license. The city has no debt for operating purposes; all debt issued is for capital projects.
City XYZ is governed by a Mayor elected citywide and a 7-member Council elected by district. The city workforce consists of 701 full-time equivalent positions. Of that amount, approximately 80% are union employees and 20% are exempt (non-union) employees. There is a residency requirement for City employees (i.e., they must live in the city).
FY2024 Budget Proposal
The Mayor has proposed a FY2024 proposed municipal general operating budget of $81.7 million. The budget for City XYZ must be approved by the City Council, so the Mayor must persuade them to accept it. However, there is strong opposition to the Mayor’s plan from Council members and many stakeholder groups.
The budget includes operating appropriations as well as capital spending. Capital spending includes spending for long-term infrastructure such as capital projects and capital equipment.
As a home rule government, the City has access to a wide variety of tax and fee revenues. It also imposes, collects or receives a number of non tax resources, including federal revenues, the sale of property and advertising and concession revenue. The federal government annually provides millions of dollars in federal revenues to the City for transportation, public housing and environmental protection programs. While the amount changes from year to year, the federal government will always continue to provide some financial support to the City because of the influence of the state's powerful congressional delegation. The tax and fee revenues plus the non tax resources provide the city with its total general operating fund resources.
Because of growing expenditure pressures, the administration proposes to enact the following revenue enhancements in its FY2024 budget:
• A 2.5% Property Tax Levy increase (as a home rule municipality, City XYZ is not subject to property tax caps). Property Taxes were held flat in FY2020 through FY2022 and then raised 2.5% in FY2023. A 2.5% increase is proposed in FY2024.
• Increases in sanitation fees, parking taxes and licenses and permits.
• New home rule cigarette taxes as permitted by state law.
• A new 911 telephone tax.
In addition:
• The City opened a public golf course in FY2023 for which it receives fees from a management company.
• The City's current downtown casino license will expire and be re-bid to a new operator in FY2024. The City estimates it will get $1.225 million in one-time revenue for the license.
• Home rule gambling taxes will be raised in the FY2024 Proposed budget.
Appropriation Trends Over Time
The following appropriation actions will occur in City XYZ between FY2020 and the FY2024 proposed budget.
· Employee salary Increases: 5% in FY2021, 5% in FY2022, 5.5% in FY2023 and in the FY2024 Proposed Budget
· There were pension benefit expenditure increases in FY2021, FY2022 and FY2023 that corresponded to the salary increases.
· The Mayor proposes a $1.1 million pension holiday in FY2024. This will bring pension funds down to a 60% funded ratio (assets/liabilities). There will likely be another pension holiday in FY2025 of $1,500,000. A pension holiday means that the City reduces the amount of money it spends for pensions - because of this reduction the funded ratio will decrease, putting the fiscal health of the pension funds in jeopardy.
· Increased energy costs in FY2024 requires increases required spending in the FY2024 proposed budget for utilities.
Note: Benefits include insurance + pension benefits
The budget data for this exercise are contained in a separate Excel Spreadsheet called "Fall2024BudgetCaseData" on Blackboard under the Class Assignments tab.
Pay attention to the assumptions listed in that document.
CLASS ASSIGNMENT
Part 1: Prepare Trend Analyses of the Proposed Budget – 12 points
Use excel charts and/or graphs (line, bar or pie) to illustrate your analysis in the text and the brief narrative description you provide that is included before or after the charts or graphs. Do not put charts and graphs in an appendix. Which charts and graphs you use and how many charts and graphs you use is up to your group.
Please use subheadings for each section.
A. Prepare and Analyze Resource Trends
General Operating Fund Resource Trends
How much will total general operating fund resources grow between FY2020 and FY2024?
• Present both the dollar amount and the percentage increase or decrease in the Excel chart.
• Describe in a brief narrative what you find.
The focus is on general operating fund resources, not all resources because these are the most reliable sources of funding for the budget. Resources include revenues plus all other recurring sources used to fund the operating budget. So, to calculate growth in these resources only, you must exclude figures for the following resources:
Ø Transfers
Ø Tax Amnesty
Ø Casino License
Ø Sale of Property
Ø Advertising and Concession Revenue
Ø Interest Earnings
Also, do not include Unreserved General Operating Fund Fund Balance in your calculations
Note: You do not need to provide calculations of changes in each year between FY2020 and FY2024, just the changes between the two fiscal years, FY2020 and FY2024.
General Operating Fund Appropriation Trends
• How much will total general operating fund appropriations increase between FY2020 and FY2024?
• Give the dollar amount and percentage increase or decrease amounts.
• Describe in a brief narrative what you find.
Note: You do not need to provide calculations of changes in each year between FY2020 and FY2024, just the changes between the two fiscal years, FY2020 and FY2024.
General Operating Fund Recurring Resources vs. Nonrecurring Resources
• Break out Total General Fund Operating and Non Tax Resources for FY2020 versus FY2024 into two categories - Recurring versus Nonrecurring Resources.
• Recurring resources are those that the City receives on an annual basis all the time like general taxes. The amounts are likely to be relatively constant, perhaps increasing if there is a tax or fee increase in a particular year. Nonrecurring resources are received on a one-time basis, like a fee from a contract. These amounts are likely to vary widely from year to year.
• Resources include revenues plus all other resources used to fund the operating budget. (Do not include the Unreserved General Operating Fund Fund Balance)
• Describe the dollar amount and percentage change for recurring versus non-recurring resources between FY2020 and FY2024– for guidance see the Sanders and Tyler reading assignment on Blackboard.
• Note that Sanders & Tyler state that interest should be classified as a 50% recurring and 50% non-recurring resource.
Note: You do not need to provide calculations of changes in each year between FY2020 and FY2024, just the changes between the two fiscal years, FY2020 and FY2024.
Reliance on Certain Revenues
· What will be the top 5 resources for City XYZ by dollar amount in FY2024?
· What will the top 5 resources be by percent of total resources? (Include only Total General Fund Operating and Non Tax Resources – do not include the Unreserved General Operating Fund Fund Balance)
• What were the top 5 resources in FY2020?
· What will the top 5 resources be by percent of total resources?
• Has there been a change in top 5 resources between FY2020 and FY2024? Describe in a brief narrative what you find.
Note: You do not need to provide calculations of changes in each year between FY2020 and FY2024, just the changes between the two fiscal years, FY2020 and FY2024.
Debt Service
• How much will debt service increase between FY2020 and FY2024?
• Give dollar amounts and percentage increase or decrease.
• Describe in a brief narrative what you find.
Total Capital Appropriations
• Analyze the total capital portion only of the appropriations spreadsheet.
• How much will total capital expenditures increase or decrease between FY2020 and FY2024?
• Give dollar amounts and the percentage increase/decrease changes.
• Describe in a brief narrative what you find.
B. Prepare and Analyze Personnel Trends
• How much money is budgeted each year for vacancies vs. filled positions? (include cost of salaries + benefits)
• How much will total personal services increase between FY2020 and FY2024? Give dollar amounts and percentage increase or decrease.
• How much will total benefits increase between FY2020 and FY2024? Give dollar amounts and percentage increase or decrease.
• Describe in a brief narrative what you find.
• What are the implications of these trends? (Don’t forget to consider what is being considered at this point for FY2024 for pensions).
Part II. Conduct an Analysis of the Budget – 8 points
Please examine the trends you plotted in Part I and evaluate the fiscal condition of City XYZ from FY2020 through FY2024 in a narrative discussion.
Your task here is to evaluate the strengths and weaknesses of the City’s fiscal condition.
To conduct your analysis, you should calculate and then discuss each of the following eight (8) indicators in a short narrative:
1. The trend of the General Operating Funds RESOURCE Growth versus General Operating Fund Appropriation Growth between FY2020 and FY2024 – this means you must compare the percentage increase of General Operating resources between FY2020 and FY2024 to the percentage increase of General Operating Fund Appropriations between FY2020 and FY2024.
Please explain in a short narrative what this trend tells you. is this a positive, negative or neutral trend? Why?
2. General Operating Fund Recurring versus Non-Recurring Resources – this means you must identify which resources are recurring and which are non-recurring.
Then, you must calculate the percentage increase of General Operating Fund recurring resources between FY2020 and FY2024 and compare it to the percentage increase of General Operating Fund non-recurring resources between FY2020 and FY2024.
Here you compare all of the resources that the city uses, including revenues and non tax resources - Please explain what this trend tells you; i.e. is this a positive, negative or neutral trend? Why?
3. Reliance on Resources – Does City XYZ rely on a few revenues or resources (such as property taxes) or does it rely on a broad mix of many different types of resources or revenues?
To answer this, you need to identify the biggest types of General Operating Fund resources in FY2020 and FY2024 (just those 2 years) and calculate how much of the General Operating Fund resources are the top sources.
Please explain what this trend tells you.
4. Total Capital Expenditure Trends - what is the trend in total capital expenditures between FY2020-FY2024)?
What does this tell you; i.e. is this a positive, negative or neutral trend?
5. Debt Service as a Percentage of Recurring Resources – what is the trend over time (FY2020 to FY2024)?
Classify the figures in each as year as Low, Good, Acceptable or Maximum as does the article by Sanders, Kenneth D. and Tyler, Charlie, “Local Government Financial and Budgetary Analysis
What does this tell you; i.e. is this a positive, negative or neutral trend?
6. Fund Balance – what is the unreserved General fund balance trend over time (FY2020 to FY2024)?
To answer this question, you must calculate the ratio of unreserved General Operating Fund Fund balance to Total General Operating fund operating resources for each year from FY2020 to FY2024. Remember, the GFOA best practice standard is that the ratio should be 17%.
What does this trend tell you?
7. Transfers into the General Operating Fund - what is the trend in transfers between FY2020-FY2024)?
What does this tell you; i.e. is this a positive, negative or neutral trend?
8. Personnel Costs - What is the trend for total personal service expenditures and for benefits only over time (FY2020-FY2024)?
What does this tell you; i.e. is this a positive, negative or neutral trend?
9. Review your analysis of all 8 indicators. Overall, what is the City’s financial condition based on these indicators? Strong? Weak? Somewhere in between?
Part III. Develop an Alternative FY2024 Budget (10 points)
Choose one of the stakeholders listed below and develop an alternative budget to the Mayor’s budget for FY2024 from their perspective.
· So, you must develop an alternative budget that reflects their views.
· You must show the changes you propose for your alternative budget in an excel spreadsheet.
· Council Member Smith – you were elected on an anti-tax, pro-business campaign with overwhelming support from your ward. You have dreams of running for the Office of Mayor at the next election. You are against increasing taxes, especially taxes on business.
·
Council Member Jones – you are an ally of the Mayor and his policies. However, you favor shifting the tax burden away from your constituents and onto businesses. After all, businesses can’t vote but citizens do! You support the mayor in general EXCEPT that you do not want citizens to experience any tax increases. You do support business tax increases.
· Public Employee Union President – The local public employee union represents most city workers, including police and firefighters. They are opposed to any policy that will reduce their members’ pay or benefits. You oppose any layoffs of union personnel. You oppose any privatization deal. However, union members do live in the City and property taxes. They complain about rising property taxes.
· Taxpayer Organization. Your organization argues that City XYZ taxes are too high and are driving people and businesses out of the City. However, you will support non-tax (fee) revenue solutions as well as spending adjustments or cuts to balance the budget because there is a voluntary element to those solutions – if you don’t use the service, you don’t pay!
· Citizens for a Better XYZ - This nonpartisan civic group focuses on making City XYZ a better place to live. They are strongly opposed to gambling and want the city to close its casino. They believe that the city should find revenues from sources other than the casino license and gambling taxes. They also oppose the City's aggressive pursuit of selling advertising on city property as being excessively commercial and adding to blight. They want the City to no longer get any advertising and concession revenue and cancel all such contracts with vendors. They would support small increases in home rule taxes but they are strongly opposed to any increase in the property tax including the proposed increase for FY2024. They also oppose any layoffs of current personnel.
· Social Service Organization Advocate – You represent a coalition of social service provider organizations (healthcare, job training, housing assistance, daycare, etc.) representing underserved communities and the poor. You advocate for spending increases in the City budget for these programs to meet growing needs as the local economy slips into a recession. You oppose the Mayor's budget because it does not spend enough money on social services.
· Chamber of Commerce – As an organization representing the businesses of City XYZ, the Chamber of Commerce is absolutely opposed to increasing business or property taxes. You oppose the Mayor's budget because you do not want any increase in business or property taxes.
· Senior Citizens Coalition – Senior citizens living on fixed incomes are strongly opposed to any increase in property taxes. They are a powerful interest group in City XYZ who vote in large numbers. They are very unhappy with the Mayor who has consistently raised property taxes.
· Open Space Advocate – Your organization wants to close the new City golf course and turn it into a public park. The park would cost $500,000 per year. Shutting down the golf course will cost the City revenue. You oppose the Mayor's budget because you want to close the golf course.
Assumptions in Building an Alternative Budget
Your budget will use different assumptions than the City XYZ has initially estimated.
In building a budget you have to consider all relevant resource and expenditure constraints and opportunities available.
Your alternative budget proposal must include:
1) An Excel spreadsheet showing how you propose to balance the FY2024 budget. You will replace the FY2024 Proposed Budget column in the Excel chart with your own budget proposal figures. You will also show your assumptions in the column next to your new budget proposal.
Balancing the budget requires increasing revenues and/or cutting expenditures. How you balance the budget is up to you.
2) A narrative where you discuss the reasons you have made the decisions represented in your budget and defend your proposal.
Considerations in Building Your Alternative Budget
· State Taxes: You cannot increase the assumed rate of increase for any of the state taxes.
· Federal Receipts: You cannot increase the assumed rate of increase for federal revenues.
· Interest Earnings: You cannot increase the assumed rate of increase
· Transfers: You cannot transfer any resources from other funds in FY2024.
The City has home rule legal authority to implement a number of new taxes and fees but has not yet done so. You can choose to implement these taxes and fees. These revenues and how much they could yield in FY2024 are as follows:
• Real Estate Transfer Tax: $250,000 – this is a tax imposed on home sales in City XYZ
• Home Rule Gasoline Tax: $500,000 – this tax would be in addition to the state motor fuel tax
• Restaurant Tax: $150,000 – this tax would be added to restaurant meals in addition to state and home rule sales taxes.
Personnel Options
· City XYZ can consider a number of personnel actions to reduce its projected FY2024 expenditure increases:
o Cancel salary and benefit increases for non-union (exempt) employees.
o Require furloughs for non-union employees. A furlough is mandatory unpaid time off. The employee retains his or her position and benefits. A furlough amounts to a salary reduction. 5 furlough days for non-union employees could reduce salary costs for those employees by about 1.9%.
o Require furloughs for all employees, including union employees. A furlough is mandatory unpaid time off. It amounts to a salary reduction. 5 furlough days for non-union employees could reduce total salary costs by about 1.9%.
o Increase all employee health insurance premiums and co-payments for prescription drugs by 10%. This could generate an additional $250,000 per year.
o Eliminate or reduce the subsidy for health insurance coverage for retirees who are not yet eligible for Medicare.
§ Eliminating the current 100% subsidy could save $750,000 per year; these retires are eligible for Obamacare.
§ Reducing the subsidy by requiring the retirees to pay premiums for 50% of the cost of their health insurance could save $375,000 per year.
§ Reducing the subsidy by requiring the retirees to pay premiums for 25% of the cost of their health insurance could save $187,500 per year.
o Reduce or eliminate vacancies.
o Lay off or fire workers – Reduce the workforce by eliminating full-time equivalent positions. Union employees are eligible for layoffs; they can return to work if funding is restored at a future dated; non-union employees can be terminated (fired).
Privatization Option
· City XYZ could privatize the management and operation of refuse and recycling services, currently budgeted at 5% of the total budget. This would save up to $600,000 per year for the term of a 5-year contract with a private provider such as Waste Management. If this option was employed, 35 employees would have to be transferred to other city positions, offered early retirement or a deal struck with the private provider to retain them at their current wage schedule.