{"id":5466,"date":"2020-02-19T15:00:00","date_gmt":"2020-02-19T14:00:00","guid":{"rendered":"https:\/\/beebole.com\/blog\/?p=5466"},"modified":"2025-03-31T13:02:11","modified_gmt":"2025-03-31T11:02:11","slug":"annual-compensation-reviews-google-sheets-free-template","status":"publish","type":"post","link":"https:\/\/beebole.com\/blog\/annual-compensation-reviews-google-sheets-free-template","title":{"rendered":"The complete guide to annual compensation reviews using Google Sheets"},"content":{"rendered":"\n<p><strong>Annual compensation reviews<\/strong> are a must. Reviewing and resetting employee salaries help you ensure that your employees are paid fairly. In this article, you\u2019ll learn to perform a salary and comp review in <strong><a href=\"https:\/\/beebole.com\/blog\/google-sheets-pivot-tables-reporting\/\">Google Sheets<\/a><\/strong> efficiently with our free spreadsheet template.<\/p>\n\n\n\n<p>It\u2019s no secret that compensation is crucial to attract and retain talent. When you lose an employee due to compensation, you\u2019re sure to cost your organization more in the form of recruiting fees, signing bonuses, and loss of productivity.<\/p>\n\n\n\n<p class=\"has-text-align-center\">&#8211;<br><em>This article is one of many <a href=\"https:\/\/beebole.com\/blog\/category\/learn-tutorials-howtos\/\">tutorials<\/a> on our blog. Among other things, we dive into actionable spreadsheet tips that you can implement in the office today. Is there something you\u2019d like to know how to do in a spreadsheet? Let us know in the comments below!<\/em><br> &#8211; <\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"definition\">What\u2019s an annual compensation review?<\/h2>\n\n\n\n<div  class=\"montserrat-font my-5 mx-auto bbl_definition_snippet\">\n  <div class=\"mb-4\">\n    <div class=\"bbl-ds-item question mb-3\">\n      <h2 class=\"h4 mb-0 mt-0\">What is a compensation (comp) or salary review?<\/h2>\n    <\/div>\n    <div class=\"bbl-ds-item answer\">\n      <p>A compensation review is when <strong>companies periodically review pay and other benefits<\/strong> to assure that each employee is compensated in line with their experience and expertise.<\/p>\n    <\/div>\n  <\/div>\n<\/div>\n\n\n<p>Let\u2019s face it: We often become so busy in the day-to-day work of running a business that it\u2019s easy to lose sight of people issues like compensation. An <strong><a href=\"https:\/\/beebole.com\/blog\/what-does-annual-compensation-mean\/\">annual compensation<\/a> review<\/strong> is a great time to revisit the pay of your teams and make adjustments where needed. Remember, this is also an important time to consider <a href=\"https:\/\/beebole.com\/blog\/how-to-calculate-the-real-cost-of-an-employee\/\">the true cost of employees in your organization<\/a>.   <\/p>\n\n\n\n<p>Too often, companies adjust salary only as issues arise. Maybe an employee receives an offer from a competitor that leads them out the door. By then, it\u2019s often too late to counter with a pay increase and retain them. Attracting and <a href=\"https:\/\/beebole.com\/blog\/internal-employee-transfers\/\">retaining employees<\/a> is tough. Ask any HR manager, and you\u2019re sure to hear that this tight job market is creating a battle for talent.<\/p>\n\n\n\n<p>Let\u2019s learn how to use a <strong>Google Sheets comp review<\/strong> that helps you keep your teams intact and your growth on track.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"elements-of-successful-comp-review\">Elements of a successful comp review<\/h2>\n\n\n\n<p>At its core, a successful annual compensation review asks one fundamental question: <strong>are my employees paid fairly?<\/strong>  <\/p>\n\n\n\n<p>There\u2019s no single data point that can determine if someone is paid fairly. Instead, using several heuristics can help you identify outliers and adjust comp so that you don\u2019t risk losing a rockstar employee.  <\/p>\n\n\n\n<p>Here are <strong>three key analytics<\/strong> to consider when reviewing compensation:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Comparison to peers (internal)<\/strong>: Perhaps the riskiest part of comp reviews is if an employee finds out they make significantly less than a peer. You should review compensation for outliers to avoid just this type of situation. <\/li>\n\n\n\n<li><strong>Comparison to market (external)<\/strong>: What do employees at peer companies in similar roles make? Paying in line with the market is key to attracting and retaining talent. <\/li>\n\n\n\n<li><strong>Cost of living, performance, and other factors<\/strong>: Salary is just one part of the \u201ctotal compensation\u201d package. You have to consider other factors that influence compensation levels, <a href=\"https:\/\/beebole.com\/blog\/employee-performance-reviews-experts-advice\/\">such as an individual\u2019s performance<\/a>, and the geographic impact on cost of living.<\/li>\n<\/ul>\n\n\n\n<p>With the help of a free and simple analysis tool like Google Sheets, you can incorporate all of these factors in your comp review. Let\u2019s learn how to perform an effective comp review.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"spreadsheet-setting-up-your-data\">1. The spreadsheet: Setting up your data<\/h2>\n\n\n\n<p>When working in spreadsheets, half the battle is setting the data up correctly. All of your analysis is easier if you can build a central document with every data point you need. Let\u2019s build a spreadsheet with perfectly structured data for easy analysis.<\/p>\n\n\n\n<p>Here are the suggested fields that every salary review needs:  <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Employee name<\/strong>: Capture the first and last names in separate columns. <\/li>\n\n\n\n<li><strong>Current base salary<\/strong>: A 5% increase is more impactful to senior employees than junior team members. Comp reviews require thinking in both total dollars and percent change. <\/li>\n\n\n\n<li><strong>Department<\/strong>: Comparisons to peers rely on considering each employee\u2019s department or \u201cfunction\u201d within the organization. <\/li>\n\n\n\n<li><strong>Title<\/strong>: Checking for salary outliers is a matter of comparing to others in similar roles, so you need to capture job title. <\/li>\n\n\n\n<li><strong>Last year\u2019s comp increase<\/strong>: You might not remember exactly what an employee received each year, but they certainly will. Monitoring for multiple successive years of low increases is a risk. <\/li>\n\n\n\n<li><strong>Location<\/strong>: Remember, geographics impact the cost of living. Your employees in San Francisco might be paid differently than those in Austin. <\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"116\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/comp-review-in-google-sheets-sheets-list-700x116.jpg\" alt=\"Learn how to do an annual comp review using Google Sheets with this tutorial.\" class=\"wp-image-5630\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/comp-review-in-google-sheets-sheets-list-700x116.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/comp-review-in-google-sheets-sheets-list-768x127.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/comp-review-in-google-sheets-sheets-list.jpg 850w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><\/figure>\n\n\n<div  class=\"my-5 mx-auto bbl_cta_assets_block bbl_cta_block bk-light\">\n\t<div class=\"bbl_cta_block-blockcontent d-block overflow-hidden position-relative rounded-4 text-decoration-none\">\n\t\t\t\t\t<div class=\"object-fit-cover position-absolute bbl-orange-dot-round\" style=\"background-image: url(https:\/\/beebole.com\/blog\/wp-content\/themes\/sage\/public\/images\/orange-dot-round.609972.svg)\"><\/div>\n    \t\t<div class=\"bbl_cta_block-row align-items-center d-flex flex-md-row justify-content-center mx-0 no-gutters position-relative row\">\n\t\t\t<div class=\"bbl_cta_block-img-col col d-flex justify-content-start pe-md-2 px-0\">\n\t\t\t\t<img\n\t\t\t\t\talt=\"Get the Template &amp; Start Implementing These Comp Review Techniques Today\"\n\t\t\t\t\tclass=\"d-block h-auto me-md-4 mw-lg-100\"\n\t\t\t\t\theight=\"251\"\n\t\t\t\t\tloading=\"lazy\"\n\t\t\t\t\tsrc=\"https:\/\/beebole.com\/blog\/wp-content\/themes\/sage\/public\/images\/promotion-download-asset.198bac.png\"\n\t\t\t\t\twidth=\"360\"\n\t\t\t\t\/>\n\t\t\t<\/div>\n\t\t\t<div class=\"bbl_cta_block-text-col col mt-md-0 ps-md-2 px-0\">\n\t\t\t\t\t\t\t\t\t<div class=\"mb-1\"><div class=\"bbl_cta_block-label font-weight-bold lh-base mb-4\">DOWNLOAD THE SPREADSHEET &amp; FOLLOW ALONG<\/div><\/div>\n\t\t\t\t                  <div class=\"bbl_cta_block-title font-weight-bold lh-base\">Get the Template &amp; Start Implementing These Comp Review Techniques Today<\/div>\n        \t\t\t\t\t\t\t\t\t<div class=\"mt-1 pe-lg-0 pe-md-4\">\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<a class=\"bbl_cta_block-cta-button w-100 w-lg-auto btn btn-outline-primary text-primary link-light  me-lg-4 mb-3 mb-lg-0 mt-4 free-download-link\" href=\"https:\/\/docs.google.com\/spreadsheets\/u\/1\/d\/1JUmWGbeS7Nx-R0B__h-TPtatYWFUjSAf1kCscVbP9C8\/copy\" target=\"_blank\" rel=\"noopener\">\n\t\t\t\t\t\t\tDownload now!            <\/a>\n          <\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/div>\n  <\/div>\n<\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"salary-benchmark\">2. Create a salary benchmark<\/h2>\n\n\n\n<p>Once you have your salary and personnel information captured, it\u2019s important to create averages and summarize the data. Each row in our salary data is an individual data point, but salary reviews are about comparisons to the average.  <\/p>\n\n\n\n<p>For this tip, we\u2019ll use the <strong>Pivot Tables<\/strong>. These tables help us to summarize large sets of data to create a salary benchmark. Start by highlighting all of the rows and columns in our salary data, then go to the <strong>Data &gt; Pivot table<\/strong> option. Then, click <strong>Create<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"383\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-compensation-tutorial-create-pivot-700x383.jpg\" alt=\"This Google Sheets tutorial walks you through how to do an annual salary review.\" class=\"wp-image-5626\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-compensation-tutorial-create-pivot-700x383.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-compensation-tutorial-create-pivot-768x420.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-compensation-tutorial-create-pivot.jpg 813w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><\/figure>\n\n\n\n<p>Remember, we want to compare <em>individual<\/em> salaries to the overall averages. That\u2019s why we need to create those averages. But it\u2019s also important to create averages within categories like a job title.  <\/p>\n\n\n\n<p>To do that, click on the <strong>Add <\/strong>button in the <strong>Rows<\/strong> section of your pivot creator. Then, choose <strong>Add<\/strong> again, and choose <strong>Title<\/strong>.  <\/p>\n\n\n\n<p>Now, you have a list of all departments and titles. The last step is to find the <strong>Values<\/strong> section on the right side and choose <strong>Current Base Salary<\/strong>. When you add it, you\u2019ll see the total of all salaries across those titles. To change it to an average, choose <strong>AVERAGE<\/strong> from the <strong>Summarize by<\/strong> dropdown on the right side.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"518\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-comp-review-average-salaries-700x518.jpg\" alt=\"It&#039;s important to take into account average salaries when performing an annual compensation review.\" class=\"wp-image-5623\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-comp-review-average-salaries-700x518.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-comp-review-average-salaries-768x568.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-comp-review-average-salaries.jpg 986w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><\/figure>\n\n\n\n<p>So far, we\u2019ve built a basic average, but it also helps to have the highest and lowest values for a given job title. We\u2019re developing comparison points to help us understand where each employee falls on the salary curve, so knowing the upper and lower limits is key.  <\/p>\n\n\n\n<p>Click on <strong>Add <\/strong>again in the <strong>Values Section<\/strong>, and again choose <strong>Current Base Salary<\/strong>. This time, choose <strong>MIN<\/strong> from the <strong>Summarize By<\/strong> dropdown. This shows the minimum value (the lowest pay) for each title. Repeat the process, and choose <strong>MAX<\/strong>. Now, you have all of the needed data points.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"113\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/salary-comparison-base-min-max-700x113.jpg\" alt=\"Another key to a successful comp review at work is looking at the base minimum and maximum salaries.\" class=\"wp-image-5631\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/salary-comparison-base-min-max-700x113.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/salary-comparison-base-min-max-768x124.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/salary-comparison-base-min-max.jpg 827w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><\/figure>\n\n\n\n<p>In just a few steps, you\u2019ve benchmarked salary across your organization. You know the average, the highest, and the lowest salaries by job title. Now let\u2019s work to compare them.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"vlookup-formula\">3. VLookup formula for comp reviews: Comparison to peers<\/h2>\n\n\n\n<p>First, let\u2019s work to review salary in a peer group. Remember, the goal is to identify outliers so that you can course correct.  <\/p>\n\n\n\n<p>In the prior step, we created a benchmark that shows the average, maximum, and minimum salary by job title. Let\u2019s leverage those statistics to review the annual compensation.  <\/p>\n\n\n\n<p>In the main sheet with all of our source data, let\u2019s write a VLOOKUP formula to pull those benchmarks we created. For each employee, we\u2019ll want to find the average salary from the benchmark, then compare it to the average.  <\/p>\n\n\n\n<p>Let\u2019s create a \u201cSalary vs Average\u201d Column, then add this formula to it:<\/p>\n\n\n\n<p><strong>=VLOOKUP(E2,&#8217;Salary Benchmark&#8217;!B:E,2,FALSE)  <\/strong><\/p>\n\n\n\n<p>This formula is looking up the job title in cell E2, then going to the tab labeled \u201cSalary Benchmark\u201d and pulling through the average for each title.  <\/p>\n\n\n\n<p>Now, let\u2019s modify it to subtract the average from each employee\u2019s salary:  <\/p>\n\n\n\n<p><strong>=C2-VLOOKUP(E2,&#8217;Salary Benchmark&#8217;!B:E,2,FALSE)  <\/strong><\/p>\n\n\n\n<p>Presto! Pull the formula down to apply it to each employee. Now, we have each salary compared to the average. Let\u2019s also write two more formulas to  compare versus minimum and maximum.  <\/p>\n\n\n\n<p>Salary versus minimum:  <\/p>\n\n\n\n<p><strong>=C2-VLOOKUP(E2,&#8217;Salary Benchmark&#8217;!B:E,3,FALSE)  <\/strong><\/p>\n\n\n\n<p>Salary versus maximum:  <\/p>\n\n\n\n<p><strong>=C2-VLOOKUP(E2,&#8217;Salary Benchmark&#8217;!B:E,4,FALSE)  <\/strong><\/p>\n\n\n\n<p>Notice that the only thing that changes between formulas is the column from the Benchmark to pull the comparable data from (column \u201c2\u201d for the average, \u201c3\u201d for the minimum, and \u201c4\u201d for the maximum.)<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"129\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/salary-comparison-data-700x129.jpg\" alt=\"When performing an annual comp review, comparing salaries with peers is another important step.\" class=\"wp-image-5632\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/salary-comparison-data-700x129.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/salary-comparison-data-768x142.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/salary-comparison-data.jpg 850w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><\/figure>\n\n\n\n<p>Consider what a numeric value of \u201c0\u201d means for each of these fields: <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A 0 in the Average column means the employee makes exactly the average for their title <\/li>\n\n\n\n<li>A 0 in the minimum column means that the employee makes the minimum salary for their job title <\/li>\n\n\n\n<li>A 0 in the maximum column means that the employee makes the maximum salary for their job title<\/li>\n<\/ul>\n\n\n\n<p>Setting up these comparison points is key, but the real work begins as you start to review the data. Work through this column to find outliers and use it as the key input to flag needed comp adjustments.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"comparison-to-market\">4. Comparison to market<\/h2>\n\n\n\n<p>The competition for top talent has never been tougher. It\u2019s easy for your competitors to find your top talent and lure them away with a pay increase.  <\/p>\n\n\n\n<p>Unfortunately, there\u2019s no structured way to create a comparison to peers. Here are three ways that companies gather competitive comp data:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Browse sites like Glassdoor of top competitors and build a list of salaries by job title. <\/li>\n\n\n\n<li>Purchase a salary survey or data from a service like the <a href=\"https:\/\/www.shrm.org\/resourcesandtools\/business-solutions\/pages\/salary-data-service.aspx\" target=\"_blank\" rel=\"noopener\">SHRM Compensation Data Center<\/a>. <\/li>\n\n\n\n<li>Voluntarily ask for salary information from employees who are leaving the company in an exit interview. (Remember to ask for the title as well)<\/li>\n<\/ul>\n\n\n\n<p>No matter what approach you use, create a new spreadsheet in your workbook called <strong>Competitor Salaries<\/strong>. Then, add the data you gathered from one of the techniques above.  <\/p>\n\n\n\n<p>Once you have your competitor data in a spreadsheet, you\u2019ll need to <strong>normalize<\/strong> it for comparison. The hard part about creating comparisons to market is cleaning up the data. Titles aren\u2019t always the same at every company so you\u2019ll need to spend some time making it comparable.  <\/p>\n\n\n\n<p>The goal is to create a list that you can use for comparison. Each job title in your competitor compensation data needs to be matched to a comparable title within <em>your<\/em> organization.  <\/p>\n\n\n\n<p>Paste all job titles from your organization from our main tab into Column A. Then, use <strong>Data &gt; Remove Duplicates<\/strong> and choose <strong>Remove Duplicates<\/strong>. This reduces all of the duplicates to each job title appearing once.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"412\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-comp-review-in-google-sheets-remove-duplicates-700x412.jpg\" alt=\"You can use Google Sheets to clean up data and get rid of duplicates when performing an annual salary review.\" class=\"wp-image-5624\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-comp-review-in-google-sheets-remove-duplicates-700x412.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-comp-review-in-google-sheets-remove-duplicates-768x452.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-comp-review-in-google-sheets-remove-duplicates.jpg 850w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><\/figure>\n\n\n\n<p>Now, you\u2019ll need to map the titles from your company to comparable titles. In the first column, put the title in your team that most closely matches your imported data. The goal here is to create a comparable list that we can add to our comparison. <\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"279\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-salary-review-imported-comp-700x279.jpg\" alt=\"Learn how to use Google Sheets to perform an annual salary compensation review.\" class=\"wp-image-5627\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-salary-review-imported-comp-700x279.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-salary-review-imported-comp-768x306.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-salary-review-imported-comp.jpg 850w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><\/figure>\n\n\n\n<p>Once you\u2019ve built out a comparable list, go back to your Base Data tab. Then, let\u2019s write a VLOOKUP to pull the comparable compensation for each employee and compare it: <\/p>\n\n\n\n<p><strong>=C2-VLOOKUP(E2,&#8217;Competitor Salaries&#8217;!A:C,3,FALSE)<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"249\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/using-google-sheets-for-annual-salary-review-salary-vs-competitor-700x249.jpg\" alt=\"Use Google Sheets to analyze competitor salaries in your annual comp review.\" class=\"wp-image-5634\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/using-google-sheets-for-annual-salary-review-salary-vs-competitor-700x249.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/using-google-sheets-for-annual-salary-review-salary-vs-competitor-768x273.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/using-google-sheets-for-annual-salary-review-salary-vs-competitor.jpg 850w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><\/figure>\n\n\n\n<p>With the new field you\u2019ve added, a positive number means that the employee\u2019s compensation is higher than competitive peers. A negative number shows the employee is paid lower than the competitive benchmark.  <\/p>\n\n\n\n<p>That\u2019s it! You\u2019ve added yet another crucial data point to compare each employee\u2019s compensation.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"other-factors\">5. Cost of living and other factors<\/h2>\n\n\n\n<p>We all know that the cost of living rises every year with inflation. If you don\u2019t include this as a part of your annual compensation increase, employees will feel their wallets squeezed and might look for a new job to \u201creset\u201d their pay.  <\/p>\n\n\n\n<p>Many American companies use CPI, or consumer price index, as the inflation factor. CPI is published <a href=\"https:\/\/www.bls.gov\/cpi\/\" target=\"_blank\" rel=\"noopener\">by the Bureau of Labor Statistics<\/a> and describes how consumer goods are increasing or decreasing in price.  <\/p>\n\n\n\n<p>Don\u2019t forget that pay also includes a number of intangible factors. For example, high potential employees might be paid more than peers in order to retain them for a long, productive career. All of these are factors \u201cbeyond the numbers\u201d that are important to consider.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"annual-compensation-adjustments\">6. Applying annual compensation adjustments<\/h2>\n\n\n\n<p>So far, our work has focused on setting up all of the data we need for salary comparisons. It\u2019s up to your team to use this data to guide discussions and determine the appropriate adjustments.  <\/p>\n\n\n\n<p>Now, let\u2019s create a spreadsheet, called <strong>Salary Adjustments<\/strong>, to calculate and apply the adjustments. First, start by creating a new sheet with all of your employees, their current job title, and their current salary.  <\/p>\n\n\n\n<p>Many teams apply compensation adjustments in multiple parts:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Cost of living \/ inflationary adjustments <\/strong><\/li>\n\n\n\n<li><strong>Merit-based adjustments<\/strong><\/li>\n<\/ul>\n\n\n\n<p>In my experience, it\u2019s typically best to keep these two components separate. Ultimately, every comp adjustment leads to higher payroll costs for the company. Keeping the two parts separate helps advise leadership of the cost of each component.  <\/p>\n\n\n\n<p>Let\u2019s create two new columns: <strong>Inflation Adjustment<\/strong> and <strong>Merit Adjustment<\/strong>. Then, let\u2019s also create a <strong>Total Adjustment<\/strong> column.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"173\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-slary-review-adjustment-starting-point-700x173.jpg\" alt=\"Use Google Sheets during your annual comp review to easily look analyze salary adjustments.\" class=\"wp-image-5629\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-slary-review-adjustment-starting-point-700x173.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-slary-review-adjustment-starting-point-768x190.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-slary-review-adjustment-starting-point.jpg 850w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><\/figure>\n\n\n\n<p>In the inflation adjustment column, multiply the <strong>Current Base Salary <\/strong>times the adjustment you determined for inflation. I\u2019ll apply a 1.8% increase across the board and pull the formula down to calculate it for each employee.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"173\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-salary-review-inflation-adjustment-700x173.jpg\" alt=\"One important thing during an annual salary review is adjusting for inflation.\" class=\"wp-image-5628\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-salary-review-inflation-adjustment-700x173.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-salary-review-inflation-adjustment-768x190.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-salary-review-inflation-adjustment.jpg 850w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><\/figure>\n\n\n\n<p>Now, let\u2019s apply a merit adjustment in column E. Multiply current compensation times the amount you\u2019ve decided for each employee. Keep in mind that you don\u2019t want to \u201cdouble count\u201d the increase with what you\u2019ve already added for inflation.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"138\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-comp-review-merit-adjustment-700x138.jpg\" alt=\"Apart from inflation, you&#039;ll also want to take into account the merit-based increase during a yearly comp review.\" class=\"wp-image-5625\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-comp-review-merit-adjustment-700x138.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-comp-review-merit-adjustment-768x151.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/annual-comp-review-merit-adjustment.jpg 850w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><\/figure>\n\n\n\n<p>One last step: create a <strong>SUM<\/strong> formula in the<strong> Total Adjustment <\/strong>column. This calculates the total increase in compensation for each employee.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"157\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/salary-review-in-google-sheets-total-adjustment-700x157.jpg\" alt=\"When doing an annual salary or compensation review, by taking into account inflation and merit-based raises, you&#039;ll arrive to the total adjustment.\" class=\"wp-image-5633\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/salary-review-in-google-sheets-total-adjustment-700x157.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/salary-review-in-google-sheets-total-adjustment-768x173.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2019\/12\/salary-review-in-google-sheets-total-adjustment.jpg 850w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><\/figure>\n\n\n\n<p>Now, you have everything you need to complete your compensation adjustment. You can report the total increase in payroll costs to management and issue a report to your managers as well.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"takeaways\">Five key takeaways to perform your comp review<\/h2>\n\n\n\n<p>With the help of the right data, you can create a compensation review that\u2019s fact-based and easy to explain to your employees. That helps ensure that you retain top talent and monitor your salary costs.  <\/p>\n\n\n\n<p>Keep these 5 key points in mind before you start your comp review:  <\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Spend the majority of your time creating a salary benchmark. This gives you data points to flag outliers for adjustment. <\/li>\n\n\n\n<li>It\u2019s not easy to assemble the data, but including some comparison to your competition (both local and in your industry) is important. <\/li>\n\n\n\n<li>Keep the non-numeric factors in mind as well, like ensuring that your high potential employees are paid to retain them. <\/li>\n\n\n\n<li>Make sure that you calculate merit and cost of living as separate figures for easy reporting. <\/li>\n\n\n\n<li>The data you put together in this tutorial is just a starting point for your annual compensation review. Ultimately, you\u2019ll need to balance the data with management conversations and budgets as well.<\/li>\n<\/ol>\n\n\n\n<p>Remember, this post is part of our <a href=\"https:\/\/beebole.com\/blog\/category\/learn-tutorials-howtos\/\">tutorial series<\/a>, where we uncover nifty tips in Google Sheets and Microsoft Excel. If you found this post helpful, don&#8217;t miss this one on <a href=\"https:\/\/beebole.com\/blog\/excel-power-query-for-business-intelligence\/\">Excel Power Query and 5 business intelligence hacks<\/a> you can try, this handy tutorial on <a href=\"https:\/\/beebole.com\/blog\/employee-cell-data-usage-with-google-sheets-free-template\/\">building an automated employee cell phone data usage analysis<\/a>, or this how-to on <a href=\"https:\/\/beebole.com\/blog\/automating-emails-from-excel-employee-bonus\/\">automating emails from Excel<\/a> (which is especially useful when sending bonus emails!)<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-next-steps\">Next steps<\/h2>\n\n\n\n<p><em>Don\u2019t forget about <a class=\"highlighted-link bbl-link-hs bbl-link-hs-v-1\" href=\"https:\/\/beebole.com\"><span>Beebole<svg width=\"17\" height=\"18\" viewBox=\"0 0 17 18\" fill=\"none\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\"><path fill-rule=\"evenodd\" clip-rule=\"evenodd\" d=\"M11.25 0.875H15.625C15.7908 0.875 15.9497 0.940848 16.0669 1.05806C16.1842 1.17527 16.25 1.33424 16.25 1.5V5.875C16.25 6.04076 16.1842 6.19973 16.0669 6.31694C15.9497 6.43415 15.7908 6.5 15.625 6.5C15.4592 6.5 15.3003 6.43415 15.1831 6.31694C15.0658 6.19973 15 6.04076 15 5.875V3.00833L4.81667 13.1917C4.69819 13.3021 4.54148 13.3622 4.37956 13.3593C4.21765 13.3565 4.06316 13.2909 3.94865 13.1764C3.83414 13.0618 3.76854 12.9074 3.76569 12.7454C3.76283 12.5835 3.82293 12.4268 3.93333 12.3083L14.1167 2.125H11.25C11.0842 2.125 10.9253 2.05915 10.8081 1.94194C10.6908 1.82473 10.625 1.66576 10.625 1.5C10.625 1.33424 10.6908 1.17527 10.8081 1.05806C10.9253 0.940848 11.0842 0.875 11.25 0.875ZM2.5 4.625C2.16848 4.625 1.85054 4.7567 1.61612 4.99112C1.3817 5.22554 1.25 5.54348 1.25 5.875V14.625C1.25 14.9565 1.3817 15.2745 1.61612 15.5089C1.85054 15.7433 2.16848 15.875 2.5 15.875H11.25C11.5815 15.875 11.8995 15.7433 12.1339 15.5089C12.3683 15.2745 12.5 14.9565 12.5 14.625V7.75C12.5 7.58424 12.5658 7.42527 12.6831 7.30806C12.8003 7.19085 12.9592 7.125 13.125 7.125C13.2908 7.125 13.4497 7.19085 13.5669 7.30806C13.6842 7.42527 13.75 7.58424 13.75 7.75V14.625C13.75 15.288 13.4866 15.9239 13.0178 16.3928C12.5489 16.8616 11.913 17.125 11.25 17.125H2.5C1.83696 17.125 1.20107 16.8616 0.732233 16.3928C0.263392 15.9239 0 15.288 0 14.625V5.875C0 5.21196 0.263392 4.57607 0.732233 4.10723C1.20107 3.63839 1.83696 3.375 2.5 3.375H9.375C9.54076 3.375 9.69973 3.44085 9.81694 3.55806C9.93415 3.67527 10 3.83424 10 4C10 4.16576 9.93415 4.32473 9.81694 4.44194C9.69973 4.55915 9.54076 4.625 9.375 4.625H2.5Z\"\/><\/svg><\/span><\/a>! It&#8217;s a place for employees to safely track hours worked and time spent on projects, as well as a powerful tool for project planning and accurate budgeting. By combining data like specific project estimations and employee salaries, you&#8217;ll be well on your way to unlocking invaluable business intelligence. What&#8217;s more, Beebole offers free integrations for <a class=\"underlined-link bbl-link-hs bbl-link-hs-v-2\" href=\"https:\/\/beebole.com\/timesheet-google-sheets\/\" target=\"_blank\" rel=\"noreferrer noopener\"><span>Google Sheets<svg width=\"17\" height=\"18\" viewBox=\"0 0 17 18\" fill=\"none\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\"><path fill-rule=\"evenodd\" clip-rule=\"evenodd\" d=\"M11.25 0.875H15.625C15.7908 0.875 15.9497 0.940848 16.0669 1.05806C16.1842 1.17527 16.25 1.33424 16.25 1.5V5.875C16.25 6.04076 16.1842 6.19973 16.0669 6.31694C15.9497 6.43415 15.7908 6.5 15.625 6.5C15.4592 6.5 15.3003 6.43415 15.1831 6.31694C15.0658 6.19973 15 6.04076 15 5.875V3.00833L4.81667 13.1917C4.69819 13.3021 4.54148 13.3622 4.37956 13.3593C4.21765 13.3565 4.06316 13.2909 3.94865 13.1764C3.83414 13.0618 3.76854 12.9074 3.76569 12.7454C3.76283 12.5835 3.82293 12.4268 3.93333 12.3083L14.1167 2.125H11.25C11.0842 2.125 10.9253 2.05915 10.8081 1.94194C10.6908 1.82473 10.625 1.66576 10.625 1.5C10.625 1.33424 10.6908 1.17527 10.8081 1.05806C10.9253 0.940848 11.0842 0.875 11.25 0.875ZM2.5 4.625C2.16848 4.625 1.85054 4.7567 1.61612 4.99112C1.3817 5.22554 1.25 5.54348 1.25 5.875V14.625C1.25 14.9565 1.3817 15.2745 1.61612 15.5089C1.85054 15.7433 2.16848 15.875 2.5 15.875H11.25C11.5815 15.875 11.8995 15.7433 12.1339 15.5089C12.3683 15.2745 12.5 14.9565 12.5 14.625V7.75C12.5 7.58424 12.5658 7.42527 12.6831 7.30806C12.8003 7.19085 12.9592 7.125 13.125 7.125C13.2908 7.125 13.4497 7.19085 13.5669 7.30806C13.6842 7.42527 13.75 7.58424 13.75 7.75V14.625C13.75 15.288 13.4866 15.9239 13.0178 16.3928C12.5489 16.8616 11.913 17.125 11.25 17.125H2.5C1.83696 17.125 1.20107 16.8616 0.732233 16.3928C0.263392 15.9239 0 15.288 0 14.625V5.875C0 5.21196 0.263392 4.57607 0.732233 4.10723C1.20107 3.63839 1.83696 3.375 2.5 3.375H9.375C9.54076 3.375 9.69973 3.44085 9.81694 3.55806C9.93415 3.67527 10 3.83424 10 4C10 4.16576 9.93415 4.32473 9.81694 4.44194C9.69973 4.55915 9.54076 4.625 9.375 4.625H2.5Z\"\/><\/svg><\/span><\/a> and <a class=\"underlined-link bbl-link-hs bbl-link-hs-v-2\" href=\"https:\/\/beebole.com\/timesheet-excel-addin\/\" target=\"_blank\" rel=\"noreferrer noopener\"><span>Microsoft Excel<svg width=\"17\" height=\"18\" viewBox=\"0 0 17 18\" fill=\"none\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\"><path fill-rule=\"evenodd\" clip-rule=\"evenodd\" d=\"M11.25 0.875H15.625C15.7908 0.875 15.9497 0.940848 16.0669 1.05806C16.1842 1.17527 16.25 1.33424 16.25 1.5V5.875C16.25 6.04076 16.1842 6.19973 16.0669 6.31694C15.9497 6.43415 15.7908 6.5 15.625 6.5C15.4592 6.5 15.3003 6.43415 15.1831 6.31694C15.0658 6.19973 15 6.04076 15 5.875V3.00833L4.81667 13.1917C4.69819 13.3021 4.54148 13.3622 4.37956 13.3593C4.21765 13.3565 4.06316 13.2909 3.94865 13.1764C3.83414 13.0618 3.76854 12.9074 3.76569 12.7454C3.76283 12.5835 3.82293 12.4268 3.93333 12.3083L14.1167 2.125H11.25C11.0842 2.125 10.9253 2.05915 10.8081 1.94194C10.6908 1.82473 10.625 1.66576 10.625 1.5C10.625 1.33424 10.6908 1.17527 10.8081 1.05806C10.9253 0.940848 11.0842 0.875 11.25 0.875ZM2.5 4.625C2.16848 4.625 1.85054 4.7567 1.61612 4.99112C1.3817 5.22554 1.25 5.54348 1.25 5.875V14.625C1.25 14.9565 1.3817 15.2745 1.61612 15.5089C1.85054 15.7433 2.16848 15.875 2.5 15.875H11.25C11.5815 15.875 11.8995 15.7433 12.1339 15.5089C12.3683 15.2745 12.5 14.9565 12.5 14.625V7.75C12.5 7.58424 12.5658 7.42527 12.6831 7.30806C12.8003 7.19085 12.9592 7.125 13.125 7.125C13.2908 7.125 13.4497 7.19085 13.5669 7.30806C13.6842 7.42527 13.75 7.58424 13.75 7.75V14.625C13.75 15.288 13.4866 15.9239 13.0178 16.3928C12.5489 16.8616 11.913 17.125 11.25 17.125H2.5C1.83696 17.125 1.20107 16.8616 0.732233 16.3928C0.263392 15.9239 0 15.288 0 14.625V5.875C0 5.21196 0.263392 4.57607 0.732233 4.10723C1.20107 3.63839 1.83696 3.375 2.5 3.375H9.375C9.54076 3.375 9.69973 3.44085 9.81694 3.55806C9.93415 3.67527 10 3.83424 10 4C10 4.16576 9.93415 4.32473 9.81694 4.44194C9.69973 4.55915 9.54076 4.625 9.375 4.625H2.5Z\"\/><\/svg><\/span><\/a>, so you can easily link all of your time data with spreadsheets in real time.<\/em><\/p>\n\n\n<div  class=\"mb-4 call_to_action-block\">\n    <div class=\"call_to_action-blockcontent py-5 px-4 text-center border-top border-bottom\">\n                    <h4 class=\"call_to_action-header h2 mt-0\">Streamline Your HR Management<\/h4>\n                            <p class=\"call_to_action-text\">Project estimations, employee costs, absences, and more.<\/p>\n                <div class=\"call_to_action-btns btns-wrap d-block d-lg-flex justify-content-center mx-auto\">\n                            <a class=\"w-100 w-lg-auto btn btn-outline-primary text-primary link-light me-lg-4 mb-3 mb-lg-0 bbl_cta_block_demo_btn \" href=\"https:\/\/beebole.com\/talk-sales\/\" id=\"cta_post_5466_article_demo_1\">Book a Call<\/a>\n                                        <a class=\"w-100 w-lg-auto btn btn-primary text-light bbl_cta_block_signup_btn \" href=\"https:\/\/beebole-apps.com\/signup\/\" id=\"cta_post_5466_article_sign_up_1\" target=\"_blank\" rel=\"noopener\">Sign Up<\/a>\n                    <\/div>\n    <\/div>\n<\/div><div class=\"bbl-post-disclaimer\">The experts who have written or contributed to this article are independent from Beebole, and their contribution doesn't serve as endorsement for our company\/tool or their past\/present organizations, employers, or associates.<\/div>","protected":false},"excerpt":{"rendered":"<p>Annual compensation reviews are a must. Reviewing and resetting employee salaries help you ensure that your employees are paid fairly. In this article, you\u2019ll learn to perform a salary and comp review in Google Sheets efficiently with our free spreadsheet template. It\u2019s no secret that compensation is crucial to attract and retain talent. When you [&hellip;]<\/p>\n","protected":false},"author":15,"featured_media":10698,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[123],"tags":[2890,2892,4012,4013],"class_list":["post-5466","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-productivity-time-management","tag-hr","tag-google-sheets","tag-tutorials","tag-templates"],"acf":[],"_links":{"self":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/5466","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/comments?post=5466"}],"version-history":[{"count":23,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/5466\/revisions"}],"predecessor-version":[{"id":13796,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/5466\/revisions\/13796"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/media\/10698"}],"wp:attachment":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/media?parent=5466"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/categories?post=5466"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/tags?post=5466"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}