1. Overall payroll analysis
Alright, we're in the final stretch of this course. The last type of analysis that we want to do is a high-level variance analysis, so your report will show fluctuations in this payroll compared to the same period in the previous year.
2. Current week vs. same week prior year
Don't worry, the person down the hall that does the production payroll is OK with you using her data. Phew!
Let's look at some additional departments. We have chosen to compare the numbers from this week to the same week in the previous year to eliminate any seasonal or time-of-month related variances, such as the month-end close in the accounting department that would cause increased hours in one week but not the next.
3. Add variances
First, let's add a column for the dollar variance and one for the percent variance. The dollar variance is calculated by subtracting the amount for the week that year minus the amount for that week in the previous year.
The percent variance is this difference divided by the prior year number.
Your goal is to format the departments that varied more than 2,000 dollars and more than 10 percent. The variances could be either positive or negative. Using a percentage and a dollar amount helps to filter out the large percentage variances that don't deal with a material dollar amount, like the shipping department.
If you look at just the percentage, the variance in the department looks material, but when you also look at the dollar amount, it becomes evident that the variance is not very large even though the percentage is.
4. Combination of rules
A good way to do this would be to set up two conditional formatting rules.
The first rule is for positive variances. This formula is using the AND function, so it needs the results of both of the operations to be true. Notice that we fixed the columns with dollar signs, which forces the rule to evaluate only those columns each time. That is what enables highlighting of the entire row.
The second rule looks at the negative variances. The rules are very similar, and mostly involve switching the signs.
5. Red background
For the rule highlighting the negative, use a different background fill color to help show the difference between the two rules.
6. Both rules applied
This is what your spreadsheet looks like now with both rules applied. Nice and easy to read. It is apparent that the green is positive and the red is negative before you even look at the actual variances.
You take this report for your weekly payroll discussion with the accounting manager. You see that it follows the trend of the last few months. Production and shipping have been down, so your company has increased marketing to try to bring it back up.
All of the work that you did to make sure the payroll register was formatted correctly seems to have paid off.
7. One more practice!
Now you get to try out what you have learned in this final video. We've come pretty far together. We started out just discussing what conditional formatting is and now you can use it to make a plain spreadsheet come to life!