2. Essentials of Excel for Modelling

Essentials of Excel for Modelling

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 by introduction essential aspects of Excel as a modelling tool. It then covers sensitivity analysis. The third part covers essential functionality and short-cuts for building models, such as creating and manipulating formulas, formatting, and so on. It also introduces functions, covering approximately 30 core functions that can be used to create conditional logic, comparisons, arithmetic, evaluate scenarios and many other uses.
  • 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

  • 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 economic evaluation. Payback periods · Time to breakeven, cumulative payback
  • 30 Excel functions and their applications. Logic, conditionality, comparison, aggregation, and lookups.

QUIZZES

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