When it comes to important processes like annual reviews, goal-setting and compensation reviews, your HR team most likely does a lot of the heavy lifting. Given just how important these processes are, that is no small feat.
Table of Contents
You don’t have to involve your full finance team or be a spreadsheet guru to create a comp review. With just Excel and the steps in this guide, you can create a great comp review to retain top talent and keep costs in check. Wondering where to start? Related resource Compensation Planning: what it is and why you need it.
Make sure to download a copy of the salary benchmark Excel spreadsheet, which you’ll be able to use with your own data to create a comp review, rapidly.
How to Set Up Your Data For An Excel Compensation Review
When you build an Excel spreadsheet, the most important part is how you set the data structure. With the proper data structure, all of your analysis work is easier.
As you start to prepare your comp review, here’s a guide to the fields you should capture:
- Employee name – all of your data should be captured at an “employee level”, meaning it’s specific to each employee. You’ll need to capture the first and last name for each employee in separate columns, each on their own row.
- Current base salary – the current base salary is the starting point for all analysis, so make sure that you have this for each employee.
- Department (function) – comp structures vary by job role and function. Make sure to log the department for each employee so that you can apply comparisons accurately.
- Title – remember, the goal is to compare individuals to the overall population to find outliers. List the title for each employee so that you can make proper comparisons.
- Last year’s comp increase – every company goes through lean years. Sometimes, you’ll need to curtail raises so that you can make your budget goals. But make sure you track those adjustments each year so that employees aren’t disappointed in multiple years.
- Location – distributed teams are more popular than ever, so it’s important to note where each employee is working. Make sure to account for the cost of living for each employee based on location.
I call this tab the Base Data. It’s the single source of all your current information. Capture the data with care so that all of your analysis work is accurate.
After you capture your data in an Excel spreadsheet, you’re ready to start analyzing it. That means reviewing and summarizing. Let’s learn more about summarizing data for your comp review.
How to Create a Salary Benchmark in Microsoft Excel
When you review compensation, it helps to compare individuals to the overall population. Compensation reviews are designed to identify outliers so that you can adjust their compensation accordingly.
The first step is to create a salary benchmark. This helps you organize and summarize your data so that you can review situations that need to be adjusted.
Your data is already captured for each employee, so how do you find meaning in it? The answer is to summarize it and create averages and other data points. These are used to compare current salary to others.
With the help of an Excel pivot table, we can take our comp data and summarize it. Then, we can compare individuals to the overall population. Highlight columns A – H, then go to the Insert > PivotTable option on Excel’s ribbon.
On the pop-up menu, make sure you leave the option set to Select a table or range and have all of your data highlighted. It should include all columns from A to H for our example. Then, click OK to create a pivot on a new sheet. You’ll see a new report builder appear.
Pivot tables are used to summarize data. I think of them as powerful, drag-and-drop reports. In our example, we’ll want to create three key data points for a salary benchmark:
- The average for each job title
- The maximum pay for each job title
- The minimum pay for each job title
Notice that we want to divide our data by department and job title. In our example, we need to put each division and title on its own row.
On the PivotTable Fields option, click and drag Department to the Rows section below. You’ll see each department in your data appear on its own row. Then, drag-and-drop Title underneath Department. Now, your pivot will match mine in the screenshot above, with each title on its own row.
So far, we’ve listed the job titles and departments. But what we really need are numeric values for the salary. This creates a benchmark.
Drag and drop the Current Base Salary field into the Values box on the PivotTable builder. You’ll see a new column called Sum of Current Base Salary pop into the PivotTable. This field sums up the salaries paid for each job title.
But what we really need is the Average. The sum of salaries doesn’t really tell us anything about our data. Right-click on the amounts and choose Summarize Values By > Average to change these to the average.
You’ve created an average for each job title. Now, drag Current Base Salary into the Values box two more times. Then, right-click on the new columns and choose Summarize Values By > Max and Min for the two new columns.
That’s it! You’ve created an average, max, and minimum salary for each job title in your organization. These data points are really helpful as you create your compensation adjustments.
How to Compare Employee Salaries to Peers
With our salary benchmark, we’ve created a useful guide to average salaries, plus high and low points on the curve. Now, it’s time to take those data points and compare each individual salary accordingly.
Back in our Base Data tab, let’s create several new columns:
- Salary vs Avg
- Salary vs Min
- Salary vs Max
Now, let’s pull those data points from our benchmark. Starting in the Salary vs Avg column, let’s write a VLOOKUP. This formula will use each title’s average, max and minimum salaries. In Column I, let’s write this formula:
This formula grabs the job title from cell E2, then matches it to a title in “Salary Benchmark” and shows the average for each title.
Now, we’ll write a formula to subtract an individual employee’s salary versus the average:
Let’s write two more formulas in columns J and K respectively. You can use the same formula and change only one part, the column to match from the Benchmark tab. Change “2” to “3” for the minimum, and “3” to “4” for the maximum.
Pull each of these formulas all the way down the column to extend them and create comparisons for each and every employee. This creates comparisons for each salary versus the average, maximum, and minimum points.
Her’s what a “0” means for each of these fields:
- If the Average column shows a 0, it indicates the employee makes exactly the average salary for their title.
- If the Minimum column shows a 0, it indicates the employee makes the minimum for their title.
- If the Maximum column shows a 0, it indicates the employee makes the maximum for their title.
Comparison to Market
Performing a comparison to the market is a crucial step to help you retain your employees. Without measuring your salary versus the market, you risk losing your top employees.
Here’s what you need to do:
- Create a list of all the titles in your organization that you need to find comparable salaries for
- Get data on salaries from competing sources.
- Match up the titles in your competing data
The hardest part of creating a comparison to market is getting Here are a few ideas for sources on compensation data:
- Use LinkedIn and Glassdoor data to build up your own compensation survey.
- Source salary data from a reliable service like SHRM Compensation Data Center.
- Ask employees for compensation data when they accept counteroffers.
Create a new tab in your workbook called Competitor Salaries, and let’s copy and paste the entire column from our Base Data tab that includes job titles. Then, highlight the column and choose Data > Remove Duplicates on Excel’s ribbon.
This creates exactly one instance of each job title.
Now, in column B, start listing your competitor job titles and match them up. Create a list of corresponding job titles along with competitor salaries.
We have everything we need to benchmark our data versus the competition now. Let’s jump back to the Base Data tab. In a new column titled, Salary vs Competitor, just add this formula to complete your comparison:
Cost of Living And Other Factors
The cost of living is a real drag on raises. Getting a 1% raise is a slap in the face as consumers catch the cost of living rise by 2% in many economies. Of course, you’re managing your payroll costs with budgets in mind, but so are employees in their households!
One popular way to monitor inflation is with the CPI, or consumer price index. It’s calculated and updated by the Bureau of Labor Statistics. It focuses on costs and provides a good guidepost to monitor rising costs.
Other factors might include monitoring high potential employees or high-need areas of your company. These go beyond the numbers and help you keep key positions staffed.
Applying Compensation Adjustments
All of the data above supports a compensation review. It gives you the points you need to make adjustments. Work with your managers and teams to determine which employees need adjustment, and how much that should be.
Let’s create a new sheet in our workbook to apply our compensation adjustments. Let’s call it Salary Adjustments. It helps to create a standalone sheet for this so that you can share it with your managers and employees when finished – cleanly.
Let’s create salary adjustments in two columns:
- Inflation adjustment – capturing an “across-the-board” adjustment
- Merit adjustment – add an employee-specific adjustment with individual adjustments
Then, we’ll sum it up in a column called Total Adjustment.
In the Inflation adjustment column, multiply the base salary times your agreed-upon inflation adjustment. I’ll apply a 1.8% increase across the board, and pull the formula down to calculate it for each employee.
Now, let’s create a merit adjustment in column E. Multiply base salary times the individual adjustment percentage to create the merit-based adjustment.
Finally, create a sum in Column F for D and E. You’ve calculated your total adjustment, and now you’re ready to share!
5 Key Takeaways To Perform Your Comp Review
Once you structure your data properly, just follow the steps in this tutorial to create a compensation review in Excel. You’ll have everything you need to adjust salaries so that you never lose top talent.
Remember these 5 points as you launch your comp review:
- Make sure that you prioritize the steps to create your salary benchmark. Knowing your current salaries and comparing them with others is the key to a successful comp review.
- Don’t miss out on comparing comp to the competition. You’ll reduce the risk of losing talent to others in your area and industry.
- Don’t forget about factors “outside the numbers.” Paying top talent above-market is a great example.
- Separate your merit and cost of living adjustments. This helps drive better conversations with your leadership.
- The approach in this tutorial is a great way to review comp, but there are always many factors to balance. Work with management and your managers to ensure that your increases fit budget and performance targets.
Multiply Regular Hours by Hourly Rate. Click cell "F1" and type "Regular Salary." Press "Enter." Click cell "F2" and type "=E2*C2" in the cell. Press the "Enter" key. This formula multiplies the employee's regular hours by his hourly rate.What can I do with an Excel certification? ›
- Accounting and auditing.
- Financial analysts.
- Data analysts.
- Business intelligence analysts.
- Market researchers and analyzers.
- Cost estimators.
- Digital marketers.
Excel is a Microsoft Office program that's designed to help calculate, tabulate, store, chart, and compare data for current and future reference. Its features are robust. Using Excel for accounting can provide an excellent tool for performing these tasks for small businesses.Do accountants still use Excel? ›
Accountants and bookkeeping professionals use Excel to perform complex calculations, like amortization tables and depreciation schedules. They also use it to compute product cost, allocate overhead, and perform budgeting and forecasting.What is the formula for compensation? ›
Accordingly, following formula shall be applicable for determination of compensation: Compensation = 2 X W X N.How do I calculate my total compensation? ›
- Start with your base salary. The first step in figuring out your total compensation is knowing your base salary. ...
- Add time-off benefits. ...
- Figure out insurance costs. ...
- Calculate any applicable commissions and/or bonuses. ...
- Assess any other benefits you receive.
Microsoft certification exams are usually hard, really hard. They are generally not very fun to take. The exams dive into minutia, asking questions that people with years of experience can't answer. Every question is a trick question so they are pretty hard to guess at.What is the passing score for Excel certification? ›
What score is needed to pass the MOS Excel exam? A minimum passing score is 700ish. This sounds odd, but it's because not all questions are weighted the same. In other words, some questions may count more than others.Is being Excel Certified worth it? ›
Yes, they are absolutely worth it. Your potential employer has no way of knowing if you've opened Excel once or if you have an expert certification. Earning a Microsoft Excel certificate can help you stand out in the job market by demonstrating to employers and hiring managers that you have the necessary skills.How long does it take to learn Excel for accounting? ›
Although it's possible to gain a basic understanding of Excel's interface and core functions in just a few hours, it can require additional time and study to master its more complex capabilities. It takes most Excel users approximately 18-20 hours to fully learn this spreadsheet application.
Excel is an important, in-demand skill in countless industries. Advanced Excel skills include the ability to produce graphs and tables, use spreadsheets efficiently, and perform calculations and automation to process large volumes of data.What Excel skills are most valuable for accounting? ›
- Spreadsheet Accessibility: General and Number Formatting. ...
- VLOOKUP and HLOOKUP. ...
- Making use of pivot tables. ...
- Auditing formulas. ...
- Data Validation. ...
- What If analysis. ...
- Making use of templates. ...
- Using Proper Cell Referencing.
- Google Sheets. Very similar in appearance and functionality to Excel, Google Sheets is probably the most popular Excel alternative. ...
- Apache OpenOffice. ...
- Excel Online. ...
- Zoho Sheet. ...
- LibreOffice. ...
- Bime. ...
- ThinkFree. ...
- Numbers For Mac.
QuickBooks Online is much easier to set up and use than Excel, which requires some knowledge of formulas. QuickBooks Online is a double-entry accounting system that automates most processes, saving time. You'll need a thorough understanding of bookkeeping systems to customize Excel to keep your books.What software do most accountants use? ›
- Sage 50cloud.
- NetSuide ERP.
- QuickBooks Enterprise.
- Zoho Books.
- Quickbooks Online.
- Sage Business Cloud.
Components of compensation include the following four items:
- Base pay, either an hourly wage or salary. ...
- Wage and salary add-ons. ...
- Incentive pay for performance. ...
- 5 Different Types of Sales Compensation Plans. Talent & Recruitment. ...
- Straight Salary. Straight salary sales compensation plans aren't very common, but they do have a place in some organizations. ...
- Salary plus Commission. ...
- Commission Only. ...
- Territory Volume.
- Research the industry and geographic area. ...
- Emphasize your flexibility. ...
- State a range rather than a fixed amount. ...
- Consider offering high compensation. ...
- Be honest. ...
- Focus on why you expect the amount. ...
- Prepare for price negotiation.
- Direct compensation: your salary, hourly rate and any overtime pay. ...
- Indirect compensation: areas like unemployment tax, social security tax or workers compensation insurance.
- Benefits: anything that is outlined in your benefits package.
Total employer compensation costs for private industry workers averaged $38.91 per hour worked in June 2022. Wage and salary costs averaged $27.44 and accounted for 70.5 percent of employer costs, while benefit costs were $11.47 and accounted for 29.5 percent.
A good score for an Excel test will vary between assessments. Aiming for a minimum score of 70% is a sensible starting point, which should stand you in good stead to progress to the next stage of the application process.What is the hardest certification exam? ›
California State Bar Exam
While none of the bar exams are exactly easy, the one required to practice law in the state of California is widely regarded to be the most difficult. The test takes two days to complete and covers 13 legal subject areas.
What is the hardest exam in the world? Master Sommelier Diploma Exam is considered as the toughest and hardest exam in the world and is a wine-tasting test for expert winemakers.How do I pass Excel skills test? ›
- Understand the Job Requirements. It's important to understand what sort of excel skills are required by your potential employer. ...
- Practice. One of the best ways to prepare for an Excel test is by practicing. ...
- Be Confident.
Enter this formula in cell C4: =IF(B4<70,”FAIL”,”PASS”) . This means if the score in B4 is less than 70, then enter the word FAIL in cell B4, else/otherwise enter the word PASS. Copy this formula from C4 to C5 through C13. Use an IF statement to convert numeric scores to a pass-fail status.How many questions are on the Excel certification test? ›
Excel - 35 Questions. PowerPoint - 35 Questions. Word Expert - 26 Questions. Excel Expert - 26 Questions.Is Excel worth learning in 2022? ›
Used across different industries and job roles, Excel makes you more productive at work, provides you with better information and analysis for making decisions, and can keep your personal life more organized, from finances to home projects. Whether at work or at home, Excel is worth learning.Are online Excel courses worth it? ›
Excel certification is worth it for anyone who works with numbers and data. For employers, Excel is one of the most sought-after skills when looking at new employees. And, for employees, there are also many benefits. Through Excel, it becomes possible to automatize and streamline many common tasks.Can I master Excel in 3 months? ›
excel is too big to master in a month. let alone a month, even one year is not enough. it is too big and too complex, unless you are born genius. but, yes, you can become good at it, provided (a) you use it daily and (b) share / receive tips and tricks from others.Can I learn Excel in 2 days? ›
You can learn Excel in a day
It is possible to learn Excel in a single day. If you dedicate a full day to learning Excel you will be able to gain a firm understanding of the basic functionality of how to work with spreadsheets and perform basic tasks.
If you practice every day and dedicate around 2-3 hours every day to learn the concepts, then you can learn it within four weeks. But, to master the concepts in Excel, you need to use the tricks and formulas on a daily basis. You need about 8-10 days to finish the Basic and Advance excel course.What are the 5 basic Excel skills? ›
- Data recording. Basic Excel users must know how to navigate and record data. ...
- Formulas. There are some formulas you need to know for basic Excel skills: ...
- Charts and graphs. ...
- Data organization. ...
- Pivot tables.
MS Excel is one of the most demanded skills for employment across many industries. A certification in Excel will open many doors of opportunities for you. Because it can be used in a variety of business settings, from project management to human resources, anyone who is a Microsoft Excel expert is always in demand.What is the most demanding skill in 2022? ›
- Time management.
- Project management.
- IT automation.
- Data analysis and statistics.
- Knowledge of Accounting Practices. ...
- Proficiency in Accounting Software. ...
- Ability to Prepare Financial Statements. ...
- Knowledge of General Business Practices. ...
- Ability to Analyze Data. ...
- Critical Thinking Skills.
- VLOOKUP. VLOOKUP (vertical lookup) is a function to look up a value in a table and retrieve data from a specific column. ...
- INDEX MATCH. ...
- Advanced conditional formatting. ...
- Pivot tables and reporting. ...
- Macros and VBA. ...
- Data simulations.
VLOOKUP and HLOOKUP are two of the most useful Excel functions for accountants. These functions let you search a table of data and give all the appropriate information for an individual set of data based on only one part of it.What program is better than Excel? ›
LibreOffice is an open source Excel alternative that has been derived from the original Openoffice.org, just like Apache Open Office. And it's not just Excel, LibreOffice is an alternative to the whole Microsoft Office application.
- Manual data entry is slow and problematic. Spreadsheets require employees to copy and paste or rekey data into the system. ...
- Inability to share data. ...
- Lack of real-time or historical data. ...
- Difficulties in reporting and analysis. ...
- Security issues.
Office 365 Personal: $6.99/month or $69.99/year Office 365 Home: $9.99/month or $99.99/year Office Home & Student 2019: $149.99 one-time Office 365 Business: $8.25/user/month (annual commitment required) Office 365 Business Premium: $12.50/user/month (annual commitment required) Office 365 Business Essentials: $5/month ...
- Xero: Best overall QuickBooks alternative.
- Sage: Most versatile QuickBooks alternative.
- FreshBooks: Best for self-employed people.
- Wave Accounting: Best free option.
- Zoho Books Accounting: Best for business owners who already use Zoho apps.
- Bonsai: Best for freelancers.
The business owners would hire only those who could cope with a large volume of numerical flows, their display on paper, and maintain all the records organized, clear and accurate without the technology that did it all for you. Currently, one does not need to have a lot of knowledge or know higher mathematics.Do people still use QuickBooks? ›
QuickBooks is the accounting software of choice for more than 29 million small businesses in the U.S. They have over 80% market share and have a diverse product offering suited to help both small businesses (QuickBooks Online) and larger growing companies (QuickBooks Enterprise) and everything in between (QuickBooks ...Does Excel count as accounting software? ›
Excel continues to be one of the most popular accounting tools used by small-business finance managers. Smaller businesses tend to have and use less financial data when compared with enterprises, and Excel allows such businesses to focus on basic accounting tasks, such as budget planning and forecasting.What technology will replace accountants? ›
Instead, AI will create more value for these accounting professionals. It will make the basic accounting tasks faster and be done more efficiently. It can likewise allow accountants to focus more on high-impact and high-quality tasks. This is the future of accounting.Do accountants use Excel? ›
Microsoft Excel is an invaluable tool for accountants to make numerous schedules and calculations that traditional bookkeeping software doesn't provide. It comes with functions for performing complex mathematical calculations and also supports many add-ons for activities like financial forecasting and modeling.How do you calculate employee compensation percentage? ›
Divide the labor cost by gross sales and multiply the result by 100. Suppose gross sales equal $500,000 and the labor cost totals $140,000. Divide $140,000 by $500,000 and multiply by 100. Your employee labor percentage equals 28 percent.How do you calculate an employee's pay? ›
- Total pay / Total number of hours worked = Regular hourly rate.
- (Hourly rate) / (2) = Half-time rate.
- (Half-time rate) X (Overtime hours) = Overtime Pay.
- (Number of weeks worked) x (Half-time rate) = Back wages.
- (New paycheck amount) - (Previous paycheck amount) = Pay difference.
A typical range of 80 percent to 120 percent is set around a midpoint target for a given pay grade. New or inexperienced employees are typically paid closer to 80 percent of the midpoint, whereas the most outstanding or longest-tenured employees are paid more, up to the 120 percent end of the pay range.How much is a 5% raise? ›
An employee's current annual salary is $50,000, and she earns a $2,500 raise, her annual salary will increase to $52,500. Divide $2,500 by $50,000 and the result is 0.05, which is 5 percent (2,500/50,000 = 0.05).
Using our formula, a 3 percent raise would look like this: $52,000 X . 03 = $1,560 raise over the course of the year. This brings your employee's total salary to $53,560.How do you calculate salary manually? ›
- Payroll calculations usually constitute 4 main components – Basic pay, Allowances, Deductions, and IT Declarations.
- Gross Pay is the sum of Basic pay + Allowances.
- Net Pay is the difference between Gross Pay – (Deductions + Total TDS). ...
- Basic Pay is 50% of Gross salary (Gross * 0.5)