How to Model a Compensation Plan in Excel

Ashley Williams

Customer Operations - Senior Manager

May 17, 2023

Ashley Williams, Senior Manager - Customer Operations

Most Incentive Compensation Management (ICM) software vendors don't want you to know that their software is useless for modeling.

While your software provider (or their consultant partners) may set you up to model one or two outcomes during your initial implementation year, your sales compensation team that will almost certainly revert to the comforting green sheets of Excel for any modeling — and probably many other ad-hoc reports too.

The reason is that most sales compensation software solutions do not offer the level of flexibility as the humble spreadsheet. And when it comes to the nuances of sales comp plan modeling, adaptability is critical.

It's one of the reasons we purpose-built Forma.ai to manage reporting and modeling at the level required by large enterprises.

For those who don't use our platform, this guide will show you how to model your sales compensation plan outcomes in Excel step-by-step. And if you want to see how our customers can model thousands of scenarios at the click of a button, book a call with one of our experts here.

Design Your Sales Compensation Plan First

Before we dive into the mechanics, you should have completed the incentive plan design phase, including:

Setting metrics that are strategically aligned with the business objectives/priorities and market best practices
Deciding on the overall incentive plan structure (e.g., target pay, performance measures, weights, measurement, period, frequency, etc.)
Designing metric mechanics (e.g., tiered commission rate, bonus based on performance to target)
Ensuring chosen metrics are ready from a data standpoint (i.e., have accessible and reliable data that can be used to track and pay on)

If you want to dive deep into building a world-class sales compensation program, check out our free guide here.

Modeling Prevents Misalignment

Once you have determined all those elements, you are ready to cost model the incentive plan and assess the impact this plan will have on individuals' pay, the cost to the company, and whether it will motivate the right behaviors.

Invest the time in modeling as many scenarios as possible; Incorrectly modeling a plan or skipping this step in the design process can result in profound cost implications for the company and misaligned goals that can impact results and demotivate your sales team.

How to Model Incentive Compensation Plan Outcomes in Excel

Incentive plan modeling follows six steps to ensure your plan is effective and rewards your sales reps meaningfully.

Step 1: Data collection

First, we must collect all of the data that is relevant and informative to our model. Determine the period in which sufficient compensation and performance data exists — you want as much data as possible. One year of data is the absolute minimum required to predict performance with any degree of accuracy and to account for seasonality. The more data you can integrate into your model, the better.

Using actual historical compensation and performance data will allow you to compare the plan outcomes to current plan designs, ensuring any current plan gaps and misalignments are not an issue with the new plan and the results are as expected (i.e., those with the strongest performances are rewarded).

We suggest collecting the following:

Employee Details: Pull data for all individuals you want to include in the model, including but not limited to employees, territories, roles, teams, demographics, etc.
Compensation Data: All current compensation data for individuals to be modeled, including target compensation, historical incentive payouts, base salary, and guaranteed pay.
Performance Data: Pull in any data you will use to calculate payments under the incentive plan. Using historical performance as a proxy for future performance allows you to model actual scenarios vs. only looking at assumption-based ranges (e.g., at target, at a threshold, etc.)

NOTE: We can also use Monte Carlo simulation to model sales compensation data and forecast sales comp spend. To learn more, read this article on "Sales Comp Budget Forecasting using Monte Carlo Simulation."

Once you have gathered all this data, organize it into individual columns in your Excel spreadsheet.

Step 2: Define your model inputs

Create a section in your workbook for all the plan inputs you want to model. These inputs will be used to calculate the pay under the new plan and will be calibrated, adjusted, and refined to get the desired outcome.

Inputs can include but are not limited to:

Target Pay Mix (used if % split of base salary/target incentive is being modeled) - e.g., 70% base salary / 30% target incentive/variable compensation.
Target Metric Weights (target % of each metric/plan component) - e.g., 70% sales revenue commission and 30% Gross Margin Bonus.
Metric/Component Mechanics (tiered commission rates, payout curve structure, thresholds/targets, etc.). It is helpful to set up these as inputs so the various rates/tiers can be easily changed when calibrating the model.

Step 3: Model calculations

Once you have all the necessary data and plan inputs set up, you are ready to model the plan and calculate the new payouts for each individual using historical performance as a proxy for future sales performance.

The modeling must be dynamic, so we can instantly see the impact of any changes on outputs. We do this by linking everything back to the model inputs where it makes sense, using Excel formulas referencing the inputs section. That will allow you to adjust the inputs and refine the model outputs to ensure the incentive plan is designed effectively without further work.

At a high level, the structure of your calculations should include the following:

A vast number of variations and nuances are involved in making an effective sales compensation model. The above metrics are designed to get you started, but we strongly encourage you to review them and include additional metrics relevant to your business and goals.

Looking for an example? We made a simple spreadsheet to get you started:

Step 4: Model outputs

Create different outputs to aggregate and summarize new incentive plan results. That will help ensure the plan is structured correctly to align with overall outcomes. Review by role, individual (most significant increases and decreases in pay), and team or region, if appropriate.

The goals of this phase are:

Ensure average and top performers can adjust their performance to succeed under the new comp plan design.
Ensure who receives the earnings makes sense, given historical performance levels and your priorities around activities and behavior.
Review the aggregate to help assess the financial impact and ROI of the new incentive plans. It can be helpful to gross up the modeled population to the actual people to more accurately evaluate the expected cost.

Step 5: Refine and calibrate

This is where the "art" of sales compensation comes into play, where you adjust the plan inputs to determine how much they shift the outputs.

At this stage, all the work setting up the model to feed as many dynamic inputs as possible pays off. Adjusting the rate or the payout curve automatically results in the updated pay and updated outputs to review, so you can quickly see if your adjustments have the desired effect on outcomes.

For example: If most of the modeled population will earn double what they were previously, the payout curves/rates are likely too high, so reduce them and recalculate. Or, if the outputs show that lower performers will get the most significant pay increase, play with your thresholds or target inputs to find more appropriate payout levels.

Once you are satisfied with the model outputs, you can roll out the new incentive plans feeling more comfortable about the cost impact and alignment of the program to business objectives and priorities.

Step 6: Monitor continuously

This is where the modeling stops for many organizations, but they are missing a huge opportunity by doing so. We recommend our clients approach their comp plan modeling as an ongoing exercise, which turns the hypothetical model into a real-time measure of realized performance.

As data is produced throughout the year, update the inputs section of your model to see how actual performance is tracking against your modeled outcomes, adjusting the calculations to iron out any discrepancies you find. That will allow you to forecast end-of-year results with greater accuracy and make your model more reliable in the future.

Book a call with one of our sales compensation experts here to see how Forma.ai's platform allows our customers to perform infinite modeling and real-time optimization.