Automated Stock Analysis Spreadsheet Documentation

This section provides best practices with StableBread's Automated Stock Analysis Spreadsheet, including knowing how to optimize for performance, avoid errors, and avoid confusions as you navigate the spreadsheet. See this list of best practices below:

• Always enable all Excel workbook macros, otherwise you cannot use the macro buttons in this workbook.
• Navigate between Excel sheets easily via the Navigation pane, which you can access on your Excel ribbon under the "View" tab. This is also where you can hide Gridlines, Headings, and the Formula Bar.
• If you want to unprotect a sheet, navigate to "Review" on your Excel ribbon and click on "Unprotect Sheet." The only sheet that cannot be unprotected is the COVER sheet. However, you should keep sheets protected by default, and navigate across the sheet with your arrow keys. This will let you automatically jump to input cells, if you have the "select locked cells" unchecked after clicking on "Protect Sheet."
• Freeze and unfreeze panes as needed. If you want to freeze or unfreeze panes for any sheet, navigate to "View" on your Excel ribbon and click on "Freeze Panes," then select the appropriate option.
• Enable multithreaded processing. Go to file --> options --> advanced --> click on the "Enable multi-threaded calculation" checkbox under the "Formulas" section. Then, simply input the number of processors you'll let Excel use. This will make the entire Excel workbook run faster.
• On your Excel ribbon, navigate to "Formulas," and click on the "Calculation Options" dropdown. Select "Automatic Except for Data Tables." This will improve the performance of your Excel workbook. If there's a data table on your current sheet just click on the "Calculate Now" or "Calculate Sheet" button instead.
• Save copies of your completed analysis sheets, whether it be in Excel or in PDF format.
• If data is slow to load, re-input the stock ticker on the DASHBOARD tab or restart your Excel.
• Contact StableBread for any spreadsheet support questions. Contact Wisesheets if you're having troubles with their API data or have any questions for them.

For further information on what's included in StableBread's Automated Stock Analysis Spreadsheet, the rest of this page is dedicated towards providing a closer look at the key features in each spreadsheet, and how to use each spreadsheet. To begin, all of the spreadsheet tabs will feature these two items:

1. Save PDF Macro Button: This saves the current sheet's pre-defined print areas as a PDF in your selected file path on your computer. This is useful for generating reports, sharing valuation models with others, and being able to document any stock analysis.
2. Excel Stylesheet and Branding: This Excel workbook follows a common theme across all of its spreadsheet tabs, as found on the GUIDE tab. Along with the pre-defined spacing and collapsible rows/columns, this makes navigating the sheet easier and should improve your overall analysis process. You'll also see StableBread's logo on all sheets, which directs you back to StableBread's website homepage.

The COVER sheet is the cover page for StableBread's Automated Stock Analysis spreadsheet. It features the version number, the last official updated date, and a disclaimer. For privacy and security reasons, this sheet cannot be unlocked or deleted. No calculations are driven off or on this page.

The GUIDE sheet shows you how to use this workbook to its fullest potential. This sheet is broken out into three different sections:

1. StableBread Workbook Navigation Best Practices
   • Shows the tab colors and a shortened description of what each tab includes. You can view these descriptions by clicking on the colored cells.
   • Shows a list of all the sheet names and navigation links, and categorizes each sheet as its own purpose.
2. StableBread Stock Analysis Spreadsheet Cell Formatting Rules
   • Shows you how to read the cell formatting in the workbook. Provides a description of the cell format, a definition, and a format example.
3. StableBread Excel Shortcut Guide
   • Lists out all ~380 defined name ranges in the workbook and their shortcut names. The outputs of these calculations or cell references can be accessed by using the "=" sign on any cell and typing in the shortcut name. This is particularly when you want to know a financial metric or data point for a company quickly. For example, typing "=LFY_Revenue" into any cell will give you the company's total revenue for the last fiscal year.
   • Shows the reference cell or formula in which the shortcut name applies, as well as the source tab in which the shortcut name is located.

The DASHBOARD sheet summarizes the key information from all other relevant financial analysis or stock valuation tabs. It acts as an input tab for key market data, an output tab for final valuation analysis, and a summary tab for investors to evaluate a company's overall fundamentals and trends. More specifics on this sheet are included below:

1. Master Workbook Inputs
   • Stock Ticker: This should always be equal to a publicly traded company's stock ticker, without any symbols (i.e., don't include $ or #). Updating this cell will trigger the model to refresh and pull the financial statement data, along with other financial data and company information.
   • Analysis Year: This is a dropdown selection driven off the BACKEND tab. Changing this year will adjust the last actual date of the financial statement and any outputs from these statements. It's recommended that you keep this year as the most recent year.
   • Unit Selector: This is a dropdown selection driven off the BACKEND tab. It lets you select between billions, millions, and thousands, which is how all of the numbers in the spreadsheet tabs will be outputted. It's recommended that you keep this option as "millions," for best readability.
2. Fundamental Company Information, Trading and Dividend Information, Key Statistics and Valuation Ratios
   • Fundamental Company Information: Company name, company website, company description, location info (exchange, currency, country), industry and sector info (GICS sector, GICS industry), company codes (ISIN, CIK, CUSIP).
     • The last fiscal year, last calendar year, and historical year are calculated in the BACKEND tab, and are critical towards this sheet functioning properly.
     • The "Auto Fit Description" macro button, if the sheet is unprotected, lets you automatically auto-size the company description, which is convenient to use after loading in a new stock ticker. Note that this macro does not work on Excel for Apple products.
   • Trading and Dividend Information: Closing stock price and close/open difference calculations, 52-week high/low and high/low difference calculations, including % off 52-week high/low calculations. Market cap, enterprise value, shares outstanding, volume, and average volume are included as well. Dividend information (dividend per share, dividend payout ratio, and dividend yield) are included if the company pays dividends.
   • Key Statistics and Valuation Ratios: 10-year CAGR's on key growth metrics, 10-year median margins for profitability analysis, 10-year median returns for measuring effectiveness, YoY capital structure and debt comparisons, YoY short-term liquidity ratio comparisons, YoY efficiency comparisons, and YoY key valuation ratio comparisons (including a chart). I've inputted default statistics and valuation ratios here, but you can choose from any of the options on the KFR tab if you'd like to view another type of relevant ratio within that subsection category.
3. Key Financials and Key Financial Ratios
   • Key Financials: Ability to select any three line items from the KFS tab, which includes supplementary line items, and analyze their trends over a 5-year period. This includes being able to assess their YoY growth, charts, 5-year CAGR's, and an option to view between the 5-year min, 5-year mean, 5-year median, and 5-year max.
   • Key Financial Ratios: Ability to select any three ratios from the KFR tab, and analyze their trends over a 5-year period. This includes being able to assess their YoY growth, charts, 5-year CAGR's, and an option to view between the 5-year min, 5-year mean, 5-year median, and 5-year max.
4. Stock Charts
   • Historical Performance: Input the number of months back for the stock chart date range and visual. Do NOT enter a number greater than 120, as the BACKEND tab only supports 10 years of historical data (for performance purposes). Inputting a negative number or decimal may break the historical performance chart. This will let you see the historical daily closing price for the company, its # month change percentage, and its # month CAGR percentage.
   • Security Comparison: Input the number of months back for the stock chart date range and visual. Do NOT enter a number greater than 120, as the BACKEND tab only supports 10 years of historical data (for performance purposes). Inputting a negative number or decimal may break the security comparison chart. You can also input up to 5 other publicly traded stock symbols to visualize the percent change since the start date. The data for this is outputted in the BACKEND tab.
5. Income Statement Summary and Charts
   • Income Statement (IS) Summary: Provides a summarized/condensed version of the income statement (IS) tab, with up to 10-years of historical financials, sparkline charts, and 2-10 year CAGR selection periods. Note that unlike the IS tab, expenses are shown as negative values here.
   • Income Statement (IS) Charts: There are two charts that summarize the income statement further. The first is a revenue and costs combo stacked bar and line chart graph. The second is a line chart visualizing the growth of the income statement's primary profitability measures.
6. Balance Sheet Summary and Charts
   • Balance Sheet (BS) Summary: Provides a summarized/condensed version of the balance sheet (BS) tab, with up to 10-years of historical financials, sparkline charts, and 2-10 year CAGR selection periods.
   • Balance Sheet (BS) Charts: There are four charts that summarize the balance sheet further. The first is a breakdown of a company's net worth, via its main balance sheet line items. The other three charts are smaller charts that visualize the breakdown in total assets, total liabilities, and total shareholders' equity.
7. Cash Flow Statement Summary and Charts
   • Cash Flow Statement (CFS) Summary: Provides a summarized/condensed version of the cash flow statement (CFS) tab, with up to 10-years of historical financials, sparkline charts, and 2-10 year CAGR selection periods.
   • Cash Flow Statement (CFS) Charts: There are two charts that summarize the cash flow statement statement further. The first is an overview of the company's net financing, net investing, and net operating cash flows over a period of time, in addition to the company's PP&E and FCF. The second chart provides a historical breakdown of the company's cash position, with respect to its changes in cash balances, and beginning and ending cash balances.
8. Valuation Inputs and Summary
   • Key Market Data: This is where you must enter in risk-free rate data, inflation rate data, GDP growth rate data, and corporate bond yield data. This is important to do, as they provide critical data points that the valuation models in this spreadsheet need.
   • Valuation Methods: Intrinsic Buy Price Ranges: After completing all of the valuation models you'd like to complete for the company, you can input your range of high/low values for each applicable stock valuation method. You can then see the average or median range of these values and compare it to the current stock price, to determine its market valuation.
     • The output of this range is also shown on the Football Field Valuation Range chart, which lets you visualize the range of intrinsic buy prices for each valuation method. After unprotecting the sheet, you can also drag the dotted line to where the company's current stock price is trading.
   • Radar Chart: After completing your stock analysis for the company, the radar chart visual can help summarize the key strengths and key risks of the business. You can enter different risk metric names and apply a risk score to each metric (from 0 to 5, with 5 being the best).
9. Valuation Models Reference Inputs and Outputs
   • This section summarizes the main inputs and stock valuation outputs for all of the valuation models provided in this workbook. It's merely for reference and convenience when completing the intrinsic buy price range table and radar chart, as opposed to having to navigate between multiple sheets to get your answer. By default, this section is not included in the print area.

This sheet is the income statement (IS), otherwise known as the profit and loss (P&L) statement. It provides a breakdown of the company's revenues and costs over a period of time. You can also input estimates for the next twelve months (NTM), and also view horizontal and vertical analysis trends in more depth. More specifics on this sheet are included below:

1. Income Statement Financials and Charts
   • The income statement presented is a standardized version that the Wisesheets API outputs. Therefore, this does not capture all of the detail you'd see in a company's 10-K or 10-Q income statement, just the most important components. By default, all financial data is presented in millions, except per share data, and goes back 10 years from the Analysis Year. The trailing twelve months (TTM) is also included, along with an option to input your next twelve months (NTM) estimates as well. You can also see the sparkline charts for every income statement line item, including revenue growth and percentage of revenue calculations.
2. Income Statement Horizontal Analysis
   • Horizontal analysis lets investors evaluate growth for every income statement line item over a particular period.
3. Income Statement Financials Distribution
   • Investors can reference the min, mean, median, and max columns to evaluate the normal distribution of the company's income statement line items, up to 10 years. Investors can also select any time period within the last 10 years and compare the min, mean, median, and max between these two dates, to get a better picture of historical or recent trends.

This sheet is the balance sheet (BS). It provides a breakdown of a company's net worth at point in time, which is its assets, liabilities, and stockholders' equity. You can also input estimates for the next twelve months (NTM), and also view horizontal and vertical analysis trends in more depth. More specifics on this sheet are included below:

1. Balance Sheet Financials and Charts
   • The balance sheet presented is a standardized version that the Wisesheets API outputs. Therefore, this does not capture all of the detail you'd see in a company's 10-K or 10-Q balance sheet, just the most important components. By default, all financial data is presented in millions, except per share data, and goes back 10 years from the Analysis Year. The trailing twelve months (TTM) is also included, along with an option to input your next twelve months (NTM) estimates as well. You can also see the sparkline charts for every balance sheet line item.
2. Balance Sheet Horizontal Analysis
   • Horizontal analysis lets investors evaluate growth for every balance sheet line item over a particular period.
3. Balance Sheet Financials Distribution
   • Investors can reference the min, mean, median, and max columns to evaluate the normal distribution of the company's balance sheet line items, up to 10 years. Investors can also select any time period within the last 10 years and compare the min, mean, median, and max between these two dates, to get a better picture of historical or recent trends.

This sheet is the cash flow statement (CFS). It provides a breakdown of the cash flowing in and out of the business over a period of time, from its net operating, net investing, and net financing cash activities. You can also input estimates for the next twelve months (NTM) for the cash flow statement, and also view horizontal and vertical analysis trends in more depth. More specifics on this sheet are included below:

1. Cash Flow Statement Financials and Charts
   • The cash flow statement presented is a standardized version that the Wisesheets API outputs. Therefore, this does not capture all of the detail you'd see in a company's 10-K or 10-Q cash flow statement, just the most important components. By default, all financial data is presented in millions, except per share data, and goes back 10 years from the Analysis Year. The trailing twelve months (TTM) is also included, along with an option to input your next twelve months (NTM) estimates as well. You can also see the sparkline charts for every cash flow statement line item.
2. Cash Flow Statement Horizontal Analysis
   • Horizontal analysis lets investors evaluate growth for every cash flow statement line item over a particular period.
3. Cash Flow Statement Trend Analysis
   • Investors can reference the min, mean, median, and max columns to evaluate the normal distribution of the company's cash flow statement line items, up to 10 years. Investors can also select any time period within the last 10 years and compare the min, mean, median, and max between these two dates, to get a better picture of historical or recent trends.

The key financial statements (KFS) tab can be thought of as the source tab in which all other financial statement related data is calculated from. It also lets you see the financial statements in one sheet, without hiding any information like the financial statements on the DASHBOARD tab does, and calculates supplementary data that is utilizes in other sheets. This sheet also lets you compare trends, growth, and see CAGR's for any 3 selected key financials. More specifics on this sheet are included below:

1. Three Financial Statements
   • The green-colored numbers all reference the financials in the income statement, balance sheet, and cash flow statements. These financials are then used as an output for calculations and models in the rest of the workbook. As mentioned, this view lets you see the key financial statements in one view, without hiding any information, so that you're better able to piece together the company's financial position and story.
2. Supplemental Data
   • This section calculates financial data that is primarily used in the KFR tab, as the three financial statements themselves do not include everything. Specifically, what's calculated here is net invested capital, average daily expenditures, net working capital, tangible book value, market sizing figures (market capitalization and enterprise value), simple free cash flow, free cash flow to the firm, and free cash flow to equity. Dividend per share data is also provided, which comes from Wisesheets API.
3. Three Selected Key Financials Trends
   • On the right side of the sheet, you'll see three dropdown list cells where you can select any 3 key financials. The output of this selection will let you see the selected financials' growth trends over a period of time, up to 10 years, its CAGR, and its min, mean, median, and max over a selected date range. You can also view the CAGR's over multiple periods of time for these 3 selected key financials, to give you a better picture of growth. You'll also see one chart that shows the growth percentage of these 3 selected key financials over a period of time, up to 10 years and inclusive of TTM and NTM estimates (if completed). The other chart on the tab shows the CAGR trends for these 3 selected key financials, based on the year inputs.

The key financial ratios (KFR) tab calculates 75+ different ratios, up to a 10-year period and inclusive of the TTM and NTM estimates, if completed. This sheet shows you the formulas for all of these key ratio calculations, as well as a long-form and short-form description of each key ratio name. A threshold input-based pass/fail mechanism is also included in this sheet, which can help identify good/bad ratios and trends. This sheet also lets you compare trends, growth, and see CAGR's for any 3 selected key financials. More specifics on this sheet are included below:

1. Descriptions and Formulas
   • For every key ratio calculation, there is a complete name description, a shortened name description, and a complete formula to show how the ratio was calculated. The key ratios in this tab are categorized into the following sections: Capital structure and debt ratios, interest rate coverage, short-term liquidity, dividends, returns, margins, turnover ratios, efficiency ratios, price ratios, enterprise value ratios, and free cash flow ratios.
2. Key Financial Ratiosand Thresholds
   • All of the ratios calculated on this tab come from financial data on the KFS tab.The key financial ratio value colors will always either be black, red, or green. The color will be black if there's no financial ratios in the prior period to compare to, or if the ratio is the exact same as the prior period. The color will be red or green depending on where the current period's ratio stands relative to the last period's ratio. This is where you can reference the green/red up and down symbols (in column X and AA). For example, a lower debt-to-assets ratio is better, whereas a higher interest coverage ratio is better, and the ratio's colors will reflect this difference accordingly (with green = better, red = worse).
     • The threshold pass/fail input cells can also be used as a screener. For example, let's say you don't want to invest in companies with a price-to-earnings ratio above 30x and only below 15x. In this case, you'd enter 30x as a fail threshold and 10x as a pass threshold. Any values below 10x over the available period will highlight green, any values greater than 30x will highlight red, and any values in-between will highlight yellow.
3. Key Financial Ratios Trend Analysis
   • Investors can reference the min, mean, median, and max columns to evaluate the normal distribution of the company's key financial ratios, up to 10 years. Investors can also select any time period within the last 10 years and compare the min, mean, median, and max between these two dates, to get a better picture of historical or recent trends.
4. Three Selected Key Financial Ratio Trends
   • On the right side of the sheet, you'll see three dropdown list cells where you can select any 3 key financial ratios. The output of this selection will let you see the selected ratios' growth trends over a period of time, up to 10 years, its CAGR, and its min, mean, median, and max over a selected date range. You can also see the CAGR's over multiple periods of time for these 3 selected key ratios, to give you a better picture of growth. You'll also see one chart that shows the growth percentage of these 3 selected key ratios over a period of time, up to 10 years and inclusive of TTM and NTM estimates (if completed). The other chart on the tab shows the CAGR trends for these 3 selected key ratios, based on the year inputs.

The FCFF | FA | NWC tab serves a purpose of forecasting FCFF, which is an option that can be used in the DCFs | TVs tab, for discounted cash flow (DCF) valuation. FCFF is a measure of a company's ability to generate cash after accounting for capital expenditures, which is why it's used in DCF valuation methods. This sheet lets you accomplish these FCFF forecasting steps effectively, and comes with flexible assumption inputs and sensitivity analysis options. More specifics on this sheet are described below:

1. Free Cash Flow to the Firm (FCFF): This is also known as "unlevered free cash flow" (UFCF). The top section of this tab shows you the line items and calculations required in order to get down to FCFF, and includes conservative-case, base-case, and optimistic-case scenario calculations as well (which are hidden by default). The forecast FCFF is estimated based on the assumptions subsection, the fixed assets schedule, and the net working capital schedule. The assumptions subsection includes dropdown projection options based on historical average revenue growth and percent of revenue expenditure line items, specifically over last year, the last 3-years, and the last 5-years.
   • Note that the assumptions section also includes input cells for conservative, base, and optimistic revenue growth cases. This is where investors should input their worst-case, most-likely case, and best-case assumptions on how they expect revenue to perform in the future. These inputs, or lack of inputs, will be reflected in the company's FCFF forecasts.
2. Fixed Assets (FA): The fixed assets schedule is used in the FCFF calculation because it provides information about a company's capital expenditures, which is subtracted from operating cash flow to get to FCFF. The top section of this schedule shows you the calculation needed to get to the capital expenditure number. The assumptions subsection has two dropdown projection options for depreciation and amortization and for capital expenditures, specifically over the last year, the last 3-years, and the last 5-years. These are both expressed as a percentage of the company's beginning PP&E.
3. Net Working Capital (NWC): Net working capital is the difference between a company's current assets and current liabilities. It represents the amount of short term liquidity a company has to meet its short-term obligations. The NWC schedule on this sheet therefore provides a picture of the company's liquidity over time. Note that a decrease in NWC will increase a company's operating cash flow and result in a higher FCFF, whereas an increase in NWC will result in a lower FCFF. The NWC on this sheet shows you the non-cash current assets and current liabilities required in order to calculate NWC, and includes conservative-case, base-case, and optimistic-case scenario calculations as well (which are hidden by default).The assumptions subsection forecasts accounts receivable, inventories, and accounts payable by calculating the company's days sales outstanding (DSO), days inventory held (DIH), and days payable outstanding (DPO) respectively, with the other net working capital items being represented as a percent of total revenues. There are also dropdown projection options for these items, specifically over the last year, the last 3-years, and the last 5-years.

The weighted average cost of capital (WACC) provides a popular approach of estimating the discount rate of a company, and includes the capital asset pricing model (CAPM) within the calculation. Both the WACC and the CAPM are common discount rate options that can be used in absolute valuation models. The WACC represents a company's average cost of obtaining capital from debt and equity sources, and is calculated by taking into account the proportion of each type of financing in the company's capital structure, and the cost of each type of financing. This sheet provides a comprehensive approach towards calculating the WACC, as described further below:

1. Capital Structure, Debt and Equity Weights
   • This is where you should input the amount of common stock and preferred stock shares the company currently has outstanding, as well as their current market prices. This information can be found in a company's 10-K and 10-Q statements, or you can simply use the total shares outstanding and the company's current market price instead. In either case, you'll get the total capitalization of the company and the company's debt and equity weights, which is used in the WACC calculation.
2. Cost of Debt
   • The cost of debt represents the cost of the company borrowing money, such as from bonds and loans. The after-tax cost of debt section lets you select one of the three pre-tax cost of debt calculation methods found at the bottom of the tab (debt rating, debt rating via estimating a synthetic rating, or interest expense to total debt), and then applies a tax shield to calculate the after-tax cost of debt. This shield is applicable only for the company's cost of debt, as the interest payments companies make on debts are tax-deductible, which reduces their taxable income.
3. Cost of Preferred Stock
   • The cost of preferred stock is the return investors expect to receive for holding preferred stock, a hybrid security that combines features of both bonds and common stock. Preferred stock typically pays a fixed dividend, which is used in the cost of preferred stock formula, along with the current price of preferred stock (which was inputted prior), and the perpetual growth rate of the preferred stock dividend. It's not uncommon for a company to not have any preferred stock, in which case this section can be left blank.
4. Capital Asset Pricing Model (CAPM) for Cost of Equity
   • The CAPM provides a framework for understanding the relationship between a company's risk and return, and is utilized in the WACC formula to calculate the cost of equity. The CAPM can be calculated by finding a company's risk-free rate, beta, and market return. The default risk-free rate used in this tab is the 10-year risk-free rate, as inputted from the DASHBOARD tab. There are three options for beta in this tab, the Wisesheets beta API number, the unlevered beta (found further below), or the levered beta (also found further below).
     • Unlevered beta measures the systematic risk of a company's assets, and is useful when comparing the risk of companies that have different levels of debt financing, as it adjusts for differences in their capital structure. Levered beta measures the systematic risk of a company's equity, including the effects of its debt financing. Therefore, levered beta is useful for evaluating the risk of a company's equity under its current capital structure, which is why its recommended over unlevered beta for CAPM calculations, given that it reflects the risk of equity better than unlevered beta.

Warren Buffett's Owners Earnings is how Buffett estimates the free cash flow (FCF) of a company, instead of calculating the FCFF or FCFE of a company like traditional finance teaches. This is a direct output option in the DCFs | TVs tab, for discounted cash flow (DCF) valuation, and can be used in any other valuation model where FCFs are forecasted. There are two versions of the owners earnings formula, and 6 methods within each of these two versions. This is due to the optionality that comes with estimating maintenance and growth capital expenditure (CapEx). More specifics on this sheet are described below:

1. Owners Earnings Formula Version #1
   • This is the simpler approach of Buffet's formula for estimating the owners earnings of a company. There are 6 slightly different subsections which offer different methods of calculating owners earnings. The method you select depends on the information available to you, and your confidence in estimating maintenance and growth CapEx from the total CapEx number.
2. Owners Earnings Formula Version #2
   • This is the more complex but more literal translation on how Buffet estimates owners earnings for a company. Like version #1, there are 6 slightly different subsections which offer different methods of calculating owners earnings. Again, the one you use depends on the information available to you, and your confidence in estimating maintenance and growth CapEx from the total CapEx number.
3. Bruce Greenwald's Maintenance Capital Expenditure Calculation Methods
   • If you've exhausted all other options of estimating or finding the breakdown of maintenance and growth CapEx for a company, you can opt into using one (or both) of Bruce Greenwald's maintenance CapEx calculations. The premise of these two methods can be followed on the tab, with the final output value being in the bottom right cell of the two tables, and in the last two methods of the two formula versions discussed above.
4. Owners Earnings Formula Distribution Summary and Final Owners Earnings Number
   • There are two bar charts here which show the distribution of output answers for the two owners earnings formula versions, as well as their 6 respective formula methods. This is also where you make a decision and select the final owners earnings number, which is referenced in the DCFs | TVs tab as the Analysis Year's FCF number.

Discounted cash flow (DCF) valuation follows the premise that the value of a company (aka its intrinsic value) can be derived from the present value (PV) of its projected free cash flow (FCF). The outputs of a DCF model provide investors with the company's estimated intrinsic value, which can then be compared to its current market price to determine its valuation. This tab lets you select a discount rate method, lets you select between multiple DCF formula input assumptions, shows how FCF is forecasted, and shows the output calculations and scenarios for the DCF model. This tab also includes a terminal value section, where you can calculate four different ways of estimating the terminal value of a company, which is required in the DCF calculation and applies after the end of the FCF forecast period. More specifics on this sheet are described below:

1. DCF Model Description
   • The top of this sheet features a collapsible section, which describes the premise of the DCF valuation model, the assumptions the model comes with, the user defined inputs, and the DCF formula.
2. Discount Rate Method
   • This section lets you see all of the possible discount rate options. The options are your personal required rate of return (Personal RRR), the weighted average cost of capital (WACC), the capital asset pricing model (CAPM), and a risk-free rate option between the 10-year risk-free rate (10-Yr RF Rate), the 3-month Treasury bill rate (3-Mo T-Bill Rate), or the long term average risk-free rate (LT Avg RF Rate).
     • The discount rate you select here depends on the DCF valuation approach you're following. If you were following Warren Buffett's approach, you'd use one of the 3 risk-free rates as the discount rate. If you were following a traditional finance valuation approach, you'd likely use the WACC or the CAPM. If you want to simplify things and have flexibility in your model, then use the personal required rate of return, as it should reflect the annualized rate of return you're expecting from purchasing an undervalued security.
3. DCF Formula Inputs
   • This is where you select all of the inputs that go into the DCF formula calculation The 5 inputs are described below:
     • Calendarization & Mid-Year Convention: This is a yes/no dropdown option. If "yes" is selected, you must input a date of analysis. Calendarization converts irregular cash flows into an annualized cash flow stream. Mid-year convention is used to account for the time value of money, and assumes that cash flows received at the beginning of the year are received halfway through the year, and that cash flows received at the end of the year are received at the end of the next year. Therefore, an equal distribution of cash flows is assumed throughout the year.
     • FCF Forecast Period (Years): This selection represents the number of years you will forecast FCF to grow. The number of years you select here (up to 10 years) will depend on your confidence in the company's FCF forecast numbers, as the further out your forecast, the more estimation error you're subject to. Traditional finance theory models usually forecast FCF for 5 years, whereas value investors like Warren Buffet may opt to forecast FCF for 10 years. However, this really depends on the company, the market, the company's industry, and your confidence in your FCF forecast.
     • FCF Forecast Method: This is where you select between either FCFF or Owners Earnings. Selecting "FCFF" will reference the FCFF you should've already forecasted in the FCFF | FA | NWC sheet. Selecting "Owners Earnings" will reference the final owners earnings number from the BUFFETT OE sheet, and then provide you with 3 input options for conservative, base, and optimistic case FCF scenarios, where you can input in your assumptions for annual FCF growth. Selecting either method will also affect the "DCF Formula Outputs" section further below in the sheet, and what's hidden there (in gray).
     • Discount Rate Method: This references the "Discount Rate Method" section discussed above, and just asks you to select one of the discount rate options for the model to use.
     • Terminal Value Method: This represents the estimated value of the investment beyond the FCF forecast period, which will grow into perpetuity. The calculations for these methodologies are found at the bottom of this sheet, as discussed below.
4. DCF Formula Outputs
   • This section shows how enterprise value is converted into equity value, for FCFF DCF valuations, and how net present value is calculated for Owners Earnings DCF valuations. In either case, the intrinsic share prices and buy prices are also provided, which will tell you whether the conservative, base, and optimistic cases are undervalued or not. You'll also be presented with two sensitivity tables where you can select between the different growth scenarios, and see associated charts below. Comparison charts between market value and intrinsic value, and between market value and buy prices are also presented. Lastly, you'll see a bar chart that visualizes the growth of FCF over the forecast period, amongst the 3 different FCF scenarios.
5. Terminal Value Methods
   • At the bottom of this sheet, you'll find four different methods of estimating terminal value. Generally speaking, the two exit multiple methods are applicable if you're following a traditional finance valuation approach, otherwise decide between the no-growth perpetuity method and Gordon Growth method for estimating the terminal value of a company. The Gordon Growth method is the only method where you can input a terminal value growth rate, which is why there's also a small warnings subsection to ensure you're not over-estimating terminal growth.

Dividend discount models (DDMs) are absolute valuation methods that follow the concept that a company can be valued based on its future expected dividend payments. The premise of the DDM is that the value of a company's stock is equal to the present value of its future dividends. DDMs therefore assume that dividends are the only source of return to shareholders, and that the company will continue to pay dividends in perpetuity. This sheet lets you evaluate the dividends the company has paid out, up to a 10-year period, along with in-depth valuation models on the five different DDM valuation approaches. More specifics on this sheet are described below:

1. Dividend History and Estimated Dividend Growth
   • The top section of this sheet shows the annual dividend payments the company has distributed to shareholders, up to a 10-year period. Investors can also select any time period within the last 10 years and compare the min, mean, median, and max between these two dates, to get a better picture of historical or recent trends in the company's dividend payments. This section is also where you can get a better picture on the expected growth rate of the company's dividends, which is useful to have as a reference for the 5 DDMs covered in this sheet.
2. Gordon Growth Model (GGM)
   • The GGM is useful for valuing a firm that's in a "steady stage," with dividends growing at a rate that can be sustained forever. This is the simplest DDM. The GGM section features a collapsible area which describes the model, its assumptions, the user defined inputs, and the formula. Below this, you'll see the discount rate calculation options, the dividend calculation options, the formula input and output options and result, the warnings to watch out for, and the valuation price comparison. You'll also see charts comparing the value of the stock and the expected growth rate, as well as how dividends will grow over the next 10 years, with supporting data presented as well. Lastly, there are two sensitivity tables to give you a better picture of the possible price per share and buy price ranges.
3. Non-Constant Growth in the First Stage (NCGFSM)
   • The NCGSM is useful for valuing a firm that's growing erratically in the first stage, subsequently followed by a "steady state" dividend growth rate into perpetuity. This model is preferable over the two-stage dividend discount model, which is almost identical, but only if you're very confident in your dividend predictions over the next 1-5 years. The NCGSM section features a collapsible area which describes the model, its assumptions, the user defined inputs, and the formula. Below this, you'll see the discount rate calculation options, the dividend calculation options, the formula inputs where you can manually input your dividend expectations, the formula outputs, the warnings to watch out for, and the valuation price comparison. You'll also see a chart that visualizes the dividend growth over the next 10 years, with supporting data presented as well. Lastly, there are two sensitivity tables to give you a better picture of the possible price per share and buy price ranges.
4. Two-Stage Dividend Discount Model (TSDDM)
   • The TSDDM can be used to value a firm that's growing at an unstable rate in the first stage, subsequently followed by a "steady state" dividend growth rate into perpetuity. In comparison to the NCGFSM, the TSDDM uses a singular growth rate in the first stage, whereas with the NCGFSM, investors must input their expected dividends or growth rates for every year of the first stage. The TSDDM section features a collapsible area which describes the model, its assumptions, the user defined inputs, and the formula. Below this, you'll see the discount rate calculation options, the dividend calculation options, the stock buyback inputs (which is currently only unique to the TSDDM), the formula inputs for the first stage and second stage, the formula outputs, the warnings to watch out for, and the valuation price comparison. You'll also see a chart that visualizes the dividend growth over the next 15 years, with supporting data presented as well. Lastly, there's one sensitivity table to give you a better picture of the possible buy price ranges.
     • Note that the model can be adjusted for stock buybacks, if you select an option other than "calculated dividend payout ratio (DPR)," which is based on the table calculations to the right of the sheet. If a company has done a lot of stock buybacks recently, you'll want to enable this option, because otherwise your valuation is ignoring the significant amount of cash returned to stockholders in the form of stock buybacks.
5. H-Model Dividend Discount Model (HMDDM)
   • The HMDDM is a two-stage model for growth, which instead of a constant initial growth phase, has a linearly declining initial phase over a specific period of time until it reaches a stable growth rate in the steady stage. This model is useful for firms that are growing rapidly now, but where growth is expected to decline gradually over time as the firms become larger and the differential advantage they have over competitors declines. The HMDDM section features a collapsible area which describes the model, its assumptions, the user defined inputs, and the formula. Below this, you'll see the discount rate calculation options, the dividend calculation options, the formula inputs, the formula outputs, the warnings to watch out for, and the valuation price comparison. You'll also see a chart visualizing how dividends and the EPS growth rate will grow over the next 15 years, with supporting data presented as well. Lastly, there are two sensitivity tables to give you a better picture of the possible price per share and buy price ranges.
6. Three-Stage Dividend Discount Model (TSDDM)
   • The TSDDM combines the features of the two-stage and the H-model. This model allows for an initial high-growth period, a transitional period where growth declines, and a final stable growth phase into perpetuity. This is the most flexible but extensive dividend discount model. The TSDDM section features a collapsible area which describes the model, its assumptions, the user defined inputs, and the formula. Below this, you'll see the discount rate calculation options, the dividend calculation options, the formula inputs for the first stage and second stage, the formula outputs, the warnings to watch out for, and the valuation price comparison. You'll also see a chart visualizing how dividends and the EPS growth rate will grow over the next 15 years, with supporting data presented as well. Lastly, there's one sensitivity table to give you a better picture of the possible buy price ranges.

Benjamin Graham's stock valuation method considers the interest rate environment, the earnings, and the future growth potential of a business to value a company. Because the last official version of Graham's formula came out in 1974, this sheet covers the original formula and a revised version that's more relevant to our current market environment. In either case, Graham's formula works best for companies with a positive and growing earnings per share (EPS) number. More specifics on this sheet are described below:

1. Benjamin Graham's Original Valuation Formula
   • This section shows Graham's original valuation formula from his "The Intelligent Investor" book. The only inputs you need to complete are the EPS growth rate and the margin of safety, everything else is automatically calculated. This model will then output the intrinsic share price, the buy price, and the current valuation.
2. Benjamin Graham's Revised Valuation Formula
   • This section shows Graham's revised valuation formula. The only inputs you need to complete are the EPS growth rate and the margin of safety. You can also toggle between the basic EPS or the diluted EPS, and have the option between using the default (revised version) P/E base no-growth option, or a P/E base no-growth company option that is based on the company's cost of equity. Everything else is automatically calculated.
3. Benjamin Graham's Revised Valuation Sensitivity Analysis
   • This section shows the company's basic EPS and diluted EPS growth, as well as a dynamic sensitivity analysis section based on the inputs to Graham's revised valuation formula. For this sensitivity analysis, you can adjust the P/E base no growth, EPS growth rate, growth multiplier, and margin of safety. This can be adjusted to fit your understanding of the business and to get a reasonable valuation buy price range.

Comparable company analysis (comps) is a relative method of valuation that involves comparing a company to similar companies, in order to estimate the company's value. After you find similar companies that are a good proxy for the target company, you can use their financial information as a benchmark for valuation. This sheet automates this entire process, and just requires that you identify and input these publicly traded comparable companies. More specifics on this sheet are described below:

1. Comparable Companies
   • Identify the appropriate comparable companies and input the stock ticker names in the top-left section of this sheet. The Wisesheets API will then pull the basic share count, diluted share count, market cap, enterprise value, revenues over last 3-years, EBITDA's over the last 2-years, and net income's over the last 2-years for all of these comparable companies. On the right side of the sheet, you'll see a colored scale (green = better, red = worse) comparing the comparable companies' trading ratios and trading multiples. This is then summarized below, with the maximum, 75th percentile, mean, median, 25th percentile, and minimum distribution calculations.
2. Implied Valuation
   • The bottom section of this sheet is where the implied valuation is done, based on the inputted comparable companies. This is also broken out into the maximum, 75th percentile, mean, median, 25th percentile, and minimum distribution calculations. Follow the calculations from left to right to see how the implied share prices are calculated. The only input you can change (which is optional) is selecting between the company's basic shares outstanding or diluted shares outstanding, which calculates the equity value of revenue, EBITDA, and net income. The implied share price itself provides a range of values in which the company's estimated value lies, with the mean and median typically being the most valid cases.

The BACKEND tab is used for backend calculations that do not require any inputs, but are vital for the workbook to function properly. Currently, this tab includes the following calculations:

• 52-Week High/Low: This is used in the DASHBOARD tab as well as the COMPS tab.
• Stock History Data: Shows the most recent closing stock price data, the closing stock price, the open price, the high price, the low price, and the volume. This information is referenced in the DASHBOARD tab.
• Data Range Selection: This is where the model determines when the last actual date of historical financials is for a company, its last fiscal year, its calendar year, the number of available historical years, and the available valid years for the "Analysis Year" dropdown on the DASHBOARD tab. Key information from this section is shown at the top of the DASHBOARD tab, and is reflected across almost every tab in this workbook.
• Historical Stock Prices: This is where the backend data for the company's stock chart and security comparison chart is located.
• Key Financials, Long Key Ratios Labels, Short Key Ratios Labels: These are long filtered and sorted columns of data that act as the data source for the KFS, KFR, and short KFR dropdown options found across the workbook.