How to Create a Merit Matrix for Salary Increase with Goal Seek Function
HTML-код
- Опубликовано: 1 окт 2017
- Want to learn how to design a salary structure? Check: www.caripros.com/design-salar...
FREE template for my video: Excel for HR - Create Annual Employee Salary Increase Template from Scratch. You can download and try it out yourself here: bit.ly/2MLLdb7
FREE actual workbook for my video "Split a Master Spreadsheet into Multiple Sheets with 1 click - VBA for Beginner". You can download and try it out yourself here: bit.ly/2UmeX2v
New course Launched! I created it to show you step-by-step how to design a salary structure with regression analysis in Excel. Check out the detail here:
caripros-hr-analytics.teachab... **HR Analytics with Excel**
Topic: How to create a Merit Matrix with Goal Seek function
Business Scenario: When planning for salary increase, you need to create a merit matrix that allows you to spend your merit increase budget properly.
Formula: Goal Seek, Index() and Match()
Steps to create a Merit Matrix:
1. Set up Merit Matrix relationship around your anchor point 2:19
2. Use Index and Match to apply increase suggestion to all employees 13:50
3. Calculate merit increase spend for all employees and sum it 14:40
4. Use Goal Seek to find the ideal Merit Matrix % 16:00
5. Adjust Merit Matrix relationship and re-run goal seek if needed 17:57
For more successful stories, view at: caripros.com/index.php/success...
*****Follow-up Consulting Services*****
If you have specific question regarding your issue, you can email me at the email here goo.gl/WejijZ Note that there will be a fee of US$200 charged for solving your issue. The turnaround is within 24 hours. Any follow-up issue in 3 days will also be answered with no charge. Payment link: www.paypal.me/caripros
*****More Videos in Playlists*****
Power BI for Beginners: bit.ly/3ivKitD
Power BI for Advanced Users: bit.ly/3lE9zmO
Excel for HR goo.gl/JdeVnd
Excel for HR - Master Class goo.gl/LYfq2f
Excel Macro - Beginner goo.gl/Yae5nc
Excel Macro/VBA - Splitting a Master File goo.gl/m8CHya
Excel Macro/VBA - Auto-hide Rows or Columns bit.ly/2Mzteb5
Excel Charts Data Visualization goo.gl/2ao6BP
Excel Vlookup Function goo.gl/kP2Wpz
Excel Pivot Table Function goo.gl/rukkPs
Excel Array Function goo.gl/i4sQH8
Excel Index and Match Function goo.gl/i7VGU4
Excel Solver/Goal Seek Functions goo.gl/FTkTnj
Excel Cell Formatting Solutions goo.gl/gpa6MY
HR Analytics - Merit Matrix goo.gl/Koy7co
HR Analytics - Salary Structure goo.gl/uZBnFa
Excel Tricks goo.gl/TeqGDw
Excel Troubleshooting goo.gl/bdY5by
Fun HR Topics goo.gl/7zVg8h
For more successful stories, view at: caripros.com/index.php/success...
#ExcelforHR#HRAnalytics#Excel#HR
You've got some of the best human beings who ever existed working for you! Who are you?!
Thank you for the step by step instructions. I needed to see every step. Very grateful for your help.
This is really good and easy to understand!Thanks a lot!
Bravo, this is an excellent tutorial ! thank you very much for providing these
I have seen several of your videos. I have to say you are amazing at teaching this material. Your step by step and pace along with explanation is extremely helpful!!! THANK YOU!!
Fabulous presentation with clear cut explanation. Thank you so much
Love your presentation! Thank you for coming up with this tutorial. You are a Godsend!
Glad you find it helpful Carol! Good luck!
Thank you very much for this gold 🥇, this morning I realized what you show us here, I'm very very happy, thank you again
Really good! Love this video
Thank you!
please keep up the good work.
Fantastic! Please don't stop making these videos!
Thank you! I will try my best =)
I know I'm a bit late, but this video have help me a lot on understanding the practical way to allocate increment from a budget given to me. Thank you so much for this!
Just amazing, thank you very much!!! I wish I watched your video earlier!
Thank you for your sharing ^^
Big Like for this amazing video🥰
This is great. Thank you
My pleasure Zuraidah!
thank you so much we need to make our own matrix and its hard because we can't really ask the teacher due to this pandemic thank you so much and nice video well explained i love it i'll leave you a like and subrscribe thank you
Very informatic video, thank you very much. However I need to know from where the data comes in the column D (Compa-ratio), Row No 14 onwards? is it manual or is there any calculations?
Thanks a lot for your detailed video its really awesome, very informative. I have a doubt in setting up the vertical salary set up, ie. performance rating % in your video ie 300,200,100 50 & 0% or rate of the hike how will we set this out? what is the criteria we set for this on a yearly basis & update the salary hike policy/philosophy. Any relationship with horizontal & vertical salary setup ? can we amend vertical setup alone ? can you please explain this. Thanks.
Thank u ! a little question : how does the match formula in the column named "compa ratio grouping" knows that there is a range when you wrote in cells E5:I5 80-90% and 90-100% ... ? it works only if i write the specific percentage, otherwise it returns NA.
thanks a lot! loved your video!
Thank you very much! A little question. How is the salary adjustment calculated for a part time employee (example 0.6 FTE)? If the salary is projected to 100%, the salary is above 120% (compa-ratio) and the adjustment is 1% in the example video with a performance ranking of 3. If I use the real salary, this salary is below 80% (compa-ratio) and with performance ranking 3, 3% is suggested in the example video. So the salary is adjusted too much. What is the formula considering the target value search with a given budget?
How would I do something similar using years of service as my guide for how much an employee raise is?
Thank you so much! Do you have a suggestion as an anchor point for a 4 point performance rating scale?
good question! not sure if you have an official bell curve distribution for the perf ratings, but the general rule of thumb is to take where you'd have the most population as the anchor point. if not applicable for your case, then you can take the midpoint between your 2 middle ratings as the anchor point. I don't recommend you to go more complicated than that as it'd confuse people and decrease their trust in the system.
@@CariprosHRAnalytics Thank you very much!
This is really good, Thanks a lot...
Can you suggest !! what was the base for Comp-ratio?
Hi Ramana - Do you mean the formula for Compa-ratio? It's "Salary/Salary Structure Midpoint" x 100%
Got it. Thanks... But I have one more question to you... I used your framework for merit review, but I am struck with pro-rata calculation for the ppl who joined in between the year. can you please help me with this. Thanks in advance.
Hi Ramana - that's a good question. For pro-rata calculation for ppl that joined mid-year, there are 2 ways to do it: you pro-rate the salary, or you pro-rate the merit increase %. I'd strongly recommend to pro-rate the salary which is easy to understand and less likely to mess up. for example, if one has $50,000 annualized salary, and joined for say 6 months (50%), you still use the merit increase % in the merit metrix, but you apply the pro-rated salary of 25,000 for this person to get his/her final merit increase $. I think I will do another video next week to showcase how to do this more clearly. Stay tuned! =D
Hi Ramana, check out my video on pro-rating salary here ruclips.net/video/WflReLOcokw/видео.html hope it's helpful!
Is there an up to date way to get these templates? The link isn't working, saying account is cancelled and not accepting new subscibers.
can you provide the same sheet for practice purpose.
Can you give new link for downloading this sheet....present one is not working. Thanks
Just want to know how to determine the anchor point?
Oh I know it. It's determined by the merit increase budget.
I don’t feel like managers should ever control raises, the fact that she said “if I feel like it,” in relation to giving an employee an increase. Smh