Specifically designed for finance 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 Finance roles. This very practical course covers everything you need to perform your job and nothing you don’t!
This course is part of the Essential Excel Series:
- Essential Excel for Finance
- Essential Excel for Business Administration
- Financial Reporting for Executive Assistants
For those who don’t have time to sit through extensive Excel “how to” training, the Essential Excel courses arm you with a range of tools, techniques and formulas essential for your job. Guaranteed to contain “no fluff”, you will be equipped with the skills to increase productivity and produce more efficient and accurate Excel calculations and reports.
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
Who should Attend?
Financial Accountants, Business Analysts, Finance Managers, Reporting Analysts, Financial Analysts, Management Accountants, Commercial Managers, Financial Controllers, Budget Managers, Accounts Receivable, Accounts Payable
Learning Objectives
This course is very hands-on and practical and each participant will be expected to create their own spreadsheet models. During the day, we’ll use the Excel tools and techniques learned to achieve common workplace solutions:
- Create an interest rate calculator to test sensitivity of repayments to changes in loan assumptions
- Build self-balancing error checks and variance alerts
- Calculate weighted average cost of capital (WACC) and project evaluation measures NPV, IRR and payback period
- Forecast cash revenue using date functions and pivot tables
- Develop a user-friendly, interactive pricing model containing spin buttons and drop-down boxes
Course Content
Must-know Tools and Techniques
- The upgrade to the latest version of Excel
- Absolute referencing
- Named Ranges
- Time saving shortcuts
Formulas you simply can’t live without
- LOOKUP, VLOOKUP, HLOOKUP and their successor, XLOOKUP (365 only)
- 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
Other Useful Stuff
- Spin buttons
- Hiding & Grouping
- Working with dates
- Hyperlinking
- Conditional formatting
