Advanced Excel
This eight-week course is designed to equip current and aspiring actuaries with essential Excel skills for effective data analysis, financial modeling, and reporting. The course focuses on practical applications of Excel functions, tools, and techniques to streamline workflows and improve efficiency in actuarial tasks.
Apply Now
Instructor: Noura Shamseddine
Noura Shamseddine, FSA, in her role as actuarial exam instructor, specializes in preparing students to pass Exam P of the SOA exams. She achieved her FSA designation in 2023 and currently works in the role of Chief Actuary on the life insurance side of the actuarial industry. She earned a Master’s degree in Actuarial Science and a Bachelor’s degree in Pure Mathematics. With 10 years of experience in the actuarial field, Noura has taught courses in pure mathematics, statistics, probability, and various actuarial subjects. Students have found her to be a strong instructor and very enjoyable to work with.
Objectives
By taking this course you will learn the following skills:
- Fundamentals of Excel and functions
- Advanced data analysis and statistical functions
- Techniques for insightful reporting
- Creating interactive dashboards and professional reports for stakeholders
Additional Features:
- Homework Assignments: Weekly exercises designed to reinforce the concepts covered in class.
- Hands-on Practice: Real-life actuarial scenarios to apply learned skills.
- Interactive Learning: Weekly interactive sessions and Q&A with the instructor.
- Certificate of Completion: Upon successful completion of the course and final project.
Course Schedule
Session 1
Introduction to Microsoft Excel
- Overview of Excel interface (ribbons, cells, rows, columns);
- Basic operations;
- Customizing the Excel workspace;
- Introduction to functions and formulas.
Mathematical Functions in Excel
- Sum & SumProduct;
- Average Function and percentage Gain;
- Practice;
- Homework Assignment;
Lookup Functions
- VLOOKUP;
- HLOOKUP;
- Index Match;
- XLOOKUP;
- Practice;
- Homework Assignment;
Session 3
Advanced Math & Statistical Functions in Excel
- Sum & Sumifs;
- Count & Countifs Functions;
- Max and Min, large, Small;
- Frequency Analysis;
- Practice;
- Homework Assignment;
Logical Functions
- IF & IFs;
- AND, OR, NOT;
- IFERROR;
- Practice;
- Homework Assignment;
Session 4
Text Formula
- Split & Merge Text;
- Text Formatting: PROPER, LOWER, UPPER;
- Trim Function;
- Transpose Data;
- Practice;
- Homework Assignment;
Date/Time Formula
- TODAY and TDATE
- DAY, MONTH, YEAR, HOUR, MINUTES, SECONDS
- DAYOFWEEK, WORKDAY, WEEKNUM
- DIFFDATE
- Practice
- Homework Assignment
Session 6
Basic of Pivot Tables and Data Preparation
- Why do we need pivot tables?
- Preparing data for a pivot table;
- Creating our first pivot table;
- Pivot table fields;
- The “Analyze” and “Design” tabs of a pivot table;
- How to clear, select, and move a pivot table;
- How to refresh a pivot table and change the data source of a pivot table?
- TEST: Pivot table basics and data preparation;
- PRACTICE: Pivot table basics and data preparation;
Session 7
Creating Pivot Tables
- Formatting numeric values in a pivot table;
- Automatically formatting empty cells;
- Customizing the appearance and style of a pivot table;
- Editing pivot table headers
- Conditional formatting in a pivot table;
- TEST: Pivot table formatting
- PRACTICE: Pivot table formatting
Session 8
Sorting, Filtering and Grouping Pivot Table Data
- Sorting pivot table data;
- Pivot table label filters;
- Pivot table value filters;
- How to apply multiple filters in a pivot table;
- Grouping pivot table data;
- Pivot table slicers and timelines;
- How to show all filter pages of a pivot table;
- TEST: Sorting, filtering, and grouping pivot table data
- PRACTICE: Sorting, filtering, and grouping pivot table data
Session 9
Customizing Calculations in Pivot Tables
- Summing pivot table data;
- Additional calculations in the pivot table;
- Show data as % of Column/Row;
- Show data as % of Parent Total;
- Show data differences dynamically;
- Show data as a running total;
- How to show the rank of pivot table values;
- Creating calculated fields in a pivot table;
- Creating calculated items in a pivot table;
- TEST: Calculations in a pivot table;
- PRACTICE: Calculations in a pivot table;
Session 10
Pivot Charts
- Introduction to pivot charts
- How to create a column chart;
- How to create a pie chart;
- How to create a bar chart;
- How to protect a chart from resizing;
- How to change the chart type;
- Chart style and design;
- How to move a chart to a separate sheet;
- How to use slicers and timelines;
- TEST: Pivot charts in Excel
- PRACTICE: Pivot charts in Excel
Session 11
Power Pivots and Power Charts
- What is a Power Pivot;
- How to add data to the Power Pivot;
- How to link tables together
- Counting unique values in a pivot table;
- TEST: Power Pivots;
- PRACTICE: Power pivots;
Session 12
Content:
- Final Assignment
- Get Certificate
Final Project:
At the conclusion of the course, students will be required to complete a final project using a provided actuarial dataset. The project will test their ability to:
- Data Preparation: Clean, manipulate, and organize raw data for analysis.
- Excel Modeling: Apply advanced Excel functions, formulas, and PivotTables to build actuarial models.
- Reporting & Dashboards: Present results through professional reports and interactive dashboards, communicating findings clearly to stakeholders.
Prerequisites:
Students should have at least a basic understanding of the following topics and exams.
- Probability and statistics
- Financial mathematics
- The very basic excel operations
Required Software/Operating System:
- Microsoft Excel (preferably the latest version)
- A laptop or desktop computer
Join Computer Programming for Actuaries & Data Scientists and advance your computer skills to a level that will make you a hot commodity in the Actuarial Science and Data Science job markets.
REGISTER
