 in this presentation we will continue on with our comprehensive problem for the payroll register going to our second pay period that being september we have our first pay period here of august 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 than 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 we're going to continue with that information for september note that we don't have another header line down here before the september data input sheet what we're going to do is we're just going to freeze the pain which is going to be an excel kind of trick technique that we're going to use and that's going to make it so this sells above this line will stay the same now before doing that i'm going to scroll all the way to the left and then i'm going to scroll down just a little bit because i don't really need to see these two rows when we freeze the pain these two rows i'm going to scroll down just till we just see this header here and once we do that i want to put my cursor right above the header and all the way to the left so you want your cursor in a four uh a four then we're going to go to the view tab up top we're going to go to the windows groups these are the groups we're going to go to freeze pains select freeze pains and then we will freeze the pain pain has been frozen no more pain it's frozen and then we're going to scroll back down and you'll notice that the top rows will stay the same now just to get perfectly down there if i if i scroll down with my scroller it takes just goes too far and i want one more row to do that i'm just going to go down here and just click one more time and that'll give us on our on our tab here so it takes a little while to get used to that freeze pains once you have it it can be useful depending on the worksheets that we are using okay so we're going to go a little bit faster this time we're going to use the similar information notes that uh the so if we go too fast go back to the prior uh pay period and it'll be much the same that's what's great about accounting and payroll it's going to be much the same from period to period once we do this few times especially if we're working with the same company so we can get this information is going to be exactly the same if i scroll back up in other words the marital status and the number of exemptions won't change unless they're changed with an updated w4 so typically i can even just copy these i can right click copy and go down here and just say that those are pretty much the same right click and paste we're not going to make any adjustments in this problem to the number of allowances typically they'll be pretty much the same for most employees unless again they make a change to the number of allowances or you know someone gets married or or stops being married so then now we're going to go through and we're going to enter the number of hours so for anthony more we're going to say it has 163 hours for the month of september the rate is going to be the same as the prior month 25 the regular pay then we're just going to go straight through and calculate this everything that's the same we won't put as much detail into it to it we'll do it right in excel here we're going to say this equals the 163 hours times 25 the rate now note if i hit enter it'll go down to the one below it if i hit tab it'll go to the right side which is what we want so i usually get in the practice of hitting tab it goes to the right we're going to say that there's three hours of overtime and again these hours in the overtime we would have to calculate by a timesheet or a time clock so i'm giving you this data that we would have to compile through whatever time instrument we're using it's some kind of punch in punch out time clock system so we're not so there's three hours also note that the hours are our overtime would have to be calculated however that is done typically 40 hour work weeks anything over 40 hours would have to be overtime i'm going to give us the overtime hours here just for the purposes of the problem so uh that means that there's 163 plus three or 166 total hours three of them over time 163 regular time the ot rate is going to be the regular rate times time and a half so this equals the 25 regular rate times one the time 0.5 and a half so that's 150 percent of the regular rate tab that's going to be 37 50 then we're going to take the hours overtime hours times the overtime pay rate so this equals the uh three hours times the 37 50 pay rate tab now i'm going to add up the total columns or the total earnings which is going to be the regular plus the ot and that's going to equal the regular in f12 plus the ot in i12 and tab now we're going to go through and pick up the um earnings for oast i futa and suites now note oast i no one's going to hit the limit yet because it's only the second pay period and that's pretty high limit for the for the wages we're talking here so no one's hit that so it's can just be equals the same now remember that this number could differ from total earnings if uh there was some uh pre-tax deduction which could include cafeteria plans for the insurance so just note that if this was a qualified section 125 plan which we're going to say it's not for the purposes of this problem then we could uh reduce uh fight or fit federal income tax social security oast i uh and the medicare tax for it but we're going to say this is a post tax non section 125 deduction will be coming out of the paycheck but it's not going to be something that's going to be reducing the taxable earnings for fit oast i and medicare futa however uh is an area of difference here futa we picked up the same number but we want to there's a 7 000 cap so if i go over to the the earnings schedule for like anthony because it's only the first month the cumulative earnings are just the first pay periods earnings so it's really just this 4025 is the only earnings in the prior month but if there were more than one pay period we'd have to go back here not just to the prior month and see how much has been earned so that means that uh we need the lesser of what he got paid this time period or what takes us up to the cap of seven thousand so in other words to get to the cap here of seven thousand seven thousand minus this uh 4025 means that there's only we can only have 297 of wages if the wages he was paid is greater than that we have to pick up the 297 and not go over the seven thousand cap so i'm scrolling back to the left we're going to go back down well let's keep this so we can see this data so we can pick up that same number here since there's only one pay period so we could say he got paid this time more than that amount so it was two thousand something so we're going to do a subtraction problem so it's going to be seven thousand minus what he got paid to date which is just this one pay period so he needs another uh two thousand nine thirty seven fifty to get up to the seven thousand this plus this now being the cap out for his life of seven thousand or at the company's income the life of the company income we're going to do the same thing for sutex except the cap is eight thousand so again if if whatever he needs to get up to eight thousand is less than what he got paid then that's what we need to pick up which it is so we're going to say this equals the seven thousand minus what he got paid prior and that's going to be the two thousand uh i'm sorry eight thousand should be eight thousand equals eight thousand minus what he got paid prior and that's going to be the three thousand nine thirty seven fifty okay so that's going to be the new thing this is basically the new thing this time and we'll go to the next pay periods we'll we'll we'll see that this basically goes down to zero so now we're going to do the payroll taxes so we're going to scroll to the next item so we are in in 12 i'm going to scroll back down so we don't get confused on what we're calculating so here we are we're on the oast i we're going to pick up the oast i wages times 6.2 or 0.062 so this equals pointing to the four thousand one eighty seven fifty times 0.062 and tab then we're going to go to the medicare hi we're going to say this equals and we're going to point to the total earnings because there's no cap so we don't need a medicare wages times 0.0145 0.0145 what i missed the one 0.0145 so 0.0145 tab and then the fit this is going to be where we need to go to the table again so i'm going to scroll back to the left note that when we look up the federal income tax the fit within the table we will be using total earnings which isn't exactly correct we should be reducing it by the 401k plan because that's going to be a pre-tax calculation for federal income tax so in that case we're using a number here that's going to be a little too high when we look up the table the result of that would be that we're going to be withholding a little too much and if that were to happen then at the end of the year when the 1040 is filled out we'd have too much withholding which would typically result in a higher refund or a lesser tax owed at the end of the year at the 1040 calculation now it's going to be married four and the wages are going to be 4187.50 so we're going to go back here we're looking for the tables for married monthly four and the wages are 4187 so monthly married we're scrolling down four thousand one so it's going to be between here and here so i'm going to go over to the four and there it is so this one eight 187 187 yeah i think that's it so i'm going to go back here and note that again most of time most people aren't going to calculate this FIT manually like this but it's not nice to do from time to time so you can see kind of the complications of it the computer will typically do a better job and make a less likely to make an error in this area note if we do make an error on the FIT it'll it'll wash out basically the refund you should get a greater or lesser refund on the on the end of it when we do the the 401 the 1040 for the individual so when they do their 1040 they should get the refund or perhaps have to pay if there's an error involved in it if it's an SI if it's an error involved in the OASDI or the Medicare we have possibly more of a problem in some cases because it won't it may not be refunded when the taxes are done and therefore we would have to amend that but these two it's less likely to make an error because they're very easy to calculate okay so we're going to go to the insurance we're back to the insurance here it's just going to be the same we're going to give the insurance number and the insurance number will be i'm just going to pick the same up from last time last time it was this number tab the union dues are going to be the same tab the 401k is going to be the same so tab and again those will be dependent on the conditions then i'm going to scroll back down so let's see there's something i'm scrolling around a bit then we're going to do the net pay which is going to be the total earnings minus all the deductions OASDI HI FIT group union and 401k we'll do that with a formula this equals the total earnings minus the sum double click the sum function and we're going to pick up the OASDI all the way to the 401k shift zero and close it up and so there we have it so here's the growth pay minus all this stuff gives us the net pay now we're going to do the employer calculations and the employer taxes for OASDI same it's going to be the same amount but i like to do the calculation again kind of a double check i guess it's going to be this 4187 times 0.062 and that's going to be the 259.67 uh 63 which is equivalent to here we'll do the same for the HI which is Medicare so this is going to equal scrolling back over the total earnings times so you go back over so we can see it times 0.0145 that's going to give us the 60 which should match what we have here and then the FUTA we got to take the FUTA wages which is different than what we calculated in the last pay period that's going to equal this 2009 3750 times 0.006 tab and then SUTA which is going to equal the SUTA wages we scroll back over which is this one in M12 times 0.054 and ENTER and so there's going to be that information now we'll do the same thing for the next employee so I'm going to scroll back over all the way to the left we've got Sandy Lewis married for we're going to go through our hours here we're going to say that the hours were 161 this time we're going to have the same pay rate of 28 per hour multiplying out the regular pay it's going to equal in F13 this 161 in D13 times the $28 an hour and tab then we're going to get the overtime hours which will be one have one overtime hour we'll calculate the overtime rate again which is going to be equal to the regular rate times time and a half times 1.5 150 percent tab then the overtime pay is going to be the one times 42 we obviously can do the math in our head but we're going to calculate it so we have the same formula the one hour times the 42 equals G13 times H13 tab then we're going to add up for total earnings the regular pay and the overtime pay so in J13 equals the regular pay in F13 plus the overtime pay in I13 tab then the OASTI wages no one's going to have hit that one or they're not going to have hit that one 28 400 so it's just going to be the same equals that same number the we're going to have the same issue with the 7000 they will have hit that so what we need to do is just get the enough wages to get to 7000 it's not going to be the full 4550 for FUTO wages we need whatever it is to get to the 7000 which will be less than this amount we'll do that with a subtraction problem which will equal the 7000 minus the prior periods wages because that's what it was up to this date there's only one prior period and that's going to be 2005 73 meaning this plus the prior period equals the 7000 which is the cap so we can't use the whole amount because it capped out same thing with SUTA except there's an 8000 cap so equals 8000 minus the prior period SUTA 4,424 tab then this number plus the prior period goes to the cap of 8000 so we can't pick up the full number here we have to pick that up going to scroll back down so we can see our headers better and we'll go through the OASTI so this equals the OASTI wages times 0.062 tab the HI or Medicare equals the total earnings because there's no cap times 0.0145 and then the FIT so we're gonna have to scroll back over and we'll find that FIT for Sandy Lewis M married four monthly pay period the amount that we're looking up is 4,500 4,550 so if we scroll back over here we're looking for our tables which are here and we're looking for the married monthly the 4,550 so here's 4,550 now that one falls on the line here typically we'll do the higher amount so we want the higher amount because that'll withhold a little bit more and that's like a little bit more conservative we'd rather have a refund in other words at the end of the year when they do the 1040 then to owe money so we're going to try to err by withholding a slightly too much than too low although the dollar amount's probably pretty minor so here we have the 235 we'll go back to our table and we're going to put in just going back over to the FIT 235 group insurance i'm going to say that's the same so we're going to pick that up and again this would this could vary i mean this could differ depending on the terms but i'm going to pick up the same for the union dues i'm going to say the 401k is going to be the same it may vary depending on the earnings but we're just going to keep the a standard or fixed 401k contribution it just depends on the options that were agreed upon between the employer employee and the plan then we're going to do the net pay which is going to be the total earnings minus all the deductions and taxes so this equals the 4550 minus the sum double clicking the sum function highlighting the OASDI to the 401k tab it's going to close up that's good so there's our net pay so there is the net pay now we'll calculate the the employer taxes so OASDI should match this amount but i like to recalculate it so we're going to say this equals the OASDI wages times 0.062 which will be that same 282 282 the hi or medicare equals and we're going to scroll back over it's going to be from the total amount here times and then it's going to be the 0.0145 tab and then the futa we're going to say equals the futa wages which will be this amount in l13 times 0.006 tab and then the su ta will equal the su ta total in n13 times 0.054 entered i picked up the wrong number there so let's delete that it's going to equal we're going to scroll over just one more to find the su ta number times 0.054 all right so that looks that looks more reasonable next employee scrolling back down we're looking for jill and so jill here has a single one and 140 hours on regular pay and the hourly rate is 31 regular pay will equal the hours of 140 the normal regular hours times the 31 dollars an hour and then we're going to have the overtime we're going to say that there was zero overtime overtime rate will still calculate it even though there's not any overtime to keep in conformity with our formulas equals the 31 regular rate times time and a half or 1.5 150 percent and then the overtime pay then we're still going to calculate it even though it's zero equals the zero hours times the 4650 overtime rate then the total earnings is we're still going to calculate both numbers even though there's only a regular pay equal to the regular pay plus the overtime pay then the oast i they're not going to hit over jill hasn't hit the overtime yet for uh hasn't hit the oast i cap yet therefore this is just going to be the same number tab uh the seven thousand will have hit the seven thousand so same thing on the seven thousand we're going to say this equals the seven thousand minus the prior time period tab and so the prior time period is what they and what she has year to date in earnings plus uh the current time that makes it the seven thousand therefore we're going to pick that number up instead of the wages here same thing for the suta except for the eight thousand cap so it's going to equal eight thousand minus the suta wages from the prior time period time our tab that's gonna be 2005 90 this plus the prior time period is that eight thousand dollar amount then we're going to have the oast i so i'm going to scroll back down the oast i will equal the oast i wages times 0.062 tab the hi or medicare will equal the total earnings times 0.0145 then the fit we're going to use our tables again which is going to be single one and four thousand three hundred and forty so going back to our tables we gotta scroll up to the single table so married this is a single table monthly and we're looking for four thousand three hundred and forty and the one column is here so four thousand three hundred and forty is going to be in between here and the one column is this one because that first column is a zero column so that's going to be 491 wait a second that's 4405 it's actually 43040 we're looking for so 4325 to 430 it's going to be between this column which i believe is that 474 that we want yeah that 474 so we'll go back to our excel worksheet we're going to pick up that 474 i'm going to scroll back over fit will be 474 group insurance we're going to say that's the same as the prior period so i'm going to just say that's the same no union dues 401k we're going to say that's the same just to keep this simplified and then the net pay so we'll calculate the net pay that's going to equal the total earnings minus the sum double click the sum function of the OASDI to the 401k then we're going to close that up that's fine okay and then we want the OASDI for the for the employee or recalculating it equals the OASDI times 0.062 that's going to be the 269 which should match here and then when the hi is going to equal the hi wages which is going to be just the total scrolling back over times 0.0145 tab then we have the footer and the footer will equal we're going to scroll back over to the footer wages which is at l 14 times 0.006 tab and then the suta which will equal scrolling over to the suta times 0.054 and enter next one we're going to scroll back over last employee uh married three we're going to say this is a salaried employee so this time we're just going to say salary for the regular pay actually we're going to say salary for the uh regular hours and we're going to say that it's 35 000 and the total earnings is just going to be the same so we'll just say that equals the same amount now the OASDI she's going to get a lot closer here because she's got that 35 000 but she's not there yet she's at i mean if we look at the earnings so far 35 000 and then the 35 is the 70 so we got obviously be careful here on the OASDI i shall be the first one or the only one that will possibly hit that cap hasn't yet those so it's still the 35 and then the futa already hit the futa here so there is no futa so it's gone and it's going to be gone for the rest of the time period so this always happens in the in the early periods or the early payroll periods the futa is going to be everything and then it's going to fade away uh and then obviously if we weren't monthly if we were something other than monthly then it would fade away a little bit more slowly if we paid people weekly rather than monthly but uh it's it's going to go away after the first quarter or the first quarter and a half typically for most employees okay and then we're going to say the OASDI will equal the OASDI wages times 0.062 tab and then the hi or medicare will equal the 35 000 times the 0.0145 tab and then the fit now it's going to be the same we can do the calculation again but well let's take a look at it if we scroll back over here i'm going to scroll back up and we we have to use these tables up top so we'll do this at least one more time we might not do it next time it's going to should be the same but we'll take a look at this percentage calculation and so we got to go all the way up past these tables because the tables are not uh going to have enough numbers in order to calculate this it won't be high enough to calculate the wages now remember what we're going to do is we're going to scroll back up top and we're going to pick up this number first so 34580 so 34580 times 3 because she has three exemptions so this number this 1037 40 is what we're going to decrease the total earnings of 35 000 buy to then use in the table so this minus minus the 35 000 is 33 962 if we scroll down to the correct table now that's the number we're going to use at 33 000 or so we want to find the monthly payroll table and we're going to find the 33 is going to land in between here in between these two numbers so we we're going to say 55348 plus 32 percent over the floor which is going to be this 27 right this first number the 27 here 27 so if we take out the calculator here what we're really doing is we're saying how much is actually in between this this section well we said it was 33 962 60 minus the floor of of this category which is 227 1213 means that there's 6749 60 within this range and that's the 32 percent range so times 0.32 that's how much is going to be taxed at the highest 32 percent and then we're going to add to it what was in there before from prior brackets which we can calculate from the floor already which is going to be plus this 5348.26 so that's going to be that 7508 so i'm going to go back over here and we'll see the formula for i'm all i'm going to do is copy the formula so if we scroll here the formulas here i'm going to copy this and just put that full formula right click and paste 123 so i just right click and copy right click and paste and then let's just take a look at it if we double click on it it looks really complicated here because we don't see the subtotals so it's almost easier to calculate this not in this kind of fashion but we can do it this way and it's it's nice to be able to see so what we're taking is this 35 000 minus the exemption amount of 345.8 times three because there's three exemptions so because of the order of operations it's going to multiply first and then subtract and then it's going to take this bracketed number whatever we get there and say that's minus the floor number 27213 and and then it's going to take that's going to be really the number we calculate this whole thing at the highest bracket of 32 percent times 32 percent then plus the 5348 all other brackets added up so that's going to be that number then the group insurance same thing as the prior month so we'll just say that equals the 1500 nothing in the union dues and then the 401k will be the same and so the net pay i'm going to scroll back down a little bit will equal the total earnings minus the sum double clicking the sum highlighting the oasti to the 401k tab and we're going to say yes that's what we want and then the oasti should be the same here but i'm going to recalculate it equals the oasti wages times 0.062 tab and then the hi or medicare and note this number matches this number equals we're going to scroll all the way over to this 35 000 times 0.0145 tab and that number should match this number and then the futa equals i'm going to scroll over to the futa wages and it's zero times the 0.006 and then the su ta wages which is also going to be zero times 0.054 so there we have those numbers