Learn the tricks and techniques in Excel needed for financial analysts to build financial models, create sensitivity analyses, and more.
Master the advanced Excel tricks, techniques, and tools financial analysts use to build and audit financial models. By the end of this course, you will know how to write efficient and flexible formulas, create sensitivity analysis, and master Excel's features for finance.
- Get started with cash flow modeling and projections in Excel
- Master Excel shortcuts and techniques for financial modeling, including navigation, formula writing, and working without the mouse
- Audit financial models and write efficient, flexible formulas
- Learn advanced analytical tools for sensitivity analysis
- Learn financial functions for NPV, IRR, Bond Yields, & Mortgage Payments
- Conquer advanced functions financial analysts need: Nested functions, VLOOKUP-MATCH, INDEX-MATCH
Prerequisite:
- Prior financial experience is helpful, but not required.
- Excel proficiency equivalent to Intermediate Excel for Business is required, including VLOOKUP, Pivot Tables, and IF statements.
What You’ll Learn at a Glance
- Financial functions: NPV, IRR, XNPV, and XIRR
- Advanced database functions: VLOOKUP-MATCH, & INDEX-MATCH
- Create Data Tables to show outputs based on various scenarios
- Use Goal Seek's powerful calculation tools
- Create financial projections based on historical data
- Learn Hot Keys to work in Excel without the mouse
- Master Excel with a variety of shortcuts finance professionals need
Course Syllabus
- Advanced techniques to Expedite Workflow
- Efficient formula writing
- Formula & model auditing
- Advanced cell locking
- Hot Keys to work without the mouse
- Windows & work with multiple applications
- Go To Special
- Paste Special
- Project #1: Revenue build-up by store count and same store sales growth
- Learn different methods for projecting company revenues
- Apply advanced Excel techniques to quickly build out projections
- Advanced Analytical Tools & Sensitivity Analysis
- Goal Seek
- Data Tables
- Scenario Manager
- Project #2: Cash flow modeling & sensitivity analysis
- Project revenues and expenses
- Discount cash flows to the present value
- Run a sensitivity analysis for valuation based on revenue growth and gross margin
- Database Functions for Finance
- Advanced SUMIFS
- VLOOKUP-MATCH
- INDEX-MATCH
- Functions for Financial Modeling
- Financial functions
- Date functions
- Nested IF statements
- IF Statements with AND/OR
- CHOOSE function
- Weighted average calculations
- IFERROR
- Projects #3: Loan amortization model
- Model cash flows from an amortizing loan
- Use advanced functions to aggregate the data
Learn more about Advanced Excel for Financial Modeling at NYIM Training.
