Public Budgeting forcasting

INSTRUCTIONS FOR COMPLETING FORECASTING ASSIGNMENT

You’re a budget analyst who’s tasked with doing a “sanity check” on Arapahoe County’s 2018 adopted budget. In doing so, you’ll need to think about the nature of forecasting. What we are doing here, as budget examiners, is to build projections using the previous years to develop our own independent estimates of what to expect for the 2018 budget.

This type of analysis would usually be done on the front end as part of budget formulation and is logically prior to budget adoption. So we have to use “make believe” to get past the timing being backwards.

By developing the baseline, this assignment demonstrates a cardinal rule of analysis: it is easier to compare two items than to examine a single item on its own merits. In the latter case, as long as the folks producing the budget (or any set of numbers) have been careful to maintain internal consistency, then we’re left, as analysts, in the position of reviewing specific assumptions, checking the math, etc. So highlighting the differences with the baseline, composed of amounts the analyst derived independently (using projections), is a time-honored way to examine budgets.

An important distinction is that we’re going to look at two types of costs: 1) those that we expect to follow a trend and 2) those that vary without any apparent direction (no idea what to expect). That’s not an absolutely true fact; rather it’s an assumption, an assumption you’ll need to address as part of your memo (more below).

The assumption about which spending categories that we expect to take a particular direction (usually, up) over time, as opposed to wandering about without any pattern, is pretty simple. Both compensation categories—“Salary and Wages” and “Benefits”—are personnel-related costs, which we would expect to trend up over time, as salaries rise and benefits follow in turn. For these compensation categories we use the two (2) trend-friendly forecasting methods--based on the mere assumption of looking for a trend-- regression and constant % rate of change .

All of the other categories, beginning with Supplies, Services & Other, etc., will be treated as if there’s no trend. For those non-compensation categories--again, based on the mere assumption of pattern-less variation--we use the trend-proof method moving average to balance the regression . Often, the moving average projection stretches out the range between minimum and maximum projections because one of them ( regression ) will follow an apparent trend and the other ( moving average ) will ignore it. There may not be an apparent trend for Supplies, Services & Other, etc. because (recall the assumption is that there’s no trend) it’s by far an unexpected pattern. But in that unexpected case where the prior-year budgeted amounts increase or decrease consistently, the projected range will be protected from too aggressively following the trend, because at least one end of the range is based on ignoring the sequence of the historical data (sequencing of data is what produces an apparent trend).

You have the usual spreadsheet template and detailed instructions to follow for doing the computations and writing the memo. Here are the main steps accomplished by the detailed instructions below to get your results, so you aren’t lost in the "forest" by getting overwhelmed by the "trees":

· Load the data from the 2014-2017 budgets into rows 4-11 & columns B-E.

· Use Forecast function to project 2018 spending (all categories) via regression in F4–F11.

· Use constant % rate of change formula to project salary & benefits spending in F17, F18.

· Use moving average to project non-compensation categories’ 2018 spending in E22-E27.

· Use Min (in C34-C41) & Max (in D34-D41) functions to produce low, high projections.

· Compare the budget amounts actually adopted for 2018—by spending category and for the total budget (see row 42)—based on the low (in C34-C42) and high (in D34-D42) projections. Use % differences in E34-E42 (2018 budget – low projection)/low projection and F34-F42 (2018 budget – high projection)/ high projection to determine where the differences may not be too alarming (for example, single-digit percentages).

DETAILED INSTRUCTIONS

1) At the top of the spreadsheet template is an area entitled “Arapahoe Cty Budget Projection 1st Way (Regression).”

2) Replace E1, which says “(your dept)” with your assigned department--same one as Cutback.

3) Next, you’ll fill in 4 years of budgets for the 8 categories in columns B through E, rows 4-11.

4) Find the budgets for 2014, 2015, 2016, and 2017 in the attachments.

5) The .pdf files have budget data for this area of the spreadsheet.

6) For each column, you have to go to a different .pdf file to get the data. For 2014 Adopted Budget, which is in which is in column E, open Complete 2014 Adopted Budget. Repeat for years 2015, 2016, and 2017.

7) In each of the .pdf’s, you’ll find Summary Budget tables on the following pages

Department	2014 Adopted Budget	2015 Adopted Budget	2016 Adopted Budget	2017 Adopted Budget
Assessor’s Office	p. 167 (179 of 397)	p. 168 (180 of 399)	p. 220 (232 of 415)	p. 218 (230 of 407)
Clerk & Recorder’s Office	p. 177 (189 of 397)	p. 178 (190 of 399)	p. 230 (242 of 415)	p. 228 (240 of 407)
Community Resources	p. 187 (199 of 397)	p. 188 (200 of 399)	p. 240 (252 of 415)	p. 238 (250 of 407)
County Attorney	p. 198 (210 of 397)	p. 200 (212 of 399)	p. 250 (262 of 415)	p. 247 (259 of 407)
Finance	p. 211 (223 of 397)	p. 213 (225 of 399)	p. 264 (276 of 415)	p. 265 (277 of 407)
Human Resources	p. 216 (228 of 397)	p. 218 (230 of 399)	p. 269 (281 of 415)	p. 269 (281 of 407)
Human Services	p. 220 (232 of 397)	p. 222 (234 of 399)	p. 273 (285 of 415)	p. 273 (285 of 407)
Information Technology	p. 226 (238 of 397)	p. 228 (240 of 399)	p. 278 (290 of 415)	p. 278 (290 of 407)
Public Works & Development	p. 241 (253 of 397)	p. 243 (255 of 399)	p. 292 (304 of 415)	p. 290 (302 of 407)

8) You will find a corresponding row for each “Expenditure Category” under the “Budget Summary” table in the .pdf in A4-A11 of your spreadsheet. IMPORTANT: All departments don’t have all categories. Only the spending categories that apply to each department are listed in the budgets. So, if your department doesn’t list a particular category (for example, “Community Programs”), enter 0. Also IMPORTANT: Make sure to skip “Staff – FTE” row.

9) Enter the right-most column of data from the .pdf (labeled “201x Adopted Budget”) into the spreadsheet in the column (either B, C, D, or E) having the same year.

10) 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. HINT: watch out for any numbers that automatically align to the left (which text does) rather than automatically aligning to the right (which numbers do).

11) In row 12, across from where it says “Total” in A12, we’re going to add a total to check our copying from the .pdf just as we did in the Cutback Scenario. For example B12 should be: =sum(B4:B11)and so forth for C12, D12, and E12.

12) We’re ready for the first projection of 2018 spending! Let’s start with Salary & Wages in F4.

13) There are two ways to approach this…. a) Either pick the Function from the menu (which you get by picking it from the drop-down under “Formulas” or keying Alt-I F). Once the window for functions comes up, under “select a category” pick the drop-down for “All” (it’ll probably say “Most Recently Used” initially, but “Forecast” probably isn’t one of your most recently used functions). Now scroll down the list and pick “Forecast” and click on the “OK” button. Where it says “x” click on “2018” (that’s F3). Now hit tab to take you to “known_y’s” and then drag the mouse across the budget numbers for 2014-2017 (that’s B4 thru E4). Then hit tab to take you to “known_x’s” and then drag the mouse across the years for 2014-2017 (that’s B3 thru E3). If you’ve done this all correctly, you should see out to the right of the cell ranges the actual results, for example, 2018 for the first line, {2014, 2015,…} for the last line, and your actual budget amounts for the middle line. Once you’ve got this, click on the “OK” button. You should see a dollar amount projected for 2018 in F4 and it should be in line with the amounts from 2014 thru 2017 (in B4 thru E4). That is, if amounts are mainly increasing from B4 to E4, then you should see F4 > E4. b) The other way to enter this function is just to start entering it. So key =forecast( and once you enter the left parenthesis, Excel figures out that you’re using a function and supplies the instructions for the rest of it. You should see just below the cell “FORECAST(x, known_y’s, known_x’s)” which is telling you to click on F3 or enter “F3” or enter 2018 and, whether you clicked or keyed, key a comma, which tells Excel you’re ready for the next part, which is dragging the mouse across the budget numbers for 2014-2017 (that’s B4 thru E4, which you can enter as B4:E4) and keying another comma, and last dragging the mouse across the years for 2014-2017 (that’s B3 thru E3), keying the closing parenthesis, and hitting enter/return.

14) Repeat entering the “Forecast” function for F5 thru F11. If you get a “#DIV/0! and you’ve got no data for that spending category, don’t worry about it. Just delete the formula and leave the field empty. Or you can enter zeros wherever there are blank fields in column B thru E. IMPORTANT: DO NOT COPY FROM F4 & PASTE INTO F5…. By copying the correct formula from F4 into F5 and below, you’ll be shifting the row references for the Years in row 3. That means that for the formula in F5, you’re no longer referencing 2018 for the first term (F3), but are now referring to the dollar amount projected in F4, which makes absolutely no sense. Also, the last term is off, since it also references dollar amounts rather than the years 2014 to 2017. In order to safely copy and paste from F4 to F5 and so forth, you first have to anchor the rows that you don’t wish to change. So the formula in F4 has to be =forecast(F$3,B4:E4,B$3:E$3). With the row references “locked in” to the first and last terms, you can copy and paste with only the middle term shifting its row reference (which is what you want, so that it picks up the new “known y’s” in row 5 and so on), while the first and last terms will remain pointed at the years in row 3. You can also key in the years manually, so that the first term in your FOREFAST function is 2018 and the last term is {2014,2015,2016,2017}) ( I DON’T RECOMMEND DOING IT THAT WAY—the syntax must be perfect: comma, then opening bracket, then year,year,year,year, then closing bracket and finally right parenthesis to finish off the FORECAST function.

15) We’re done with the projection using the first method, which is a linear regression! Move down to an area of the sheet labeled “Budget Projection 2nd Way (Compounded annual % change)” where we’ll compute some trend-related data for the personnel categories (Salaries and Wages and Benefits) in columns B thru F in rows 17 and 18.

16) Now, we’re going to do a year-over-year change for the amounts we entered into B4 thru E5. These results are not used in the projections, but to “eyeball” (check out) the results we’re going to get in the next step. This works because for small proportional changes, the constant compounded rate of change will fall somewhere in the middle of the average annual changes. The entry for “Salaries and Wages” in B17 should be (Salaries and Wages 2015 minus Salaries and Wages 2014) divided by Salaries and Wages 2014: =(C4-B4)/B4 . Once you get the result for B17, for the rest of the year-over-year changes (B17 thru D18), just copy (right click and then left click on “copy”) from B17, then paste (right click and then left click on “paste”) into C17, and repeat to copy from C17 and paste into D17.

17) Next, we’re going to figure a compound annual change to get a constant percentage increase or decrease from 2014 to 2017. The compound change computes from the first year to the last year, ignoring the middle years (2015 and 2016). To figure the compound annual rate of change from 2014 to 2017, take the cube-root—the same idea as a square root, except it’s the same amount multiplied 3 times together to get a number, so the cube root of 64 is 4 because 4*4*4=64—of (Salary and Wages 2017 divided by Salary and Wages 2014), then subtract 1. The formula for E17 is =(E4/B4)^(1/3) - 1 . You will note we didn’t do anything with B17 thru D17 – they’re only there to give you a benchmark for the results you just computed. Notice the annual rate of compound change is somewhere in the midst of the percentages for 2014-15, 2015-16, and 2016-17, the year-over-year rates of change you computed in B17 through D17 (let me know if it’s not). Then, you can copy from E17 and paste into E18. Finally, for the “2018 Projection (using compounded annual % change)” in F17, multiply last year’s budget (2017) times 1 + E17 to extend the rate of change you just figured for one more year (that’s how we project using compounded rate of change). So for F17, 2018 Salaries, it’s =E4 * (1 + E17). Copy F17 and paste into F18 to get 2018 Projection for Benefits category.

18) If you want to check the computation you just did, as well as showing how compound rates of change work, you can do the following test. If you don’t care or understand already how the formula works, go on to the next step. To check your annual rate of compound change, find an empty cell (like F19). Multiply the Salary and Wages 2014 (in B4) by 1 plus the rate we computed in 17 (1 + E17) three times: =B4*(1+E17)*(1+E17)*(1+E17). You should get the Salary and Wages 2017 (in E4). The reason for multiplying three times is that in E17 we figured the ANNUAL rate of change (that’s why we used the cube root – to turn a 3-year rate of change into a one year rate change – it’s the right way whereas averaging rates of change can create problems). To see the effects of averaging rates of change, just try averaging a 50% increase and a 50% decrease, you’ll get zero!! But work it out (like a 50% markup from wholesale followed by “50% off” sale in retail) and you’ll get 75% of what you started with.

19) Now go to “Budget Projection 2nd Way (3-year moving average)” where we’ll work (in D22 thru F27) with the spending categories that we don’t expect steady change in any direction (the non-personnel categories). We compute these using the moving average, which says the next year equals an average of the last x years (that’s a 3-year moving average).

20) We’re now going to make projections using the moving average (aka the 3-year average) for all of the non-personnel spending categories…. a) In D22, average the budgets over the 3 years (2014-2016) by adding B6 through D6, then dividing by 3, for example, D22 has: =sum(B6:D6)/3. You can also use the AVERAGE function: =average(B6:D6). Either formula goes into cell D22. We’re NOT going to use this to project the budget, but we want it for comparison. Then copy from D22 and paste into D23 through D27. b) For E22, compute the moving average (the 3-year average) for 2015-2017. The average you get for the most recent years (2015-2017) IS the projection for the next year, 2018, using the moving average method. This just repeats the instructions for 20(a) above, merely shifted one column to the right. So for E22, average the budgets over the 3 years (2015-2017) by adding C6 through E6, then dividing by 3, for example, E22 has: =sum(C6:E6)/3. You can also use the AVERAGE function: =average(C6:E6). Either formula goes into cell E22. That is the projection for 2018. Then copy from E22 and paste into E23 through E27.

21) Since we used two different projection methods to develop a baseline for the 2018 Budget: the first method in rows 4 to 11 and the second in rows 17 and 18 and 22 to 27. We’re going to combine them to try to gain a little more accuracy. This process is called “triangulation” and works on the same principal as the depth perception in our vision that allows Adam Jones to hit the slider or Venus Williams to return her sister’s serve.

22) In the area of the spreadsheet labeled “Arapahoe Cty 2018 Budget Comparison Combining 2 Ways” the 2018 Lower Projection is going to be the lower of the two projections we generated. We compute this using the MIN function (short for minimum) as follows for cell C34: =min(F4,F17). Just copy and paste from C34 to C35. For column D, the 2018 Higher Projection, do the same thing except with the MAX function, for example, cell D34 has: =max(F4,F17). Copy D34 and paste in D35. You have to do the non-personnel spending categories separately because there’s another header row in between row 18 and row 22 that goofs up the copy and paste. So key C36: =min(F6,E22) and copy from C36 and paste into C36 thru C41. Key D36: =max(F6,E22) and copy from D36 and paste into D36 thru D41. The total in C42 has: =sum(C34:C41) and the total in D42 is=sum(D34:D41).

23) Now key the 2018 Budget data, which we’re going to compare against the projections we set up. Open the .pdf called “Complete 2018 Adopted Budget” and find the amounts for your department in... For the Assessor’s Office, it’s in the “Budget Summary” table on page 220 (232 of 408). For the Clerk & Recorder’s Office… on page 231 (243 of 408). For Community Resources… on page 241 (253 of 408). For County Attorney’s Office… on page 250 (262 of 408). For Finance… on page 263 (275 of 408). For Human Resources… on page 268 (280 of 408). For Human Services… on page 272 (284 of 408). For Information Technology… on page 278 (290 of 408). For Public Works & Development… on page 288 (300 of 408).

24) Put the 2018 Adopted Budget amounts from above into B34 thru B41. Add the sum formula to B42, which is =sum(B34:B41), and check it against the total from the Budget Summary table. Then, we just use our change formula to find the percentage difference between the Adopted Budget and LOWER independent estimate we developed, for example E34 has: =(B34-C34)/C34. Copy from E34 and paste into E35 thru E42. Then, repeat for column F using the Higher Projection instead, for example F34 has: =(B34-D34)/D34 . Copy F34 and paste into F35 through F42.

25) The whole reason for applying this type of analysis, as a budget examiner, is to highlight potential areas to question the agency about their budget submission. You have 2 main things to think about:

A) Check the budget amounts actually adopted for 2018—by spending category and for the total budget (see row 42)—based on the low (in C34-C42) and high (in D34-D42) projections. Use % differences in E34-E42 (2018 budget – low projection)/low projection and F34-F42 (2018 budget – high projection)/ high projection.

B) Assess whether trending assumptions were correct—remember we expected the compensation categories to trend (check out B17 through D17 and B18 through D18 to see if those year-over-year % changes seem to be following a consistent trend) and the non-compensation categories not to trend.

26) Write using the usual 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. It may not be possible to stay within 2 pages. 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 2018 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 2018 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. (If you were doing a household budget, you wouldn’t start with your receipts from 4 years ago.) 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 2018 Adopted Budget amounts and 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 2018 Adopted 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 2018 Adopted Budget amount falls outside the projected range and far away from both low and high projections. The second case, when Adopted 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 2018 Adopted Budget to differ with the historical pattern (that is, the projected range)! There are plenty of logical reasons for the 2018 Adopted 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 used for compensation categories (Salaries and Benefits) versus the methods used for non-compensation categories (Supplies, Services & Other, etc.)? It’s an important question, because we’re using the projections as the yardstick to check out the 2018 Adopted 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 2018 adopted 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 17 and 18. 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 2018 budget produce a workable baseline to compare with what the budget developers produced, the 2018 Adopted 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.

GOOD LUCK, EVERYONE!