Table of Contents Introduction . Introduction . . . . . . . . . . . . . . . . . . . . 2 Templates Alpha Models. . . . . . . . . . . . . . . . 3 Charts . . . . . . . . . . . . . . . . . . . . . . . . 4 Commodities and FX. . . . . . . . . . . . . . . . 5 Company Tearsheets . . . . . . . . . . . . . 6 Data Explorers Explorers . . . . . . . . . . . . . . . .7 .7 Equity Comparables . . . . . . . . . . . 8 Estimates. . . . . . . . . . . . . . . . . . . . .9 Fixed Income. Income. . . . . . . . . . . . . . . .10 Indices and Ownership. Ownership. . . . . . . . . . .11 Industry . . . . . . . . . . . . . . . . . . . . . 12 Macroeconomic . . . . . . . . . . . . . . . 13 People . People . . . . . . . . . . . . . . . . . . . . . . . 14 Real-Time . . . . . . . . . . . . . . . . . . 15 15 Segments . . . . . . . . . . . . . . . . . . . . . 16 Transaction . . . . . . . . . . . . . . . . . . . 17 17 Valuation . . . . . . . . . . . . . . . . . . . . . . .18 . 18 Training The Street . . . . . . . . . . . . .19 .1 9 Plug-In Tools . . . . . . . . . . . . . . . . . . 20
^Table of Contents
Accessing Templates* The Capital IQ Excel Plug-In provides a number of comprehensive templates that can be customized. To access templates in the Capital IQ Excel Plug-In, click on the “ Capital IQ” menu item in the toolbar. Go to “Templates” and select “Get/Update
Templates.” *Please contact your Capital IQ Account Manager or Client Support at US: 888.806.5541 or Global: +44.(0)20.7176.1234 / +1.212.438.1070 to receive
the templates not available for direct download in the Capital IQ templates dropdown. All templates are complimentary.
^Table of Contents
Alpha Models Alpha Models Provides a proprietary Alpha Rank from 1-100 for publicly traded U.S. companies. Utilizes four models (Value, Growth, Quality, and Momentum) and various factors in each model to derive a composite score.
^Table of Contents
Charts Historical Bond Graph
Historical Multiples vs. Price Chart
VWAP
Plot the performance of a fixed income security on a
Plot the pricing history of a company, along with a
Chart the volume weighted average price (VWAP) as
historical basis. User also has the choice to plot YTW
moving average (user can select number of days for
well as a trailing VWAP for a given company.
or STW.
the moving average), overlaying a historical valuation multiple of their choice.
Individual and Dual Chart Template
Yield Curve Chart various yield curves at a given date.
Plot two financial metrics on graphs vs. each other
Shares Traded Analysis
or plot financial metrics on the same graph to compare
Compares equity trading levels for a company across a
27-11 Chart
two companies. Graphs include: Income Statement,
range of close prices for a specified time period.
Chart recommendations and target stock price
Balance Sheet, Cash Flow Statement, Short-Term
information overlayed over a historical price chart.
Liquidity, Long-Term Solvency, Leverage/Coverage,
Stock Price/Volume Chart
Valuation, Margin Analysis and Growth Analysis.
Chart a company’s stock price (unadjusted, dividend adjusted, or volume weighted average price (VWAP))
Annotated Stock Price/ Volume Chart
and volume history.
Overlay key development inputs onto a historical stock
Valuation Multiples Chart
price/volume chart.
Plot various valuation multiples, with the ability to display frequency (daily, weekly, monthly, quarterly
Currency Conversion Plot historical exchange rates between 180 currencies and compare current exchange rates for up to 11 currencies at once.
and yearly).
Users can input their own proprietary information.
^Table of Contents
Commodities and FX Commodities Pricing Estimates Template Replicates the Commodities Est. Forecast pages on the Capital IQ website. Allows users to view data on commodity price targets and detail on individual analyst estimates that make up the consensus target.
Futures Template Replicates the Futures pages on the Capital IQ website. Allows users to view data on futures contracts across 75+ commodity categories and 80+ foreign exchange categories.
^Table of Contents
Company Tearsheets Financial Snapshot Tearsheet Provides business description, key executives and
Key Developments, News and Events Template
board members, enterprise value build-up,
View Key Developments, Events and News data for a
summary historical/projected financial metrics
selected company. Users can filter Key Developments
and corresponding multiples. Also includes a second
across 10 Major Categories and over 100+ Sub Types
page with a summary income statement, balance
within a specified time-frame. Also allows users to
sheet and cash flow data, summary profitability ratios,
track upcoming earnings calls and dial-in information
margins analysis, liquidity analysis and leverage
for a selected universe of companies.
multiples. Provides the option to chart Revenue, EBITDA, EBIT or EPS metrics.
One Page Tearsheet Provides business description, key executives and board members, enterprise value build-up, summary historical/projected financial metrics and corresponding multiples. Also gives users options of selecting a one-year price volume chart vs. a relative price performance chart. Provides the option to chart Revenue, EBITDA, EBIT or EPS metrics.
^Table of Contents
Data Explorers Data Explorers Short Interest Overview View summary information on short interest and other indicators for multiple companies as well as detailed data and charts for a single company.
Data Explorers Portfolio Dashboard Provides a detailed aggregation of Data Explorers data for portfolio managers. Provides the ability to import portfolio holdings and analyze short interest, sentiments, and other indicators for individual holdings and the portfolio as a whole.
^Table of Contents
Equity Comparables Detailed Comps
target companies (Balance Sheet, Income Statement,
A more detailed version of the Summary Comps.
Cash Flow Statement, Multiples, Ratios and Market
Includes capital structure breakdown, ratings
Data). Once the user selects a category, another
information and historical valuation multiples.
dropdown will allow them to choose a financial data item under the category to display.
Industry Summary Comps (Airline, Cable, Healthcare, Home Builder, Hotels and Gaming, Internet Media, Metals and Mining, Oil and Gas, Pharmaceuticals and Biotech, Restaurant, Retail, Semiconductor, and Telecom) Compare companies in a specific industry based on industry specific valuation metrics.
Screening Template Import screening results directly into a spreadsheet and then choose the financial metrics to display for the
Bank Comps Compare banks, thrifts, and other depository institutions using industry specific metrics.
Profile and Comps Includes a financial statements tab (with historical income statement, balance sheet and cash flow data) and a summary one-page tearsheet for the subject company. Subsequent tabs display summary and detailed trading multiples output sheets.
Comp Set Replicates the comparable company set downloaded from Quick Comps on the Capital IQ website.
^Table of Contents
Estimates Detailed Broker Estimates Template displays summary estimate information with broker by broker detail and allows for the creation of custom consensus estimates. Data includes contributor and analyst names, historical and forward estimates and multiples from individual contributors, and broker specific surprise, among other data.
Estimates Export of the Estimates tab from the Capital IQ website, but with Excel formulas driving the data.
Intelligent Estimates Capital IQ has developed a robust approach to forecasting company earnings. Rather than focus on the most accurate analyst, the Intelligent Estimates template focuses on the attributes of an accurate forecast. These attributes provide a more accurate estimate over the traditional equal weighted consensus forecast.
^Table of Contents
Fixed Income Credit Comps Compare the credit statistics of various companies
Credit Default Swaps and Index Template
against each other. Includes items such as ratings
View historical pricing and perform comparable analysis
information, leverage and coverage ratios and a full
for Credit Default Swaps and CDS Indices. Includes
capital structure breakdown.
items such as CDS name, type, term, restructuring type, pricing, ratings and leverage and coverage multiples.
Fixed Income Comps Input fixed income security tickers for a comparable
Fixed Income Tearsheet
fixed income security analysis. Includes items such as
Replicates the Fixed Income Summary page on the
issue size, coupon, maturity date, rating, price, YTW
Capital IQ website. Provides both book and market value
and leverage and coverage multiples.
debt overviews.
Barclays Capital Indices
Securities Summary
Displays 975+ Barclays Capital Fixed Income Indices.
Export of the Securities Summary tab from the Capital IQ
Users will have access to a tearsheet of popular indices
website, but with Excel formulas driving the data.
as well as a chart that graphs up to 10 indices and various pricing and fixed income related data points.
Structured Securities Analysis Provides a comparable overview of up to 5 structured securities.
^Table of Contents
Indices and Ownership Institutional Crossholdings
Public Holdings
Provides ownership data for the top 100 institutional
Replicates the Detailed and History Public Holdings
holders of a company and displays those institutions’
pages on the Capital IQ website. Allows users to
crossholdings in up to 10 additional companies. Data
view the current and historical public holdings of a
can be viewed by percentage owned, number of shares
selected private investment firm. Data includes
held, or market value.
portfolio composition, share count, value, investment type and position date.
Mutual Fund Tearsheet Replicates the Tearsheet for a Mutual Fund on the
Public Ownership
Capital IQ website. Allows users to pull pricing data,
Replicates the Public Ownership pages on the
investment criteria, investment style, holdings data by
Capital IQ website. Allows users to view summary
industry, geography and company, advisors and spon-
ownership information by owner type, top buyers
sors, and key professionals.
and sellers, crossholdings, and detailed ownership historically.
Private Equity Tearsheet Profiles a selected PE firm and provides data on their
Index Summary
current, pending and prior investments, as well as
Replicates the Tearsheet, Constituents, Analyst
co-investors and investments as an LP. The template
Coverage, and Key Stats & Ratios pages for Indices on
also displays specific fund level data, such as the size,
the Capital IQ website. Allows users to view aggregate
date launched, and stage. Lastly, the template shows
data on indices as well as its individual constituents.
a fund’s investment criteria on a geographic, industrial, and financial basis.
^Table of Contents
Industry Includes Income Statement, Balance Sheet, Cash Flow
•
Healthcare
Key Stats
•
Home Builder
Export of the Key Stats tab from the Capital IQ website,
•
Hotels and Gaming
but with Excel formulas driving the data. Available
•
Insurance
for both the Standard Template and Industry Specific
•
Internet Media
Balance Sheet data, input company comparables and
•
Metals and Mining
Download Financials*
provides summary and detailed outputs for
•
Oil & Gas
With one formula (CIQMATRIX), users can retrieve the
valuation multiples and a variety of other financial
•
Pharmaceuticals and Biotech
Standard, Industry Specific or As Presented Income
•
REIT
Statement, Balance Sheet or Cash Flow Statement of a
•
Restaurant
company.
•
Retail
•
Semiconductor
•
Telecom, Cable and Wireless
•
Thrift Regulatory (US)
•
Utility
Statement, supplemental line items and ratios for a selected company, with a dropdown to choose period type and order of periods (most recent on the leftmost or rightmost column). On subsequent tabs, it allows users to view common size Income Statement and
metrics. The line items provided in the templates will vary by industry.
•
•
Standard Banks
•
Bank Regulatory (US)
•
D&B Standard
•
Airlines
•
Brokerage
•
Financial Services
Templates.
Toyo Keizai Six Industry templates using data from Japanese data provider Toyo Keizai as well as a robust Estimates template using Toyo Keizai estimates and guidance data.
^Table of Contents
Macroeconomic Macroeconomics Select a country to view 30 historical and 9 forecasted Macroeconomic data points associated with that country. Most metrics also have the ability to show not only the value but various growth rates, dates, footnotes, and other useful data. For Macroeconomic data associated with the United States, there are over 575 data points, which also have the ability to show growth rates, etc. United States Macroeconomic da ta groups include GDP, Balance of Payments, Corporate, Government Receipts, Consumption and Investment, Labor and Unemployment, Housing, Manufacturing, Retail and Wholesale, Industrial Production, Money Supply, Inflation, Consumer and Business Sentiment, and Energy.
Macroeconomic Events Retrieve past and upcoming macroeconomic events for both countries and the world as a whole. Provides dates and times for the events, as well as forecasts, actuals, and previous results.
^Table of Contents
People People View qualitative data on the top executives and board members of a selected company and its peers. Available data includes background, education, present and past board and professional relationships, age, compensation, and contact information.
^Table of Contents
Real-Time Real-Time Full Quote Tearsheet * Allows users to display summary data on a selected company in real time as provided by Capital IQ’s RealTime Application. Data includes pricing, volume, summary financials, multiples and news. If the template is left running during trading hours, data on intraday trading will be recorded for covered companies.
Real-Time Market Tearsheet * Allows users to display data on global indices and a universe of companies of their choosing (via Capital IQ Screening) in real time as provided by Capital IQ’s RealTime Application. Data includes pricing, news, and daily gainers and losers. * Requires Version 7 of the CIQ Excel Plug-in or higher
^Table of Contents
Segments Business and Geographic Segments Template* Provides operating statistics and revenue contribution charts for a selected company’s business and geographic segments.
Key Stats (w. Segments)* Export of the Key Stats tab of the Capital IQ website, but with Segment data and Excel formulas driving the data.
One Page Tearsheet (w. Segments)* Provides business description, key executives and board members, enterprise value build-up, summary historical/ projected financial metrics, corresponding multiples and segment data. Also gives users options of selecting a one-year price volume chart vs. a relative price performance chart. Provides the option to chart Revenue, EBITDA, EBIT or EPS metrics.
Segment Comps* Provides a comparison of segment operating statistics for selected companies and business segments. * Requires Version 6 of the CIQ Excel Plug-in or higher
^Table of Contents
Transaction Transaction Comps Analyze transaction values and multiples for precedent deals in order to arrive at a valuation for a future transaction.
Transaction One Page Tearsheets Analyze a company transaction with a detailed breakdown based on each individual transaction. Users can dig deeper into a specific M&A, IPO, Private Placement, Buyback, Bankruptcy, Spin-Off/Split-Off, and TARP transaction by viewing information such as: General Transaction Details, Transaction Valuation, Important Dates, Pre-Deal Share Performance, and Target & Acquirer LTM financials.
^Table of Contents
Valuation Historical Beta Calculation
Analysis at Various Prices
Projections Model
Calculate historical beta on a weekly or monthly basis
Analyze how various premiums on a target company’s
Project a company’s financial statements, including an
(up to 5 years back) against various benchmarks.
share price will impact the company’s valuation
Income Statement, Balance Sheet, Cash Flow State-
multiples.
ment and debt schedule.
Perform a “quick and dirty” merger analysis with
DCF Model
Share Repurchase
accretion/dilution analysis and pro-forma financials
Calculate the discounted cash flows of a company,
Provides Accretion/Dilution, Cash Flow, and
and multiples.
including WACC and exit multiple sensitivity outputs.
Capitalization statistics for various share repurchase
Quick Merger
scenarios. Also compares Accretion/Dilution at
WACC Template
Dupont Analysis
Calculate the Weighted Average Cost of Capital
Provides an analysis of where return is derived for a
(WACC) with cost of debt and cost of equity sensitivity
company by breaking down Return on Equity into three
Treasury Method Calculation
analyses. Allows users to input comparable companies
parts: Net Margin, Asset Turnover and Leverage.
Compute fully diluted shares using the Treasury
to determine subject company’s beta. Other user inputs are required to output subject compa ny’s WACC.
various Prices and P/E multiples.
Method. Displays tranche by tranche data for warrants
IPO Valuation
and options outstanding and exercisable.
Provides an implied IPO valuation of a private firm
Accretion/Dilution Comps
using the median P/E and Net Income Margin of
Weighting Template
Compare different companies against each other,
comparable public companies as a benchmark. Output
Weigh a financial metric by using Share Price, Market
highlighting the accretion/dilution that would occur
provides Equity and Enterprise valuation and multiples,
Cap, TEV or Equal weights as a weighing method. The
if a buyer were to purchase the company on a pro
potential share count, and per share price for a range
user can see the actual value compared to the adjusted
forma basis.
of P/E premiums.
weighted value side by side.
Analysis at Various PE Analyze how various premiums on a target company’s P/E ratio will impact the company’s valuation multiples.
^Table of Contents
Training The Street Templates TTS – DCF Template A basic Discounted Cash Flow model designed by Training The Street and powered by Capital IQ. The template covers the three major concepts of a DCF: calculating WACC, projecting Free Cash Flows, and determining a terminal value.
TTS – LBO Template A basic Leveraged Buyout model designed by Training The Street and powered by Capital IQ. The template introduces the basic concepts behind an LBO, the common underlying assumptions, and the steps required to construct a simple functioning LBO model.
TTS – Merger Model The template introduces the basic concepts behind a merger model, the common underlying assumptions, and the steps required to construct a simple functioning merger model.
^Table of Contents
Plug-In Tools Excel Plug-In Formula Index A comprehensive directory of all Capital IQ Excel Plug-In formulas.
Capital IQ Ticker Convertor This utility allows a user to input company names and/or raw tickers to retrieve the Capital IQ ID for use with the Capital IQ Excel Plug-In.
