S&P CAPITAL IQ'S EXCEL PLUG-IN v.8.x: FREQUENTLY USED FORMULAS

S&P CAPITAL IQ'S EXCEL PLUG-IN v.8.x: FREQUENTLY USED FORMULAS

BALANCE SHEET

Cash And Equivalents

Short Term Investments

Total Cash & ST Investments

Accounts Receivable

Total Receivables

Inventory

Total Current Assets

Gross Property, Plant & Equipment

Net Property, Plant & Equipment

Long-term Investments

Total Intangibles

Total Assets

Accounts Payable

Short-term Borrowings

Current Portion of Long Term Debt

Curr. Port. of Cap. Leases

Total Current Liabilities

Long-Term Debt

Capital Leases

Minority Interest

Total Liabilities

Total Pref. Equity

Common Stock

Retained Earnings

Treasury Stock

Total Common Equity

Total Equity

Total Liabilities And Equity

= IQ_CASH_EQUIV

= IQ_ST_INVEST

= IQ_CASH_ST_INVEST

= IQ_AR

= IQ_TOTAL_RECEIV

= IQ_INVENTORY

= IQ_TOTAL_CA

= IQ_GPPE

= IQ_NPPE

= IQ_LT_INVEST

= IQ_GW_INTAN

= IQ_TOTAL_ASSETS

= IQ_AP

= IQ_ST_DEBT

= IQ_CURRENT_PORT_DEBT

= IQ_CURRENT_PORT_LEASES

= IQ_TOTAL_CL

= IQ_LT_DEBT

= IQ_CAPITAL_LEASES

= IQ_MINORITY_INTEREST

= IQ_TOTAL_LIAB

= IQ_PREF_EQUITY

= IQ_COMMON

= IQ_RE

= IQ_TREASURY

= IQ_TOTAL_COMMON_EQUITY

= IQ_TOTAL_EQUITY

= IQ_TOTAL_LIAB_EQUITY

Supplemental Items

Filing Date

Period Date

Shares Outstanding on Filing Cover

Shares Outstanding on Balance Sheet Date

Total Shares Out. On Filing Date

Total Shares Outstanding on BS Date

Book Value/Share

Tangible Book Value

Tangible Book Value/Share

Total Debt

Total Current Debt

Total Non-Current Debt

Net Debt

Total Capitalization

Full Time Employees

= IQ_FILINGDATE_BS

= IQ_PERIODDATE_BS

= IQ_OUTSTANDING_FILING_DATE

= IQ_OUTSTANDING_BS_DATE

= IQ_TOTAL_OUTSTANDING_FILING_DATE

= IQ_TOTAL_OUTSTANDING_BS_DATE

= IQ_BV_SHARE

= IQ_TBV

= IQ_TBV_SHARE

= IQ_TOTAL_DEBT

= IQ_TOTAL_DEBT_CURRENT

= IQ_TOTAL_DEBT_NON_CURRENT

= IQ_NET_DEBT

= IQ_TOTAL_CAP

= IQ_FULL_TIME

INCOME STATEMENT

Total Revenues

Cost Of Revenues

Cost Of Goods Sold

Gross Profit

Selling General & Admin Exp.

R & D Exp.

Depreciation & Amort.

Amort. of Goodwill and Intangibles

Operating Income

Net Interest Exp.

EBT Excl Unusual Items

Total Unusual Items

EBT Incl Unusual Items

Income Tax Expense

Earnings from Cont. Ops.

Earnings of Discontinued Ops.

Extraord. Item & Account. Change

Net Income

Pref. Dividends and Other Adj.

Merger/Restructuring Costs

NI to Common Incl Extra Items

NI to Common Excl Extra Items

Basic EPS

Basic EPS Excl. Extra Items

Weighted Avg. Basic Shares Out.

Diluted EPS

Diluted EPS Excl Extra Itmes

Weighted Avg. Diluted Shares Out.

Normalized Basic EPS

Normalized Diluted EPS

= IQ_TOTAL_REV

= IQ_COST_REV

= IQ_COGS

= IQ_GP

= IQ_SGA_SUPPL

= IQ_RD_EXP

= IQ_DA_SUPPL

= IQ_GW_INTAN_AMORT

= IQ_OPER_INC

= IQ_NET_INTEREST_EXP

= IQ_EBT_EXCL

= IQ_TOTAL_UNUSUAL

= IQ_EBT

= IQ_INC_TAX

= IQ_EARNING_CO

= IQ_DO

= IQ_EXTRA_ACC_ITEMS

= IQ_NI

= IQ_PREF_DIV_OTHER

= IQ_MERGER_RESTRUCTURE

= IQ_NI_AVAIL_INCL

= IQ_NI_AVAIL_EXCL

= IQ_BASIC_EPS_INCL

= IQ_BASIC_EPS_EXCL

= IQ_BASIC_WEIGHT

= IQ_DILUT_EPS_INCL

= IQ_DILUT_EPS_EXCL

= IQ_DILUT_WEIGHT

= IQ_EPS_NORM

= IQ_DILUT_EPS_NORM

Supplemental Items

EBITDA

EBITDA (Incl. Equity Inc. from Affiliates)

EBITA

EBIT

EBITDAR

Net Rental Expense

Normalized Net Income

Same Store Sales Growth %

Effective Tax Rate

Payout Ratio

Interest On Long Term Debt

Total Current Taxes

Total Deferred Taxes

= IQ_EBITDA

= IQ_EBITDA_EQ_INC

= IQ_EBITA

= IQ_EBIT

= IQ_EBITDAR

= IQ_NET_RENTAL_EXP_FN

= IQ_NI_NORM

= IQ_SAME_STORE

= IQ_EFFECT_TAX_RATE

= IQ_PAYOUT_RATIO

= IQ_INT_EXP_LTD

= IQ_CURR_TAXES

= IQ_DEFERRED_TAXES_TOTAL

CASH FLOW STATEMENT

Net Income

Depreciation & Amort., Total

Asset Writedown & Restructuring Costs

Stock-Based Compensation

Net Cash From Discontinued Ops.

Change In Accounts Receivable

Change In Inventories

Change in Acc. Payable

Change in Unearned Rev.

Change in Inc. Taxes

Change in Def. Taxes

Cash from Ops.

Capital Expenditure

Sale of Property, Plant, and Equipment

Cash Acquisitions

Divestitures

Sale (Purchase) of Intangible assets

Net Cash from Investments

Cash from Investing

Short Term Debt Issued

Long-Term Debt Issued

Total Debt Issued

Short Term Debt Repaid

Long-Term Debt Repaid

Total Debt Repaid

Issuance of Common Stock

Repurchase of Common

Issuance of Preferred Stock

Repurchase of Preferred

Common Dividends Paid

Pref. Dividends Paid

Common and/or Pref. Dividends Paid

Total Dividends Paid

Cash from Financing

Net Change in Cash

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

=

IQ_NI_CF

IQ_DA_CF

IQ_ASSET_WRITEDOWN_CF

IQ_STOCK_BASED_CF

IQ_DO_CF

IQ_CHANGE_AR

IQ_CHANGE_INVENTORY

IQ_CHANGE_AP

IQ_CHANGE_UNEARN_REV

IQ_CHANGE_INC_TAX

IQ_CHANGE_DEF_TAX

IQ_CASH_OPER

IQ_CAPEX

IQ_SALE_PPE_CF

IQ_CASH_ACQUIRE_CF

IQ_DIVEST_CF

IQ_SALE_INTAN_CF

IQ_INVEST_SECURITY_CF

IQ_CASH_INVEST

IQ_ST_DEBT_ISSUED

IQ_LT_DEBT_ISSUED

IQ_TOTAL_DEBT_ISSUED

IQ_ST_DEBT_REPAID

IQ_LT_DEBT_REPAID

IQ_TOTAL_DEBT_REPAID

IQ_COMMON_ISSUED

IQ_COMMON_REP

IQ_PREF_ISSUED

IQ_PREF_REP

IQ_COMMON_DIV_CF

IQ_PREF_DIV_CF

IQ_COMMON_PREF_DIV_CF

IQ_TOTAL_DIV_PAID_CF

IQ_CASH_FINAN

IQ_NET_CHANGE

Supplemental Items

Cash Interest Paid

Cash Taxes Paid

Net Debt Issued

Levered Free Cash Flow

Unlevered Free Cash Flow

Change in Net Working Capital

EBITDA - Capex

=

=

=

=

=

=

=

IQ_CASH_INTEREST

IQ_CASH_TAXES

IQ_NET_DEBT_ISSUED

IQ_LEVERED_FCF

IQ_UNLEVERED_FCF

IQ_CHANGE_NET_WORKING_CAPITAL

IQ_EBITDA_CAPEX

Client Support

North America: 888.806.5541 / +1.212.438.1070

EMEA: +44.(0)20.7176.1234

Asia-Pacific: +852 2533 3588

S&P CAPITAL IQ'S EXCEL PLUG-IN v.8.x: FREQUENTLY USED FORMULAS

RATIOS

Return on Assets %

Return on Equity %

Gross Margin %

SG&A Margin %

EBITDA Margin %

Net Income Margin %

Levered Free Cash Flow Margin %

Accounts Receivable Turnover

= IQ_RETURN_ASSETS

= IQ_RETURN_EQUITY

= IQ_GROSS_MARGIN

= IQ_SGA_MARGIN

= IQ_EBITDA_MARGIN

= IQ_NI_MARGIN

= IQ_LFCF_MARGIN

= IQ_AR_TURNS

= IQ_INVENTORY_TURNS

= IQ_CURRENT_RATIO

= IQ_QUICK_RATIO

= IQ_DAYS_SALES_OUT

= IQ_DAYS_PAYABLE_OUT

= IQ_TOTAL_DEBT_EQUITY

= IQ_TOTAL_DEBT_CAPITAL

= IQ_EBIT_INT

= IQ_CAPEX_PCT_REV

= IQ_TOTAL_DEBT_EBITDA

= IQ_NET_DEBT_EBITDA

Inventory Turnover

Current Ratio

Quick Ratio

Avg Days Sales Outstanding

Avg Days Payable Outstanding

Total Debt/Equity

Total Debt/Capital

EBIT / Interest Expense

Capex as % of Revenues

Total Debt/EBITDA

Net Debt/EBITDA

CONSENSUS ESTIMATES

Avg Broker Recommendation (Text)

Avg Broker Recommendation (#)

Price Target

Est. Annual EPS Growth - 5 Yr

Primary EPS Estimate

Revenue Estimate

EBITDA Estimate

EBIT Estimate

Net Income Estimate

Cash Flow Per Share Estimate

# of Analyst Buy Recomm.

# of Analyst Hold Recom.

# of Analyst Sell Recom.

Estimated Next Earnings Date

Next Earnings Date

EPS Difference

EPS Surprise %

Revenue Difference

Revenue Surprise %

GROWTH (Up to 10 years of growth)

Total Revenues, 1 Yr Growth %

Gross Profit, 1 Yr Growth %

EBITDA, 1 Yr Growth %

EBIT, 1 Yr Growth %

Net Income, 1 Yr Growth %

Normalized Net Income, 1 Yr Growth %

Diluted EPS before Extra, 1 Yr Growth %

Common Equity, 1 Yr Growth %

Inventory, 1 Yr Growth %

Total Assets, 1 Yr Growth %

Tangible Book Value, 1 Yr Growth %

Cash from Operations, 1 Yr Growth %

Capital Expenditures, 1 Yr Growth %

Levered Free Cash Flow, 1 Yr Growth %

Unlevered Free Cash Flow, 1 Yr Growth %

Dividend per Share, 1 Yr Growth %

= IQ_TOTAL_REV_1YR_ANN_GROWTH

= IQ_GP_1YR_ANN_GROWTH

= IQ_EBITDA_1YR_ANN_GROWTH

= IQ_EBIT_1YR_ANN_GROWTH

= IQ_NI_1YR_ANN_GROWTH

= IQ_NI_NORM_1YR_ANN_GROWTH

= IQ_EPS_1YR_ANN_GROWTH

= IQ_COMMON_EQUITY_1YR_ANN_GROWTH

= IQ_INV_1YR_ANN_GROWTH

= IQ_TOTAL_ASSETS_1YR_ANN_GROWTH

= IQ_TBV_1YR_ANN_GROWTH

= IQ_CFO_1YR_ANN_GROWTH

= IQ_CAPEX_1YR_ANN_GROWTH

= IQ_LFCF_1YR_ANN_GROWTH

= IQ_UFCF_1YR_ANN_GROWTH

= IQ_DPS_1YR_ANN_GROWTH

Total Revolving Credit

Total Term Loans

Total Sr. Bonds and Notes

Total Sub. Bonds and Notes

Cap. Leases (Incl. Curr. Portion)

Other Borrowings

Total Principal Due

Total Senior Debt

Total Senior Debt (% of Total Debt)

Total Sub. Debt

Total Convertible Debt

Total Short-Term Debt

Total Short-Term Debt (% of Total Debt)

Long-Term Debt (Incl. Cap. Leases)

Senior Debt/EBITDA

Senior Debt/(EBITDA-CAPEX)

Total Sub. Debt/ EBITDA

= IQ_CP

= IQ_RC

= IQ_TERM_LOANS

= IQ_SR_BONDS_NOTES

= IQ_SUB_BONDS_NOTES

= IQ_CAPITAL_LEASES_TOTAL

= IQ_OTHER_DEBT

= IQ_TOTAL_PRINCIPAL

= IQ_SR_DEBT

= IQ_SR_DEBT_PCT

= IQ_TOTAL_SUB_DEBT

= IQ_CONVERT

= IQ_ST_DEBT

= IQ_ST_DEBT_PCT

= IQ_LT_DEBT_CAPITAL_LEASES

= IQ_SR_DEBT_EBITDA

= IQ_SR_DEBT_EBITDA_CAPEX

= IQ_TOTAL_SUB_DEBT_EBITDA

TEV/Total Revenues

TEV/EBITDA

TEV/EBIT

TEV/Unlevered FCF

Market Cap/Levered FCF

P/Diluted EPS before extra

P/BV

P/TangBV

P/ Sales

Forward Trading Multiples

TEV/Forward Total Revenue

TEV/Forward EBITDA

TEV/Forward EBIT

P/Forward Diluted EPS before extra

PEG Ratio

P/Forward CFPS

= IQ_TEV_TOTAL_REV

= IQ_TEV_EBITDA

= IQ_TEV_EBIT

= IQ_TEV_UFCF

= IQ_MARKET_CAP_LFCF

= IQ_PE_EXCL

= IQ_PBV

= IQ_PTBV

= IQ_PRICE_SALES

Issue Name

= IQ_ISSUE_NAME

= IQ_ISSUER

= IQ_ISSUER_PARENT_TICKER

= IQ_ISSUER_TICKER

= IQ_AMT_OUT

= IQ_MATURITY_DATE

= IQ_OFFER_COUPON

= IQ_OFFER_DATE

= IQ_OFFER_PRICE

= IQ_SECURITY_TYPE

= IQ_SECURITY_LEVEL

= IQ_YTW

= IQ_YTW_DATE

= IQ_STW

= IQ_OFFER_YIELD

= IQ_PRINCIPAL_AMT

= IQ_BOND_PRICE

= IQ_CALLABLE

Chinese Short Native Name

Business Description

Headquarters

Primary Industry

Number of Shareholders

= IQ_COMPANY_NAME

= IQ_CHINESE_SHORT_NATIVE_TICKER

= IQ_BUSINESS_DESCRIPTION

= IQ_COMPANY_ADDRESS

= IQ_PRIMARY_INDUSTRY

= IQ_NUMBER_SHAREHOLDERS

MACROECONOMIC

Unemployment Rate

GDP

Real GDP

Inflation Rate

Budget Spending

CPI

Consumer Lending

= IQ_UNEMPLOYMENT_RATE

= IQ_GDP

= IQ_GDP_REAL

= IQ_INFLATION_RATE

= IQ_BUDGET_SPENDING

= IQ_CPI

= IQ_CONSUMER_LENDING

= IQ_TEV_TOTAL_REV_FWD

= IQ_TEV_EBITDA_FWD

= IQ_TEV_EBIT_FWD

= IQ_PE_EXCL_FWD

= IQ_PEG_FWD

= IQ_PRICE_CFPS_FWD

FIXED INCOME

Issuer

Issuer Parent Ticker Symbol

Issuer Ticker Symbol

Amount Outstanding

Maturity Date

Offering Coupon

Offering Date

Offering Price

Security Type

Security Level

YTW

YTW Date

STW

Offering Yield

Principal Amount

Current Price

Callable Feature

COMPANY INFORMATION

Company Name

= IQ_EBIT_EST

= IQ_NI_EST

= IQ_CFPS_EST

= IQ_EST_NUM_BUY

= IQ_EST_NUM_HOLD

= IQ_EST_NUM_SELL

= IQ_EST_NEXT_EARNINGS_DATE

= IQ_NEXT_EARNINGS_DATE

= IQ_EST_EPS_DIFF

= IQ_EST_EPS_SURPRISE_PERCENT

= IQ_EST_REV_DIFF

= IQ_EST_REV_SURPRISE_PERCENT

VALUATION

CAPITAL STRUCTURE

Total Commercial Paper

= IQ_AVG_BROKER_REC

= IQ_AVG_BROKER_REC_NO

= IQ_PRICE_TARGET

= IQ_EST_EPS_GROWTH_5YR

= IQ_EPS_EST

= IQ_REVENUE_EST

= IQ_EBITDA_EST

MARKET DATA

Today's Date

Pricing Date

Stock Exchange

Last Sale Price

Close Price

Dividend Adjusted Day Close Price

Volume Weighted Average Price

52 Week High Price

52 Week High Date

Daily Volume

Daily Value Traded

Market Capitalization

Enterprise Value

Shares Outstanding

5 Year Beta

5 Year Price Volatility

Fund NAV

Dividend Yield

Latest Annualized Dividend Per Share

= CIQGETDATE(IQ_TODAY)

= IQ_PRICEDATE

= IQ_EXCHANGE

= IQ_LASTSALEPRICE

= IQ_CLOSEPRICE

= IQ_CLOSEPRICE_ADJ

= IQ_VWAP

= IQ_YEARHIGH

= IQ_YEARHIGH_DATE

= IQ_VOLUME

= IQ_VALUE_TRADED

= IQ_MARKETCAP

= IQ_TEV

= IQ_SHARESOUTSTANDING

= IQ_BETA_5YR

= IQ_PRICE_VOL_HIST_5YR

= IQ_FUND_NAV

= IQ_DIVIDEND_YIELD

= IQ_ANNUALIZED_DIVIDEND

CREDIT RATINGS

S&P Long-Term Company Rating

S&P Long-Term Company Rating Date

S&P Outlook/Credit Watch

S&P Long Term Security Rating

S&P Security Rating Date

S&P Security Rating Action

=

=

=

=

=

=

IQ_SP_LC_LT

IQ_SP_LC_DATE_LT

IQ_SP_OUTLOOK_WATCH

IQ_SP_ISSUE_LT

IQ_SP_ISSUE_DATE

IQ_SP_ISSUE_ACTION

CIQ FUNCTIONS

Pricing

Financials

Multiples

Dividends

Average Value Function (Dates)

Average Value Function (Period)

Average Value Function (Finan. Metric)

High Value Function (Dates)

High Value Function (Period)

Low Value Function (Dates)

Low Value Function (Period)

Percent Change Function

Percent Change Function (Period)

News Headline

Key Development ID

Event ID

Quick Comps

* T = Ticker Input, D1 = Start Date, D2 = End Date

=

=

=

=

=

=

=

=

=

=

=

=

=

CIQRANGE(T,Market Item, D1, D2)

CIQRANGE(T,Financial Metric, Period, D2)

CIQRANGE(T,Multiple Metric, Period, D1, D2)

CIQRANGE(T, Divident Metric, D1, D2)

CIQAVG(T, Market Item, D1, D2)

CIQAVG (T, Market Item, Period)

CIQAVG (T, Financial Metric, Period, D2)

CIQHI(T, Market Item, D1, D2)

CIQHI(T, Market Item,Period)

CIQLO(T, Market Item, D1, D2)

CIQLO(T, Market Item,Period)

CIQPC(T, Market Item, D1, D2)

CIQPC(T, Market Item,Period)

=

=

=

=

CIQRANGE(T,IQ_NEWS,Start Rank,End Rank)

CIQRANGE(T,IQ_KEY_DEV_ID,D1,D2,Category(ies))

CIQRANGE(T,IQ_EVENT_ID,D1,D2,Category(ies))

CIQRANGE(T,IQ_QUICK_COMP,Start Rank,End Rank)

RANGE FUNCTIONS*

COMPANY INFO

Institutional Holder

Institutional Holder Total Shares

Insider Holder

Insider Holder Total Shares

Holder Name

Holder CIQ ID

Holder Total Shares

Mutal Fund Name

Mutal Fund Shares Held

Mututal Fund % of Shares Outstanding

COMPANY INFO- Private Equity

All Direct Investments

All Direct Investments CIQ ID

Fund Families

Recent Funds

Fund Name

Fund Size (USD)

Limited Partner

Industries of Interest

Equity Investment- Min(mm-USD)

FINANCIALS- Segments

Business Segment Description

Business Segment Name

Business Segment Revenue

Business Segment Assets

Geographic Segment Name

Geographic Segment Revenue

Geographic Segment Assets

TRANSACTIONS- List

Index Constituents

All Transactions

M&A Transactions

Equity Indentifiers

Fixed Income Indentifiers

=

=

=

=

=

=

=

=

=

=

IQ_INSTITUTIONAL_OWNER

IQ_INSTITUTIONAL_SHARES

IQ_INSIDER_OWNER

IQ_INSIDER_SHARES

IQ_HOLDER_NAME

IQ_HOLDER_CIQID

IQ_HOLDER_SHARES

IQ_HOLDER_FUND_NAME

IQ_HOLDER_FUND_SHARES

IQ_HOLDER_FUND_PERCENT

=

=

=

=

=

=

=

=

=

IQ_INVESTMETNS_ALL

IQ_INVESTMETNS_ALL_ID

IQ_PE_FUND_FAMILIES

IQ_RECENT_FUNDS

IQ_PE_FUND_NAME

IQ_PE_FUND_SIZE

IQ_LIMITED_PARTNERS

IQ_INVEST_CRITERIA_INDUSTRY

IQ_INVEST_CRITERIA_EQUITY_MIN

=

=

=

=

=

=

=

IQ_BUS_SEG_DESCRIPTION

IQ_BUS_SEG_NAME

IQ_BUS_SEG_REV

IQ_BUS_SEG_ASSETS

IQ_GEO_SEG_NAME

IQ_GEO_SEG_REV

IQ_GEO_SEG_ASSETS

= IQ_CONSTITUENTS

= IQ_TRANSACTION_LIST

= IQ_TRANSACTION_LIST_MA

= IQ_EQUITY_LIST

= IQ_FIXED_INCOME_LIST

Bond Identifiers

= IQ_BOND_LIST

Preferred Security Indentifiers

= IQ_PREFERRED_LIST

= IQ_BANK_LOAN_LIST

Bank Loan Identifiers

Credit Default Swap Identifiers

= IQ_CDS_LIST

*Please refer to the front page for the syntax of CIQRANGE formula

Client Support

North America: 888.806.5541 / +1.212.438.1070

EMEA: North

+44.(0)20.7176.1234

America: 888.806.5541 / +1.212.438.1070

Asia-Pacific:

2533 3588

EMEA:+852

+44.(0)20.7176.1234

Asia-Pacific: +852 2533 3588

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download