We run several Excel courses.
- Foundation Excel
- Intermediate Excel
- Advanced Excel
- Modern Excel
- Building Financial Models in Excel
Here are the details of these courses.
Foundation Excel
Who should attend: This is a foundation course in Excel suitable for beginners and for people who have been using Excel for a while but want to refresh their understanding of the basics of Excel.
Learning Objectives: You will learn how to edit and view an Excel worksheet: navigate the sheets, learning common functions and how to write basic formulas.
Outline:
- Getting Started. Excel User Interface and Layout: Entering Data. Data validation
- Formulas. Relative and fixed cell references. Named ranges.
- Functions. Whirlwind tour of some of the most useful Excel functions.
- Introduction to Excel tables: how to create them and why they are very useful.
- Presenting data in tables. Conditional formatting (data bars, icons, sparklines).
Intermediate Excel
Who should attend: People who have attended “Foundation Excel ” course and want to improve their Excel skills.
Learning Objectives: You will learn about Excel tables, how to use Excel for analysis and presentation of data and learn common techniques that summarise data including pivot tables and spill functions.
Outline:
- XLOOKUP – a simpler and more flexible alternative to VLOOKUP.
- Excel tables in detail.
- Summarising data with pivot tables.
- Introduction to spill functions – what they are and why they are very useful.
- Summarising data with formulas and functions such as SUMIFS
Advanced Excel
Who should attend: People who have attended “Intermediate Excel ” course and want to improve their Excel skills to an advanced level.
Learning Objectives: You will learn several advanced Excel techniques and capabilities and how to use these effectively.
Outline:
- Spill Functions in detail: what they are, how to use them and look at situations where they are useful. These include the FILTER, SEQUENCE, UNIQUE, SORT and TRANSPOSE functions.
- Use spill functions to split, combine and extract text: the TEXTSPLIT, TEXTBEFORE, TEXTAFTER, TEXTJOIN, ARRAYTOTEXT functions.
- Use functions to reshape arrays: the VSTACK and HSTACK functions.
- Data tables – how to create them, why they are very useful especially in financial models.
Modern Excel
Who should attend: People who are familiar with Excel users – this courses introduces new features in the most recent versions of Excel that can make their spreadsheets simpler and better.
Learning Objectives: Over the last few years, Excel has added many new capabilities. These include new functions such as dynamic arrays and spill functions that help us build better and more robust spreadsheets and models. This course demonstrates and explains many of these new capabilities, always with a practical perspective on how we can take advantage of them.
Outline: Dynamic Arrays underpin many of the new capabilities. We start with a brief look at the essentials: for example, even ordinary old-school functions now work with arrays and produce multiple results rather than a single value under the right conditions. Dynamic arrays allow us often to write simpler formulas, for example, to avoid mixed references in “grid” formulas.
Spill functions return not just a single result in the cell where the formula is written but the results spill over into surrounding cells. We start with a few examples of spill functions, UNIQUE(), SORT() and TRANSPOSE() then focus on a very useful spill function FILTER(). This can filter a table or range and return a set of rows.
Several spill functions, TEXTBEFORE(), TEXTAFTER(), TEXTJOIN() and TEXTSPLIT() make handling text much easier than before. We no longer need to combine functions like SUBSTRING(), FIND() and LEN() together which was always hard.
Several new functions reshape arrays. The most useful are VSTACK() and HSTACK(). We take a quick look at these and others that come in useful
The course covers several new advanced functions and explains when and why these are useful. The functions include:
• LET() use variables for readability, performance in formulas
• LAMBDA() allows us to define and reuse custom functions
• MAP() works with LAMBDA() to create and use an anonymous function
Building Financial Models in Excel
Who should attend: Excel users who want to build financial models in Excel, especially where those models have a “what-if” aspect.
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.
Outline:
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.