Stock Valuation Spreadsheet

Readers of Computerized Investing know that there is an unending choice of Web sites and software packages for stock analysis. However, there is a relative lack of services out there to help you find the “fair” value of a stock.

Furthermore, there is not always the need for a complex, and potentially expensive, software package or Web service to perform the desired analysis. Many investors still use a spreadsheet program to perform the bulk of their investment tracking and analysis.

This article presents a revised valuation spreadsheet template that you can use to gauge what a stock is actually worth. The valuation spreadsheet includes updated market statistics and notes current sources of company and market data.

Furthermore, readers of Computerized Investing have exclusive access to this spreadsheet template at the Computerized Investing Web site: www.aaii.com/ci/spreadsheets/stockval.xls.

Worksheet Overview

This stock evaluation worksheet provides an easy-to-follow systematic format that walks you through the analysis process without bogging you down by complicated principles. The results of the worksheet’s calculations are several valuation estimates, based on company factors such as sales, cash flow, earnings, and dividends. There are also two valuation models that relate current company multiples to market multiples, as well as areas for you to test out your assumptions and perform sensitivity analysis.

First:    Last:    Email:

Adding additional valuation techniques does not necessarily lead to a better valuation. The valuations we use in this worksheet help to identify firms trading at prices that deviate from their historical norms. Using different models allows you analyze a larger range of company situations. For example, the dividend valuation will not work with a company that does not pay a dividend. Likewise, the price-earnings approach fails when a company reports negative earnings.

Using the Spreadsheet

This valuation spreadsheet is divided into four main sections—current stock and market data, historical market data, and historical stock data (Figure 1), and valuations (Figures 2 and 3). Shaded cells in the figures are those requiring user input.

The top section of Figure 1 contains current data for the stock under analysis and the market, as defined by the S&P 500 index in this example.

Figure 1. Stock and Market Data Sections of Valuation Worksheet CLICK ON IMAGE TO SEE FULL SIZE.

The first area contains basic information such as the date of the analysis and the company name, ticker symbol, exchange, current price, and beta.

The Current Stock Data area contains common market multiples. All are computed using trailing 12-month figures except dividends per share (DPS). The worksheet uses the indicated dividend for the DPS figure, which is the approximation of what the company will pay out in dividends over the next 12 months. For a company paying quarterly dividends, the indicated dividend is the latest quarterly dividend payment multiplied by four. These multiples are ratios that compare a company’s financial data element, such as earnings per share, to the stock price, which is set by the market.

The Current Market Data (S&P 500) area provides the underlying data for the worksheet’s price-earnings (P/E) relative and dividend yield relative models. The Standard & Poor’s site (www.standardandpoors.com) was used as a source for the bulk of the current and historical index earnings and dividend figures. By going to the S&P 500 area of the site’s Indices section, you can find free historical data relating to the indexes’ historical and forecasted earnings and dividends along with current and historical market multiples. The Standard and Poor’s Analysts’ Handbook also provides this data, and was the source of the market book value data in the spreadsheet template. Free sources of index book value data are very difficult to come by, however, so you may have to do without this figure for your analysis.

The Barron’s Stock Market Laboratory in the Market Week section of the printed publication, as well as the Market Lab section of Barron’s Online (www.barrons.com), provides current earnings, dividends, and book value for several market indexes, including the S&P 500. Note, however, that the book value figures in Barron’s tend to be slightly dated. As of April 23, 2007, the Barron’s Web site was reporting 2004 and 2005 book value figures for the S&P 500.

S&P 500 Market Data Section

The market data section of the spreadsheet provides a historical perspective of the market needed for the market relative valuations. In addition, the data you enter in this section serve as the inputs for the market index financial ratios that appear below the S&P 500 market data. Variations in data reporting exist between different data services, so it is a good idea to get into the habit of citing your sources.

This section includes the market close figure for each of the last five years, along with the high and low figures for each period. The close provides an indication of the market (or stock) at a specific point in time. When you see that the high price for the year is at or near the close, you know that the year closed strongly. The S&P 500 closed within 5% of its high for the year in each of the years 2003 through 2006.

Also, note that the formula for the five-year growth rates for the various market and stock data uses a series of if-then statements, which allow it to handle both positive and negative values.

Market Estimates

The market data section includes a space where you can enter a personal P/E estimate and dividend yield estimate. These estimates allow you to perform “what-if” analyses with the valuations below. Similarly, there is room for you to enter company market multiple estimates in the stock data section. These estimates allow you to see the impact upon the stock valuation if the expected market multiple changes. Multiples reflect the expectations built into the price. For example, a high price-earnings ratio indicates that the market is willing to support a higher price in relation to the current earnings per share because there is a high chance for even greater earnings per share in the future. Multiples adjust as the levels of optimism and risk for the market, industry, or company change over time. This section provides the ability to try various multiple estimates so that you can scan the historical multiples, examine past ranges, possibly spot a trend, and input an expected future multiple based on the expected trend.

Stock Data Section

Manitowoc Company, Inc. (MTW) was used as our example company. The company engages in the manufacture and marketing of cranes and related products, foodservice equipment, and marine products in the U.S. and internationally.

Here we used Stock Investor Pro, AAII’s fundamental stock screening and research database program—with data as of April 27, 2007—as the primary source of stock data (Reuters is the primary data provider, with earnings estimate data coming from I/B/E/S). Again, it is desirable to use one data source for all of your comparative analysis. Furthermore, it is important to fully understand each data source’s strengths and weaknesses.

This section calls for high, low and close prices for each period, as in the Market Data section, along with four additional company data elements—sales per share (SPS), cash flow per share (CFPS), total assets per share (APS), and total liabilities per share (LPS). These additional figures provide inputs for the additional financial ratios found in the Financial Ratios—Stock section of the worksheet.

Financial Ratios—Stock

This section calculates additional company-specific ratios—asset turnover, return on assets, total liabilities to total assets, total liabilities to equity, and sustainable growth.

Return on assets (ROA) is a measure of profitability, as is return on equity (ROE) and profit margin. Specifically, ROA measures how well management is using the assets of the firm to generate profits. One method of calculating return on assets is to multiply profit margin (earnings divided by sales) by asset turnover (total assets divided by sales). When judging ROA, it is helpful to look at these components: Management can increase ROA with higher profit margins or higher asset turnover. A company improves its profit margin by lowering expenses relative to sales, while asset turnover can be improved by using assets more effectively—selling more goods with a given level of assets. Like all financial ratios, you should examine them over time to identify trends. Steadily increasing ROA ratios are considered positive. However, comparing them against industry norms tells you if these ratios are high. For example, supermarkets tend to have low profit margins, but relatively high asset turnover.

Return on equity (ROE) takes into consideration the impact of the firm’s operating performance and its capital structure. ROE is sometimes calculated by dividing ROA by the remainder of the total liabilities to total assets ratio subtracted from one. To increase ROE, a company can increase its operating performance (ROA) or use greater financial leverage. Impressively, Manitowoc Company has managed to increase its return on equity from 13.1% to 21.5% while still managing to lower its debt by significantly increasing its return on assets.

The sustainable growth rate is the final stock-specific financial ratio in the worksheet. It measures the growth potential of the firm considering its profitability (ROE) and how much money the company reinvests in itself versus pays out as dividends. It provides a “baseline” growth potential of the firm. To increase sustainable growth, a firm must increase its ratio of earnings to equity or plow more of the earnings back into the firm instead of paying it out as dividends. A negative sustainable growth rate indicates that a firm is paying out more in dividends than it is earning, which will limit its future growth potential if this continues over an extended period.

Comparing the 2006 sustainable growth rate of 20.4% to the five-year annualized earnings growth rate of 31.1% helps to show that Manitowoc Company cannot maintain such a high level of growth over a long period. However, as the company has improved its return on equity over the last few years, its sustainable growth rate has risen above the five-year average of 9.4%.

The earnings figure you use may have a significant impact on the company’s long-term growth rate. We are using earnings from continuing operations here, which excludes any extraordinary or one-time items that management has discretion over. Manitowoc Company had extraordinary write-offs in 2002, which significantly lowered earnings that year. Had we used basic earnings, the five-year growth rate for Manitowoc Company would be over 70%.

Valuation Estimates: Average Multiple Models

The valuation estimates in Figure 2 represent the heart of this worksheet and allow you to perform sensitivity analysis as well as make comparisons to the current price. A company’s sales, cash flow, earnings, dividend yield, and book value serve as the basis for these models to arrive at the various average multiple valuations.

The value-to-price ratio provides a quick snapshot of how the valuation compares to the current stock price. A ratio of 100% means that the valuation is equal to the current stock price, while ratios above 100% indicate valuations above the current price, and ratios below 100% indicate valuations below the current price.

Figure 2. Valuation Estimates Based on Average Multiple Models CLICK ON IMAGE TO SEE FULL SIZE.

The worksheet also allows you to vary the estimates for next year’s figures and vary the multiples to see the impact upon the valuation. Using this what-if analysis, you can measure the impact on price of a change in the market expectations for a company.

Valuation Estimates: Relative Market Multiple Models

Comparing the relationship of a stock to the market can reveal interesting information about a company beyond just looking at the company’s historical market multiples. Based upon growth expectations, companies trade at multiples greater or smaller than that of the market. For example, you would expect companies with prospects better than the market as a whole to trade with higher multiples than the market.

By dividing a company’s multiple by that of the market’s, a multiple relative is determined. This stock valuation worksheet uses the P/E relative and dividend yield relative in Figure 3 because it can be difficult to obtain other multiples for the market.

The first step in using the model is to divide the company’s multiple by the market’s multiple. After determining the multiple relative, you must determine the expected market multiple and multiply it by the multiple relative to determine the adjusted company multiple. Applying the expected earnings or dividend figure for next year to this adjusted company multiple provides a valuation.

Using the Manitowoc Company relative P/E valuation as an example, you can see that the market’s opinion of the company’s prospects has trended upward over time. Early on, the company labored at a deep discount below the market, but most recently it has commanded premiums above the market.

Figure 3. Valuation Estimates Based on Relative Market Multiple Models CLICK ON IMAGE TO SEE FULL SIZE.

One key for determining the company’s fair price is deciding if the market is correctly interpreting the company’s prospects. The five–year average P/E relative for Manitowoc Company ranges from 1.29 to 1.83. Multiplying these P/E relatives by the current S&P P/E of 18.3 produces a range of adjusted P/Es from 23.6 to 33.5—roughly 30% higher than the company’s current P/E of 25.8.

However, before you decide whether a valuation based on this adjusted P/E is valid, you should consider whether the current S&P 500 P/E ratio of 18.3 is reasonable. If you feel confident that the economy will continue to grow, and with it corporate earnings, a lower market P/E may be appropriate. Standard & Poor’s is forecasting the reported earnings for the S&P 500 to increase to $89.45 by the end of this year. At the current S&P 500 price level, that would translate to a P/E of 16.7.

Multiplying this 16.7 estimated market P/E by the range of the company’s P/E relatives results in adjusted company P/Es ranging from 21.5 to 30.6. Multiplying the range of projected company earnings by these P/Es results in the valuation range shown in rows 169 through 171, columns G, I, and K. The spreadsheet also allows you to input P/E relative estimates to check the sensitivity of this item on company valuation.

Conclusion

Using a spreadsheet such as this provides a consistent technique for examining a company and its relationship to the market. It helps to identify the factors driving the current market price and test various assumptions to arrive at a “fair” price. However, always keep in mind that stock valuation is not an exact science. The valuations you arrive at rely heavily on assumptions that may or may not hold true. If performed effectively, however, it can serve as a rewarding tool in the overall stock analysis process.