Accounting Budgeting & Miami Dade County Excel Questions

Below is a chosen set of financial indicators used to assess the financial condition of local governments in Florida by the

Auditor General

. Using the EXCEL template, assess the financial condition of Miami-Dade County using the four financial indicators for the past five years and answer the following questions. Note that “Benchmarks” indicate the average value of the specific indicator of counties with similar population size and taxable value with Miami-Dade County.

Financial Indicators                                        Formula                                                                  Warning Trend

Cash ratio                   Cash and Investments / Current liabilities                 Decreasing trend may indicate that the local government   has overextended itself in the long run or may be having difficulty raising   the cash needed to meet its current needs

 

Fund balance ratio         Unassigned and Assigned FB / Total Expenditures           Decreasing trend may indicate   unstructured budgets that could lead to future budgetary problems for the   local government even if the current fund balance is positive.

 

Operating ratio         Excess Revenue Over (Under) Expenditures / Total Revenues                  Decreasing surpluses or   increasing deficits may indicate that current revenues are not supporting   current expenditures

 

Long-term debt per capita                 Long-term debt / Population                   Increasing trend may indicate   that the local government has a decreasing level of flexibility in how   resources are allocated or decreasing ability to pay its long-term debt.

Calculate the financial indicators on the EXCEL template so that you complete the four graphs. Copy and paste the graphs here.

Assess the historical trend for each indicator and compare it against the benchmark. Is Miami-Dade County under a favorable trend? Is the county financially healthier than its peers?

If you have identified warning trends, the next question is what to do about it.

The list below identifies possible financial strategies. Based on your analysis above, which strategy would you recommend to the county for what reasons? Choose at least two strategies and explain how those strategies can improve financial condition.

Shift resources to safe investments

Invest in capital equipment, facilities

Invest in revenue-generating programs, services

Restructure/Refinance long-term liabilities

Cut expenditures

Outsource key public services

Short-term borrowing to smooth cash-flow

Sell or lease capital assets

Miami-Dade County
Data Element Description
Cash and Investments – General fund
Current Liabilities – General fund
Unassigned/Assigned FB – General fund
Total Expenditures – General fund
Excess Rev Over (Under) Exp – Governmental funds
Total Revenues – Governmental funds
Long-Term Debt – Governmental funds (Constant)
Population
2017
228,342,000
82,016,000
258,396,000
1,759,729,000
648,659,000
4,322,616,000
4,563,186,000
2,743,095
2017
Cash ratio
Unassigned/Assigned fund balance ratio
Operating ratio*
Long-term debt ratio
Cash ratio
1.20
1.00
0.80
0.60
0.40
0.20
0.00
2017
2018
2019
Cash ratio
Operating ratio*
1.20
1.00
0.80
0.60
2020
2021
0.60
0.40
0.20
2017
2018
2019
Operating ratio*
2020
2021
2018
262,150,000
90,638,000
329,919,000
1,838,883,000
774,065,000
4,696,786,000
4,694,341,000
2,779,322
2019
273,302,000
99,137,000
365,791,000
1,997,060,000
789,978,000
5,157,206,000
4,928,373,000
2,812,130
2018
2020
459,229,000
110,829,000
362,420,000
2,050,948,000
348,916,000
5,174,295,000
5,251,785,000
2,832,794
2019
2021
221,547,000
120,830,000
399,275,000
5,255,187,000
663,976,000
5,919,163,000
5,315,875,196
2,867,312
2020
2021
Unassigned/Assigned fund balance ratio
1.20
1.00
0.80
0.60
0.40
0.20
0.00
2017
2018
2019
2020
Unassigned/Assigned fund balance ratio
Long-term debt ratio
$1
$1
$1
$1
2021
$1
$0
$0
$2017
2018
2019
Long-term debt ratio
2020
2021
Benchmark
7.25
0.35
0.66
$
553
2021
2021