Visual Basic for Applications
What is Visual Basic for Applications?
Intermediate Excel for Finance Professionals looks at the end-to-end process of creating flexible, robust and user-friendly financial models. Our approach is about going back to basics – to the core concepts underpinning the entire programme, to understand its ‘brain’ and the underlying premises. Then, to deepen the understanding of the theory, we run a series of exercises integral to the practice of Intermediate financial modelling techniques.
This course has been written by Jake Blackmore ACCA, who has built up his knowledge and experience in Excel over the years by working in a variety of analytical roles for large industries. Jake can relate to the demands and challenges that you as an accountant deal with every day; the tight month-end deadlines, cumbersome finance systems, planning complex business models; and continuous change. Jake will present this course and he will lead you on a journey and show you the many excellent tools Excel offers and he will demonstrate how these tools helped him overcome many of the challenges he has faced.
This is an intensive hands-on three-day course. The course includes textbooks, a range of structured exercise files, code snippets that can be implemented to improve your finance models and a couple of useful free Excel utilities.
All textbooks and exercise files are supplied in electronic formats for take-away purposes and the textbooks contain a variety of links to online tutorials for continued learning.
MODULE ONE – Purpose of Excel
- The fundamental goals of Excel
- Initial insight into Excel’s “Brain”
- Exposure to Excel formats
MODULE TWO – Navigation & Structure
- Excel’s ribbon and helpful shortcuts
- Worksheet grid and an initial look at Named Ranges
- Merged cells and the respective risk
MODULE THREE – Data Management
- Automate data import and input using query tables and Power Query
- Number, date, Boolean, text or error
- Operators and Operandums
- Implement balancing checks and flags using conditional formatting and number formatting, and implement an approach that allows for easy error tracking
MODULE FOUR – Adjusting & Customising
- The effects of altering the Dependency tree
- Inserting objects such as Microsoft Word documents or shapes
- Simple use of Named ranges in Data Validation to deliver drop down lists
- Improve model flexibility and speed up calculations by implementing Dynamic (Named) Ranges for formula references
MODULE FIVE – Intermediate Formula
- Discover how worksheet functions actually work and explore a collection of useful functions
- Learn a structured approach to breaking-down and understanding complex formulas
- Locate and handle formula errors; and discover the value of unhandled formula errors such as #N/A
- Using Boolean logic to deliver digital results
- Lookups: Binary lookup vs. Linear Search in VLookups and become able to improve lookup performance by an average of 400%. Also discover a variety of other useful applications of binary searches.
MODULE SIX – Output & Protection
- Setting print ranges and printing options
- Creating Pivot Tables instead of formulae-based summaries
- Understand the concepts of good charting principles and learn how to determine the appropriate chart type best suited to represent the data
- Implement interactive charts using Pivot Charts
- Create mix chart types and use a secondary axis
- Slicers: Link multiple charts (and/or Pivot Tables) to common Slicer controls
- An initial look at cell, worksheet and workbook protection
MODULE SEVEN – Question & Answer Time
- Attendees are encouraged to ask questions throughout all modules, but you are also encouraged to bring real problems to address as a group
- Review the various online resources to further ones Excel learning
This is very much a hands on course. Delegates will be required to bring laptops and participate in a variety of technical exercises to reinforce the methods and concepts covered.