As reported by the Dallas Morning News and CNN, 375 teachers were laid off, 450 reassigned to other schools, and 40 counselors and assistant principals were let go due to a massive miscalculation in the budget.

You originally project a $64 million shortfall but now it's $84 million? Where did the other $20 million come from?

I find it hard to believe that the finance and accounting staff would not have access to some type of salary data. And you're telling me that, after "X" amount of years that the school district has been in existence, we're just now discovering that we have systems in place that don't talk to each other? That's why we can't get the right salary data? If that's true, we seemed to get by with no problems in the past using these same systems - what's different now?

That's precisely why finance and accounting staff should build the budgets. The administrative function is to review and approve the budgets.

It's my guess that the finance and accounting staff had very little to do with building the budgets. And after they were created, the finance and accounting staff we're probably not asked to do a high-level review for reasonableness or a technical review for accuracy. After all, simple variance analysis techniques would have revealed a shortfall of this magnitude.

As seems to be more and more the norm these days, good hardworking people (in this case - the teachers and others) are punished due to the incompetence of management. It's management's job to put processes in place so that these types of things don't occur. But rather than management loosing their jobs, I'm betting that after it's all over and done they'll do just fine.

A chart that I have to show each month combines a trend of current results with a forecast. As an example, for the month of May 2008 the chart would look similar to this:

Fast-forwarding a few months, in August 2008 it would look similar to this:

By comparing the two examples, you can see that the "Actual" line (red line) expands each month reflecting current results. Conversely, the "Plan" line (blue line) contracts as the year moves forward. A marker and label indicating the Actual and Plan net operating income is always shown for the current month.

Although the chart appears to be Line chart, it really is a combination Line-XY (Scatter) chart. The chart can be automated so that it automatically updates as the most current data is entered. To do so, the screenshot below shows the setup:

The Actual and Plan data are entered into the range C4:D15. The Chart Source and Data Labels Source ranges contain formulas that allow the chart to automatically update. At the start of the year enter your Plan data into the range D4:D15. At the end of each month the results are entered into the range C4:C15.

There are six different formulas that drive the chart. The first two formulas drive the Actual and Plan lines.

The first formula, which drives the Actual series, is entered into cell F4 and then copied through the range F4:F15. The formula is:

=IF(C4,C4,NA())

The second formula, which drives the Plan series, is entered into cell G4 and then copied through the range G4:G15. The formula is:

=IF(COUNT($C$4:C4)=COUNT($C$4:$C$15),D4,IF(C4,NA(),D4))

The remaining four formulas drive the points for the current month and use an XY (Scatter) chart as their source. The following formulas appear in the following cells:

Actual Label Sources

Cell J5: =COUNT($C$4:$C$15)

Cell J6: =OFFSET($C$3,COUNT($C$4:$C$15),0)

Plan Label Sources

Cell J10: =COUNT($C$4:$C$15)

Cell J11: =OFFSET($D$3,COUNT($C$4:$C$15),0)

Finally, the chart source looks like this:

Line Chart Sources

Actual Line Source

Plan Line Source

XY Chart Sources

Actual Marker and Label

Plan Marker and Label

During the last couple of weeks I've been redesigning this site. Although not yet complete, I've expanded the site to include multiple weblogs for selected areas. The search page has been expanded to include more options. Hopefully the RSS feed working poperly now and the HTML for each entry appears as it should in Google Reader. A link to the entry category and comments page has been added to the bottom of each entry in the RSS feed. Finally, I'm taking advantage of ExpressionEngine's Pages Module to recreate many of the static web pages from regular weblog entries.

My goal is to have the redesign complete by the end of this week. The changes will be minimal as viewed from the outside, but will really help me keep up with the maintenance.

It's good practice to add the proper titles, headings, add line numbers to reports. The addition of line numbers to Excel based reports sounds easy . . after all it's just 1, 2, 3, 4, etc. However, in practice and with reports that include thousands of lines and/or modified by multiple people, the numbers often get out-of-sync.

Over time I've found that the following process works well for insuring that the line numbers are always in order. First make sure that any Excel based report has at least two empty columns. The first column will contain the line numbers and the second column will contain some type of identifier for what line is to be numbered.

To explain, start with the second column first. Any line that needs a number gets ")" added to the second column. Next, a macro like the one below below can be used to add the line numbers to the first column:

Sub AddLineNumbers()

    Application.ScreenUpdating = False

    Dim Constants As Range
    Dim Rng As Range
    Dim Cnt As Integer

    Cnt = 1

    Set Constants = Range("C7:C40").SpecialCells(xlCellTypeConstants, 2)

    For Each Rng In Constants
        If Rng.Value = ")" Then
            Rng.Offset(0, -1).Value = Cnt
            Cnt = Cnt + 1
        End If
    Next Rng

End Sub

In the financial statement below, line numbers have been added column "B".

Going through the process above, the two empty columns originally were columns "B" and "C". A ")" was added to each line within column "C" that needed a line number. The macro uses the SpecialCells method to identify any constants in column "C". Once the constants are identified, it loops through each constant and places a line number to the left of any constant that is a ")".

The VBA SpecialCells method combined with background colors can be a simple and effective method to perform high-level worksheet audits. The process works by using a macro to apply colors to individual cells flagged as per the SpecialCells criteria.

For example, in Excel 2003 when you go to Edit -> Go To... -> Special, the following dialog box appears:

Options are available that allow you to select cells with constants, formulas, blanks, etc.

The same can be done with VBA and the SpecialCells method. The advantage of using VBA is that you can apply background colors to make the cells quickly visible. Additionally, there are even more options available via VBA than what you see in the dialog box.

To explain, you're boss asks you to audit the accuracy of a simple financial statement. To do so, start by making sure that formulas appear where they logically should. A simple VBA procedure using the SpecialCells method as presented below can be used to color all of the cells with formulas in the desired range yellow (yellow is tied to the index value number "6" below - this may be different on your machine.)

Sub Flag_Formulas()
    Dim Rng As Range
    Set Rng = Range("F9:I32")
    Rng.SpecialCells(xlCellTypeFormulas).Interior.ColorIndex = 6
End Sub

After running the macro, the financial statement looks like this:

A quick look at the statement reveals that something is wrong in cell H19. Instead of seeing a cell with a formula it looks like somebody hard-coded a number.

This same process can be applied to other types of cells. The macro below can be used to flag hard-codes numbers:

Sub Flag_Hard_Coded_Numbers()
    Dim Rng As Range
    Set Rng = Range("F9:I32")
    Rng.SpecialCells(xlCellTypeConstants, 1).Interior.ColorIndex = 6
End Sub

This macro flags text entries:

Sub Flag_Text()
    Dim Rng As Range
    Set Rng = Range("F9:I32")
    Rng.SpecialCells(xlCellTypeConstants, 2).Interior.ColorIndex = 6
End Sub

For colors, if you want to specify an RGB number rather than an index number you can use a statement like:

Rng.SpecialCells(xlCellTypeConstants, 2).Interior.Color = RGB(255, 255, 204)

Finally, if you look up "SpecialCells" in Excel's VBA help there are even more options available. You can flag cells with comments, validation, same formatting, and even more.

In our accounting shop, we build our budgets in Excel but submit them in another software called Hyperion. Hyperion requires us to submit in whole numbers. To do so, we need to round every formula in the final Hyperion load so that the budget foots and crossfoots.

Before submission, I'll use the following macro to wrap the ROUND function around every formula:

Sub Add_Rounding()

    Dim cellRange As Range
    Dim Rng As Range
    Dim cellFormula As String

    On Error Resume Next
    
    Set cellRange = Range("A1:C10").SpecialCells(xlCellTypeFormulas)

    For Each Rng In cellRange
        
        cellFormula = Mid(Rng.Formula, 2, 1024)
        If InStr(UCase(cellFormula), UCase("Round")) = 0 Then
            Rng.Formula = "=round(" & cellFormula & ",0)"
        End If
    
    Next Rng

End Sub

The macro works by:

• Looping through each cell in the range.
• Uses the Special Cells method to identify only those cells with formulas.
• Sets up a string called "cellFormula" which is a picture of the formula.
• Uses the InStr function to look at the cellFormula string and identify if it already has the ROUND function included. If so, the macro goes on to the next cell. If not, it adds the ROUND function to the formula.

You can use the same type of logic to apply the ISERROR function to a range of formulas:

Sub Add_ErrorTrap()

    Dim cellRange As Range
    Dim Rng As Range
    Dim cellFormula As String

    On Error Resume Next
    
    Set cellRange = Range("A1:C10").SpecialCells(xlCellTypeFormulas)

    For Each Rng In cellRange
        
        cellFormula = Mid(Rng.Formula, 2, 1024)
        If InStr(UCase(cellFormula), UCase("Error")) = 0 Then
            Rng.Formula = "=if(Iserror(" & cellFormula & "),0," & cellFormula & ")"
        End If
    
    Next Rng

End Sub