OVERVIEW IN KEYWORDS
The following summarises the content of the course using key words. More information can be seen by clicking the “More details” buttons.I.a Modelling Structures and Calculations (I)
Uses and importance of sensitivity analysis. Sensitivity analysis in model design. Manual sensitivities. Automatic sensitivity analysis. DataTables. One-way analysis. Two-way analysis. Sensitivity analysis in time-based forecasts. Using GoalSeek. Common calculations and modelling structures. Growth-based forecasting, ratio-driven calculations, mapping historical data and forecast assumptions, logic reversals, corkscrews and reverse corkscrews.
I.b Excel Functions and Applications (I)
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.
I.c Modelling Structures and Calculations (II)
Function-based common modelling calculations and structures. Flag variables, linear and non-linear allocations, and triangles. Automated updating with new data. Waterfalls for general purpose allocations to categories, and for capital-return allocation purposes. Introduction to financial statement forecasting models; creating credible balancing.
I.d Excel Functions and Applications (II)
Lookup and reference functions. Further modelling structures calculations, and applications in time-based modelling, flags, payback analysis, data analysis, triangles, scenario analysis, scenario decks, and variance analysis. Array functions and dynamic arrays. Dynamic output ranges. Bespoke calculations in finance, data analysis and statistics.