Passport GMID for Market Entry Strategies

Passport GMID is one of our premier market research databases. Learn how to utilize this tool to its fullest when working to craft a market entry strategy for consumer products. This video will illustrate some of the many ways you can use Passport GMID to inform your decisions. And, feel free to contact the Lippincott librarians for additional assistance with using this great resource.

Ins and Outs of EINs

There is no shortage of symbols or numbers that can be used to identify companies, which makes trying to match company lists from different sources challenging. The more commonly used company identifiers include ticker symbols, CUSIPs, CIKs and ISINs, as well as proprietary numbers like D-U-N-S Numbers or BvD identification numbers. One of the less commonly used company identifiers is the Employer Identification Number (EIN), also known as the Federal Tax Identification Number. It is used by the Internal Revenue Service (IRS) to identify entities that need to file business tax returns. It is a nine digit number, either expressed as a single string (050155090) or with the first two digits, a dash/hyphen and the final seven digits (e.g. 05-0155090). EINs are not as readily available in directory and company databases as other identifiers. Where can you find EINs for company matching when you need them?

Individual EINs for a specific company are available from a company’s SEC filings; see Lippincott’s Business FAQ Where do I find SEC (Securities & Exchange Commission) filings? for details about locating these filings. On the front page of a filing, the EIN is listed as the IRS Employer Identification No.

aapl8-kb

It is also listed in the filing submission text files with the label IRS Number. You can find EINs for private companies if they have filed with the SEC. See for example the IRS Number from the complete submission text file from a Form D filed by Uber in 2015.

uberformdb

Corporate Affiliations, a directory database from LexisNexis providing corporate structure information, includes EINs in its records for public companies and some private companies. One caveat about Corporate Affiliations and the other resources mentioned below is that they often only list EINs for public companies, even though private companies are also required to obtain and use EINs. To find EINs in Corporate Affiliations, search for a company of interest and view its record. The EIN will appear toward the top of the record as FEIN.

hasbrocorpaffiliationsb

Note that Corporate Affiliations is misleading with regard to the exportability of EINs. While FEIN appears to be available as an exportable field, it is not populated in the export results; a FEIN column will display, but it will be blank. Another LexisNexis option is to use the FEIN search in Lexis.com. Lexis.com is available to current Wharton students, faculty and staff at Lippincott; please contact us at lippinco@wharton.upenn.edu to set up an appointment.

Mergent Online includes the EIN in the header information at the top of  the company record, labeled IRS Number.

mergentalleghenytechnologies21b

mergentcompanyanalysislisb

These can be exported for 500 records at a time via the Company Analyst List option that appears on the results page after running a search.

After adding companies to the analysis list, click the My Mergent Tools button and select Company Analysis List. From the My Mergent Tools page, select Company Comparison Report. EIN appears as IRS Number in the Select Data Items area.

mergent-selectdataitems

EINs for nonprofit organizations can be found in their IRS 990 filings, officially titled Return of Organization Exempt from Income Tax.

990filingexampleb

 

 

 

 

 

 

 

See the 990s, Financials & Regulations tab of our Nonprofit Sector research guide for information about tools for locating 990s.  They appear At a Glance section of each foundation record in the Foundation Directory Online.

PewFoundationCenter.jpg

The National Directory of Nonprofit Organizations includes the EIN in organization records, and also provides it as a field in exported lists.

Election 2016: By the Ad$pend

kantar_logoadspender_logo

There are several Lippincott Library resources that contain advertising expenditure data, but only one that tracks it at the advertiser/brand/product level, by media, by market. That is AdSpender, from Kantar Media. AdSpender allows you to find out how much advertising is being done (in dollar amounts, or by units aka number of ads) over time. Our subscription includes a rolling 5-year period. One great way to use this during this election cycle is to track presidential campaign ad spending, down to the city level. This way we can see in which states the candidates’ campaigns feel the race is close and want to spend more, and in which states they feel confident in their winning Electoral College votes – a great indicator of where political advertising dollars will be spent.

createOnce you enter into AdSpender, use the Custom Reports menu on the left to click Create. First, you’ll be able to select from a large menu of Available Media – including Network TV, Cable TV, National Newspapers, Network Radio, Internet Display, and even Outdoor (such as billboards). Select all or as many media formats as you’d like to track and move them to the Selected Media menu. Or, click on the Media tab to select certain Designated Market Areas (DMAs) of interest. Then click the Next link in the top right (how you’ll navigate through all steps of this process).

Now we can choose the time period that we’d like to have measured. To check the date range coverage of the media format that you are interested in, click on Availability in the top right corner. Remember that our coverage is on a rolling 5-year basis. You also havetimeperiods the choice of viewing the data as one large aggregated sum (Time Period-Single) or at a monthly/quarterly level (click on the Select Multiple Time Periods button).

The next step is to choose what AdSpender calls our Product Set. This is the company or advertiser or brand or product that you want to learn more about. We can search Advertiser names like Clinton For President or Trump For President to find their presidential campaign advertising. Double-click the correct result productset– it will
italicize when it
has been properly added to your report.

 

Step 4 is where it gets fun, because we can customize our report format to the detailed level we’d like to see. So, for reportformatexample, I can break out my columns first by Media, then by Product, then by Market, then by Time Period. So I’ll be able to see, for each campaign, the spending on each media format then sorted into the DMAs that saw this coverage, then see the trend over time. Set any addition Report Format (e.g. to show both Dollars and Units) and Report Options you’d like (e.g. font and shading preferences), then set a title and hit Run Report.title

 

 

 

The Report Viewer menu shows you all of the recently created reports within Penn’s subscription. The report will move through stages here – Submitted, Running, Completed. This can sometimes take a few minutes. One refreshtrick if you’ve noticed that your report is taking longer than you’d like is to hit the Refresh Listing link, which will manually refresh the menu rather than waiting for it to auto-refresh. Then you’ll be able to view/download the report, in both PDF and .csv formats.

outputpdfoutputcsv

And for more about political advertising, including how to find an archive of the ads and their creative content, use our Business FAQ: “How can I track political ads and spending?

Welcome to Lippincott Library Fall 2016

Welcome to the Fall 2016 semester and the Lippincott Library!!    The Lippincott Library supports the academic and research needs of the Wharton School.  Visit our website, contact us, chat with us, email lippinco@wharton.upenn.edu or stop by and see us.

Here’s a few tips to help get you started.

xmarksthespot

X marks the spot!!  Lippincott is located at 3420 Walnut Street.  Just look for the statue of “Broken Button“, located on Locust Walk at the entrance to the Van Pelt Library Building.  Lippincott is located on the 2nd floor, west wing of the building.

Use our Lippincott Services Guide to learn about all of our services.

yablon3

The Yablon Financial Resources Lab, located at the library, includes Bloomberg and Capital IQ terminals.  This space is available to Wharton students only whenever the building is open.

 

 

 

datapoints-forlipphome

Read  Datapoints, Lippincott’s blog,  to find out about business databases including content coverage and search tips. Posts also cover Workshops, such as Bloomberg 101 and other Library happenings.

 

Search the Business FAQ to locate the best resources to answer your research question.

 

tweet

 

 

Follow us on Twitter@LippincottLib

 

 

 

Book our group study rooms to practice presentations, hold meetings, and create content as a group.  These include 4 small study spaces, 2 group meeting rooms and a larger Seminar Room.

Join friends for a snack in Mark’s Café

1060_Marks-Cafe-5

Weigle2

 

Make use of all of the equipment and programs available at the Weigle Information Commons .

 

 

Sign up for workshops including Bloomberg 101, Entrepreneurship,  and Job Interview Prep.

I Graduated!! Can I use the Library Databases?

Congratulations to the Class of 2016!!!

“Why can’t I get access to the Library’s databases?” is the perennial question of the recent Penn graduate.

graduation_celebration-hands-throwing-caps-in-ai2r

Unfortunately, the library’s subscription databases are only available to current students, faculty, and staff members of the University of Pennsylvania. Once the school switches a student’s status from student to alumni – usually mid-August – access to the Library’s electronic resources ends. Below is a summary of the Penn Libraries resources that you can access as an alumni, both remotely and on-campus. We also have a few tips for finding business resources at your local public library or on the Web.

Alumni Services provides information on visiting the Library, Library Events and E-resources that are accessible remotely using your PennKey information.

Alumni also have access to some business databases on campus as shown in this list: Alumni Business Database Access on Campus.

Other options for Alumni include the local public library.  Many public libraries provide access to selected databases for members.  Check your library to find out what is available to you.   New York City’s Science Industry and Business Library, (SIBL) rivals many college libraries. The Free Library of Philadelphia also has a large collection of business resources.

There are also many useful freely available web resources.  Here are a few tips on locating and finding reliable information.  First, check our Library Research Guides.  Many guides include web sites carefully vetted by subject Librarians.

libguides_cropped_marked2

Many Universities, such as Penn, provide Scholarly Commons where research by faculty and students are readily available.

Use Google filters.  For instance, when you are researching topics such as foreign trade or exporting, limit your search to government sites.  To search for trade regulations in Brazil, use this search:

TRADE REGULATIONS BRAZIL SITE:.GOV

You will retrieve resources about trade with Brazil from U.S. government sites only.

To search for information from associations or organizations use the filter SITE:.ORG.  Many organizations such as the World Bank provide free information. One great resource from the World Bank is the annual “Doing Business In survey of the ease of doing business in countries across the Globe.  The World Bank also provides detailed information on each country including business regulations and historical macroeconomic data.

Google Scholar contains many scholarly publications from around the world.  Many are accessible via the web.  This can also be used as a way to build a bibliography that can be used at the local library.

google scholar

 

 

 

 

 

The Library of Congress lists many resources, print and online in the Business Reference Services website.  Check out the Bibliographies & Guides as well as recommended Internet Resources.

Large consulting companies often provide free reports. PricewaterhouseCoopers provides industry overviews and research and insights.  Real estate firms such as CBRE provide quarterly international market research reports.

An easy way to access government data is through American Factfinder.  Select your topic of interest such as demographics, economics or housing.

CensusMap

FRED, the Federal Reserve Bank, St. Louis, is another depository of economic statistics and research.  This covers a range of countries as well as the United States.

The Thomas Register is the largest directory of suppliers in the the U.S.  Many companies include brochures.taking-your-biz-to-the-top2

 

The Pew Research Center is a nonpartisan fact tank providing information about the issues, attitudes and trends shaping America.

Financial Data is also accessible on the web.  Yahoo Finance and Bloomberg provide data on stocks, bonds and other financial instruments as well as providing current news.

Yahoo-Finance-Logo

 

 

 

 

Edgar provides free access to company filings with the Securities and Exchange Commission (SEC).  It also includes information about the usage for various filings.

This is a sampling of sites that are available on the web.  Good luck going forward!!

For more detailed information about the Library’s business databases available to alums, please look at our blog post Library Database Access for Penn Alumni.

 

 

 

What’s Your Company’s RQ™ (Research Quotient)?

Research and Development (R&D) expenditure is the amount of money a company spends on developing new products and services each year.  Academic business researchers have intensively investigated the relationship between a company’s R&D and its market value, and have searched for ways to derive a firm’s optimal R&D spending.  A recent innovation in the analysis and measurement of a firm’s R&D has been the development of the concept of Research Quotient (RQ).

A company’s Research Quotient is the percentage increase in the company’s revenue from a 1% increase in its R&D. RQ is a measure of a firm’s ability to generate revenue from its R&D expenditures.  RQ is calculated from a formula that combines a company’s measure of capital, labor and R&D. For more details concerning RQ calculation, click on Manuals and Overviews from the WRDS Research Quotient database.

RQ can be used:

  • To Link R&D spending to firm growth
  • Link R&D spending to market value
  • Derive a firm’s optimal R&D spending

The WRDS RQ database includes RQ measures for all companies in the COMPUSTAT database that report R&D expenditures. The data covers 1972 to 2010 and is updated annually.  The file allows searching by 4 digit SIC and by GV Key (COMPUSTAT’s unique company identifier).

Table 1 is an example of the output showing some of the default variables.

TableOne

  • “Raw RQ” is the “Research Quotient” that identifies the ability of a firm to generate revenue from its R&D expenditures. The higher the RQ the greater the revenue generated.
  • “RSTAR” is a calculation of optimal R&D expenditure.
  • “RD Ratio” is the ratio of R&D expenditure to Revenue.

In Table 2, for clarification, I have supplied tickers and names of companies together with a measure of “RQ” that I calculated from the “Raw RQ” supplied by WRDS. This RQ is analogous to the human IQ measure with a mean of 100 and a standard deviation of 15. An RQ with a mean of 100 is often used by academic writers as a way of making the RQ measure more intuitive.

Table 2 ranks the first 20 companies in the U.S. by their RQ in 2010.

Table2improved

There are more than 260 four digit SIC codes represented in the 2010 files, but only 10 codes have more than 35 companies. Table 3 collapses the codes into 2 digits, and ranks the average RQ of the largest 15 industry groups.

Table3Formatted

About 78% of the companies in the 2010 file were based in the U.S.  Figure 1 graphs the countries with 3 or more companies in 2010 by average R&D expenditures and average RQ.

Figure1Formatted

The principal developer of the RQ concept is Anne Marie Knott, Professor of Business at Washington University, St. Louis. In a 2012 article in Harvard Business Review, she estimateds that if the 20 largest US firms had optimized their R&D expenditure in 2010, they would increase their aggregate market capitalization by $1 trillion. (Knott, Anne Marie. “The Trillion-Dollar R&D Fix.” Harvard Business Review (90:5) 2012, pp. 76-82.) This article can be accessed using Business Source Complete.

What did they say? Resources for transcripts and audios of Earnings Calls.

Earnings calls are conference calls or webcasts at which a public company’s management team discusses the company’s financial performance from the previous reporting period (usually a quarter). Participants include investors, financial analysts, and others, including shareholders. Reading transcripts from these earnings calls and listening to audio recordings can be a great way to glean information about a company’s performance and plans for its future directly from the company leaders themselves.

Presentation

While archival transcripts of calls can be easy to find, audio broadcasts of earnings calls can sometimes be more challenging to find. Often, companies will only make them available for a limited amount of time following the actual earnings call. If you wish to listen to the broadcast, you will need to keep track of when a particular company’s earnings calls are and how long they make their broadcasts available after the call. For example, Apple only makes their broadcasts available for two weeks after the call. While archives of audio recordings will not be as extensive as those for the written transcripts, you can often find at least the recordings for the last call or two for a given company.

The Lippincott Library has a number of resources where you can access these materials.

Continue reading

Are You Certified??? Certification Programs for Bloomberg or Thomson One!!!

Many students and potential employees like certifications for Bloomberg or Thomson One. This demonstrates that the student has an understanding of how to use these expensive programs before coming on board.  The learning curve for using these products on the job is greatly reduced.

BloombergMonitor

Bloomberg, available at Lippincott Library or Huntman Hall, currently offers two certificates.

Bloomberg Market Concepts (BMC) is a self- paced, 8 hour introduction to finance using 70 Bloomberg terminal functions.  This provides a good foundation for undergraduates or people new to finance.  It covers four topics:  Economic Indicators, Currencies, Fixed Income and Equities. The format is interactive video sessions. There is no cost for this certificate if you use the Library Bloomberg terminals.  You can sign up for a web-based session at a discounted price of $149.00.

BESS is also self-paced and is recommended for individuals with more experience in Finance and Bloomberg.  Again, there is no cost when using the Library Bloomberg terminals.  Sessions are based on written materials.

Once in Bloomberg, just type BMC<GO> or BESS<GO> to get started.

Both classes require that you take quizzes in order to get the certificate.

Thomson2

Thomson One Investment Bank also offers a certificate program.  Click here for general information.  Once you have viewed the materials for the modules, request a token to take the exam.  The exam comprises 30 questions.  You have 60 minutes to complete the test.  If you don’t pass the first time, you are allowed to take the test a second time.  It is encouraged that you use Thomson One while taking the test.  This is free to students at Universities which subscribe to Thomson.

Remember that Thomson One only works with IE.  Please refer to these postings for further information.

Thomson One and Compatibility:  Internet Explorer Issues 

Thomson One Compatibility and Chrome or Firefox.

Using Capital IQ to screen for company executives and board members

capiqlogo

S&P Capital IQ is a premier financial database that is available for the use of Wharton students in the Yablon Financial Resources Lab here in the Lippincott Library, or through MBA Career Management for MBA students during the academic year. One great feature of Capital IQ is the Screenings tool, with which you can generate a list of companies, equities, transactions, or people based on a specific set of criteria that you identify.

Once you create your screening, you are able to export it to Excel. This is particularly useful for the People Screening function if you are looking to download a structured data set on a group of executives that meet specific criteria. There are not many resources that allow you to do this for individual people as opposed to companies or equities as a whole.

However, Capital IQ’s screening tools are not the most intuitive, so finding the specific criteria you want to include in your screening can sometimes be a challenge. Here, we will show you how to pull structured data on executive compensation history for a set of companies. These techniques can be applied to a wide variety of screenings in Capital IQ.

  • To get started, hover over “Screening” in the ribbon at the top of the page and select “People.”

Cap IQ screenshot 1a

  • For the purposes of this blog post, let’s say that you want to screen for executives from companies in the S&P 500 index. In the first column of links on the Person Screening screen, click on “Indices” in the “Equity Details” box.

Cap IQ screenshot 2b

  • Select “S&P 500 Index” from the list of options.

Cap IQ screenshot 3crop

  • If you would like to screen for current professionals at these companies, ensure the drop-down is set to “Current Professional.” If you would like to screen for prior professionals, ensure that the drop-down menu is changed to “Prior Professional.” Click “Add Criteria.”

Cap IQ screenshot 3b

  • In the 3rd column of criteria on the page, there is an “Employment/Board” window. Click on “Professional Job Functions” in that window to specify which job functions you would like to evaluate in this screening.

Cap IQ screenshot 4

  • Choose the desired job functions from the “Available Items” window. To screen for current professionals, make sure that the “Limit to current job functions only” box is checked, and the drop-down is set to “Current Professionals.” If you are screening for prior (previously employed) professionals, make sure the drop-down menu is changed to “Prior Professionals.” Click “Add Criteria.”

Cap IQ screenshot 5b

  • If you would like to screen for board members as opposed to employees, click on “Board Job Functions” in the “Employment/Board” window instead of “Professional Job Functions.” Choose the desired board functions from the “Available Items” window. To screen for current board members, make sure that the “Limit to current job functions only” box is checked, and the drop-down is set to “Current Professionals.” If you are screening for prior board members, make sure the drop-down menu is changed to “Prior Professionals.” Click “Add Criteria.”
  • IMPORTANT: If you want to look at compensation information for current AND prior employees or board members, you will need to run separate screenings for each set of criteria and then combine them in Excel later. For example, if you were interested in looking at data for current and prior executives and board members, you would need to run FOUR separate screenings: current executives, prior executives, current board members, and prior board members.
  • Once you have selected all of your desired criteria, click on the “Customize Display Columns” tab. This is where you are able to select the information you wish to view about each executive who fits your screening criteria.

Cap IQ Screenshot 7bmarked

  • To display compensation for each executive, click on “Compensation” under “Person Details” in the fourth column. To display available salary information, make sure “Salary” is highlighted. Under time frame, select either “Latest Annual” or the fiscal year of your choice. Click “Add Columns.”
  • To display additional compensation information, such as bonuses, repeat the same steps for each individual criteria.
  • To view options information, click on “Options” under “Person Details” in the fourth column and add  your desired criteria.
  • If you are interested in viewing available education information for the executives in your screening, click on “Education” under “Person Details” in the fourth column. Select your desired criteria, and click “Add Columns”. Repeat for each remaining desired criteria. Note that not all education information is available, and depends on whether it was made public by the executive or the company.

graduationyear

  • Once you have added all of your desired criteria and column information, click the “View Results” button. This will generate a list of executives meeting your screening criteria. To export the list to Microsoft Excel, make sure that Excel is selected near the top of the screening results and click “Go.” This will generate an Excel spreadsheet for your results.

rptgeneration

  • You can adjust your screening as necessary by clicking “Edit” next to the criteria you would like to adjust. Once you have made the necessary adjustments, click “View Results” again to update the screening. You will need to export the results to Excel again if you haven’t already done so.
  • Your screening is complete! You will be able to use this information to analyze executive compensation for your project.

There are a multitude of additional screenings you can run in Capital IQ, whether you are screening for companies, people, key developments, or transactions. We will focus on other types of screenings you can do in Capital IQ in a future blog post.