Data Training from Zomalex

Home | Power BI | Excel | Python | SQL | Visualising Data | Generative AI

Building Financial Models in Excel Course

Who should attend

Excel users who want to build financial models in Excel, especially where those models have a what-if aspect.

Course Length

2 days

Learning Objectives

You will learn how to structure an Excel model and in particular techniques such as scenario modelling (best/base/worst case estimates) and sensitivity analysis.

Pre-requisites

Completion of the Excel foundation and intermediate courses.

Course Content

Introduction (Presentation/Discussion)

What is a spreadsheet model? Different sorts of model: e.g. scenario model, sensitivity analysis

Recap on Excel techniques and Functions Useful for Financial Models

This covers Excel techniques and functions useful for building financial models:

• relative and absolute cell addressing;
• the SUMIFS() and COUNTIFS() functions group and aggregate data;
• the IFS() function avoids nested IF() formulas;
• the XLOOKUP() function is a simpler more flexible robust alternative to VLOOKUP();
• functions that spill and their advantages, and
• functions for discounted cash flow and NPV analysis.

Introduction to Data Tables (Lab Exercise)

Data tables are a very useful technique in building models especially for scenario modelling. In the lab we build a few models: specify the assumptions, build the model template then use data tables to generate a set of results for different scenarios.

Scenario Models and Sensitivity Analysis (Lab Exercise)

Scenario Models answer what-if questions as well as the expected case, what is the possible upside and how bad could things get? Sensitivity Analysis helps us understand the spread of range of values of key results based on a set of possible values of the variables in our assumptions of our model. In the lab exercise, we build a simple financial model, the income statement of a fictitious company.