Finance
Yinkus811
easyintrinsicvalueinstructions.docx
Original article:
http://www.aaii.com/computerized-investing/article/calculating-intrinsic-stock-value-in-excel
Calculating Intrinsic Stock Value in Excel
What is it worth? Will I pay too much? How much price safety should I build into my buy price? How does its fair value compare to the value of other opportunities? At what price should I consider selling it? Is it priced as a bargain or it is greatly overpriced?
We are talking about the intrinsic value per share of a stock as a measure of its worth—a value free of emotions or other biased influences. It is the value a knowledgeable owner would sell to a knowledgeable buyer. The biasing influences one can encounter include psychology, expectations, insider information, manipulation, current events, erroneous assumptions, etc.
In this article, I describe an Excel spreadsheet that I designed in order to estimate the unbiased worth of a company and, subsequently, its intrinsic value per share. The model gives an investor the ability to perform independent analysis in order to determine whether the stock’s intrinsic value exceeds the market price by a comfortable margin of say 15%, even 80% or more. This analytical tool is one way to detect mispricing.
Resources used to verify the workings of the Excel model are listed at the end of this article.
Common Approaches to Estimating Intrinsic Value
There are different ways to estimate the intrinsic value of a stock. Depending upon the input data, the assumptions and model complexity, each method could yield a different result. AAII’s June 2016 SI News describes valuation methods that use stock multiples.
Another method uses an appraisal type of approach to estimate the worth of a company and then its intrinsic value per share. Such a model uses past company performance data, market statistics and other important economic variables to perform the calculations.
Syndicated columnist and teacher Lauren Rudd has often stated that one of his priority criterion when evaluating investment candidates is to determine whether or not the stock’s intrinsic value is greater than its market value. Rudd likes intrinsic value to exceed the market quote by a minimum of 10% to 15%. One of the valuation calculators that he refers his readers and students to is the ValuePro calculator. However, since the investor cannot independently replicate the intrinsic value calculations, the ValuePro calculator is considered a “black box” calculator.
(Using the same numerical data set as in the ValuePro black box calculator, my Excel model calculations and the ValuePro calculations at each of the 10 excess years into the future match. However, it should be noted that the ValuePro RECALCULATE button may have to be clicked in order for that valuation to match my Excel calculation.)
By having the freedom to vary the model’s numerical data inputs, such as the company’s projected growth rate, future revenues, and the amount of short- or long-term debt the company might be taking on, plus economic data such as the movement of Treasury yields, an investor can perform “what-if” calculations to gauge the significance of economic changes and how a mix of different scenarios could influence the stock’s cash flow and ultimately the intrinsic value. This makes Microsoft Excel a powerful tool for an individual investor, a money manager or even a corporation.
When realistic data sets are used, the intrinsic valuation model is void of gut feelings, emotions and biased influences. Consequently, the data inputs that the investor uses to drive the model’s calculation engine must be carefully evaluated. Useful discussions are presented in the video “ Calculate Intrinsic Value With ValuePro.net - Value Investing ” by Ryan Ruppert.
An Overview of the Intrinsic Value Spreadsheet
The Intrinsic Value spreadsheet is my version of the valuation model and can be downloaded for your use. Please note that if you make changes that shift the rows or cells, the cell references in this article may no longer apply.
In this article, sections of the spreadsheet are highlighted in separate figures. When you download the Intrinsic Value spreadsheet, you will be able to print the whole spreadsheet on one legal-size page.
For hypothetical stock XYZ, Figure 1 shows all the data inputs needed to drive the Excel model (cells G9 to X9, which are highlighted in yellow, require manual input), Figure 3 includes all the intermediate calculations (Rows 16 to 68) needed to calculate intrinsic values, and Figure 2 includes the column of resulting year-by-year intrinsic values per share for the company (cells Q42:Q51). The 10 years of projected intrinsic values for company XYZ are plotted in the graph within the spreadsheet, shown in Figure 5 . The graph shows how the valuation changes each year.
Short descriptive headings are placed above each cell or group of cells. For example, the heading above cells C41:C51 in Figure 2 reads Projected Revenues ($ Mil). The heading above cells B41:B51 reads Excess Period N. For example, for an excess period of four years, N=4. Excess periods are future time periods; choosing the amount of excess time periods to evaluate is based on the user’s assessment of how long it will take the subject company to reach a “mature” phase.
Although the calculations are usually performed for 10 years into the future (within this spreadsheet), this length of time may not be valid for some companies. Tutorial 2 of “ Discounted Cash Flow Analysis ,” by Ben McClure on Investopedia gives a good guideline on how far into the future the projected valuation and resulting intrinsic value calculation might be useful with respect to a company’s maturity. Also, an investor’s time horizon may be less than 10 years.
Table 1 summarizes excess periods for three different “types” of companies—slow-growing, solid and outstanding growth—as described in McClure’s Tutorial 2. For example, if your candidate company (stock XYZ) is similar to the #2 solid company described in Table 1 , the projected intrinsic value shown in cell Q46 of the spreadsheet for the period N=5 (cell P46) is the intrinsic value you should use. Likewise, for each of the excess periods N = 1, 2, …. , 9, 10, the intrinsic value shown opposite its matching excess period is the one to use.
In this Excel model, our first priority is to find the value of future annual cash flows to the firm in today’s money. We start with the company’s recent operational revenues and use the growth rate of revenues (%) to project the company revenues for the next 10 years. Using the company’s revenue growth rate, the profits are determined next. Several deductions—such as taxes, operating costs, working capital requirements and net capital investments (or capital expenditures)—are made in order to determine how much cash is estimated to be available to the firm. This is called free cash flow for the firm (FCFF). The annual stream of FCFF money is what we call king.
Steps to Calculating Intrinsic Value
Step 1: Inputs
Input the values highlighted in yellow in cells E9 through X9.
Insight into evaluating data inputs and how each might be varied can be found at Valuepro , in the Ruppert video and in the book “ Streetsmart Guide to Valuing a Stock ,” by Gary Gray, Patrick J. Cusatis and J. Randall Woolridge (McGraw-Hill, 2004). The seven Valuepro tutorials are very useful and the Ruppert video shows, in a streamlined way, how to quickly find on the internet the needed input data by using free internet tools such as Google Finance , Yahoo Finance and the U.S. Department of the Treasury . Websites such as Investopedia are a great help for definitions, explanations and formulas.
Referring to Figure 1 , the input data set needed for this spreadsheet (highlighted in yellow in the actual spreadsheet) consists of the following.
Cell E9: Stock Symbol
This is the ticker symbol for the company’s stock.
Cell F9: Excess Return Period (years)
This is the number of years into the future that the Excel model forecasts cash flow for a company. As shown in Table 1 , the valid forecast period for a company might be one year, six years or 10 years, the longest period in this Excel model.
Table 1. Excess Return Periods for Different Types of Companies
Source: “ Discounted Cash Flow Analysis ,” Tutorial 2, by Ben McClure, Investopedia.
Cell G9: Annual Revenues ($ Mil) This is the sum total of operating income for the reported tax years, as found in the company income statement. Option: use a 12-month trailing value.
Cell H9: Growth Rate (%) The company’s audited income statement will show annual revenues for three years. You can calculate the growth rate from that data. A Google search will return several sources, including current and forecast growth rates. To put a bound on the range of possible intrinsic values, and for conservative investment reasons, you can use the Excel program to vary the growth rates around historical values and forecast values. The Excel model accepts negative and zero growth rates. Some might use the consensus of analysts for the growth rate.
Cell I9: Net Operating Profit Margin, or NOP (%) This is the net of what the company will pay in operating revenues for the tax year minus the operating expenses for the same tax year divided by the revenues for the tax year, expressed as a percent. For every dollar of revenues the company nets, it is the percent in profit. In reverse, for every dollar in revenues the company incurs, the percent in operating costs is (1 – NOP).
Cell J9: Effective Tax Rate (%) The effective tax rate is the average rate at which a company is taxed as a percent of profits before income tax during its tax year, expressed as a percentage. As is normal for a company, the tax rate will vary from year to year. A conservative or bounding set of values might be used.
Cell K9: Closing Stock Price ($) Most free financial websites, such as Yahoo Finance , will list the closing price along with the previous close and a graph of closing price for the last five years or longer. The investor might vary this number around recent high and low closing values or use a trailing average.
Cell L9: Shares Outstanding (Mil) The quarterly and audited annual reports should list this number. Most free financial websites, such as Yahoo Finance , will list the shares outstanding. By using the average number of diluted shares instead of the more popularized average shares outstanding, a more conservative valuation is performed.
Cell M9: 10-Year Treasury Yield (%) Go to the TreasuryDirect website for current U.S. Treasury rates and charts. You can input varying rates in this cell to gauge the effect that an increase or decline in Treasury rates could have on a stock.
Cell N9: Bond Spread to Treasury (%) This is the yield difference between the 10-year U.S. Treasury bonds and corporate debt. For low-risk companies, it might be less than 1% and for high-risk companies it might be 5% or much higher. A default choice could be 1.5%.
Cell O9: Preferred Stock Yield (%) This is the interest rate being paid on the preferred stock of a company.
Cell P9: Depreciation Rate (%) This is the ratio of the annual depreciation rate of items like property, plant and equipment (PPE) expenditures divided by the company’s annual operating revenues.
Cell Q9: Investment Rate (%) To spur growth and to keep ahead of the competition, companies need to keep investing in capital items such as property, plant and equipment. The net investment rate is calculated by taking capital expenditures, as disclosed in a company’s statement of cash flows, and subtracting non-cash depreciation charges, found on the income statement. Lastly, divide the result by revenue.
Cell R9: Working Capital (% of Revenues) Working capital refers to the cash a business requires for the expenses of its day-to-day operations.
Cell S9: Short-Term Assets ($ Mil) As listed in the company annual report, this is the current assets that can be liquidated within one year. Financial websites will also give this value.
Cell T9: Short-Term Liabilities ($Mil) This is any debt incurred by a company that is due within one year.
Cell U9: Equity Market Risk Premium (%) This is the extra premium (percentage) that an investor requires over and above a risk-free percentage premium such as that obtained from a U.S. Treasury bond. This extra return is the compensation an investor would require for taking on the relatively higher risk of equity investing. It can range from about 3.5% or more for short-term equity investments to over 5 % for long-term equity investments. The premium will fluctuate with respect to existing or anticipated economic conditions such as inflation. The theory for a risk premium is based on William Sharpe’s 1990 Nobel Prize-winning capital asset pricing model (CAPM). The value the user selects could be based upon the individual’s investment style.
Cell V9: Company Beta Beta is a quantitative measure of how volatile (beta > 1.00) or how stable (0 < beta < 1.00) a specific stock is over a prescribed period of time when compared to a market benchmark index over the same period of time. A beta of 1.00 means that the stock moves in sync with the benchmark index. Most financial websites calculate beta using five-year returns for the stock and the S&P benchmark index. Many websites show how to calculate a custom beta to meet the investor’s time horizon.
Cell W9: Value Debt Outstanding ($Mil) Use Google Finance or Yahoo Finance to find this value for the company.
Cell X9: Value of Preferred Stock Outstanding ($ Mil) Use Google Finance or Yahoo Finance to find this value for the company.
The input data set at cells E9 to X9 and at cells O42:O51 are highlighted in yellow in Figures 1 and 2 and in the spreadsheet . All inputs must be made before the Excel calculations are useable. All input values are decimal numbers without the percent sign or a dollar sign. Some numbers such as the growth rate value (H9) and the investment rate (cell R9) can be negative. Data input examples include: a tax rate of 34.32% (cell J9) is entered as 31.32; recent revenues (cell G9) of $5,851,100 is entered as 5851.1; outstanding shares (cell L9) of 106,300 is entered as 106.30.
A future Computerized Investing (CI) article will describe how to integrate these data into the Excel model using AAII’s Stock Investor Pro database.
Step 2: Project Future Revenue
Use a growth rate (or rates) to sequentially project the company revenues for each of the 10 excess years. You could use either a flat rate (cell H9) for all 10 years into the future or use any mix of negative, a zero or positive growth rates that best match the anticipated growth rate or growth cycle of the candidate.
If you decide to vary a company’s growth rate year by year instead of keeping that data input constant (cell H9), enter a growth rate for each year in cells O42:O51, which is also highlighted in yellow. You can use this capability in the spreadsheet to modify any of the row 9 user inputs in order to better match a candidate’s projected year-by-year performance profile or anticipated changes in the economy.
The projected revenues for the first excess year (N=1) is based upon the most recent reported company revenues (cell G9, or C$41) and the growth rate of cell O42 ( Figure 2 ). The values could be a 12-month trailing value or taken from an annual report or quarterly reports. The Excel equation in cell C42 is =ROUND(C$41*(1+(O42/100)),2).
The equation states that the projected revenue for excess period N=1 is equal to [revenues in $ millions at excess period N=0] x [1 + (growth rate in percent for N=0 ÷ 100)].
The answer is rounded to two decimals and returned as a value in millions. Thus, if the value for cell C$41 is $6,348.44, the projected operating revenues for N=1 is $6,348,440.
The $ sign in C$41 means that cell is an “absolute cell reference.” This Excel $ sign notation ensures that if you move or copy the formula to another location, the moved or copied formula will always refer to that original cell location for a data value, in this case the data value entered at cell C$41. The reference to the original data cell is not changed by relocating or copying the formula to another location.
The comma at the end of the equation ROUND(C$41*(1+(O42/100)),2) rounds the computed result to two decimals.
The process is repeated for all 10 excess years. Thus, for excess period N=6, the equation in cell C47 is ROUND(C$46*(1+(O47/100)),2).
Again, the projected revenues for N=6 years into the future equals [revenues at excess period N=5] x [1 + (growth rate in percent for N=6) ÷ 100], with the answer rounded to two decimals.
Step 3: Calculate Free Cash Flow for the Firm (FCFF)
The next task is to use the projected revenue for N=1 at cell C42 along with the data input set, highlighted in yellow, to perform a series of calculations, shown in each of cells D42 to J42 ( Figure 2 ), that culminate in an estimate of how much cash is left after that tax year’s operating expenses are subtracted from the tax year’s operating income. This resulting annual amount of negative or positive cash is called the free cash flow for the firm (FCFF). It is shown in cell K42 for N=1.
Figure 2. Calculating Discounted FCFF
Free cash flow for the firm is the portion of the firm’s cash flow that is available to both equity and debt stakeholders. All or a portion of this cash is available to give back as a dividend to preferred and common stockholders, a preferred shareholder, a bondholder or any other person who holds a legitimate claim to the company. This is the cash that is king! A portion of the cash might instead be used to increase the worth of the company by additional expenditures on research and other product lines, an increased production capability to satisfy its expanding regional or global markets, or even a buyback of common stock.
Free cash flow is automatically calculated once you’ve entered the required inputs.
Step 4: Calculate the Weighted Cost of Capital
The weighted average cost of capital (WACC), calculated in cell F30 ( Figure 3 ), is used to determine what the annually projected FCFF is worth in today’s dollars (cells M42:M51). The WACC is commonly used in a DCF model as the “discount rate”since it nicely blends the return requirements of two groups of stakeholders, the corporation’s debt holders and equity holders. (See “ The Validity of Company Validation Using Discounted Cash Flow Methods ,” by Florian Steiger, Seminar Paper, Fall 2008, pages 4-7.)
Figure 3. Calculating Aftertax WACC
WACC represents the company’s cost of capital; a company will strive to make more than its cost of capital. However, be aware that some analysts might use slightly different data or methods to arrive at a WACC value.
The calculation for WACC is:
[(cost of equity) x (% of equity in structure)] + [(cost of preferred stock) x (% of preferred stock in structure)] + [(cost of debt) x (1 – tax rate) x (% of debt in structure)]
The percentages of equity, preferred stock and debt are on a market value basis.
Three items need to be accounted for when determining the weighted average for the cost of capital: the cost of long-term debt (cell B26 = M9 + N9)), the cost of preferred stock (cell B27 = O9) and the cost of common stock (cell B28 = cell B18).
The cost of preferred stock is pulled directly from the inputs, while the cost of common equity and aftertax cost of debt are calculated.
The cost of common equity for the common stock holders is calculated using the capital asset pricing model, or CAPM, in cell B18:
B14+B15*B16
Essentially, the expected return for an investor is the sum of a risk-free return, or 10-year Treasury yield (cell B14 = M9) and the beta (or volatility) of the stock (cell B15 = V9) multiplied by market’s risk premium (cell B16 = U9).
The cost of long-term debt figure (B26) is the sum of cells M9 and N9. A company’s cost of debt is essentially its weighted average yield on outstanding bonds; it represents what the company is paying to its bondholders. Here we are using the 10-year Treasury yield and adding a bond spread over the Treasury yield.
A company’s cost of debt has to be adjusted for tax because interest is tax deductible. The aftertax yield for the cost of long-term debt is cell C26 [((100-J9)/100)*B26]. In other words, if cell J9 is the tax rate then the aftertax yield is 100 minus cell J9 times the cost of long-term debt (cell B26).
At the company level, preferred stock and common stock is not taxed, so the “aftertax” amounts for cost of preferred and common equity are the same as the pretax figures. Thus, the current yield for a company’s preferred stock is the same as its aftertax yield in the WACC calculation.
The value for the aftertax yield for common equity is cell C28. The market value is cell D28, which multiplies the market stock price (cell K9) by the common shares outstanding (cell L9). This calculation represents market capitalization.
To calculate the percent of total market value (cell D30) that each of the three components have, the sum of the market values (cells D26:D28) is required. The percent share of capitalization for each is then calculated. For long-term debt, cell E26 = D26 ÷ C30; for preferred stock, E27 = D27 ÷ C30; for common stock equity, E28 = D28 ÷ C30. Note that cell E30, with the formula SUM(E26:28), equals 100%.
The weighted aftertax yield for each of the three components is then calculated:
Long term debt in cell F26 = [C26 ÷ 100] x E26
Preferred stock in cell F27 = [C27 ÷ 100] x E27
Common stock equity in cell F28 = [C28 ÷ 100] x E28
The individual aftertax yields are summed in cell F30 (F26:F28) to give the WACC. This is the value to be used to discount each of the FCFFs in cells K41:K51 to today’s worth.
Step 5: Discount FCFF to Today
After calculating the FCFF, it is a straightforward path to value those future FCFF dollars into today’s dollars and finally to determine the intrinsic value per share for the candidate company (cell C68 at 10 years into the future, or cells Q42:Q51 for each excess year in the future, depending on the company’s competitive position per Table 1 ).
The discounted cash flow model (DCF) is used to value the future FCFF dollars in terms of today’s dollars. DCF analysis is used to project cash flow into the future and then “discount” it back to today in order to get a better idea of what a company is worth today based on future expectations. (See Steiger paper, pages 4-5.)
The discounting factor for each excess year N=1, 2, … 9, 10 is calculated in cells L42:L51 ( Figure 2 ). The formula is [1 ÷ (1 + WACC)N], where the superscript N means that the term (1 + WACC) is taken to the Nth power for excess year N. Thus (1 + WACC)3 = (1 + WACC) x (1 + WACC) x (1 + WACC).
Once the FCFF values are calculated for company XYZ (cells K42:K51) for excess years one through 10, the DCF model is then exercised to calculate what the projected FCFF dollars are worth in today’s money (cells M42:M51). Discount factors are multiplied by the future FCFF values to arrive at values in today’s terms. For example, in excess year 5 the formula in M46 is K46 x L46.
Accounting for the Time After the Excess Period
As explained above, each of the companies an investor might consider will exhibit different levels of business growth, decline or could be in a steady state situation. Some are new start-ups, some running hot with growth, some fully mature, others in decline.
For example, suppose that a company is operating as a solid company, with excess return period equal to six years. In this case, the intrinsic value for excess year N=6 is the one to use. However, the company will still exist after this time horizon. The total valuation of a company should account for the valuation up to and including the chosen excess period and for the time after the chosen excess period. The model handles the calculation in two standardized steps.
First, the Excel program sums the discounted FCFF values for the chosen number of excess years to obtain an input to the valuation calculation due to discounted FCFF values. If the excess period is six years, the sum M42:M47 is used in the valuation calculation.
The second step is to account in an approximate manner for the worth of the company for the time after the chosen excess period. The flow of the calculation is shown in row 55.
The second step is important. We need to estimate the worth of the company for the time beyond the excess (forecast) period. This value is termed a residual or terminal value.
The further out into the future we go, the more difficult it is to forecast a company’s financial performance. Thus, the residual or terminal value calculation is considered an approximation of the company’s value beyond the forecast period. However, since it can represent over half of the company’s value in today’s terms, it must be included in the calculation. The assumption made for this second step is that, on the average, the company’s growth beyond the forecast period is expected to be approximated by the growth value used in the model.
Step 6: Calculate Intrinsic Value per Share for a Stock
We now calculate the total projected total value of common equity or stock using the stream of FCFF money discounted to today’s money.
Since the company is expected to still exist after the Nth year, we add the discounted residual value (cell M55 = cell C58) to the discounted FCFF values (cells M42:M51 = cell C57) along with the corporation’s short-term assets (cell C59 = cell S9) to determine the total firm value (cell C61). This section of the spreadsheet is shown in Figure 4 .
Figure 4. Calculating Intrinsic Value
Next, we subtract the value of the company’s debt, preferred stock value and other liabilities in order to find the total projected value to common equity (cell C67).
Thus:
Cell C67 = [M42:M51 + M55 + C59] – [C63 + C64 + C65]
= Total value to equity
Total firm value = total equity value – outstanding debt – preferred stock – short term liabilities
Dividing the company’s total equity value by the number of common shares outstanding (cell L9) results in the projected intrinsic value per share (cell C68). Dividing the intrinsic value per share by the market value per share (cell K9), we obtain the ratio of intrinsic value to market value (cells R42:R10; or for N=10, cell C71).
The equation used to calculate the intrinsic value at any other excess year from N=1, 2, … , 8, 9 will be the same as shown above. However, for discussion purposes, suppose the company’s valuation is calculated out to excess year N=5. The entire N=5 values on row 46 would be copied into row 55 except for cells K55 and M55. Values calculated for N=6 to N=10 would be ignored since they no longer are part of the calculation.
The value for cell K55 would then be cell F55 ÷ Y9, for N=5. The value for cell M55 would be cell M55 = L55*K55.
Thus, the short-cut equation to calculate the intrinsic value at cell Q46 for N=5 is:
IF(((SUM(M$41:M46)+F46/Y$9*L46+S$9)-(W$9+X$9+T$9))<0,0,((SUM(M$41:M46)+F46/Y$9*L46+S$9)-(W$9+X$9+T$9))/L$9)
The Excel notation <,0 in the IF statement means to calculate the first equation and if it is less than zero, set the value equal to zero. Therefore, in this equation, if the value of common equity (the first half of the formula) is negative, then calculated intrinsic value is set to zero.
A plot of the set of intrinsic values for N=1,2, … , 9,10 is shown on the right side of the spreadsheet as a graph ( Figure 5 ).
Figure 5. Graph of Projected Intrinsic Values
Whatever excess period the investor might consider before the company is considered mature (N=1, 2, 3 …. 8, 9, 10 years), each value shown in cells Q42 to Q51 is the intrinsic value to use. Caveat: This is true only if the input values for all excess years do not change.
The ratio of intrinsic value to the quoted market value for stock XYZ is shown in cells R42:R51 and graphed to the right. It is interesting to observe that the ratio of intrinsic value to market value exceeds 1.0 sometime after excess year N=5. The ratio is 1.32 at the end of excess year N=10. If the investor’s time horizon is less than five years, this stock might not be appropriate for the investor since its projected worth per share is less than market value. If the time horizon is 10 years, this company might make the investor’s short list for further consideration since the intrinsic value per share is greater than its market value.
Excel Formulas and Calculations Used
The cell-by-cell calculations along row 51 ( Figure 2 ) are as follows for N=10, which would be the same for N1, 2, … 8, 9:
Cell D51: Net Operating Profit, or NOP ($ Mil) =ROUND(C51*(I$9/100),2)
The company’s net operating profit for excess period N=10 is equal to the [projected revenues (cell C51)] x [the company’s expected net operating profit margin (I$9) ÷ 100]. The answer is rounded to two decimals. The units are in millions.
Cell E51: Adjustment to Taxes ($ Mil) =ROUND(D51*(J$9/100),2)
The company’s adjustment to taxes for N=10 is equal to the [projected NOP (cell D51)] x [the company tax rate (cell J$9) ÷ 100]. The answer is rounded to two decimals. The units are in millions.
Cell F51: Net Operating Profit After Taxes, or NOPAT =ROUND(D51-E51,2)
The company’s NOPAT after taxes for N=10 is equal to NOP (cell D51) – adjustment to taxes (cell E51). The answer is rounded to two decimals. The units are in millions.
Cell G51: Annual Amount Invested ($ Mil) =ROUND(C51*(Q$9/100),2)
The company’s annual amount invested for the excess year N=10 is equal to [projected revenues (cell C51)] x [company investment rate (cell Q$9) ÷ 100]. The answer is rounded to two decimals. The units are in millions.
Cell H51: Depreciated Amount ($ Mil) =ROUND(C51*(P$9/100),2)
The company’s annual amount depreciated for the future excess year N=10 is equal to [projected revenues (cell C51)] x [company depreciation rate (cell P$9) ÷ 100]. The answer is rounded to two decimals. The units are in millions.
Cell I51: Net Change in Investment ($ Mil) =ROUND(G51-H51,2)
The company’s annual net change in investment for N=10 is equal to company’s annual amount invested (cell G51) – company’s annual amount depreciated (cell H51). The answer is rounded to two decimals. The units are $ millions.
Cell J51: Net Change in Working Capital ($ Mil) =ROUND((C51-C50)*(R$9/100),2)
The company’s net change in investment capital for N=10 is equal to [revenues for N=10 (cell C51) – revenues for N=9 (cell C50)] x [percent of the cash difference in revenues (R$9) a business requires for day-to-day operations ÷ 100]. The answer is rounded to two decimals. The units are in millions.
Cell K51: free cash flow for the firm, or FCFF ($ Mil) =ROUND(D51-E51-I51-J51,2)
The company’s FCFF for excess year N=10 is [NOP (cell D51)] – adjustment to taxes (cell E51) – net change in investment capital (cell I51) – net change in working capital (cell J51)]. The answer is rounded to two decimals. The units are in millions.
The Intrinsic Value spreadsheet is a very useful tool to calculate the intrinsic worth of a company by using a set of data inputs that are representative of the company’s performance and the state of the economy. Because intrinsic value is what an individual investor defines as the “fair value,” stocks whose market price is less than the intrinsic value per share may be of interest to the value investor.
The tool is useful in other ways. By doing “what if” studies, the user can observe the relative effects that changes in the input values can have on the intrinsic value.
The tool could also be employed by a company to help identify some of the strategic goals that have to be fulfilled in order to reach its targeted worth or per share stock valuation (See Steiger paper).
A slight change in the weighted average cost of capital can have a significant multiplier influence on a stock’s intrinsic value. This is due in part to the compounding effect that WACC has on valuing the annual stream of future free cash flow for the firm in today’s money, and then the per share value. For example, a sensitivity study would show that by increasing the WACC by plus or minus 0.5% from a value of 4%, the stock’s intrinsic value per share could change by 15%.
By varying the input values for one or more of the input parameters to the Excel model, the user can project likely upper and lower bounds for a stock’s intrinsic values. An even more sophisticated analysis might include statistical permutations and variations of the input parameters. Thus, as previously shown, the basic Excel formulas could be further upgraded to produce an even more advanced analysis tool.
The Intrinsic Value spreadsheet has been tested against the ValuePro black box calculator. At each of the 10 years into the future, all intrinsic values match if the same inputs are used. The other references mentioned in the article and listed below have been used to verify the analytical approach.
The compactness of the Excel spreadsheet enables one to add additional formulas to perform other types of important calculations, such as to gauge whether or not there will likely be a dividend stream via the FCFF projections. The 10 excess years of projected FCFFs (cells K42:K51) and NOPATs (cells F42:F51) that result from the Excel model calculations could be a launching point for even more sophisticated financial projections for a company.
With some additional programmatic work, my Excel program could be enhanced to calculate the free cash flow to equity (FCFE). It is a measure of how much cash can be paid to the equity shareholders of the corporation after all expenses, reinvestments and debt are paid (See “ What is free cash flow and how do I calculate it? ,” byDr. Pamela Peterson Drake; Florida Atlantic University; page 4.)
By carefully selecting the input data set, you can perform intrinsic value calculations free of gut feelings or emotions. The Ruppert video gives a good overview on how to quickly obtain much of the input data using such free internet tools as Google Finance , Yahoo Finance and the U.S. Department of Treasury .
For due diligence reasons, it must be stated that the investor should not base the decision to buy or sell a stock solely on an intrinsic value calculation. Additional analysis must be performed. This Excel program makes calculations, not decisions.
|
|
Mailing AddressONE COCA COLA PLAZAATLANTA GA 30313
Business AddressONE COCA COLA PLAZAATLANTA GA 30313404-676-2121
COCA COLA CO (Filer) CIK: 0000021344 (see all company filings)
