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

As an update to Two-Way Variance Analysis Post 1, the example below shows the generally accepted calculation as well as notes (A and B) describing the components of the calculation.

The right side of the example shows a simple alternative method to perform the calculation. In my opinion this method is easier to remember and understand because it keys in on the percent change only of each component of variance. In other words, you don't need to remember footnotes A and B to perform the calculation. This can be especially useful when performing analysis of more than two components as the calculations get much more complex.

Jon Peltier's PTS Blog is holding a contest to "highlight scientific and engineering applications of Microsoft Excel, by sharing models that users have built."

"The intent is to highlight scientific and engineering applications of Microsoft Excel, by sharing models that users have built."

"The contest winner will receive a copy of Excel MVP Bernard Liengme’s Guide to Microsoft Excel 2002 for Scientists and Engineers. This is the Excel 2002 edition of the book. The Excel 2007 edition will not be out until this fall, but any lessons learned in the Excel 2002 version will be applicable to Excel 2007, though some of the specific commands may have changed."

As a sidenote, when I entered college I wanted to be an engineer. Everything was going great until I hit the second Chemistry and specifically calculations for molarity and molality. For some reason I just couldn't get it. Eventually I went to the professor and asked to drop the class (at that time you had to have the professor sign the drop slip before you could get out.) After an hour of stern lecturing by the professor because, in his view, I wasn't studying enough, he finally signed the slip. Sadly, that was the end of my engineering career. Now I do financial analysis all day long but I still wonder after 20 years how things might have been different. Oh well.

Jon's contest is an excellent opportunity to share best practices. Please participate if you can.

I've been away for a month and during that time the skyrocketing price of gas has received a lot of press. Having worked in the oil industry summers while in school, I've always found it interesting how people react to the price of gas. Until recently my thought had been that the price of gas had not kept up with inflation and the we in the U.S. should be probably be paying four to five dollars per gallon. However, I've never thought to prove my theory out until now.

In an effort to track what the price of gas has done over time, I first visited the U.S. Department of Labor's Consumer Price Index page. The first area of information I wanted to find was the Consumer Price Index for All Urban Consumers. Using January 1984 as the base, I set the base CPI for my analysis at 101.90. I then copied each monthly value from January 1984 to April 2008 into an Excel spreadsheet. Next, I retreived the average price per gallon values for regular unleaded gasoline for the same time period. To get an inflation adjusted price for gasoline, I multiplied the base price of gas (January 1984) by the CPI for the month. The results appear in the chart below. The year is on the X axis and the price per gallon of regular unleaded is on the Y axis.

The chart shows that the price per gallon of regular unleaded remained essentially flat from 1984 to 2000. After a small bubble that occurred from 2000 to 2002, the price starts to increase at a pace of over 20% per year. The 20% figure is calculated using the Compound Annual Growth Rate formula. The formula is:

( Future Value / Present Value ) ^ ( 1 / Number of Periods ) - 1

Excel's XIRR function can also be used to perform this calculation. The Analysis Toolpak must be installed for this function to work.

In January of 2002, the price of regular unleaded averaged $1.14. Five years later the price averaged $3.05. Using the formula above to calculate the compounded annual growth rate . . .

( $3.05 / $1.14 ) ^ ( 1 / 5 ) - 1 = 21.75%

Using 1984 as the base, the chart shows that the average price of unleaded gas exceeds the inflation adjusted price somewhere around 2006 to 2007. Unfortunately all of the growth has taken place in the last five years and it really doesn't start to stand out until 2004 - 2005. Based on inflation, it looks like my $4 to $5 price target may have been too high. Or, maybe $4 to $5 is the reality and the CPI is not reflecting that growth yet because the overall economy hasn't fully priced in current gas costs.

On another note, in the summer of 2004 I parked my truck and started to ride my motorcycle everywhere. I like to tell people that I anticipated the rising price of gas but the truth is that I just wanted to start riding a motorcycle instead of driving a car. Co-workers like to tell me "oh, you must be saving a lot of money by riding a motorcycle to work every day instead of driving a car". Although the bike does get between 40 to 50 mpg depending on driving conditions, I'm not sure that I'm really saving any money. What they don't realize is that I purchased a new motorcycle as opposed to driving a truck that was paid for. I recently figured I would have to ride the motorcycle for about ten years if I thought I was going to break even based on gas prices. I may be saving gas but I'm not saving any money, at this point anyway.

From Microsoft - Knowledge Base Article 213644:

In Microsoft Excel, you can set the Minimum and Maximum properties of the y-axis scale to any value. The following macro sets the minimum and maximum values for the y-axis scale in a chart to the maximum and minimum values of the data used to create the chart.

Sub SetScaleToMinAndMaxValues()
    Dim ValuesArray(), SeriesValues As Variant
    Dim Ctr As Integer, TotCtr As Integer
    With ActiveChart
        For Each X In .SeriesCollection
            SeriesValues = X.Values
            ReDim Preserve ValuesArray(1 To TotCtr + UBound(SeriesValues))
            For Ctr = 1 To UBound(SeriesValues)
                ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr)
            Next
            TotCtr = TotCtr + UBound(SeriesValues)
        Next
        .Axes(xlValue).MinimumScaleIsAuto = True
        .Axes(xlValue).MaximumScaleIsAuto = True
        .Axes(xlValue).MinimumScale = Application.Min(ValuesArray)
        .Axes(xlValue).MaximumScale = Application.Max(ValuesArray)
    End With
End Sub

It you need to use "=NA()" as a source value, the macro above fails. However, you can modify the macro to allow the array to accept only numeric values. To do so, modify the For-Next procedure as follows:

For Ctr = 1 To UBound(SeriesValues)
    If IsNumeric(SeriesValues(Ctr)) Then
        ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr)
    End If
Next

Before distributing a report as an Excel file, I always add or make sure of the following (in no particular order):

• A personal preference - use Arial or Verdana font. I've found these two fonts to be the most professional as well as the easiest to read on paper, on screen, or on the web.
• A descriptive title. I usually use three lines - the first is the company name, the second is the title of the report, and the third is the time period that the report covers.
• If the report contains confidential information, add the word "Confidential" to the header.
• If there are more than four or five lines or rows of information, use line numbers or alpha references on the left side of the report to quickly identify those lines or rows.
• If there are more than four or five columns of information, use column numbers or alpha references above the column titles to quickly identify those columns.
• Format dollars as dollars and statistics as numbers. A dollar sign ($) in front of any dollar value always insures that the viewer knows the difference between dollars and numbers.
• A footer that identifies the location of the report on the network as well as other information such as who created the report, the date and time created, page numbers, etc.
• Always remember to set up the print range before distributing the report.
• If the report contains cells with formulas that might are return errors, remove the errors or use conditional formatting to mask the errors before distribution.
• If the Excel file with report contains macros, I usually remove them before distribution so that whoever receives the report does not have to respond to macro prompts when the file is opened.
• Protect the sheets against unwanted changes if needed. I added this idea but rarely do this in practice. If I really want to protect an Excel based report from someone else making changes after distribution, I send it out as an Adobe .pdf file.

Am I missing anything?