e
S&P Capital IQ
Excel Plug-in
Template Guide
August 2017
Table of Contents
Templates
ACCESSING TEMPLATES* .............................................................................................. 3
SNL AND CIQ COMBO
†
.................................................................................................... 4
ALPHA FACTORS ............................................................................................................. 6
CHARTS ............................................................................................................................ 7
COMMODITIES AND FX ................................................................................................... 9
COMPANY TEARSHEETS .............................................................................................. 10
CREDIT ANALYTICS ....................................................................................................... 12
CREDIT HEALTH PANEL ................................................................................................ 13
EQUITY COMPARABLES ............................................................................................... 14
ESTIMATES .................................................................................................................... 15
FINANCIALS.................................................................................................................... 16
FIXED INCOME ............................................................................................................... 18
LOCAL LANGUAGE TEMPLATES .................................................................................. 19
MACROECONOMIC ........................................................................................................ 20
OWNERSHIP................................................................................................................... 21
PORTFOLIO AND INDEX ................................................................................................ 23
PROPRIETARY DATA ..................................................................................................... 26
RATINGS DIRECT .......................................................................................................... 27
SEGMENTS……………………………………………………………………………………….28
SPCIQ EXCEL PLUG-IN TOOLS ................................................................................... 289
TRAINING THE STREET ................................................................................................. 30
TRANSACTION ............................................................................................................... 31
VALUATION .................................................................................................................... 32
WORKFLOW ................................................................................................................... 34
S&P Capital IQ Excel Plug-In Template Guide
August 2017
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Accessing Templates | 3
ACCESSING TEMPLATES*
The S&P Capital IQ Excel Plug-In provides a number of comprehensive templates that can be customized. To
access
templates in the S&P Capital IQ Excel Plug-In, click on the “S&P Capital IQ” menu item in the toolbar.
Go to
“Templates” and select “Get/Update Templates.”
*Please contact your S&P Capital IQ Account Manager or Client Support at US: 888.806.5541,EMEA:
+44.(0)20.7176.1234 , APAC: +852-2533-3565 to receive the templates not available for direct download in the
S&P Capital IQ templates dropdown. All templates are complimentary.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
SNL and CIQ Combo | 4
SNL AND CIQ COMBO
†
†
Requires access to both S&P Capital IQ and SNL Excel add-ins.
Company Mine NPV
Consensus Estimates Tearsheet
Country Credit Risk Analysis
Crossholdings Analysis
Energy Conference Call and Earnings Calendar
Energy Midstream Profile
Energy Power Profile
Global Real Estate Profile
Insurance Global Profile
Macroeconomics Overview
Media & Communications Global Multichannel Top Operators
Media & Communications Profile
Metals & Mining Company Profile
Metals & Mining Peer-Index-Commodity Comparison
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Alpha Factors | 6
ALPHA FACTORS
AFL Multi-Factor Screening
Perform Multi-Factor Screening to support the stock selection process with a filtering and ranking mechanism
based on select fundamental criteria.
AFL Portfolio Style Analysis
A visual heatmap of portfolio and index style tilts using AFL style rankings.
CIQ Alpha Models
The CIQ Alpha Models template intelligently blends industry and company fundamental analysis with robust
quantitative
validation to provide institutional investors with profitable, intuitive stock selection strategies and
portfolio-level workflow
enhancements. Leveraging the S&P Capital IQ proprietary Alpha Factor Library, each
model contains the key ratios and
value drivers needed to outperform the market. Covering 4,800 active
securities, CIQ Alpha Models utilizes four models
(Value, Growth, Quality, and Momentum) to deliver superior
risk-adjusted returns across a variety of investment strategies
and horizons. Each of the four models provides
proprietary rankings from 1-100 on individual factors, which are then used
to derive the Composite Score on the
same 1-100 scale, signifying best to worst.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Charts | 7
CHARTS
Annotated Stock Price/ Volume Chart
Overlay key development inputs onto a historical stock price/volume chart.
Corporate Credit Tearsheet
Review a simple credit analysis for a corporate entity. Contains multilingual outputs for Japanese, Korean, and
Simplified Chinese.
Corporate Yield Curves
The Corporate Yield Curve data set provides yield and credit spread term structures by Currency, Sector & S&P Credit
Rating using corporate bond quotes.
Currency Conversion
Plot historical exchange rates between 180 currencies and compare current exchange rates for up to 11 currencies at
once.
Custom Index Chart
Create and chart custom indices comprised of companies of your choosing.
Historical Multiple vs Price Chart
Plot the pricing history of a company, along with a moving average (user can select number of days for the moving
average), overlaying a historical valuation multiple of their choice.
Individual and Dual Charts
Plot two financial metrics on graphs vs. each other or plot financial metrics on the same graph to compare two companies.
Graphs include: Income Statement, Balance Sheet, Cash Flow Statement, Short-Term Liquidity, Long-Term Solvency,
Leverage/Coverage, Valuation, Margin Analysis and Growth Analysis.
Market View
A performance and financial metric overview of global markets, sectors, commodities, FX rates, and interest rates in an
intuitive one-page format. Includes the ability to adjust periods, metrics, and markets using scroll bars.
Risk vs. Return
Build your own custom indices and plot a scatter chart of risk versus return.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Charts | 8
Shares Traded Analysis
Compares equity trading levels for a company across a range of close prices for a specified time period.
Valuation Multiples Chart
Plot various valuation multiples, with the ability to display frequency (daily, weekly, monthly, quarterly and yearly).
VWAP
Chart the volume weighted average price (VWAP) as well as a trailing VWAP for a given company.
Yield Curve
Chart various yield curves at a given date.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Commodities and FX | 9
COMMODITIES AND FX
Commodities Pricing Estimates
Replicates the Commodities Est. Forecast pages on the S&P 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
Replicates the Futures pages on the S&P Capital IQ website. Allows users to view data on futures contracts across 75+
commodity categories and 80+ foreign exchange categories.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Company Tearsheets | 10
COMPANY TEARSHEETS
Company Profile
Company analysis including a tearsheet, financials, multiples, ratios, key devs, news, events, segments, comps,
and
ownership. Ability to load in a list and print out flipcharts as well.
Corporate Credit Tearsheet
Review a simple Credit Analysis for a Corporate entity – contains multilingual outputs for Japanese, Korean
and Simplified Chinese.
Corporate Governance
View corporate governance policies and takeover defenses for a company.
Corporate Tree
This template mimics the Corporate Tree link on the S&P Capital IQ website, displaying multiple levels of
subsidiaries for
a company.
Equity Listings
View security level detail for all equity securities for a company, including dividend history.
Events Calendar
View past or upcoming events for a list of companies with the ability to choose the type of event.
Financial Snapshot Tearsheet
Provides business description, key executives and board members, enterprise value build-up, summary
historical/projected financial metrics, multiples, summary statistics and charting options.
Insurance Tearsheet
The Insurance Tearsheet has been created to provide investors with both a Credit & Equity perspective by
combining
S&P Capital IQ fundamental data with key Quantitative Credit Indicators such as market share,
published credit quality,
CDS, index & price chart, valuation multiples & ratios, fundamental credit quality, and
summary Income Statement &
Balance Sheet.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Company Tearsheets | 11
Key Development Screening
Allows screening for up to 15 key dev types on a list of companies with flexible ticker entry, date, and key dev
type
options. Includes hyperlinks back to the SPCIQ platform for key dev details.
Key Developments, News and Events
View Key Developments, Events and News data for a selected company. Users can filter Key Developments
across 10
Major Categories and over 100+ Sub Types within a specified time-frame. Also allows users to track
upcoming earnings
calls and dial-in information for a selected universe of companies.
Markit Partners Short Interest Tearsheet
View summary information on short interest and other indicators as well as detailed data and charts for a single company.
Net House Summary
Provides trade information for Canadian and Australian securities on broker-specific block trades.
One Page Tearsheet
Provides business description, key executives and board members, enterprise value build-up, summary
historical/projected financial metrics, multiples, and charting options.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Credit Analytics | 12
CREDIT ANALYTICS
CA Prop Data Upload Template – Private Companies
Allows users to upload proprietary financial data for private companies including Credit Model and PD Model
tabs.
CA Prop Data Upload Template – Public Companies
Allows users to upload proprietary financial data for public companies including Credit Model and PD Model
tabs.
CA Single Entity Analysis
Conduct robust single entity credit analysis with access to the CA pre-scored database and quantitative scoring
models (PD Fundamental Credit Model).
CA Single Entity Credit Risk Pricing
Conduct robust single entity credit risk analysis and pricing with access to the CA pre-scored database,
quantitative scoring models (PD Fundamental Credit Model) Corporate Yield Curves.
Credit Market Pulse Index
This template was developed to allow users to develop views into overall risk of equity indices as well as drill
down to
explore individual contributions to that risk level. The template is also an additional tool to be used with
the Credit Market
Pulse publication. While the Credit Market Pulse publication will be generated bimonthly, this
template allows users to
review this information on an updated daily basis.
Credit Model Batch Scoring Template
Used for pre-scored and prop data companies for portfolio scoring with model details and stress testing.
Credit Surveillance Template
Assess the credit worthiness of counterparties, competitors, portfolio companies, etc. View aggregate information
as well
as single entity statistics.
Fundamental PD Batch Scoring Template
Used for pre-scored and prop data companies for portfolio PDs with model details and stress testing.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Credit Health Panel | 13
CREDIT HEALTH PANEL
Credit Health Panel
Score companies on Operational, Solvency, and Liquidity measures as compared to their peer set.
Credit Health Panel – Basic
A more simplified version of the Credit Health Panel template which scores companies based on predefined peer lists.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Equity Comparables | 14
EQUITY COMPARABLES
Company Comparable Analysis
Comprehensive comparables report allowing for automatic peers population including quick comps, indices,
portfolios,
and watchlists.
Detailed Comps
A more detailed version of the Summary Comps. Includes capital structure breakdown, ratings information and
historical
valuation multiples.
Industry-Specific Comps
(Airline, Bank, 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.
Screen Watchlist Index Comps
Import Screening, Watchlist, or Index results directly into a spreadsheet and then choose the financial metrics to
display
for each company (Balance Sheet, Income Statement, Cash Flow Statement, Multiples, Ratios and
Market Data).
Summary Comps
Company comparables highlighting pricing, valuation, growth, leverage, and credit metrics available in both
portrait and
landscape versions.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Estimates | 15
ESTIMATES
Corporate Estimates Monitor
Monitor quarterly and yearly broker by broker estimates for a single company.
Detailed Broker Estimates
Template displays summary estimate information with broker by broker detail and allows for the creation of
custom
consensus estimates. Data includes a list of companies covered by a specific broker or analyst, historical
and forward
financial estimates, target prices, and other useful data.
Earnings Watch Dashboard
A visual model to track how an index or your portfolio is performing during earnings season on the aggregate and
constituent level. Includes sector level performance snapshots and the ability to drill down on a single company, as
well as
a preview of upcoming earnings reports.
Estimates – Oil & Gas
Export of the Estimates tab from the S&P Capital IQ website with industry-specific oil and gas estimates as well.
Estimates
Export of the Estimates tab from the S&P Capital IQ website with Excel formulas driving the data.
Guidance
View company guidance estimates for a variety of financial line items.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Financials | 16
FINANCIALS
Includes Income Statement, Balance Sheet, Cash Flow 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 Balance
Sheet data, input company comparables and provides summary and detailed outputs for valuation multiples and
a variety of other financial metrics.
The line items provided in the templates will vary by industry.
•
Airlines
•
As-Presented Standard
•
Banks
•
Bank Regulatory (US)
•
Brokerage
•
Capital Markets
•
Financial Services
•
Healthcare
•
Home Builder
•
Hotels and Gaming
•
Industry-Specific
•
Insurance
•
Internet Media
•
Metals and Mining
•
Oil & Gas
* Requires Version 8 of the S&P Capital IQ Excel
Plug-in or higher
•
Pharmaceuticals and Biotech
•
Private Company Financials
•
Real Estate
•
REIT
•
Restaurant
•
Retail
•
Semiconductor
•
Standard
•
Telecom, Cable and Wireless
•
Thrift Regulatory (US)
•
Utilities
Download Financials*
With one formula (CIQMATRIX), users can retrieve the Standard, Industry Specific or As Presented Income
Statement, Balance Sheet or Cash Flow Statement of a company.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Financials | 17
Key Stats
Export of the Key Stats tab from the S&P Capital IQ website, but with Excel formulas driving the data. Available
for both
the Standard Template and Industry Specific Templates.
Professional Details & Compensation
View qualitative data on the top executives and board members of a selected company and its peers. Avail- able
data includes background, education, present and past board and professional relationships, age, compensation,
and contact
information.
Toyo Keizai
Six Industry templates using data from Japanese data provider Toyo Keizai as well as a robust Estimates tem-
plate using Toyo Keizai estimates and guidance data.
*
Requires Version 8 of the S&P Capital IQ Excel Plug-in or higher.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Fixed Income | 18
FIXED INCOME
Barclays Capital Indices
Displays 975+ Barclays Capital Fixed Income Indices. Users will have access to a tearsheet of popular indices
as well as
a chart that graphs up to 10 indices and various pricing and fixed income related data points.
Credit Comps
Compare the credit statistics of various companies against each other. Includes items such as ratings information,
leverage and coverage ratios and a full capital structure breakdown.
Credit Default Swaps
View historical pricing and perform comparable analysis for Credit Default Swaps and CDS Indices. Includes
items such
as CDS name, type, term, restructuring type, pricing, ratings and leverage and coverage multiples.
Debt Capital Structure Details
This template is designed to show Component Level Details on each Debt Instrument as well as a historical
analysis of
outstanding amounts for a company.
Fixed Income Comps
Input fixed income security tickers for a comparable fixed income security analysis. Includes items such as issue
size, coupon, maturity date, rating, price, YTW and leverage and coverage multiples.
Fixed Income Tearsheet
Replicates the Fixed Income Summary page on the S&P Capital IQ website. Provides both book and market
value debt overviews.
Securities Summary
Export of the Securities Summary tab from the S&P Capital IQ website, but with Excel formulas driving the data.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Local Language Templates | 19
LOCAL LANGUAGE TEMPLATES
The Local Language Templates include three of our most popular templates (Detailed Comps, One Page Tearsheet,
Standard Template), as well as several of our Credit Analytics models, translated in their entirety.
•
Japanese
•
Korean
•
Simplified Chinese
•
Traditional Chinese
Detailed Comps
A more detailed version of the Summary Comps. Includes capital structure breakdown, ratings information and historical
valuation multiples.
Equity Offering Transaction Case Study
Enter a company equity offering transaction and view a presentation-ready case study including all relevant information.
M&A Transaction Case Study
Enter a company M&A transaction and view a presentation-ready case study including all relevant information.
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.
Standard Template
Includes Income Statement, Balance Sheet, Cash Flow 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 Balance Sheet data, input company
comparables and provides summary and detailed outputs for valuation multiples and a variety of other financial metrics.
*
Requires Version 8 of the S&P Capital IQ Excel Plug-in or higher
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Macroeconomic | 20
MACROECONOMIC
Macroeconomic Charts
Chart and compare various macroeconomic data points for countries.
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.
Macroeconomics Overview
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 data 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.
Sovereign Credit Risk Analysis
A clickable world heat map covering a variety of sovereign credit risk indicators.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Ownership | 21
OWNERSHIP
Crossholdings
Provides ownership data for holders of a company and displays those holders’ crossholdings in up to 19 additional
companies. Additional data such as equity assets held in sector/comps, buying/selling activity, and clickable US/World
heat maps are also displayed.
Estimated Cost Basis
Estimate the cost basis of holders of public companies using the Average, LIFO, and FIFO methods
Focus List
This model offers a snapshot of the investors a CEO / CFO / IRO are meeting with, married with vital holdings data; all in
a presentation-ready and easy-to-produce report
Holdings Aggregator
The Holdings Aggregator model is a quick and easy way to combine the current holdings of several user- selected entities
into an aggregated view. Combine up to 10 portfolios of your choice to view quick summaries of what industries and
stocks they are holding, buying, and selling. Use it to spot trends in a particular asset category and understand what some
of the largest players are rotating in and out of, etc.
Investor Profile
The Investor Profile template generates C-level presentable tearsheets of an institutional investor or mutual fund,
complete with customized ownership data, buy-side meeting contact details, and investment approaches. It is used in deal
and non-deal roadshows and investor conferences.
Mapped Institutional and Fund Holders
View the institutional holders of a security, while being able to drill down to the more granular level of specific funds under
each institutional parent.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Ownership | 22
Private Equity Tearsheet
Profiles a selected PE firm and provides data on their current, pending and prior investments, as well as co-investors and
investments as an LP. The template also displays specific fund level data, such as the size, date launched, and stage.
Lastly, the template shows a fund’s investment criteria on a geographic, industrial, and financial basis.
Public Holdings
Replicates the Detailed and History Public Holdings pages on the S&P Capital IQ website. Allows users to view the
current and historical public holdings of a selected private investment firm. Data includes portfolio composition, share
count, value, investment type and position date.
Public Ownership
This model analyzes the current and historical holders of a single company, provides clickable US and World heat maps
highlighting top buyers/sellers, and displays trend/summary charts.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Portfolio and Index | 23
PORTFOLIO AND INDEX
CIQ Alpha Models
The CIQ Alpha Models template intelligently blends industry and company fundamental analysis with robust quantitative
validation to provide institutional investors with profitable, intuitive stock selection strategies and portfolio-level workflow
enhancements. Leveraging the S&P Capital IQ proprietary Alpha Factor Library, each model contains the key ratios and
value drivers needed to outperform the market. Covering 4,800 active securities, CIQ Alpha Models utilizes four models
(Value, Growth, Quality, and Momentum) to deliver superior risk-adjusted returns across a variety of investment strategies
and horizons. Each of the four models provides proprietary rankings from 1-100 on individual factors, which are then used
to derive the Composite Score on the same 1-100 scale, signifying best to worst.
Beneish M-Score
The Beneish M-Score is a mathematical model that uses financial ratios and eight variables to identify whether a company
has manipulated its earnings. The variables are constructed from the data in the company’s financial statements and,
once calculated, create an M-Score to describe the degree to which the earnings have been manipulated.
Bottom-Up Aggregates
Calculates financial and trading data for indices using the bottom-up method of aggregating constituent data.
Constituent Returns
An intuitive way of looking at the performance of securities in a portfolio over various time periods broken down by
sector. Choose between price and total return views.
Credit Market Pulse Index
This template was developed to allow users to develop views into overall risk of equity indices as well as drill down to
explore individual contributions to that risk level. The template is also an additional tool to be used with the Credit Market
Pulse publication. While the Credit Market Pulse publication will be generated bimonthly, this template allows users to
review this information on an updated daily basis.
Custom Index Chart
Create and chart custom indices comprised of companies of your choosing.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Portfolio and Index | 24
Earnings Quality
Calculates key financial statement measures for a list of companies which should be reviewed when evaluating
corporate earnings, including S&P Credit Ratings. This analysis is useful in monitoring changes in the ability of a
company’s assets to generate consistent income prior to being reflected in a company’s income statement.
Equity Portfolio Tearsheet
View summary statistics of a portfolio benchmarked against an index, ETF, or another portfolio.
HeatMap Template
Graphical representations of MarketCap and Performance dispersion within an index, portfolio or ETF.
Historical Aggregates
View index ticker-level aggregates for Financials, Multiples, Ratios and Key Stats.
Index Summary
Replicates the Tearsheet, Constituents, Analyst Cover- age, and Key Stats & Ratios pages for Indices on the S&P Capital
IQ website. Allows users to view aggregate data on indices as well as current and historical constituents.
Market Trends Growth – Revision - Value
Provides an analysis of recent trends in growth, estimate revisions, operating performance, and valuations for an index,
portfolio, or ETF.
Multiple Portfolio Analysis
Two page report comparing up to four portfolios or a mix including benchmarks.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Portfolio and index | 25
Performance Snapshot
A snapshot view of a portfolio or composite highlighting top winners/losers, most/least active stocks based on average
volume, sector summaries, and top holdings. Includes adjustable composite-level performance metrics and a
performance chart.
Piotroski F Score
The Piotroski F Score ranges from 0-9 (with 9 being the best) and reflects the strength of a firm’s financial position
according to Joseph Piotroski, a Chicago Accounting Professor who devised the score.
Portfolio Beta
Risk-based portfolio model presenting Beta, R-Squared, and Standard Error statistics. Allows for customizable time
period, frequency, and benchmark index.
Portfolio Snapshot
Provides a high-level overview of portfolio characteristics and performance attribution including benchmark selection.
Report Builder
Flexible data item display for a portfolio and benchmarks at the sector and aggregate level.
Value at Risk
Value at Risk (VaR) is a statistical technique used to measure and quantify the level of financial risk within an investment
portfolio over a specific time frame. It estimates how much an investment might lose , given normal market conditions, in
a set time period such as a day.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Proprietary Data | 26
PROPRIETARY DATA
Bank-Specific Prop Data Upload Template
This template allows users to upload proprietary financial data for private and public bank-specific companies.
CA Prop Data Upload Template – Private/Prop Companies
Allows users to upload proprietary financial data for private and public companies including Credit Model and PD Model
tabs.
Credit Health Panel with Prop Data
Standard CHP template enhanced to incorporate Prop Data to allow rating of private companies where CIQ Financials are
not available.
Private Company Comps
An easy way to perform Quick Comps analysis for Private Companies. As part of this workflow, users can leverage our
Proprietary Data capabilities to upload financial data. The data is presented in both table format and easy-to-analyze chart
views.
Prop Data Upload
This template allows users to upload proprietary financial data for private and public companies.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Ratings Direct | 27
RATINGS DIRECT
Credit Stats Direct – Aggregates for Industrials & Utilities
Calculate and analyze Credit Stats Direct credit metrics for Industrials & Utilities companies.
Credit Stats Direct – Banks
View S&P ratings and other market derived signals as well as pre-adjusted and adjusted financials for banks.
Credit Stats Direct – Industrials & Utilities
View S&P ratings and other market derived signals as well as pre-adjusted and adjusted financials for industrials and
utilities companies.
Credit Stats Direct – Public Finance
View reported financial statement information for U.S. public finance issuers.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
SP CIQ Excel Plug-In Tools | 28
SEGMENTS
Business & Geographic Segments
Provides operating statistics and revenue contribution charts for business and geographic segments.
Geographic Segment Intelligence
View geographic segment data in a standardized and aggregated format for an index, a portfolio, watchlist, or a custom
set of company comparables. Analyze region, currency, and country exposure for both growth prospects and risk
mitigation.
Segment Comps
Provides a comparison segment operating statistics for selected companies and business segments.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
SP CIQ Excel Plug-In Tools | 29
SPCIQ EXCEL PLUG-IN TOOLS
SPCIQ Contributor Index
Reference document containing Contributor IDs and Codes that are able to be used with SPCIQ Detailed
Contributor Estimate formulas.
SPCIQ Excel Guide
Focuses on native Excel functionality and tools, providing an overview of topics like Excel shortcuts, data
validation, array functions, Index (Match), charting in Excel, and Excel formatting, among others.
SPCIQ Formula Index
A comprehensive directory of all SPCIQ Excel Plug-In formulas.
SPCIQ Formula Structure Cheat Sheet
A parameter-by-parameter SPCIQ formula guide which gives users the ability to see the exact structure behind
each data
set’s function calls.
SPCIQ Identifier Converter
This utility allows a user to input company names and/or raw tickers to retrieve the SPCIQ company ID. Users can
also
input names of professionals to retrieve the SPCIQ person ID.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Training The Street | 30
TRAINING THE STREET
TTS-Acquisition Comps Template
A basic Transaction Comparables model designed by Training The Street and powered by S&P Capital IQ.
TTS-DCF Template
A basic Discounted Cash Flow model designed by Training The Street and powered by S&P 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 v2.0
A basic Leveraged Buyout model designed by Training The Street and powered by S&P 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 v2.0
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.
TTS-Trading Comps
A basic Public Comparables model designed by Training The Street and powered by S&P Capital IQ. The template
presents a relative valuation approach to evaluate how the equity markets are valuing a peer group of companies that are
similar to a target company.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Transaction | 31
TRANSACTION
Equity Offering Transaction Case Study
Enter a company equity offering transaction and view a presentation-ready case study including all relevant information.
Investor Activism
Enter a subject Company ID or Activist ID and see all the campaigns against or initiated by that company.
M&A Transaction Case Study
Enter a company M&A transaction and view a presentation-ready case study including all relevant information.
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.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Valuation | 32
VALUATION
Analysis at Various PE & Prices
Analyze how various premiums on a target company’s share price and P/E ratio will impact the company’s valuation
multiples.
DCF
Calculate the discounted cash flows of a company, including WACC and exit multiple sensitivity outputs.
DuPont Analysis
Provides an analysis of where return is derived for a company by breaking down Return on Equity into three parts: Net
Margin, Asset Turnover and Leverage.
Historical Beta Calculation
Calculate historical beta on a weekly or monthly basis (up to 5 years back) against various benchmarks.
IPO Valuation
Provides an implied IPO valuation of a private firm using the median P/E and Net Income Margin of comparable public
companies as a benchmark.
Projections
Project a company’s financial statements and
debt schedule.
Quick Merger
Perform a “quick and dirty” merger analysis with accretion/dilution analysis and pro-forma financials and multiples.
Treasury Method Calculation
Compute fully diluted shares using the Treasury Method. Displays tranche by tranche data for warrants and options
outstanding and exercisable.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Valuation | 33
WACC
Calculate the Weighted Average Cost of Capital (WACC) with cost of debt and cost of equity sensitivity analyses. Allows
users to input comparable companies to determine subject company’s beta. Other user inputs are required to output
subject company’s WACC.
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Workflow | 34
WORKFLOW
A collection of models organized by Workflows that have been numbered in order of importance based on opinions from
both industry experts and the Financial Modeling Team within S&P Capital IQ.
The Workflows highlighted include:
Corporate
o
Corporate Development
o
Investor Relations
Credit
o
Counterparty Credit
o
Fixed Income Portfolio Management
Equity Research
Investment Banking and Private Equity
o
Equity Capital Markets and Corporate Access
o
Investment Banking
o
Private Equity
Investment Management
o
Traditional Asset Management
o
Wealth Management
S&P Global Market Intelligence
S&P Capital IQ Excel Plug-In Template Guide
August 2017
Table of contents >
Copyright © 2017 by S&P Global Market Intelligence, a division of S&P Global Inc. All rights reserved.
No content (including ratings, credit-related analyses and data, valuations, model, software or other application or output therefrom) or any
part thereof (Content) may be modified, reverse engineered, reproduced or distributed in any form by any means, or stored in a database
or retrieval system, without the prior written permission of S&P Global Market Intelligence or its affiliates (collectively, S&P Global). The
Content shall not be used for any unlawful or unauthorized purposes. S&P Global and any third-party providers, as well as their directors,
officers, shareholders, employees or agents (collectively S&P Global Parties) do not guarantee the accuracy, completeness, timeliness or
availability of the Content. S&P Global Parties are not responsible for any errors or omissions (negligent or otherwise), regardless of the
cause, for the results obtained from the use of the Content, or for the security or maintenance of any data input by the user. The Content is
provided on an “as is” basis. S&P GLOBAL PARTIES DISCLAIM ANY AND ALL EXPRESS OR IMPLIED WARRANTIES, INCLUDING,
BUT NOT LIMITED TO, ANY WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE OR USE,
FREEDOM FROM BUGS, SOFTWARE ERRORS OR DEFECTS, THAT THE CONTENT’S FUNCTIONING WILL BE UNINTERRUPTED
OR THAT THE CONTENT WILL OPERATE WITH ANY SOFTWARE OR HARDWARE CONFIGURATION. In no event shall S&P Global
Parties be liable to any party for any direct, indirect, incidental, exemplary, compensatory, punitive, special or consequential damages,
costs, expenses, legal fees, or losses (including, without limitation, lost income or lost profits and opportunity costs or losses caused by
negligence) in connection with any use of the Content even if advised of the possibility of such damages.
Credit-related and other analyses, including ratings, and statements in the Content are statements of opinion as of the date they are
expressed and not statements of fact. S&P Global Market Intelligence’s opinions, analyses and rating acknowledgment decisions
(described below) are not recommendations to purchase, hold, or sell any securities or to make any investment decisions, and do not
address the suitability of any security. S&P Global Market Intelligence assumes no obligation to update the Content following publication in
any form or format. The Content should not be relied on and is not a substitute for the skill, judgment and experience of the user, its
management, employees, advisors and/or clients when making investment and other business decisions. S&P Global Market Intelligence
does not act as a fiduciary or an investment advisor except where registered as such. While S&P Global Market Intelligence has obtained
information from sources it believes to be reliable, S&P Global Market Intelligence does not perform an audit and undertakes no duty of
due diligence or independent verification of any information it receives.
S&P Global keeps certain activities of its divisions separate from each other in order to preserve the independence and objectivity of their
respective activities. As a result, certain divisions of S&P Global may have information that is not available to other S&P Global divisions.
S&P Global has established policies and procedures to maintain the confidentiality of certain non-public information received in connection
with each analytical process.
S&P Global may receive compensation for its ratings and certain analyses, normally from issuers or underwriters of securities or from
obligors. S&P Global reserves the right to disseminate its opinions and analyses. S&P Global's public ratings and analyses are made
available on its Web sites, www.standardandpoors.com (free of charge), and www.ratingsdirect.com and www.globalcreditportal.com
(subscription), and may be distributed through other means, including via S&P Global publications and third-party redistributors. Additional
information about our ratings fees is available at www.standardandpoors.com/usratingsfees.
Contact Us
Americas Sales: 1.877.863.1306 Americas Support: 1.888.806.5541
APAC Sales: +852-2533-3565 APAC Support: +852-2533-3565
EMEA Sales: +44.(0)20.7176.1234 EMEA Support: +44.(0)20.7176.1234
Email: market.intelligence@spglobal.com Email: support.ciq@spglobal.com
