Day 3 - Excel Power Modeling

This program covers a range of tools and techniques in Excel designed to improve effectiveness and efficiency. The program covers building scenarios into models using a variety of functions, together with adding in on-sheet controls, sensitivity analyses and flexible output tables. The topics in this menu can be chosen according to participants’ needs.

Key topics:

• Modeling with speed and style (1 - 2 hours 2003 stds)
  • Setting up toolbars so that Excel can be used as efficiently as possible
  • Setting up formatting styles
  • Custom number formats
• Keeping circularities under control (1 - 2 hours 2003 stds)
  • Working with intentional circular references
  • Debugging a model with non-intentional circular references
  • Avoiding intentional circular references with a circular reference macro
• Flexible output tables (1 - 2 hours 2003 stds)
  • Use functions to extract information from a model
  • VLOOKUP
  • HLOOKUP
  • OFFSET
  • INDEX
  • MATCH
  • Data validation
  • INDIRECT
• Scenarios (1 - 2 hours 2003 stds)
  • Building scenarios into the model
  • CHOOSE
  • OFFSET
  • INDEX
  • MATCH
  • On sheet controls
  • Conditional Formatting
• Working with Text and Dates (1 - 2 hours 2003 stds)
  • Creating dynamic headings and footnotes in the model
  • Working with dates
  • Concatenation
  • TEXT function
  • UPPER, LOWER, PROPER functions
  • TRIM and CLEAN functions
  • RIGHT, LEFT & MID functions
  • DATE, DAY, MONTH, YEAR functions
• Sensitivity analysis (1 - 2 hours 2003 stds)
  • Run sensitivities on model outputs
  • One-input data tables
  • Two-input data tables
  • Three-input data tables
  • Data table secrets
• Lists (1 - 2 hours 2003 stds)
  • Creating lists
  • Sorting
  • Basic and advanced filtering
  • SUMIF and COUNTIF
  • Calculate with advanced criteria with database functions
  • Pivot tables
• Other functionality (1 - 2 hours 2003 stds)
  • Printing with views
  • Print macro
  • Print macro with a form for easy print selection
  • User defined functions
  • Beauty save macro
  • Protecting your work
  • Charts
  • Arrays
  • Range names
  • Shortcuts