How much is a stock worth?
By David Harper, Principal, Investor Alternatives, LLC

It is interactive

This Xcelsius visual model helps you answer a common question: should I buy or sell a stock? But rather than produce a yes/no answer, we use some of the unique features of Xcelsius to ask a much better question. Specifically: what set of assumptions are required to make this stock a good purchase? And conversely: given my assumptions about this stock and my own criteria for selling (my "sell discipline"), is it time to sell this stock? To get at these questions, our visual model plots projected quarterly earnings per share (EPS). On top of these fundamental quarterly projections, we superimpose a projected stock price (based on assumptions, of course). This is sometimes enough to make a judgment.

But we also go further. If the user wants, they can display a plot of the discounted stock price. The discounted stock price is the projected price path expressed in terms of today's value. For example, we may require a threshold return of 6% in the equity markets. This could also be called the cost of equity or opportunity cost of equity capital. If the user prefers, they can superimpose this discounted price on top of the current price. This allows for an apples-to-apples comparison of today's (current) stock price with today's value of the projected future stock price. All based on the assumptions made in the visual model, of course. To continue the example, if our required rate of return is 6% and it just so happens that our projected stock price grows by 6%, then the discounted price will equal today's price and we would have a current value which is exactly fair.

The spreadsheet in three sections

(Note: you can download all the source files for this example free at: www.bestresearch.info)

Our spreadsheet for this example is divided into three sections: Financial Assumptions, Xcelsius buttons, and the timeline. First, the financial assumptions are collected together in the upper left-hand section of the spreadsheet. This is a good practice to get into because, if they are located here, their links to Xcelsius are less likely to be broken as you update and re-import the spreadsheet.

Second, our Xcelsius model uses three radio buttons and three toggle buttons. These are also collected together. For the radio buttons, we simply enter the radio labels and a single cell (cell J2) contains a '1' for our default entry (i.e., 'Trailing' is the first entry so that is our default). Three labels identify each of our toggle buttons (Sales Override, Financials?, and Discounted Value?). Zeros indicate that our buttons will begin in the off-state. Finally, the 'Show 2 nd Y-axis' is calculated; it equals 1 minus the value in the cell labeled 'Sales Override'.

Can building radio buttons be this easy? Yes! See the Xcelsius Radio Button Properties > General dialog box below. We point Labels to the three cells in the spreadsheet labeled "Trailing", "Current" and "Forward". This makes three radio buttons. Then we point the Insert In: tocell J2 above (to the right of Trailing, it contains a '1'). You will notice that Insert Option is set to 'position'. That's all we need. If the user clicks on a radio button, then the vertical position of that button (i.e., 1, 2, or 3) is automatically inserted into cell J2.

Third, the timeline projections are also collected together for display. This is really just an abridged income statement. Earnings before interest and taxes (EBIT) is also called operating profit after depreciation and amortization. Then we subtract interest and taxes to produce earnings after taxes (EAT). This is also called net income. We divide this into common shares outstanding (CSO) to get earnings per share (ESP):

We calculate three different kinds of EPS: TTM, Current and Forward. Trailing twelve months (TTM) is EAT for the most recent four quarters added together; current EPS is EAT for the previous two plus the forward two quarters (i.e., the two quarters after the quarter being measured) added together; and forward EPS is the sum of projected EAT for the forward four quarters. The reason we calculate three flavors of EPS is that people tend to use some combination or all three of these to value stocks, and we want to give the user a choice. In order to calculate the predicted value of a stock, we multiply EPS by a reasonable P/E multiple. So, for example, if we are projecting a stock price for the 1 st quarter of 2008, we could multiply a trailing EPS (i.e., the sum of the four quarters during 2007 since these are the four trailing quarters) by the trailing P/E multiple.

The Xcelsius Visual Model

Our Xcelsius visual model has two special features. First, there are three charts stacked on top of each other: two line charts and a column chart. Second, there are several hidden components that are revealed only if the user clicks on a toggle button. This demonstrates an incremental approach to displaying information. The initial chart is (hopefully) easy to read and uncluttered. If the user prefers, they can select additional functionality.

The three charts are illustrated below. Initially, the blue line chart is not displayed. It contains the discounted stock price path and we don’t want to clutter the chart unless the user wants to see it.

You can see below, in the screenshot of the Object Browser, a few of the components on the Xcelsius "canvas". In Xcelsius, you will find that the Object Browser is critical to manipulating and organization objects on the canvas while you are creating your visual model. Notice that I grouped many of the miscellaneous components into folders. I highly recommend this practice. It is very easy to do. You select the relevant components, then right-click to get an object menu, and then select Group. This collects the selected components into a newly created folder.

Dynamic Controls

The second special feature of this visual model is that we use quite a few dynamically displayed components. The idea is to leverage Xcelsius capabilities to give the user an incremental display of functionality: we start with something simple and let the user decide if he/she wants to introduce more complexity!

The key to this is the Toggle buttons. When clicked, they change the zeros to ones in the Xcelsius Buttons section of the embedded spreadsheet. The hidden components are linked to these four cells; they get displayed when a zero changes to a one in the spreadsheet. Take the example of the vertical slider called 'Required Return (ROE)'. This slider lets the user input his/her threshold return for purposes of displaying the discounted stock price path. It is hidden unless and until the user clicks on a toggle button. Below is a part of the Behavior property panel for this vertical slider:


The thing to notice is that we linked to a cell in Display Status. We linked it to a cell in the Xcelsius Buttons section of the spreadsheet. The cell starts as a zero, so this vertical slider is hidden. But when the user clicks on the toggle button, the zero changes to a '1' and then, because of our entry here under Dynamic Visibility, our vertical slider appears.

That concludes our brief tour of this Xcelsius visual model. Hopefully, you see there are an infinite number of ways you could customize it. For example, you could substitute or add cash flow valuation to the model.

When you get familiar with the many features in Xcelsius, you can create visual models that will simplify and enhance your work – and maybe even help you evaluate your stocks!

(Note: you can download all the source files for this example free at: www.bestresearch.info)

David Harper is the Principal of Investor Alternatives, LLC, (www.investoralternatives.net) a firm that specializes in investment research, software sector coverage, and derivatives valuation. He is the Editor-in-Chief of Investopedia Advisor (http://advisor.investopedia.com), a newsletter devoted to the early recognition of public companies that are likely to be lead future market. He publishes the Bionic Turtle Study Notes, courseware for learning advanced risk management concepts. He is a Charted Financial Analyst (CFA) and Financial Risk Manager (FRM).

Close window