+1 (208) 254-6996 [email protected]
Select Page

A Budgeting Guide for Local Government, 3 rd

edition

Don't use plagiarized sources. Get Your Custom Essay on
A Budgeting Guide for Local Government, 3 rd
Just from \$13/Page

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.

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.

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

Order your essay today and save 10% with the discount code ESSAYHELP