Both sector. Welcome back. In this video, you'll learn how to create formulas for the payroll shown before you. Let's start by taking a quick look at much lower performance that will be created.
So first of all, would have this imaginary companies that say there's five employees or have their rate and their hours.
This information is going to be kind of like hard coded everything else all this data and this data will be computed you some formula.
The first formula is going to be over time. So if someone worked 40 hours or so anything over 40 is over time. So they work a half an hour.
Right take their gross pay. In this case, the gross pay is going to be the rate times the hour, but they worked over time. It's time and a half.
And pretax were just take off one third to the net pay, which is going to be the progress k minus attacks and then the our comparison I take a look here.
The average employee work 35 hours. So this case Adam has worked five and a half hours more than the average and play. Well, now that we know we want to do, let's go ahead and get started here. So the first step. I'm gonna do is open up Microsoft Excel.
And the very top of their it said a big
Mark
Weekly.
Payroll and I want that information to span from a one to each one. So the highlight that information. And I'm going to come up here and go merge and center so Merchant Center that information and then I'm gonna go ahead and get a little bit larger font size, sir.
Okay, if you want to. I'm gonna go ahead and fill that in. I could come right over here and pick a color.
And then I can also put a border around the outside. Maybe if they voted for him.
One thing you're going to notice it doesn't look like there's a board on the top on the left, but if you print it out, sure enough, there will be more to their table. Let me just hit a little bit larger font size all this, maybe I even make this a little bit larger bigger
OK, so now let's type in employee.
And I'm going to position my cursor, or if you've got a smaller font size, you wouldn't have to do this, I'm
Just make a little bit more room here employee type in the rate hours.
Overtime
Recipe
Tax
Net pay and then this one is our and now we're going to do some hold the Alt key down and type in comparison
Here, the reason for doing that. I take a blog. This one that information expanded to going to two lines over there's like break them. Okay, now let me go ahead and resize some of these columns.
See if I can get a little bit higher.
Over time, maybe I want to look too large to
Reduce this
Okay that's looking pretty good right now.
Okay. Okay. Looks they need to do is add some please Adams.
Baker.
Moda.
Walker.
I also want to take a look, I've got a line underneath this header and over here on the left. Maybe I'll add average min and max.
Beverage
Minimum
Maximum
Okay, this one I'm going to highlight this information.
And we'll go ahead and insert a bottom mortar.
They're going to highlight this information and also insert a right border. Okay, I think we're looking pretty good. Here, let's just take a look here.
One little thing here. I could get to our comparison Seattle where our the center there. I could highlight that so
And some of that. Okay, I think we're ready to go. So the next step. When I take a look. Is this is formatted has currency. Notice that dollar sign two decimal points. So make employment enter the data. I'm gonna highlight all this room, and I'm gonna come up here and format that as currency.
Okay, so start off with a 25 10.5 770-520-1565
OK.
And I'm going to share about the format. It looks like that. Maybe it was centered. We get that center as well.
Ok now for the hours, how this worked 40.5 48 25.5 20 and 41 okay one day, which I always like to do is have consistency here. So if I take a look at this. Notice I've got
All of them with two decimal points even like a 20.0 20.00 or 41. Okay. Well, what I'm going to do is to simply highlight that I'll keep on going down is I'm going to want this information for men to say when
I can press on this little down arrow and I'm going to choose number. And I just want to make sure decimal places to set to two. So number. That's the places to and press again.
And maybe I get that center as well. Okay, that is information that's going to be kind of like hard coded the rest of information is going to be computed use in formulas
So the first one here, Adams work 40.5 hours you worked on this soon there anything over 40 hours overtime. So he worked half an hour overtime. Well, how can we write that. Here's the formula. I'm not start off with our form to start off with the equal sign on the type equals
I'm gonna take a look at information and see three
That's 44 five is that greater than 40 if so I'm going to do subtraction. We take what's in C three and minus for him.
And that that evaluates defaults, which you can see here, and I'm just going to put a zero. So there's the formula.
Okay, so again, the big thing with Microsoft Excel is a change this to fit all of a sudden his work 10 hours over time, not be doing that server time soon, where they went.
Ok now for
The gross fan. I'm just going to get a little tricky here. It's going to be the right times yeah hour but again they get time and a half if they work over time. So the gross pay is going to be be three.
Times C three and that would be simply the rate times the hour. But now if you want to deal with overtime.
I'm going to do a plus. And I'm going to take this information, which is in the thread the river hours they work overtime.
And I'm going to multiply that times
Have the rate which is the
Three divided by two.
You and take a look. And now, that's correct. There. So anyway, what we did is I took be three times c three.
Plus and what you're going to find as multiplication has precedence over edition. So it's going to do this first
And second, going to do this, then it's going to add them up. So the last step is going to do is add up the overtime times half of the rate to get my
Chris K ok for tax by time you take out federal tax a tax and security seems like it's about a third. So I'm just going to simply go ahead and put equals, we're going to take this information, which is an E three
Times.
Let's go point three three.
Okay that's looking good. Okay. The net pay on the simply do a subtraction on these two
Okay, I'm going to take the gross pay minus attacks and that's going to simply be equals
Minus up through
Okay I can't get the our comparison. Yes. I don't know how many hours. The average person work.
Okay. For Baker, I could repeat that whole process right in this formulas Rossa overtime pay attacks, etc.
Phenomenal at Microsoft Excel did this for me. I'm going to highlight these for ourselves. I'm going to position my cursor.
Over this selection hand on the bottom right hand returns to across hair. I'm going to drag it on down and it will have automatically computed this form. And so if I take a look at this one, rather than B and C three and automatically adjusted that to see for
Okay, take a look down here a few more.
As far as the average. I'm just going to go equals
Average specify a range range is going to be V3 up seven
We're going to go ahead and
Find the smallest value in a pool. I got a few you could easily point. Another that's 10.5 if you start having 100 or more. They take a lot of fun this the smallest the short. In this case, the smallest raid. And that's going to be
V3 colon be seven.
And probably the Max, Max, the same range be three colon. The sudden
Okay, and I can do the same thing here for the hours I can come along and go and how breach see three policy seven
Written that Microsoft Excel to the apartment and highlight these position my cursor over the selection handle interestingly across here, drag it over.
So now when I press on that it's going to average see three colon see seven. Okay, now I know what the average employee work 36 maybe I should get this
Turned on Me go back just to a number
OK, so the average employee is worth 35 hours Adams is work 45 hours. So when I come over here to our comparison. I'd like to see that his 45 and a half hours more than the average employee.
Let's do this. I'm going to do this kind of incorrectly. First of all, I'm gonna take a C three minus c nine
OK, and now we're perfect for Adams, the average employees work 35 hours added was reporting half is worth five and a half hours more than the average and play. Watch this. If I drag this on down. So I start getting into a walker. He's more the average employees were 35 hours.
And what walkers were 41 is like, what's this. So, these numbers aren't working. So anyway, let's start here with the first one that we see three
Minus C non network. Right now, the next one I'm taking see for this looking at see 10 which is 20 minutes looking at see five
And eventually, we're, we're looking at patient numbers. Okay. I want to make sure that this stays fixed saw
Over here, so that the see nine states fixed. And to do that, I can just place $1 for dollar sign in front of on and that's going to make that an absolute reference and now when I drag on down.
I'm going to get the correct answer. So when I take a look at this one is taking see four
Minus seen on. Same thing here, see five minus 89 I notice all these are looking at seeing on. Okay, I think that's a wrap this one up. So in this video.
Oh, hadn't entered some tax worked with several formulas one on overtime is the net function gross pay tax that pay our comparison to go get these right here for you. Okay. Well, I think that wraps up this video kitchen. Excellent. Thank you.