Day 3 - Power Modeling in Excel

Participants are introduced to a variety of techniques and functions in Excel that enable quick and easy scenario and sensitivity analysis in financial models. They will be shown how to overcome some of the more frustrating issues that arise from data downloads such as number formats not being recognized and taught how to automate the extraction of useful information from strings of text and numbers. Further topics include creating charts, printing, and an introduction to macros. Finally, participants apply the techniques to an existing fully integrated model.

Key topics:

• Creating flexible output tables
  • VLOOKUP
  • HLOOKUP
  • INDEX
  • OFFSET
  • MATCH
  • INDIRECT
• Building flexible scenarios
• CHOOSE
• Conditional formatting
• Data validation
• Buttons and Switches
  • Using text functions for data extraction and dynamic headers
  • LEFT
  • RIGHT
  • FIND
  • LEN
  • MID
  • VALUE
  • TEXT
  • Concatenation
• Sensitivity analysis
  • 1, 2 and 3 input datatables
• Building basic Macros