- Develop a good knowledge of many Excel functions, as required for general purpose applications in modelling and data analysis.
- Understand the need for functions, the nature of function inputs and outputs, and the similarities and differences with arithmetic operations.
- Gain an intermediate level knowledge of Excel.
- Achieve the proficiency required to take the courses in the Level II (Advanced) Program.
- Earn the Certificate in Financial Modelling and Data Analysis Level I (Intermediate)
- Overview. This course covers the applications of a wide range of Excel functions that are commonly required in general applications of modelling and data analysis. The course first explains why functions are necessary, and then covers Information functions, Logical functions, functions for summation, aggregation, and ordering. It covers the use of conditional functions and conditional aggregations, as well as array functions, dynamic array functions and Lookup functions. The course provides many examples of the use of the functions to practical applications in both modelling and data analysis. The course also describes important modelling structures and calculations that require functions to be used, including flags, waterfalls, allocation methods, and triangles, as well as scenario analysis, variance analysis and introduces topics related to advanced sensitivity analysis.
- 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 applications of modelling and data analysis are covered in later courses, especially in the Level II courses “II.1 Economics and Financial Evaluation” and “III.1 Data Analysis, Manipulation and Statistics”).
- Function basics. Limitations of arithmetic operations and the need for functions. Entering functions into a cell.
- Information functions. Determining the nature of cell content. Distinguishing numbers, text, logical items, blanks and errors.
- Summation, aggregation and ordering. Commonalities and differences between functions and arithmetic. Sums, counts, products, powers, maxima, and ordered values.
- Logical functions. Functions to implement logic, conditionality, and comparisons. Exclusive and chained conditionality. Distinguishing logical items from text. Use of logical items as inputs to functions and formulas.
- Conditional aggregations. Conditional summation and related items. Selected statistical and mathematical functions.
- Array functions and dynamic arrays. Dynamic output ranges. Bespoke statistical calculations.
- Lookup and reference functions. Applications to time-based modelling, basic data analysis and scenario analysis.
- Function-based common modelling calculations and structures. Flag variables, waterfalls, linear and non-linear allocations, and triangles.
- Introduction to advanced sensitivity analysis using functions. Scenario analysis, variance analysis.