Creating a Dynamic Incentive Modeling Tool

Effective incentive modeling is critical for the success of any incentive plan. Untested incentive models can lead to affordability issues, overpayment or underpayment, insufficient differentiation, and lack of motivation. Excel provides powerful tools and techniques for Modeling incentives, but you need to know how to use them effectively.

In this course, Excel guru, Dianne Auld will guide you through key areas of incentive modeling in Excel, such as eligibility, measures, performance targets, hurdles, weightings and payout levels, and outline guidelines for good incentive model design. Moreover, Auld will construct an Incentive Modeling tool for a company-wide incentive bonus plan.

The Modeling tool will empower you to:

• Model various measure weightings, performance targets, payout levels, hurdles, eligibility and target achievement levels.
• Experiment with different incentive parameters to minimize incentive payout moderation to the incentive pool.  

Auld will demonstrate the creation of interactive tables, charts and data tables to illustrate payout costs for different business areas and levels, payout distribution and incentive pool moderation required. You will be provided with a partly completed template for hands-on practice, along with replication instructions for all demonstrated functions and the completed incentive modeling tool. Additionally, you’ll have access to a learning video for 120 days post-course with detailed instructions for replicating demonstrated functions.

What You Will Learn:

• How to create an interactive Incentive Modeling tool
• Spin buttons, Data Validation, Vlookup and If functions to model different eligibility and target achievement levels
• Name ranges to make formulas easier to write, understand and edit
• Tables and structured table references to allow for possible increases in employee numbers, and to make formulas easier to write and understand
• How to set up flexible performance – payout tables linked to the incentive Modeling tool using the Rows function
• How to calculate employee incentive payout amounts before and after modification using Vlookup, If, If(And) and Sumproduct functions, referencing the parameters set up in the incentive Modeling tool
• Set up a bonus pool linked to EBIT and a modifier to ensure that incentive payout costs do not exceed the bonus pool
• Use the What-if Analysis tools, Goal Seek and Data Tables, together with conditional formatting to model the impact of alternative incentive options
• Create interactive tables and charts showing payout costs by business area and level, using Sumifs and Averageif functions
• Create an interactive table and chart showing % of target payout distribution for eligible employees using dynamic array Frequency and Filter functions
• Add a shape and linked information to the chart using the Text function

Additional Information

This course is ideal for HR professionals, compensation specialists or anyone seeking to create a dashboard to help tell a story regarding HR-related metrics. The course requires an intermediate knowledge of Excel, you must be familiar with designing and/or managing short-term incentive programs. You should already work with HR, Rewards or payroll data in Excel regularly, and be experienced with writing Excel formulas and creating charts.

Based in India? Contact india@worldatwork.org for India-special course pricing.

Related Courses

This learning experience is part of WorldatWork’s suite of Excel mastery courses, which include:

• Advanced Excel Skills for Compensation Professionals
  
• Analyzing & Automating Rewards Data with Excel
• Designing Powerful Dashboards
  
• Dynamic Arrays: Excel Turbo Charged
• Excel Skills for Compensation Professionals
• Hello Excel Power Query, My New BFF
• Job Titles & Grades: Creating an Interactive Matrix in Excel
• Regression Analysis Made Easy with Excel
• Working Smarter & Faster with Excel Formulas

Policies

View our Return/Cancellation policies and more here.