All Industries, Financial Model, General Excel Financial Models |
Excel, Financial Analysis, Financial Model, Financial Modeling, Financial Planning |
Options and Real Options model consists of an Excel model which presents a valuation framework calculation for options. The calculations can be applied to both call options and put options, split into European and American options.
The Black and Scholes method is used for the valuation of the European options, while the binomial method is used for the valuation of the American options.
So, a quick overview of the model, in the contents tab you can see the structure of the model and by clicking on any of the headlines to be redirected to the relevant worksheet.
On the manual tab you can feed the general information for the model such as: model name, responsible, timeline of the model and date and currency conventions.
Additionally, there is a description of the color coding of the model in the same tab. Inputs are always depicted with a yellow fill and blue letters, call up (that is direct links from other cells) are filled in light blue with blue letters while calculations are depicted with white fill and black characters. Grey cells are restricted cells which you must not change.
There is also a color coding for the various tabs of the model. Yellow tabs are mostly assumptions tabs, grey tabs are calculations tabs, blue tabs are outputs tabs (that is effectively results or graphs) and finally light blue tabs are admin tabs (for example: the cover page, and contents).
On the tab “Options”, the user can set the various inputs for the option valuation, such as the stock price, strike price, expiration of the option, risk free rate, standard deviation, dividend yield, and the number of steps.
The option value is calculated here for both the Call Option and the Put Option. The American option values are calculated through binomial trees based on the methodology of Cox-Ross-Rubinstein, which can be found on the next tab. In this tab, we first calculate the value of the underlying asset and then we can calculate the value of both American and European options for both call and puts.
Going back to the “Options” tab, a sensitivity on the stock price is performed for both put and calls, from this sensitivity the value as well as the profit of the option at expiration is calculated.
Finally, on this tab, is the convergence of the valuation price of the European option calculated through the binomial model, to the Black and Scholes price.
Moving on we can apply the option valuation logic to real world cases, which are called real options. We consider the following cases:
– An expansion option: sometimes a project must be a series of expansion investments, instead of a single initial investment. Effectively the initial investment might be negative from a net present value point of view, but the full expansion project might be significantly positive. An example might be Uber for a single town versus Uber for several countries.
– An abandonment option: conceptually this states that a project which has an exit clause (either contractually or real) might have more value than a project without the exit possibility. The terms of this clause can be valued through an option. As soon as you introduce an exit option, the project might be worth trying.
– The option to delay: a project that is negative from a net present value perspective now, might become more valuable in the future. This requires some exclusive rights the asset, and thus high barriers to entry to keep this option valuable. An example would be a land rich in natural resources which might not have value now, due to low prices, but might have value in the future if natural resources prices go up.
– Finally, the last real option application presented is the value of a company in financial distress. Despite the value of the distressed company being negative now, the equity still has value if we consider that there is a chance that the company succeeds in its restructuring / turnaround, and as a result can continue servicing its debt, and operating normally.
File Types:
Excel Full Version – .xlsx
PDF Demo Version – .pdf
Similar Products
Other customers were also interested in...
Operating Company Financial Model
Operating Company Model consists of an Excel model which allows the user to generate forecast financ... Read more
Cash Budget Model
Cash Budget Excel Model generates a simple cash forecast based on a series of assumptions, and compa... Read more
Mergers and Acquisition (M&A) Financial Model
Merger and Acquisition Model template consists of an excel model which assists the user to assess th... Read more
Capital Budgeting Model Excel Template
Capital Budgeting consists of a process that companies use for decision making on investment project... Read more
Dynamic Arrays Financial Model
Dynamic Arrays Financial Model generates the three financial statements (profit & loss, balance ... Read more
Inventory Dashboard Model Template
!! Kindly use the latest Microsoft Excel Version before purchasing the model, otherwise, the dynamic... Read more
Leveraged Buyout (LBO) Model
Leveraged Buy Out (LBO) Model presents the business case of the purchase of a company by using a hig... Read more
Sales Commissions Dashboard
This model will allow you to plan your commission structure and monitor the performance of your sale... Read more
Dashboard Actuals PnL Model
!! Kindly use the latest Microsoft Excel Version before purchasing the model, otherwise, the dynamic... Read more
Private Equity Fund Model (Investor Cashflows)
Private Equity Financial Model to analyze fund cashflows and returns available to Limited Partners (... Read more
Reviews
These option concept have flexibility compare to common finance industry DCF
9 of 14 people found this review helpful.
Help other customers find the most helpful reviews
Did you find this review helpful?
You must log in to submit a review.