Advanced Excel for Actuaries and Data Scientists

Session 2
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 3
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 4
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 5
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 6
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 7
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 8
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 9

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 10

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.