DEVELOPMENT EXPENDITURE HISTORY 'ACCOUNT FUND: GENERL [1014014010X-XX-XXXX) ACTUAL ACTUAL ACTUAL ACTUAL ACTUAL ACTUAL ADOPTED REVISED NUMBER EXP EXP EXP EXP EXP EXP BUDGET ESTIMATE 1-11-1110 SALARIES OF REGULAR EMPLOYEES $3,561,200 $3,774,900 $3,963,600 $4,082,500 $4,184,600 $4,268,300 $4,319,500 $4,332,300 17111130 OVERTIME 143,700 152,300 159,900 164,700 168,800 172,200 174,300 174,800 1711-1120 SCHEDULED TEMPORARIES 95,800 101,500 106,600 109,800 112,600 114,800 116,200 116,500 1-11-1140 LONGEVITY PAY 30,700 32,500 34,100 35,100 36,000 36,700 37,200 37300 17111150 BILINGUAL PAY 4,300 4,600 4,800 4,900 5,100 5,200 5,200 5,200 1712-1230 GROUP HEALTH INSURANCE 376,000 398,500 418,500 431,000 441,800 450,600 456,000 457,400 1-12-1240 CONTRIBUTIONS TO EMPLOYEE RETIREMENT 431,100 456,900 479,800 494,200 506,500 516,600 522,800 524,400 27222110 OFFICE SUPPLIES 24,500 26,000 27,300 28,100 28,800 29,400 29,700 29,800 2722-2220 OPERATING SUPPLIES 25,900 27,400 28,800 29,600 30,400 31,000 31,400 31,500 2-22-2240 MOTOR VEHICLE FUEL 16.800 17.800 18,700 19.200 19,700 20.100 20.300 20.400 27232310 MOTOR VEHICLE REPAIR 176,300 186,800 196,200 202,100 207,100 211,200 213,800 214,400 2723-2340 MINOR EQUIPMENT 53,200 56,400 59,200 60,900 62,500 63,700 64,500 64,700 37313110 TRAINING 17,700 18.800 19,700 20.300 20.800 21.200 21.500 21.600 37313120 TUITION REIMBURSEMENT 2,400 2,500 2,700 2,700 2,800 2,900 2,900 2,900 3731-3150 ENGINEERING SERVICES 23,900 25,400 26,700 27,500 28,100 28,700 29,000 29,100 37313150 LEASE OF EQUIPMENT 23,900 25,400 26,700 27,500 28,100 28,700 29,000 29,100 37323210 TRAVEL EXPENSES 19,200 20,300 21,300 22,000 22,500 23,000 23,200 23,300 3732-3220 ADVERTISING AND LEGAL NOTICES 11,500 12,200 12,800 13,200 13,500 13,800 13,900 14,000 37333310 TELEPHONE BASIC LINE CHARGES 76,600 81,200 85,300 87,900 90,000 91,800 92,900 93,200 37333320 TELEPHONELONG DISTANCE 10,100 10,700 11,200 11,500 11,800 12,100 12,200 12,200 3733-3330 CELLULARI'MOBILE PHONES 3,400 3,600 3,700 3,800 3,900 4,000 4,100 4,100 37343410 BUILDING REPAIR AND MANTENANCE 21,100 22,300 23,500 24,200 24,800 25,300 25,600 25,600 37343420 MACHINERY AND EQUIPMENT MAINTENANCE 72,800 77,200 81,000 83,500 85,500 87,300 88,300 88,600 373573510 IT CHARGES 35,400 37,600 39,400 40,600 41,600 42,500 43,000 43,100 37353520 COURT FILING FEES 6,200 6,600 6,900 7,100 7,300 7,500 7,600 7,600 37353560 PROFESSIONAL LICENSES 15,300 16,200 17,100 17,600 18,000 18,400 18,600 18,600 373573570 DUES AND MEMBERSHIPS 1,000 1,000 1,100 1,100 1,100 1,100 1,200 1,200 3735-3580 MESSENGER AND MAIL 5,300 5,600 5,900 6,000 6,200 6,300 6,400 6,400 3-35-3590 OTHER CONTRACTUAL 1400 1,500 1600 1600 1,700 1,700 1,700 1,700 373773710 BUILDING CONSTRUCTION 335,300 355,400 373,100 384,300 394,000 401,800 406,700 407,900 3737-3720 MOTOR VEHICLE PURCHASES 191,600 203,100 213,200 219,600 225,100 229,600 232,400 233,100 3-37-3730 OFFICE EQUIPMENT PURCHASES 71800 76200 80 000 82 400 84 400 86100 87100 87 400 l....._ DEVELOPMENT REVENUE HISTORY FY2007-08 FY2008-09 FY2009-10 FY2010-11 FY2011-12 FY2012-13 FY2013-14 FY2013-14 ACCOUNT ACTUAL ACTUAL ACTUAL ACTUAL ACTUAL ACTUAL ADOPTED REVISED NUMBER FUND: GENERL (101-401-4010-X-XX-XXXX) REV REV REV REV REV REV BUDGET ESTIMATE 2-40-4120 TEMP ENCROACHMEMENTS $137,000 $145,200 $152,400 $157,000 $160,900 $163,800 $165,000 $165,500 2-40-4121 WRECKING/MOVING PMTS $23,200 $24,600 $25,900 $26,600 $27,300 $27,800 $28,000 $28, 100 2-40-4122 HOUSE MOVERS PERMITS $2, 100 $2,200 $2,300 $2,400 $2,400 $2,500 $2.500 $2,500 2-40-4123 CERTIFICATE OF OCCUPANCY $2,500 $2,600 $2,800 $2.900 $2,900 $3,000 $3,000 $3,000 2-40-4124 GAS WELL DRILLING PERMITS $24,900 $26,400 $27,700 $28,500 $29,300 $29,800 $30,000 $30, 100 2-40-4125 PLUMBING BUS REGISTR $51,500 $54,500 $57,300 $59,000 $60,500 $61,600 $62,000 $62,200 2-40-4126 MECH LIC & REG $70,600 $74,800 $78,500 $80,900 $82,900 $84,400 $85,000 $85,200 2-40-4127 SIGN LIC & REG $9, 100 $9,700 $10,200 $10,500 $10,700 $10,900 $11,000 $11,000 2-40-4128 TEMP POWER PERMITS $8,300 $8,800 $9.200 $9,500 $9,800 $9,900 $10,000 $10,000 2-41-4210 BOARD OF ADJUSTMENT $83,700 $88,800 $93,200 $96,000 $98,400 $100,200 $100,900 $101,200 2-41-4220 ZONING COMMISSION FEE $161,900 $171,600 $180, 100 $185,500 $190,200 $193,600 $195,000 $195,500 2-41-4221 BED & BREAKFAST FEES $800 $900 $900 $1,000 $1,000 $1,000 $1,000 $1,000 2-41-4222 DEV PROC MANUAL $12,500 $13,200 $13,900 $14,300 $14,600 $14,900 $15,000 $15,000 2-41-4223 PLANNING COMMISSION $340,300 $360,700 $378,800 $390, 100 $399,900 $407, 100 $410,000 $411, 100 2-41-4224 BUILDING PERMITS $3,486,000 $3,695,200 $3,879,900 $3,996,300 $4,096,200 $4, 170,000 $4,200,000 $4,211,700 2-41-4225 ORDINANCE INSPECTION $199,200 $211,200 $221,700 $228,400 $234, 100 $238,300 $240,000 $240,700 2-40-4129 ELECTRICAL PERMITS $257,300 $272,700 $286,400 $295,000 $302,300 $307,800 $310,000 $310,900 2-41-4226 ANNEXATION FEES $2,900 $3, 100 $3,200 $3,300 $3,400 $3,500 $3,500 $3,500 2-40-4130 BILLBOARD REGISTRATION $6,200 $6,600 $6,900 $7, 100 $7,300 $7,400 $7,500 $7,500 2-42-4310 ENCROACHMENT LETTERS $1,200 $1,300 $1,400 $1,400 $1,500 $1,500 $1,500 $1,500 2-40-4131 MECHANICAL PERMITS $91,300 $96,800 $101,600 $104,700 $107,300 $109,200 $110,000 $110,300 2-40-4132 PLUMBING PERMITS $265,600 $281,500 $295,600 $304,500 $312, 100 $317,700 $320,000 $320,900 2-40-4133 THIRD PARTY-PLUMBING $3,700 $4,000 $4,200 $4, 300 $4,400 $4.500 $4,500 $4,500 2-40-4134 THIRD PARTY-BUILDING $564,400 $598,300 $628,200 $647,000 $663,200 $675, 100 $680,000 $681,900 2-41-4227 EXAMINATION FEES $6,600 $7,000 $7,400 $7,600 $7,800 $7,900 $8.000 $8,000 2-40-4135 SIGN PERMITS $49,800 $52,800 $55,400 $57, 100 $58,500 $59,600 $60,000 $60,200 2-40-4136 RESID REMODEL PERMIT $14,900 $15,800 $16,600 $17, 100 $17,600 $17,900 $18,000 $18,000 2-41-4228 BOARD APPEALS FEE $4,200 $4,400 $4.600 $4, 800 $4,900 $5,000 $5,000 $5,000 2-42-4311 CONST CODE BOOK SALE $20,800 $22,000 $23, 100 $23,800 $24,400 $24,800 $25,000 $25, 100 2-41-4229 MOBILE HOME INSPECTION FEE $2,500 $2,600 $2,800 $2,900 $2,900 $3.000 $3,000 $3,000 2-41-4230 REINSPECTION FEES $5,000 $5,300 $5,500 $5,700 $5,900 $6,000 $6,000 $6,000 2-41-4231 AFTER HOURS FEE $3,700 $4,000 $4,200 $4,300 $4,400 $4,500 $4,500 $4,500 2-42-4312 MISCELLANEOUS REVENUE $10,000 $10,600 $11, 100 $11,400 $11,700 $11,900 $12,000 $12,000 2-42-4313 MAP SALE REVENUE $1,200 $1,300 $1,400 $1,400 $1,500 $1,500 $1,500 $1,500FY 2013-14 FY2013-14 FY2014-15 Adopted Budget Revised Estimate Proposed Budget Total Revenues Total Expenditures #REF! #REF! #REF! Net #REF! #REF! #REF!Defining budget balance The issue of a balanced budget-one the fiscal year and approve supplemental in which current revenues cover current appropriations, but a local government expenditures-has been at the fore of may still conclude the fiscal year with a public debate for several decades as a deficit.) The most stringent balanced bud- result of the continuing deficits incurred get condition specifies that the budget by the federal government. Those must be in balance when it is proposed, annual budget deficits must be funded when it is adopted, and at year-end. somehow, and the federal government Because there are no uniform and en- chooses to fund them with debt-that forceable standards for budgets as there is, by borrowing through the issuance are for financial reports, budgets are of Treasury bills, notes, and bonds. (The much more amenable to manipulation.' Bureau of the Public Debt's website, Local governments facing a budget publicdebt.treas.gov, contains details on crisis may resort to financial sleight of the amount of debt and who owns it.) hand to temporarily achieve budget- Local governments, however, are usually ary balance: they may approve overly required to adopt a balanced budget. optimistic revenue forecasts, shift the last There have been numerous calls for an payday of the fiscal year to the next fiscal amendment to the U.S. Constitution year (thus saving one pay period in the requiring the same discipline from the current budget), accelerate the due date president and Congress. on taxes, defer payments for pensions or There are several points in the life of other obligations, book the sale of assets a budget where it can be balanced (see as revenue before the sale actually oc- figure below). But a balanced budget is curs, defer maintenance expenditures, or elusive. For example, the manager may borrow against a future revenue source.? be required to propose a balanced bud- These actions may temporarily balance get, but the council may have the lati- the budget. However, budget shenan- tude to approve an unbalanced one. Or igans have consequences. Unless the state or local law may require that both local government changes its budget the proposed and approved budgets practices, the imbalance will return with balance but allow deficits at year-end to a vengeance, leaving local leaders with be carried over to the next year. (Coun- fewer options and increasingly more cils typically amend the budget during costly remedies. Executive Accounting and Preparation phase Legislative phase implementation auditing 1. Chief executive 2. Budget 4. Budget 5. Fiscal year must submit approved by amendments must end with a balanced council must be and a balanced budget. balanced supplemental budget. 3. Budget signed appropriations by elected must include chief executive, offsetting after any revenue vetoes, must sources. be balanced. 1 Yilin Hou and Daniel L. Smith, "A Framework for Understanding State Balanced Budget Requirement Systems: Reexamining Distinctive Features and an Operational Definition," Public Budgeting & Finance 26 (Fall 2006): 22-45. 2 Karl Nollenberger, Evaluating Financial Condition, A Handbook for Local Government (Washington, D.C.: ICMA, 2003), 149-152.A Budgeting Guide for Local Government, 3" 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: %A = 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 (Sheet 1). 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.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 Ex 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: %A = 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. Theequation 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 FY 15. Place cursor in cell $5. 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. 1 1. 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, $38 (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!$38". 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 $38 in the Exp tab is updated (because the formulas were updated on that spreadsheet), the number in Do 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 at the start of this assignment, did the department adopt a "balanced budget" for the current year? What issues might arise when defining a balanced budget? 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