8 Topics
5 Days
Programme Overview
Training Description
Who Should Attend
- Accountants
- Budget Analysts
- Data Analysts
- Finance Managers
- Financial Analysts
- Investment Analysts
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
This intensive training course is designed to equip participants with advanced Microsoft Excel skills specifically tailored for creating robust and dynamic budgeting and financial models. You'll delve into powerful functions, data analysis tools, and best-practice techniques to enhance the accuracy, flexibility, and presentation of your financial workbooks. The focus is on transitioning from manual data management to using automated and efficient modeling approaches to support better decision-making and forecasting within an organization. This course moves beyond basic spreadsheet creation to advanced manipulation and visual presentation of complex financial data.
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
