Public Budgeting

DETAILED INSTRUCTIONS FOR CUTBACK SCENARIO

Once you have opened “Revenue Adjustment Template” – an Excel Spreadsheet…

1) Go past the first 6 lines. But notice there are two different rates – the first is the reduction percentage (-10%) for all departments except for one, and the 2nd (-5%) for Human Services.

2) The next area is entitled “Arapahoe County 2015 Budget Including All Funds.”

3) Replace F5, which currently says “(your dept)” with your assigned department. Your department is assigned as follows: For student ids ending in 1, Assessor’s Office For student ids ending in 2, Clerk & Recorder’s Office For student ids ending in 3, Community Resources For student ids ending in 4, County Attorney For student ids ending in 5, Finance For student ids ending in 6, Human Resources For student ids ending in 7, Human Services For student ids ending in 8, Information Technology For student ids ending in 9 or 0, Public Works

4) Get data (under the Assignments tab).

5) The .pdf file called “Complete 2015 Adopted Budget” has budget data for this area of the spreadsheet.

6) Adopted budget data can be found on different pages for different departments. For the Assessor’s Office, it’s in the “Budget Summary” table on page 168 (180 of 399). For the Clerk & Recorder’s Office… on page 178 (190 of 399). For Community Resources… on page 188 (200 of 399). For County Attorney’s Office… on page 200 (212 of 399). For Finance… on page 213 (225 of 399). For Human Resources… on page 218 (230 of 399). For Human Services… on page 222 (234 of 399). For Information Technology… on page 228 (240 of 399). For Public Works & Development… on page 243 (255 of 399).

7) Fill in the amounts for all Expenditure Categories present (as many as you have) from the far right column of the Summary Budget Table into B7 through B14 (in the column called “2015 Adopted Budget”).

a. To save keying mistakes, you can highlight only 1 number at a time in the .pdf, copy it, and paste it into the spreadsheet. Warning: If you try 2 or more numbers at a time, Excel will paste it as a text field, not a number. You cannot do anything (add, subtract,…) with text.

b. Do not try to copy a “dash” from the .pdf, which means zero. Just enter 0 in the spreadsheet

8) Total the column (in B15) to check against the .pdf’s total. B15 should be: =sum(B7:B14)

9) Next, locate your department’s General Fund spending by category. These amounts start in Appendix 7, on page 358 (370 of 399). Only Assessor’s Office is on that page. Clerk & Recorder’s Office is on the following page, p. 359 (371 of 399). The rest of the departments are on the next 4 pages – through page 363 (375 of 399). Public Works & Development finishes on the top of page 363, continued from the bottom of the previous page, p. 362 (374 of 399). Enter the amounts from the Expenditure Categories present from the FAR RIGHT column into B19 through B26 in the column called “2015 Adopted Budget.”

10) Total the column (in B27) to check against the .pdf’s total. So B27 is: =sum(B19:B26). Remember – this is the General Fund only, so the total in B27 will USUALLY be less than the total in B15.

a. HINT: The reason that the total in B27 is usually less than the total in B15 is that B15 includes funding from all sources, whereas B27 includes only the money spent from the General Fund. If you want to see where all the funding sources are, check the very next table below the Budget Summary (see #6 above for the page number) called “Budget Summary by Fund.” The totals for B15 and B27 will be equal only if there’s a single fund with a non-zero dollar amount (i.e., the amount from the General Fund matches “Total Expenditures” from the Budget Summary, or from the Social Services Fund in the case of the Human Services Department).

11) Only if your department is Human Services, there was no General Fund spending on the pages listed in steps 9 and 10. You have to go the next page, p. 364 (376 of 399). Under the heading of “Social Services Fund” at the middle of the page, there’s a heading for Human Services. Enter the amounts from the Expenditure Categories present from the far right column into B19 through B26 in the column called “2015 Adopted Budget.” Total the column (in B27) to check against the total from the .pdf. Remember – this is the Social Services Fund only, so the total in B27 will be slightly less than the total in B15.

12) Enter the amounts reduced in C19 through C26 as negative numbers! These amounts are up to you. You can enter a formula or an amount. They are only constrained by two things: 1) amounts can’t be greater than the corresponding amounts in column B (because you can’t end up with negative fund balances when these amounts are added to the ones in column B); and 2) all the amounts have to sum (in C27) to a 10% reduction (in B2--for all departments except Human Services, which is a 5% reduction, shown in B3. So multiply B2 or B3, depending on your department, times B27 to get B28. So B28 is =B27*B2 [or] =B27*B3 (the 2nd one only if you have Human Services).

a. HINT: you’re guaranteed to get the right result by making a pro rata reduction of 10% (or 5% in case you have Human Services). That gives for C19 (for instance): =B19*B2 (or, for Human Services, =B19*B3).

b. You don’t have to arrive at your numbers using the formula in (a) above, it’s just a sure-fire way to come up with correct results if you don’t care where the cuts are.

13) You check your results in C27 by comparing the “Target Reduction” (it should also be a negative number!) in B28 to the sum of your reductions in C27. They should be the same. If they are not the same, change the amounts in C19 through C26.

14) Now add the reductions in C19 through C26 to the full budgeted amounts (Annual Fund and Social Services Fund plus other funds) in B7 through B14 to get the reduced amounts in C7 through C14. For instance, Salaries & Wages in C7 is, =B7+C19 . Continue for the rest of the Expenditure Categories until you have results in all the applicable cells in C7 through C14. You can copy (right-click and then left click on “copy”) from C7 and paste (right-click and then left click on “paste”) into C8 through C14.

15) Sum the new budget amounts C7 through C14 and put the result in C15: =sum(C7:C14). Next, compute the percentage reduction in D7 through D15 (including the total), using a formula quite like year-over-year change. For D7, it’s the following: =(C7-B7)/B7 ; and for the rest (D8 through D15), just copy (right click and then left click on “copy”) from D7, then paste (right click and then left click on “paste”) into D8 through D15.

16) Now you have the information in order to write a one-two page, double-spaced memo with the following sections. First, the standard title “M E M O R A N D U M” across the top. Next, the “To:”; “From:”; “Date:”; and “Subject:” on separate lines. The memo should be to “Department Manager” and from you. The subject should convey that these are findings from analyzing the Arapahoe County 2015 Adopted Budget. Then, I’d like 3 to 4 paragraphs containing the following.

a. First, describe the analysis you did in enough detail that I (as a manager) have a decent idea of what you did, but skipping the gory details. It wouldn’t be out of the way in a normal organization, for the manager to route something like this to other managers who may not be familiar with what you did. I’m not holding folks to two pages, but it should be plenty for this one. This paragraph should describe the purpose of your analysis (interim budget reduction due to revenue shortfall) and define the scope of what you did. The next 2-3 paragraphs should convey the following information:

b. Paragraph two: describe the changes to the spending categories that you’ve chosen to recommend (remember: analysts aren’t the decision makers). Did you apply a flat 10 percent cut to the General Fund (or Social Services Fund) amounts or did you apply some other rationale. What were the changes to the overall budget – you don’t need to say if they’re the same or virtually the same as the changes to the General Fund – but in some cases there are significant sources of funding beyond the fund you reduced. I’d give precedence to the percentage reductions to the overall budget (in D7 through D15), which are more relevant to the level of changes to the department’s budget (including all funding sources) that would be required.

c. Paragraph three: describe the impact of the changes you’ve recommended. For instance, if you’re furloughing or dismissing staff, how many staff is that. You don’t need to say how many pencils the department is doing without if you cut supplies, but you might indicate what some possible impacts may be. You can be a little creative – despite the length of it, the budget lacks the necessary detail to dispute your account.

d. Paragraph four: (optional) you can justify why you believe your recommendations are preferable to another course (for instance, if you chose not to simply pro rate the cut, you could say why you decided against it).