📊

Excel Tips for Financial Modeling

Sep 21, 2024

Financial Modeling Session 2 Notes

Introduction

  • Welcome to the second session of financial modeling.
  • Recommended to watch the first session before proceeding.
  • Focus on Excel functionalities and settings crucial for financial modeling.

Session Structure

  1. Excel Settings: Understand default settings to optimize Excel for financial modeling.
  2. Excel Functions: Learn functions that will be used throughout the modeling process.

Key Concepts

  • Mastery of Excel is critical due to the large amounts of data involved in financial modeling.
  • Practice is essential; simply watching the session is not enough.
  • Hands-on exercises will be provided for better learning.

Excel Settings

  • **Default Settings to Change:
    **
    • Set default font to Calibri, size 11.
    • Set default view to normal view.
    • Limit the number of sheets in new workbooks (set to 1).
  • Formula Settings:
    • Automatic calculation is recommended unless working with data tables.
    • Disable iterative calculation (only enable when necessary to avoid circular references).
    • R1C1 referencing style should be disabled.
  • General Settings:
    • Save workbook every 1 minute for data security.
    • Consider disabling the start screen for faster access to blank workbooks.

Basic Excel Functionalities

  • Reduce mouse usage; utilize keyboard shortcuts for efficiency.
  • Example shortcuts for navigation and formatting:
    • Alt + F for file options.
    • Ctrl + Space to select a column.
    • Shift + Space to select a row.
  • Use Alt + H for home functions and formatting commands.

Setting Up a Financial Model

  1. Create margins:
    • Use a narrower first column for a margin.
  2. Header Formatting:
    • Currency at the top, followed by years for the financial statements.
  3. Formatting Years:
    • Hardcode the first year and use a formula for subsequent years (e.g., =A1 + 1).
    • Show actual years separately from forecast years (e.g., "A" for actual, "E" for estimated).
  4. Column Widths:
    • Use uniform widths for consistency (e.g., set to 10 or 12).
  5. Freeze Panes:
    • Freeze the header row for visibility when scrolling.

Basic Financial Modeling Structure

  • Create headers for the income statement (e.g., Revenue, COGS, Gross Profit).
  • Understand the importance of consistent formatting:
    • Use black for formulas, blue for hardcoded numbers.
  • Implement grouping for income statement components for clarity.

Functions in Financial Modeling

  • Basic Functions:
    • Use SUM, AVERAGE, MIN, MAX, IF, etc., for calculations.
    • Understand how to apply conditional formatting (e.g., highlighting values based on conditions).
  • Advanced Functions:
    • Utilize EOMONTH, YEARFRAC, DATE for date calculations.
    • Understand the importance of absolute and relative references in formulae.
    • Implement SUMPRODUCT for weighted averages.

Conclusion and Next Steps

  • Practice exercises provided in the description for hands-on experience.
  • Encourage consistent practice and attention to detail in Excel modeling.
  • Connect on LinkedIn for further questions or assistance.
  • Subscribe for future sessions and further learning.