 in this presentation we're going to enter the payroll data into the payroll register for the last month of the year the last payroll period of the year that being the month of December we're going to enter the information into the payroll register and then put that into the earnings record support accounting instruction by clicking the link below giving you a free month membership to all of the content on our website broken out by category further broken out by course each course then organized in a logical reasonable fashion making it much more easy to find what you need then can be done on a youtube page we also include added resources such as excel practice problems pdf files and more like quickbooks backup files when applicable so once again click the link below for a free month membership to our website and all the content on it over on the right side in the same presentation a very ambitious endeavor here but i think we're going to do that a bit faster this time so it will be doable in the last month we are going to try to use some shortcuts put this information in there as quickly as possible and practice here not so much what the payroll calculations are but what we can do to kind of make the process a bit faster and learn a little bit more on excel as we go if we scroll up top we can see that the pains are frozen so to freeze the pains you want to put your cursor right in the a four cell if they're not frozen already and then go to the view tab up top go to the windows group and freeze pains and then select the freeze pains option and then we'll go down to our payroll register now note we're going to basically say that december is going to be much the same as november so we can get much of our information from november so to do that i'm going to go up top the these two are going to be uh these two columns the filing status and the number of allowances will be much the same unless they change their payroll status on their w4 so we'll highlight or select those right click on them and copy them scroll back down and put that into our current month that being the month of december in cell b 33 right click and paste 123 the hours the regular hours uh that's going to be the same too so i'm going to pick those up we're going to copy that i'm going to right click uh the rate the rate will be the same as well so let's pick that up too that'll be great so we'll pick all that up we're going to right click on that copy that's going to be the regular hours and the regular rate scroll back down we're going to put that on d 33 right click and paste 123 then we will calculate the regular pay because we want to practice that but then we'll copy that formula down learning the uh the auto fill and copy functions a bit better if we don't know those now so i'm going to say equals i'm going to use the keyboard as much as possible on this last one going left two times to the 163 hours multiply times i'm going to go left once note that uh you're always going left it'll start over like on on the cell you're calculating in in other words you're not going to go to this cell and then go right like you think you're already over there uh after the multiplication time so and then it's going to be enter and then we're just going to copy those down uh we could copy and paste the formula or we can just drag this little auto fill button so you want to get your cursor on the fill handle so it looks not like that but like that not like that but like that and then left click on it uh and you could double click on it like that i was going to drag it down but double clicking will work as well and there's nothing here of course because there's there's no calculation for um the salary so we're just going to put here the 35 000 which is her salary and that'll be this cell i'm going to format painted i'm going to put my cursor here format paint i'm not sure what happened formatting there and there we have that okay so now the hours are going to be the same so the overtime hours i'm just going to copy the overtime hours for the prior period of november right click and copy we're going to bring that down to the current period of december right click and paste one two three we'll calculate the overtime rate and copy that down as well so the overtime rate once again will be equal to the 25 in this case regular rate times time and a half or 1.5 or 150 percent and enter then we're going to put our cursor on that cell we could double click on it again or i'm just going to drag it down this time as i intended last time just two cells we're just going to drag it down two cells that's auto fill auto drives it down dr fill does the calculations and there we have it now you'll note that excel if you double click on any of these cells we'll then have put the relative reference down meaning it went to the next cell down to do the same calculation which is typically what you want in an excel sheet but something that may not be natural when you first start using excel and something we need to get used to so then we're going to say that the pay overtime pay will be equal to in i33 the overtime hours times the overtime rate given us the overtime pay then we're going to copy that down in a similar fashion using the auto fill by selecting the cell selecting the fill handle dragging down and once again we can see that the relative references have moved down that's the niceness of excel okay so now we're going to do the total pay the total pay will equal the regular pay and the overtime pay the calculation will be equals scroll into the regular pay in cell f or column f plus go in left one time to column i and enter once again we'll auto fill that down so we're just going to put our cursor on the fill handle once we're on the cell drag that down all the way down this time to judy's 35 thousand dollar salary for month of uh december not bad and then we're going to go to the wages so we got the oasti wages which has this cap here we're not too worried about these first three employees hitting that cap it's only big judy here that's going to be over that cap most likely so we can just say for these three this equals the same amount and we're going to auto fill that down with the fill handle dragging down to the other two employees then judy we're going to check her um payment record because although this number is under the 128 400 she may be over that number for the year to date which is what we're looking for so we're going to go over here to the earnings record looking for judy and judy is indeed over that amount so you can see that she's already capped out the oasti of 128 400 for the year therefore her uh social security wages or uh oasti fika wages uh will be zero so zero for that and then futa i believe they've all hit the cap for futa and suta we can just double check that if we go to the earnings records for these all these individuals because it's the final pay period it's most likely that unless we have new employees which could be the case and is something that you always have to check which is kind of a pain uh so you can see that these have obviously judy's over the cap here uh over the cap for this employee all the employees we don't need the name right now the names are a little to the left but uh we can see they're all over the cap so no futa no suta for any employee so we're going to scroll back down for the last pay period that'll make things a little bit easier a little bit faster for our ambitious goal for this uh this one so we're going to say zeros and zeros and i could highlight those two and auto fill the zero down and it'll put the zero down then we'll do the oasti calculation in cell in 33 with an equals and going to the oasti wages make sure you're picking up oasti and not the total earnings it does make a difference this time not for this employee but it will once we fill it down to judy so we're going to enter then put our cursor on that once again and select the auto fill and i like to i like to drag it down rather than double clicking on it at first unless there's a lot of data so i'm just going to drag it down and there we have it so no difference here so this is important and then that uh we use the correct column uh at this point because of course judy uh has a different oasti wage than her normal wage then the hi we're going to do a similar calculation however it'll be equals the total earnings not the oasti earnings because there is no cap times 0.0145 and enter then we'll auto fill that down selecting the fill handle and dragging down then the f it we're going to pull that from the prior pay period because the pay periods are going to be the same and therefore the calculation for federal income tax or f it will too be the same so we're just going to say that equals the prior period then i'm going to auto fill that down we're going to select that auto fill and fill that down and there we have it and it looks just like this column now so it looks like it filled correctly and notice that the moving of the cells as we fill it down with the formulas just gotta get used to how that will work and you'll note that uh it's the best way to work it's great that it it's able to see the relative references which is usually what we want now i'm going to do the group insurance which is going to be the same as the prior period and instead of hitting enter i'm going to select tab because i'm going to do the same thing for the union and the and the 401k so i want to go to the left to the right instead of down when i hit uh tab so there's next cell union dues equals and the reason i'm going to do this is that because then i'm going to highlight all three cells and auto fill it down at the same time which is going to be very exciting and uh great to do so we're gonna say tab and then the 401k equals the same as the prior period and now enter or tab wouldn't matter but now i'm going to highlight these three and we can auto fill those down by selecting the fill handle and dragging down and that should populate and look the same as up here it did of course enter the two zeros that's okay and then we're going to calculate the net pay so the net pay in cell t 33 will be equal to we're going to scroll all the way to the total earnings so here's the total earnings minus the sum we're going to say shift nine left once there's that and then hold down shift left left left left left note if you go too far then you can just go back right one and then shift zero and enter then we can auto fill that down so we're going to put our cursor on the cell on t 33 fill handle drag it down auto drives it down dr. phil does the calculations and there we have the net pay now at this point in time you're probably saying something looks very funny with the net pay and uh and you probably noticed that there was an error back here in the oast i calculation where we just i just picked up the the oast i wages which is the correct column but did not multiply times the rate which is 0.062 so let's correct that now we're going to go back over here we're going to double click and we're going to say times 0.062 now we can auto fill that down we don't have to delete these which you can just auto fill it and it'll just override it so i'm going to put my cursor on the fill handle and drag it down so that was intentional correction of um a problem and uh you can see how that works all right so then we've got the net pay looks good and it should basically be the same as a prior period because it's the same numbers so we look good there now we're going to calculate the oast i what i'm going to do now is we'll calculate this one then the medicare then the futa then the suit and then we'll drag them all down so we're going to calculate oast i we've already seen it in the employee side now we're doing the employer side which looks much the same scrolling over to the social security wages and then saying times 0.062 then selecting tab now we're in hi where we'll say equals and we'll go over to the total wages times 0.0145 tab then we're in futa where we'll say equals and scroll over to the futa wages times 0.006 tab then we're in suta where we will say equals and scroll over to the suta wages and say times 0.054 and enter okay so now we just need to i'm going to highlight these four cells we're going to select what should say select those four cells and then we're going to auto fill those four columns down so we'll select the fill handle scroll down auto drives it down dr fill does the calculations and we have completed the earnings record for the last pay period the final month of november now we're going to take this data and we're going to reformat it note that this is in format of vote for first pay period and then employee we want to then format it in the earnings record by employee and then pay period so to do that i'm going to scroll back over and we're going to make the i'm going to make this one green once again so we can kind of do it with our with almost an almost in a brain dead fashion because we're just we're just going to look for the green cells we're going to be on the green cells so i'm going to highlight all of those and we're going to right click this set of data go to the paint bucket the bucket of paint here and we'll make that light green so now when we and when we put this in our earnings record we'll have the light green over there so now we'll scroll all the way up to the top and we're going for anthony we're going for the month of december last month of the year for anthony in cell a b 8 i'm going to say equals for the filing status we're going to scroll down to those green data looking for the green data green data anthony more and we're just going to put equals or enter i should say enter and so that cell we're just saying is equal to b 33 b 33 now we're not going to auto fill it across we could but it would auto fill not only the the formulas but also the format of the cell which we don't want to do that's why we have to right click and copy the cell and then highlight all the data to the right of it or the non data the blank cells that don't yet have data and then right click and paste the formulas only formulas only just the formulas and that gives us of course the detail the reason we're doing this is that we can then give the gross check as well as all the stuff that we took up from these employees out of their gross check for their own good supposedly and then gave them the net check we can see that for the individual pay period as well as the year to date numbers now we're going to do this for the next employee and then we'll do that for the next employee and then the next one so cindy lewis so within cell ab 15 we're going to say equals i'm going to go all the way to the left and then i'm going to scroll down looking for that green data looking for the green data so there it is cindy lewis we're in cell b 34 we're going to say enter then we're just going to pick up that m once again and copy it to the right so i'm going to right click and copy put a cursor to the right into the blank data we're going to select the blank data in the blue area right click and paste it formulas only formulas only doesn't sound as nice as 123 but formulas only all right so now we're on this cell in ab 22 we're going to do the same thing we're going to say equals scroll to the left scroll down we're looking for the green data this time for jill there's jill jill's file and status we're going to say enter scroll back up and then we're just going to pick up that file and status by right clicking on it copying it putting our cell to the right of it and selecting the the cells to the right and then right click and paste formulas only and then we're going to scroll back down and we'll do this one last time for the big earner judy jones so we're in ab 29 equals we're going to scroll to the left scroll back down looking for the green data looking for the green data there's judy jones last m last file and status and enter and we're going to scroll back up just a bit we're going to pick up that m we're going to right click on it and copy and then select the data to the right of the m and right click and paste formulas only okay so there's our information now we can see if we could scroll down we've got the green zeros remember that that is our indication that the total earnings over here the net pay is equivalent to the total earnings for the year on the payroll register but let's double check it one more time this way as well we're going to say 141 976 now and by the way this is kind of like the lazy way to check it we should check each number but the net check obviously takes into account you know all these numbers from the gross check to the net check so if we're going to check one number should be this one so it's 141 976 23 we're going to scroll to the right scroll down and see if we can find that same number on the net check 141 976 23