Financial Analysis Project: Phased Submission with Excel Dashboard
Introduction
Welcome to the Financial Analysis Project! This project is designed to help you apply real-
world knowledge by analyzing a publicly listed company’s financial performance over the
past five years. Working in pairs, you will explore the company’s financial reports, calculate
key metrics, and develop a professional Excel dashboard to consolidate your findings. The
project will be completed in five phases, each focusing on a specific aspect of financial
analysis.
Project Objectives
- Develop the ability to read and interpret annual reports and financial statements.
- Apply concepts learned in financial management, accounting, and capital budgeting.
- Use Excel to create a professional, interactive dashboard to visualize and present data.
- Gain insights into real-world financial decision-making and reporting practices.
Project Requirements
Teamwork
- The project will be completed in pairs.
- Both members are expected to contribute equally and collaborate effectively.
Phased Submissions and Presentations
- The project is divided into five phases, each aligning with a module.
- Submissions and presentations will occur at the end of each phase.
Excel Dashboard
- Create an Excel dashboard that consolidates data and analysis across all phases.
- The dashboard must include:
- Key financial metrics and ratios.
- Graphs and charts to visualize trends.
- Interactive elements (e.g., filters for different years or categories).
Comparative Analysis
- Students must compare the calculated financial ratios with industry averages to evaluate
the company’s performance.
- Perform inter-company analysis by selecting at least one competitor and comparing
financial performance metrics, trends, and insights.
Phases and Deliverables
Phase 1: Company Overview and Balance Sheet Analysis
Deliverables:
1. Company Background:
- History, industry, products/services, stock ticker, and current stock price.
2. Balance Sheet Analysis (5 Years):
- Trends in assets, liabilities, and equity, supported by references to the Notes to the
Financial Statements.
- Key ratios: Current ratio and debt-to-asset ratio.
- Compare these ratios with industry averages and a competitor.
3. Excel Dashboard Contribution:
- A company snapshot section.
- Balance sheet trends visualized through charts.
- Key ratios in a dynamic table.
Phase 2: Income Statement Analysis
1. Income Statement Overview:
- Format (single-step or multi-step) and its components.
2. Profitability Trends (5 Years):
- Ratios: Gross profit margin, operating margin, return on assets.
- Use notes to explain unusual items or significant changes.
- Compare these ratios with industry averages and a competitor.
3. Excel Dashboard Contribution:
- Revenue and expense trends with visualizations.
- Profitability ratio tracker.
Phase 3: Statement of Cash Flows
1. Cash Flow Breakdown (5 Years):
- Trends in operating, investing, and financing cash flows.
- Reference notes for major changes.
2. Free Cash Flow Analysis:
- Calculation and implications for sustainability.
- Compare cash flow trends and free cash flow with a competitor.
3. Excel Dashboard Contribution:
- Cash flow trends visualized through charts.
- Free cash flow tracker.
Phase 4: Stockholders’ Equity and Investment Analysis
1. Equity Trends (5 Years):
- Analysis of changes in stockholders’ equity (e.g., share buybacks, dividends).
2. Investment Ratios:
- Ratios: Dividend payout ratio, dividend yield, price-to-earnings (P/E) ratio.
- Compare these ratios with industry averages and a competitor.
3. Investment Recommendation:
- A buy/sell/hold recommendation with reasoning, incorporating comparative analysis.
4. Excel Dashboard Contribution:
- Equity trends visualized through charts.
- Investment ratios with interactive features.
Phase 5: International Financial Reporting Standards (IFRS)
1. IFRS Overview:
- Basics of IFRS and comparison to U.S. GAAP.
2. IFRS Impact Assessment:
- How IFRS adoption would affect the company’s financial statements, referencing notes.
3. Competitor Comparison:
- Differences between the chosen company and a competitor using IFRS.
4. Excel Dashboard Contribution:
- Summary of IFRS impacts.
- Comparative analysis section.
Final Deliverables
1. Excel Dashboard:
- A polished, interactive dashboard consolidating all analysis and visualizations.
2. Comprehensive Report:
- Summarizing findings from all phases with references to the annual reports and notes.
- Include comparative analysis with industry averages and a competitor.
3. Final Presentation:
- Synthesizing the five phases into a cohesive story about the company’s financial
trajectory and investment potential.
Evaluation Criteria
1. Depth of Analysis:
- How well the financial statements and notes were interpreted.
2. Comparative Analysis:
- Effectiveness of comparisons with industry averages and a competitor.
3. Accuracy:
- Correctness of calculations and insights.
4. Dashboard Quality:
- Functionality, design, and use of visualizations.
5. Collaboration:
- Evidence of teamwork and shared contributions.
6. Presentation:
- Clarity, professionalism, and effective use of the dashboard.
Important Notes
- Use only publicly available annual reports and financial data.
- Cite all sources and references used.
- Ask questions and seek clarification whenever needed.
Good luck, and enjoy the process of exploring the world of financial analysis!
