finance project Supplemental User’s Guide

COMPUTING RATIOS FROM BALANCE SHEET
11/09/2019
Impact on Investment-Management Strategies
11/09/2019

finance project Supplemental User’s Guide

This tutorial was written by student users as a supplemental guide for using eVal to create financial forecasts and valuations.

The tutorial is not intended to instruct users how to analyze these forecasts, or which variables to use. It is intended to help guide you through the steps of utilizing all of the features available in eVal so that users can make forecasts and valuations based on reasonable assumptions.

The tutorial is organized to correspond with the tabs in eVal. So, if help is needed with forecasting assumptions in eVal, simply click on the forecasting assumptions tab in the tutorial to find help.

Page Contents

2 Input Historical Data

3 Ratio Analysis

6 Ratio Analysis Graphs

6 Cash Flow Analysis

7 Forecasting Assumptions

7 Income Statement Forecasting

10 Balance Sheet Forecasting

13 Valuation Parameters

15 EPS Forecasts

15 Residual Income Valuation

15 Discounted Cash Flow Analysis

16 Model Summary

Input Historical Data

The first step required to begin working with eVal is to input a company’s historical data.

To do this, click on the “Input Historical Data” tab. Once this is done, a box with three options will pop up. The three choices are explained below:

1. Import Core Data – This is the input option that is going to be used 99.9% of the time, as it is the easiest method possible.

If this method of inputting data is selected, the user will be taken to a screen that lists thousands of publicly traded companies. eVal comes with a list of these companies including the ticker symbols, the industry, sectors, as well as a list of the recent financial statements and news.

To import the data, the user must enter the ticker symbol of the company that is to be researched in the text box, and click “go.” If the ticker symbol of the company is now known, either type the name of the company into the text box and click search, or search for the company’s name in the list manually. All companies are listed in alphabetical order.

2. Import Data From A Saved File – This option should ONLY be used if desired historical data are not already programmed into eVal, and may only be used if historical financial data is already saved in one of the following formats:

1. Data from Thompson Research (Global Access)

2. Data from WRDS

3. Data from Compustat template

4. Data from Market Guide template

After selecting the option of the desired format to import the information, the user will be asked to select the input file from somewhere on the hard drive, and once the user clicks “okay”, the information will be imported into eVal.

3. Input Data Manually – This option will be used if the company’s data are not available in eVal. After choosing to input data manually, the user will be given two options which are presented below:

A. Manually enter data for two or more historical years. This option will be by far the most tedious method of importing financial data, as the user will have to manually enter all of the historical financial data for the company.

Note: The user must enter AT LEAST two years of historical financial information for eVal to make useful forecasts.

B. Move existing data back one year and manually enter data for the most recent year. This option will be used once new financial information not already programmed into eVal becomes public.

Note: The account balances eVal chooses to use may not be the same as the account balances or names in the company’s actual financial statements. It is important to look at how eVal allocated past years’ data when deciding which dollar amounts should be placed in which accounts. Example: Some companies separate marketing costs from administrative costs. eVal puts these two accounts together, so the user must be aware of that and combine the two different accounts on the company’s balance sheet into one when inputting the data into eVal.

Ratio Analysis

Key:

BB = Beginning Balance

EB = Ending balance

#Div/0! – Denominator of the equation is 0

#N/A – Free Cash Flow or Net Income is Negative

The ratio analysis section of eVal breaks down the different account balances into a number of ratios, some of which are somewhat complicated.

The different ratio sections and their formulas and analysis are as follows:

1. Annual Growth Rates

This section of the ratio analysis is the expected growth rates for each of the accounts presented.

If the symbol “N/A” is found where the user would normally expect there to be a number, it means that the account balance for the year is negative and therefore eVal cannot make the calculation.

Note: The sustainable growth rate is defined as the rate of growth that can be sustained if there is no additional equity issued. The formula for calculating the sustainable growth rate is:

= ROE * (1 – Dividend Payout Ratio)

2. Profitability

The profitability ratios provided by eVal represent the company’s profitability in relation to the amount of common equity and net operating assets.

Return on Equity = Net Income / ((BB Common Equity + EB Common Equity) / 2)

Return on Equity (Before non-recurring items)

= Net Income – Extraordinary Items – Discontinued Operations –

Other Income (Loss) – ((EBT – Income Taxes) / Income Taxes)

(BB Common Equity + EB Common Equity) / 2

Note: If EBT is equal to 0, ((EBT – Income Taxes) / Income Taxes) is not subtracted from the numerator.

Return on Net Operating Assets

= Net Income – (Interest Expense * (1 – Effective Tax Rate)) – Preferred Dividends

((BB Common Equity + BB Preferred Stock + BB Current Debt + BB Long-term Debt +

EB Common Equity + EB Preferred Stock + EB Current Debt + EB Long-Term Debt / 2)

3. Basic DuPont Model

The basic Dupont model is a more complicated formula that calculates ROE using the following three ratios:

Net Profit Margin = Net Income / Sales

Total Asset Turnover = ((Sales / BB Total Assets + EB Total Assets) / 2)

Total Leverage = (BB Total Liabilities and Equity + EB Total Liabilities and Equity) /

(BB Total Common Equity + EB Total Common Equity)

Return on Equity = Net Profit Martin * Total Asset Turnover * Total Leverage

Note: Will Always Equal Net Income / ((BB Common Equity + EB Common Equity) / 2)

4. Advanced Dupont Model

The advanced Dupont model is an even more complicated and intensive way to calculate ROE. This model uses the following ratios:

Net Operating Margin

= (Net Income – Interest Expense * (1 – Effective Tax Rate) – Preferred Dividends) / Sales

Net Operating Asset Turnover

= Sales / (BB Common Equity + BB Preferred Stock + BB Long-Term Debt + BB Current Debt

+ EB Common Equity+ EB Preferred Stock + EB Long-Term Debt + EB Current Debt) / 2)

Return on Net Operating Assets

= Net Operating Margin * Net Operating Asset Turnover

Net Borrowing cost

= (Interest Expense * (1 – Effective Tax Rate) + Preferred Dividends) /

((BB Current Debt + BB Long-Term Debt + EB Preferred Stock +

EB Current Debt + EB Long-Term Debt + EB Preferred Stock) / 2)

Spread

= Return on New Operating Assets – Net Borrowing Cost

Financial Leverage

= (BB Current Debt + BB Long-Term Debt + BB Preferred Stock +

EB Current Debt + EB Long-Term Debt +EB Preferred Stock+

(BB Total Common Equity + EB Total Common Equity)

Return on Equity

= Return on Net Operating Assets + Financial Leverage* Spread

5. Margin Analysis

The margin analysis involves a set of different income statement margins.

Gross Margin = Gross Profit / Sales

EBITDA Margin

= EBITDA / Sales

EBIT Margin = EBIT / Sales

Net Operating Margin (Before reoccurring items)

= (Net Income – (Interest Expense * (1 – Effective Tax Rate)) – Preferred Dividends –

Extraordinary Items – Discontinued Operations – Other Income –

(Non-Operating Income * (1 – Effective Tax Rate)) / Sales

Net Operating Margin

= Net Income – (Interest Expense * (1 – Effective Tax Rate)) – Preferred Dividends / Sales

6. Turnover Analysis

Turnover analysis is a measure of the time it takes for assets such as inventories and receivables to “turn over.”

Net Operating Asset Turnover

= Sales / (BB Common Equity + BB Preferred Stock + BB Long-Term Debt + BB Current Debt+

EB Common Equity + EB Preferred Stock + EB Long-Term Debt + ERB Current Debt) / 2

Net Working Capital Turnover

= Sales / (BB Total Current Assets – BB Total Current Liabilities + BB Current Debt +

EB Total Current Assets – EB Total Current Liabilities + EB Current Debt) / 2)

Average Days to Collect Receivables

= (365 * ((BB Receivables + EB Receivables / 2) / Sales)

Average Inventory Holding Period

= (365 * (BB Inventory + EB Inventory) / 2) / (COGS)

Average Days to Pay Payables

= (365 * (BB Accounts Payable + EB Accounts Payable) / 2) /

(Cost of Goods Sold + EB Inventory – BB inventory)

PP&E Turnover

= (Sales / BB {{&E + EB PP&E) /2

7. Analysis of Leverage – Long-Term Capital Structure

Analysis of leverage is an analysis of the relationship between debt, equity, and cash flows.

Debt to Equity Ratio

= (Total Current Debt + Total Long-Term Debt) / Total Common Equity

Funds from Operations (FFO) to Total Debt

= (Funds From Operations / (BB Current Debt + BB Long-Term Debt +

EB Current Debt + EB Long-Term Debt) / 2)

Cash From Operations (CFO) to Total Debt

= (Cash From Operations / BB Current Debt + BB Long-Term Debt +

EB Current Debt + EB Long-Term Debt) / 2)

8. Analysis of Short-Term Liquidity

An analysis of short-term liquidity of the company.

Current Ratio = (Total Current Assets / Total Current Liabilities)

Quick Ratio = (Operating Cash and Marketable Securities + Accounts Receivable) /

Total Current Liabilities

EBIT Interest Coverage = (EBIT / Interest Expense)

EBITDA Interest Coverage = (EBITDA / Interest Expense)

9. Analysis of Earnings Quality

This section is an analysis of the quality of a firm’s earnings.

Note” A cell that is shaded red means that 90% of companies with similar ratios to the company you are evaluating saw a decrease in stock price.

10. Analysis of Credit Risk

This section is an analysis of the risk of default of a company.

Ratio Analysis Graphs

This section does not need any explanation. It is simply a graphical representation of the ratios calculated in the ratio analysis.

Cash Flow Analysis

The cash flow analysis is based upon the statement of cash flows, which is created from the income statement and the balance sheet.

The most important items of the cash flow analysis are the free cash flows that eVal calculates for the current and past years and the forecasted free cash flows.

Free Cash Flow to Common Equity

FCF to common equity is a measure of the net cash available to common equity shareholders.

Free Cash Flow to All Investors

FCF to all investors is a measure of the net amount of cash available to all investors (including common equity holders, preferred stockholders, and creditors).

Forecasting Assumptions

eVal has a built in function that allows it to forecast future financial statements. However, if the user feels that the forecast does not accurately reflect the company’s future prospects, eVal provides the capability to easily change the assumptions.

Note: Analysts are not expected to enter specific numbers. Instead, enter a percentage, which for the income statement, for example, is either a percentage of sales or cost of goods sold. Be sure to check which account is being forecasted.

Example: In entering the amount of inventory the company is expected to have, the analyst would not enter $5,000; instead, enter the percent of COGS you expect inventory to represent.

To start, go to the User’s Guide (by clicking on the gray button in the upper left hand corner of the screen). Once there, click on the “Input Forecasting Assumptions” button. The user will be presented with three choices: 5 years, 10 years, 20 years.

5 years – Selecting 5 years will allow the analyst to input data for the next 5 years, and will use the terminal growth rate for the year following the five year input window.

Selecting this option is a good option for very large, mature companies that are expected to have relatively low, but stable growth over the coming years.

10 years – Selecting 10 years will allow the user to input data for the next 10 years, and will use the terminal growth rate for the years following the 10 year input window.

Selecting this option is good for mid-size companies that are expected to continue growing at a relatively high rate over the next 10 years but then level off as the company or industry matures.

20 years – This option is ideal for fast growing companies or fast growing industries. Because of the rapid growth these companies usually experience, it is important to forecast rapid sales growth for many years into the future.

Terminal Growth Rate – The rate of growth assumed to be constant after the end of the forecasting period. THE TERMINAL GROWTH RATE SHOULD NEVER BE LARGER THAN 3 to 4%. If the rate was higher, the company would be growing faster than the economy and take over the world.

Income Statement Forecasting

Users should be critical of each and every one of the income statement forecasts provided by the eVal program. This section is intended to help users better forecast the different line items on the income statement by identifying key influences and providing direction to locate guidance that the firms typically provide. As a general rule of thumb, near-term performance and forecasts are usually driven by firm-specific activities, while long-term performance is seen as the result of industry wide macro-economic factors. As a note of caution, the inputs in this section have consequences on both the financial ratio and cash flow sections. If the outputs in these sections seem unrealistic, which is typical for the first time through, users should adjust the forecasts accordingly.

Sales Growth

One key indicator of future sales is the firm’s current and future investments, such as new sales locations, recent promotional campaigns, or new products.

When a large amount of capital is invested there is a large boost in sales followed by a steady stream of future sales. This general pattern is the reason why the eVal program smooths sales between the user’s initial forecast and the terminal growth rate. However, users may want to eliminate the smoothing function by entering forecasts for each year depending on the company’s specific situation. The terminal growth rate should never be larger than GDP growth, otherwise the firm would theoretically take over the world.

A good resource for obtaining information pertaining to sales growth is the segment disclosure in the financial statements. It describes sales, profits, and investments broken out by product lines and geographic regions. The Management Discussion and Analysis (MD&A) section from the company’s Form 10K report also describes its future growth prospects.

Cost of Goods Sold / Sales

Some questions about the company’s products or services to think about are:

Can it charge a premium price over its competitors?

Can it sustain the pricing premium in the long run?

Can the firm lower production costs through manufacturing efficiencies?

Competition with the industry and sector have a large impact on the forecast.

The firm’s past COGS / Sales ratio and the past COGS / Sales of its closest competitors are good sources to examine when making a forecast. Also, the MD&A and earnings announcement should provide additional guidance.

Research and Development Expenses / Sales

This ratio is loosely tied to the firm’s stage in its life cycle, meaning that start-up firms will have larger research and development (R&D) expenses, which they will attempt to reduce over time. An exception to the rule would be a pharmaceutical company because it perpetually tries to replenish its pipeline through R&D no matter how mature the company.

A firm that has a strategy to develop new products and sell them at a premium usually has higher R&D expenses in relation to a firm that has a strategy to copy products and sell them at a discount.

The topic of R&D should be discussed in the company’s financial statements.

Selling, General and Administrative Expenses / Sales

Most of the expenditures in this section are highly discretionary and therefore difficult to predict.

The user should analyze how this ratio has changed in response to changes in the sales growth rate in order to detect evidence of economies of scale.

The MD&A section of the Form 10K report should provide guidance for future changes in this ratio, especially if the firm is planning to implement a cost-cutting initiative.

Depreciation and Amortization / Average PP&E and Intangibles

The footnote in the financial statements that describes the firm’s accounting policies will often give the useful lives of the major types of assets. Obviously, if a company only has one major type of asset then it will be more helpful than if there are multiple asset types.

Two problems that arise with this line item are:

1. Gross PP&E is usually in the financial statements while gross intangibles is not, and neither is on the face of the balance sheet. This is not a problem for stable firms, but may cause a problem for growing firms.

2. Depreciation and Amortization is not shown on the income statement and is coded as zero by some standardized data providers. It appears on the Statement of Cash Flows, but it is difficult to discern into which income statement category the company has lumped them.

Check Chapter 8, Forecasting Details of the Equity Valuation & Analysis textbook (Lundholm and Sloan) to find out how to deal with these problems.

Interest Expense / Average Debt

A good indicator of future borrowing rates are the past rates at which the firm has borrowed money, that is unless the user feels that there will be a change in the firm’s default risk or macro-level interest rates.

Two things to look out for in this section are interest income that is netted against the company’s interest expense and convertible debt. Both of these items will make past ratios seem lower than they should be.

Non-Operating Income / Sales

Non-operating income includes dividends received, interest income from investments, write-down of assets, and other miscellaneous income.

If the non-operating income is from a financial asset the user should question whether or not the asset will exist in the future. For example, if interest income is produced due to recently raised capital and the analystfeels that the cash will soon be invested in operating assets, then the analyst should not continue to include it.

If built up cash is invested in another company, which produces dividend income or equity method income that will continue in the future, then the user should forecast the non-operating income as a dollar amount and input the result directly on eVal’s Financial Statement sheet.

If there are a string of impairment and restructuring expenses in a company’s past financial statements, then it is likely that the expense will continue in the future.

Effective Tax Rate

The statutory tax rate for most firms in the United States is 35 percent. The effective tax rate is the statutory tax rate plus local taxes minus tax advantages from foreign operations.

The tax footnote in the company’s financial statements contains a table that explains the discrepancy between the effective tax rate and the statutory tax rate.

For a company that is producing a before tax loss, the current effective tax rate may be a poor indicator of the future tax rate. If the losses create net operating loss carryforwards, then the company’s future effective tax rate will likely be zero. However, if the user forecasts that the company will become profitable in the future, the effective tax rate will remain zero for a few years because of its net operating loss carryforwards, but will eventually increase to a normal rate.

Minority Interest / After Tax Income

Minority interest represents the claim on the income of the consolidated firm by the shareholders in the minority-owned subsidiaries.

For most companies this number is zero because it owns 100 percent of its subsidiaries. However, if it is not zero and it is not changed it can cause a forecast mistake.

There is no reason why this forecast will remain constant, which is why e-Val uses a scaling variable as an alternative.

Other Income / Sales

If this item is not zero, then the user should look through the financial statements and find out what items are included in order to decide whether or not they will continue.

Note: This line item is a good place to make any major adjustments the user might want make to the financial statements.

Preferred Dividends / Average Preferred Stock

This item can be found in the financial statement footnotes, or it can be inferred from the statement of shareholders’ equity.

If there is a historical balance of preferred stock on the balance sheet and this ratio is zero, the user should find the dividend rate from the financial statements and input the preferred dividends manually into eVal’s Financial Statements sheet.

Balance Sheet Forecasting

Just as explained in the Income Statement Forecasting section of this guide, users should be critical of each and every one of the balance sheet forecasts provided by the eVal program. Again, as a note of caution, the inputs in this section have consequences on both the financial ratio and cash flow sections. If the outputs in those sections seem unrealistic, which is typical for the first time through, users should adjust the forecasts accordingly.

Ending Operating Cash / Sales

If a firm has historically held a large percentage of cash relative to its peers, it may indicate that part of the balance is an investment in financial assets rather than a necessary cash reserve.

The as-reported financial statements should provide a sufficient estimate for how much of the balance is operating cash and how much is an investment in financial assets.

There are three options for dealing with this situation:

1. If you feel that the firm will hold the financial assets, then the user can forecast a high operating cash / sales ratio and include interest income in non-operating income from the financial assets.

2. Analysts can reclassify the financial assets portion of the balance into Investments and forecast the line item separately.

3. The third and most common choice for traditional DCF models is liquidating the financial assets in the first forecasting period. This approach allows the user to focus on forecasting the operating variables. The user can accomplish this in eVal by assuming a low operating cash / sales ratio, which will proportionately lower levels of debt and equity. This may or may not result in a large stock repurchase for the firm. See Chapter 8 of the textbook (Lundholm and Sloan) for a discussion of the reasoning associated with these adjustments.

Ending Receivables / Sales

This ratio depends directly on a firm’s collection policy, as well as its customers’ ability to pay.

If a firm’s historical receivables / sales ratio is relatively constant, it should be a good indication that it is unlikely to change in the future. However, if the ratio is erratic, the user should investigate the firm’s policies more closely. A question the analyst may want to ask is, what is the firm’s relative bargaining power with customers?

Ending Inventories / COGS

An increasing Inventory / COGS ratio is considered to be a red flag that the company is having difficulty selling its goods.

Here are some questions to ask that may help to forecast this ratio:

1. Is it anticipated that the company implementing a “just in time” inventory system, which should decrease this ratio?

2. Does the analyst sense that the firm’s customers or suppliers have superior bargaining power that would allow them to force the company to hold its inventory for increased periods of time?

3. Was there an unusual event in the most recent period that caused the ratio to fluctuate significantly from its normal level?

Ending Other Current Assets / Sales

Other current assets include tax refunds, prepaid expenses, and other miscellaneous items.

This ratio tends to increase with the size of the firm. However, if is a large amount, the analyst should look at the financial statements in order to reveal the line item’s composition and decide whether or not it should actually move with sales.

Ending Accounts Payable / COGS

Because accounts payable is basically an interest free loan to the firm, the higher this ratio the better. Of course, this assumes that the company actually has the means to pay back the loan.

An increase in the user’s forecast should be because the company has bargaining power over its suppliers. In other words, the company can delay paying its bills.

Ending Taxes Payable / Sales and Ending Other Current Liabilities / Sales

Similar to Other Current Assets / Sales ratio, both of these ratios are forecasted as a percentage of sales because it is assumed that they roughly increase with the size of the firm. Also, users should consult the financial statements in order to reveal the composition of the line items and decide whether or not they should be tied to sales.

Other current liabilities includes dividends declared but not yet paid, customer deposits, unearned revenue, and other miscellaneous liabilities.

Ending Net PP&E / Sales

A good source of information that can be used to forecast this ratio is the discussion of liquidity and capital resources in the MD&A section of the Form 10K report. Some companies even give estimates for future capital expenditures. Capital-intensive industries such as steel, auto making, and airlines often give capacity utilization statistics in the Selected Date Schedule (Item 6 on the Form 10K). Finally, users can get industry-level statistics on growth rates in investments in different classes of assets from the Bureau of Economic Analysis fixed asset tables.

PP&E tends to rise during a company’s early years and then remains constant. However, this does not mean that the ratio will rise and flatten out. What users really need to do is decide whether or not there are economies to scale that the firm will enjoy, then forecast based on that information.

Ending Investments / Sales

Investments primarily refers to equity holdings the firm may have in other companies. If the number is large, users should look through the financial statements to see what the investments represent.

The eVal program forecasts this item as a percentage of sales, but there may be no reason why this should be the case. This is where investigation into the financial statements will be beneficial.

Ending Intangibles / Sales

What users really need to forecast this line item is purchased intangibles. This includes purchased patents, copyrights, licenses, and trademarks.

The fact that goodwill, the most common purchased intangible, comes in large, unpredictable lumps, and the distinction between purchased and internally developed intangible assets is so arbitrary, makes this line item very hard to forecast.

The reason that this ratio is a percentage of sales is because larger firms tend to have more intangibles than smaller firms, not because it is necessarily tied to sales.

Ending Other Assets and Other Liabilities / Sales

Other assets include long-term receivables, pre-opening expenses for retail stores, and pension assets.

Other liabilities include pension liabilities and other miscellaneous non-current liabilities.

The financial statement footnotes are a good place to investigate these line items if the firm has a significant amount.

Deferred Taxes / Sales

A company’s specific deferred tax items can be found in the footnotes to the financial statements. The most prevalent source of deferred tax liability arises from the timing difference between depreciation on PP&E and the tax deductions for these investments.

The ratio will increase slightly if the asset base is growing, but if the user forecasts that the firm will decrease its asset base, the ratio will fall dramatically.

Current Debt / Total Assets, and Long-Term Debt / Total Assets

Current debt is short-term borrowing plus the current portion of long-term debt that is due within a year.

Long-term debt, combined with the current debt, is the firm’s total debt financing.

Details about the company’s debt contracts can be found in the footnotes to the financial statements. Three items of particular interest are:

1. The discussion of short-term borrowing.

2. The allocation of total debt to current and non-current portions.

3. The schedule of future maturities of existing debt.

Minority Interest / Total Assets

There is no reason why minority interest should remain constant with total assets, other than the fact that larger firms tend to have larger minority interests.

A firm does not typically have minority interests, but if it does, then it is imperative that the user investigate this ratio. It is extremely difficult to predict.

Preferred Stock / Total Assets

Users may want to forecast this line item to remain a constant dollar value, rather than a constant percentage of total assets. That is, unless the company specifically says that it intends to continue issuing preferred stock.

Dividend Payout Ratio

Note that the forecasting assumptions already entered into eVal imply the net amount of new common equity that will be issued or discharged. That is, since the future equity balances are already determined, this assumption can only change the composition of the equity.

This item is very important, especially when it comes to the cash flow analysis.

Valuation Parameters

This is an important section of eVal. The section requires the user to enter the variables that are used in the stock valuation process. Each of these variables is discussed below.

Cost of Equity Capital

Just because a company is not paying out dividends or repurchasing stock does not mean that the capital provided by shareholders is free.

It is management’s responsibility to maximize shareholder wealth, and the return required by the shareholders, dependent upon market conditions and risk, is the cost the company must bear.

The cost of equity capital can be estimated using the following equation, known as the Capital Asset Pricing Model (CAPM).

= Risk Free Rate + ((Expected Market Return – Risk Free Rate) * Beta)

The Risk Free Rate used is typically the interest rate on a long-term Government bond (10 year bond).

(Expected Market Return – Risk Free Rate) is also known as the Market Risk Premium and is a measure of the average market return over the risk free rate, it has historically averaged 5% to 6%.

The beta is the main variable in the equation. The beta is a measure of risk, and as risk increases so does the beta, so it makes sense that as the risk of a stock increases, so should the required rate of return, and therefore the cost of equity capital.

Value of Contingent Claims on Equity

This value is defined as claims on equity that are not currently factored into the stock price or the financial statements. The most important aspect of these claims, for the purpose of this valuation, are stock options. Fortunately, instead of making the user calculate the possible claims on equity, eVal has a function that does it automatically.

First, click on the gray box towards the top of the screen labeled “Go to Contingent Claims Calculator.”

That will bring up a screen with several input options.

The current stock price input is self-explanatory.

All of the other input variables are found in the stock option footnote of a company’s Form 10K report.

The three numbers that may be more difficult to find are the exercise price, the years to expiration, and the number of shares subject to claim.

Unfortunately, companies issue stock options to many executives and others, so in order to calculate the correct number to input here, it is necessary to calculate a weighted average exercise price and years to expiration.

The following is a simple example to demonstrate how to do this:

Example Exercise Price Years to Expiration # of Shares $ of Total Shares
Executive A $40 4 600 42.86%
Executive B $30 5 500 35.71%
Executive C $25 6 300 21.43%
Total $95 15 1400

Weighted Average Stock Price

= ($40 * 42.86%) + ($30 * 35.71%) + ($25 * 21.43%)

= $33.21

Weighted Average Years to Expiration

= (4 * 42.86%) + (5 * 35.71%) + (25 * 21.43%)

=4.78571

Number of Shares

=1,400

Note: The total value of contingent claims will not be automatically carried over to the valuation parameters section, the user must manually enter the value into the appropriate input box.

Date of Valuation

This is the date that the user performs the valuation (today).

Dilution Factors for Splits

This number is required if the company has issued a stock split since the last issuance of an annual report.

If it has, the number that is required in this box is the ratio of the stock split. For example, if the company issued a 3 for 2 stock split, the number would be 1.5 (3 / 2).

The only effect of not entering a number here when there should be (for a 2 for 1 stock split) would be a stock price twice as large as what eVal is actually predicting.

Cost of Debt

eVal automatically calculates the cost of debt, but if the user thinks the number should be different, there is an option to change it here.

Cost of Preferred Stock

Like the cost of debt, this is a pre-calculated number, but the user has the option to change it if the user believes that eVal did not accurately calculate it.

EPS Forecasts

This section of the analysis forecasts future earnings ;per share based on income and common equity issuance and repurchase assumptions.

It is often helpful to obtain actual analysts’ forecasts to compare to the numbers calculated by eVal.

Residual Income Valuation

The residual income valuation is very similar to a discounted cash flow valuation, except that it discounts residual income rather than cash flows.

Residual Income is calculated as follows:

Residual Income = Net Income – (BB Common Equity * Cost of Common Equity)

Discounted Cash Flow Analysis

This section is self-explanatory.

The model simply discounts the pre-calculated (in the cash flow analysis section) future cash flows of the company and divides the combined total by the total number of shares outstanding to determine the stock price.

Note: The stock price calculated using the DCF Valuation will always be the same as the stock price calculated using the residual income valuation method.

Model Summary

The left half of the model summary screen shows all of the inputs currently being used to create the forecasts and to determine the stock price.

The right half of the screen is also important, it is the sensitivity analysis.

The sensitivity analysis allows the user to change key variables to see how the stock price will react if the variables currently being used do not turn out to be correct.

Note: Changing variables in the sensitivity analysis will not change them throughout the entire model. It will only change them in this screen for the purpose of analyzing the effects on the stock price.

Changing the forecast horizon by clicking one of the boxes for either 5, 10, or 20 years will show the user the expected stock price that many years in the future.

To reset the variables back to where they are currently in the model, just click the Reset to Current button.

2

WhatsApp Chat with us on Whatsapp