Message Font: Serif | Sans-Serif

No. of Recommendations: 19
To calculate the classic (Trouncing the Dow) Benchmark Investing downside price target you need 10 years of historic data. I'll be using one of the new Dow compoments PFE as my example. I'm using it since VL gives this data away for free (http://www.valueline.com/dow30/f7040.pdf), so I don't anticipate any trouble if I reproduce the numbers here.

Collect 10 years of High prices, Low prices, Book Value per sh, Return on Shr. Equity. Since it is 2004, use 1994-2003 as the historical data. 2004 data will serve as my estimates for Book Value and Return on Shr. Equity. Here's the PFE data:
`                     1994 1995 1996 1997 1998 1999 2000 2001 2002 2003High Price            6.6 11.1 15.2 26.7   43   50 49.3 46.8 42.5 36.9Low Price             4.4  6.2   10 13.4 23.7 31.5   30   34 25.1 27.9Book Value           1.15 1.44  1.8 2.04 2.27 2.31 2.55 2.91 3.24 8.53Return on Shr Equity   30 28.2 27.7 27.9 29.9 38.2 40.4 45.6 47.9 19.5`

According to the author Ken Lee, if there are years where the Return on Shr. Equity is marked NMF, then all the data for that year is tossed out. I've extended this to include throwing out data if the hi and lo prices are missing (Frequent occurance in Midcap data), or there is price data but no BV or ROE data (very infrequent). I'll show JPM in another message since it has missing price data.

For each of these items calculate the 10 year average.
10Y avg High Price: 32.81
10Y avg Low Price: 20.62
10Y avg BV: 2.824
10Y avg ROE: 33.53

Now get the 2004 BV and ROE and calculate the Adjusted ROE which is the 2004 ROE divided by the 10Y avg ROE.
2004 BV: 9.60
2004 ROE: 22.0

Now we're ready to calculate the 10Y Downside Price. This is the price below which the stock becomes attractive from a value perspective. The formula to calculate downside price is:
Downside = (10Y Avg Low Price)/(10Y avg BV)*(2004 BV)*(Adjusted ROE)
Downside = 20.62/2.824*9.6*0.656 = 45.98

PFE's current price is 35.43, so it is trading at a hefty discount to its downside price, which would indicate it's a bargain.

To the nitpickers out there who check the downside at the benchmarkinvesting.com site, you'll find a one cent discrepancy due to roundoff error, since I clipped the adjusted ROE at 3 decimal places.

- Lee
No. of Recommendations: 5
```Lee:

Thanks for the illustrative example.

To make the intent clearer, I will re-write the BI formula as:

Downside = 10Yr_Avg_Low * (2004_BV/10Yr_avg_BV) * (2004_ROE/10Yr_Avg_ROE)

In other words, the downside value is the 10 year average low,
normalized by BV and ROE.

is that the BV for PFE has been rising very rapidly.  So, the BI
formula would lead to a corresponding rapid increase in the downside.
This is perhaps as it should be.

However, I asked myself -- what if I smoothed the values somewhat,
either because I don't think that the spike in BV is real (is that
possible), or just because I wanted to be conservative and assumed that
the stock price would rise more gradually to catch up to intrinsic
value as defined by the BI formula?

So, I tried a little experiment ...
I computed a moving average of sorts, where the 10-year average is the
average of 1993, a weight times 1994, weight_squared times 1995, and so
on -- in essence, a lagged average where the oldest data is emphasized,
and the newest data is de-emphasized.  I hasten to add that this is
counter-intuitive, and driven strictly by my desire to de-emphasize the
recent spike.
I would have preferred to "test" this by predicting last couple of
years' numbers, but since I did not have 1993 and 1992 data, I
approximated them using 9-year and 8-year lagged averages.

Here are the results using straight averages:

2002	2003	2004
Actual_High	42.50	36.90
Upside		69.98	70.04	73.18
Downside	43.11	42.88	45.99
Actual_Low	25.10	27.90

Notice that 2004 prediction for downside is \$45.99, consistent with
your calculation.  Downside for 2003 was below actual low, but above
actual low for 2002.

Here are the results using lagged averages:
2002    2003    2004
Actual_High     42.50   36.90
Upside	        47.58   46.25   48.45
Downside        29.01   28.10   30.00
Actual_Low      25.10   27.90

This would predict a downside of \$30 for 2004!  Downside for 2002 and
2003 is close to actual lows.

My sense is that this is a classic example of curve-fitting :-)
At the very least, I would guess that the weighting factor would
have to be adjusted for each stock individually.
```
No. of Recommendations: 0
is that the BV for PFE has been rising very rapidly.

PFE has certainly been on an aquisition binge over the last decade. Since, IIRC, book value increases by the amount paid for target company if PFE has been overpaying then the book value numbers could be inflated. It would be interesting to compare the numbers for tangible book value.

DB2
No. of Recommendations: 1
Since, IIRC, book value increases by the amount paid for target company if PFE has been overpaying then the book value numbers could be inflated. It would be interesting to compare the numbers for tangible book value.

Looking at PFE's balance sheet at Yahoo, shows that intangible assets have increased since 12/31/02 from 921M to 36.3B. Also Goodwill is up from 1B to 22B in that same period. It certainly looks like intangible assets have played a large part in the BV increase.

- Lee
No. of Recommendations: 0
Lee
I appreciate all your posts. It's evident to me after looking at this example that some of my figures are WAY off.
My problem seems to be with current ROE and Book Value, which I've been getting them from various sources such as Yahoo, the Fool, MSN Money etc. These figures are often greatly different from Value Line's.
Do you know why that would be? And do you know of any source other than Value Line (as my 'local' library is a good drive away) for these figures?

Thanks

ugmo
No. of Recommendations: 2
My problem seems to be with current ROE and Book Value, which I've been getting them from various sources such as Yahoo, the Fool, MSN Money etc. These figures are often greatly different from Value Line's.

A couple of years back Stevnfool used to post the Midcap BI stocks that my list highlighted. He used MSN and would get different prices as well. Here's a link to one of his old posts http://boards.fool.com/Message.asp?mid=17680329.

The current ROE and BV from VL are estimates for the current year. Professionally I do hundreds of consulting estimates every year and there is always an amazing variability in estimates even within my own organization. I would assume the same goes for different data sources trying to do all these estimates.

VL has made a point of stating for years that they try to do their estimates based on the macroeconomic factors first, then company specific factors second. I think this is going to lead to different estimates from other data sources, since most of Wall Street seems to be focused on bottom-up estimates. Also VL numbers can get stale since they are updated only every 3 months, whereas an online service can be much faster.

VL is also peculiar in that they round all ROE estimates to either .0 or .5. They round all BV estimates to .x0 or .x5 as well. Also VL's High and Low prices appear to be slightly different. This is especially true for stocks that have split multiple times. I haven't tried it lately, but recreating the VL numbers using Yahoo can be a real trick.

Hope this helps to explain some of the sources of variability.

- Lee
No. of Recommendations: 2
ugmo,

I know there is a long thread that explains the differences between the financial data vendors such as Compustat, Factset, Standard & Poor's, Value Line, et al. Maybe someone could point you in the right direction.

Even though I still think Value Line has the most conservative data, had I known way back when I created benchmark investing in 1988 that Value Line would be so "difficult" to work with and back-test in an automated way I would've gone elsewhere for my financial data. It sure would've made my life much simpler.

No. of Recommendations: 0
Hey,
One other thing, could someone give me a point to where Value Line indicates (if they do) projected earnings growth?
Thankyou
ugmo
No. of Recommendations: 0
Dowbuys, did you ever look into using tangible book value to avoid the problem of companies that overpay for their acquistions?
No. of Recommendations: 0
Hello Dr. Bob,

I use the book value that Value Line provides. When we recently conducted a back-test back to the 1960s for the Dow 30 stocks, we had to calculate the tangible book values for the oil companies, though.

No. of Recommendations: 2
we recently conducted a back-test back to the 1960s for the Dow 30 stocks, we had to calculate the tangible book values for the oil companies, though.

Ken,
Did you use outside sources to calculate the tangible book values for the oil companies, or just other VL data sheet items? How did you get around the issue of no hi or lo prices before 1973? Does this new backtest cover all months or just January 1 starts?

- Lee
No. of Recommendations: 0
Lee,

Thank you for your example on how to calculate the BI downside.

One more question: how do you deal with negative BV and ROE values in single years? Do you skip these years or do you skip the whole stock or do you only skip a stock if the average value of BV or ROE becomes negative?

Staka
No. of Recommendations: 1
One more question: how do you deal with negative BV and ROE values in single years? Do you skip these years or do you skip the whole stock or do you only skip a stock if the average value of BV or ROE becomes negative?

Negative ROE's never occur while using VL, since they always put NMF in a negative ROE field or a ROE field that is huge due to a tiny shareholder equity.

I include negative BV's and use that year's data. There is one stock in the S&P 500 that has a negative 10 yr average BV, so we exclude a stock that has negative average BV for 5 or 10 yrs.

- Lee
No. of Recommendations: 0
Negative ROE's never occur while using VL, since they always put NMF in a negative ROE field or a ROE field that is huge due to a tiny shareholder equity.

Ok, I'll ask the question: What is NMF and how it is used? I can always guess, but I'll leave it to the VL (ValueLine) experts to give an authorative answer.

Thanks,

Keelix
No. of Recommendations: 0
NMF stands for "Not Meaningful"

Jeffrey
No. of Recommendations: 0
What is NMF and how it is used?

It is either Not Meaningful or No Meaningful Figure. VL uses it in all of its % calculations on its data sheets. It's used in multiple ways. On a ROE calculation it can mean either a negative number or a very high number (>150%) was generated. On things like dividend payout, it's used when profits go negative. In the electronic data, I believe it leaves a blank or space.

- Lee
No. of Recommendations: 2
Ken,
Did you use outside sources to calculate the tangible book values for the oil companies, or just other VL data sheet items? How did you get around the issue of no hi or lo prices before 1973? Does this new backtest cover all months or just January 1 starts?

Lee,

My apologies for taking so long to reply... simply not enough hours in the day sometimes. Anyway, to answer your questions:

I used an outside source for the tangible book values for the oil companies.

It took two of us over two months to calculate the benchmarks for the Dow 30 industrials because the only way to get the yearly highs and lows was from my set of Standard & Poors Yearend Stock Guides--which I have all the way back to 1953. Because of the splits that occurred, it took a real long time to make sure we had the right prices, book values, etc.

Even though we were only dealing with 30 stocks, it took 2 months of hard work to calculate the returns. And that was for the January 1 starts alone. Sorry, I wish we had all monthly start dates.

One last note: we used only what we would have had on December 31 of each year. One year, Value Line didn't provide estimates for the upcoming year (why, I don't know), so we simply used the previous year's data, which is not what we can do today (thankfully).

Again, sorry for the long delay in answering you. Hope this helps.

Ken

No. of Recommendations: 1
Ken,
Did you use outside sources to calculate the tangible book values for the oil companies, or just other VL data sheet items?

In case you were wondering how the book values were calculated...

... we "reverse engineered" the missing book values by (1) multiplying the earnings per share by total shares. Obviously, this is not exactly accurate because total shares fluctuate all the time, but it was the best we could do. Then (2) we divided the income figure by ROE and (3) divided that figure by number of shares to estimate the missing book values.

When we looked up what Value Line years later provided for tangible book value we saw that our estimates were very close. For example, we came up with a \$44.78 book value for Standard Oil of California in 1967 and Value Line later showed it was actually \$45.10. So, we were off less than 1 percent. The most we were off in any one year was 6 percent.

Remember, though, that we were trying to replicate all the data we would have had available at the end of each year in order to come up with a realistic determination of whether or not benchmark investing outperformed or underperformed the Dow back then.
No. of Recommendations: 1
Lee:

While my computer churns away building a huge database I had some time to work so I've come back to the S&P 400 Midcaps. So far I've completed pulling in all data from your posts and soon I'll have a new database with all these values so that more backtesting can be done. I've now got all values from 9/7/01 to present. This will be much better than what I did before since I only put the picks in my database previously, but this time I've put in all the data and will be able to make Radiscripts that allow me to test anything on this data. But this is actually just a side note.

I'm trying to replicate the PFE data you posted in this thread. My goal will be to extend the S&P 400 Downside values back to 1996 for 10 Year and 1991 for 5 Year.

I find that the values do not match up very well with the historical PDF from Value Line and I'm wondering why.

Here is the table I've produced for validation purposes:

-- The top four rows are from the PDF as you posted it.
-- The next four are the average of all values for each year from VL.
-- The next two are the year end values from VL.
-- The two after that are the year begin values from VL.

No matter what, it seems to me that they apply the As Reported values to the previous year, not the present year for ROE. As to Book Value, I'm not sure where the difference lies.
`Lee's Posted Data      1994   1995   1996    1997    1998    1999   2000High Price             6.60  11.10  15.20   26.70   43.00   50.00  49.30Low Price              4.40   6.20  10.00   13.40   23.70   31.50  30.00Book Value             1.15   1.44   1.80    2.04    2.27    2.31   2.55Return on Shr Equity  30.00  28.20  27.70   27.90   29.90   38.20  40.40Value Line Data                                                         Average Full Year                                                       High Price            78.00  94.88  90.63  103.75  121.75  150.13  50.04Low Price             52.50  30.00  37.25   32.81   40.50   28.67  30.00Book Value            12.66  10.32   8.05    7.55    5.99    4.44   2.30Return on Shr Equity  28.68  30.17  28.80   27.85   27.86   29.47  35.94                                                                        Year End Values                                                         Book Value            12.04   6.88   8.64    5.39    6.13    2.27   2.31Return on Shr Equity  30.50  30.00  28.20   27.73   27.89   29.89  38.17                                                                        Year Begin Values                                                       Book Value            14.51  12.04   6.88    8.64    5.39    6.13   2.27Return on Shr Equity  23.20  30.50  30.00   28.21   27.73   27.89  29.89`

High - Low Stock Prices

The top values are what you posted from the PDF. The rows after that are adjusted and unadjusted prices using the Close, High or Low price. Based on this the best match is using Split Adjusted High and Low prices, not close, and not Unadjusted.
`Lee's Posted Data    1994   1995   1996    1997    1998    1999   2000High Price           6.60  11.10  15.20   26.70   43.00   50.00  49.30Low Price            4.40   6.20  10.00   13.40   23.70   31.50  30.00                                                                      Historical Prices                                                         High Price                                                        Close Prices                                                               Unadjusted     78.63  94.00  90.50  122.00  126.13  150.13  48.94    Split Adjusted   6.55  11.04  15.08   25.94   42.04   50.04  48.94High Prices                                                                Unadjusted     79.37  94.87  91.25  123.13  128.94  150.12  49.25    Split Adjusted   6.61  11.15  15.21   26.67   42.98   50.04  49.25                                                                          Low Price                                                         Close Prices                                                               Unadjusted     53.63  43.88  60.75   52.44   73.94   32.44   30.5    Split Adjusted   4.47   6.22  10.13   13.65   24.65   31.71   30.5Low Prices                                                                 Unadjusted     53.13  43.50  60.25   51.06   71.06   32.19  30.00    Split Adjusted   4.43   6.21  10.04   13.44   23.69   31.54  30.00`

Questions:

1. What is my problem with Book Value and how do I correct it?

2. Would you use the ROE from the following year (1996 is used for 1995), the beginning of the year in question, the end of the year in question, or the average of all values of the year in question? It appears from this validation process that the first is what they've done.

3. Do you agree that it is optimal to use High and Low Split-Adjusted prices?

4. What is the obstacle to writing a Radiscript that will work using VL data? Do you have a Radiscript for this?

I'm running out of time to continue to pursue this, but if some here can get back to me on this soon I'll try to push this project through to completion by the weekend.
No. of Recommendations: 0
1. What is my problem with Book Value and how do I correct it?

It looks like all of your book value data has been shifted one year later. Could this be due to fiscal vs calendar year? PG is good company to use for this since I think there fiscal year ends June 30th. I'm pretty sure VL bases all their data on Fiscal Year, so 2005 when it is finalized will include data that was released in 2006. Also shouldn't the Book Value be Split Adjusted. VL does split adjust it.

2. Would you use the ROE from the following year (1996 is used for 1995), the beginning of the year in question, the end of the year in question, or the average of all values of the year in question?

Again I think the data looks shifted, but year end values look like they match better.

3. Do you agree that it is optimal to use High and Low Split-Adjusted prices?

Yes, but only if you split adjust book value.

4. What is the obstacle to writing a Radiscript that will work using VL data? Do you have a Radiscript for this?

The biggest obstacle is emulating the TTD book's method of dropping all the data of a year when it has no hi/lo price or a ROE of NMF. In my spreadsheet I always have a raw data section and processed data section that is cleaned up and ready to calculate.

I played around with the idea of using Radiscript for the Dow Historic backtest, but I never got around to getting it working once I had an all months spreadsheet.

- Lee
No. of Recommendations: 3
Lee wrote:
It looks like all of your book value data has been shifted one year later. Could this be due to fiscal vs calendar year?

I'm using the "As Reported" dates -- that is, the actual dates of the files from VL. I assume some of the data you use comes from VL -- what do you do about this? You'll note that as the dates get closer to the present the values are the same as the pdf file -- oh, no, you can't notice, I only posted up to 2000. Well, that is the case -- the closer to the present, the closer to the pdf.

Yes, but only if you split adjust book value.

So one choice would be to leave the prices unadjusted and the book value unadjusted? But what about the ROE in this case?

The biggest obstacle is emulating the TTD book's method of dropping all the data of a year when it has no hi/lo price or a ROE of NMF.

Is this due to the fact that VL just doesn't have the price data, or the fact that the company did not exist back ten years? If the former, I believe I'll be able to provide the prices where they cannot, but if the latter, that is inherent to the method.

If you could help me come up with a Radiscript to match this I'll be able to test this farther back in time and fulfill your stated goal to do so on the MI board.
No. of Recommendations: 0
I'm using the "As Reported" dates -- that is, the actual dates of the files from VL. I assume some of the data you use comes from VL -- what do you do about this?

My Book Value, shareholder ROE, Cash Flow/share, and Capital Spending/share come from VL out of the File/Export/Annual Historical Data menu. This is the same data as the VL Reports show, but it does get split adjusted the week after the split. Earnings and Altman Z data comes from the spreadsheet. F-score I get from interfacing to MSN and Yahoo via Macros.

So one choice would be to leave the prices unadjusted and the book value unadjusted? But what about the ROE in this case?