Please look at the attached files for questions. It should be done in excel sheet using formulas. Time period is 5 days. thank you

STEP #1: Template for Income Statement, Balance Sheet & Ratios. [PDF DOCS ONLY]. Attached Files: NEW 7200 Sep.Oct 2016 BENCHMARK.pdf (282.587 KB) YOU ARE EXPECTED TO BUILD THIS SPREADSHEET FROM SCRATCH. IF IT COMES TO MY ATTENTION THAT YOU HAVE USED A SOFTWARE PROGRAM (OR ANY OTHER MEANS) TO CONVERT MY PDF DOCUMENT INTO AN EXCEL DOCUMENT, YOU WILL RECEIVE A ZERO FOR THE ENTIRE ASSIGNMENT. IT IS JUST NOT WORTH IT. The first significant milestone for this project is to build an excel worksheet EXACTLY like this. Step guidance as follows: 1. Choose your S$P 500 company. 2. Then select 2 key competitors of that company. 3. My recommendation is to use Yahoo Finance. Has tons of information on each company. Just click on COMPETITORS link to find out key competitors. 4. Download Income Statement Data and Balance Sheet Data into a new Excel w/s. 5. Start building the framework. 6. Complete Income Statement section first. 7. Complete Balance Sheet section. 8. Use Quad Graphics Data FIRST. Replicate my EXACT w/s. 9. Once you have replicated all my equations, now drop in your company data. 10. Once you have completed one company, just copy w/s into 2 new tabs. 11. Lather, rinse, repeat :-) 12. Excluding your IS and BS data input, your worksheet should be 100% equations. I will be checking for that with your final submission. STEP #2: Complete Company Analysis (Part 2). [PDF DOCS ONLY]. Attached Files: 7200 Template2 - Sep.Oct 2016 v1.0.pdf (362.727 KB) Step Guidance as follows: 1. First, review the sections that are required for Part 2. 2. There are 4 sections: Stock Analysis, WACC Analysis, Beta Analysis, and Cash Flow Analysis. 3. A GOOD RULE OF THUMB is to FIRST build your financial equations by replicating my data in the worksheet. 4. KEEP IN MIND: You will need to complete this worksheet for all 2 companies. Data from this worksheet will need to be transferred to Balance Scorecard. Stock Analysis 1. Retrieve monthly share prices by going to Yahoo HISTORICAL PRICES. 2. Calculate the Standard Deviation using excel formula. 3. Remember, the bigger the standard deviation, the riskier the stock (asset). WACC Analysis 1. Go to your Income Statement and Balance Sheet for the data you need in this section. 2. If your company does NOT have interest expense (or long term debt) data, calculate the arithmetic average of your 2 competitor companies, and use that. 3. You may have to do some work to calculate the DIV GROWTH rate. If you cannot find this stat through research, like stock analyst opinions and recommendations, you will have to calculate it using historical dividend data. Do the following: Go to Historical Prices on YF banner. Change the setting to MONTHLY prices. Make sure you back at least 4 years. In addition to share prices, YF provides the dividend paid. Group the last 4 dividends to make your current year. Do that again for the next 4 dividends. Then do it one more time. Now calculate the dividend growth rate for each year. Use these rates for your dividend growth rate assumption. 4. PLEASE PROVIDE YOUR DIV DATA in the right section of the worksheet with AVG calculations for me to review. BETA Analysis 1. Go to YF Historical Prices section. 2. Set FREQUENCY to MONTHLY. 3. Type in your first company. Copy and paste share prices for 12 months into your excel worksheet. 4. Do this again for your other 2 companies. 5. Now use the same screen to retrieve S&P historical prices. The S&P 500 is like a security. It is an index. Just type in S&P 500 and you will get the index prices. 6. Now replicate the monthly % change logic that I have in my worksheet. 7. The BETA calculation is the % change of the stock (num) divided by the % change of the S&P 500 (denom). 8. The final step is to calculate the 12 month arithmetic average. 9. Transfer that data point to your Balance Scorecard. CASH FLOW Analysis 1. This data will come from the Cash Flow Statement from YF. Last report in the banner. 2. Keep numbers in the THOUSANDS. 3. Replicate my financial logic that I have in my worksheet. 4. Make sure your totals (per section) agree to the totals on YF. You do this by plugging the OTHER line item (in each section). 5. You will need to include SHARES OUTSTANDING for each year. 6. If you cannot easily find the # of shares outstanding, do a little reverse engineering. Since EPS = REV / Shares Outstanding, then just take REV / EPS to back into the # of shares outstanding for each year. 7. Remember, the WTD average #s that you transfer to the Balance Scorecard should be weighted by RECENCY. ((cur.yr x 3)+(mid.yr x2)+(base.yr x STEP 3: Transfer Your Metrics to the Balanced Scorecard. Attached Files: Balance Scorecard Sep.Oct 2016 v1.0.xlsx (14.966 KB) Step Guidance as follows: 1. Download the Balanced Scorecard template. 2. Manually transfer your metrics from the Project Templates to the BS scorecard. 3. Follow all directions on the template. 4. After your metrics have been transferred, now RATE each scorecard COMPONENT based on its value to the business. 5. When you have completed, theoretically, the company with the HIGHEST score is financially the strongest. 6. You will use this BS during your presentation in Week 6. 7. Your presentation should be focused on WHY a particular company scored the way it did. 8. You must be prepared to field questions from the teacher and class. STEP #4: Equity Research Report Report requirements follow: 1. GOAL. The final step of the class project is to produce an EQUITY RESEARCH REPORT / COMPARISON. Equity reports are generally produced for individual companies. However, you will be preparing a COMPARISON of your two companies. Pretend you are a Financial Advisor. Your client informed you that he wants to invest $1MM of his retirement in ONE of the two companies you are researching. Your goal is to prepare a robust, well researched report that concludes with a recommendation of one company. 2. FORMAT. APA format. 5 pages of content (minimum) 10 pages maximum. Page 6 and 7 are the cover page and sources page. Abstract page is not required. 3. GRAMMAR. Grammar, spelling, punctuation, highlights, and business clarity/brevity all count. 4. SECTIONS OF PAPER. You should have the following sections in your paper: Basic Information, Analyst Opinion and Summary, Key Highlights of Both Companies, Snapshot of the Industry, Financial Ratio Analysis, Valuation Analysis, and Investment Risk Factors. 5. HOW TO REFERENCE. Do not cut and paste your Benchmark, Page 2 or your Balanced Scorecard into this report. You can reference and include "key data". 6. INCLUDE PDF DOCS. Please include PDF documents of your Benchmarks, Page 2 and Balanced Scorecard at the end of the report so that I can easily reference. Each PDF document should be ONE page and readable. That means you will have to get good at managing the size of columns and rows for each page. When in doubt, just go to my PDF examples. When you print each report, it prints nicely on ONE page. While it is small, it is still professional and readable. Remember, the inclusion of these PDF documents do not count towards your report page count. 7. REFERENCES. Please use the website references below to learn how to write great equity research reports. 8. YOUR CLASS PRESENTATION. After you have done ALL this work, you should be able to stand up in front of the class and explain your Investment Recommendation. You are expected to give a 3 minute (timed) presentation. You can have notes in front of you -- but no projector slides. My goal with this requirement is to teach you to credibly discuss and present key financial aspects of companies. And be prepared for questions from the class and myself. WEBSITES ON HOW TO WRITE AN EQUITY RESEARCH REPORT http://www.financewalk.com/equity-research-report-writing-format/ https://www.educba.com/write-equity-research-report/ https://www.cfainstitute.org/community/challenge/Documents/rc_equity_research_report_ess entials.pdf Quad/Graphics - Benchmark Template REV 2014 4,862,400 1.4% (3,891,900) 970,500 (425,500) (336,400) 208,600 (92,900) (20,200) (77,200) 18,300 Cash And Cash Equivalents Net Receivables Inventory Other Current Assets Total Current Assets Property Plant and Equipment Goodwill Other LT Assets Total Assets $ $ $ $ $ $ $ $ $ 40,800 814,600 287,800 39,100 1,182,300 1,855,500 775,500 263,900 4,077,200 1.0% 20.0% 7.1% 1.0% 29.0% 45.5% 19.0% 6.5% 100.0% $ 17,600 $ 747,000 $ 272,500 $ 37,200 $ 1,074,300 $ 1,925,500 $ 773,100 $ 392,800 $ 4,165,700 0.4% 17.9% 6.5% 0.9% 25.8% 46.2% 18.6% 9.4% 100.0% $ 31,700 $ 640,800 $ 242,900 $ 74,600 $ 990,000 $ 1,926,400 $ 768,600 $ 413,900 $ 4,098,900 0.8% 15.6% 5.9% 1.8% 24.2% 47.0% 18.8% 10.1% 100.0% Accounts Payable Short/Current Long Term Debt Other Current Liabilities Total Current Liabs Long Term Debt Other Liabs Total Liabilities $ $ $ $ $ $ $ 765,000 96,200 1,400 862,600 1,338,400 723,700 2,924,700 18.8% 2.4% 0.0% 21.2% 32.8% 17.7% 71.7% $ 751,700 $ 134,600 $ 2,500 $ 888,800 $ 1,290,200 $ 697,800 $ 2,876,800 18.0% 3.2% 0.1% 21.3% 31.0% 16.8% 69.1% $ 619,800 $ 123,700 $ 9,300 $ 752,800 $ 1,250,800 $ 859,900 $ 2,863,500 15.1% 3.0% 0.2% 18.4% 30.5% 21.0% 69.9% Common Stock Retained Earnings Treasury Stock Capital Surplus All Other Total Stockholder Equity $ $ $ $ $ $ 1,400 515,200 (218,800) 971,300 (116,600) 1,152,500 0.0% 12.6% -5.4% 23.8% -2.9% 28.3% $ 1,400 $ 558,800 $ (248,800) $ 983,100 $ (5,600) $ 1,288,900 0.0% 13.4% -6.0% 23.6% -0.1% 30.9% $ 1,400 $ 588,100 $ (279,300) $ 985,600 $ (60,400) $ 1,235,400 0.0% 14.3% -6.8% 24.0% -1.5% 30.1% GRAND TOT Total Liabs + Equity B/S proof $ 4,077,200 0 100.0% $ 4,165,700 0 100.0% $ 4,098,900 0 100.0% LIQUIDTY RATIOS CURRENT RATIO QUICK RATIO 2014 1.37 1.04 2013 1.21 0.90 2012 1.32 0.99 WTD AVG 1.31 0.98 ACTIVITY RATIOS INVENTORY TURNOVER AVG DAYS IN A/R AVG DAYS IN A/P ASSET TURNOVER 13.5 61.1 (71.7) 1.19 14.0 56.9 (72.2) 1.15 13.1 57.1 (71.1) 1.00 13.6 59.0 (71.8) 1.15 DEBT RATIOS DEBT RATIO EQUITY RATIO CAPITAL RATIO AVG INT RATE (use LTD only) TIMES INT EARNED RATIO 72% 28% 18% 6.94% 2.2 69% 31% 18% 6.65% 1.7 70% 30% 17% 2.52% 3.4 70.5% 29.5% 18.0% 6.11% 2.2 PROFITABILITY GROSS PROFIT MARGIN OPERATING INCOME MARGIN NET INCOME MARGIN EARNINGS PER SHARE # of common shares outstanding 20.0% 4.3% 0.4% 0.53 34,600,000 20.7% 3.0% 0.6% 0.89 34,600,000 NOI INT EXP TAXES NI ASSETS GRAND TOT LIABS TOT LIABS EQUITY TOT EQ EFFICIENCY MARKET RATIOS PROJECT SPECIFIC RETURN ON ASSETS RETURN ON EQUITY REVENUE / EMPLOYEE # of employees PRICE EARNINGS RATIO MARKET BOOK RATIO MARKET CAPITALIZATION (000s) shares outstanding price of stock at year end book value INVENTORY DAYS REV / PP&E (multiple) REV GROWTH % CASH DAYS (of sales) NEW 7830 Sep.Oct 2016 Project TEMPLATES $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 0.45% 1.6% 202,600 24,000 442.4 7.0 8,096,400 34,600,000 234.00 33.31 27.0 2.62 1.4% 3.06 $ -80.0% 20.0% -8.8% -6.9% 4.3% -1.9% -0.4% -1.6% 0.4% $ $ $ $ $ $ $ $ $ $ $ 0.74% 2.4% 199,829 24,000 257.5 6.2 $ 7,958,000 34,600,000 $ 230.00 $ 37.25 $ 26.2 2.49 17.1% 1.34 100.0% $ -79.3% 20.7% -8.7% -9.1% 3.0% -1.8% -0.5% 0.0% 0.6% $ $ $ $ $ $ $ $ $ 2012 4,094,000 $ COGS GP SG&A 100.0% 2013 4,795,900 17.1% (3,801,900) 994,000 (416,000) (435,800) 142,200 (85,800) (23,300) (2,200) 30,900 Total Revenue Revenue Growth COGS Gross Profit Selling General and Administrative All Other Operating Income Interest Expense Taxes All Other Net Income $ $ (3,183,500) 910,500 (347,100) (456,900) 106,500 (31,500) (20,200) 32,300 87,100 -77.8% 22.2% -8.5% -11.2% 2.6% -0.8% -0.5% 0.8% 2.1% 22.2% 2.6% 2.1% 2.52 $ 34,600,000 20.6% 3.6% 0.8% 0.98 na 2.12% 7.1% 170,583 $ 24,000 0.83% 2.8% 196,340 24,000 79.4 5.6 $ 6,920,000 34,600,000 $ 200.00 $ 35.71 $ 100.0% WTD AVG na 11.0% -79.4% 20.6% -8.7% -8.3% 3.6% -1.7% -0.5% -0.7% 0.8% 320.3 6.5 $ 7,854,200 na $ 227.00 $ 35.02 27.8 2.13 $ NA 2.83 26.9 2.49 11.0% 2.45 09/03/16 Class Project - 7200 Sep.Oct 2016 Template (Part 2) This template will need to be done for EACH company. Mo-End SH Price COMPANY STOCK ANALYSIS Std Dev Standard deviation measures the risk of assets. The larger the StdDev, the more riskier. COMPANY STOCK PRICE mo12 (most recent) mo11 mo10 mo9 mo8 mo7 mo6 mo5 mo4 mo3 mo2 mo1 WACC ANALYSIS $ $ $ $ $ $ $ $ $ $ $ $ 35.25 35.00 34.75 34.50 34.25 34.00 33.75 33.50 33.25 33.00 32.75 32.50 0.944 Current Yr Yr 2 Yr3 WTD AVG DIV DIV GROW The WACC is one of the most important financial calcs in Finance. You have all the data you need from your benchmarks, except dividends. You will need to get that from Yahoo Finance when you download historical share prices. Div Growth Model interest exp long term debt interest rate $ $ 1,800 $ 20,000 $ 9.00% 2,200 $ 30,000 $ 7.33% 1,900 25,000 7.60% current div growth rate current stock price required rate of return $ 1.16 $ 11.54% 35.25 $ 15.21% 1.04 $ 20.93% 32.00 $ 24.86% 0.86 19.44% 31.00 22.76% common stock paid in capital retained earnings total capital $ $ $ $ 2,000 20,000 10,000 32,000 total debt + capital long term debt % capital % $ 52,000 $ 38.5% 61.5% $ WACC BETA ANALYSIS Last 12 Mos. Base Month mo12 (most recent) mo11 mo10 mo9 mo8 mo7 mo6 mo5 mo4 mo3 mo2 mo1 $ $ $ $ 2,000 20,000 11,000 33,000 $ $ $ $ 63,000 $ 47.6% 52.4% qtr1 qtr2 qtr3 qtr4 0.29 0.29 0.29 0.29 1.16 0.26 0.26 0.26 0.26 1.04 0.23 0.23 0.2 0.2 0.86 0.18 0.18 0.18 0.18 0.72 qtr1 qtr2 qtr3 qtr4 2,000 20,000 11,000 33,000 qtr1 qtr2 qtr3 qtr4 58,000 43.1% 56.9% qtr1 qtr2 qtr3 qtr4 12.82% 16.51% 16.22% 14.62% Co Stock S&P 500 Stock % Chg S&P Chg 0.7% 0.7% 0.7% 0.7% 0.7% 0.7% 0.7% 0.8% 0.8% 0.8% 0.8% NA 0.6% 0.6% 0.6% 0.6% 0.6% 0.6% 0.6% 0.6% 0.6% 0.6% 0.6% NA Beta 11.5% 20.9% 19.4% Avg Beta Analysis is a predictor of share price movement in relation to the total market. A Beta of 1.0 means that your company price moves in the same % and direction as the market. CASH FLOW ANALYSIS $ $ $ $ $ $ $ $ $ $ $ $ 35.25 35.00 34.75 34.50 34.25 34.00 33.75 33.50 33.25 33.00 32.75 32.50 $ $ $ $ $ $ $ $ $ $ $ $ Current Yr 1,689 1,679 1,669 1,659 1,649 1,639 1,629 1,619 1,609 1,599 1,589 1,579 Yr 2 Yr3 1.20 1.20 1.20 1.20 1.21 1.21 1.21 1.21 1.21 1.21 1.21 NA 1.21 Current Yr Yr 2 Yr3 WTD AVG Cash Flow data will come from the Cash Flow Statement on Yahoo Finance. Collapse detail data to the line items I have below. You may want to work in thousands or millions. Up to you. Data in the right is cash flow data per share. Make sure you do necessary conversions to make cash flows per share meaningful information. Oper Activities net income add: depreciation other changes total oper activities $ $ $ $ 500 20 (10) 510 capital expenditures investments other total invest act $ $ (200) $ 40 $ $ dividiends paid stock transactions net borrowings other total finance act Net Cash net cash activity Common Stock O/S common shares Invest Activities Finance Activities 7200 Template2 - Sep.Oct 2016 v1.0 500 20 30 550 $ $ $ $ 0.05 0.00 (0.00) 0.05 $ $ $ $ 0.05 0.00 (0.00) 0.05 $ $ $ $ (300) $ 50 $ (400) 60 (160) $ (250) $ (340) $ $ $ (100) $ 200 $ 300 $ (80) $ 300 $ 320 $ (90) 250 350 $ 400 $ 540 $ 510 $ $ $ $ $ $ $ $ $ $ (0.02) 0.00 (0.02) (0.01) 0.02 0.03 0.04 $ $ $ $ $ $ $ $ $ $ (0.03) 0.00 (0.02) (0.01) 0.03 0.03 0.05 $ $ $ $ $ $ $ $ $ $ $ 750 $ 805 $ 720 $ 0.08 $ 0.07 $ 10,000 $ $ $ $ 500 20 (5) 515 11,000 1 $ $ $ $ 0.04 0.00 0.00 0.05 $ (0.03) 0.01 (0.03) $ (0.01) 0.02 0.03 0.04 $ 0.06 $ 0.05 (0.02) 0.04 0.07 12,000 09/16/16 7200 Sep.Oct 2016 - Balanced Scorecard DIRECTIONS 1. Transfer all your data to this Balance Scorecard. 2. Please make sure that cells are formatted correctly. EG: percentages have percent sign. 3. Do NOT change the "percentage weights" that are in Column C. 4. Based upon your data, you are to ASSIGN which company gets a 2 (highest rating) vs a 1 (lowest rating). 5. Pay attention to when a high metric is FAVORABLE vs a high metric that is UNFAVORABLE. 6. When in doubt, please consult a classmate on Db. 7. Make sure you re-label each column with Company abbreviation name. Input METRICS here Input RATINGS here % TGT Co CO1 TGT Co current ratio 3.50% 1.3 1.2 2 1 0.07 0.04 quick ratio 3.50% 0.7 0.8 1 2 0.04 0.07 inventory turnover 3.50% - - avg collection period (days) 3.50% - - avg payment period (days) 3.50% - - total asset turnover 3.50% - - debt ratio 3.50% - - equity ratio 3.50% - - avg int rate 3.50% - - times int earned ratio 3.50% - - gross profit margin 3.50% - - operating income margin 3.50% - - net income margin 3.50% - - earnings per share 3.50% - - return on assets 3.50% - - return on equity 3.50% - - revenue / employee 3.50% - - price earnings ratio 3.50% - - market book ratio 3.50% - - market capitalization (000s) 3.50% - - price of stock at year end 3.50% - - book value 3.50% - - CASH FLOW ANALYSIS operating cash flow / share 3.50% - - investment activity / share 3.50% - - financing activity / share 3.50% - - WACC wacc 3.50% - - STOCK PRICE company stock price 3.50% standard deviation 3.50% - - stock beta 2.00% - - LIQUIDTY RATIOS ACTIVITY RATIOS DEBT RATIOS PROFITABILITY EFFICIENCY MARKET RATIOS TOTAL 100.00% $ 57.00 $ 78.00 1 CO1 Auto-Calcs. 2 TGT Co 0.04 0.11 CO1 0.07 0.11