You're the budget analyst who's tasked with doing a "sanity check" on Howard County's 2023 proposed budget using a "baseline" of previous budgets. What we're doing is to "forecast" from the past into the present. Using forecasts to update/refresh previous years gives budget examiners (us) independent estimates of spending levels to expect in 2023: to compare with the budget they came up with.Memo format ("M E M O R A N D U M" across the top, then "To:" addressed to the "Department Manager" and, finally, "From:" (you), "Date:" and "Subject:" on separate lines. If you have a department that uses a lot of spending categories, it's pretty difficult, because you'll want to address each with at least a mention. Be sure to include the following elements: a. Context. A description of the purpose of the analysis (HINT: it's checking the 2023 budget, rather than making projections, which only serve as the means or method you're using). b. Method. A description of the process you're using to do the comparison or review of the 2023 budget. Here's where it's appropriate to talk about projections. Recall the reason for the projections: NOT to showcase a fancy technique. The problem with comparing old budgets is the comparisons will be invalid due to being dated. The reason you have to use a projection is that you can't compare 4-year-old and 3-year-old, etc. numbers against current numbers (like "apples to oranges"). So the projections, using 3 distinct methods, bring these old budgets up to date so that we're making apples-to-apples comparisons. Another important part of the methodology to remember is that we're using ranges (and not a single point) as the yardstick for comparison. Reflect on what a range does for us... if you have a single point, the comparison is hardly ever going to yield a spot-on match, so it's going to necessitate establishing an interval around the projection, which would also produce a range! So the range we're developing using low and high projections makes possible the comparisons described in the next section. c. Budget assessment. Are there discrepancies in the comparisons between the 2023 Budget amounts vs. projected ranges (low projection to high projection) that flag certain spending categories (or the total budget) for follow-up inquiries? There are two outcomes: 1) a spending category (or total budget) is validated by the projections, when the 2023 Budget amount is between low and high projections or very close to one of the projections, or 2) it's not validated. It's not validated when the 2023 Budget amount falls outside the projected range and far away from both low and high projections. The second case, when 2023 Proposed Budget amounts have discrepancies with the projected range, signifies a mismatch with the historical pattern. No need to infer, intuit, or guess what caused the 2023 Budget to differ with the historical pattern (that is, the projected range)! There are plenty of logical reasons for the 2023 Budget to diverge from prior-year budgets, even after they are updated to form projections. All kinds of factors can send spending in a new direction, up or down. It's not your concern; just identify what's questionable.

d. Method assessment. Did our assumptions about trending provide a sound basis for the methods we picked for first 2 categories (Personnel Costs and Contractual Services) versus the methods we picked for the remaining categories? It's an important question, because we're using the projections as the yardstick to check out the 2023 Budget amounts. This question addresses a problem at the heart of the analysis, which is there's no way to check whether the projected ranges are reasonable. A version of the perennial issue: Who checks the checkers? It's illogical to say the projected ranges are valid because they happen to be close to the amounts from the 2023 Budget. That would be circular reasoning! Circular reasoning is... I'm pretty tall because I'm the same height as my dad; he must be tall too, because he's as tall as I am. (I know it sounds stupid, but a lot of circular reasoning is fixed up so that it's not quite as obvious. And that's when it's dangerous, given that underneath it's just as illogical.) Here's one way to confirm that we made a valid choice of methods for spending categories assumed to trend. Looking for evidence of steadily rising budgets for the compensation categories-- which is what we assumed when we decided to apply trend-susceptible methods to these categories--points us to the year-over-year changes (cols. B, C, and D) in rows 16 and 17. If those percentage changes for salaries and benefits are positive and pretty consistent, that's what we assumed. If they include both increases and decreases or increases that fluctuate widely, they're at odds with our assumption. Even consistent trends will be subject to minor fluctuations, as long as the overall picture shows a tendency toward steady change. However, if the pattern of year- over-year changes is bumpier, that raises doubts about the trending assumption. e. Reflection. As a process enhancement, did the methods of projecting the 2023 budget produce a workable baseline to compare with what the budget developers produced, the 2023 Budget? If so, this method can provide a comparison to use for future budgets to identify a particular department whose entire request or a few specific categories are "out of whack." As I mentioned, it's the idea of having something to compare with: a baseline. If it worked out reasonably well, these projections provide some guidelines to alert us when budgeted amounts are too high or too low. However, it's possible that for your department it just didn't work out, for a variety of reasons, such as wild variability in historical budgets. Keys for doing this assignment:

Here are the main things to consider, so you aren't lost in the "forest" by getting overwhelmed by the "trees":

Load the data from the 2019-2022 budgets into rows 4-11 & columns B-E.

Use Forecast function to refresh 2023 baseline (all categories) via regression in F4-F10.

Use "constant % change" formula to refresh baseline another way for Personnel Costs & Contractual Services in F16, F17.

Use "moving average" formula to refresh baseline another way for the remaining categories' spending in E21-E25.

Use Min (in C32-C38) & Max (in D32-D38) functions to produce low, high projections.

Compare 2023 budget (B32-B39) with low (% in E32-E39) and high projections (% in F32-F39).

You have 2 main things to do:

1) Check the budget amounts actually adopted for 2023by spending category and for the total budget (see row 39)based on the low (in C32-C39) and high (in D32-D39) projections. When it's helpful (when the 2023 budget is outside the projected range) use % differences in E32-E39 (2023 budget - low projection)/low projection OR F32-F39 (2023 budget - high projection)/ high projection. (I said "OR" because you use whichever end of the range is closer.)

2) Assess whether trending assumptions were correctremember we expected compensation-based (the first 2) categories to trend. So check out B16 through D16 and B17 through D17 to see if those year-over-year % changes seem to be following a consistent trend. You can also (optional) "eyeball" any other anomalies, say, non-compensation categories seeming to trend.

BASELINE REFRESH 2ND HAY (constant percentage change)-- Technology and Communication Services BASELINE REFRESH 2WD WAY (3-year moving average)-- Technology and Communication Services 3-yr average 202022ALSO 20 -Commitment Summary" [spending category] for 2019-21 2023 Projection 21 Supplies and Materials $2,012,647$1,650,327 22 Capital Outlay 23 Debt Service 24 Expense Dther Dperating Transters 26 27 28 HOWARD CDUNTY 2023 BUDGET CDHPARISON WITH PROJECTED RANGE-- Technology and Communication Services 30