Many people see the markets moving up or down but have no idea of risk or rate of return of their personal investments. Andreas Steiner's web site provides a wealth of information on investment performance and many Excel templates that you can download for free. Keep in mind that that header on the downloads page states The downloads provided on this page are free and designed for educational purposes. They are meant to be illustrations to be used at your own risk, not productive applications.

There are a number of Excel book excerpts available around the web:

• Links to excerpts from four of John Walkenbach's 2007 books are available at The Spreadsheet Page.
• From the book titled Microsoft Office Excel 2003 Programming Inside Out, the chapter titled Creating Advanced User Forms is available.
• Several chapters are available from the book titled Microsoft Excel Data Analysis and Business Modeling. They are Determine the Optimal Product Mix with Solver, Introduction to optimization with the Excel Solver Tool, and Pick Your Fantasy Football Team with Solver.
• From the book titled Excel 2007 VBA Programmer's Reference, the chapter titled Primer in Excel VBA is available via Wiley.
• Links to excerpts from several chapters of the book titled Professional Excel Development are available at Stephen Bullen's Office Automation site. The chapter on RibbonX might be of special interest given the changes in Excel 2007.

Below are a few blog posts that I found especially useful:

• Jon Peltier recently posted articles titled 9 Steps to Simpler Chart Formatting and Overlapped Bar Chart - Thinner Bars in Front.
• John Walkenbach recently posted an article dealing with Color Contrast Functions with a tip titled Quantifying Color Choices. I found this post to be useful because many of the people I work with tend to use multiple background colors to highlight different types of information. As they use more and more colors, their spreadsheets become more and more distracting and difficult to read. On top of that, color printers don't always replicate the spreadsheet colors as intended so your printed copies look totally different from what you see on the screen.

MSN Money and Yahoo Finance are two good resources for those that like to follow stock trends by creating their own tables or charts in Excel. To retrieve a history of the performance of a selected stock, start at the Home page for either site. Enter a stock symbol into the "Get Quotes" input box. I started with Berkshire Hathoway's Class A shares, which closed yesterday at $115,500 / share (the highest price of any U.S. stock).

After entering the symbol into MSN Money, you get a page that contains the following:

The Yahoo Finance page looks similar to the MSN Money page:

Both sites have sidebars on the left side of their pages that allow you to access historical data. In the MSN Money sidebar, look under Charts - Historical. When you click on that link you get a chart that shows the history for the time period you selected. To retrieve the chart data, scroll down to the bottom of the page and click on the "View price history with dividends / splits" link.

In Yahoo Finance, look under Quotes - Historical Prices. Yahoo Finance offers a greater amount of price detail for download than MSN Money does. For example, you can get historical prices by day, week, or month as well as select your own time periods. The screenshot below shows the daily level of detail that's available.

Don't worry if you can't afford the $115,500 / share Class A stock. You can always pick up a few shares of the Class B stock for approximately 1/30 of the price of the Class A shares. Yesterday the Class B shares closed at $3,850.

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 ")".