As the course summary below clearly illustrates, much of the syllabus is similar to an advanced Excel course, but there are some distinct differences:
- The course always refers to the real-life usage of Excel in the context of the Finance world. More specifically, the world according to Accountants and Analysts. So it does not simply teach functions & formulae without giving examples of where we find them of use in practice.
- It is written and presented by The Excel Pro, a Chartered Accountant with 20 years expertise in the application of advanced Excel in UK Finance, rather than someone who has only been trained to teach theory.
- Unlike several other courses out there, no time is allocated to functions or formulae or other technical areas which are rarely found being used in practice in UK Finance.
- However, plenty of time is dedicated to other relevant areas for accountants and analysts who regularly use Excel, such as: Planning, I.T. issues, Reporting, Modelling, Data Cleansing, and Master Data Management.
- A minimum 60% of the course time is dedicated to delegates working-through hands-on examples under the expert and personal supervision of The Excel Pro.
The course is split into the following twelve sections, many of which are inter-connecting in nature:
1 - Get More Out Of Your Dates
2 - Smarter Variance Analyses
3 - Isolating Critical Data
4 - Consolidation Considerations
5 - Cleansing & Merging Data
6 - Controlling & Validating Data
7 - Managing Master Data Risks
8 - Populating & Mapping
9 - Automating Via Drop-downs
10 - Extracting System Data
11 - Modelling Fundamentals
12 - Repeat Report Planning
How and why many of these areas inter-connect will be pointed-out where relevant during the course.
Below is a slightly more detailed summary of some of the items covered in each of the twelve sections of the course:
1 - Get More Out Of Your Dates
1.1 - Summarising Monthly Data
1.2 - Using Filters
1.3 - Using Formulae
1.4 - Using Pivot Tables
2 - Smarter Variance Analyses
2.1 - Copying & Amending Worksheets
2.2 - Using Edit... Replace
2.3 - Updating External Links
2.4 - Hiding Worksheets
2.5 - Using SUMIF
3 - Isolating Critical Data
3.1 - Using Text-Related Formulae
3.2 - Using nested IF's
3.3 - Simplifying Formulae
3.4 - Using Filters & Copying
4 - Consolidation Considerations
4.1 - Using Filters & SUBTOTAL
4.2 - Methodologies to avoid
4.3 - Grouping Data
4.4 - Preferred Methodologies
4.5 - Summing Multiple Worksheets
4.6 - When & How to use Edit... Replace
5 - Cleansing & Merging Data
5.1 - Functions to Amend or Re-arrange Text
5.2 - Combining Functions
5.3 - Methodologies for Removing Duplicates
5.4 - Methodology Limitations
6 - Controlling & Validating Data
6.1 - Validating Totals
6.2 - Locking Cells
6.3 - Restricting Data Entry
6.4 - Conditional Formatting
6.5 - Formulae-driven checks
7 - Managing Master Data Risks
7.1 - VAT number checks
7.2 - Bank detail checks
7.3 - Companies House checks
7.4 - Companies Court winding-up checks
8 - Populating & Mapping
8.1 - Manual Entry Risks & Mitigation
8.2 - Linking Advantages
8.3 - Using Autofill & Formulae
8.4 - When & How to use Absolute, Mixed, or Relative-References
8.5 - Advantages of Copying Parent & Child File Structures
8.6 - Uses & Advantages of Mapping Methodologies
8.7 - Using SUMIF & VLOOKUP
9 - Automating Via Drop-downs
9.1 - Creating Drop-down lists
9.2 - Creating Automatic Reports from Drop-down menu's
9.3 - Creating Conditional Drop-down lists
10 - Extracting System Data
10.1 - Sources for Reports
10.2 - Timing Relevance for Reports
10.3 - Accelerating Report Delivery
10.4 - Dynamic vs Static Data & Impact on Reporting
10.5 - Format Options for Extracting Reports
10.6 - Dumping-Data vs File-Saving
10.7 - Improving Presentation of Reports
10.8 - The Text Import Wizard
11 - Modelling Fundamentals
11.1 - Financial Model Structures
11.2 - Best-Practice Methodologies
12 - Repeat Report Planning
12.1 - Advice for Preparing Frequently-required Excel Reports
