Excel Business Modelling (online)

• What makes a good formula
• Separating inputs and calculations
• Maintaining constant periodicity

• Creating range names
• Editing/deleting range names

• Avoiding the IF function
• Creating a mask

• Using styles to apply formatting
• Number format styles

• Standard number formats
• Hiding cell contents
• Date formats

• Creating cell rules
• Managing cell rules
• Viewing conditional formats
• Data bars and icon sets

• Setting validation rules
• Input and error messages
• Highlighting validated cells
• Circling invalid data

• How protection works
• Unlocking cells
• Turning protection on for a worksheet
• Protecting the whole workbook

• Creating and removing groups
• Expanding and collapsing

• Group editing
• Formulae across sheets
• 3D summing across sheets

• Block formulae
• What the curly brackets mean
• Functions using arrays

• Avoiding circularity
• Separating interest paid and received
• Dealing with compound interest rates

• Net present value
• Internal rate of return
• Non-periodic cashflows

• Creating a single =IF formula
• Using =SUMIFS and =COUNTIFS
• Combining conditions (AND, OR and NOT)

• Using VLOOKUP for continuous values
• Using VLOOKUP for exact matches
• List data validation for exact matches
• Dynamic range names
• Handling errors in lookup tables

• Using = MATCH to find values
• Combining =INDEX and =MATCH
• The OFFSET function
• The INDIRECT function

• Creating what-if scenarios
• The scenario manager

If time

• Targeting a given value
• Setting the number of iterations

• One-way data tables
• Two-way data tables