Incentive Compensation

In this data analysis project, we aim to meticulously examine the raw sales data of a pharmaceutical company and draw insights for company sales performance, employee incentive compensation.

In the pharmaceutical industry, the distribution of drugs does not follow a direct-to-customer model. Instead, products are channeled through a network of distributors ( Customer Reps ) operating in distinct regions or territories. These intermediaries play a pivotal role in facilitating the flow of pharmaceutical goods from manufacturers to end-users.

Incentive compensation serves as a strategic mechanism to acknowledge and reward reps based on their performance. Effective incentive compensation management involves the meticulous design and implementation of incentive schemes aimed at enhancing business outcomes. The effective management of incentive compensation entails the precise design and execution of incentive schemes intended to optimize business outcomes. This approach fosters a closer alignment between the endeavors of sales representatives and the overarching objectives and targets of the organization.

Choosing the right plan is crucial part of the IC analysis:

Goal-Based Plan : In a goal-based plan, incentive payouts are tied to the achievement of predefined goals. These goals can be tailored to individual roles, products, or market conditions.

Types of IC plans

MBO (Management by Objectives) Plan : An MBO plan links incentive compensation to the achievement of specific, predefined objectives. Objectives are set collaboratively between managers and employees, ensuring alignment with overall business goals. Payouts are determined based on an objective achievement scale, which quantifies the extent to which goals are met.

Evaluating which incentive compensation (IC) plans to use involves considering several guiding principles to ensure alignment with organizational goals, employee motivation, and overall effectiveness. Here are the key principles :

Alignment with Business Objectives : The IC plan should directly support the company’s strategic goals and objectives. Ensure that the incentives are driving the right behaviors that contribute to the company’s success.

Measurability : The performance metrics used to determine incentives should be clear, objective, and easily measurable. Ensure that the necessary data to measure performance is available and reliable.

Fairness and Transparency : The plan should be perceived as fair and equitable by all employees. Clearly communicate how the plans work, including how payouts are calculated and what is expected from employees.

Flexibility and Adaptability :The plan should be flexible enough to adjust to changing business conditions and objectives. Ability to tailor the plan to different roles, departments, or individual employee needs.

Cost-Effectiveness : The plan should be financially sustainable and provide a good return on investment. Maximize the motivational impact of each dollar spent on incentives.

Fiscally Responsible : Risk Mitigation: Design plans to reduce exposure to risks arising from forecast uncertainty and external factors. Budget Constraints: Align the plan with the organization’s financial constraints and objectives.

Our dataset contains data of a US Pharmaceutical Company

Load and Transform data into Power BI Desktop

The dataset encompasses the Wholesale-Retail Data of a Pharmaceutical Manufacturing Company. The detailed field description of the raw data is provided below.

Data Cleaning and Transformation using Power Query Editor :

The following steps were undertaken to ensure the dataset’s integrity. Remove anomalies, blanks, duplicates, inaccurate formatting, jumbled columns, etc.

• Correct column heading provided
• Remove anomalies, duplicates, inaccurate formatting, jumbled columns, etc.
• Filter out unwanted rows, handle missing values, and correct data entry errors.
• Correct data type is assigned to columns

Data Model Creation

The data set used contained both categorical ( Dimensional ) and quantitative ( Factual ) data.

Key points to distinguish between dimensions and facts in a data set :

Fact Table

1. A fact table contains quantitative data representing measurable metrics relevant to business operations.
2. These events can include sales, orders, transactions, latitude, longitude or any other business metrics that generate measurable data points.
3. Fact tables typically contain vast amounts of data due to the detailed nature of the captured events or transactions.

Dimension Table

1. A dimension table contains descriptive attributes that provide additional context to the data in the fact table.
2. Dimension tables are used for filtering and categorizing data, enabling more precise and meaningful analysis.
3. Dimension tables can have hierarchical structures, such as product categories or geographic regions, facilitating organized and structured analysis.

We build a data model where dimensions and facts are separated, then they are linked together by logical relationship to form a Star Schema. The star schema is a well-established data modeling method commonly used in relational data warehouses. This approach requires to categorize tables as either dimension tables or fact tables.

What is Star Schema?

We implement star schema under the model view of Power BI desktop.

In Power BI when you have a local model open, navigate to Model view. In the Data pane of Model view you’ll see options to select Tables or Model at the top of the pane, select Model to see Model explorer. In a star schema, queries can efficiently join the fact table with any combination of dimension tables to retrieve meaningful insights.

Star Schema

Steps to Guess the Cardinality Between Tables in Power BI

1. Understand Your Data:

• Review the tables and their columns to understand the data and the potential relationships.
• Identify primary keys (unique identifiers) and foreign keys (columns that refer to primary keys in other tables).

2. Inspect the Data:

• Examine the data in each table to see how the columns are populated.
• Look for unique values, duplicates, and patterns that indicate the relationship type.

3. Use the Manage Relationships Feature:

• Go to the “Model” view in Power BI.
• Click on “Manage Relationships” to open the relationships management window.

4. Automatic Detection:

• Power BI can automatically detect relationships between tables. It uses algorithms to suggest relationships and their cardinality based on the data.
• You can view these suggestions in the “Manage Relationships” window and decide if they are accurate.

5. Manual Configuration:

• If automatic detection is not accurate, you can manually configure the relationships.
• In the “Manage Relationships” window, click “New” to create a new relationship or select an existing relationship to edit it.
• Specify the tables and columns that form the relationship.

6. Set the Cardinality:

Below described cardinalities can be used to connect dimensions and measures tables.

1 : * (One-to-Many): One record in the first table relates to multiple records in the second.

* : 1 (Many-to-One): Multiple records in the first table relate to one record in the second.

1:1 (One-to-One): One record in the first table relates to one record in the second.

* : * (Many-to-Many): Multiple records in the first table relate to multiple records in the second, often requiring a junction table to implement.

Relationships and Cardinality in the Data Model

1. Country — Dimension to City — Dimension:

• Cardinality: One-to-Many (1:*)
• Explanation: Each Country ID in the Country — Dimension table relates to multiple City IDs in the City — Dimension table.

2. City — Dimension to Sales — Facts:

• Cardinality: One-to-Many (1:*)
• Explanation: Each City ID in the City — Dimension table relates to multiple records in the Sales — Facts table.

3. Customer — Dimension to Sales — Facts:

• Cardinality: One-to-Many (1:*)
• Explanation: Each Customer ID in the Customer — Dimension table relates to multiple records in the Sales — Facts table.

4. Distributor — Dimension to Sales — Facts:

• Cardinality: One-to-Many (1:*)
• Explanation: Each Distributor ID in the Distributor — Dimension table relates to multiple records in the Sales — Facts table.

5. Month — Dimension to Sales — Facts:

• Cardinality: One-to-Many (1:*)
• Explanation: Each Month ID in the Month — Dimension table relates to multiple records in the Sales — Facts table.

6. Product — Dimension to Sales — Facts:

• Cardinality: One-to-Many (1:*)
• Explanation: Each Product ID in the Product — Dimension table relates to multiple records in the Sales — Facts table.

7. Sales-rep — Dimension to Sales — Facts:

• Cardinality: One-to-Many (1:*)
• Explanation: Each Sales_rep_IDs in the Sales-rep — Dimension table relates to multiple records in the Sales — Facts table.

8. Sales-team — Dimension to Sales-rep — Dimension:

• Cardinality: One-to-Many (1:*)
• Explanation: Each Sales_team_ID in the Sales-team — Dimension table relates to multiple Sales_rep_IDs in the Sales-rep — Dimension table.

9. Subchannel — Dimension to Sales — Facts:

• Cardinality: One-to-Many (1:*)
• Explanation: Each Subchannel ID in the Subchannel — Dimension table relates to multiple records in the Sales — Facts table.

10. Channel — Dimension to Subchannel — Dimension:

• Cardinality: One-to-Many (1:*)
• Explanation: Each Channel ID in the Channel — Dimension table relates to multiple Subchannel IDs in the Subchannel — Dimension table.

Report Creation

This report provides an in-depth analysis of the company’s sales team performance, focusing on granular details at both the product class and specific product levels. The objective is to uncover key insights that can inform strategic decisions, optimize sales efforts, and enhance overall profitability.

Thanks for reading :)