Collaborate on Make Bill in Excel for HR with Ease Using airSlate SignNow

Make bill in excel for HR

in this video I'm going to show you how you can use pivot tables to analyze your benefit enrollment information specifically we're going to go over two different ways that you can use pivot tables one way is to look at a pivot table in terms of how to do a monthly billing statement and then the second thing that we're going to look at is how you can analyze your benefit information using pivot tables during annual enrollment period and what are some of the things that you want to look at in terms of benefit trends all right first thing we're going to look at here is we have a roster of our dental billing statements so this is a report that you can run either out of your HR es system or maybe your payroll system and it's a roster of all of your employees who are enrolled in the dental plan which type of dental plan they're enrolled in which which tier of coverage meeting employee plus spouse or ploy only family level of coverage and then what the cost is so the spreadsheet may have a lot of data on it but if you have if you're set up with your carrier to do a self billing statement it might look something like this and this is if you agree with your vendor that they don't send you a monthly premium but that you provide that to them after you run payroll you provide them the list of your enrollments and your accounts in your premium dollars so this is a self bill enrollment type template so how can we use a pivot table to easily fill in this template from our billing roster all right so let's go back to our roster we're gonna insert pivot table click OK and this inserts the tab with your pivot table and let's go ahead and look at the plan the tier and the cost all right so this looks good the other field of information that we know we need to put into our template is a is a count a unit count so to do that what we want to do is take the tier and drag it down under values and it's going to automatically put count if it weren't that and you wanted to change if it did the sum for some reason you would just click value field settings and change it to count and click ok so let's move this up one because we want to put that column ahead of the sum of the total cost so we just drag it up and move it let's highlight this column and make it a dollar so it looks easier to read and now this very much this pivot table now looks very much like our self billing statement so now all we have to do is copy and we hit ctrl C to copy go back to our statement here and paste in the information so now with a few clicks of a button you have automatically analyzed your your billing statement for your data plan or you for your dental plan and pasted that into your monthly billing template so that's an easy way that you can use a pivot table to create your monthly billing statement and it gives you a good snapshot of some data around your dental benefit enrollment all right now let's take a look at general information let's say this is just a sample report that has some benefit premium information been benefit enrollment information for your group and it includes employees who have medical dental and vision coverage what tier of coverage they have what the costs are and some of the dependent information for those benefits so let's look at let's insert a pivot table and click OK and let's first take a look at by plan coverage and the employee cost the employer cost and the total premium ok and then what I like to do is highlight those and make those dollar signs so they're easier to read so now you can look and see the total costs of by plan and by tier of coverage now let's say you want to also look at counts the count of all of these we're gonna go up here to coverage and bring that down over here and it's going to give us a count how many people are actually enrolled in the employee plus child level of coverage for the dental plan that's what that's showing us now let's say we want to see a percentage of that right like what percent of the dental people who are enrolled in dental are enrolled in single level of coverage versus family level of coverage the way you can do that is to go up here to coverage again drag it down again so you create another column it creates a duplicate column but then what you can do is come over here and right click and then say show value as and we want to look at the percent of the parent total and what that's gonna do is we're gonna say base that on the plan we're gonna say okay so what that means is for the dental plan what percentage of enrollment are employees only and it's forty percent so you can see there's different ways that you can slice and dice and look at this information this comes in very helpful during annual enrollment periods where you want to get a sense of your actual demographics of your benefit enrollment if you have any other questions how to use pivot tables to look at your and analyze your benefit data be sure to leave me a comment below and I'll be happy to help visit my website Sharon Smith HR calm and don't forget to subscribe to my youtube channel thanks for watching you