There should be a check against the iteration box. If there is not, you will get circular reasoning errors.

If you want to capitalize R&D, you have to input the numbers into the R&D worksheet.

If you have operating leases, please enter your lease commitments in the lease worksheet below and I will convert to debt

Computed numbers: Here is what your company's numbers look like, relative to industry.

If you are not working in US dollars, you should add the inflation differential to the industry averages.

Industry (Global data)

Revenue growth in the most recent year =

Compounded annual revenue growth rate over next 5 years =

Pre-tax operating margin in the most recent year =

Target pre-tax operating margin (EBIT as % of sales in year 10) =

Sales to capital ratio in most recent year =

Speed of convergence level

Return on invested capital in most recent year=

Efficency of Growth Lever

Standard deviation in stock prices =

Cost of capital =

Revenues in year 10, based on your revenue growth =

Pre-tax Operating Income in year 10, based on your operating margin =

Return on invested capital in year 10, based on your sales/capital ratio =

Check the Diagnostics worksheet for more details.

In stable growth, I will assume that your firm will have a cost of capital similar to that of typical mature companies (riskfree rate + 4.5%)

Mature companies generally see their risk levels approach the average

Though some sectors, even in stable growth, may have higher risk.

I will assume that your firm will earn a return on capital equal to its cost of capital after year 10. I am assuming that whatever competitive advantages you have today will fade over time.

Mature companies find it difficult to generate returns that exceed the cost of capital

But there are significant exceptions among companies with long-lasting competitive advantages.

I will assume that your firm has no chance of failure over the foreseeable future.

Many young, growth companies fail, especially if they have trouble raising cash. Many distressed companies fail, because they have trouble making debt payments.

Tough to estimate but a key input.

B: Book value of capital, V= Estimated fair value for the company

Enter the distress proceeds as percentage of book or fair value

This can be zero, if the assets will be worth nothing if the firm fails.

I will assume that your effective tax rate will adjust to your marginal tax rate by your terminal year. If you override this assumption, I will leave the tax rate at your effective tax rate.

I will assume that you have no losses carried forward from prior years ( NOL) coming into the valuation. If you have a money losing company, you may want to override tis.

Check the financial statements.

An NOL will shield your income from taxes, even after you start making money.

I will assume that the growth rate in perpetuity will be equal to the risk free rate. This allows for both valuation consistency and prevents "impossible" growth rates.

This can be negative, if you feel the company will decline (and disappear) after growth is done. If you let it exceed the risk free rate, you are on your own in uncharted territory.

I have assumed that none of the cash is trapped (in foreign countries) and that there is no additional tax liability coming due and that cash is a neutral asset.

If yes, enter trapped cash (if taxes) or entire balance (if mistrust)

Cash that is trapped in foreign markets (and subject to additoinal tax) or cash that is being discounted by the market (because of management mistrust)

& Average tax rate of the foreign markets where the cash is trapped

Additional tax rate due on trapped cash or discount being applied to cash balance because of mistrust.

EBIT (Operating) margin

EBIT (Operating income)

Cumulated discount factor

Terminal cost of capital

PV (CF over next 10 years)

Value of operating assets =

+ Non-operating assets

Value of equity in common stock

Estimated value /share

Operating margin (b)

Sales to capital ratio =

And a decrease in the cost of capital.

Traling 12 month

Traling 12 month

Value of operating assets =

Estimation of Current Cost of Capital

Operating Countries ERP calculator

If direct input, enter levered beta (or regression beta)

What approach do you want to use to input ERP?

Operating Regions ERP calculator

Latin America & Caribbean

Small Asia (No India, China & Japan)

Multi Business (US Industry Averages)

Estimated Value

Estimated Value of Straight Debt in Convertible =

Preferred Stock

Multi Business (Global Industry Averages)

Estimated Value

Valuing Options or Warrants

Enter the current stock price =

Enter the strike price on the option =

Enter the expiration of the option =

Enter the standard deviation in stock prices =

Enter the annualized dividend yield on stock =

Enter the treasury bond rate =

Enter the number of warrants (options) outstanding =

Enter the number of shares outstanding =

Do not input any numbers below this line

VALUING WARRANTS WHEN THERE IS DILUTION

Stock Price=

# Warrants issued=

Strike Price=

# Shares outstanding=

T.Bond rate=

Expiration (in years) =

Annualized dividend yield=

Div. Adj. interest rate=

Value per option =

Value of all options outstanding =

This spreadsheet converts R&D expenses from operating to capital expenses. It makes the appropriate adjustments to operating income, net

income, the book value of assets and the book value of equity.

Over how many years do you want to amortize R&D expenses

! If in doubt, use the lookup table below

Enter the current year's R&D expense =

The maximum allowed is ten years

Enter R& D expenses for past years: the number of years that you will need to enter will be determined by the amortization period

Do not input numbers in the first column (Year). It will get automatically updated based on the input above.

R& D Expenses

! Year -1 is the year prior to the current year

! Year -2 is the two years prior to the current year

Amortization this year

Value of Research Asset =

Amortization of asset for current year =

Adjustment to Operating Income =

! A positive number indicates an increase in operating income (add to reported EBIT)

Tax Effect of R&D Expensing

Operating lease expense in current year =

Operating Lease Commitments (From footnote to financials)

Commitment

! Year 1 is next year, ….

6 and beyond

Pre-tax Cost of Debt =

! If you do not have a cost of debt, use the synthetic rating estimator

Number of years embedded in yr 6 estimate =

! I use the average lease expense over the first five years

to estimate the number of years of expenses in yr 6

Converting Operating Leases into debt

Commitment

Present Value

6 and beyond

! Commitment beyond year 6 converted into an annuity for ten years

Debt Value of leases =

Restated Financials

Depreciation on Operating Lease Asset =

! I use straight line depreciation

Adjustment to Operating Earnings =

! Add this amount to pre-tax operating income

Adjustment to Total Debt outstanding =

! Add this amount to debt

Adjustment to Depreciation =

Inputs for synthetic rating estimation

Please read the special cases worksheet (see below) before you use this spreadsheet.

Before you use this spreadsheet, make sure that the iteration box (under calculation options in excel) is checked.

Enter the type of firm =

Enter current Earnings before interest and taxes (EBIT) =

(Add back only long term interest expense for financial firms)

Enter current interest expenses =

(Use only long term interest expense for financial firms)

Enter long term risk free rate =

Interest coverage ratio =

Estimated Bond Rating =

Note: If you get REF! All over the place, set the operating lease commitment question in cell F5

Estimated Company Default Spread =

to No, and then reset it to Yes. It should work.

Estimated County Default Spread (if any) =

Estimated Cost of Debt =

If you want to update the spreads listed below, please visit http://www.bondsonline.com

For large manufacturing firms

For smaller and riskier firms

Financial Svcs. (Non-bank & Insurance)

Heathcare Information and Technology

Oil/Gas (Production and Exploration)