Budget Preparation

A Budgeting Guide for Local Government, 3 rd

edition

Excel Exercise #1

Chapter 8, The budget cycle: Preparation and legislation

Prepared by

Bob Bland, Professor of Public Administration

Skip Krueger, Associate Professor of Public Administration

Patrick Shinkle, Associate Director, Center for Public Management

University of North Texas

Background

This dataset is the line-item budget for a city’s Development Department, which has

responsibility for issuing building permits and inspecting residential and commercial

construction. The goal is to develop a realistic estimate of expenditures and revenues for this

department, and to identify whether or not this department will have revenues that meet

expenditures. This city has a policy goal that development fees will cover operating

expenditures of this department.

The first six columns are the actual expenditures for each line-item for the past six fiscal

years (2007-08 to 2012-13). The last two columns are the adopted budget for 2013-14 and

the revised estimate for this same year. The last column reflects amendments to the adopted

budget that have been approved thus far this year. It represents the best estimate of the year-

end amount of expenditures for each line item.

Task

The assignment is to develop a budget request for FY 2014-15 for this department. For this

exercise, compute the average increase in each line item for the fiscal years reported. To

compute percent increase, use this formula:

%∆ = Current year – Past year Past year

If you know how to apply this formula to an Excel spreadsheet, proceed to the dataset by

opening Excel Exercise #1 (http://bookstore.icma.org/A_Budgeting_Guide_Teaching_Res_P1773C147.cfm) and

compute

(1) six columns with the annual percentage changes for each line item,

(2) the average percent change for all the line items for each year,

(3) a column with the proposed budget assuming a 1.0 percent increase for FY15, and

(4) export the totals to the budget worksheet (Sheet1).

Repeat these steps for the revenue tab and prepare a final budget worksheet. Then answer the

discussion questions at the end of this exercise.

Detailed Excel commands

These instructions are for Microsoft Office Excel 2003 and newer.

Page 1/Excel Exercise #1

A Budgeting Guide for Local Government, 3rd

1. Setting up your toolbar. If the formula bar is not showing, click on Tools, Options, then

check Formula bar or display the toolbar through the Options Menu.

2. Excel is divided into numbered rows (on left side) and lettered columns (across the top).

Row 5 should be the first row of data for each of the FYs. At the bottom, notice the three

tabs: Exp, Rev, Sheet1 (Expenditures, Revenues, and Budget Worksheet). Make sure the Exp

tab is highlighted in bold. (Clicking on the other two tabs takes you to these spreadsheets.

Try it, but then return to the Exp tab.)

3. Task #1: compute totals by column. Move the cursor to Row 38, Column C. To compute

totals for column C, type in “=sum(C5:C37)”. (Type only the items inside the quote marks.

Don’t include the quote marks.) or use the AutoSum function in the toolbar. SAVE the

worksheet.

4. Task #2: Compute totals for all remaining columns. Place cursor on C38, note the box in

the lower righthand corner of the highlighted cell. Place cursor on this box and drag in a

straight line across row 38 to cell J38. Release cursor. The column totals should then appear.

(If at first they don’t, try dragging your cursor again from C38. You may need to make more than one attempt.) Label this row (B38) TOTAL EXPENDITURES. SAVE the worksheet.

5. Task #3: Compute the annual percent change by line item using this formula:

%∆ = Current year – Past year. Past year

Move the cursor to cell L5 and highlight. Type in “=( D5-C5)/C5”. The cell (L5) should

return. 6.001%. That is, there was a 6.001% increase between FY08 and FY09 in Salaries of

regular employees. SAVE the worksheet.

6. Task #4: Compute annual percent change for all other rows in column L. Move cursor to

L5, drag lower right corner downward to row 38, release cursor. The columns should fill in

with the percentages for each line item. SAVE the worksheet.

7. Task #5: Compute the annual percent change for all other fiscal years. Highlight L5 to

L38. Place cursor on L38, note the box in the lower righthand corner of the highlighted cell.

Place cursor on this box and drag in a straight line across row 38 to cell Q38. Release cursor.

The column calculations should then appear. (If at first they don’t, try dragging your cursor again from L38. You may need to make more than one attempt.) The remaining block of

cells should fill in with percent changes for each of the fiscal years. (Row 37 will have an

error message – DIV/0!. Delete these error messages since the cells are empty.) SAVE the worksheet.

8. Task #6: Correct column Q by using Revised estimate (Column J). Column Q uses the

original adopted budget in computing the change from FY12 to FY13. You may want to use

the Revised budget (Column J) as a more accurate indicator of the percent change.

Recompute Column Q using the instructions in Task #3 but substituting J5 and H5. The

Page 2/Excel Exercise #1

A Budgeting Guide for Local Government, 3rd

equation should be “=(J5-H5)/H5”.Then drag lower right column downward to compute

revised percentages for all of column Q. SAVE the worksheet.

9. Task #7: Compute average percent change for the past six fiscal years. Place cursor in R5

and type “=average(L5:Q5)” . Drag the cursor down the column to complete the average percent change for each line item. Label column at top “Avg % Chg”. SAVE the worksheet.

10. Task #8. Compute proposed line item budget for 2014-15. At this point, the budget

analyst has several pieces of information that are helpful in formulating a budget request for

FY15. The average percent change (column R) may overstate the needs for FY15 since the

percentage changes for most line items having been trending downward between FY08 and

FY14. At this point, the analyst will need to exercise judgment in deciding what factor to use

in estimating FY15 expenditures. For the present purpose, assume expenditures will increase

by 1.0 percent in FY15. Place cursor in cell S5. Type “=(J5*1.01)”. The 1.01 factor increases column J by itself (1.00) plus 1 percent (.01). Click and drag downward to

complete the cells in column S. At the top, label this column “Proposed budget 2014-15”. SAVE the worksheet.

11. Task #9. Transfer expenditure totals to Budget Worksheet. Open the Budget Worksheet

tab at the bottom of the page (Sheet1). Notice that it has three lines and three columns. One

nice feature of Excel is that work in one tab can be updated automatically in another tab. For

example, if you changed the factor for increasingly line items in the proposed budget to 1.5

percent, S38 (total proposed expenditures) would change and the updated number would be

reflected in D6 of the Budget Worksheet. To create this feature, use the following link

command: “=Exp!S38”. This command tells Excel to look at cell S38 on the Exp tab, and put

that value in this new cell. Whenever the value in S38 in the Exp tab is updated (because the

formulas were updated on that spreadsheet), the number in D6 will be updated automatically.

A similar command can be used for B6 and C6 by changing the cell reference in the Exp tab.

SAVE

12. Task #10. Repeat the forgoing tasks for the Revenue tab. Tabulate the total revenue for

Adopted, Revised, and Proposed columns and link it to the Budget worksheet. Then compute

the Net line in the Budget Worksheet. SAVE

Discussion questions

1. Did the department meet its goal of generating revenues sufficient to cover direct

expenditures for each of the years for which data are available?

2. Based on the discussion in Chapter 8, did the department adopt a “balanced budget” for the current year? What issues might arise when defining a balanced budget? Are

the department’s services public or private goods? Discuss the reasons for your choice.

3. Given your preliminary projections, will the department be able to adopt a balanced

budget next year? If not, what options does the department have to ensure that

revenues meet expenditures?

4. If target-base budgeting was used by this city, what target level of funding would you

recommend for FY14-15 for this department? For the expenditure tab on the Excel

Page 3/Excel Exercise #1

A Budgeting Guide for Local Government, 3rd

spreadsheet, add a column for current services using the discussion in Chapter 7.

5. Discuss the key budget linkages for this department using the discussion at the end of

Chapter 7.

Page 4/Excel Exercise #1

A Budgeting Guide for Local Government, 3rd