Excel 2007 Business Modelling
Categories: Microsoft Excel
Excel 2007 Business Modelling.

About the Course:
This course is for Microsoft Excel 2007. 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.

Course Contents:
- 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
- Using name manager
- Styles and themes
- How themes work
- Using the default styles
- Creating custom styles
- Number formatting
- Scaling numbers
- The four parts of a format
- Creating custom formats
- Dates and times
- How dates are stored
- How 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
- Cell comments
- Grouping and outlining
- Setting cell validation
- Protecting cells/worksheets
- Protecting a workbook
- Multiple worksheets
- Copying, moving and renaming sheets
- 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
- The fourth lookup argument
- Advanced lookup functions
- Using MATCH to find values
- Combining MATCH and INDEX
- Using OFFSET
- The feared INDIRECT function
- Scenarios
- The scenario drop-down tool
- Adding scenarios
- What-if analysis
- Creating a scenario report
- Goal-seeking 
- Goal-seeking using charts
- Data tables
- One-way data tables
- Two-way data tables
 
Tools
 
Key Details
Duration2 days
 
£595
per delegate