Quick Salary Calculation By Using Goal Seek in Microsoft Excel

If you are a Payroll Manager and you want to pay $50,000 Net Salary to your employee and you just have only Percentages of House Rent Allowance, Dearness Allowance, Medical Allowance, Vehicle Allowance, Provident Fund Contribution rate, Withholding Tax rate deduction etc but you don't have Basic Pay and Gross Pay. So Don't worry this is not a rocket science if you are using Microsoft Excel.

Because in Microsoft Excel, we can quickly calculate your desired net salary by using Goal Seek Analysis Tool.

What is Goal Seek?

In Microsoft Excel, Goal Seek is a great analysis tool that will help to find out the value for a selected cell with the help of What-If Analysis by using another cells formula.

By using following steps, you can easily find Basic Salary and Gross Salary with the help of Net Salary:

Step 1: First Create a Sample Salary Sheet in Excel as mentioned below:

Step 2: To automatically calculate employee Basic Pay and Gross Pay with the help of Net Pay, First Remove your sample Basic Pay from Employee Salary Slip as mentioned below:

As we know that Net Pay and Gross Pay always depends on Basic Pay so when we will remove Basic Pay then all values will become zero. For assistance please see below snapshot:

Step 3: Now Click on the Net Pay cell in which you want $50,000 Net Pay Amount.

Step 4: Now Click on Data Tab, In Data Tools group, Click on What-If Analysis and Select Goal Seek in drop down menu.

Step 5: Now in Goal Seek dialog box, Enter you desired Net Pay $50,000 in to value text box.

Step 6: Now Select Basic Pay Amount cell in By changing Cell and Press OK. Because Net Pay and Gross Pay always depend on Basic Pay. So we will inform to Excel, the basis of calculations as mentioned below:

Step 7: Now again Press OK to See your desired Net Pay with auto calculated Basic Pay and Gross Pay as mentioned below:

---Thank You---