Question
You will do computations (per instructions provided) in Excel, which involve adjusting amounts within budget categories in response to a budget cutback scenario. Next, write
You will do computations (per instructions provided) in Excel, which involve adjusting amounts within budget categories in response to a budget cutback scenario.
Next, write the memo, which should address the following topics:
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 wouldnt 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.
Next, describe the changes to the spending categories that youve recommended (remember: analysts arent the decision makers). Did you apply a flat 10 percent cut to the General Fund amounts or did you apply some other rationale. What were the changes to the overall budget you dont need to say if theyre 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. Id give precedence to the percentage reductions to the overall budget, which are more relevant to the level of changes to the departments budget (including all funding sources) that would be required.
Finally, describe the impact of the changes youve recommended. For instance, if youre furloughing or dismissing staff, how many staff is that. You dont 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.
Resources for the Assignment
DETAILED INSTRUCTIONS FOR CUTBACK SCENARIO Once you have opened Revenue Adjustment Template 2023 an Excel Spreadsheet... 1) Skip the first 4 rows. 2) In row 5, youll find an area entitled Howard County 2023 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 0 or 1, Police For student ids ending in 2, Planning and Zoning For student ids ending in 3, Public Works For student ids ending in 4, Recreation & Parks For student ids ending in 5, Community Resources & Services For student ids ending in 6, Transportation Services For student ids ending in 7, County Administration For student ids ending in 8, Finance For student ids ending in 9, Technology & Communication Services. 4) Get data (under the Assignments tab). 5) The .pdf file called Howard County FY23 Operating Budget has budget data for this area of the spreadsheet. 6) Adopted budget data can be found on different pages for different departments. For Police look in Expenditures by Commitment Summary on page 127 (151 of 436). For Planning and Zoning... on page 154 (178 of 436). For Public Works... on page 163 (187 of 436). For Recreation & Parks... on page 190 (214 of 436). For Community Resources & Services... on page 199 (223 of 436). For Transportation Services... on page 209 (233 of 436). For County Administration... on page 297 (321 of 436). For Finance... on page 305 (329 of 436). For Technology & Communication Services... on page 329 (353 of 436). 7) Fill in the amounts for B7 through B13 for all Expenditure Categories that your department has. Only a few departments have all 7 categories, so check the .pdf under Commitment Summary Item to make sure the names match column A of the spreadsheet (A7 through A13). Also, make sure to use the amounts in the column under FY 2023 Proposed: a. For example, the first category for all the departments is Personnel Costs (just below the heading entitled Commitment Summary Item). Skip past the first 3 columns of figures--FY 2021 Actual, FY 2022 Approved, and FY 2022 Estimated--to find the next (4th) column of figures: FY 2023 Approved. To save keying mistakes, select the amount in the row labeled Personnel Costs from the .pdf, (the whole row will be bold), then copy and paste it into the spreadsheet in cell B7. Warning: If you include spaces or 2 or more numbers at a time, Excel will paste it as text, not a number. b. Continue with the bold amounts from the .pdf for rows that match A8 through A13. Start with Contractual Services (paste into B8), Supplies and Materials(paste into B9), and the other bold amounts below them. If theres no category in the .pdf matching A10, A11, A12, or A13, enter 0 in column B of the spreadsheet. 8) Total the column (in B14) to check against the .pdfs total. B14 should be: =sum(B7:B13). a. Your total in B14 should match the TOTAL row (bottom of the table) in the .pdf. 9) Next, locate your departments General Fund spending by category. These General Fund amounts will go into B18 through B24 in the column called 2023 General Fund Amount. a. The General Fund amounts are in the row entitled General Fund just follow the alphabetical order to the Gs below the amounts you found under instruction #7. b. For example, for Personnel Costs find the bold row where you got the amount to enter in B7 and look down the rows below it until you find the row which will be labeled General Fund (indented and not bold). Skip (just as you did in instruction #7) past the first 3 columns of figures--FY 2021 Actual, FY 2022 Approved, and FY 2022 Estimated--to find the next (4th) column of figures: FY 2023 Approved. Select the amount in the 2nd row of the .pdf (row below Personnel Costs), then copy and paste it into the spreadsheet in cell B18. c. Continue with the General Fund amounts below Contractual Services (paste into B19), below Supplies and Materials (paste into B20), and below the other ones you used for instruction #7b. If theres no General Fund amount in the .pdf matching A21, A22, A23, or A24, enter 0 in column B of the spreadsheet. 10) Total the column (in B25) to check against the .pdfs total. So B25 is: =sum(B18:B24) The total for the .pdf will be on the previous page (see Expenditures By Fund/Fund Center), in the row labeled General Fund (in bold). Remember this is the General Fund only, so the total in B25 will be less than the total in B14. a. HINT: The reason that the total in B25 is less than the total in B14 is that B14 includes funding from all sources, whereas B25 includes only the money spent from the General Fund. 11) Enter the amounts reduced in C18 through C24 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 cant be greater than the corresponding amounts in column B (because you cant 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 C25) to a 10% reduction (in B2). So multiply B2 times B25 to get B26. So B26 is =B25*B2. a. HINT: youre guaranteed to get the right result by making a pro rata reduction of 10%. That gives for C18 (for instance): =B18*B2. b. You dont have to arrive at your numbers using the formula in (a) above, its just a sure-fire way to come up with correct results if you dont care where the cuts are. 12) You check your results in C25 by comparing the Target Reduction (it should also be a negative number!) in B27 to the sum of your reductions in C25, that is =sum(C18:C24). They should be the same. If they are not the same, change the amounts in C18 through C24. 13) Now add the reductions in C18 through C24 to the full budgeted amounts in B7 through B13 to get the reduced amounts in C7 through C13. For instance, C7 is, =B7+C18 . Continue for the rest of the Expenditure Categories until you have results in all the applicable cells in C7 through C13. 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 C13.
14) Sum the new budget amounts C7 through C13 and put the result in C14: =sum(C7:C13). Next, compute the percentage reduction in D7 through D14 (including the total), using a formula that divides the year-over-year change by what you started with. For D7, its the following: =(C7-B7)/B7 ; and for the rest (D8 through D14), 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 D14 (even for D14 use the percentage reduction formula(new-old)/oldNOT a summary: doesnt work to sum those percentages). 15) 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 Howard County 2023 Budget. Then, Id 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 wouldnt 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. Im 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 youve chosen to recommend (remember: analysts arent the decision makers). Did you apply a flat 10 percent cut to the General Fund amounts or did you apply some other rationale. What were the changes to the overall budget you dont need to say if theyre 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. Id give precedence to the percentage reductions to the overall budget (in D7 through D14), which are more relevant to the level of changes to the departments budget (including all funding sources) that would be required. c. Paragraph three: describe the impact of the changes youve recommended. For instance, if youre furloughing or dismissing staff, how many staff is that. You dont 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).
Spreadsheet template TEMPLATE FOR REVENUE ADJUSTMENTRevenue adjustment for General Fund -10.0% HOWARD COUNTY 2023 BUDGET INCLUDING ALL FUNDS-- (your dept) Expenditure Category (Commitment summary) 2023 Proposed Budget New Budget Amount % Reduction Personnel Costs Contractual Services Supplies and Materials Capital Outlay Debt Service Expense Other Operating Transfers Total DEPARTMENT'S GENERAL FUND ONLY Expenditure Category (Commitment summary) 2023 General Fund Amount Amount Reduction Personnel Costs Contractual Services Supplies and Materials Capital Outlay Debt Service Expense Other Operating Transfers Total Target Reduction <= equals /|\ ?
Howard County budget Howard County FY23 Operating Budget-1.pdf Download Howard County FY23 Operating Budget-1.pdf
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started