Hone your Excel skills with this ACTEX Professional Series of Seven On-Demand Webinars that you can watch at your convenience over the next 180 days. Perfect for individual or corporate training! 9+ hours of video instruction!
Instructor Mary Pat Campbell, FSA, MAAA, PRM, is Vice President, Insurance Research at Conning in Hartford, Connecticut. She also teaches courses on computing (Excel, Access, and VBA) and business writing for actuarial science students at the University of Connecticut.
Mary Pat has had a long interest in spreadsheet best practices in actuarial work, writing for SOA publications on the topic since 2007. She has also spoken for SOA webcasts and at meetings on spreadsheet issues, most recently at the 2015 SOA Annual Meeting: "Session 30: How to Keep Your Spreadsheets Out of the Headlines". She is a member of the European Spreadsheet Risks Interest Group and on the SOA Modeling Section Council (2015 - 2018).
Contents of this Professional Series:
1. "Principles for Actuarial Spreadsheet Design"
Spreadsheets are one of the largest workhorses in actuarial work and business work in general, but few of us have serious, rigorous training in their use and design. Various bodies have developed lists of best practices for spreadsheets, but these best practices lists are often developed for accountants and financial analysts, and do not necessarily fit with actuarial needs.
This session will cover high-level principles of spreadsheet design with a special focus on actuarial needs and issues, as well as specific practical implementation tips.
Specific topics to be covered include:
- Considering why one should (or should not) do certain things, not only how.
- How to structure the flow of information through your spreadsheet together with appropriate documentation
- Building spreadsheets with future changes in mind
- Why using the INDEX/MATCH combination is preferable to VLOOKUP
- Considerations on the use of Named Ranges
2. "Best Practices for Auditing Actuarial Spreadsheets"
Inherited somebody else's spreadsheets? Looking to untangle a monster? One of the most challenging tasks for actuaries is to take a spreadsheet already in production and try to do something useful with it - especially if this is a standard spreadsheet that has accumulated years of changes from multiple authors. And especially if one small change appears to break everything.
This session will cover auditing, testing, and fixing extant spreadsheets with a special focus on actuarial needs and issues, as well as specific practical implementation tips.
Specific topics to be covered include:
- A methodical approach to auditing spreadsheets
- Creating formulae and names inventories
- VBA code for finding spurious cells, and why one wants to minimize such cells
- Untangling megaformulas
- Approaches to spreadsheet testing, derived from software testing principles
3. "Excel VBA for Beginners Part 1: An Overview of VBA"
For the complete beginner in VBA, this session is going to look at the basics of how to use VBA in Microsoft Excel. For those with some VBA experience, this session will give you the comprehensive overview needed to fully understand what VBA can do for you. Instructor Mary Pat Campbell will provide an overview of VBA's features so you can take the knowledge and apply it to your workflow.
Specific topics covered will include:
- Setting up the VBA environment in Excel
- Navigating around the VBA editor
- VBA "Stuff": Objects, Methods, and Events
- Using the Macro recorder
- Writing your first subroutine
- Writing your first user-defined function
- Resources for growth
4. "Excel VBA for Beginners Part 2: Getting Started with Automation in Excel VBA"
Need to update that monthly report in Excel that you mess with manually? Why not get VBA to do it for you! Assuming no programming experience on the part of the participant, we'll look at the steps of using VBA to automating processes:
- Quick refresher on VBA subroutines and the macro recorder
- Walking through macro results - what to keep, what to throw
- Loops and Control Logic
- Don't copy that range! How to transfer results best
- Making your sub run faster
- Working with Events
- More resources
5. "Getting the Most Out Of Excel Part 1: Moving Data Around"
The first presentation in our "Actuarial Technical Toolbox" webinar series explores various methods for pulling data into Excel from external sources, as well as methods for saving data within Excel. In addition to explaining the processes with real-world examples, we will also review best practices for maintaining data fidelity and documentation for your spreadsheets.
The following scenarios will be explained in great detail:
- Importing data from Internet sources
- Importing and Exporting .txt files
- Importing and Exporting Excel VBA data
6. "Getting the Most Out Of Excel Part 2: Preventing & Fixing Errors"
The second presentation in our "Actuarial Technical Toolbox" webinar series. It reviews and explores Microsoft Excel’s built-in tools for tracing errors in Microsoft Excel and the Microsoft Excel Visual Basic (VBA) Editor. Comprehension of these tools are sure to make your spreadsheets bulletproof!
The following tools and best practices will be demonstrated in great detail:
- Debugging tools within the Microsoft Excel Visual Basic Editor
- Error-checking tools within Microsoft Excel
- Diagnosing formula errors
- Using data validation to prevent user errors.
7. "Getting the Most Out Of Excel Part 3: Add it Up with Data Aggregation"
The third presentation in our "Actuarial Technical Toolbox" webinar series. It takes a look at the different approaches for aggregating and slicing data in Microsoft Excel. You will leave with a comprehension of the various data tools available in Excel that will help you tease out patterns in your data. In addition to exploring their practical application, you will also understand the pros and cons of each tool/function so you can choose the best data aggregation approach based on your needs.
The following data tools are demonstrated in great detail:
- Data Tables
- How they work, and why you may wish to use them.
- Pivot Tables
- The “original” Excel tool used to slice and dice your data. We will explore in detail how calculated fields work.
- Aggregating Formulas
- SUMIFS and other Excel functions
- Array Formulas
- More Excel functions
Multi-Participant Customers: ACTEX will provide a self-registration portal and activation key for your participants. This new system will make it easy to distribute this Professional Series to your participants without the need for rigorous administrative attention. Once you have completed the purchase, ACTEX will send you the URL for the registration site and the key for the number of participants.
Please Note: Participants must agree to the ACTEXeLearning.com Site Policy in order to access this webinar. All prices are in USD.
No Refunds or Cancellations are allowed with this product.
EA Credit Information:
The Joint Board for the Enrollment of Actuaries (JBEA) has approved ACTEX as a qualifying sponsor of continuing professional education (CPE) programs for enrolled actuaries.
ACTEX believes in good faith that you may earn continuing professional education (CPE) non-core non-ethics credits under the Joint Board for the Enrollment of Actuaries (JBEA) rules for attending this webinar. The JBEA makes the final determination about what constitutes core, non-core, ethics, or non-ethics CPE and the number of CPE credit hours allocated.