Capstone ProjectCapstone ProjectIn this project, you will use your understanding of financial accounting to analyze a company’s financial statements. To prepare this analysis, you will use data from the Internet and organize it using excel worksheets. You need to complete the worksheets first to compile the data utilized in writing the paper.Then, you will communicate the results of your analysis in a detailed written report (see rubric) and highlight the results in a power point presentation that you will present in Canvas to the class. Accordingly, this project will help you build your critical thinking, technology and communication skills. Please organize your paper as listed below. Do not follow the outline in the text. There are differences between this project and the text project. Points will be deducted if you do not follow the outline below.
This is a team project. You will be self-assigning to teams.It is your responsibility to interact with class members and find those with an interest in a corporation you are interested in, too. Under no circumstances will I assign you to a team because you failed to do this. At the conclusion of the project you will complete an evaluation on yourself and your team members. Should your team members give you a score of less than 10 of 15 points on participation; you will lose 10% of the total grade. If they give you a score of less than 5 of 15 points participation, you will lose 20% of the total grade for the project. I must receive your evaluation before a final grade is issued.If I do not receive your evaluations, your final grade will be reduced by 20%. It is very important that no one team member take on the responsibility of preparing all the Excel worksheets. Each team member should take a worksheet and then write the corresponding paper that is the analysis of that worksheet. The other sections should be divided up among the team members.You will submit the checklist of who is completing which part of the project by the end of class the first night, August 22. Failure to submit this checklist will result in a 10% penalty on your overall score.Please be sure to start early as this project is due Monday, October 9, MIDNIGHT in theCanvas Assignment Forum. You will present your power point to the class on TUESDAY, October 3 if you are in a hybrid class. Your worksheets are due at our second meeting, September 5. Failure to submit these worksheets in class that night will result in the complete loss of points for those worksheets. Errors in your worksheets must be corrected or your analysis will be wrong. Worksheets will be graded for accuracy.If you are in an online course you will be posting your power point to the week 8 discussion forum. Students will have the chance to review and discuss it on the discussion forum the following week. You will be uploading two files into the canvas assignment folder. The first is your paper, which must be submitted in WORD. Do not copy the excel worksheets into your word doc paper. The second is your excel workbook. You submit this as one file only, not individual worksheets. Only one member of your group should upload the project into the assignment dropbox. DO NOT UPLOAD YOUR POWER POINT IN THE DROPBOX. Please do not use the dropbox to exchange files. You have a discussion forum set up for your group to accomplish this. Professional Quality. Prepare your report as a professional looking document for which you were paid a great deal of money. Be careful with grammar and spelling; business leaders have a “zero tolerance” policy regarding basic spelling and grammar mistakes. Your paper should be written in 12 pt. font, one inch margins and double spaced.All parts should be written in the same font. Do not label each part as to who prepared it. The paper should read as one cohesive paper. Use “we” not “I” when writing. It should follow the order cited below and in the rubric (which is in your excel worksheets. You should have headings for each section of the paper.Proper Referencing. Copying or rewording another report without proper citation and quotation is considered plagiarism. In the body of the paper, include proper references to all written materials and internet sources, with a complete page of sources used. All papers are run through the plagiarism checker Turnitin and any having more than a 20% match will receive a 0.
Part 1-The paperWritten Report and Company ResearchPurpose:• Use a variety of resources to research a corporation.• Research the current stock quote of your company and analyze past market activity.• Prepare a financial statement analysis using condensed, trend and common-size financial statements.• Compute financial statement ratios and analyze.• Compare company ratios to industry averages.• Support your investing advice with three to five significant points based on previous research, analysis and sound reasoning.• Prepare a well-written report analyzing your corporation using appropriate business and accounting vocabulary.
Part A: Tell me about your company-its industry, its products, and its market (1-3 pages)10 pointsChoose a company to research. The company must be publicly traded but not regulated. Each team will select a different company. Four students per team. The instructor must approve the company. If you are undecided about which company to select, choose one of the 30 corporations comprising the Dow Jones Industrial Average (DJIA) listed below or one of the companies used in this text, which can be found in Appendix A. (see page 301). Research your company using a variety of resources.1. Explore the corporate website, which usually provides updated comprehensive information including links to current news items and financial information. Note that the company provides the information posted on the website and, therefore, may be biased in favor of the company. Answer the following questions:a) Location of the corporate headquartersb) Industryc) Primary products or services provided by the corporationd) Major competitors
2. Review the Annual Report, especially the Letter to Shareholders and Management’s Discussion & Analysis, which summarizes the past year and highlights future opportunities. Links to the Annual Report are usually found on the company website under Investor Relations.
3. Consult the Form 10-K filed with the SEC. This form contains a wealth of information. Read through Part 1 Item 1. Business to find information regarding the business, product offerings, market strategy, competition, and market share. Form 10-K information is available at http://.sec.gov/edgar/searchedgar/companysearch.html.
4. Additional sources of information which can be accessed through the Library research portal are:a) D&B Industry Norms and key business ratiosb) Standard & Poor’s Net Advantagec) Almanac of Business & Industrial Financial ratiosd) Hoover’s
5. Sources of financial statements are:a) Yahoo financeb) Morningstarc) Bloomberg Business Weekd) MSN Money
Part B: Company News (1-2 pages)10 points
Search for at least to significant news items or events that have affected your company or its industry over the past year. Briefly describe each event. Next, discuss how this event will affect the company’s operations and the financial ratios. Make certain that the item is truly newsworthy unless it is a major surprise.
Part C: Stock Market Activity (1-2 pages)10 points
Summarize the market activity of your company’s stock, including the current stock quote and market activity over the most recent ten years. In addition, explain trends and events influencing the stock chart. Include your company’s ten-year stock chart.Find stock market activity information and charts at:
Part D: Financial Statement Analysis (4 pages, one for the Balance sheet, one for the Income Statement, one for the Cash Flow Statement and a summary page)
Collect three years of financial statement information to prepare the condensed balance sheet, the condensed income statement, and the condensed statement of cash flows. Use the three most recent years available. Copy the information into your Excel worksheets. Your worksheets are condensed versions so you may need to clump together certain categories to make the information fit into our format. There is a worksheet for the Balance Sheet (1), a worksheet for the Income Statement (2) and a worksheet for the Cash Flow Statement (3).Do not change the formatting of the excel worksheets. I have created them to convey the information in the best form. Do not alter them in any way. You are to use formulas in your excel worksheets1, 2, 3 to calculate the trend and vertical analysis on each statement. When I click on the cell, I need to see the formula. Points are deducted if you do not input formulas.The purpose of the worksheet is to help you organize your information so that you can write about the results in your paper.
Financial Statement information can be found at:
Balance Sheet: Prepare a trend analysis and common size balance sheet using the amounts from the condensed balance sheet. Enter the results on worksheet 1.Income Statement: Prepare the trend analysis and common-size income statement using the amounts from the condensed income statement. Enter the results in worksheet 2.Statement of Cash Flows: Prepare the trend analysis and common size cash flow sheet using the amounts from the Statement of cash flows. Enter the results on worksheet 3.
Use the data on worksheets 1, 2, 3 to analyze each Financial Statement (1 page each)
Balance Sheet: Review the condensed, trend, and common-size balance sheet. Summarize your analysis in a one-page written report. Comment on trends in the growth of assets, liabilities, and stockholders’ equity, and overall financial risk.After you perform the horizontal and vertical analysis, answer the following questions for analysis:1) What was the increase or decrease in assets from year to year? Which assets were the primary reasons for the increase or decrease?2) Did liabilities increase or decrease from year to year? Which liabilities had the most impact?3) Was there an increase or decrease in stockholders’ equity? Why?4) Which balance sheet accounts experienced the most significant changes from year to year? What events may have caused these changes?5) Were there any other significant observations from your review of the balance sheet?
Income Statement: Review the condensed, trend, and common-size income statement. Summarize your analysis in a one page written report. Comment on trends in the growth of revenues, expenses and net income and overall profitability.After you perform the horizontal and vertical analysis, answer the following questions for the analysis:1) Did the company have foreign currency translation adjustments? If yes, were there significant changes from year to year? What were the reasons for these changes?2) Did the company have unrealized gains or losses on securities? If yes, were there significant changes from year to year? What were the reasons for these changes?3) Did the company have unrealized gains or losses on pension obligations? If yes, were there significant changes from year to year? What were the reasons for these changes?4) Were there any other significant observations from your review of the statement of comprehensive income? If yes, please discuss.20 points
Statement of Cash Flows: Review the condensed, trend, and Cash Flow statement. Summarize your analysis in a one page written report. Comment on trends in the growth of operating, investing, and financing activities, and the overall cash position.After you perform the horizontal and vertical analysis, answer the following questions: 1) Were there significant changes in the company’s operating cash flows? What were the reasons for these changes?2) Were there significant changes in the company’s investing cash flows? What were the reasons for these changes?3) Were there significant changes in the company’s financing cash flows? What were the reasons for these changes?4) What were the most significant sources of cash?5) What were the most significant uses of cash?6) What were the most significant reasons for the differences between net income and cash flows from operating activities?7) Were there any other significant observations from your review of the statements of cash flows?
Summarize your results in a 1-page Written report.10 points
Part E: Ratio Analysis
Collect the additional data needed for ratio computations listed below:• Accounts receivable• Inventory• Interest Expense• Capital Expenditures• Dividends paid
Report this information on worksheet D-Ratio Analysis.
Compute two years of ratios for each ratio listed below:
a. Return on Sales (Income)5 pointsb. Return on Assets5 pointsc. Return on Equity5 pointsd. Gross Profit Margin5 pointse. Accounts Receivable Turnover5 pointsf. Inventory Turnover5 pointsg. Asset Turnover5 pointsh. Current Ratio5 pointsi. Debt Ratio5 pointsj. Financial Leverage5 pointsk. Times Interest Earned5 pointsl. Free Cash Flow5 pointsm. Capital Expenditures 5 pointsn. Dividends 5 pointso. Overall Presentation 5 points
Your ratios must be presented in table form.Ratio formulas can be found in Appendix B. Record your results on worksheet 4-Ratio Analysis. Note that all ratios may not apply to your company.
Analyze Ratio Trends:
Compare the two years of ratio information. Indicate whether each ratio is stronger/weaker, quicker/slower, more/less liquid, or more/less risk on worksheet D-Ratio Analysis.The 5 points per ratio includes the analysis, not just the ratio itself. Liquidity ratios measure a company’s ability to meet current obligations such as paying accounts payable or short-term debts. Liquidity ratios may be the most important ratios when evaluating the financial health of a corporation. This is because they reveal a company’s ability to pay short-term obligations as they become due. The liquidity ratios are current ratio and the quick (acid test) ratio.Profitability ratios measure how well management is using the resources of the company to earn a profit. This includes the ability of management to control expenses while increasing revenues. Below are some of the common profitability ratios used by those interested in an organization. Profitability ratios include return on sales, return on assets, return on equity, earnings per share, and gross profit margin.Efficiency ratios reveal how well management is using the resources of the company to earn a return. These ratios are generally used to compare the management of companies within the same industry. The efficiency ratios areaccounts receivable turnover, inventory turnover and asset turnover.Solvency ratios measure the degree of protection afforded to creditors who lend the company funds on a long-term basis. It also helps in determining the ability of the company to take on more debt. The Solvency ratios are debt ratio, times interest earned, financial leverage and free cash flow.
Obtain Industry Averages:
Industry average information can be found at money.msn.com and finance.yahoo.com. Note the industry and record at the top of worksheet D-Ratio Analysis. Record the industry averages in the first column of worksheet D-Ratio Analysis. Note: Because free cash flow is not comparable among companies, no industry average is available.At money.msn.com to find industry average ratios: (1) Type I the Ticket Symbol or Company Name in the GET QUOTE box, then (2) in the left-hand column menu under Fundamentals click on Key Ratios.At finance.yahoo.com to find industry average ratios: (1) Type I the Ticket Symbol or Company Name in the GET QUOTE box, then (2) in the left-hand column menu under COMPANY click on Competitors.
You can also ask me and I will help you locate those you have not been able to find.
Summarize your results in a 1-3 page written report:15 points
What information each ratio reveals about your company, Your company’s profitability, efficiency, liquidity, and solvency, and noting whether they are improving or deteriorating. Your company’s profitability, efficiency, liquidity, and solvency, and noting whether they are better or worse than its peers.
Part F: Research Summary (2 pages)25 points
Use the format shown in worksheet 5-Research Summary to prepare a two-page summary of your analysis. This conclusion should summarize your research in a clear and concise manner. As part of this analysis:
1. Indicate whether the overall profitability, efficiency, liquidity, and solvency of the company are strong, average or weak when compared to its competitors. Briefly explain why you came to this conclusion.2. Prepare the DuPont Analysis of ROE for your company and its industry. Indicate 3. Summarize current events and other items of importance. What do financial analysts have to say about this company? About the industry?4. Indicate whether you would invest in your company, identifying three to five significant points that justify your conclusion.
Reports will be graded on comprehensive coverage, accuracy of the analysis, use of a clear and concise writing style that is easy to understand and professional appearance.
Part G: References, layout, organization, punctuation, spelling, grammar 10 points
Part 2: The Power Point presentation25 points
Purpose: Prepare and deliver a presentation titled, “Would you advise a friend to invest in this company” that is based upon research, analysis and sound reasoning.
Prepare power point slides as a visual aid using at least two graphs. The purpose of the power point is to present data in a way that the viewer can see trends of the company. Copying the financial statements onto the slide is not acceptable. The data is too small and means the viewer then has to perform the analysis. That is your job! Copying the worksheets onto the slides is unacceptable. This does not give the viewer concise information. A slide with meaningful data would have short bullets of information, not paragraphs. You should pick out several strong ratios or trends and make a slide on each of those.
Use the research summary to help you organize your presentation.Convince your peers to invest or not to invest in the company.Present the highlights of your research not a broad summary.Focus on the financial information and key ratios.The presentation should have a strong opening and closing.Make your presentation interesting.Slides should contain bullets not sentences or paragraphs.If you are presenting in class, all group members should speakBe creative-have fun!
Part 3: Group Evaluations
You must submit a group evaluations excel file. This is due October 9, midnight in the canvas dropbox. Failure to submit this file will result in a 20% point reduction of your final grade.
WORKSHEET 4: RATIO ANALYSIS Industry Company Ratios Ratios Title of Ratio Average Change Current Year Prior Year Profitability Ratios 2017 2016 Return on Sales (ROS) Stronger/Weaker 3.94 4.56 Return on Assets (ROA) 6.87 Stronger/Weaker 7.05 8.24 Return on Equity (ROE) 18.63 Stronger/Weaker 22.89 24.95 Gross profit margin 24.90 Stronger/Weaker 29.68 29.53 Efficiency Ratios Accounts receivable turnover Slower/Quicker 171.17 160.46 Inventory turnover 8.35 Slower/Quicker 5.78 5.98 Asset turnover Slower/Quicker 1.78 1.80 Liquidity Ratios Current Ratio 0.78 More/Less Liquid 0.94 1.12 Solvency Ratios Debt Ratio 0.54 More/Less Risk 1.01 0.92 Financial Leverage 2.75 More/Less Risk 3.42 3.11 Times interest earned More/Less Risk Free cash flow ($ in millions) More/Less Risk 3889.00 4406.00 Additional Data Needed for part E of paper Capital expenditures -1547 -1438 -1786 Dividends paid 2.32 2.16 1.9