project essay + excel
Financial statement analysis project This project requires you to conduct firm and financial statement analysis on a company and peer of your choice. As you work on the project, make sure you read everything in this document. Note that the instructions and guidelines for the project may be modified during the semester to provide additional clarity or to increase the project’s relevance to the course. General Notes Writing expectations For written material to earn full credit, it must be clear and easily understandable. The submission should also be grammatically correct and have very few, if any, typos and other errors such as incomplete sentences. In addition to simply being understandable, the discussion should include all items laid out in the instructions. All assertions and conclusions should be clearly conveyed and supported by data, information, and sound reason. Remember that a company is singular. For example, Walmart wants to be a low-cost seller, so it has low prices. “They have low prices” is not correct. If you are concerned about your writing, you are encouraged to make an appointment with the GMU writing center for help with any writing-related assessment in the course (https://writingcenter.gmu.edu/). The text in written documents should be double-spaced and 12-point font in either Calibri or Times New Roman font. All margins (top, bottom, left, and right) should be 1 inch. Page numbers should be included (preferably, at the bottom center of the page). New paragraphs should be clearly indicated either by starting with an indent or by having a blank line between paragraphs (this document, which is single-spaced, takes the “blank line” approach). Text should be left justified. All written documents should be submitted as a pdf file, not as a Word (or other word processing software) file. I anticipate that many of you will work in Word or other word processing software such Google docs, which is fine, but when you have your final version, save it as a pdf or print to file. Submissions should be made through Blackboard. If I cannot open a document, then you may not receive any credit for it. This is true for all documents submitted through Blackboard – Excel, PowerPoint, or Adobe (pdf). Therefore, you are responsible for ensuring that your document was submitted properly and fully accepted by Blackboard. In general, emailed submissions will not be accepted. Project requirements 1) Select a company to analyze This project requires you to conduct firm and financial statement analysis on your choice of a company that satisfies all of the following 5 conditions: 1) The company is headquartered in the United States 2) The company has at least 2 full years of historical data 3) The company stock is currently trading on the NYSE or NASDAQ (which refers to any stock on wither the NASDAQ Global Select Market, NASDAQ Global Market, or NASDAQ Capital Market) 4) The company has total assets of $250 million or more 5) The company is not primarily engaged in financial, insurance, or real estate activities. Banks, credit card firms, mortgage lenders, insurance companies, and real estate investment trusts are among those entities that cannot be analyzed. 2) Fill in the relevant cells in the Excel file provided with the project Fill in all the yellow and green cells in the Excel file provided with the project. Do not add any rows or columns to the spreadsheet on this file. Do not change the name of the spreadsheet in the file. Making these changes could have a substantially adverse effect on your grade. Also, do not enter anything in the columns highlighted in black. Some of the requested figures are ratios and measures that will be included in the written report. Other ratios and measures – net working capital, operating net working capital, and common size financial statement figures – are asked for only in the spreadsheet. Make sure that you appropriately use information for the previous year and the most recent year. The order of the years in the table is the opposite of the order in the Form 10-K. All ratios and measures should be computed with financial statement data for the consolidated firms obtained from your choice of sources. The firm’s consolidated income statement, balance sheet, and statement of cash flows in the Form 10-K (typically in item 8, but sometimes in Item 15 or other appendix) are useful as are library resources like NetAdvantage and Mergent, as well as financial websites like yahoo.finance.com. You can report figures for the “raw data” part of the spreadsheet in dollars, thousands of dollars, millions of dollars, or billions of dollars. Whichever approach you choose, be consistent throughout the “raw data” part of the spreadsheet. Note that some “raw data” items refer to subtotals and totals, and others refer to more specific items (like accounts receivable or short-term debt). All relevant items in a subtotal or total may not be requested. Therefore, subtotals and totals will not necessarily equal the sum of items in the “raw data” from that category. For example, total current assets would not necessarily equal the sum of the reported current asset items (cash, inventory, etc.), because all current items may not be listed in the “raw data” items. Simply use “raw data” to report information you get from another source (or sources, if necessary). Two helpful reminders are to make sure that 1) total debt = sum of short-term debt, current portion of long-term debt, & long-term debt (in long-term liabilities) and 2) total equity = total assets - total liabilities. Also, remember not to change the name of the spreadsheet. You can rename the file, but keep the tab name as FSA info. My preferred way to get a firm’s 10-K is to search on “company name 10-K” and use the link that go to the company’s web site (there are services that provide 10-Ks, but I don’t like them as much). The SEC is another useful place for obtaining a Form 10-K (https://www.sec.gov/edgar/searchedgar/companysearch.html). Important note on financial ratios & measures Financial ratios and measures should be computed with the approaches covered in class. Do not simply copy the ratios and measures from elsewhere in the Form 10-K or some other source. Although the spreadsheet may be formatted to present answers to a limited number of decimal places, the actual numbers that make up your answers should not be rounded. Using Excel to compute ratios and measures will make this occur automatically. If you use a calculator, which I advise against, make sure to include many, many decimal places in all your work so that any rounding error is undetectable. The acceptable margin of error is very small, so even very small rounding differences can result in an answer being marked incorrect. You can use cells below the “Ratios & Measures” section to do interim work if desired. For ratios and measures that involve both a stock and a flow, use the end of period stock, which is the method used in the course throughout the term. If you use the stock from the start of the period or from an average, the answer will be marked incorrect if that stock changed over the period. In some cases, an item in the “raw data” may equal 0, making it impossible to compute certain ratios and measures. If you encounter such a situation, then enter that item as 1 in “raw data.” For example if a firm had no inventory, write in 1 instead of 0 in the “raw data” items part of the spreadsheet. Sometimes, if a firm has 0 for an item, that item is not reported. If you encounter this situation, enter the amount as 1 in “raw data.” Inventory, short-term debt, and current portion of long-term debt are cases where this is common. Note that, 0s are being changed to 1s, so that everyone can do every computation for this project. It is not something that is regularly done by analysts. Assume the tax rate is 27.00 percent when computing return on invested capital (ROIC). Common size financial statement measures should indicate the figure that would be presented for the relevant item on the relevant common size financial statement. You will need to complete 2 years of measures for “your firm” and 1 year for another firm. The other firm should be a competitor to your firm that you think is a good peer for comparison. A good peer is one that engages in similar activities. Other factors like being generally similar in size are often taken into account as well. The peer must satisfy the same 5 conditions that “your firm” satisfies – headquartered in the U.S., 2 years of data, traded on NYSE or NASDAQ, assets of at least $250 million, and not a financial, insurance, or real estate firm. If any of the competitor’s “raw data” items are 0, enter them as 1. Your firm’s Form 10-K may list key competitors and an Internet search of “competitors your firm” may be productive as well. It is preferred that the “most recent” years for your firm and the other firm be similar, but given differences in the dates of fiscal years, I recognize that they may differ. It’s acceptable for the project for the “most recent” years to differ between the 2 firms. Grades for this piece of the project are based on following the relevant directions. They are also based on the accuracy of the figures in the relevant cells. 3) Complete the “ratios and measures” table Complete the table that can be found at the end of this document using figures from the Excel spreadsheet. Replace all “name” headings and labels with the actual names. The table should be presented on a single page, as the last page of the written analysis described in item 3. If you copy and paste from Excel into Word (or other word processing software), make sure the table is formatted correctly with single-spacing (unlike the rest of the write-up), 12-point font, and center-justified entries. All figures in the table should include 2 digits to the right of the decimal point. Profitability measures should be presented as percentages (e.g., 23.45% or 1.50%) and measures for all other types of ratios and measures should be presented in decimal format (e.g., 23.45 or 1.98). Make sure to change the following items in the table template to the relevant names and years for your project: Other