- Cover the core features, operations and functions required when working with Excel for modelling applications.
- Gain practical experience in building and using simple models, using the functionality and functions of Excel.
- Set the foundation knowledge required in future courses.
- Overview. This course covers key aspects of Excel that are necessary to build models in Excel. It provides an introduction to modelling and its use in decision-support tool, covers the essential aspects of model planning and design. It covers the use Excel as a modelling tool, including essential functionality, short-cuts, functions and the use of sensitivity analysis. It covers a wide range of Excel functions and the core aspects of model structures and best practices.
- Practical work, quizzes and exercises. There are 10 Quizzes which test the key concepts and require one to do practical exercises in Excel.
- 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. Logic, conditionality, comparison, and aggregation. Introduction to lookup, text, information and other functions.
- Best practices. Principles for design, layout, structure, and formatting. Choices of structure. Database approaches. Principles of transparency. Dealing with circularities. Complexity reduction and optimisation.
- Modelling structures. Corkscrews, waterfalls, and triangles. Linear and non-linear allocations.
- Model planning. Addressing the right decision. Context, objectives. Strategy analysis. Decision criteria. Decision support, criteria, and challenges. Determining model variables. Flow and granularity. Logic reversals. Historical versus forecasted analysis. Updating requirements. Horizontal or vertical time axis. Data sources and structures. Structural limitations versus parameter flexibility. Benefits of modularity. Approaches to link modules. Decision sequences.
- Sensitivity analysis. Uses and importance. Manual sensitivities. Automatic sensitivity analysis. DataTables. One-way analysis. Two-way analysis. Using GoalSeek. Sensitivity analysis in model design.
- Scenario modelling. Approaches, functions and calculations. Introduction to optimisation analysis.
- Introduction to risk and uncertainty analysis. Risk as reality. Purpose and Benefits. Risk management. Modelling approaches and techniques. Risk identification and mapping. Applications in business and finance. Introduction to Monte Carlo Simulation.
- 10 Quizzes. Creating simple models, running sensitivity analysis, using Excel operations and functions, and best practices.