Fin 320 Advanced Financial Analysis
Homework Assignments
Fall 2002
Prof. P.V. ViswanathAssignments must be typed and submitted before the start of class on the due date.
Group Assignment 1
Pick a US company that belongs to one of the following sectors. You should stay with this company for the assignments that follow as well. You must confirm your company selection with me before you start your analysis.
Telerate Sector Symbol S&P Sector Symbol Materials !SPBAS.G !SPBAS Industrials !SPIND.G !SPIND Consumer Discretionary !SPCYC.G !SPCYC Consumer Staples !SPSTA.G !SPSTA Transportation !SPTRN.G !SPTRN You can find companies in these sectors by using the Telerate program in the G-PACT room (W404), or by using StockVal. The company that you choose should satisfy the following conditions:
Make sure that the market value of your chosen firm is at least 0.75 billion dollars.
It should have public debt, preferably publicly traded.
You can select your company in Telerate as follows:
Open up the Analytics/Pages display in your workspace. On the command line, type in the Telerate or the S&P sector symbol followed by /ig. This will give you a list of all companies in that sector. Thus, if you wanted to choose a company in the S&P Consumer Discretionary sector, you would type in !spcyc/ig. This would give you a list of companies in that sector, as well as some basic information about those companies -- the last column will give you the market value of the company in thousands of dollars (the t at the end stands for thousands). You can choose from this list.
If you type in one of the Telerate sector symbols followed by [bpc, you will get a list of the narrower S&P industry groups that comprise the sector. Thus, if you type in !spcyc.g[bpc, you will get a list of narrower industry groups, beginning with !sp120 (S&P Automobiles). If you now type in !sp120[bpc, you will get a list of the automobile firms in the S&P, viz. Ford Motor Company (F) and General Motors Company (GM).
What if you have a company that you wish to work with, and you want to know if it qualifies? First you need to know the company's symbol. To find the symbol for the company (which is usually the ticker symbol), type in /lu/company name. Thus, to find out the symbol for Goodrich, Inc., type in /lu/goodrich. You will get a list of entities, the last of which has the Telerate symbol us;gr and is identifed as Goodrich Corporation. This is the one that we want. The others are either subsidiaries of Goodrich or other companies with a similar name. If there is any doubt, type in the suggested symbol, followed by /biz (e.g. us;gr/cf/biz or gr/cf/biz to get a business profile of the entity).
Next, type in sym/inx; this will give you the narrower industry groups and other indices that this company is part of.
If you want to know if this company is part of a particular sector, simply bring up the IndexWatch display on your Workspace, and at the top right, type in the ampersand sign, followed by the symbol for the sector (this will only work with the S&P group). Thus, if you type in &spcyc, you will see a list of 78 companies that make up this sector. If you right-click the mouse anywhere in the display, you will get a menu that will allow you to check "Company Names." This will replace the ticker symbols with the actual company name for easier identification. You can also use the following table to relate sectors to industry groups (Source: http://www.spglobal.com/gics1.html).
Consumer Staples Industrials Consumer Discretionary Agricultural Products Aerospace & Defense Advertising Brewers Air Freight & Couriers Apparel & Accessories Distillers & Vintners Airlines Apparel Retail Drug Retail Airport Services Auto Parts & Equipment Food Distributors Building Products Automobile Manufacturers Food Retail Commercial Printing Broadcasting & Cable TV Household Products Construction & Engineering Casinos & Gaming Meat Poultry & Fish Construction & Farm Machinery Catalog Retail Packaged Foods Data Processing Services Computer & Elecronics Retail Personal Products Diversified Commercial Services Consumer Electronics Soft Drinks Electrical Components & Equipment Department Stores Tobacco Employment Services Distributors Environmental Services Footwear Materials Heavy Electrical Equipment General Merchandise Stores Aluminum Highways & Railtracks Home Furnishings Commodity Chemicals Industrial Conglomerates Home Improvement Retail Construction Materials Industrial Machinery Homebuilding Diversified Chemicals Marine Hotels Diversified Metals & Mining Marine Ports & Services Household Appliances Fertilizers & Agricultural Chemicals Office Services & Supplies Housewares & Specialties Forest Products Railroads Internet Retail Gold Trading Companies & Distributors Leisure Facilities Industrial Gases Trucking Leisure Products Metal & Glass Containers Motorcycle Manufacturers Paper Packaging Transportation Movies & Entertainment Paper Products Air Freight Photographic Products Precious Metals & Minerals Airlines Publishing & Printing Specialty Chemicals Railroads Restaurants Steel Shipping Specialty Stores Truckers Textiles Tires & Rubber
To check if the debt of your company is publicly traded, go to BondLookUp (by double-clicking in the gray background area and selecting it) on Telerate Plus, enter the ticker symbol for you company and choose Gov/Corp as your Type. If there are is any publicly traded debt, it will show up.
Write a report on Corporate Governance at your company. Start your report by providing a brief description of the company (about half a page), taken from Telerate. Use another two to three pages to present a well-organized report on "Who own/runs your firm. Make sure to put the important analyses up front; all detailed information, including tables, particularly if they are long, should go at the end as an appendix. If the table or the graph is small, then you can include it up front, particularly if you're going to refer to the contents of the table in detail. In any case, you should not include any information in Appendices that you don't refer to at all in the text.
And don't forget to use spell check and grammar check!
Some of the questions that you should think about in writing up your report:
Who are the institutions holding the stock? Use the Telerate command: sym/cf/inst. (Similar information can also be found on Yahoo at http://biz.yahoo.com/hd/m/msft.html for Microsoft -- msft; replace msft with the symbol for your stock.) What conclusions would you draw from the quality and number of these institutions, as well as the proportion of the firm's stock that they hold?
How many analysts follow the stock? This information can be found on Telerate (Use the Telerate command sym/cf/nel.) What implications would you draw from the quality and number of analysts?
Who are the mutual funds that hold the stock? (Check http://biz.yahoo.com/hd/mf/m/msft.html; replace msft with the symbol for your stock.) What conclusions would you draw from this information?
Are there major conflicts of interest in the running of the company that have not been addressed by compensation contracts, etc.?
Have there been major events in the life of the company recently that are indications of conflicts of interest?
Get information on the covenants/bond indentures of the company. Can you conclude that the bondholders are well protected from stockholder aggression?
How does the company see its role as a corporate citizen? For example, is it respectful of its social obligations?
You can use the following analysis of Disney, Inc. done by Aswath Damodaran, in 1997. Focus on the first two sections, Corporate Governance Analysis and Stockholder Composition. This is a very good model to use.
You can use the following general sources:
http://www.cii.org/links.htm (Council of Institutional Investors; use the links given there to obtain more information on how good corporate governance is at your firm.)
http://www.corpgov.net/ (Corporate Governance Network).
For company specific information,
You can search the Wall Street Archive.
You can use the Lexis-Nexis database from the Pace Library homepage (go to http://library.pace.edu, click on Databases, then choose Lexis-Nexis Universe from Complete Alphabetical Listings) to search for information regarding your company. You can select Business News, then search using your company name, as well as other useful keywords, such as executive compensation or shareholder rights, or other terms that you can come up with based on your reading of Chapter 2 and my webnotes/slides.
Search Lexis/Nexis, focusing on publications such as the Economist, Forbes, Fortune, etc. using appropriate keywords.
You can also look at the Annual Report of the company, which you can find at the company website (you can find the company website by searching on Google, or by going to http://biz.yahoo.com).
Search on Google using the company name and relevant keywords.
Use the information on analysts at http://www.zacks.com
Use information that you may be able to find at http://biz.yahoo.com or at http://www.marketguide.com.
Group Assignment 2
(Use the same company, as for Assignment 1, if possible. If not, pick another company satisfying the conditions specified in Assignment 1.) Do either part A or part B, whichever will allow you to stay with your company. (If you need to switch companies, you have to get permission from me beforehand.) This assignment should be done in Excel. Make sure that you provide sources for all your data and other information. You can use http://10kwizard.com; note, however, that access to 10kwizard.com is no longer free. You can get the same information from the Library's databases, or from Yahoo or Edgar Online (http://www.edgar-online.com/).
Go through the company's most recent 10K . Search for research and development expenses. If you find any, capitalize them, and make up a revised balance sheet, including the capitalized R&D. Also, show a revised income statement. (Include the original balance sheet and income statement, as well.) Leave out all the details of these financial statements that are irrelevant for the purposes of this exercise; i.e., do not itemize them. (Obviously, the left and right sides of a balance sheet have to match!) Present all relevant information from the 10K. If you need to go to any other sites, do so. Explain all the assumptions that you make. In particular, justify your choice of the amortized life of R&D for your company.
Go through the company's most recent 10K. Search for operating lease expenses. If you find any, capitalize them, and make up a revised balance sheet, including the capitalized operating lease liability. Also, show a revised income statement. (Include the original balance sheet and income statement, as well.) Leave out all the details of these financial statements that are irrelevant for the purposes of this exercise; i.e., do not itemize them. (Obviously, the left and right sides of a balance sheet have to match!) Present all relevant information from the 10K. If you need to go to any other sites, do so. Make as few unsupported assumptions as possible.
Group Assignment 3
Go to the G-PACT Room (W404). Download historical price information on the stock that you worked on for Assignment 1, using the information given below, and compute market betas as described below:
Go to the G-PACT site. Click on Using Telerate: Manuals and Other Help in the menu at the bottom. Then click on University of Illinois - Champaign/Urbana's Introduction to Bridge. This will bring up a website, managed by the University of Illinois, in a new window. Click on Excel Bridge Link in the menu at the bottom. Follow the instructions under Getting Started, from point number 2. (If you have difficulty in running Excel from Telerate, go to the Desktop and click on Excel directly. If you have any questions, check with the G-PACT aide on duty.)
Download daily closing price data on your stock for the periods required. (Specify custom on the Create-Link History applet, and also specify a sufficiently large number of periods.). Do the same for the NYSE Composite (NYA). Compute the return on the stock and on the index. If your stock pays dividends, get this information by using the command sym/CF/DIV/; to go beyond the time period shown on the first screen, use sym/CF/DIV/PG2, etc. Then compute the daily return by using the formula, R8/11/99 = P8/11/99/P8/10/99-1, where Rt, Pt stand for the return and price on date t. If a stock went ex-dividend on August 11, 1999, use the formula, R8/11/99 = (P8/11/99+Div8/11/99)/P8/10/99-1, where Divt is the amount of the corresponding dividend.
Similarly, download monthly closing price data on your stock and on the index for the required periods. Compute monthly returns, assuming that the dividends are paid at the end of the month in which they are actually paid.
Now run two sets of regressions:
Regress the daily stock returns on the stock against the index for the period -- beginning 1999 to end 2001);
Regress the monthly stock returns for the months 1/96 to 12/98 on the index returns for the corresponding period;
Regress the monthly stock returns for the months 1/99 to 12/01 on the index returns for the corresponding period.
Use Excel to run the regressions; interpret your results. (You can get more information on regression analysis from my website. You may also want to consult the solution to Q. 2 on Midterm II in Spring 2000.)
Prepare a table as follows:
NYA (Your regression betas) NYA (Telerate betas) Monthly data Beg. 1996 - end 1998 Monthly data Beg. 1999 - end 2001 Daily data Beg. 1999 - end 2001 Now plot beta pictures of your stock for the time-periods and index specified above, using the Analytics Display in the Telerate software (six combinations in all), using the information below to structure the different commands:
The command us;dell/sc/bf(100*(ls-ls.1)/ls.1)/se/grd3/da/dt>jan-99/dt<oct-01/fc/tc12/sym=us;nya will generate a beta plot using daily data for dell from Jan. 1999 to Oct. 2001 against the New York Stock Exchange Composite. You will, in general, only need to change the items shown in red italics to obtain the different combinations. Instead of us;dell, input the symbol for your stock; instead of da, put in mo for monthly data; modify dt>jan-99 and dt<oct-01 to change the time period for the data; modify us;nya to change from the New York Stock Exchange Composite index to other indices.
Show the Telerate beta pictures by copying them from the Analytics display and pasting them in your Excel worksheet. This can be done by clicking the right mouse anywhere in the Analytics display; this will prompt you to "Save Window as" a bitmap file. This file can then be inserted in the appropriate spot in the Excel spreadsheet using the Insert Object command from the pulldown menu and giving the name of the file where the beta picture was saved.
Next,
Compare the betas computed with respect to the index, using daily data versus monthly data, for the period 1999-2001.
Compare the betas computed with respect to the index using monthly data for the period 1995-1997 versus the period 1999-2001.
If there are any differences in the betas, explain them. (Keep in mind also, the R2s of the regression, the standard errors of the estimates, and the characteristics of the firm and the indices over time.)
Submit your results in an Excel file. Your submission should have the following structure:
Worksheet 1: Name of Group with names of members, and details of individual participation in different parts of the project.
Worksheet 2: Introduction, Table of betas as described above. Analysis discussing differences in the beta estimates. In terms of explaining beta changes over time, your analysis should focus on how the beta determinants changed from one period to another. For example, has the financial leverage changed significantly, have the assets changed, has operating leverage changed, etc. In terms of explaining the beta difference between the two indices, look carefully at the section entitled "Betas" in the text (2nd edition), at page 196-210.
Worksheet 3: Your beta regressions
Worksheet 4: The Telerate beta pictures carefully labelled.
Worksheet 5: Your raw data plus your dividend computations. Your return calculations should be shown using Excel formula, so I can see exactly how you have computed them.
The actual work should be shown in other worksheets in the same file.
Assignment 4: Capital Structure
Compute the weighted average cost of capital for the company that you chose in Assignment 1.
You can use the following exam problems for hints on what sort of information you will need, as well as on how to process the information.
Among other sources, you can also use:
- Telerate: You can look up the long-term liquidity ratios of the firm, using the command: sym/CF/LIQ. You can look at EBIT/Interest Expenses, as well as %Long Term Debt/Total Assets. You can also look up news items. Bond Browser, Bond Search and Bond Analytics will provide you with a lot of bond specific data that you might have difficulty obtaining from other sources.
- Global Access Disclosure Suite (from http://library.pace.edu)
- http://biz.yahoo.com
- http://www.marketguide.com
Make sure your report is well researched, well analyzed, based on theory, and written up in a form easy to understand.
Go to the FIN 320 Home Page
Go to Prof. P.V. Viswanath's Home Page