| An apology: I apologize for the number of inputs that are required on this sheet. Many of the inputs are required only if you choose the appropriate option, though. |
|
|
|
|
| If you have negative operating income, you will either have to normalize it to make it positive, or use the highgrowth.xls spreadsheet. |
|
|
|
|
| Master Input Sheet |
|
|
|
|
| Do you want to capitalize R&D expenses? |
No |
! Yes or No |
Go to R&D Converter |
|
| Do you want to convert operating leases to debt? |
Yes |
! Yes or No |
Go to Operating lease converter |
|
| Do you want to normalize operating income? |
No |
|
Go to Earnings Normalizer |
|
| |
|
|
|
|
| Inputs |
|
|
|
|
| From Current Financials |
|
|
|
|
| Current EBIT = |
$1,071.00 |
! If negative, go back and choose to normalize earnings. |
|
|
| Current Interest Expense = |
$187.00 |
|
|
|
| Current Capital Spending |
$650.00 |
|
|
|
| Current Depreciation & Amort'n = |
$525.00 |
|
|
|
| Tax Rate on Income = |
38.00% |
Previous year-end |
|
|
| Current Revenues = |
$13,212.00 |
8488 |
|
|
| Current Non-cash Working Capital = |
($404.00) |
|
|
|
| Chg. Working Capital = |
($700.00) |
Previous year-end |
|
|
| Book Value of Debt = |
$0.00 |
0 |
|
|
| Book Value of Equity = |
$11,722.00 |
7191 |
|
|
| |
|
|
|
|
| Cash & Marketable Securities = |
$2,016.00 |
|
|
|
| Value of Non-operating Assets = |
$7,032.00 |
|
|
|
| |
|
|
|
|
| Market Data for your firm |
|
|
|
|
| Is your stock currently traded? |
Yes |
|
|
|
| If yes, enter the following: |
|
|
|
|
| Current Stock Price = |
$64.88 |
|
|
|
| Number of shares outstanding = |
6,890.00 |
|
|
|
| Market Value of Debt = |
$0.00 |
|
|
|
| If no, enter the following |
|
|
|
|
| Would you like to use the book value debt ratio? |
No |
|
|
|
| If no, enter the debt ratio to use in valuation |
35% |
|
|
|
| |
|
|
|
|
| General Market Data |
|
|
|
|
| Long Term Riskfree rate= |
4.25% |
|
|
|
| Risk premium for equity = |
4.91% |
|
|
|
| |
|
|
|
|
| Ratings |
|
|
|
|
| Do you want to estimate the firm's synthetic rating = |
No |
! If yes, use the rating estimator worksheet that is attached |
|
|
| If yes, choose the type of firm |
2 |
|
|
|
| If not, what is the current rating of the firm? |
BBB |
|
|
|
| Enter the cost of debt associated with the rating = |
5.75% |
|
|
|
| |
|
|
|
|
| Options |
|
|
|
|
| Do you have equity options (management options, warrants) outstanding? |
Yes |
|
|
|
| If yes, enter the number of options |
439.00 |
|
|
|
| Average strike price |
$22.52 |
|
|
|
| Average maturity |
6.8 |
|
|
|
| Standard Deviation in stock price |
40% |
|
|
|
| Do you want to use the stock price to value the option or your estimated value? |
P |
|
|
|
| |
|
|
|
|
| Valuation Inputs |
|
|
|
|
| High Growth Period |
The questions below, especially the yes or no ones, can be confusing. Please read the comments on the input cells. |
|
|
|
| Length of high growth period = |
12 |
|
|
|
| Beta to use for high growth period for your firm= |
1.43 |
|
|
|
| Do you want to keep the debt ratio computed from your inputs? |
Yes |
|
|
|
| If yes, the debt ratio that will be used to compute the cost of capital is |
#NAME? |
|
|
|
| If no, enter the debt ratio that you would like to use in the high growth period |
7.00% |
|
|
|
| Do you want to keep the existing ratio of working capital to revenue? |
No |
|
|
|
| If yes, the working capital as a percent of revenues will be |
-3.06% |
|
|
|
| If no, enter the ratio of working capital to revenues to use in analysis |
12% |
|
|
|
| Do you want to compute your growth rate from fundamentals? |
Yes |
|
|
|
| If no, enter the expected growth rate in operating income for high growth period |
15% |
|
|
|
| If yes, the inputs to the fundamental growth calculation (based upon your inputs) are |
|
|
|
|
| Return on Capital = |
8.49% |
|
|
|
| Reinvestment Rate = |
1.30% |
|
|
|
| Do you want to change these inputs? |
No |
|
|
|
| Return on Capital = |
16.00% |
|
|
|
| Reinvestment Rate = |
80.00% |
|
|
|
| |
|
|
|
|
| Do you want me to gradually adjust your high growth inputs in the second half? |
Yes |
|
|
|
| |
|
|
|
|
| Stable Growth Period |
|
|
|
|
| Growth rate during stable growth period = |
4.00% |
|
|
|
| Beta to use in stable growth period = |
1.00 |
|
|
|
| Risk premium for equity in stable growth period = |
4.91% |
|
|
|
| Debt Ratio to use in stable growth period = |
#NAME? |
|
|
|
| Pre-tax cost of debt in stable growth period = |
5.25% |
|
|
|
| Tax Rate to use in stable growth period = |
35.00% |
|
|
|
| To compute the reinvestment rate in stable growth, you have two options |
|
|
|
|
| Do you want to compute reinvestment needs in stable growth based on fundamentals? |
Yes |
|
|
|
| If yes, enter the return on capital that the firm will have in stable growth |
12.00% |
|
|
|
| If no, enter capital expenditure as % of depreciation in stable growth |
120% |
(in percent) |
|
|
| |
|
|
|
|