Consolidate all excel spreadsheets from part1 in a single Excel le. Please make a cover page that includes names and IDs for a team) in Sheet1 in your EXCEL le. To submit this project electronically, use Black- board. DO NOT send me it via email. Please submit your project in a single EXCEL le. ONLY an EXCEL FILE will be accepted. You have write up with your own words. Do not copy from others' work. In the last page, you have to show all your references for your project in the last sheet in your Excel. Note that this main goal of this project is to replicate \Key Stats" and \Pro tability Ratios" in Bloomberg. If you just copy and paste the values from Bloomberg, you will get zero. You answers in Excel must be computed based on the data. Part 1 Collecting Data and Computing nancial indicators Instruction: You have been retained by an investment analysis unit of the SHB Brooklyn investment, LLC to provide an investment analysis report to the CEO of the company, Seungho Baek. Your unit head asks you to evaluate ve portfolio constituents chosen by junior fund managers as below. I. Allergan, plc (Ticker: AGN) II. Tesla, Inc. (Ticker: TSLA) III. Plantronics, Inc. (Ticker: PLT) IV. Ritchie Bros Auctioneers (Ticker: RBA) V. Devon Energy Corporation (Ticker: DVN) Thus, you are about to generate key variables, which are widely used in nancial markets, so as to evaluate these companies using Bloomberg. The key values include 1) Market Capitalization, 2) Enterprise Value, 3) Revenue, 4) Gross Pro t, 5) Net Income, 6) EPS, 8) Cash from Operations, 7) Capital Expenditure, and 8) Free Cash Flow. Problem 1. To evaluate this pool of stocks, you decide to collect historical 1) stock prices, 2) shares outstandings, 3) balance sheets, 4) income statements, and 5) cash ow statements for these 10 companies from Bloomberg terminal. The data period for this analysis is from the year of 2013 to Dec. 2017. Download all the data using Bloomberg and consolidate all the data in your Excel file. Hints: In order to locate historical stock data (price and shares outstanding) for a speci c company using Bloomberg, follow the below procedures.1 Enter Ticker Press EQUITY (F8) yellow key Type HP Press GO green key 1Bloomberg provides \Key stats" on FA. 1 Set monthly data frequency and data range from 12/31/2013 to 12/31/2017 By changing MARKET menu collect stock prices (Last Price), and shares outstanding (Current Market Cap) Click EXPORT TO EXCEL Also to nd historical nancial statements for a speci c company using Bloomberg, follow the below procedures. Enter Ticker Press EQUITY (F8) yellow key Type FA Press GO green key Click B/S (I/S) and select \Standardized" (Note: for I/S, you need download two state- ments: \Adjusted" and \Reconciliation") Click EXPORT TO EXCEL Problem 2. Compute annual market capitalization(MC) for the period between the year 2014 to the year of 2017. MC (t) = Price (t) Shares Outstanding (t) where t represent year.

Problem 3. Compute yearly enterprise value (EV) from 2014 to 2016. EV (t) = MC (t) Cash Equivalents (t) + Preferred and Other (t) + Total Debt (t) where: Cash and Equivalents = Cash & Cash Equivalents + Short Term Investments Preferred and Other = Preferred Equity and Hybrid Capital + Minority/Non Controlling Interest Total Debt = Short Term Debt + Long Term Debt Problem 4. Compute yearly percentage of revenue growth change from 2014 to 2017. Growth % (t) =Revenue (t) - Revenue (t-1) divided by Revenue (t-1) multiple by 100. where t represent year. For example, set t to 2014. Revenue (t) indicates revenue of year 2014 and Revenue (t-1) indicates revenue of year 2013. Problem 5. Compute gross pro t and its margin percent from 2014 to 2017. Gross Pro t (t) = Revenue (t) Cost of Revenue (t) Gross Pro t Margin % (t) = Pro t (t) - Pro t (t-1) divided byPro t (t-1) multiple by 100. Problem 6. Calculate EBITDA and its margin percent from 2014 to 2017. EBITDA (t) = Operating Income (Loss) + Depreciation and Armotization EBITDA Margin % (t) =EBITDA (t) - EBITDA (t-1) divided by EBITDA (t-1) multiple by100. 2 Problem 7. Compute Net Income (NI) and its margin percent from 2014 to 2017. NI (t) = Adjusted Net Income Available to Common (t) NI Margin % (t) =NI (t) - NI (t-1) divided by NI (t-1) multiple by 100 Problem 8. Compute Eearning Per Share (EPS) and its growth percent from 2014 to 2017. EPS (t) =Adjusted Net Income Available to Common Shareholders (t) divided by Basic Weighted Average Number of Share (t) EPS Growth % (t) =EPS (t) - EPS (t-1) divided by EPS (t-1) multiple by 100 Problem 9. Compute Free Cash Flow (FCF) from 2014 to 2016. FCF (t) = Cash Flow from Operating Activities (t) divided by Capital Expenditure (i.e. Purchase of Fixed Production Assets) (t) Problem 10. Compute Return on Equity (ROE), Return on Assets (ROA), Return on Capital (ROC) and Return on Invested Capital (ROIC) from 2014 to 2017. ROE (t) = Net Income Available to Common Shareholders based on GAAP (t) divided by (Total Common Equity(t)+Total Common Equity(t-1))/2 multiple by 100 where Total Common Equity = Share Capital and APIC (i.e. APIC: additional paid in capital or capital surplus of common equity) + Retained Earning + Other Equity - Treasury Stock Also, we de ne Average Total Common Equity as (Total Common Equity(t)+Total Common Equity(t-1))/2. ROA (t) = Net Income based on GAAP (t) divided by (Total Assets (t)+Total Assets (t-1))/2 multiple by100 Problem 11. Interpret all the values for ve companies and explain their performance.

Part 2 Analysis of Key Stats and Evaluating Stocks Problem 1. Investigate all the pro les, Ratios, and Revenue &EPS for ve stocks. You need to summarize their business information using Bloomberg. Enter Ticker Press EQUITY (F8) yellow key Type DES Click 1) Pro le, 2) Issue Info, 3) Ratios, 4) Revenue &EPS Problem 2. Read recent news for each company and summarize them indicating positive (or negative) news to their business. Enter Ticker Press EQUITY (F8) yellow key Type CN Read recent news Problem 3. Based on your ndings from problem 1 and problem 2, suggest your opinion about their stock performance. Problem 4. Next earning announcements for their companies are scheduled as: AGN: 11/01/2018 TSLA: 10/31/2018 PLC: 10/30/2018 RBA: 11/09/2018 DVN: 10/30/2018 Based on your analysis from problem 11 in part1 and problem 1 and problem 2 in part 2, 1) Suggest your opinion about Bloomberg Q3/2018 estimates. (Hint: with reasonable evi- dence, try to answer a question of whether a company's actual earning exceeds estimates or not. ) 2) Provide your 2018 investment guideline about each stock based on your analysis. ( Fore example, simply you might give an opinion of \Buy- AGN\ or \Sell - AGN".)