Description
Day 1: Advanced Data Management and Analysis
- Advanced Data Validation
- Custom data validation rules
- Dynamic drop-down lists
- Advanced Filtering and Sorting
- Custom views
- Advanced filter criteria
- Conditional Formatting
- Using formulas for conditional formatting
- Color scales, data bars, and icon sets
- Advanced PivotTables and PivotCharts
- Calculated fields and items
- Using slicers and timelines for interactive analysis
- Practical Exercise:
- Analyzing an insurance claims dataset with advanced PivotTables
Day 2: Advanced Formulas and Functions
- Array Formulas and Functions
- Introduction to array formulas
- Using functions like TRANSPOSE, MMULT, and FREQUENCY
- Lookup and Reference Functions
- Advanced use of VLOOKUP, HLOOKUP
- Using INDEX and MATCH together
- Introduction to XLOOKUP
- Logical and Information Functions
- Combining IF, AND, OR, and NOT functions
- Using ISERROR, ISNUMBER, and other information functions
- Text Functions
- Using LEFT, RIGHT, MID, SUBSTITUTE, and TEXTJOIN
- Creating complex text formulas for data cleaning
- Practical Exercise:
- Creating a detailed insurance premium calculation model using advanced functions
Day 3: Financial Modeling and Analysis
- Financial Functions
- Using PV, FV, NPV, IRR, PMT, and other financial functions
- Building loan amortization schedules
- Scenario Analysis and Data Tables
- Creating and managing scenarios
- Using one-variable and two-variable data tables
- Afternoon Session:
- What-If Analysis
- Goal Seek
- What-If Analysis
- Solver for optimization problems
- Monte Carlo Simulation
- Introduction to Monte Carlo methods
- Building a simple Monte Carlo simulation in Excel
- Practical Exercise:
- Developing a financial model for forecasting insurance product performance
Day 4: Automation with Macros and VBA
- Introduction to Macros
- Recording and editing macros
- Assigning macros to buttons and shortcuts
- Introduction to VBA
- VBA editor and basic coding
- Writing simple VBA scripts
- Advanced VBA Techniques
- Creating user forms
- Advanced VBA Techniques
- Error handling and debugging
- Automating Tasks
- Automating repetitive tasks in insurance reporting
- Creating custom functions with VBA
- Practical Exercise:
- Developing a VBA script to automate the generation of insurance policy reports
Day 5: Data Visualization and Dashboards
- Advanced Charting Techniques
- Creating combo charts, waterfall charts, and other advanced chart types
- Advanced Charting Techniques
- Using sparklines for mini charts
- Interactive Dashboards
- Designing interactive dashboards
- Using form controls (buttons, drop-downs, sliders) in dashboards
- Afternoon Session:
- Power Query and Power Pivot
- Importing and transforming data with Power Query
- Power Query and Power Pivot
- Creating data models and relationships with Power Pivot
- Connecting to External Data Sources
- Connecting to databases and web data sources
- Refreshing data connections automatically
- Practical Exercise:
- Building an interactive dashboard to visualize key insurance metrics and KPIs
Description
> Enhance Data Management Skills: Improve efficiency and accuracy in handling large datasets.
> Develop Advanced Analytical Capabilities: Utilize advanced functions and tools for in-depth data analysis.
> Improve Financial Modeling and Analysis: Build robust financial models and conduct scenario analysis tailored to the insurance industry.
> Automate Repetitive Tasks: Use macros and VBA programming to reduce manual effort and minimize errors.
> Enhance Data Visualization and Reporting: Create advanced charts, interactive dashboards, and visual reports.
> Enable Efficient Industry-Specific Applications: Apply Excel skills to insurance-specific tasks such as premium calculations and claims analysis.
> Foster Problem-Solving and Critical Thinking: Apply critical thinking to real-world insurance data challenges.
> Promote Continuous Learning and Adaptability: Stay updated with the latest Excel features and best practices for ongoing professional development.
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.
- Zoom, Google meet
Choose the date you would like to take the course
Related Courses
10,000+ unique online course list designs
