8 Topics
5 Days
Programme Overview
Training Description
Who Should Attend
- Financial Planning & Analysis (FP&A) Professionals
- Treasury Professionals
- Investment Analysts
- Management Accountants
- Business Analysts
- Project Finance Professionals
- Internal Auditors
- Risk Analysts
- Procurement and Supply Chain Professionals
- Operations Managers
- Entrepreneurs and Business Owners
- Banking and Financial Services Professionals
Session Objectives
- Master advanced Excel functions relevant to budgeting and financial modeling (e.g., INDEX, MATCH, OFFSET, INDIRECT).
- Learn how to build dynamic and flexible financial models using advanced Excel formulas.
- Learn how to build dynamic and flexible financial models using advanced Excel formulas.
- Master advanced data manipulation and cleaning techniques using Excel's Text to Columns, Flash Fill, and Remove Duplicates.
- Learn how to leverage Power Query (Get & Transform Data) to import, clean, and transform data from various sources.
- Develop skills in using Power Pivot to create and manage data models for complex financial analysis.
- Master the creation of sophisticated charts and dashboards for effective visualization of financial data.
- Learn how to use Excel's data validation features to ensure data integrity in financial models.
- Develop skills in utilizing advanced conditional formatting to highlight key financial insights.
- Understand how to automate repetitive budgeting and modeling tasks using Excel macros (VBA basics).
- Learn how to link Excel worksheets and workbooks for integrated financial models.
- Explore best practices for designing robust, auditable, and user-friendly financial models in Excel.
- Develop a practical toolkit of advanced Excel skills applicable to real-world budgeting and financial modelling scenarios.
About the Course
Excel remains one of the most powerful and widely used tools for financial analysis, budgeting, forecasting, and business decision-making. The Advanced Excel Skills for Budgeting & Financial Modelling Training Course is designed to equip finance professionals and business users with advanced Excel techniques required to build accurate financial models, automate reporting processes, analyze data, and improve financial planning capabilities.
The course covers advanced Excel functions, financial modelling principles, forecasting techniques, data analysis tools, dashboard creation, automation, and model optimization. Participants will gain practical experience in building integrated financial models, conducting sensitivity analysis, preparing management reports, and improving the efficiency and accuracy of financial planning processes.
General Notes
- This course will be delivered by our experts and professionals in data analysis with vast experience. The workshop will be highly interactive
- Training manuals and additional reference materials are provided to the participants.
- Upon successful completion of this course, participants will be issued with a certificate.
- The training will be conducted by PB Institute of Research and Technology
- The training fee covers tuition fees, training materials, lunch and training venue. Accommodation and airport transfer are arranged for our participants upon request.
- Payment should be sent to our bank account before the start of training and proof of payment sent to: info@pbirt.co.ke
Curriculum & Topics
8 Topics | 5 Days
-
Subtopic 1.1:
Deep dive into lookup and reference functions (INDEX, MATCH, OFFSET, INDIRECT) for dynamic model building for your module.
-
Subtopic 1.2:
Utilizing logical functions (IF, AND, OR, IFERROR) for robust error handling and conditional calculations.
-
Subtopic 1.3:
Mastering text functions (LEFT, RIGHT, MID, FIND, TEXT) for data extraction and formatting.
-
Subtopic 1.4:
Mastering text functions (LEFT, RIGHT, MID, FIND, TEXT) for data extraction and formatting.
-
Subtopic 1.5:
Leveraging array formulas for complex calculations across multiple cells.
-
Subtopic 2.1:
Creating flexible assumption sections that drive model outputs for your module.
-
Subtopic 2.2:
Implementing dynamic range selection using functions like OFFSET and INDIRECT.
-
Subtopic 2.3:
Building robust formulas for calculating key financial metrics (e.g., NPV, IRR, ROI).
-
Subtopic 2.4:
Developing scenarios and switches within models using IF and CHOOSE functions.
-
Subtopic 2.5:
Ensuring model transparency and auditability through clear formula construction.
-
Subtopic 3.1:
Utilizing Excel's What-If Analysis tools (Scenario Manager, Goal Seek, Data Tables) for exploring different financial outcomes for your module.
-
Subtopic 3.2:
Building dynamic scenario selection mechanisms using dropdown lists and formulas.
-
Subtopic 3.3:
Performing sensitivity analysis to assess the impact of changing key assumptions.
-
Subtopic 3.4:
Visualizing scenario and sensitivity analysis results using charts.
-
Subtopic 3.5:
Applying stress testing techniques to evaluate model resilience.
-
Subtopic 4.1:
Mastering Excel's Text to Columns and Flash Fill for efficient data parsing and transformation for your module.
-
Subtopic 4.2:
Utilizing Remove Duplicates to ensure data integrity.
-
Subtopic 4.3:
Employing advanced filtering and sorting techniques for data exploration.
-
Subtopic 4.4:
Leveraging functions like SUBTOTAL and AGGREGATE for dynamic data aggregation.
-
Subtopic 4.5:
Validating data using Excel's data validation features to prevent errors.
-
Subtopic 5.1:
Importing data from various sources (CSV, TXT, Excel, databases, web) using Power Query for your module.
-
Subtopic 5.2:
Cleaning and transforming data using Power Query's intuitive interface.
-
Subtopic 5.3:
Combining and appending data from multiple sources.
-
Subtopic 5.4:
Automating data import and transformation processes.
-
Subtopic 5.5:
Understanding the M language for advanced Power Query transformations.
-
Subtopic 6.1:
Creating data models in Power Pivot using relationships between tables for your module.
-
Subtopic 6.2:
Understanding and utilizing Data Analysis Expressions (DAX) for advanced calculations.
-
Subtopic 6.3:
Building calculated columns and measures in Power Pivot.
-
Subtopic 6.4:
Performing complex aggregations and analysis using DAX functions.
-
Subtopic 6.5:
Integrating Power Pivot models with PivotTables and PivotCharts for powerful reporting.
-
Subtopic 7.1:
Designing impactful and informative charts for visualizing financial data beyond basic chart types for your module.
-
Subtopic 7.2:
Utilizing combo charts, scatter charts, and other advanced visualizations.
-
Subtopic 7.3:
Creating dynamic charts that respond to data changes and scenario selections.
-
Subtopic 7.4:
Building interactive financial dashboards using linked charts and slicers.
-
Subtopic 7.5:
Applying best practices for data visualization in financial communication.
-
Subtopic 8.1:
Understanding the fundamentals of Visual Basic for Applications (VBA) for automating tasks for your module.
-
Subtopic 8.2:
Recording and running simple macros to automate repetitive actions.
-
Subtopic 8.3:
Editing and customizing recorded macros using the VBA editor.
-
Subtopic 8.4:
Understanding basic VBA syntax and programming concepts (variables, loops, conditions).
-
Subtopic 8.5:
Creating simple user-defined functions (UDFs) to extend Excel's functionality
