Statement of Personal Benefit: Every employer wants you to SHOW them that you ca

Statement of Personal Benefit:
Every employer wants you to SHOW them that you can do something, not just tell them that you can. The purpose of this assignment is to give you something to show. You will be working with data from the real world and not a sterilized textbook experiment. Your data source is actual company filings at the Securities and Exchange Commission (SEC). Real analysts on Wall Street do what you are about to do. I have actually taken some of the components of this assignment from interactions with a Senior VP at Bank of America, a VP at BNY Mellon, and a Senior Equity Research Analyst at Jeffries. These contacts volunteer their time to work with the Business Analytics Team, which is available to all PSU Behrend and PSU World Campus Finance students. You will have an experience that helps you think critically about real-world data. The estimated time to completion is 10+ hours. Make sure that you watch the video below to make your life much easier.
Gathering Financial Data from the SEC: Step 1: Download this Excel template.
Step 2: Navigate to http://www.sec.gov/ to an external site.. Select the “Filings” dropdown menu and the option for “Company Filing Search”.
Step 3: Using the search box in the middle of the page, type in the ticker symbol for the company in question. Your first company is Proctor & Gamble and the ticker symbol is PG. After you type in the ticker, select your company from the drop-down list that appears. (Do not type PG and then press enter. Wait for the dropdown list to load.)
When the search result loads a page that says “EDGAR: Company Search Results”, then you need to select “Classic Version” in the upper right corner. At this point, you should see a series of rows with company filings listed with the most recent filings at the top. You will see in Row 1 on the template, which annual reports (10-Ks) you need to find. Locate your first 10-K and select the blue oval that says “Interactive Data”. Once the new page loads, just below the company’s name in the upper left corner, you will see a link that says “View Excel Document”. Click this link and you will download the full 10-K, which includes the Balance Sheet, the Income Statement, and the Statement of Cash Flows. At this point, you should just be gathering data. Download all spreadsheets needed first. See Row 1 on the Excel template. Wait to use the data until the next section on these instructions.
Step 4: Repeat step 3 to gather all needed information for Proctor & Gamble. In Row 1 on the provided template, you will see all the 10-Ks that you need to gather.
Step 5: Repeat step 3 to gather all needed information on your second company, which is Colgate Palmolive (ticker: CL). Your third company under analysis is Clorox (ticker: CLX). I have provided you with the information that you need for CLX so that you can see how this process is done.
Using the Downloaded Data:
Step 1: It is extremely important to understand that companies in the real world will periodically adjust their financial results. The 2018 results that were originally reported on the 2018 10-K may be changed/adjusted on the 2020 10-K. For this reason, you must use all information needed from the most recent 10-K. Then go to the next most recent 10-K and gather any information not already provided in the most recent 10-K. For example, use the 2018 Sales disclosed on the most recent (i.e., 2020 10-K), not on the original 2018 10-K.
Step 2: On the template, you will see certain rows that are highlighted in blue (i.e., Operating Income, Net Income, Total Assets, etc.). You need to type a formula into each of these rows. The formulas are left in for you on CLX to help you understand the logic. The purpose of this step is so that when you adjust the statements, the major line items (i.e., Operating Income, Net Income, Total Assets, etc.) will automatically adjust too. The items listed below should all use a formula, which means this number should not be copied over in step 3. Verify that each of these lines matches the 10-K before you make adjustments. You need to use formulas in:
Inventory, Net (this only applies to PG)
Total Current Assets
Total Assets
Total Current Liabilities
Total Liabilities
Total Equity
Total Liabilities plus Total Equity
Operating Income
EBT
Any version of Net Income (some firms have two or three levels of Net Income on the Income Statement).
Step 3: Now that you understand the process, you need to begin copying your information from the downloaded spreadsheets to the template. Watch the video below to see how easy this process can be. Check the row at the bottom of the Balance Sheet. It should say “YES” if your Balance Sheet is in balance. If it does not say “YES” then something is wrong with your spreadsheet.
Special Note: You may find that PG (or another company) chooses to not list cash as a line item on the Balance Sheet. If you find this, then you need to look at the bottom row (ending cash balance) on the Statement of Cash Flows. This is an annoyance, but you are dealing with real-world data and it is sometimes a little messy and not consistent. You may also find that a company reports Interest Expense as a negative number (i.e., in parenthesis). This is just a display issue. If interest expense were negative then someone is paying the company to take out a loan. Enter the absolute value of Interest Expense.
Adjustments:
An analyst will make adjustments to reported financial statements. The easiest adjustment is for one-time items that are not expected to keep happening (i.e., disposition expenses, retirement plan charges, goodwill impairment, asset writeoffs, etc.). Check Column O on the Income Statement for instructions on some items that need to be made zero if they exist in a given year. Since you used formulas in step 2 of the last section, your main items of interest will automatically adjust.
Calculations:
Note: All calculations must involve a formula in Excel. You need to use cell references and write out your formulas. Do not just use Excel functions except for the AVERAGE function which is acceptable for the 5-year average for each ratio.
Step 1: Before you begin making calculations, please make sure that you notice the comment in cell D16 on the Ratio Summary page, the data that I am giving to you in row 46 on the Balance Sheet, the NIBCL notations in column N on the Balance Sheet, the WACC that I give to you in row 24 on the Income Statement, and all comments in column O on the Income Statement. Also, please make sure that you are using the course ebook as a guide for all formulas. Chapter 13 is specifically designed as a tool to help make this project a success for you.
Step 2: You will be calculating financial ratios on the “Ratio Summary” tab on the Excel template. You need to use cell references for all formulas. DO NOT simply type in numbers. You need to use formulas. You will automatically lose 10% of the assignment grade if you simply type in numbers rather than use formulas. Part of this assignment is about learning how to use Excel. You will likely need to learn how to use parenthesis to isolate the order of operations in your formulas. Also, make sure that you are using cell references in all formulas. Do not simply type numbers into the cells.
Step 3: Calculate the ratios for each company. In Excel, you can use the “$” symbol to lock in a cell so that you can drag a formula across a row. Check out this YouTube video: https://www.youtube.com/watch?v=b3i6AgMEssI this is a new concept for you. Make sure that you know which formulas require averaging and why they need averaging. See the course ebook if this is confusing at all. Chapter 13 in the ebook was written as a guide for this project.
Step 4: Calculate the five-year averages (in each row) using the AVERAGE function in Excel. The CAGR is completely different. Look at the CAGR discussion in the ebook for examples of exactly how to do this. For the Revenue CAGR row on the Ratio Summary tab, you need a one-year CAGR for each individual year and a five-year CAGR for the five-year average. Apply the same logic for the Net Income CAGR. Make sure that you are using the right row for Net Income. See the highlighted comment in cell O15 on the Income Statement for PG on the template provided.
Check the Annual Report:
Step 1: Google the “Annual Report for Proctor & Gamble”. The annual report contains all of the financial statements that you have already worked with in Excel, but they are packaged in a way to be more digestible for investors. The Annual Report also includes a very valuable enhancement called Management’s Discussion & Analysis (MD&A). Pay attention to strategic goals, any mention of “organic” sales growth, any mentions of risks or opportunities that they are facing. Also, pay attention to any comments about Research & Development (R&D). It is not a good sign for a company to have weak organic sales growth and/or declining R&D spending. You need to replicate this process for Colgate and Clorox so that you are discussing the MD&A for all three firms.
Analysis and Discussion:
You need to analyze each ratio calculated. This means all three firms. You want to consider: (1) the current calculation; (2) the five-year average for the metric in question; and (3) the trend. Your paper should include this depth of discussion. You need a subheading on your discussion paper for each bulleted item below. Note: Your subheadings should look professional and not simply be a series of bulleted items or be a copy and paste from my list below.
MD&A Discussion
Current Ratio Discussion
Quick Ratio Discussion
Working Capital Discussion
Inventory DOH Discussion
Receivables DOH Discussion
Total Asset Turnover Discussion
Debt-to-Equity Ratio Discussion
Interest Coverage Discussion
Discussion of CAGRs (both revenue and net income)
Profit Margin Discussion
Return on Assets Discussion
EVA Discussion
ROIC Discussion
DuPont ROE Discussion
Investment Recommendation
In each of these subheading areas, you need to discuss the current value of the metric, the trend of the metric, and the five-year average of the metric. Make sure that you interpret the numbers and what they mean to an investor. Each subheading should discuss all three companies all conclude with a statement in boldface of which firm wins based on this metric, in your opinion. You also need to make a recommendation on how to allocate $100,000 between these three stocks. Focus on strong logic and use details from your financial analysis to inform your recommendation.
Deliverable:
You will turn in a 3-4 page (minimum length requirement), single-spaced paper in MS Word.
Use Times New Roman, 12-point font with 1-inch margins.
Make sure that you follow the standards for all written assignments posted in the Course Resources module.
Make sure to organize your thoughts logically and professionally. Use paragraphs and good sentence structure. Also, use subheadings as instructed above.
You will also turn in your MS Excel file.
If you only turn in the Excel file, then you have only done a portion of the work. The rubric is setup such that over half of the assignment points are linked with the Word document.
The finished product should have an appearance that you would be proud to show in an interview.
This project is not a group project. Your submission should represent your individual effort. In accordance with Penn State’s Academic Integrity Policies, if I suspect that your submission does not represent your individual effort, you may receive a zero on the assignment at the instructor’s discretion.
Please make sure content and grammar is professional and correct. My professor is really strict. I have a video from my professor where he explains how to do the assignment. However, I cannot send it as a screen recording that I have saved to my computer. Do you know any other way how I can send it to you? Since I believe it would be really helpful.