|User Guide Navigation:|
Business Spreadsheets User Guides: Project Planning and Management Multiple Regression Analysis and Forecasting Investment and Business Valuation Real Options Valuation Portfolio Optimization Portfolio Performance Tracking Customer Invoicing
Real Options Valuation Help Topics: Option Pricing Fundamentals The Option to Delay a Project The Option to Expand a Project The Option to Abandon a Project Binomial Option Pricing Game Theory Analysis
The Binomial Option pricing tool offers a more advanced application of Real Option Valuation where there exists 'options on options'. The binomial model is able to evaluate the present value of an unlimited number of branches where at each node the value of the underlying asset or investment can go either up or down. This is useful for evaluating more complex real world situations where a wide range of possible outcomes may exist, and is especially powerful when re-evaluating the value as certain outcomes eventuate.
As the estimation of positive and negative outcome values and probabilities can be difficult to estimate, the model facilitates this by providing an approximation to the normal distribution, whereby underlying cash flow volatility is automatically translated into upward and downward outcome results. Moreover, this assumption that underlying asset volatility is distributed normally (as assumed by the Black-Scholes model above), can be relaxed to model scenarios where the possible outcomes (volatility) are skewed either positively or negatively. This flexibility literately enables a limitless number of possible real option valuation scenarios.
On clicking the 'Start' button in the 'Menu' sheet; you are taken to the 'Binomial' sheet. Binomial branches can be created or deleted by selecting a dark blue color cell with white text labeled 'Valuation', and clicking the corresponding button at the top left.
Creating a Branch
By clicking the Create Branch button, a form is displayed for the inputs to create the binomial option branch. These are:
- If similar projects or investments have been undertaken or made in the past the standard deviation of cash flows resulting from these projects can be used as a proxy for the standard deviation in cash flows for the proposed investment.
- Probability analysis can be run on simulations of key inputs, such as revenue and cost drivers, market size and market share, to estimate the standard deviation of the resulting present value. While this type of analysis can be accomplished by using sampling analysis in the Analysis ToolPak add-in shipped with Excel, third-party add-ins can facilitate more sophisticated applications.
- The standard deviation of publicly traded firms in the same business or industry can be used a proxy for the proposed investment. This is the least preferred method due to the likely diversity of activities undertaken in other firms and resulting differences in variance characteristics. Such industry specific volatility data can be obtained from third party market data providers (such as those recommended at the Business-Spreadsheets.com web site) and entered into the Pre-Defined sheet for future use across models and proposals. It should be noted that the Pre-Defined sheet can also be utilized to store standard deviation data from similar projects undertaken in the past as described in the first method.
On clicking the 'Create' button, the binomial branch is created on the worksheet with both upward and downward value outcomes, key inputs and resulting option value in the original valuation box. All cells highlighted with a light blue color can be manipulated directly here to model alternative distributions and outcomes. Only the upward multiplier probability can be changed to modify outcome probabilities. This is because both probabilities must sum to 100%, and the downward probability changes automatically to accommodate. Further binomial branches can now be created in the same way by selecting the 'Valuation' cell on either of the two outcomes. The valuation at the beginning node changes to account for all subsequent branches (outcomes).
Deleting a Branch
To delete a branch, you must select the 'Valuation' cell on the node for which the branch was created from, and click the 'Delete Branch' button in the top left corner. Only end branches can be deleted in this way; therefore to delete multiple branches, you must start at the end and work backwards.