Financial Analysis Project – 80 points
The purpose of this assignment is to analyze a company and make an informed decision about the company’s past performance and its future potential, while utilizing the basic functionality of Excel to complete your analysis. Students must choose one company from the list below to analyze for the project.
I chose this company from the list
Best Buy: BBY
Find the most recent annual report (10-K) from the following website, searching by the company’s ticker symbol shown above in the search bar: https://www.sec.gov/edgar/searchedgar/companysearch.html. You will download the Excel version of the financial statements (or copy them into Excel) and perform all ratios using Excel. You will also use the written version of the 10-K
Word Document (60 points)
1. Describe the history, development, and primary operations of the corporation. What do you find interesting about the company? Why did you choose this company? Obtain this information from the 10-K, company website, Google, etc. (Limit discussion on #1 to one page maximum.) (4 points)
2. What is the purpose of the section of the 10-K entitled “Management Discussion and Analysis”? Discuss this portion of the 10-K. What is interesting? What can you tell about the company and their management? (3 points)
3. What has been the lowest price of the common stock in the last year? What has been the highest price of the common stock in the last year? Include a one year historical graph of stock price – you can get this graph from yahoo finance. (3 points)
4. What has been the trend in the stock price over the past year from today’s date and prior (upward, downward, volatile)? Find at least one piece of news that is reflected in stock price and discuss the event and change in stock price. This could be the announcement of a new product, mergers, acquisitions, etc. Discussion required here. This discussion must be in your own words and your interpretation of the stock price. (5 points)
5. Has there been a growth in profits (Net Income) over prior years? Discuss and include the net income numbers for the last two years. If you do see growth, why? If not, why not? Obtain the earnings per share amounts from the face of the income statement and include these in your discussion. A discussion is required here about the company income fluctuations, EPS, and what has been going on with the company to contribute to income fluctuations. Again, this discussion must be in your own words and your interpretation of company profits, EPS, etc. (4 points)
6. What does the 10-K and/or company website say regarding ethic responsibility as a company? Do you think this is sufficient? Is the company doing their part to create an ethical workplace? In your opinion, are they maintaining ethical practices? If you cannot find this information in the 10-K, Google the company name and ethics. Research the company’s ethical policies and summarize. (5 points)
7. Review the financial statement footnotes of the company you are analyzing to obtain information regarding the methods used for two significant accounting policies that we have learned about in class. (Examples include inventory, receivables, payables, fixed assets, expenses, etc.) These are generally listed in Note 1 after the financial statements (should be labeled Summary of Significant Accounting Policies). Describe how each method affects the financial statements and the decisions made by management within the company you are analyzing. Do not just copy and paste the wording from the report. You must include a discussion of the policies and how it impacts the financial statements and management decisions. This discussion must be in your own words. (8 points)
8. Examine the cash flow statement and describe one line item from each section (operating, investing, and financing). You will include a discussion for each of the three items. What is the item and amount? How does the change affect cash? Only list one from each section and explain what it means for company operations. Discussion required here in your own words. (4 points)
9. Discuss the importance of the three main financial statements (Income Statement, Balance Sheet, and Statement of Cash Flows) and the notes to the financial statements. What do each of these statements and the notes tell you? What were you able to determine about the company by looking at the financial statements and 10-K? (4 points)
10. Ratio Discussion – Include your ratio discussion and a table of the results within the body of the Word document. All ratios must be calculated within Excel. (See below.) After calculating the ratios, briefly explain whether the company is performing better, similar to, or worse in comparison to the prior year (FOR EACH RATIO). Do not just state that they are doing better, because it is higher, you must explain why. The goal is to demonstrate that you know what the ratio means not just state what happened to the numbers, which changed the ratio. If a ratio is not applicable, explain why. (10 points)
11. Invest or Not? Given your research, would you invest in this company? Why or why not? Include a discussion of the ratio results and the other research completed. Justify your decision based on all research. Discussion here approximately 1 page. (7 points)
12. Overall format, spelling, punctuation, grammar, citations. When using outside sources including websites, the 10-K, etc. you must cite your sources. (3 points)
Excel Document (10 points)
Create an Excel document with the following:
• Tab 1 label “Income Statement” and recreate the Income Statement from the 10-K. You can also use the Excel document downloaded from the SEC website according to the instructions above. If you do this, delete all tabs that are not related to the computations.
• Tab 2 label “Balance Sheet” and recreate the Balance Sheet from the 10-K.
• Tab 3 Ratio Calculations
All numbers in the ratio calculations must be linked to the financial statement tabs. No numbers can be hard coded in the formula. For example, the current ratio would look something like this: =Balance Sheet B5/Balance Sheet B16 where B5 is current assets and B16 is current liabilities. All ratios must be computed for two years.
• Net Profit Margin = Net Income / Revenues
• Gross Profit Margin = (Net Sales Revenue – Cost of Goods Sold) / Net Sales Revenue
• *ROE = Net Income/Average Common Stockholders’ Equity
• Current Ratio = Current Assets / Current Liabilities
• *Days to Collect = 365 / Receivables Turnover
o Receivables Turnover = Sales or Revenue / Average Receivables
• *Days to Sell = 365 / Inventory Turnover
o Inventory Turnover = Cost of Goods Sold / Average Inventory
• Debt to Assets = Total Liabilities / Total Assets
*These ratios require an average calculation. To calculate the prior year average for your comparison, you will have to find the prior year 10K (scroll until you find it on the SEC website).
• There is no page requirement for the project. Be sure to answer each question thoroughly and provide in depth analysis when requested.
• Citation format is up to you. Footnotes are nice and present a clear document. You can use whatever style of citation you prefer.
• Numbering each question in the paper is preferred versus an essay format.