From the course: Excel: Creating Business Budgets

Calculating staff costs - Microsoft Excel Tutorial

From the course: Excel: Creating Business Budgets

Start my 1-month free trial

Calculating staff costs

“

- [Instructor] Estimating staff budgets can be a little bit tricky, but luckily using Excel, there are some neat tricks that we can use to make this simple. If you'd like to follow along with me, have my business budget staff open and navigate to the staff plan worksheet. Once open, you'll notice that we have pre-populated a table with six staff members, and what we're going to do now is estimate an annual salary for each of these staff members. Let's assume that Joe will be paid $18,000 per year, Emily $12,000, Charles $14,000, Jason, only being part time, might be $5,000, Kylie 8,000, and lastly, Agnes $16,500. What we want to do at this point is convert from an annual salary to a monthly budget, and this is easy with Excel. We're simply going to go here into cell D5, type equals, select cell C5 and divide it by 12 because we're converting between an annual salary and a monthly salary. There are 12 months in a year which is why we divide by 12. What we want to do at this point is change this part of the formula from being a relative reference to a partly absolute reference, and what we mean by that is as we copy and past this formula, we want part of the formula to change. The only part of the formula that we want to change is the row. We want the column to stay the same, and you'll see in a moment why. If I select F4 on the keyboard and I'm going to type it three times, a dollar symbol appears in front of the C and not in front of the five. Have a look at what happens if we hit enter on the keyboard and drag this cell across to the right just by one cell, go back and select the formula. You'll notice that this part of the formula hasn't actually changed. We have a dollar symbol in front of the C and the five has stayed the same. If we now go ahead and drag this down, select the cell, the number has increased to six, and if we go ahead and drag it across to the right now and select that cell once more, the column has stayed the same but the row has increased, and that's exactly what we want to see. If we go back and select D5 now, copy that across the entire table, and then paste, which is control V, if we click on any one of these particular cells at random, you will notice that it is doing exactly what we want. It is taking an annual salary, in this case for Charles, and dividing it by 12, and finally, we can easily create a total for each month for our staff bill. In cell D13 we type equals, sum, open brackets, then we want to select all of the staff members above and just hit enter on the keyboard. What we can now do is go back to cell D13 and drag that cell all the way across to the right, and what that does is it automatically copies the formula for every single month. Have a look at what happens if we select cell O13 and hit F2 on the keyboard. You'll see that the formula is adding all of the cells above. That is every single staff member's monthly budget. And there you have it. We've prepared a simple summary table of the business's staff budgets per month.

Contents