Excel Business Modelling training course (2 days)
How many of the following boxes can you tick: you know Excel well, and use it every day? You work with financial models? You know how to create an IF function, but struggle with MATCH? This two-day course is aimed at those who need to build or work with financial models in Excel. On this course you'll learn best-practice techniques for building models, using range names more than you could ever conceive, and you'll find out about functions like MATCH, INDEX and SUMPRODUCT. Oh, and on the way you'll learn tons of short-cut keys and tips and tricks, we promise!
Excel Business Modelling training - Course Contents
The contents of our Excel Business Modelling course are as follows (any items marked with a * will be covered if time allows):
Building models
- Freezing windows
- What makes a good formula
- Separating inputs and calculations
- Avoiding the IF function
- Maintaining constant periodicity
Masking
- Avoiding the IF function
- Creating a mask
Range names and absolute references
- Absolute references ($ symbol)
- Fixing only the row/column
- Creating range names
- Labelling ranges automatically
Styles and themes
- How themes work
- Using the default styles
- Creating custom styles
Number formatting
- Creating custom formats
- The four parts of a format
- Scaling numbers
Dates and times
- How dates and times are stored
- Useful date/time functions
- Formatting dates and times
Conditional formatting
- Creating/using cell rules
- Data bars and colour sets
Validation and protection
- Setting cell validation
- Protecting cells/worksheets
- Cell comments
- Grouping and outlining
Outlining
- Creating outlines
- Improving how symbols appear
Multiple worksheets
- Copying, moving and renaming
- The dangerous group mode
- Summing across worksheets
- 3-D range names
|
Array formulae
- Creating array formulae
- Editing and deleting array formulae
- Strengths and weaknesses
Cashflow calculations
- Avoiding circularity
- Separating interest paid and received
- Dealing with compound interest rates
- Getting quarterly summary figures
Investment appraisal
- Net present value
- Internal rate of return
- Non-periodic cashflows
IF and LOOKUP functions
- The conditional (IF) function
- Nested IFs are evil
- Lookup tables
Advanced lookup functions
- Using MATCH to find values
- Combining MATCH and INDEX
- Using OFFSET
- The feared INDIRECT function
Advanced formulae tricks (*)
- Using SUMPRODUCT
- Using dynamic ranges
Other functions
- Text functions in Excel
- Inspection functions
- INFO and CELL
Scenarios
- The scenario drop-down tool
- What-if analysis
- Creating a scenario report
Goal-seeking
- Targeting a given value
- Setting number of iterations
Data tables
- One-way data tables
- Two-way data tables
|
Click on any link above to show free exercises for the topic in question. Delegates should already be very comfortable with using the basics of Excel. Some financial modelling or accountancy background, while not essential, will be very useful. There will be a maximum of 6 people on each course, and each person will have their own computer.
