OBJECTIVES
- Develop a good knowledge of a range of Excel functions that are especially important to build flexible and powerful models.
- Gain an intermediate level knowledge of Excel functions.
DESCRIPTION
- Overview. This course covers the use of more advanced functions in Excel, especially those which are needed to increase the flexibility of models. These include array calculations, Lookup functions and the use of dynamic output arrays. The course provides examples of the use of the functions to practical applications in both modelling and data analysis. The course also describes some important modelling structures and calculations that require these functions to be used, including using flags, scenario and variance analysis,time-based scenario decks, and data integration.
- Practical work and exercises. Students are required to conduct numerous hands-on modelling exercises and can also follow along by replicating other examples that are shown in the text.
- Assessment tests. There are several tests which cover both the key concepts and require one to do practical exercises in Excel.
(Note that many functions that relate to more specific areas, such as financial calculations and advanced data analysis are covered in Level III and Level IV).
KEY TOPICS
- Lookup and reference functions. Applications to time-based modelling, basic data analysis and scenario analysis.
- Function-based common modelling calculations and structures. Flags, payback analysis, data analysis, triangles, scenario analysis, time-based scenario decks, variance analysis.
- Dynamic arrays. Dynamic output ranges. Introduction, meaning, syntax and uses.
- User-created array functions. Bespoke calculations in finance, data analysis and statistics.
