Classroom Training
Excel Business Modelling
Classroom training course - online version also available
£750 + VAT for most venues (£895 + VAT for London)
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!
Course details
Possible locations:London, Manchester, Online
Next classroom course:No classroom dates
Time:10.00-16.30
Course Contents
The contents of this course are as follows:
Building models
- What makes a good formula
- Separating inputs and calculations
- Maintaining constant periodicity
Range names
- Creating range names
- Editing/deleting range names
Masking
- Avoiding the IF function
- Creating a mask
Styles
- Using styles to apply formatting
- Number format styles
Formatting numbers and dates
- Standard number formats
- Hiding cell contents
- Date formats
Conditional formatting
- Creating cell rules
- Managing cell rules
- Viewing conditional formats
- Data bars and icon sets
Data validation
- Setting validation rules
- Input and error messages
- Highlighting validated cells
- Circling invalid data
Protection
- How protection works
- Unlocking cells
- Turning protection on for a worksheet
- Protecting the whole workbook
Grouping/outlining
- Creating and removing groups
- Expanding and collapsing
Multiple worksheets
- Group editing
- Formulae across sheets
- 3D summing across sheets
Array formulae
- Block formulae
- What the curly brackets mean
- Functions using arrays
Cashflow calculations
- Avoiding circularity
- Separating interest paid and received
- Dealing with compound interest rates
Investment appraisal
- Net present value
- Internal rate of return
- Non-periodic cashflows
Conditional formulae
- Creating a single =IF formula
- Using =SUMIFS and =COUNTIFS
- Combining conditions
Lookup functions
- The XLOOKUP function
- VLOOKUP for continuous values
- VLOOKUP for exact matches
- Data validation for exact matches
Advanced lookup functions
- The XMATCH function
- Combining =INDEX and =MATCH
- The OFFSET function
Scenarios
- Creating what-if scenarios
- The scenario manager
If time
Goal-seeking
- Targeting a given value
- Setting the number of iterations
Data tables
- One-way data tables
- Two-way data tables
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. This course uses Excel 2016, but will be just as useful if you're using Excel 2013, 2010 or 2007. Note that this course is an alternative to our two-day Advanced Excel course, not a follow-on.
Why Choose Us?
- No more than 5 people per class
- Proper restaurant lunch included
- 554 references viewable online
- Free USB stick containing course files
- Genuine Wise Owl trainers only
- Unbelievably good courseware
Sample customer feedback
Our thanks to everyone who agreed to share their thoughts (note that some of these reviews may be for our online courses).
Kevin Springall (Marley Ltd)
John Harriman (Blue Prism Ltd)
Stephanie Northwood (Frontline Ltd)
Samantha Briggs (Lex)
Stuart Barnard (Mercedes UK)
There are lots more where these came from (or just refresh this page to change the quotes shown).
Related Courses
Other Microsoft Excel training resources
Excel exercises
We believe in complete transparency, so we've published all of our Excel exercises online, including the ones used in this Excel Modelling course.
View Excel, Excel VBA or PowerPivot exercises
Excel videos
The Wise Owl YouTube channel carries lots of videos on Microsoft Excel, among many other subjects.
View Excel tips, Excel VBA - Basics, Microsoft Excel or PowerPivot videos
Excel skills assessment
Test whether you know Excel as well as your "neighbour" by taking one of our skills assessment tests.
View Test your VBA knowledge or Test your Excel 365 skills skills assessment tests
Excel blogs
The blogs on this site contains lots of extra tips and tricks about how to use Excel.
View Excel, Power BI - Excel or VBA macros blogs