Sales compensation modeling and analytics in Python - Incentius Blog Home - Incentius - Enable Digital Transformation [![Logo](/img_astro/incentius-logo.svg)](/) Solutions About [Case Studies](/portfolio/) [Blogs](/blog-posts/) [Career](http://aurochs.freshteam.com/jobs) Open main menu ![Menu icon](/img_astro/menu1.png) [Get In Touch](/contact/) Solutions [![](/img_astro/generative-black.svg) ![](/img_astro/brain-green.svg) Voice AI](/voice-ai/) [![](/img_astro/product-development-black.svg) ![](/img_astro/product-development.svg) Product Development](/product-engineering/) [![](/img_astro/generative-black.svg) ![](/img_astro/brain-green.svg) Generative AI](/generative-ai/) [![](/img_astro/data-black.svg) ![](/img_astro/database-green.svg) Data Engineering](/data-engineering/) [![](/img_astro/mvp-black.svg) ![](/img_astro/rocket-green.svg) Data & Analytics](/data-analytics/) [![](/img_astro/cloud-black.svg) ![](/img_astro/computing-green.svg) DevOps & CloudOps](/devops-and-cloudops/) About Us [![](/img_astro/product-black.svg) ![](/img_astro/product-green.svg) About Us](/about-us/) [![](/img_astro/team_black.svg) ![](/img_astro/team_green.svg) Team](/team/) Solutions ![](/img_astro/dropdown-arr.svg) [ ![](/img_astro/generative-black.svg) Voice AI ](/voice-ai/)[ ![](/img_astro/product-development-black.svg) Product Development ](/product-engineering/)[ ![](/img_astro/generative-black.svg) Generative AI ](/generative-ai/)[ ![](/img_astro/data-black.svg) Data Engineering ](/data-engineering/)[ ![](/img_astro/mvp-black.svg) Data & Analytics ](/data-analytics/)[ ![](/img_astro/cloud-black.svg) DevOps & CloudOps ](/devops-and-cloudops/) [Blogs](/blog-posts/) About ![](/img_astro/dropdown-arr.svg) [ ![](/img_astro/product-black.svg) About Us ](/about-us/)[ ![](/img_astro/team_black.svg) Team ](/team/) [Case Studies](/portfolio/) [Career](http://aurochs.freshteam.com/jobs) [Get In Touch](/contact/) Sales compensation modeling and analytics in Python Sujeet Pillai 1. Jan 09, 2014 2. 4 min read ![](/_astro/Sales-compensation-analytics-using-python-sca.width-1000_cfUs7.webp) ## Introduction ![](/img/blog/modeling-a-simple-compensation-plan-in-python/python.png) Over the past few years, python has grown into a serious scientific computing language. This owes itself to several mathematical packages like NumPy, scipy, and pandas maturing and being trusted by the scientific community. These packages and the inherent simplicity of python had a democratizing effect on the ability to perform complex mathematical modeling without expensive proprietary software. In this blog post, I wish to utilize these techniques that are commonplace in the scientific community to try and apply them to the sales compensation/analytics domain. I'm going to attempt to model a basic sales compensation plan using NumPy and python. The intent is to keep the model simple and to get it up and running fast. Future blog posts will introduce more complex elements and more advanced modeling techniques. The hope is that this basic model will demonstrate the power of these python-based modeling techniques and their simplicity. ## The Interface Firstly, let's settle on an input/output interface. I believe business users are still some way off from setting parameters/data in formats like JSON/XML/YAML. This eliminates those possibilities. As most of us are used to Excel (more than we like to admit), I'd like to keep the input/output interface as Excel. I'd like the model to read inputs and parameters from Excel and write the final results into an output sheet on Excel. Python luckily has an excellent package called [openpyxl](http://pythonhosted.org/openpyxl/) that allows it to interface with an Excel file (Excel 2007 onwards only). We'll use this package to read our inputs and parameters and to write the outputs of our model. [DataNitro](https://datanitro.com/) and [Pyxll](https://www.pyxll.com/) are two other python packages you can use for interfacing with Excel; however, they run as an addition to Excel rather than as an independent python package. ## The Plan I've chosen a very simple Achievement vs Attainment Payout Table for the purposes of this model. I've created a named range "PayoutTable" in my excel file (named "plan\_modeling.xlsx") containing this payout table. I'm going to assume a simple payout table with linearly interpolated attainment between two subsequent performance levels. Below is a graphical look at my payout curve. The slope of the line above the highest performance level defined in the table is determined by the "Infinity Rate" Parameter ![Curve](/img/blog/modeling-a-simple-compensation-plan-in-python/Curve.jpg) The Target Incentive Dollars for the given class of reps is defined using the "TIC" parameter. Earnings for the plan will be calculated as the attainment as calculated using the payout table based on the achievement times of the Target Incentive. ## Simulation Parameters To simulate the performance of a salesforce on this plan I'm going to use a normal distribution of achievements around some mean and standard deviation. These parameters are defined in my Excel file as "AverageAchievement", and "StdDevAchievement". The sample size of the population of sales reps that I use to model this plan is defined by the "SampleSize" parameter. Overall the plan and simulation parameters section looks like the following. ![Inputs](/img/blog/modeling-a-simple-compensation-plan-in-python/Inputs.jpg) ## Model Setup Alright! Time to write some code!! First, let's import the required packages. Numpy and openpyxl #!python import numpy import openpyxl from openpyxl.style import NumberFormat I've created two helper packages. openpyxl\_helpers has methods for frequently used interactions with the excel file using the openpyxl library. CompUtils sets up a Rate\_Table class to maintain a payout table and has a method to apply the rate table to an achievement distribution #!python import CompUtils from openpyxl\_helpers import \* Open the workbook using openpyxl #!python # Open workbook wb = openpyxl.load\_workbook('plan\_modeling.xlsx') Pull in all the plan and simulation parameters from the excel file using their named ranges. #!python # Read plan parameters payout\_table\_range = read\_table\_from\_named\_range(wb,'PayoutTable') infinity\_rate = read\_value\_from\_named\_range(wb,'InfinityRate') TIC = read\_value\_from\_named\_range(wb,'TIC') # Read simulation parameters average\_achievement = read\_value\_from\_named\_range(wb,'AverageAchievement') std\_achievement = read\_value\_from\_named\_range(wb,'StdDevAchievement') sample\_size = read\_value\_from\_named\_range(wb,'SampleSize') Next, we'll initialize the Rate\_Table class from our helper package CompUtils. We'll add all rows from the payout\_table\_range that we read from the excel file and then set the infinity rate parameter. The Rate\_Table class expects the lookup field to be called 'start' and the value field to be called 'base'. Since we've used 'achievement' and 'attainment' instead, we'll override those names by passing startKey and base key. #!python # Initialize Rate Table payout\_table = CompUtils.Rate\_Table() payout\_table.add\_row\_list(payout\_table\_range,startKey='achievement',baseKey='attainment') payout\_table.set\_infinity\_rate(infinity\_rate) Finally, all our parameters have been retrieved, our rate table has been set up. Now we're ready to do some modeling. Let's first generate the achievement sample using our simulation parameters. We'll use the [numpy.random.normal](http://docs.scipy.org/doc/numpy/reference/generated/numpy.random.normal.html) function. #!python # Populate the achievement sample achievement\_distribution = numpy.random.normal(average\_achievement,std\_achievement,sample\_size) Now calculate the attainment for this achievement sample by applying the rate table to it. #!python # Calculate Attainment based on the Payout Table attainment = CompUtils.calculate\_lookup\_attainment(achievement\_distribution,payout\_table.rows) Calculate earnings by multiplying the attainment values against TIC. Note that the attainment is a numpy.ndarray and is being multiplied by a scalar TIC. Numpy allows us to do such multiplications simply and it inherently understands how to handle such an operation. #!python # Calculate Earnings as Attainment times Target Incentive earnings = attainment \* TIC And that's it. We've already calculated earnings for all reps in the sample. And it took all 3 lines of code for the full model!! Now let's write some of our results out in excel. #!python # Create an output sheet for us to write to output\_ws = wb.create\_sheet(title='Output') # Write Achievement, Attainment and Earnings into columns write\_list\_of\_values(output\_ws,'A1',list(achievement\_distribution),'Achievement',number\_format=NumberFormat.FORMAT\_PERCENTAGE\_00) write\_list\_of\_values(output\_ws,'B1',list(attainment),'Attainment',number\_format=NumberFormat.FORMAT\_PERCENTAGE\_00) write\_list\_of\_values(output\_ws,'C1',list(earnings),'Earnings',number\_format=NumberFormat.FORMAT\_CURRENCY\_USD\_SIMPLE) That gave us the raw values of the achievement sample, attainment, and earnings of that sample. Now let's create some more useful analytical tables. The openpyxl\_helpers package has a couple of predefined methods to help create a quick distribution table and some statistics in a stats table. Let's add those to our output sheet now. #!python # Create a Distribution Table create\_distribution\_table(output\_ws,'E1',earnings,number\_format=NumberFormat.FORMAT\_CURRENCY\_USD\_SIMPLE) # Create a Stats Table create\_stats\_table(output\_ws,'I1',earnings,number\_format=NumberFormat.FORMAT\_CURRENCY\_USD\_SIMPLE) Finally, let's write the output to a new file. #!python # Write the output to a new file wb.save(filename='/home/sujeet/Desktop/plan\_modeling\_simulated.xlsx') Our newly generated output file should have a new sheet called "Output" with data like the below screenshot. The stats table gives us some useful metrics, and the distribution table can be used to plot a bell curve of the rep's earnings. ![Outputs](/img/blog/modeling-a-simple-compensation-plan-in-python/Outputs.jpg) ## The tip of the iceberg This is a very basic model. My intent was only to display how painless it is to use python, NumPy, etc., to create a basic [sales compensation](https://incentivate.in/blog/what-is-incentive-compensation/)/analytics model. This code can be adapted to perform analysis on a complex model just as easily. Imagine the potential in quota-setting models, territory alignments, call planning, etc. You can get the code for this whole model, including the helper packages, at [sujeetpillai/simple\_python\_comp\_model](https://github.com/sujeetpillai/simple_python_comp_model) Incentius can help create, maintain, and tweak such models to achieve your business goals. This is a zone that we're very excited about, and we can't wait to help you realize the potential for such techniques! How did you like this blog post? Let us know in the comments below or on our social media pages. If you got this far, we think you’d like our future blog content, too. Please subscribe on the right side. About Author ![](/_astro/Sujeet_Pillai.width-300_2fQenu.webp) Sujeet Pillai ![](/img_astro/thumb.png) As an experienced polymath, I seamlessly blend my understanding of business, technology, and science. ### See What Our Clients Say ![](/img_astro/left-chevron_1.svg) ![](/img_astro/left-chevron_2.svg) ![](/img_astro/mind-gap-crop.svg) Mindgap Incentius has been a fantastic partner for us. Their strong expertise in technology helped deliver some complex solutions for our customers within challenging timelines. Specific call out to Sujeet and his team who developed custom sales analytics dashboards in SFDC for a SoCal based healthcare diagnostics client of ours. Their professionalism, expertise, and flexibility to adjust to client needs were greatly appreciated. MindGap is excited to continue to work with Incentius and add value to our customers. Samik Banerjee Founder & CEO ![](/img_astro/trusted-by-businesses/worldatwork_coloured.webp) World at Work Having worked so closely for half a year on our website project, I wanted to thank Incentius for all your fantastic work and efforts that helped us deliver a truly valuable experience to our WorldatWork members. I am in awe of the skills, passion, patience, and above all, the ownership that you brought to this project every day! I do not say this lightly, but we would not have been able to deliver a flawless product, but for you. I am sure you'll help many organizations and projects as your skills and professionalism are truly amazing. Shantanu Bayaskar Senior Project Manager ![](/img_astro/trusted-by-businesses/gogla_coloured.webp) Gogla It was a pleasure working with Incentius to build a data collection platform for the off-grid solar sector in India. It is rare to find a team with a combination of good understanding of business as well as great technological know-how. Incentius team has this perfect combination, especially their technical expertise is much appreciated. We had a fantastic time working with their expert team, especially with Amit. Viraj gada Gogla ![](/img_astro/trusted-by-businesses/humblex_coloured.webp) Humblx Choosing Incentius to work with is one of the decisions we are extremely happy with. It's been a pleasure working with their team. They have been tremendously helpful and efficient through the intense development cycle that we went through recently. The team at Incentius is truly agile and open to a discussion in regards to making tweaks and adding features that may add value to the overall solution. We found them willing to go the extra mile for us and it felt like working with someone who rooted for us to win. Samir Dayal Singh CEO Humblx ![](/img_astro/mind-gap-crop.svg) Transportation & Logistics Consulting Organization Incentius is very flexible and accommodating to our specific needs as an organization. In a world where approaches and strategies are constantly changing, it is invaluable to have an outsourcer who is able to adjust quickly to shifts in the business environment. Transportation & Logistics Consulting Organization Consultant ![]() Mudraksh & McShaw Incentius was instrumental in bringing the visualization aspect into our investment and trading business. They helped us organize our trading algorithms processing framework, review our backtests and analyze results in an efficient, visual manner. Priyank Dutt Dwivedi Mudraksh & McShaw Advisory ![](/img_astro/mind-gap-crop.svg) Leading Healthcare Consulting Organization The Incentius resource was highly motivated and developed a complex forecasting model with minimal supervision. He was thorough with quality checks and kept on top of multiple changes. Leading Healthcare Consulting Organization Sr. Principal ![](/img_astro/mind-gap-crop.svg) US Fortune 100 Telecommunications Company The Incentius resource was highly motivated and developed a complex forecasting model with minimal supervision. He was thorough with quality checks and kept on top of multiple changes. Incentive Compensation Sr. Director Most Read [ ![](/_astro/ChatGPT_Image_Apr_2_2025_12_06_35_PM.width-1000_ZUChn3.webp) Optimizing Large-Scale Data Exports: FastAPI, GraphQL, and AWS S3 Struggling with slow, inefficient file downloads in your application? Generating and handling large file downloads can be a nightmare—UI freezing, server overload, and endless waiting. ![](/_astro/Vinay_Chaudhari.width-300_1QPJnB.webp) Vinay Chaudhari 1. Apr 08, 2025 2. 4 min read ](/blog-posts/optimizing-large-scale-data-exports-fastapi-graphql-and-aws-s3/)[ ![](/_astro/Screenshot_2025-04-02_at_12.09.33_PM.width-1000_ZyU6y1.webp) How to Build a Web Search Agent with Phidata: A Step-by-Step Guide Phidata is an **open-source framework** for building AI agents with capabilities like memory, tool use, and reasoning​ It’s model-agnostic and supports various large language models (LLMs), allowing developers to turn any LLM (like OpenAI GPT or local models) into a functional **search agent**. ![](/_astro/Kartik_Puri.width-300_1YNCyp.webp) Kartik Puri 1. Apr 02, 2025 2. 4 min read ](/blog-posts/how-to-build-a-web-search-agent-with-phidata-a-step-by-step-guide/)[ ![](/_astro/e20f5335-cbf6-414c-a096-7d2849109cff.width-1000_15DfBS.webp) Migrating to Soketi: Improving Our Real- Time Communication Architecture Our journey with real-time data communication has evolved significantly. Initially using Flask-SocketIO with Vue, we faced several challenges that led us to seek a more robust solution. ![](/_astro/pratik.width-300_Z2vB9eu.webp) Pratik Patel 1. Mar 17, 2025 2. 4 min read ](/blog-posts/migrating-to-soketi-improving-our-real-time-communication-architecture/)[ ![](/_astro/startup-employee-looking-business-charts-usin.width-1000_2knrAp.webp) Understanding AI Agents: A Comprehensive Guide to AI Agentic Workflows In the rapidly evolving landscape of artificial intelligence, AI Agents have emerged as a transformative technology that represents far more than just industry jargon. ![](/_astro/chetan_piucv.width-300_ZK3Vor.webp) Chetan Patel 1. Feb 28, 2025 2. 4 min read ](/blog-posts/understanding-ai-agents-a-comprehensive-guide-to-ai-agentic-workflows/) ## We use cookies We use cookies to improve site. Some cookies are necessary for our website and services to function properly. Other cookies are optional and help personalize your experience, including advertising and analytics.You can consent to all cookies or decline all optional cookies. Without a selection, our default cookie settings will apply. I agree I decline [![logo](/img_astro/logo.svg)](/) Incentius is a technology and professional services company providing analytics, operations and product engineering services. [![facebook](/img_astro/facebook.svg)](https://www.facebook.com/Incentius/)[![twitter](/img_astro/twitter.svg) ](https://x.com/incentius/)[![linkedin](/img_astro/linkedin.svg)](https://www.linkedin.com/company/incentius/posts/?feedView=all) ![location](/img_astro/pin.svg) Office No.201B, 2nd Floor, Beta-1, Giga Space IT Park, Viman Nagar, Pune - 411014, Maharashtra, India ![email](/img_astro/message-icon.svg) info@incentius.com ![phone](/img_astro/telephone.svg) +1 (302) 415 3208 ![](/img_astro/secure-footer.svg) ![](/img_astro/secure-footer-mobile.svg) * Solutions * [Voice AI](/voice-ai/) * [Generative AI](/generative-ai/) * [Data Engineering](/data-engineering/) * [Product Development](/product-engineering/) * [Data & Analytics](/data-analytics/) * [DevOps & CloudOps](/devops-and-cloudops/) * [UI/UX Design](/ui-ux-design/) * Recent Blog * [Medallion Architecture in Practice: Six Layers, One Source of Truth](/blog-posts/medallion-architecture-in-practice-six-layers-one-source-of-truth/) * ["Document later" is how teams say "document never"](/blog-posts/document-later-is-how-teams-say-document-never/) * [Your Business & AI - What It Actually Means for You](/blog-posts/your-business-ai-what-it-actually-means-for-you/) * [How Internal Agent-Built Software Reduced Our SaaS Dependency](/blog-posts/how-internal-agent-built-software-reduced-our-saas-dependency/) * [MarkItDown: A Powerful Utility for File Conversion to Markdown](/blog-posts/markitdown-a-powerful-utility-for-file-conversion-to-markdown/) * [Python 3.14: A Game-Changing Release for Modern Development](/blog-posts/python-314-a-game-changing-release-for-modern-development/) * [Building a RESTful Blog API with Flask and Swagger: A Complete Guide to Streamlined API Development](/blog-posts/building-a-restful-blog-api-with-flask-and-swagger-a-complete-guide-to-streamlined-api-development/) * [Tailwind CSS 4.0 vs 3.x: What Changed and How We Implemented It](/blog-posts/tailwind-css-40-vs-3x-what-changed-and-how-we-implemented-it/) * [The Future of AI: What to Expect in the Next 5 Years](/blog-posts/the-future-of-ai-what-to-expect-in-the-next-5-years/) * Menu * [About](/about-us/) * [Blog](/blog-posts/) * [Career](http://aurochs.freshteam.com/jobs) * [Case Studies](/portfolio/) * [For Startups](/startups/) * [Let's Talk](/contact/) © Copyright 2025 Incentius [Request Site](/) [Terms and Conditions](/) [Privacy Policy](/privacy-policy/)