School Business Affairs February 2019

asbointl.org SCHOOL BUSINESS AFFAIRS | FEBRUARY 2019 35 excel tools Error Trapping in Microsoft Excel By John A. Williams Use Excel to create an error-trapping sheet. A balanced budget in Microsoft Excel is a thing of beauty. It inspires confidence that all accounts are in order and all money is accounted for. Just as creating a datasheet in a budget workbook that lists all assumptions, rates, and “hard-coded” figures is a best practice, school business officials can create an error- trapping sheet in Excel that summarizes formulas and provides a starting point when numbers in a spreadsheet are out of balance. A best practice for Excel budgets is to link revenues, expenditures, and transfers so that their differences equal zero in an error sheet. Users can format these records in Excel tables containing active total rows to summarize totals in the respective table spreadsheets. Figure 1 provides an example of a detailed list of certificated staff members with their salaries and benefits. The total in yellow captures the entire dollar amount budgeted in the spreadsheet; that total also flows into the error sheet. In addition to certified staff member listings, this example has similarly structured spreadsheets for revenues, operat- ing budgets, support staff, administrative staff, addenda (stipends), and substitutes and extra pays. In this example, all line item amounts flow to a master “expenditures” sheet (see figure 2) that records the account number and budget amount. SUMIFS, a powerful Excel function, totals the financial information and matches it with the respective account number. The expenditures sheet lists every line item and every budget total. It is the budget uploaded to the accounting software. With all detail spreadsheets (i.e., oper- ating, certificated, etc.) flowing into the summary expenditures sheet, the totals of each flow together into the error trip on the “errors” sheet (see figure 3). This errors sheet compares totals with each other. Since the expenditures sheet is the grand total, by subtracting components, the total should be zero. The net difference of the summary account and the supporting tables equals zero. The green TRUE box in cell B11 is just a conditional formula (=J11=0) that asks whether the error trap total equals zero, and when it does it turns TRUE. The green comes from conditional formatting and turns red when it is FALSE. Error traps allow school business officials to summarize figures to determine whether everything is accounted for. A similar process works for revenues and transfers (see figure 4). This budget contains a master revenue sheet that lists all revenue line items at the detail level. This sheet com- pares the summary against all detail sheets along with any transfers. The formula in cell B26 reads =J26=0. Finally is a check of expenditures work- sheet with the highest-level budgeting pages (see figure 5). These sheets are the function budgets that summarize different funds. These error checks test whether the detail found in the “expenditure” sheets tie out with the summary budgets approved by the board of trustees. With confidence, one can know that all the detail agrees with the sum- mary when the error checks go to zero and the true or false tests all show TRUE in col- umn B of the error trap. One last test examines all of column B (see figure 6). In cell C2, a formula WAVEBREAKMEDIAMICRO/STOCK.ADOBE.COM

RkJQdWJsaXNoZXIy NTMyNTY4