Master the advanced Excel techniques, tricks, and features that financial analysts use to develop and audit financial models with precision. Upon completion of this course, you will be able to construct efficient and flexible formulas, create comprehensive sensitivity analyses, and leverage Excel's powerful financial capabilities.
- Get started with cash flow modeling and creating financial projections in Excel using various methodologies and techniques
- Master Excel shortcuts and advanced techniques for financial modeling, including efficient navigation, sophisticated formula writing, and working without relying on the mouse
- Learn to audit financial models thoroughly and write efficient, flexible formulas that can adapt to changing scenarios
- Develop expertise in advanced analytical tools and learn to perform comprehensive sensitivity analysis
- Master financial functions, including NPV, IRR, bond yield calculations, and mortgage payment computations
- Master advanced functions that financial analysts rely on; understand nested functions, VLOOKUP-MATCH combinations, and INDEX-MATCH techniques
This course emphasizes mastering Excel's financial features and does not focus on corporate valuation or financial accounting concepts. To explore in-depth analysis of financial modeling concepts and build comprehensive models based on real companies, see our Financial Modeling Bootcamp.
This course comprises the first day of our Financial Modeling Bootcamp program. In the Bootcamp, you will build a complete valuation model on a real public company while learning how to analyze its financial statements and create meaningful projections. The Bootcamp covers financial accounting, corporate finance, and corporate valuation concepts, and includes additional projects in LBO modeling and comprehensive three-statement modeling.
Prerequisites:
- Prior financial experience is helpful but not required.
- Excel proficiency equivalent to Intermediate Excel for Business is required, including facility with VLOOKUP, Pivot Tables, and IF statements.
Comprehensive Course Overview:
- Financial functions: NPV, IRR, XNPV, and XIRR for valuation calculations
- Advanced database functions: VLOOKUP-MATCH and INDEX-MATCH for complex lookups
- Create data tables to display outputs under various scenarios and conditions
- Use Goal Seek's powerful calculation tools to solve for unknown values
- Create financial projections based on historical data and trends
- Learn hot keys and shortcuts to work efficiently in Excel without the mouse
- Master a comprehensive set of shortcuts that finance professionals use daily
Full Course Syllabus:
Advanced Techniques to Accelerate Workflow
- Efficient formula writing and optimization
- Formula and model auditing techniques
- Advanced cell locking and protection strategies
- Hot keys for mouse-free Excel navigation
- Managing windows and working with multiple applications efficiently
- Go To Special and its advanced applications
- Paste Special techniques for different data scenarios
Project 1: Revenue Build-Up by Store Count and Same Store Sales Growth
- Learn different methodologies for projecting company revenues
- Apply advanced Excel techniques to quickly construct detailed projections
Advanced Analytical Tools and Sensitivity Analysis
- Goal Seek for finding optimal values
- Data tables for scenario comparison
- Scenario manager for complex analysis
Project 2: Cash Flow Modeling and Sensitivity Analysis
- Project revenues and expenses into the future
- Discount cash flows to present value using appropriate discount rates
- Run sensitivity analysis to test valuation based on revenue growth and gross margin assumptions
Database Functions for Finance
- Advanced SUMIFS for conditional summation
- VLOOKUP-MATCH combinations for flexible lookups
- INDEX-MATCH for advanced data retrieval
Functions for Financial Modeling
- Financial calculation functions and their applications
- Date functions for timeline calculations
- Nested IF statements for complex logic
- IF statements with AND/OR conditions
- CHOOSE function for selecting alternatives
- Weighted average calculations
- IFERROR for error handling
Project 3: Loan Amortization Model
- Model cash flows from an amortizing loan with various payment schedules
- Use advanced functions to aggregate and analyze loan data
Learn more about Advanced Excel for Financial Modeling at NYC Career Centers.
