Description
Day 1: Advanced Data Management
Lesson 1: Data Validation and Conditional Formatting
- Objective: Learn to control data entry and highlight critical financial information.
- Content:
- Creating drop-down lists with Data Validation.
- Setting up input messages and error alerts.
- Applying Conditional Formatting rules.
- Using formulas in Conditional Formatting for financial data.
Lesson 2: Advanced Filtering and Sorting
- Using Advanced Filter to create complex criteria.
- Custom sorting options (by color, by custom list).
- Using the Subtotal function to summarize filtered financial data.
Lesson 3: Excel Tables and Structured References
- Creating and managing Excel Tables.
- Using structured references in formulas.
- Leveraging Table features for dynamic data ranges and financial reporting.
Day 2: Advanced Formulas and Functions
Lesson 4: Lookup and Reference Functions
- Using VLOOKUP, HLOOKUP, and XLOOKUP.
- Understanding INDEX and MATCH for more flexible lookups.
- Applying INDIRECT for dynamic references.
- Using OFFSET for creating dynamic ranges.
Lesson 5: Financial Functions
- Using PMT, PV, FV, NPV, and IRR for financial analysis.
- Applying CUMIPMT and CUMPRINC for loan amortization schedules.
- Using XNPV and XIRR for irregular cash flows.
- Understanding and using RATE, NPER, and other financial functions.
Day 3: Pivot Tables and Dashboards
Lesson 6: Creating and Customizing Pivot Tables
- Creating Pivot Tables from financial data.
- Customizing fields and layouts for financial reporting.
- Grouping data (by dates, numbers).
- Using calculated fields and items.
Lesson 7: Pivot Charts and Dashboards
- Creating Pivot Charts.
- Designing interactive financial dashboards.
- Using slicers and timelines for dynamic data filtering.
- Best practices for financial dashboard design.
Day 4: Power Query and Data Analysis
Lesson 8: Introduction to Power Query
- Accessing Power Query in Excel.
- Importing data from various sources (Excel, CSV, databases).
- Basic data transformations (filtering, sorting, merging).
- Loading transformed data into Excel.
Lesson 9: Advanced Data Analysis with Power Query
- Combining queries (merge and append).
- Creating custom columns and conditional logic.
- Grouping data and performing aggregations.
- Using Power Query formulas (M language).
Day 5: Automation with Macros and VBA
Lesson 10: Introduction to Macros
- Recording basic macros.
- Running and editing recorded macros.
- Understanding the VBA Editor interface.
Lesson 11: Basic VBA Programming for Financial Automation
- Writing simple VBA scripts.
- Using loops and conditional statements.
- Creating user-defined functions (UDFs).
- Automating repetitive financial tasks (data entry, report generation).
Description
- Learn to control data entry and highlight critical financial information.
- Master complex data filtering and sorting techniques.
Understand the benefits of using Excel Tables for financial data.
Learn to retrieve and reference data efficiently.
Master functions to handle financial calculations. - Summarize and analyze large financial datasets with Pivot Tables.
- Visualize financial data and create interactive dashboards.
Understand Power Query for data extraction and transformation.
Perform advanced data analysis and transformations.
Learn to record and use macros to automate tasks.
Automate financial tasks with basic VBA programming.
General Notes
GENERAL NOTES
i. The clients have many options on where they would wish to have the training
ii. The client should communicate their preferred training venue at least seven days before the training date failure to which the training shall take place in Nairobi.
iii. This course is delivered by our seasoned trainers who have vast experience as expert professionals in the respective fields of practice. The course is taught through a mix of practical activities, theory, group works and case studies.
iii. Training manuals and additional reference materials are provided to the participants.
iv. Upon successful completion of this course, participants will be issued with a Chania finance consultancy certificate.
v. The onsite training fee covers tuition fees, training materials, tea at 10am, lunch, tea at 4pm, training venue and a certificate on completion. Accommodation and travel may be arranged for our participants upon request.
vi. The online training fee covers tuition fees, training materials, and a certificate on completion.
Available at the following Venues
- Nairobi
- Mombasa
- Nakuru
- Kisumu
- Google meet
- Zoom
- Cisco Webex
- Microsoft Teams
Choose the date you would like to take the course
Related Courses
10,000+ unique online course list designs
