The article will show you how to model **salary regression analysis **in **Excel**. If you are an employer, you must follow some pattern for your employees’ salaries. Because if you don’t, there will be a discriminatory distribution of money which may cause you a massive amount of loss in the future. Here, I’ll help you to create a salary structure model using **regression analysis **in Excel. Let’s tune into the description below.

## Download Practice Workbook

## 2 Practical Ways to Model Salary Regression Analysis in Excel

In this article, I’ll show you two ways of modeling a **salary regression analysis**: **Linear **and **Exponential regression**. The following picture shows an approximate model of a salary structure, which is based on employees’ experience.

We will use the **Regression Analysis **tool to model a salary structure which will be more effective for business.

**1. Linear Salary Regression Analysis in Excel**

Suppose, you want to create a salary structure for the employees based on their experiences. You made an approximate model but now you want this to be modeled by **Linear Regression Analysis**. Let’s go through the following description for a better understanding.

- First, enable
**Data Analysis Toolpak**from Excel**Add-ins**if you don’t have it in the**Data Tab**. For that reason, go to the**File**menu first.

- After that, select
**Options**.

- Next, select
**Add-ins**from the**Excel Options** - Thereafter, select
**Excel Add-ins**from the**Manage**sections and click on**Go**.

- Later, check
**Analysis Toolpak**and click**OK**.

- After that, you will see the
**Data Analysis**ribbon in the**Data Tab**. Click on it.

- Next, the
**Data Analysis**dialog box will appear. Select**Regression**and click**OK**.

- Thereafter, in the
**Regression**window, insert the**Y**and**X variables**. As the salaries are dependent on experience, salary will be the**Y values**and experience will be the**X values**. That’s why we put the salary range (**C5:C14**) and experience range (**D5:D14**) in the**Input Y Range**and**Input X Range**sections respectively. - Make sure you check
**Line Fit Plots**and**Labels**. This will greatly help us to model the salary structure. We want the analysis data in a new sheet, so we selected**New Worksheet Ply**. - Finally, click
**OK**.

- After this command, you will see the
**regression analysis**in a new sheet. You can see there are some regression parameter values in the analysis sheet. But most importantly, we need the analysis data from the graph. It plots both the**Series Data**(approximate salaries) and**Predicted Data**(salaries for modeling). So we won’t be discussing the other values (**Coefficients**,**Standard Error**,**t Stat**, ) here. - Give your graph a name if you want. I named this chart
**Linear**.

- Later, we copy the chart and paste it into the salary model sheet.
- After that, right-click on any of the points of this chart and select
**Add Trendline**.

- The
**Format Trendline**window will appear next. Select**Linear**and check**Display Equation on Chart**.

- Thereafter, you will see the straight line plot along with the equation for this regression model.

- Next, develop a formula using this equation to model the salaries. For that purpose, we create a new column to store the
**Modeled Salaries**and type the following formula in cell**E5**, and press**ENTER**. This will provide the salary for an employee with**0**years of experience.

`=951.67*D5-16.667`

Of course, you will have to pay a fresher. This model just predicts values based on the given data. But for the rest, you will have an effective output.

- After that, use the
**Fill Handle**to**AutoFill**the lower cells.

Thus you can model **linear salary regression analysis **in Excel.

**Read More:** **How to Calculate Basic Salary in Excel (3 Common Cases)**

**Similar Readings**

**How to Calculate Prorated Salary in Excel (3 Suitable Ways)****Leave Salary Calculation in Excel (With Easy Steps)****How to Calculate Income Tax on Salary with Example in Excel****Daily Wages Sheet Format in Excel (with Quick Steps)****Per Day Salary Calculation Formula in Excel (2 Suitable Examples)**

**2. Exponential Salary Regression Analysis in Excel**

Suppose, you want to model the salary structure by the grades of the employees. Just like the previous method, you made an approximate salary structure and now you want this modeled by the **Exponential Regression Analysis**. Let’s have a look at the dataset first and then proceed to the process.

**Steps:**

- First, create a
**regression analysis**table like we did in**Method 1**. - Later, we copy the chart and paste it into the salary model sheet.
- After that, right-click on any of the
**Series Data**points of this chart and select**Add Trendline**.

- The
**Format Trendline**window will appear next. Select**Exponential**and check**Display Equation on Chart**.

- Thereafter, you will see the exponential plot along with the equation for this regression model.

- Next, develop a formula using this equation to model the salaries. For that purpose, we create a new column to store the
**Modeled Salaries**and type the following formula in cell**D5**, and press**ENTER**. This will provide the salary for an employee with**0**years of experience.

`=56164*EXP(-0.295*C5)`

The formula uses **the EXP function** which returns the value of the Euler’s Number’s power of any number.

- After that, use the
**Fill Handle**to**AutoFill**the lower cells.

Thus you can model **exponential salary regression analysis **in Excel.

**Read More:** **How to Create a Salary Increase Matrix in Excel (With Easy Steps)**

## Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

## Conclusion

In the end, we can conclude that you will learn some practical ways to model employee salary structure by applying **regression analysis **in Excel. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website **ExcelDemy**.

**Related Articles**

**How to Calculate DA on Basic Salary in Excel (3 Easy Ways)****Create a Monthly Salary Sheet Format in Excel (with Easy Steps)****How to Create Salary Slip Format with Formula in Excel Sheet****Create Tally Salary Slip Format in Excel (With Easy Steps)****How to Calculate Salary Increase Percentage in Excel****How to Calculate Annual Salary in Excel (with Detailed Steps)**