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
