Advancing Your Modeling Capabilities with the Power of Excel

Course

A Skill-Building Course with Compensation and Excel Expert Dianne Auld

In compensation work, modeling of different scenarios is critical for good plan design and for accurate costing of compensation and benefits options. All proposals for adjustments to base pay, incentives and benefits must consider the cost of alternative options, the impact on employees and the impact on market competitiveness.

However, insufficient modeling by compensation professionals is common, often due to lack of time or lack of expertise. In fact, one of the major causes of incentive plan failure is inadequate modeling of data.

Data for modeling can typically be drawn from the organization's payroll and other databases, but the modeling itself is mostly done in Excel. It is therefore critical for compensation professionals to master the skill of modeling data in Excel.

In this skill-building class, the instructor will demonstrate how Excel tools and formulas can be harnessed for powerful modeling of different scenarios:

  • Impact on market positioning and increase cost of different base pay increases per level
  • Impact on costs of stock grant eligibility, share allocation numbers and stock price
  • Impact of setting different incentive target levels on payout distribution patterns

All completed models provide immediate and striking visual impact of choices made, using tables, icons and charts, so are ideal for interactive sessions with the C-suite.

Extra Value! Take home session files and instructions for modeling the scenarios demonstrated in class.

What You Will Learn:

Section 1 — Impact on Market Positioning and Increase Cost of Different Base Pay Increases per Level

  • Modeling the impact of different base pay increases per level in the organization on average comparative ratios to market and on cost of increase.
    • Link increase percentages to the compa ratio and cost calculations using VLOOKUP, AVERAGEIF, AVERAGE and SUM formulas.
    • Set conditional formatting on the compa ratios to highlight variance from targeted market position per level.
    • Add in and use Solver to obtain the right merit increase budget while keeping compa ratios to market within the desired range.

Instructions will be provided on how to create a linked chart.

Section 2 — Impact on Costs of Stock Grant Eligibility, Share Allocation Numbers and Stock Price

  • Modeling the impact of different eligibility levels, share allocation numbers and stock price on cost of stock granted, cost of stock granted as a percent of base pay and shares granted as a percentage of shares outstanding.
    • Set up a spin button to model eligibility level.
    • Link the eligible level, number of shares allocated per level and stock price, to the total share number and cost calculations using IF, AVERAGEIF, COUNTIF and SUM formulas.

Instructions will be provided on how to create a linked chart.

Section 3 - Impact of Setting Different Incentive Target Levels on Payout Distribution Patterns

  • Modeling the impact of setting different incentive target levels on payout distribution pattern and average incentive payout. Demonstrating different methods of determining the distribution pattern of a set of data.
    • Set up a scroll bar to model different incentive target levels.
    • Link the incentive target levels to the incentive payout analysis.
    • Calculate payout distribution patterns, dependent on targets, using ARRAY and FREQUENCY formulas.
    • Learn three other methods of calculating a distribution pattern using a pivot table, using the COUNTIFS formula and using the Data Analysis and Histogram function.

Instructions will be provided on how to create a linked chart.

Learning Options

Virtual Classroom

Live online instructor-led delivery of course materials including real-time interaction with a subject-matter expert and peers — no travel required!

Course

Member: $395 USD

$525 USD

Instructor

Dianne Auld

Excel Expert Instructor
Auld Compensation Consulting

Dianne Auld, CCP, GRP, CSCP, WLCP owns a consulting practice, Auld Compensation Consulting, in Cape Town, South Africa. She consults a wide range of organizations across Africa and the Middle East in all areas of total rewards. She has developed and taught courses in Africa, the Middle East, Europe, and the United States.

Dianne is also a faculty member at WorldatWork and a reviewer for the WorldatWork Journal. Well respected and known as an expert in the compensation field and Excel, Dianne's skills and expertise are in high demand. In fact, her sessions at the WorldatWork Total Rewards Conference and Exhibition typically fill to capacity. Diane has partnered with WorldatWork to develop several educational products to assist compensation professionals with their day-to-day tasks in Excel, including "Pay Structures - Develop a Framework from Start to Finish," "Excel Skills for Compensation Professionals," "Advanced Excel Skills for Compensation Professionals," "Excel Tips for Compensation Professionals," and "Tackling Compensation's What If Questions Using Excel".

Course Credits & Certifications

  • Recertification

    0.25 credits

More Information

Who Should Register

Compensation practitioners or compensation managers with at least intermediate Excel skills.

Requirements

You should have a computer with Microsoft Excel 2010 or later in order to use the modeling tools and formulas demonstrated in class.

How to Register

Policies

Click Here to view our policies on payments, returns, class schedules, registration, cancellation, and more.

Have Questions?

Phone

+1 877 951 9191

USA and Canada

+1 480 951 9191

Other Countries

Online

Chat currently unavailable.

Email Us