WEBVTT 1 00:00:01.199 --> 00:00:08.099 William Rosener: hello, this is Dr bill roesner and this video you're going to learn how to create a payroll report using Microsoft excel. 2 00:00:09.210 --> 00:00:15.240 William Rosener: Well, we created this report from scratch, but before doing so let's just take a quick overview of what you can learn how to create. 3 00:00:15.630 --> 00:00:26.910 William Rosener: So the first two rows here this information here all of this long with all this information here is going to be hard coded manually entered along with this information below here. 4 00:00:27.480 --> 00:00:34.380 William Rosener: However, all this information is going to be computed using formulas so that as this information changes. 5 00:00:34.890 --> 00:00:46.470 William Rosener: This information will automatically be re computed even just perhaps even with the overtime right, for example, right now, if we take a look at James Smith it's worked 10 hours here's the overtime pay. 6 00:00:48.300 --> 00:00:51.870 William Rosener: 95 but if I change the overtime rate to to. 7 00:00:53.250 --> 00:00:59.730 William Rosener: All of a sudden, we can see that this information is going to automatically change now go ahead and put it back at 1.5. 8 00:01:01.230 --> 00:01:06.570 William Rosener: Okay, now that we know what we're gonna do it let's go ahead and get started we'll create this whole thing from scratch. 9 00:01:06.990 --> 00:01:24.090 William Rosener: So the first thing you're going to do is open up Microsoft excel and i'm going to start start up here at the very top up here and start in sell Avon and i'm going to type ABC corporation, and then payroll report so right up here are the type in ABC Corporation. 10 00:01:29.970 --> 00:01:36.630 William Rosener: And then i'm going to hold the alt key down and press enter and those could allow me to have this information spin two rows. 11 00:01:41.250 --> 00:01:54.300 William Rosener: Okay now take a look, I want this information to go from a one all the way to K one some of the highlights all of this on the cells i'm going to press on the merge and Center icon. 12 00:01:55.710 --> 00:01:57.180 William Rosener: Okay, and drag that up a little bit. 13 00:01:58.380 --> 00:02:05.370 William Rosener: In a particular look at that that was colored something about add a little color here and put a border around the outside there. 14 00:02:06.960 --> 00:02:12.060 William Rosener: OK, the next information we need to enter is our employee some more text here. 15 00:02:13.980 --> 00:02:16.290 William Rosener: And i'm going to come over here and on the type in our lane. 16 00:02:18.120 --> 00:02:21.300 William Rosener: will hold the alt key down and enter i've been rate. 17 00:02:23.160 --> 00:02:24.870 William Rosener: And then type in regular hours. 18 00:02:28.950 --> 00:02:33.360 William Rosener: same thing all the alt key down on PC and web regular hours. 19 00:02:40.680 --> 00:02:41.160 William Rosener: whoops. 20 00:02:42.330 --> 00:02:45.690 William Rosener: i've been over time, that the alt key down five hours. 21 00:02:46.830 --> 00:02:47.880 William Rosener: How long he. 22 00:02:50.460 --> 00:02:52.290 William Rosener: To that's going to be regular pay. 23 00:03:00.000 --> 00:03:01.020 William Rosener: overtime pay. 24 00:03:09.630 --> 00:03:10.590 William Rosener: A gross day. 25 00:03:15.660 --> 00:03:15.900 William Rosener: By. 26 00:03:19.140 --> 00:03:20.850 William Rosener: Insurance security. 27 00:03:22.980 --> 00:03:24.660 William Rosener: And we're going to get state tax. 28 00:03:30.180 --> 00:03:31.320 William Rosener: federal tax. 29 00:03:38.130 --> 00:03:40.650 William Rosener: And violin and that Pam. 30 00:03:43.920 --> 00:03:47.430 William Rosener: me see here i'm on probably increase some of these decrease. 31 00:03:48.750 --> 00:03:55.980 William Rosener: But let me just go ahead and start getting some data in here Okay, maybe let's try that over here a little bit town. 32 00:04:20.070 --> 00:04:25.710 William Rosener: Okay, there we go okay now to save a lot of time i'm going to go ahead and give you the data here. 33 00:04:27.150 --> 00:04:31.260 William Rosener: it's going to be kind of manually entered if you're watching just go ahead and read. 34 00:04:32.910 --> 00:04:35.910 William Rosener: i'm just going to position my cursor right here and paste. 35 00:04:37.380 --> 00:04:46.500 William Rosener: And then take a take a look let's go ahead and maybe we had a border up there and border blow actually need to get your totals. 36 00:04:51.780 --> 00:04:56.070 William Rosener: And then i'm going to come down here and i'm going to type in rates. 37 00:04:58.230 --> 00:04:59.370 William Rosener: Over time right. 38 00:05:02.790 --> 00:05:03.600 William Rosener: Like are ready. 39 00:05:08.340 --> 00:05:09.540 William Rosener: State tax. 40 00:05:12.810 --> 00:05:18.030 William Rosener: hey let me see if I can get this all on the screen here, and once and the down here is going to be the federal. 41 00:05:20.640 --> 00:05:21.150 William Rosener: tax rate. 42 00:05:25.800 --> 00:05:30.000 William Rosener: Okay, OK, and now we understand values here 1.5. 43 00:05:32.340 --> 00:05:41.310 William Rosener: And these are going to be percentages i'm going to go ahead and in beforehand or and i'm gonna highlight these three cells i'm going to come right up here and choose percentage. 44 00:05:42.690 --> 00:05:44.820 William Rosener: And then, this is going to be 7.5. 45 00:05:49.380 --> 00:05:50.220 William Rosener: and seven. 46 00:05:56.400 --> 00:05:56.790 William Rosener: OK. 47 00:05:58.320 --> 00:06:06.390 William Rosener: OK now ready to enter our first formal here, and to do this, all we're wanting to do is something I could put a little border underneath there. 48 00:06:09.450 --> 00:06:27.360 William Rosener: And I think, as you may be saw there if you want to resize columns just simply a position your cursor between like, for example, CND and you can resize those and the same thing with the Rose it can increase or decrease the height of the risk there. 49 00:06:28.710 --> 00:06:49.080 William Rosener: Okay, so the first one is the regular pay, and that is simply going to be the hourly rate times the number of hours worked in so far in this first one that's going to simply be what's in this cell be three times CS three someone come right up here and then type in equals. 50 00:06:50.190 --> 00:06:52.500 William Rosener: be three times. 51 00:06:53.670 --> 00:06:54.330 William Rosener: c three. 52 00:06:56.220 --> 00:07:06.570 William Rosener: Okay, and that's going to compute the regular hours in this case, we can just if you want you could come down here, and you could go ahead and type equals. 53 00:07:07.290 --> 00:07:16.110 William Rosener: b for see form, etc, there are what we can do is just like Microsoft excel finishes formulas and reposition my cursor over the. 54 00:07:16.410 --> 00:07:32.010 William Rosener: phone handle when it turns and turns to those cross hairs I can just drag it on down and now if I take a look at the next one sure enough it's before time see for this one it's going to be be five years be five times see five them. 55 00:07:33.300 --> 00:07:42.510 William Rosener: Okay, and now next one we're going to compute is the overtime pay and this one's going to get just a little bit more trickier we're going to take the hourly rate. 56 00:07:43.170 --> 00:07:57.660 William Rosener: times the overtime times number of overtime hours times the overtime rate, so this is going to be equal to, so our formulas in Microsoft excel start with an equal sign or take a be three. 57 00:07:59.430 --> 00:08:00.390 William Rosener: times. 58 00:08:01.710 --> 00:08:02.460 William Rosener: D3. 59 00:08:05.790 --> 00:08:12.030 William Rosener: And i'm going to momentarily I enter this one incorrectly, but then I just going to work for the first one and i'm going to put beat 11. 60 00:08:13.350 --> 00:08:22.440 William Rosener: Some is simply multiply these three the hourly rate times number of hours times the overtime right and i'll hit enter. 61 00:08:23.160 --> 00:08:31.440 William Rosener: Okay, and this employee has worked zero hours, so that we have no overtime Pam now watch what happens if I drag this one on down. 62 00:08:32.340 --> 00:08:40.860 William Rosener: And you start going well what's the deal here, James Smith has got 10 hours of overtime, yet the overtime pay zero. 63 00:08:41.520 --> 00:08:51.570 William Rosener: So when we take a look here, the first one was correct, but the next one notice this one to be loving that's what I wanted to look at, but on this one it's gone be 12. 64 00:08:52.260 --> 00:09:01.050 William Rosener: it's going to be 13 etc, there are we're going to do is make that an absolute reference, and you can do that just by simply putting dollar signs in front of those. 65 00:09:03.450 --> 00:09:15.480 William Rosener: So now i'm going to take this information times this times this information and be 11 it's going to be an absolute reference so as I drag that down. 66 00:09:17.280 --> 00:09:28.740 William Rosener: or taking a look at the last one here i'm going to take be seven times D seven but i'm still multiply that times be loving there to get the overtime right there. 67 00:09:30.270 --> 00:09:36.360 William Rosener: Okay, the next one is going to be the gross Pam and that's just going to be as simple summation to take the regular pay. 68 00:09:37.590 --> 00:09:42.510 William Rosener: And we'll get this format here a little bit better, as I move on i'm just going to simply equal. 69 00:09:43.890 --> 00:09:45.090 William Rosener: little bit Type II three. 70 00:09:46.440 --> 00:09:47.640 William Rosener: plus three. 71 00:09:48.990 --> 00:09:56.130 William Rosener: And that's going to give me the gross Pam and i'll go ahead and position my cursor on the film handoff and drag that on down. 72 00:10:00.090 --> 00:10:14.790 William Rosener: Okay, the next one is going to be the biker that's going to with social security, and in this case, all i'm gonna do is i'm going to take a gross pay i'm seeing information and be well. 73 00:10:15.870 --> 00:10:21.060 William Rosener: And then i'm actually going on around that but let's go to let's go ahead and start i'm just going to take equals. 74 00:10:22.800 --> 00:10:23.550 William Rosener: Two three. 75 00:10:25.380 --> 00:10:26.220 William Rosener: times. 76 00:10:27.990 --> 00:10:37.260 William Rosener: Be 12 and just like we did earlier, since I don't be copying this formula i'm going to make this an absolute by putting dollar signs. 77 00:10:39.900 --> 00:10:46.680 William Rosener: For the B and before the top and we really needed before the 12 because the be i'm not modifying that value there. 78 00:10:47.970 --> 00:11:01.710 William Rosener: Okay, so that's going to take v3 that's going to take our gross pay times or fica rate the trick here is i'm going to go ahead and around this information, some type of round. 79 00:11:04.590 --> 00:11:11.460 William Rosener: And then i'm going to take this information and then i'm a simple product to around that to two digits them. 80 00:11:13.230 --> 00:11:24.390 William Rosener: Okay, so it should be getting the 28th point Okay, at this point, I can simply a position my cursor on the phone handle and drag that on down. 81 00:11:28.050 --> 00:11:33.840 William Rosener: Okay, the state tax almost very, very similar there are going to be taken equals. 82 00:11:35.580 --> 00:11:38.250 William Rosener: going to be taking the gross pay G three. 83 00:11:42.360 --> 00:11:44.040 William Rosener: And we multiply that by. 84 00:11:45.450 --> 00:11:50.760 William Rosener: Be 13 i'm gonna put dollar signs in front of the beam. 85 00:11:52.890 --> 00:11:54.330 William Rosener: In front of the 13. 86 00:11:56.520 --> 00:12:02.730 William Rosener: And I could position it there now, I want to do is put around on this and apply around. 87 00:12:04.770 --> 00:12:13.800 William Rosener: and comma to run and that to two digits and looks like we should get 1920 Okay, let me go ahead and drag this one on down. 88 00:12:15.810 --> 00:12:22.050 William Rosener: And there we go okay for the photo tax one more here i'm going to take a equals. 89 00:12:23.730 --> 00:12:25.680 William Rosener: going to take the gross pay G three. 90 00:12:28.140 --> 00:12:35.190 William Rosener: Times be 14 again I want the hips the reference we put dollar signs in front of those. 91 00:12:38.880 --> 00:12:44.910 William Rosener: Now i'm going to come along and with the round so i'm going to type in round open parentheses. 92 00:12:46.020 --> 00:12:49.980 William Rosener: comma to and close that. 93 00:12:52.020 --> 00:12:53.760 William Rosener: And at this point, I can simply. 94 00:12:55.290 --> 00:13:10.200 William Rosener: position my cursor on the phil handle and drag that on down OK now for the last one here, the net pad on my do is i'm gonna take the gross pay minus three three values so i'm going to type equals G three. 95 00:13:12.300 --> 00:13:18.660 William Rosener: And we know either a capital a lowercase letters minus and we'll put these in parentheses each three. 96 00:13:21.330 --> 00:13:22.950 William Rosener: plus three. 97 00:13:24.870 --> 00:13:25.440 William Rosener: Was. 98 00:13:26.460 --> 00:13:27.150 William Rosener: Jay through him. 99 00:13:31.230 --> 00:13:45.180 William Rosener: And Microsoft excel gives you some great feedback notice that they actually hit the for their second ago and they can see whoa that was incorrect there, so I want to make sure i've got to the three, so it is highlighting the cells as a type. 100 00:13:46.350 --> 00:13:53.130 William Rosener: So i'm going to simply take the gross pay i'm going to add these three together and subtract them in the city of the key now. 101 00:13:54.660 --> 00:13:58.590 William Rosener: At this point, I can go ahead and drag this on down. 102 00:13:59.790 --> 00:14:11.400 William Rosener: OK now for the totals column looks like I started in gross pain all we're going to do is up some of these up equal some phenomenal tape G three. 103 00:14:12.570 --> 00:14:13.230 William Rosener: On. 104 00:14:15.810 --> 00:14:17.490 William Rosener: And that's going to be geez. 105 00:14:20.460 --> 00:14:30.480 William Rosener: Okay, and it's just going to simply add that up this point i'm going to go ahead and position my cursor here, and I can drag it on over to get my new values. 106 00:14:33.270 --> 00:14:39.540 William Rosener: Okay, I think that's looking pretty good here, maybe just a little format and i'm going to highlight this room come up here. 107 00:14:41.370 --> 00:14:45.150 William Rosener: and choose a thick bottom border. 108 00:14:46.380 --> 00:14:53.400 William Rosener: And I believe I placed this information and some color so i'm going to add this and to put a thick border around the outside. 109 00:14:54.960 --> 00:14:57.330 William Rosener: And maybe we'll have a little feel calm. 110 00:14:58.620 --> 00:15:05.550 William Rosener: and freedom even fill in a little bit more information here, we can put in a another bottom border. 111 00:15:08.670 --> 00:15:18.420 William Rosener: If you wanted to read even highlight these two cells in merchant Center those like sound Okay, let me just take a look here i'm looking pretty good here. 112 00:15:21.960 --> 00:15:36.600 William Rosener: Okay, so one more couple more things here one is saving the file so to save this file i'm going to come right up here i'm going to go to file we go to save as i'm going to browse. 113 00:15:38.220 --> 00:15:45.900 William Rosener: And in this case I could just save it right here roesner functional where you're going to save it and I could call this one slide the one. 114 00:15:50.370 --> 00:15:53.700 William Rosener: In a can when I can put a dash here and say that. 115 00:15:54.900 --> 00:16:05.910 William Rosener: A one last thing here is in addition to creating this spreadsheet i'm also going to have like this on this particular assignment and all the other assignments, are going to have you. 116 00:16:06.450 --> 00:16:16.890 William Rosener: kind of work with the tutorial that I have not created, and there I want you to enter once you to press on them create this new tab icon down here. 117 00:16:18.780 --> 00:16:24.180 William Rosener: And I think I created Wonderland so that's going to go ahead and i'm going to type in tutorial. 118 00:16:25.080 --> 00:16:31.920 William Rosener: And here, is where you're going to possibly provide a summary you're going to use insert some more formulas. 119 00:16:32.400 --> 00:16:47.190 William Rosener: Possibly some tips examples, etc, that you've learned when you walk through the tutorial on website that can then that's going to be something that I have not personally developed so anyway let's take a look here i'm gonna come back to. 120 00:16:48.600 --> 00:16:55.020 William Rosener: here's my sheet one So hopefully in this video we learned how to create. 121 00:16:55.770 --> 00:17:09.150 William Rosener: A payroll report using Microsoft excel and again make sure all of this information is computed using formulas it's not hard coded so that if I were to come along and change one of these values. 122 00:17:09.990 --> 00:17:21.570 William Rosener: Even if I changed the regular hours here to 15 i'm going to see that all this information gets changed automatic lab or Obama changing these values all of. 123 00:17:22.380 --> 00:17:30.360 William Rosener: The appropriate information would be changed accordingly in there, so make sure you save that also that you've created a tab for the tutorial. 124 00:17:30.690 --> 00:17:40.830 William Rosener: And when you finish this assignment you're going to see that there is also some additional work and that's where you're going to place that and then say that then i'll have a separate video that walks you through uploading. 125 00:17:41.280 --> 00:17:45.750 William Rosener: Your spreadsheet to your website anyway thanks i'll catch the next video.