Essentials of Modelling with Excel

Essentials of Modelling with Excel

OBJECTIVES

  • 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.

DESCRIPTION

  • Overview. This course covers key aspects of Excel that are necessary to build simple models in Excel. It starts with an introduction to modelling and the essential aspects of Excel as a modelling tool, including the use of sensitivity analysis. The essential functionality of Excel and short-cuts for building models are covered, including the creation and manipulation of formulas, formatting, and so on. It also introduces functions, covering core functions required to create conditional logic, comparisons, and aggregation calculations for modelling and data analysis.
  • Practical work, quizzes and exercises. There are 8 Quizzes which test the key concepts and require one to do practical exercises in Excel.

KEY TOPICS

  • Definition of modelling. Meaning and uses. Influence diagrams. Simple examples.
  • Excel as a modelling tool · Creating formulas · Manual and automatic calculation · Mapping a model concept into calculations · Dependency and precedents tracing · Influence diagrams.
  • Sensitivity and scenario analysis · DataTables · One-way analysis · Two-way analysis · Scenario modelling· GoalSeek.
  • Operations and short-cuts. Copy, paste, insertion, deletion, undo · Find/Replace · Wildcard characters · Named ranges · GoTo and GoTo Special · Efficient selection of ranges · Navigation around a model. Tips and tricks · Short-cuts and KeyTips.
  • Formatting. Formatting · Conditional formatting · Custom formatting
  • Graphs and charts. Principles of effective visual communication · Using graphs and charts.
  • Introduction to Excel functions and their applications. Logic, conditionality, comparison, and aggregation.
  • Core principle of model planning. Decision criteria. Level of detail. Key sensitivities. Generic model types. Links between modelling and data analysis.
  • Introduction to economic evaluation. Breakeven, time to breakeven, cumulative breakeven, discounted breakeven · Net present value (NPV). Internal rate of return (IRR).
  • Summary of the skills required to be a well-rounded financial modeller and analyst.

QUIZZES

  • Creating simple models, running sensitivity analysis, Excel operations and functions.