Financial Training from Adkins Matchett & Toy
Excel Power Features for Finance1 dayClassroom
Intermediate/Advanced
Intermediate/Advanced
Participants apply advanced Excel features to build models for more complex financial analysis.
Topics include advanced functions like OFFSET, VLOOKUP, HLOOKUP, MAX, IF, SUMPRODUCT, date math, data sorting, pivot tables, SUMIP, COUNTIF, and arrays. Advanced presentation tools include using data tables, graphs, bar charts, using comments effectively, creating user-defined styles, and automating your model with macros.
Excel Functions
- Using the OFFSET function to pick up information from a model and document a model with scenarios
- Using VLOOKUP and HLOOKUP in data analysis and multiples models
- Using MAX functions to slim down IF statement formulae
- Nested IF statements and IF statements with logical operators
- Using SUMPRODUCT for calendarization in multiples models
- Date functions and date math in Excel
- Using text functions to manipulate data downloads
- Databases and data sorting
- Pivot tables
- SUMIF, COUNTIF and D functions
- Arrays, conditional arrays and the INDIRECT and TRANSPOSE functions
Presentation Tools in Excel
- Building data tables and solving data table problems such as using data tables across sheets and calculation issues
- How to "plutonium burst" your model’s calculation
- Using graphs to present data. How to create a "floating bar chart" for valuation presentations. How to save your graph formatting to help maintain consistency.
- How to print and set print ranges effectively. Using macros to print an executive summary.
- Printing and using headers & footers
- Using concatenation to automate your labels
- Using comments to document your model. Printing comments on the spreadsheet and "moving" them so they don’t cover used cells
- Using Excel's grouping feature to set up a consistent look and format to your model
- Creating user-defined styles to maintain and change formatting in a model
- Using conditional formatting features
Advanced Features
- Using macros to automate your model
- Using action buttons for model options
Extreme Proofreading Techniques
- Error checking a model; showing and printing formulae, referencing constants, searching for links and error messages
- Solving problems with circular references and iterations
- Using "jump" to check formulae
- The upside and downside of using named ranges in models
You can call Matchett on +44 (0)1295 256161 to book a course or simply discuss your training needs OR email us:
Related Courses
Make your financial models really sing!
Pre-requisites
Excel financial modelling skills
Delegate Profile
Anyone who wants to apply advanced functions to financial models for efficiency and more complex analysis