Excel Project 1 / Spring 2019

I need help with the formulas and basic setting up of this entire project. Please help.

This is your second interview with a prestigious brokerage firm for a job as an equity analyst. You survived the morning interviews with the department manager and the vice president of equity. Everything has gone so well that they want to test your ability as an analyst. You are seated in a room with a computer and a list with the names of two companiesThe GAP Inc ( GPS ) and Chicos (CHS).

Download the annual income statements, balance sheets, and cash flow statements for the last two fiscal years from SEC EDGAR Company Search (https://www.sec.gov/edgar/searchedgar/companysearch.html)You can either search with the company name oreach of the company's stock symbols. Look for the most recent 10K report and download the excel documents..

1.Find historical stock prices for each firm from Yahoo! Finance (finance.yahoo.com). Enter the stock symbol, click "Historical Prices" in the left column, and enter the proper date range to cover the last day of the month corresponding to the date of each financial statement. Use the closing stock prices (not the adjusted close). To calculate the firm's market capitalization at each date, multiply the number of shares outstanding by the firm's historic stock price. You can find the number of shares by using "Basic" under "Weighted average shares outstanding" at the bottom of the Income Statement.

2.For each of the last two years of statements, compute the following: ratios for each firm:

Common Size Balance Sheets and Income Statements (4 points).

Make sure to watch the videos provided and review examples provided in Course Materials and resources. What do you notice? What company has a better handle on expenses? What company has a higher/lower % of debt-equity to total assets, etc. What do you think that this analysis is used for? Conclude.....

3.Calculate the following set of Ratios as described in the Project Worksheet.

oLiquidity Ratios(3 Points)

oFinancial (3 Points)

oAsset Management Ratios (3 Points)

oProfitability Ratios (3 Points)

oMarket Value or Valuation Ratios (3 Points)

oOperating Ratios (3 Points)

oConstruct the Dupont Identity (2 Points)

4.Obtain the industry average for each firm from Reuters (https://www.reuters.com/finance/stocks)

Enter the stock symbol at the top of the page in the "Symbol lookup" and then click the Company name and then on the "Financials" button, and then click

a.Scroll down to "Valuation Ratios," and compare each firm's ratios to the available industry ratios for the most recent year. (Ignore the "Company" column as your calculations will be different.)

b.Analyze the performance of each firm versus the industry and comment on any trends in each individual firm's performance. Identify any strengths or weaknesses you find in each firm. (5 points)

5.Examine the market-to-book ratios you calculated for each firm. Which, if either, of the two firms can be considered "growth firm" and which, if either, can be considered "value firm"? (5 points)

6.Compare the valuation ratios across the two firms. How do you interpret the difference between them? (5 points)

7.Consider the enterprise value of both firms for each of the four years. How have the values of both firms changed over the time period? (5 points)

8.Conclude.... Which of the companies is better managed? Which one is a better investment? Why?..... (10 points)