Excel 2003 Modelling training course (2 days)
This course is aimed fairly and squarely at people who work in Finance and business modelling. Developed in-house by an expert in business modelling, this course shows you how to create robust business models which are easy to build and (above all) easy to test. The course makes extensive use of Excel range names.
Excel 2003 Modelling training - Course Contents
The contents of our Excel 2003 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
Range names and absolute references
- Absolute references ($ symbol)
- Fixing only the row/column
- Creating range names
- Labelling ranges automatically
- The amazing 40% rule
Number formatting
- Conditional number formats
- The four parts of a format
- Creating custom formats
- Formatting dates and times
Advanced formatting
- Creating and applying styles
- Grouping and outlining
- Data validation
- Applying protection
Styles
- Creating formatting styles
- Using the style drop-down box
- The NORMAL style
Reference functions
- Combining MATCH and INDEX
- Using VLOOKUP
- Using the OFFSET function
The INDIRECT function
- How INDIRECT works
- When to use it
|
Masking
- Avoiding the IF function
- Creating a mask
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
Array formulae
- Creating array formulae
- Editing and deleting
- Strengths and weaknesses
Goal-seeking
- Goal-seeking
- Goal-seeking using charts
Scenarios
- The scenario drop-down tool
- Adding scenarios
- What-if analysis
- Creating a scenario report
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.
