The author of A Budgeting Guide for Local Government, 3rd edition, Robert L. Bland, has prepared a set of Excel exercises for use with the book. If you have questions about any of the exercises, please directly e-mail Dr. Bland. 

Microsoft Excel is a tool widely used by budget analysts. The following Excel exercises introduce readers to the preparation of a departmental budget request and the preparation of revenue forecasts. 

Excel exercises for Chapter 8 introduce readers to preparation of a department budget request and preparation of revenue forecasts.

The Excel exercise for Chapter 9 introduces readers to monitoring budget compliance through executive implementation.

The Excel exercise for Chapter 10 introduces readers to the task of determining the annual debt service requirement for a general obligation bond issue and the impact on the annual cost on the operating budget. 

Excel Exercises (Please read instructions first!)

1. Excel Exercise #1: This Excel exercise requires analyzing the line-item budget for a city’s development department, which has responsibility for issuing building permits and inspecting residential and commercial construction. This city has a policy goal that development fees will cover operating expenditures of this department. The assignment is to develop a budget request for FY 2014–15 for this department and to determine whether this department will have sufficient revenues to meet its expenditures. Download the detailed instructions and data set for this exercise.

2. Excel Exercise #2: This exercise introduces several features in Excel that aid in developing revenue projections using trend analysis and a moving average. The first task involves correcting the historical data on sales tax collections for inflation. The dataset is actual sales tax collections by month from October 1992 through February 2013. The corresponding consumer price index (CPI) for each month is provided. The data can be used to evaluate current-year trends as well as to project revenue for the next budget year. The assignment is to develop a moving average trend for the inflation-adjusted sales tax collections, compute the month-to-month percentage change in collections, and graphically display those percentage changes. Download detailed instructions, data set, and trendline interpretation tutorial for this exercise.

3. Excel Exercise #3: Once the operating budget is approved, the focus is on budget implementation. One of the most important tasks in this third phase of the budget cycle is monitoring actual revenues and expenditures with the purpose of anticipating unfavorable trends. For this Excel exercise, the analysis returns to the line-item budget for a city’s development department. The exercise involves projecting year-end expenditures (1) for each line item, (2) for total departmental expenditures, and (3) for each revenue source, and then comparing trends in actual expenditures and revenues with the totals in the adopted budget. Using these projections, not only can comparisons be made to the adopted and revised budgets, but projected revenues can be compared to projected expenditures to evaluate the potential impact on fund balance. Download detailed instructions and data set for this exercise.

4. Excel Exercise #4: This Excel exercise involves costing out the annual debt service requirements for a GO bond issue and an analysis of the effect of those annual payments on the operating budget. The dataset is actual annual payments of principal and interest for a 20-year bond issue. The data can be used to determine the annual cost of debt service the required tax rate needed to produce the revenue for the debt service fund. Download detailed instructions and data set for this exercise.