Financial analysis is essential for making informed business decisions, and Excel is one of the most powerful tools to help us do this efficiently. With a few key techniques, we can use Excel to convert raw data into actionable insights your colleagues might actually appreciate.

How to Do Financial Analysis in Excel

As with any, learning the basics, or at least brushing up on them, can save you time and prevent silly mistakes from squandering your precious time. Read on for a crash course on the fundamental concepts behind financial analysis. And for anyone entering the field, all these formulas and ratios should be facile to the point of boredom before you begin expanding your skills. Even though you can leverage AI for financial analysis in excel you still might find yourself hampered by weak skills with the basics.

Step 1: Gather financial information & fundamental data

Trash in, trash out. Any good analysis hinges on accurate data, preferably delivered faster than your competition. 

Before diving into the advanced techniques, it’s essential to set the stage by organizing your data effectively. This involves collecting and consolidating financial statements, historical data, and any relevant information into a structured Excel workbook. Create separate sheets for different datasets such as the income statement, balance sheet, and cash flow statement to ensure clarity and ease of access.

  • Collect Data: Obtain the necessary financial statements, including the income statement, balance sheet, and cash flow statement. Ensure you have data for multiple periods (e.g., past three to five years). You can also leverage a variety of excel add-ins or our dynamic financial data in order to streamline this process and lay the groundwork for dynamic models‍. 
  • Sources: Gather data from reliable sources such as company reports, financial databases, and regulatory filings. Verify the accuracy of the data by cross-referencing multiple sources if possible. You can also leverage a variety of excel add-ins for financial data that connect to paid and/or proprietary data sources that could be useful. 

Step 2:  Normalize & organize your data and financial statements

After getting the right data for your model, the next essential is ensuring that all your data types are exactly what you think they are. Issues here can lead to time wasted late on in your work. 

  • Create Separate Sheets: Set up different sheets in Excel for the income statement, balance sheet, and cash flow statement. This helps maintain clarity and organization.
  • Input Data: Enter the collected financial data into the respective sheets. Ensure accuracy and consistency in data entry by double-checking figures and using consistent units (e.g., thousands or millions).
  • Standardize Format: Use a consistent format for dates, numbers, and text to maintain clarity. Use proper cell formatting to distinguish between different types of data (e.g., currency, percentages).
  • Leverage  Macros and VBA (Visual Basic for Applications) – Macros and VBA automate repetitive tasks and complex calculations, enhancing efficiency. Record macros for simple tasks and write VBA scripts for more complex automation.

Step 3: Keep data auditable and granular

Ensuring traceability helps maintain transparency in your analysis, making it easier to audit and verify the calculations. This is particularly important in collaborative environments where multiple stakeholders review the analysis.

  • Use Cell References: Instead of hardcoding numbers, use cell references to link related data. This allows for automatic updates and ensures data integrity. For example, link the total revenue in your summary sheet to the revenue figure in your income statement sheet.
  • Trace Precedents and Dependents: Use Excel’s trace precedents and dependents features to track how data flows through your formulas. This helps in understanding and troubleshooting complex models.

While it may feel minor, clearly attributable data is what drives compelling and defensible conclusions.

Step 4: Calculate key financial ratios & compare them to targets and industry benchmarks

While the “math” is fundamental to conducting any financial analysis, it is truly just one piece of the overall puzzle that coalesces into a strategic perspective. The brass tacks of any financial analysis should already be well within your skill set but if you need to hone your skills before joining a new role or brush up the resources below can help:

  • Commonly Used Excel Formulas: While integrations, VBA, and ease of use make excel a powerful tool, the basic formulas are still essential knowledge. Depending on your background you may already be comfortable with these formulas. But for anyone starting out, or brushing up, memorizing these can help you dissect models, and develop your own tailored models more efficiently. 
  • Commonly Used Ratios: After using industry reports, financial databases, and competitor analysis to gather benchmark data most theses rely on some core relative ratios. By comparing these ratios to industry standards and target performance teams can quickly understand and project company performance. An intimate understanding of these fundamentals will serve you throughout your career. Most, but not all models will rely on a core set of financial ratios.
  • Common Analysis & Valuation Techniques in Financial Models: At the heart of financial models are the actual valuation and analysis techniques that tend to be the destination for all the data we gather. Six of the most common types are listed here and should all be familiar to you. 
  • There’s no reasonable way to cover all the potential applications of the above skills but if you’d like some samples of how all these various techniques can be applied in action you can review the resources below:
    • Watch how data sheets on DKNG can help an analyst discover KPIs and spark novel ideas.
    • Watch two analysts leverage the same platform to either quickly gain insights on Amazon’s Q1 ‘24 earnings or generate a deep peer analysis in just a few minutes.
    • Learn how any analyst can dominate earning seasons and never miss a Friday happy hour again with data feeds that update their models in seconds. 

Step 5: Spot the main drivers of variation 

In a world of mediocre models, understanding the true drivers of variance and developing models that relay non-consensus conclusions accurately and with great data is a real differentiator. As we’ve covered previously when discussing how to build great financial models , models should be oriented towards the true drivers of any given business. A great analyst focuses their efforts on what truly matters since our ultimate “goal is to derive a non-consensus outcome for the security, and to be right!”

Developing models that capture all the tough granularity relays thoroughness and care, but this often leads to highlighting the right drivers. Training your mind to identify and analyze the main drivers of variances against the previous year’s data, and  understanding these drivers provides valuable insights into which areas of the business are performing well and which need improvement. Use this analysis to inform better planning and forecasting decisions.

Step 6:  Have supporting sheets & commentary ready for a fully data-driven storytelling approach

Supporting sheets provide the necessary detail to back up your analysis and conclusions. They help create a narrative that explains how the numbers were derived and what they mean for the business. In short you should always be ready to defend your work. 

  • Detailed Calculations: Maintain sheets with detailed calculations that support your analysis. This includes breakdowns of revenues, expenses, and other key financial items.
  • Assumptions and Inputs: Create a sheet to document all assumptions and inputs used in your analysis. This ensures transparency and makes it easier to update the model as new information becomes available.
  • Scenario Analysis: Develop scenarios (e.g., best case, worst case) to demonstrate how different assumptions impact the results. Use these scenarios to provide a range of possible outcomes and their implications.

Step 7:  Visualize conclusions 

Visualization helps in effectively communicating your findings to stakeholders. Making complex data more accessible and facilitating better decision-making in less time.

  • Pivot tables are your new best friend
    ‍
    • Pivot Tables are powerful tools for data exploration and analysis and you’ll be using them daily. They enable you to drill down into the details and extract meaningful insights from complex datasets.
      • Create Pivot Tables: Use Pivot Tables to summarize and analyze large datasets efficiently. Pivot Tables allow you to quickly reorganize and explore your data.
      • Group Data: Group data by various dimensions such as time periods, departments, or product lines to uncover insights. This helps in identifying trends and patterns that may not be immediately apparent.
      • Calculated Fields: Add calculated fields within Pivot Tables for additional metrics. This allows for more sophisticated analysis and reporting.
  • Charts and graphs: Use Excel’s charting tools to create visual representations of your data. Visuals make it easier to understand and communicate the results of your analysis.
    ‍
    • The classics still work:
      • Line charts: Track changes over time for metrics like revenue and net income. Line charts are useful for showing trends and patterns.
      • Bar charts: Compare different categories or segments. Bar charts are effective for highlighting differences between groups.
      • Pie charts: Show proportions of different expense categories. Pie charts provide a clear visual of how different components contribute to the whole.
        ‍
    • Waterfall charts: Illustrate the cumulative effect of sequential positive and negative values. Waterfall charts are useful for showing how different factors impact a particular metric.
      ‍
    • Bridge charts, similar to Waterfall Charts, are used to present changes in financials over time, showing how each component affects the total change.
      ‍
    • Heat maps visualize data to show the magnitude of values, using color gradients to represent different ranges, making it easy to identify patterns and outliers.
      ‍
    • Tornado charts Tornado Charts visualize the sensitivity of an outcome to changes in input variables. They help identify which variables have the most significant impact on the model.
  • Conditional Formatting: Apply conditional formatting to highlight key data points and trends. Use color coding to make important information stand out.
  • Dashboards & Reporting: Develop a dashboard to present key metrics and visualizations in a single view. Dashboards provide a high-level overview of the company’s financial performance.

Wondering how to do financial analysis in Excel faster?

Daloopa can help you streamline your current approach with our AI. Mastering Excel is essential for evaluating a company’s performance, creating financial forecasts, or building valuation models, as it can transform complex data sets into clear and understandable outputs. However, it is important to become proficient with the fundamental formulas and ratios to the point of ease before tackling serious modeling work. Even with the aid of AI for financial analysis, struggling with the basics can hinder your analysis.