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.