Excel for Financial Management and Modelling

• Shortcut keys
• Data entry simplified
• Entering numbers with a fixed decimal point
• Autofill: Filling cells with a series of data
• The use of relative, absolute, and mixed references in formulae (use of $) Basic Functions
• SUM() AVERAGE(), MIN(), MAX(), COUNT(), ROUND(), IF()
• Formatting issues
• Custom number formats
• Quickly copying formats
• Format Painter
• Basic conditional formatting
• Hiding cells and cell contents
• Concatenating text and values
• Comments: Creating/Editing/Printing
• Manipulating Dates and Times
• Names: creation and uses
• Data validation
• Basic charting including setting up dynamic chart titles
• Recalculation options
• Protecting cells and worksheets

Conditional functions

• Nested IF() statements, COUNTA(), COUNTIF(), SUMIF()
• SUMPRODUCT()
  • This is a much more powerful function than might be suspected from reading the Excel help screens and allows for highly sophisticated conditional calculations

Lookup functions

• Basic lookup techniques
  • VLOOKUP(), HLOOKUP()
  • Trapping errors via ISERROR()/IFERROR()
• Non-exact lookups into bands of values
• The use of MATCH() and INDEX()
• Data tables based on lookups
• Dynamic charts (content of chart selected from list by end-user)

Managing lists and tables

• Sorting
• Filtering
• Grouping & Subtotals
• Forms
• Introduction to PivotTables & PivotCharts

Manipulating text

• Functions
  • LEN(), LEFT(), RIGHT(), MID(), FIND(), SEARCH()
• Examples of splitting/combining text strings
  • using the above functions
  • using "text to columns"

A case-study driven examination of the principles and practice of financial modelling, focusing on a project finance-style scenario, including the construction of an integrated set of simplified financial statements and subsequent investment appraisal and sensitivity analysis. Specific topics covered include:

• Good practice in model design
• Names as an aid to model clarity
• Circular references and iteration – uses and drawbacks
• Financial functions: NPV(), IRR()

What if? Analysis:

• Goal seek
• Solver
• Use of data tables
• Scenarios
• Understanding an existing model – Auditing techniques