Essential Excel for Finance

Specifically designed for business professionals who need to take their Excel skills to the next level, this intensive full day course focusses on the technical Excel skills required in everyday Excel modelling.  This very practical course covers everything you need to perform your job and nothing you don’t!

For those who don’t have time to sit through extensive Excel “how to” training, this course will arm you with a range of tools, techniques and formulas essential for Finance.  Guaranteed to contain “no fluff”, this course will equip you with the skills to produce more efficient and accurate Excel models and increase productivity.

Prerequisites

The course material includes extensive use of Excel and participants will gain the maximum benefit from this course if they are already competent spreadsheets users. It is designed for users who do use (or will use) Excel on a semi-regular basis, and are comfortable with using its basic tools and functions.  At minimum, it is assumed that participants will know how to:

• Navigate confidently in Excel
• Create and use simple formulas
• Link between workbooks

Software used

Workstations are provided. Course materials are written with both Excel 2007 and 2003 instructions.  The course is demonstrated mostly using Microsoft Excel 2007 and participants can choose which version of Excel they prefer. If participants are more comfortable using their own laptops, they are most welcome to bring their own equipment. PCs are used during this course, so Apple Macintosh users may prefer to bring their own laptops.

Learning Objectives

During the course, participants will create their own Excel files to take away with them. Each participant is provided with a 1GB USB memory stick on which they will be able to take their files away for future reference.

This course is very hands-on and practical.  Each participant will be expected to create their own models utilising the tools and techniques covered during the course.

You will learn how to:

1. Apply the key functions in Excel to your own models
2. Select the most appropriate formula to achieve the desired outcome
3. Create in-built error checks and variance alerts
4. Prevent incorrect use of your model by protecting worksheets
5. Validate data entry by setting data entry parameters
6. Create a navigation page to help users find their way around your model
7. Develop drop-down boxes which enable a model to produce a series of results depending on scenario variable selected

Course Content

Must-know Tools and Techniques

-    Key points on upgrading from Excel 2003 to 2007
-    Absolute referencing
-    Named Ranges
-    Time saving shortcuts

Formulas you simply can’t live without

-    LOOKUP, VLOOKUP, HLOOKUP
-    Logical functions eg. IF, AND
-    Aggregation functions eg. SUMIF, COUNTIF
-    Financial Functions eg. NPV, IRR, PMT
-    Formula Nesting

Bullet-proofing your Model

-    Protect data by locking cells
-    Password Protection
-    Restricting incorrect data entry with data validations
-    Using worksheet protection to prevent entry
-    Protecting your file using “Read Only” and password protection

Charting

-    Creating and modifying a chart
-    Making your charts look fabulous
-    Charting tips, tricks and shortcuts

Essential Advanced Tools

-    Autofilters
-    Goal Seek
-    Macros
-    Pivot Tables
-    Array formulas

Scenario Tools

-    Scenario Manager
-    Drop-down boxes
        - In cell drop-down boxes
        - Combo boxes
-    Data Tables

Other Useful Stuff

-    Spin buttons
-    Hiding
-    Working with dates
-    Hyperlinking
-    Conditional formatting