The comparable company analysis (comps) valuation model has been revamped. The two main additions are (1) calendarization, and (2) analyst estimates (via Wisesheets) for forward-looking implied valuation. Both are optional (and can be toggled off or ignored), but are recommended.
Calendarization: Calendarizing ensures that all companies' financial data are adjusted to the same fiscal year-end, providing an accurate and fair comparison across different firms for valuation purposes...
How to Use: Select "Yes" in cell J35, then select a calendarization year in cell J36. Select the same year in cell J67 (for historical implied valuation), or cell J81 (for forward-looking implied valuation). The implied share prices will be based on the calendarized figures.
Forward-Looking Implied Valuation: Forward-looking implied valuation in comps, based on analyst estimates, allows for a more predictive analysis that accounts for expected future performance and growth, providing a potentially more relevant assessment than purely historical data.
How to Use: Select between Low, High, or Avg analyst estimates, sourced from Wisesheets (found on row 42) for revenue, EBITDA, and net income (do the same for EPS in cell R84 as well). If blank, then there are no analyst estimates for the given company. Select an appropriate valuation year in cell J81. Input your assumptions for cash/ST investments and debt/minority interest (cells X86:Y86), or just reference the current fiscal year's for simplicity to complete the valuation.
Description section has also been added for the COMPS tab.
DDMs
Added a new description section to explain the general concept of DDMs, along with relevant links to learn more.
UPDATES
DCFs | TVs
Removed NPV calculations for owners earnings (this was unnecessary), as well as gray conditional format for enterprise value to equity value bridge.
Updated the DCF description and included more relevant links to learn more.
Updated the intrinsic value vs. buy price chart visual.
GRAHAM
Updated the Graham description.
COMPS
Added more options to toggle between last fiscal year (LFY) and TTM (for share count and size figures).
Added a price comparison option, which if enabled highlights green if the implied valuation price is overvalued, and red if undervalued (relative to the current stock price).
Broke up the comps implied valuations into historical and forward-looking valuations, so that you can value a company based on its historical financials and analyst estimates, for any appropriate year.
KFR
Removed "return on common equity" calculations from spreadsheet entirely. Was not producing reliable outputs.
Other:
Updated shortcut reference library on GUIDE tab
Updated default key market data.
Updated default stock screener data.
Changed "*" to "x" for formula multiplication signs.
Default first and last years are now from 2018 to 2023, instead of 2017 to 2022.
Updated default groupings and print areas (Excel only) for DCFs, DDMs, GRAHAM, and COMPS tab.
Updated video and written documentation.
Stock Screener
SCREENER DATA sheet is now unprotected by default, and the macro has been updated so it no longer protects the sheet after it has ran. Protecting this sheet caused errors with loading new data into the tab (via Wisesheets Screener).
FIXED
DCFs | TVs:
Conditional format for green font forecast FCFs was slightly incorrect, this has been fixed.
Other:
GDP growth link has been updated, so its referencing nominal, not real GDP growth.
Changed how 52-week high/low is loaded on the BACKEND. Now quicker and resolves some issues non-US Excel users were facing.
Changed how years are loaded on the BACKEND tab. Now reliable for companies with January fiscal year-ends.
Fixed minor typos on the KFR tab.
V2.6
February 04, 2024
NEW
OVERVIEW: New model configuration has been introduced, which is the currency price conversions. This is only recommended if the stock price currency differs from the reported financial statement currency. See the updated Overview Model Configuration video + written documentation for details.
OVERVIEW/BACKEND: Stock Price Retrieval accuracy and reliability (for "FY" and "CY") has been revamped. See the BACKEND tab if you're interested in the new model configuration, and the same Overview Model Configuration page for specific details.
UPDATES
OVERVIEW
New conditional format (Excel only) for Analysis Year cell and price conversion cells. For Analysis Year, the cell will highlight yellow if Analysis Year =/= last reported fiscal year, and red if it's not an option whatsoever and needs to be changed. For price conversion cells, these cells will highlight red if a conversion rate or "Yes" is inputted/selected (for the CCY conversion selector), when no price conversion is required.
Combined sector/industry label and exchange/country label at top of OVERVIEW tab to be more readable.
DCFs | TVs
Added more market cap, enterprise value, and stock shares outstanding options to DCFs
WACC:
Made slight tweak to cost of preferred stock formula and input labels. No material change.
Updated CAPM formula to be more readable.
Shortcut Reference Library
Added five new shortcut references: =Company_ReportedCurrency, =LFY_StockPrice, _TTM_StockPrice, Stock_ConversionRate, and ConvertCurrencies_Selector
Removed =Stock_EnterpriseValue shortcut. This was the same as =TTM_EnterpriseValue
Updated shortcut reference library on GUIDE tab.
Other:
"Current Stock Price" labels on valuation models are now dropdown input cells, which also offer an option for "CCY Converted Stock Price" (the currency converted stock price).
Moved DSO, DIO, DPO, CCC to be below efficiency section on KFS, instead of below liquidity.
Updated navigation panel labels to be more dynamic, based on currency conversion yes/no selector.
Removed currency labels on MANAGEMENT and OWNERSHIP tabs. Not necessary.
Updated default key market data.
FIXED
OVERVIEW:
Fixed DDM status message if company wasn't a dividend-paying company.
Minor spacing and naming convention updates.
ROCE replaced with RoCapE and RoCommE. Previously, both were labeled as ROCE so one ratio was being ignored.
Fixed missing year labels on revenue/costs breakdown chart, for Excel version.
DDMs:
Fixed typo in H-Model description.
Other:
Currency labels were incorrect for some stock tickers (show at the top of the spreadsheet models). This has been fixed.
Historical fiscal year dates are now exactly one year before for each year (instead of defaulting to month-end).
Screener workbook header names now match header names on stock analysis spreadsheet model.
Made labels for horizontal analysis on IS, BS, CFS 12px, for Excel version.
Fixed common stock repurchased border on KFS.
Fixed some formula errors for median calculations on KFR, for Google Sheets version.
Fixed Auto-Fit text macro. It was off by one row from the V2.5 update.
V2.5
January 16, 2024
NEW
KFS:
Added new supplemental data calculations for Net Borrowing and Effective Tax Rate, as well as respective shortcuts over TTM and LFY.
Added navigation side panels and custom GUIDE tab to Screener Workbook.
UPDATES
COMPS:
Added diluted EPS option for implied valuation.
Enterprise value --> equity value calculation now considers short-term investments (+ cash) and minority interest (+ total debt). This improves the accuracy of the models outputs.
Market value and enterprise value is now based on the LFY, rather than TTM, for more apples-to-apples comparisons and ratios.
GRAHAM:
After further research, revised Graham valuation formula is now just 8.5 (like the original formula), not 9.4 (for the P/E no growth default).
DCFs | TVs:
Made market value vs intrinsic/buy price charts on DCFs tab more consistent with ones on the DDMs tab.
Shares outstanding dropdown changed from LFY --> TTM (basic/diluted shares outstanding), for a more recent shares count.
Changed title from DCF Stub Periods & Mid-Year Convention to just DCF Stub Periods.
Made small tweaks to error labels and popup labels (i.e., mid-year convention doesn't require date of analysis).
Updated terminal value titles.
WACC:
Removed the second WACC heading at the top of the page, redundant.
Adjusted default print areas, rounding, and slight naming conventions tweaks to title.
Added more error checking.
Updated synthetic debt rating link, to now link to 2024 version.
Slight tweak to method numbering.
FCFF | FA | NWC:
Gross Profit Total OpEx, EBITDA, EBIT were being calculated for the historical years, they are now referencing the KFS, which matches precisely to what is pulled from Wisesheets' API (on the IS tab). This improves consistency/accuracy.
Removed short-term debt from current liabilities in NWC schedule... renamed current liabilities to non-cash current liabilities.
Changed days inventory held (DIH) to days inventory outstanding (DIO), which matches what's on the KFR tab (calculation doesn't change, just naming convention).
Slight tweak to where beginning PP&E and depreciation is being pulled from, as shown in the green font color. No change to calculation/result.
KFS:
Improved accuracy of FCFF calculation, by removing ST Debt from current liabilities.
Improved accuracy of FCFE calculation, using net borrowing and considering interest and taxes.
GUIDE:
Made slight tweaks to headers, and some copy. Primary change is to the short tab descriptions.
Other:
Updated documentation hyperlinks.
Updated shortcut list. Removed MinorityInterest, since MinorityInterestLiability already existed and output the same value.
Fixed minor formatting inconsistencies on MANAGEMENT and OWNERSHIP tabs.
Removed stock comparison chart country/exchange selections. This was not reliable.
FIXED
FCFF | FA | NWC:
Fixed EBIAT calculation. Was adding income tax, instead of subtracting it from EBIT. Also fixed on the KFS tab.
Removed negative from Tax % of EBIT assumption calculation.
Fixed small typos and borders.
DDMs:
Word "Stable" was missing from one title on the Two-Stage DDM chart.
Two of the sensitivity tables were referencing the wrong delta value, this has been fixed.
Updated titles of table headers.
WACC:
For tax rate calculation, LFY_EBT has replaced LFY_EBIT
COMPS:
EBITDA calculation was referencing the wrong cell. This has been fixed.
Other:
Made formula spacing and formatting more consistent.
"Dividend is/is not a dividend-paying company" note on OVERVIEW tab was referencing the wrong cell and was sometimes misleading, this has been fixed.
Capitalized tab names. Increased font size on headers from 10px to 12px across the workbook.
Changed "Month's Back" to "No. Month's Back" for stock charts.
Moved StableBread logos into cells themselves, now consistent with Google Sheets version.
V2.4
December 28, 2023
NEW
Stock Price Retrieval: Top of OVERVIEW tab now features a dropdown (under the "Stock Prices" label) to select between the fiscal year (FY) or calendar year (CY), for improved stock analysis accuracy.
How to Use: Selecting "FY" will force all calculations dependent on stock prices to be based on last fiscal year (e.g., 06/30/2023), going back 10 years. Selecting "CY" will do the same, except base it on the last trading day of the year (e.g., 12/31/2023). Charts that are affected have a small note under them. This will affect all of the ratios that rely on stock price and market cap for their calculations (including dividend yield, p/e ratio, etc.).
UPDATES
Stock Screner: SCEENER, SCREENER DATA tabs are now in a completely separate Excel workbook, as they served a different purpose from the stock analysis spreadsheet.
DDMs: Significant update to DDMs tab, improved accuracy, design, functionality, valuation abilities, and added more charts. All 4 models offer improved formulas, descriptions, new charts to compare market price with valuations, new/improved charts to compare DPS with changing rates (mainly DPS & EPS growth), improved formulas, improved wording, 2 sensitivity analysis tables, refined warnings, and additional supplementary data for improved accuracy...
DDM Growth Analysis: Top section is where all dividends per share (DPS, earnings per share (EPS), dividend payout ratio (DPR), and return on equity (ROE) data is being sourced from, for all 4 of the DDM valuation models. Now have the option to input and use next year's DPS, EPS, DPR, and ROE in your model's, if it can be reasonably estimated. Also, restructured historical growth rates section and added a dynamic dividend payout ratio (DPR) pie chart.
DCFs | TVs:
Stub Periods: Added a toggle for "Stub Periods" (partial fiscal year at the start of the forecast). This is an optional advanced DCF toggle that requires you to input the date of analysis.
Other: Made slight tweak to DCF design, now shows fiscal year date + mid-year projection dates every year, which matches the firm's actual fiscal year. NPV calculation is now more accurate, matches the stub period and mid-year convention toggle.
Navigation: Excel buttons have been replaced with links, due to lack of control with buttons.
Valuation: Removed bottom part of sheet (that summarized key inputs/outputs in valuation models), not necessary with navigation links and difficult to read.
Other:
Updated COVER page design and copy.
Updated shortcut list on GUIDE tab.
Stock valuation method dropdown now offers 52-week high/low as an option, and the dropdown functions without any gap spaces.
Minor Excel chart format updates, to make data more readable.
Updated default screener data.
Updated protection settings/cells.
Made slight tweaks to data validation popup messages and formatting
Updated default key market data.
Improved number rounding consistency throughout workbook.
Reduced error messages in Screener workbook, if values in SCREENER DATA tab are blank.
Made slight tweak on how dividend yield and P/E were calculated on KFR tab
Updates to BACKEND calculations to reduce formulas/requests.
FIXED
Year selection and default formatting on Google Sheets version.
V2.3
December 3, 2023
New
Top of DDMs tab (now labeled "Dividend Discount Model (DDM) Growth Analysis") is now significantly more helpful for estimating dividend growth (especially growth into perpetuity), which is a key input in dividend discount models.
Includes calculations/methods for historical dividend growth analysis (including CAGRs inputs like the KFS/KFR tabs), option to input future analyst estimates, sustainable dividend growth rate calculation, and augmented sustainable dividend growth rate calculations (to account for stock buybacks).
Two new charts: One to visualize CAGR's over time, another to visualize basic/diluted EPS and dividend payout ratio (DPR) over time.
Two-stage Model: Removed stock buybacks table and options to adjust for stock buybacks, given that this analysis is now provided at the top of the DDMs tab.
Two and Three-Stage Models: Expected growth rate in first stage is now an input cell instead of an automatic calculation/assumption. Users should reference company's historical dividend/eps growth, which can be analyzed from information found at the top of the DDMs tab.
Updates
Made charts on DDMs tab more consistent across valuation methods.
Excel Version: Flipped football field valuation range graphic so it's consistent with Google Sheets version and low/high input data on VALUATION tab.
Updated source data links for 3-month treasury bill (VALUATION tab) and synthetic ratings (WACC tab).
Reduced formulas on Google Sheets DDMs tab by replacing =CHAR functions with copy/paste subscript/superscripts for DDM formulas/references.
Levered and unlevered beta calculations were using shareholders' equity for the D/E ratio, the more accurate version is to use the firm's market cap (for "E"), which has been updated accordingly.
Removed the year and country labels in the terminal growth warning status messages. This was unnecessary and required manual updating.
Other: Updated the default financials on the SCREENER DATA tab. Updated key valuation ratios data. Slight format, formula, spacing tweaks (mainly on DDMs tab and centering all text vertically for better readability on Google Sheets version).
Fixed
On the COMPS tab, the net income margin calculation was referencing the wrong column in its denominator, this has been fixed.
On the DCFs | TVs tab, two of the intrinsic share price calculations were not referencing the right cell (both referencing the conservative case), this has been fixed.
V2.2
October 31, 2023
New
Google Sheets version has been released! Everything is almost identical to the more powerful Excel version, besides the following:
Removed all Excel VBA Macros, which also meant removing the SCREENER and SCREENER DATA tabs.
On the COMPARISON tab, I had to remove the stock comparison chart, it was clunky and affecting the performance of the spreadsheet.
Google Sheets does not offer iterative data tables (by default), so I had to make a simplified version on the DCFs | TVs tab, and removed the data tables on the DDMs tab altogether.
Other minor changes, such as minor visual edits to the charts, a slightly different GUIDE tab (to be more relevant to Google Sheets), and the buttons on the navigations panels (to jump between spreadsheet tabs) are now links.
On the DCFs | TVs tab, for the intrinsic value and buy price charts, there's now two dropdown options for Conservative, Base, and Optimistic. Previously, the default setting was Conservative.
Updates
On the Excel version, I updated the screener VBA to work more efficiently, and work even if the sheets are protected.
Updated the default financials on the SCREENER DATA tab.
Updated the default key market data (on the VALUATION tab) to match the most recent available market data.
Shortened some of the popup notes and added one on the DCFs | TVs tab for added clarity, if "Yes" is selected for the mid-year calendarization option.
Fixed
On the KFS tab, one of the median formulas was selecting the wrong row, this has been fixed.
Some of the show/hide group buttons on the DCFs | TVs, DDMs, and GRAHAM tabs were off by one row. This has been fixed.
V2.1
September 9, 2023
New
On the COMPARISON tab, users now have the option to visualize stock ticker prices across 40 different countries. This will function the same for stock tickers on U.S. exchanges, but for non-US stocks, users now have the option to (1) select a country, then (2) select the appropriate exchange name that the company is trading in. If these two are correct, then you should be able to see the stock on the comparison chart.
Updates
"Save PDF" macro now temporarily turns formula calculations to "manual," to ensure that data/charts do not refresh or change when you're saving the sheet.
Fixed
On the OVERVIEW tab, the "#VALUE!" error would show sometimes for some non-US dividend-paying companies. This has been fixed, and the message now instead eithers tells you (1) the last dividend paid and the payment date, or (2) if the company has never been a dividend-paying company.
V2.0
September 3, 2023
New
Workbook Navigation Panel: Found on the left-most and/or upper-most grouped row/column on each spreadsheet (depending on the sheet). There's now a universal navigation panel that (1) provides you with a quick overview of the inputted stock ticker, (2) shows what macros are available for the sheet you're currently on, and (3) navigation buttons to all 22 spreadsheet tabs.
NOTE: This is hidden by default on all tabs besides the GUIDE and OVERVIEW tab. Just use the [+] and [-] grouping buttons to show/hide this panel.
VALUATION: Not necessarily a "new" tab, as most of the info on this tab was pulled from the old DASHBOARD tab. However, it now features a weighted average option (if you want to assign different weights to valuation model outputs), an updated format/structure, and the addition of the savings rate as an input. Input your weights from M22:M31, then select "Weighted Avg" in cell H34 to use the new weighted average valuation feature.
MANAGEMENT: This is a non-automated tab. It's a simple tab where you can input common management team and board member information from online sources for a company. Useful if you want to do a deeper-dive on a company.
OWNERSHIP: This is a non-automated tab. It's a tab where you can input insider transactions, insider trading volume over the TTM, ownership breakdown between institutions and shareholders, and the top shareholders of the company. There are two visuals that auto-populate based on the data inputted. Like the MANAGEMENT tab, this is useful if you want to do a deeper-dive on a company.
COMPARISON: This is a new tab where you can compare the stock ticker inputted in the OVERVIEW tab to 4 other stock tickers. You can see the differences between 60 different metrics/ratios/KPIs, as well as stock price performances. I also have a threshold-based color coding option, similar to what the KFR tab offers.
SCREENER: This is a new basic stock screener tab. It utilizes 35 different metrics/ratios/KPIs, and references data in the SCREENER DATA tab. You can input any operator (<=, <, =, >, >=), and any value within the defined range (which is based on the stock's values in the SCREENER DATA tab). Then, click on the "Start Screener" macro button and you'll get all the applicable stocks.
SCREENER DATA: This is a new tab that utilizes Wisesheets' Wise Screener functionality to populate all the financial data in one click. By default, I've loaded in 1000 stocks from the iShares Russell 1000 ETF. You can easily update this entire dataset using Wisesheets' Wise Screener. See their documentation if you're unfamiliar on how it works.
Range Selection: In terms of ranges to select for the Wise Screen, select the input cells on the sheet. This is just H5:H1004 for the ticker range, and I4:AR4 for the parameter range.
Updates
COVER: Now has four links to all relevant pages for this spreadsheet model.
GUIDE: Made significant updates to this sheet. Now explains (1) the requirements needed to use the workbook, (2) updated spreadsheet quick debugging tips, (3) updated spreadsheet best practices, (4) updated spreadsheet tab descriptions, (5) new section on what each macro does, (6) updated workbook cell formatting rules, and (7) an updated Excel shortcut library (now with 430+ custom Excel shortcuts!).
OVERVIEW (previously called the DASHBOARD tab): Significant updates were made to this sheet. (1) Updated formatting and structure, (2) Can choose between =STOCKHISTORY or =WISESHEETS for stock price data/chart visualization, (3) radar chart moved to the top, no longer associated with the valuations section, (4) NEW dynamic chart visual to let you compare range of buy prices to historical and current stock prices, (5) NEW dividend info section and chart, (6) More space to see KFS and KFR ratio labels, as well as new visuals (7) Financial statements reduced to 5Y + TTM, instead of 10Y only. This keeps it as more of an overview, and lets you focus more on recent trends. (8) New chart visuals for the balance sheet that let you see (a) YoY assets and liabilities/equities change (shows % change and size, represented by length of bar chart), and (b) what consists of company's assets, liabilities, and equity over the TTM (via a treemap chart). These charts provide a better picture of YoY changes and the company's current net worth snapshot.
KFS: Column I now has short names for each of the financial statement and supplementary data line items.
DDMs: Removed the NCGFSM from this sheet. This was effectively the TwoSDDM, but let you manually enter in dividends over the next 1-5 years. Very uncommon model with little to no use, so I removed it, along with two data tables and other conditional formatting rules which will help speed up the spreadsheet (primarily when data tables recalculate/save). Also updated the dividend chart so it matches what's on the OVERVIEW tab, updated spacing, and removed default inputs across the models.
BACKEND: A few tweaks were done here to make the model more functional and quick. (1) 52-week high/low for current company and all comparable companies (for COMPS model) all load within one Wisesheets API call, vs. 11 different data ranges. (2) All dividend data is now loaded on the BACKEND tab (vs. on the KFS tab). (3) All company profile data and named ranges are loaded in one Wisesheets API call now vs. 10+ separate locations. (4) Dropdowns on OVERVIEW tab are more defined vs. showing all the ratios in the spreadsheet. The data validation ranges for these sit in the BACKEND tab, and have much more simpler formulas (now using the relatively new =VSTACK formula).
Other minor tweaks to spacing and formats were done across all of the models as well.
Fixed
"Auto Fit Text" Macro: No longer need to unprotect the sheet to run this macro, it's done automatically, and now gives you a status message telling you if it succeeded or not.
On the IS, BS, CFS, KFS, and KFR tabs, the date range above the "min, median, mean, max" labels will automatically adjust (e.g., if company only has 4 years of financial history vs. 10, it will show 2019 -- 2022, not 2013 - 2022).
#CALC! error would sometimes occur due to usage of =TODAY() function in combination with the calendar and fiscal year Wisesheets functions. Although refreshing the data would fix this, this has been fixed and no longer occurs.
If your chosen fiscal year is different than the company's last actual fiscal year, then this should now be reflected accurately across the workbook (stock prices were not adjusting previously to show the chosen fiscal year, so ratios based on the company's stock price were slightly off).
