Course Content
Spreadsheet modelling techniques
- Basic structure (separation of data from processing & output)
- Cell referenced formulas (no hard coding of data)
- Modularisation (break a complex model into simple bits)
- Prototype development and refinement
- Sensitivity analysis
- Troubleshooting and debugging
- Reasonability testing (common sense)
MS Excel functions and methods useful for modeling
- Named cell ranges
- Lookup functions
- Conditional functions (IF, COUNTIF, SUMIF)
- Financial functions (IRR, NPV)
- Formula nesting (pros and cons)
- Autofilters
- Advanced filters
- Goal seeking
- Conditional formatting
- Navigation buttons
Automating your model with macros
- Pros and cons
- Setting the macro security level
- Recording and running simple macros
- The Visual Basic editor
- Modifying a recorded macro for general use
Protecting your model from undesired changes
- Model structure
- Locking cells
- Worksheet and workbook protection
- Password protection
- Cross checks and error reports
- Data validation
Output presentation (tips for effective reporting of results)
The importance of documentation (may your model outlive you)
Spreadsheet model multimedia (just for fun)
- Adding sound to your model
- Providing video with your model
- Make your model talk
