- Learn the meaning of modelling and its core uses.
- Master the core features, operations and functions of Excel required for simple and common applications.
- Gain practical experience in building and using models.
- Run sensitivity analysis and scenario analysis.
- Develop a good knowledge of many Excel functions.
- At the end of this course, a student will have an intermediate level knowledge of modelling and Excel, and have achieved the level necessary to take the courses in the Level II (Advanced) Program.
- Overview. This course introduces modelling, its definition, key process steps, and its uses. It describes the key principles of using Excel as a modelling platform. It covers the main functionalities and short-cuts in Excel that are necessary to build models and conduct data analysis. It covers the applications of a wide range of the most essential Excel functions, including Logic functions, Information functions, Lookup functions, and array functions, amongst others. The topics of sensitivity analysis and scenario analysis are discussed in detail. The course also covers the common types of calculations and structures used in modelling, such as forecasting using growth rates and ratio approaches, corkscrew structures, flags, waterfalls and allocation methods, amongst others.
- Practical work, exercises, and quizzes. There are 10 Quizzes which test the key concepts and require one to do practical exercises in Excel, including for general forecasting using growth and ratios, breakeven analysis, sensitivity analysis, scenario analysis, and more!
- Introduction. The definition of modelling. Purpose, uses, application areas. The skills required. Links between modelling and data analysis.
- Excel modelling foundations. Workbook and grid structures. Influence diagrams. Mapping a model concept into calculations. Creating formulas. Dependency and precedents tracing. Manual and automatic calculation.
- Excel operations. Copy, paste, insert, delete, undo. Find/Replace. Wildcard characters. Named ranges. GoTo and GoTo Special. Formatting. Conditional formatting. Custom formatting. Efficient selection of ranges. Navigation around a model. Short-cuts and KeyTips.
- Graphs. Principles of effective visual communication. Using graphs and charts in Excel.
- Excel functions. Functions to implement logic, conditionality, and comparisons. Aggregations and conditional aggregations. Lookup, Reference, and Information functions. Array functions. Selected statistical and mathematical functions.
- Common modelling calculations and structures. Growth and ratio calculations, historical data and forecast assumptions, corkscrews, reverse corkscrews, waterfalls, and triangles. Linear and non-linear allocations.
- Sensitivity and scenario analysis. Uses and importance. Manual sensitivities. Automatic sensitivity analysis. DataTables. One-way analysis. Two-way analysis. Using GoalSeek. Sensitivity analysis in model design.