My Shopping Cart
 
There are no items in your shopping cart.


Simplifying Payroll Budgets in Excel (IME630B)

Presented by: David Ringstrom, CPA
 
Request Replay Date:
(*) Single User Price. For multiple users please call 1-800-223-8720
Pre Recorded Audio Conference
60 minutes
  •  Mon, June 3, 2013
Event Description

Identify Best Practices And Avoid The Common Pitfalls Of Payroll Budgets In Excel

Excel is a useful program which is widely used by individuals and companies to create payroll spreadsheets. In this live webinar, our Microsoft Excel expert David Ringstrom, CPA, will share a number of techniques that you can use to simplify payroll budget spreadsheets. Attendees will get the chance to ask any type of queries to David, even if they’re not specifically related to the material in the presentation. Our expert will mostly teach from Excel 2010, however he’ll include alternate instructions and techniques when applicable to Excel 2007, 2003, and even 2013.

Every hour many users transcribe hundreds or thousands of records into Excel to develop a payroll budget. This session will teach you, how you can eliminate manual data entry by linking to data sources, improve the integrity of your payroll budget by embedding key inputs, and how to use the pivot tables to quickly summarize and review the final results.

During this one-hour webinar you will:

  • Find out how you can link to a source file that has your payroll records, and then let Excel take care of managing the associated calculations
  • See how the hidden Form command makes it easy to review one record at a time
  • Use Range Names in your spreadsheet to streamline payroll-related calculations, or to hide key values that you don't want users overwriting
  • See how Pivot Tables can quickly summarize your results by department, region, or however you need it
  • Use the COUNTIF function to allocate costs based on head-count
  • Save the final results as a comma-separated value format so that accounting or finance can simply import your budget into the payroll software
  • See how to create automatic back-up copies of your payroll budget, so that you have fall-back positions in case something goes awry

Plus, you’ll also learn how to save others from rekeying data by creating an importable file for your budget software.

Who should attend? Beginning to intermediate Excel users

Click here to read Transcript

 

Important Copyright Information

 

Dear Valued Customer:

We have received quite a number of questions regarding the permissibility of copying and distributing our print transcripts, as well as questions regarding the

conferencing of calls to other sites by our listeners. The main questions include:

May I photocopy my transcript packet and send it to others?

May I fax or email a copy of my transcript packet to one or more colleagues to use?

The quick answer to all of the above questions is “No.” If you or others you know are doing any of the above, you are infringing on the audioconferences’ copyright.

Probably you weren’t aware this is a legal infraction, but it is, and it is punishable by law through damage awards, etc.

To explore low-cost alternatives for legally having more than one copy of this transcript, please contact our Customer Relations Department at (800)223-8720. Our

representatives will be pleased to help while staying in compliance with copyright law.

Sincerely,

Audio SolutionZ

P.S. We trust that our audience of professionals such as you will respect our legal copyright “on the honor system.” But if we learn of willful violations, we may have

to seek legal remedies.

P.P.S. If you know of any unauthorized copying or distribution of attendee packets or dial-in information, please let me know about it in confidence so that we may

follow up with the party and set them on the right path. (Your name will be kept confidential.)

Comment (D): I would now like to introduce your speaker for today, David Ringstrom.

David is a CPA and owner of Accounting Advisors, Inc., an Atlanta-based spreadsheet consulting firm that he started in 1991. Throughout his career, David has spoken at conferences on Excel, and written dozens of freelance articles about spreadsheets.

David's webinars receive rave reviews and attendees particularly like his detailed materials. His handouts include numbered steps that make it easy for you to carry out the same techniques that you'll see him demonstrate live during the webinar.

David, welcome to the program. We are now ready to begin.

Comment (DR): Thank you, (Diana). Hello everyone and welcome to “Simplifying Payroll Budgets in Excel”. I'm David Ringstrom, a CPA in Atlanta. And my goal today is to share with you a number of ways to create more effective payroll budgets.

Lots of times I see folks spend a lot of time (rigging) data unnecessarily or trying to create complicated formulas or – you know, manually pick – pecking through data within a budget. And so I’ve got a series of techniques I’m going to run you through that will hopefully empower you to be more effective in Microsoft Excel.

The session is designed to be interactive. There will be a Q&A session at the end. So if you do have any questions regarding the material that I go through, if there’s anything you’d like to me to go back to again, then you will have that opportunity at the end to – to ask any questions that you have.

And also at the – the last slide of the handout, you’ll see is my email address. And I always welcome follow-up questions as well. So, I’m here to help you.

Just a quick note, on the Excel version I’ll be teaching from, I find that predominantly most folks are using Excel 2010 or 2007 these days. So I’m going to be teaching from Excel 2010. I do have Excel 2013, 2010, 2007 and 2003, all four available on this computer.

So at the end, if you would like to see something demonstrated in a particular version of Excel, I'll be happy to do so. But throughout the day, you'll see me working in Excel 2010 and most of everything I'm covering today is going to be in the same place in 2007, 2010 or 2013.

The one difference that sometimes trips some folks up is that 2007 does not have a File menu. It
has a little round button called the Office button. So if you see me click on the File menu, just note that you'll click on that little Office button instead and then the rest of the steps will be the same.

Some slides will have notes in blue that have equivalent steps in Excel 2003 in the event that
you're still using an early version of Excel. With that, we have a lot of ground to cover so I'm going to jump right in.

And what we're going to start with today is we're going to start with a CSV file. We're going to see at the top left hand corner it says “employees.csv.” Many times we are able to get an export of the employee list from an HR program and typically it's going to be in a CSV format.

So I've set up kind of a hypothetical CSV file that we're going to link into Excel and we're going
to link it in a special way so that rather than us having to manually copy and paste that file again and again, we can just save over our CSV file and we'll be able to right-click and just refresh the data.

We're then going to add a series of calculations to the right and what's going to be special about these calculations is that if we hire some new employees and they get added to that CSV file, we're going to set up the spreadsheet in such a fashion that Excel will copy the formulas down for us. So it's going to be kind of a set and forget approach with regard to our payroll budget.

Now, sometimes we want to kind of freeze the action and not have it changed and so I'll show you how you can control whether Excel automatically refreshes the spreadsheet from the CSV file or if you – if you want to set it to be a manual refresh, you can do that as well.

Another area folks often struggle is then summarizing the data and in some cases when it – once we've got the payroll data accumulated, we then need to transfer it into an accounting package. And many times accounting packages will let us import data in a CSV format.

And so, I'm going to show how we can use a pivot table to easily summarize our payroll data associated with the proper general ledger account numbers and then create a CSV file that we could import elsewhere.

So with that, we're going to go into Excel and rather than going to the File menu and opening our CSV file, instead we're going to go to the Data menu. And so we're going to see that I'm going to go to the Data tab and I'm going to choose From Text.

It's going to give me Import Text File window and so I'm going to double-click on a file called
“employees.csv” which is available as part of your handout if you want to follow along.

So we're going to double-click on that. And notice that I said double-click. Many times in Excel rather than having to click once on say a file name and then click the import button, we can just-double click on our choice instead and save a step.

So a double-click on the employees.csv file and that will launch what's called the Text Import Wizard. Excel is going to automatically figure out that our file is delimited. And what delimited means is that there is some sort of separator between each field.

The other option is Fixed Width and we can tell this is not a Fixed Width file because if it were, all the first names would line up, all the last names would line up, the department name. Everything will be lined up nice and neat in columns that are of the same width but in this case, there is a separator between each field which is the comma.

So, once the Text Import Wizard appears, we click the Next button and then we have to tell Excel that in this case, our separator is a comma. When we do that, we're going to see the data pop into columns and we can then click Finish.

At that point, we're going to see a new prompt which is going to be that little Import Data dialog box. And so what we're going to want to do when that little dialog box comes up is click the Properties button. That's going to allow us to kind of work some magic on this data and automate some aspects that a lot of folks carry out manually in Excel.

The first choice that we're going to want to do is step 8, we're going to want to unclick Prompt for file name on refresh because typically I'm going to assume that when you're exporting from your payroll software, you're going to save over the same file again and again.

Now if there is a situation where you're going to have a different files that you want to run through here then for step 8, you can leave that clicked on and say when we do refresh the data, Excel will then let you basically choose your text file and then it will bring that text file in. But typically we're saving over the same file again and again. So typically I'd like to turn that off.

The other option that we'll want to either click on or off is in step 9, where it says Refresh Data when opening the file. What that means is that when you open – if you click that check box on, when you open your Excel spreadsheet, Excel will automatically pull in a – the latest information from that CSV
file.

So, that means that if you export from your HR software, open your – open your budget spreadsheet, it will automatically unattended pull in the newest copy of your data. If you don't want that to happen, if you want to have more control of that when it gets refreshed, then you want to leave that unclicked so that you can then later right-click on the data and tell Excel when you want to refresh.

But step 10 is the option that is strictly important that we want to make sure we click that on. That's where it says Fill Down Formulas in Columns Adjacent to Data. What that's going to do for us is put our formulas in kind of an automatic mode so that if our CSV file gains more rows or loses some rows, Excel will copy those rows – the formulas up or down as needed.

So that way we don't go back and manually copy formulas down our spreadsheet. It basically makes it be an unattended update for our budget spreadsheet. So we'll click OK a couple of times and then we'll have our data in our spreadsheet.

So let's go to Excel and then walk through those actual steps. So we're going to get – to go to the Data menu. And then we're going to choose From Text. And so we'll go to our example folder and I have a file called “employees.csv.” So I will double click on that. That launches my Text Import Wizard. So the only thing I need to do on this first screen is click Next.

On the second screen, in this case, I can see there's a comma between each field. And that's why I would click comma and now the data lines up in columns. So these delimiters allow you to specify what the separator is between the fields and typically it's going to be a comma, sometimes it's tab.

You're going to see that Excel automatically chooses the tab option and you can either leave that
turned on or off. It doesn't really have any bearing because the CSV file does not have any tabs within it.

We could click Next and then on this last screen, we can make some changes to the data if we
need to but for our purposes today, we're just going to click Finish and – and just assume that the information is all set the way that we need it. So we'll click Finish.

And so this is where we get the option to – when specified, we want to put the data. And typically you're going to want to put this up in cell A1 of a blank worksheet. You could also choose new worksheet but in this case I already started on the blank worksheet where I want to put the data.

So I'm going to click the Properties button and I will turn off prompt for file name on refresh
because if I'm refreshing the data, that means I'm not going – I'm just going to use the same file. So I can turn that off there.

I can turn on Refresh Data when opening the file but again once I'm down, you know, once I
finalize the budget then we'll want to turn that option off and I'll show you how we can get back into this dialog box to make these changes later.

Most important setting here is the one that says “Fill Down Formulas in Columns Adjacent to
Data.” That's the one that automatically manages you – your formulas for you.

So we're going to click OK twice and it just brought in our data. So if I wanted to manually refresh this list, I can right-click on the data and I can go down and choose Refresh and that would bring in a fresh copy of that CSV file.

So that's how you can maintain control if you don't want Excel automatically bringing in a new copy of the file if you turned off the automatic refresh option then you can manually refresh the data on demand.

To get back into that Data Range of Properties dialog box where we're just in, we could go up to Data Range Properties. That puts us back in this dialog box from which we can make the changes if we – if we decide we do want the prompt for file name or we do want to refresh automatically. So we can always get back into these settings here.

If for some reason you wanted to swap out and bring in a different file, so we could choose Edit Text Import. That would give us a new Text Import Wizard and would let us pick a different file and walk through the wizard and then bring in that file. So none of these is in concrete, you can always go back and rearrange the data at any point if – should you choose to do so.

Now, regardless of whether you choose to link your data in this fashion or not, one key thing that you want to do when you're building a budget spreadsheet like this is make sure that you keep all your titles in a single row.

At the top left, we can see that lots of times, folks like to pick the headings and expand across multiple rows and that can cause lots of problem and actually make Excel harder to use because Excel really works best when you're heading for your data, pa – you've got the titles in a single row in a single cell.

So what you want to do is type the entire heading whatever that heading is. Put it in a single cell and then use the Wrap Text feature and wrap the text, otherwise as we see if we have multiple line headings, if we do something simple like try to sort then our headings can get sorted with the data and can cause lots of confusion and frustration.

Another technique that we're going to be using today is concatenation. And just to give you a
quick sense of what concatenation is, it is a way of joining pieces of text together. So you're going to see when I go through and implement a couple of these formulas at different points, I'm going to be joining text together.

And so before we get into the formulas, I just wanted to touch on that. Excel does have a worksheet function called =CONCATENATE. And you can use that to concatenate data but that is a worksheet function I never use because I can always use the ampersand instead.

So if you have data that is in multiple cells that you want to join together, then we create the formula much like we would if we were adding numbers together but instead of plus signs, we use the ampersand.


So in this case, I wanted to join the first name and last name together. I can say =A2 and then an
“&” and then I want to have a space between these two words and so I put that inside two double quotes.
And then I do another “&” and then B2 and that joins the first name and last name together. Conversely, I could have =B2 and then put a comma and a space and a double quote and then A2 and that would allow me to create last name, first name.

So you'll see me use concatenation later on in a formula but I did want to take a moment and cover the technique so that when you see the ampersand within a couple of the formulas that you'll understand what that means.

Now, another thing that we want to do in any of our spreadsheets is we want to minimize hard coding values inside formulas. So for instance if we're trying to calculate Social Security tax, what we really don't – a really bad practice is to create a formula that is like =A2*0.62.

As anyone that deals with payroll is well aware, payroll tax rates are always shifting as Congress gets more and more creative about trying to figure out ways to raise revenue.

So we really want to make our spreadsheets easy to maintain. And so one of the things I'm going
to do in a moment is to create a little input table where we're going to put our settings but also I want to show you there is a different way that you can store these settings and make it harder for the average user to change them.

So to do so, what we would do is go on the Formulas tab and then we're going to choose Define Name. When we click on Define Name, we can create a name for our input such as Social Security tax and then if you're already familiar with range names, you'll know that usually this refers to – refers to a worksheet cell because in a moment we're going to create some names that will rename worksheet cells to make it easier to create our formulas.

But you can also use range names to store your inputs. So if you wanted to change that amount, you would go to the define name – actually we go to the Name Manager that – this big Name Manager button here and double-click on your name and you'll be able to change that value and then click OK.

What's great about this, two things, one you can – instead of referring to a worksheet cell or
referring to an amount, you're referring to it by name whatever the item is. But also in this case if we tuck it away inside the Define Name dialog box where we're creating a new name, that means most users aren't aware that you can do that and so they won't inadvertently type over your forms – type over your values and potentially compromise your spreadsheet.

So we wanted to create a setting like that if we go back to Excel, we can go to the Formulas tab and then we can choose Define Name. Now if you're using Excel 2003, this is on the Insert menu under
Insert Name and then Define. And so we see that it wanted to create a name called “Bolden” because my cell – my cursor happens to be on that cell.

So we'll change this. We'll call this FICA and then we'll put 0.0 – we'll raise the cell reference – 0.0765 and then we'll click OK. That means anywhere in my spreadsheet I can now type = FICA and it will return that value.

But it's not in a worksheet cell so I kind of tucked it away so most users can't find it and type over it inadvertently. So if I need to change that value I can go into the Name Manager and I can click on – I can double-click on FICA or in fact, I click once on it. We can see that refers to and so if for some reason it changed then I could make it be, you know, raise it by 1%. Click that old check box to lock in the value and then click Close.

So this gives me a great way to basically hide my inputs that I'm going to use my formulas and make it much harder for users to get to them. So that's the one approach we can take but I'm going to delete that because we're going to set up a different approach in just a moment.

What we're going to do on our spreadsheet is we're going to create a settings table, an actually settings worksheet. I always like to separate my assumptions such as these tax rates from the actual data. We tried getting too much going on, on the same spreadsheet then we might later inadvertently delete some of our assumptions or type over them so it's always best to kind of spread out a little bit and have different sheets for different purposes.

So we're going to create a worksheet called Rates and we'll put in a couple of different amounts
here for calculating these payroll taxes.

So we'll have our first worksheet. We'll call that Budget. So I've double-clicked on the worksheet
tab and I rename that and call it Budget. I'm going to add a second worksheet that we'll – I'll call Rates. These names can be anything like I just happen to call it Budget and I happen to call it Rates but there's nothing special about using these particular names. So we'll have our FICA rate for calculating the employer's half of FICA.

So we'll put in our percent. And one way to save a little bit of time when we're setting up assumptions like this is for 7.65, go ahead and type in the percent sign. We press Enter. It goes ahead and formats that cell as a percentage.

If we are to put 0.765 and then go to Home tab and click on the percent sign, we then lose our decimal places and we have to go – put them back. So it saves you – it saves you a little bit time and hassle if you just go in and type the percent sign after your percentages.

Next, we're going to have a Federal Unemployment Rate. And I'm going to take a shortcut here because I'm going to assume that every employee is making more than $7000. And in that case, the federal unemployment rate is 8/100 of a percent so we press Enter and in effect, I'm going to be paying $56 per employee per year for federal unemployment.

Now, we could get a lot more elaborate on the calculation but again, we have a lot of ground to cover today. So I'm just going to take – make some generalized assumptions here and make it easier to craft some of these formulas.

For our state unemployment, in the case of Georgia, I think back pay unemployment on the first $8500 and so let's say that my unemployment rate was 1%. Then I'd put 8500*0.1 and that would give me a value of $85. So we can see that 0.0 there as well.

And then probably the most important cell in a – in a payroll budget spreadsheet is what pay increase is everyone going to get. Now, there's lots of ways to do payroll budgets and lots of times payroll increases are applied based on merit and allocations and lots of different methods. But to keep things simple, let's just assume everyone is getting an across the board 3% increase.

I will show you some formulas in a bit that you can use to allocate things and apply things but in the course of an hour, I have a lot of ground to cover so I'm going to kind of simplify some of the calculations so that we can kind of cover the overall arch of how we're going to pull all of these together.

So in this case, we have all our – the primary inputs we need to put in place. But what I don't want to have to do is I don't want to have to remember that my FICA rate is in cell B1 of the Rates tab or that my unemployment is on cell B2 of the Rates tab and so on.

So that's where I can assign names to these cells. And to do so if we could go back and look at
our PowerPoint slide, I can click on the cell that I want and then I can assign in the name. That then allows me to refer to that worksheet cell by its name rather than its address.

That's then going to make our formulas easier to work with. So we'll click in here and we'll call this FICA. And then we would have a fed unemployment, state unemployment and then we'll have a pay increase.

Now, when we're naming these cells we can't have spaces in the names. So do make sure you
either join the words together or else put an underscore in between but you can't – if you try to leave a space in there, then Excel is going to complain that you've put an invalid name.

So we click back up – click back up in here. We'll call this pay increase and that gives us our –
the names for our inputs.

Now, we do have to be careful with our range names because if we do go back and delete them, if we – if we delete a range name from our spreadsheet, Excel does not go back and convert it back to a cell reference.

So you can end up with a pound name error in your spreadsheet. So if you have created a range name, if I go back to Excel and let's say I go in here and I'm going on to the Name Manager tab, I mean, the Name Manager which is under Formulas and I decide to click on the FICA and I click Delete to delete it, if I've used the word FICA in any of my formulas, I will then end up with a #NAME error and so I have to go back in and manually replace those cell – the word FICA with the actual cell reference.

Typically you won't want to – you won't want to delete the range names because they make it
easier to write the formulas but I just do want to point out that if for some reason you on purpose or accidentally delete one of your range names, one way to – a couple of ways to track it down, one – any cell that reference that range name will have #NAME with a question mark.

Also, if you go in and look at the formula, the affected range name is going to be in black. If it's a
valid range name, it's going to have a color to it, some kind of color coding names that Excel can't decipher, going to have it – going to be displayed in black.

Now, another technique that I often use is I like to be able to see both tabs in my worksheet at the same time because in this case, I've got rates on one worksheet and I've got my budget on the second worksheet. And so an – a way that I could make it easier to manage my workbook is I can go to the View tab and I can click on New Window.

Once I've clicked on New Window, I then clicked Arrange All, choose Horizontal. And in this case, I want to click Windows of Active Workbook. In effect I'm going to be able to see my Rates spreadsheet and my Budget spreadsheet at the same time.

So in Excel to do that, we're going to go to the View Tab. We're going to click on New Window. At first it will seem like nothing really happened. So we'll then click Arrange All, we'll choose Horizontal and then Windows of Active Workbook. And then we click OK.

Presently I see my Rates tab at the top and at the bottom. But I can now switch to the Budget tab. And so when I'm building up my formulas, I'll be able to just point to the cell reference down at the bottom if I choose to.

So, many times with budgets we have multiple tabs and we can get tangled up trying to work side to side between the tabs. And so being able to arrange the worksheet tab – worksheets side by side can really save us a lot of time.

Now, we're going to have some lists that are going to help automate our spreadsheet. And we're
going to use a feature that's called the Table feature. So some of the lists that we're going to have is we're going to have a table for our workers' comp codes.

We're going to have a table for health insurance rates. We're going to have a table of information about departments. And we're also going to have a little chart of accounts that has our account names. And so we're going to be looking up data from this different list and the Table feature is going to make it easier for us to get that done.

So let me take a shortcut and get those lists into my spreadsheet real quick. So we're going to zoom back out here.

And so just copy this list here – so in this case the first list I have, we'll go ahead and zoom in on it so you can see what I'm talking about, is we're going to have workers' comp codes.

So if we go back and look at our Budget, in fact we should probably see both tabs at the same time, we're going to see from our HR Software, we get – each employee has a workers' comp code. And so those codes didn't get associated with rates. So one of the things we're going to have to do is calculate the workers' comp cost for each employee.

Now, we're going to use a VLOOKUP formula to do this. But to make our life easier, we're going to do an extra step that you may not have done before and we're going to make this list into a table. And it's very easy to do. What we're going to do is just going to click on any cell here, go to Insert tab and we're going to choose Table.

Excel automatically highlights our list where it is and we want to tell it that our data has headers.
That just means we have a single row of titles at the top. We click OK.

And what’s interesting about this is it gives it – when we do so, we gain a Design tab. And it gives the table a name of Table1 which we can click in here and change the WC much like we would – so WC short for workers' comp, just like when we – if we're naming a range, we can give our list here a friendlier name.

A benefit to – that just should be a 5000 there – a benefit to the table is let's say we add a new rate to the table. So let's say we have a new code of 6000. When I press Enter, the table expands automatically. So I don't have to go back in and resizing my formulas. I'm going to be able to just refer to the table by name and if I increase the size of the table, Excel would take care of that for me.

So I'm going to set up a couple more tables, we're going to have a Health Insurance table. So we click on that list, we go to Insert, we choose Table, we press Enter, one – because Excel automatically identifies our coordinates. So we're going to call that table Health Insurance.

And then we're going to have our Overhead table. So we maybe we'll call that our Department though because it has department names and Accounting prefix and then also overhead amounts that we're going to allocate.

So I'll name that as a table, call that Departments. Any of these names we can change down the road if we need to. And then over here because our end goal is to be able to import a budget into an accounting package, this has our account names and account numbers. And so we're going to make that into a table as well. So we'll call that accounts – or a chart of accounts, then.

Okay. I'm not sure what it's not happy about here. Let's see.

Oh, I know, I was jumping ahead, I was trying to name it. That's what probably – when I click on
Table, I was trying to give it a name here and that's why it kept telling me it wouldn't work.

What I should have done is left that field alone because Excel automatically knows where the data is. We click OK and then we go up to the table name and then we can call it Accounts.

Many times when we do the same task go over and over again in Excel it can kind of blur. But the procedure I was doing was going to each list, going to the Insert tab, choosing Table. Once Excel saw it
as a table, I then on the Design tab gave it a different name. And so we're going to see if these names are going to make our results and formulas easier to create.

Now, one of the formulas that we're going to use a lot today is the VLOOKUP Function. And you may already be familiar with VLOOKUP but in case you're not, it has four inputs. And so typically the way that folks use VLOOKUP is they'll say =VLOOKUP and we'll specify a LOOKUP Value.

So in this case, I've got my employee name which is (Fred) and so I want to look at a separate worksheet and find that employee.

So the separate sheet I'm looking at is called Pay List and I'm looking at columns A through C. Now, what I did here is I left up the row numbers. So that allows me to look down the entire column. And so when VLOOKUP finds a match on (Fred) in Column A which is the first column, it's going to then return data from the next column – or from the column that I specify which in this case, is the second column.

So these two signifies that when there's a match down on the first column, so it's taking – looking
for (Fred). It's going to look down Column A. When it finds (Fred), it's going to go to the right and find the value in the second column and return that as the result.

This last argument where it says zero, if you're going to be working with VLOOKUP, you simply – if you get the word False there and a shortcut that you can do is put a zero instead. What that zero signifies is that you want an exact match.

And for payroll in particular, exact match is so critical. We don't want to – we don't want to mix and match salaries or benefit codes or anything like that. So whenever we're doing a LOOKUP, always best to make sure we have a zero there when we're looking at something from the list.

Another instance of how you might use VLOOKUP is you could have a VLOOKUP inside a VLOOKUP. So in this case, I've got a Health Insurance table. And on my first list, I'd have the employee information and the benefit code but I then I have another list over on this – another worksheet. So it actually converted the codes to dollar amounts.

So what I was able to do in this case is I needed to find out what (Fred's) benefit code is. So the first lookup – VLOOKUP is doing that. It's looking for the word (Fred), it's looking at – on the Pay List tab, columns A through C. This time it's returning data from the third column. So it's going to return the letter A.

Now, the letter A right here wouldn’t do much for me because I don't really care what code he has. I'm doing a budget and I want to know what amount to budget for his premium.

So that's where we used one VLOOKUP inside a second VLOOKUP. So this additional VLOOKUP is taking the letter A that got returned and comparing it to columns A and B of the Benefits tab. And when it finds a match it returns a value from the second column and then the zero indicates that we want an exact match.

So if you have data that's spread across multiple lists, then you can use VLOOKUP to string that
together.

Now, we're going to see that in our formulas today, rather than using sheet names and column references, we're going to use our table names instead which are going to make these formulas even easier to understand.

But before we build up the rest of our spreadsheet, one other function I want to share with you is the COUNTIF function. Many times we need to allocate expenses based on head count or various criteria.
And the COUNTIF function in Excel lets us count how many times something shows up.

Now on this slide, we see a list of products and so we can see that in cell J2, I have a formula that is looking down column E and every time it finds word “bananas”, it's counting it.

So COUNTIF has based with two inputs. We tell it where to look and then what to look for. And that's going to give us a way to allocate expenses and we're going to see that we have a formula that will perform that allocation that we'll get to in just a moment.

So now we kind of have our infrastructure set up and we've looked at some of the formulas we're going to use. Let's go ahead and start building out our Budget spreadsheet.

So in Column F we have salary that came – the current salary that came from the HR software.
But for budgeting purposes we need to budget a pay increase, hopefully.

And so the formula we can use is we can say take whatever is in cell F2 and multiply it by one
plus pay increase. In this case, Pay Increase is the name that I assigned to cell B2 of the Rates tab. So this allows me just refer to that cell by its name.

Now, the other thing I've done here is I've wrapped this formula with the ROUND function. So
it's going to it round it off to two decimal places.

Excel by default calculates numbers up to 15 decimal places and depending on the amount of numbers that you're working with, you can sometimes end up with some rounding errors in your totals. And so by using the ROUND function, you instruct Excel to basically round everything to two decimal places and that deal – that takes care of any rounding.

So if we go back to our spreadsheet, on our Budget tab, we're going to create a cell that is called
Budgeted Salary. So, we'll (skew over) just a little bit.

So in this case, as I said at the top, rather than putting this heading on two rows and it's happened in a single cell. And then I'm going to choose Wrap Text. So that's going to wrap the text so that way my headings are just in a single worksheet cell as it was expanding across multiple.

So let's say, F2 times – and one of the benefits to using range names in Excel 2007 and later is that it finishes their typing the range name, a list drops down. And any of your range names are going to have little tag. It looks like a luggage tag. So there is my pay increase. And I can double-click that.

Now, in this case, I forgot I want to say one plus the pay increase. So when I press Enter, that
creates my formula. And then, the next step is to make it round off to two decimal places. So in this case, it's going to take this calculation and then round it off to two decimal places.

Now, you'll notice that when we linked in our text file, we said copy (build on) formulas in columns adjacent to data.

And there's actually two ways on how I can get the formulas down to the rest of column. There's
actually three. I could manually drag it down but that's tedious and I never do that. One trick I like to do is double-click on the Fill handle here which would copy it down.

But another trick I can do, since I've linked to a text file is I can right-click and I can choose Refresh. That's assuming I'm okay bringing in that CSV file again. But if I choose Refresh, we see that also copied my formulas all the way down. So that's part of that automatic formula management that we get by linking to a text file.

Next thing we need to calculate is our FICA rate, that's our Social Security tax. So we're going to take our Budgeted Salary and multiply by the FICA rate and then round that off to two decimal places.

So let's say =FICA or we can actually say G2*FICA. And so what I like about using range names instead of having to say =G2 times and then come over here and click on this tab and then, click on that cell where it would say, in this case it was named but in fact click on rates and B1. Then I have to remember to put in the dollar signs. And it just ends up being – getting more and more complicated.

So by using range names for my formulas, I can just quickly type up these formulas and know
that I'm referring to the proper cell. So we can click – copy that down.

For federal unemployment, this formula is particularly easy. I can just say =Fed Unemployment because each employee is paying the same amount and copy that down. State unemployment is the same way.

So as I start typing, notice as soon as I type = and type an S, I get a list of potential worksheet
functions. As I keep typing, the list gets smaller. As soon as I see the list I want, I can either double-click on it or keep typing until it's the only choice and then press the Tab key.

So we had our formulas where we got our FICA rate. We created a formula to get our unemployment. And this case since we named it, we're able to just say =Fed Unemployment.

And this gives us the benefit of making our spreadsheet easier to audit because we're just referring to that cell by name instead of having to go and look and see, “Okay, wait. What's cell B2 of the
Rates tab?” We all now actually know at a glance that's our federal unemployment amount. Same thing for our state unemployment amount.

We're now going to look up our workers' comp rate. And that's where we're going to use a VLOOKUP. And typically, workers' comp is based on a factor of an amount per thousand of someone's wages.

So the formula we're going to do is we're first going to do a VLOOKUP to get the corresponding
rate for that employee. Then we'll multiply it by their salary divided by 1000 and then we'll round that off.

So for our VLOOKUP, we're going to say =VLOOKUP basing our lookup off of their unemployment code which is in cell D2 in this case. Where we want to look is our Workers' Comp table. Remember we created that table. We named it WC. In our list we can identify tables because they have a little grid. This one looks like a little calendar.

So it's going to look at the WC table. We want to return data from the second column. And then we want an exact match so we're choosing zero. So that tells me for that employee that their workers' comp rate is $0.75 per $1000 of payroll.

So I can then take that times their budgeted salary, divided by 1000 and that's going to give me
their workers' comp rate. And then, I'll round that off to two decimal places. And I can then copy that down.

So there's our formula. We first use a VLOOKUP to look up the rate from our workers' comp
table and then we multiply that against the salary.

Health Insurance is going to be a VLOOKUP and if these are monthly amounts, we're going to multiple it by 12. And so we can see it's equal – we look at our health code, compare that to our Health table that we created. We want data from the second column and a zero for an exact match.

So =VLOOKUP. Going over getting our health code. In this case, I call that Health Insurance. I want data from the second column. Zero for an exact match. And if that's a monthly amount, I multiply it by 12. That budgets their health insurance for the year.

One more complicated formula and then we'll have the easier stuff the rest of the session. And
this is allocating our overhead.

So again, we're looking VLOOKUP. And we can see we're basically using the same functions again and again. We're just using VLOOKUP. This time we're looking at our Overhead table. We want data from the third column. There is an error on this slide. There should be a comma zero in there because we do want an exact match.

But we're going to take that overhead for that department and we're going to allocate it by the number of employees. So that's where our COUNTIF is going to come in because COUNTIF is going to look at column C and count how many times Operations shows up. And allocate these overhead dollars evenly across each employee. And then we're going to round it off to (zero) decimal places.

So that you can follow along, I think I called that table Departments. So I'm going to change that and call that Overhead. So we see that if you want to change the name – name of your table, just go back to your table. Go to your Design tab and you can assign in a different name.

So we say =VLOOKUP. This time we are looking for a department name which is Operations. Looking at our Overhead table, we want data from the third column and we want a zero for an exact match.

So 30,000 is the amount for that entire department. But we don't want to chart the whole department to a single employee. We want to divide it across the employees. So we can say COUNTIF.
We're going to say, “Look at Column C.”

Notice I'm leaving out the row numbers. That feature proves my formula so that if I add more employees, I don't have to change the formula and then looking for that department name. So there are apparently 12 employees so it took that 30,000 and divide it by 12. We can then round that off. And so we quickly use a series of VLOOKUPs to put together our payroll budget.

Now, when we're working with list of data, somehow it's helpful to be able to review the data one record at a time because all these rows kind of blur together. So there's a hidden command. It's on the Data menu in Excel 2003 but buried in Excel in 2007 and later. And it's called the Form command.

And we can make it – we can add that to our Quick Access toolbar. Actually here it says
“Customize a ribbon.” But really we should have chosen add to the Quick Access toolbar. But if you go
to your Quick Access toolbar at the top of the screen and then choose More Commands, there is my favorite section here. It's called “Commands not in the ribbon”.

And if we type the letter F, it's going to take us down to the F. And we can double-click on Form command, where it just says Form and we click OK. That gives me a Form button here. So when I click on the Form command, we're going to see that I can walk through my payroll budget one employee at a time.

It gives me a nice handy list that I can scroll up and down. So I can either scroll the records by
using the scroll bar or I can click back and forth.

So the Form command is kind of buried in Excel 2007 and later. But it allows you – gives you an easy way to kind of go through and review one employee in a vertical fashion which is a lot easier than how the data kind of gets all blurred together in rows.

So you can either add it to your ribbon in Excel 2010 or later or you can add it to your Quick
Access toolbar. And so it allows you to kind of work your way through the list.

Now, once we've got the budget put together, the next thing we want to do is summarize it. And
the easiest way to do so is with a pivot table.

And so to create our pivot table, we're going to go to the Insert tab and we are going to – I circled
the wrong button here. I should have said circle Pivot – I should have circled pivot table.

So rather than clicking on the Table command, we're going to click on the Pivot table command.
That's going to display or create Pivot table dialog box. And we're going to click OK.

What we're then going to do is pick and choose the fields that we want to summarize. And so
what we're going to see is that the pivot table is going to allow us summarize this data that's in our big list where it's kind of hard to get a sense of it and give us a nice tidy little summary.

So if we go to the Insert tab and then we choose Pivot Table, we can then click OK. And I can then pick the fields that I want. So I want Department and then I want from Budgeted Salary the rest of these fields. So each time I make a choice, it's giving me a – it's totaling up my payroll by department.

So I have my totals going across here. But my goal is to be able to import this into my payroll software. So I'm going to take my Values button and I'm going to drag it down over here. So it's going to re-orient the data, so that it's now listed. But for accountants, chemists, machinists, so each one of the departments is listed going down instead of going across.

So once I've created the pivot table, two little tweaks I'm going to do is I'm going to click on the Design tab. Choose Report Layout. And I'm going to say show in tabular form and then repeat all item labels. So it – that's going to do two things. It's going to make two changes to my pivot table.

So on the Design tab, under Report Layout, we're going to say, “Show in tabular form.” And so what that does is that it gives me a column for the – for the department and a column for the values because by default in Excel 2007 and later, they're displayed in compact form where those two fields are combined in the column A.

So by clicking on a pivot table, going to Report Layout, we can say, “Show in tabular form.” The
next thing I want to do is fill in these gaps here. So I can go to Report Layout and say, “Repeat all item labels.” So that gives me my headings here.

So what I've done now, what I – where I've gotten to is I have a summary of – for the accounting
department, the salaries, payroll taxes and so on, for the chemists, for the machinists and so on.

So what I need to do is I need to link that with the account numbers because I want to – my goal is to import this into a payroll software. And so in this case, I'm going to use two VLOOKUP formulas.
The first VLOOKUP formula is going to look up the account number from my Accounts table.

The challenge I have here is that in column B, Excel added this prefix “sum of” and then Budgeted Salary. So what the mid function is going to do for me is it's going to rid of the words “sum of”
so that in my VLOOKUP I can just look for Budgeted Salary, FICA, Federal Unemployment and so on.

I'll then look at the Accounts tab and get data from the second column. That's going to get me my main account number. I then need to add on the prefix for each department.

And so that's where I use concatenation. We have an ampersand. We put a dash. We put that in quotes. We do another ampersand. And then I can string together the specific identifier for that department.

So over here, we'll get our first VLOOKUP. So if we say =VLOOKUP. We make this screen a
little bit bigger for you. The value I'm looking for is in column B but it has the words “sum of” in front of it.

So basically, I want to start in the middle of that to grab the words I want. So that's why I'm going
to use the MID function. It shows that I'm looking whatever it is in cell B4. I'm going to start at the 8th character. And then, only – I use 50 because this is just a big enough number.

So basically, I want the 50 characters starting from position eight. So the B is the 8th character in
cell B2 and now and then I'll get the next 50 characters.

So that's what I'm looking for. Looking for this from the Overhead table. Actually from the
Accounts table. And I want data from the second column and then a zero for an exact match.

So if I copy that down, looks like I mislabeled a couple of my account names here. So let's go back to my Rates table. Change that to WC and then Health.

So when you're using VLOOKUP, if you have a pound (in 8) because it didn't match, so I went back and changed my table so that it use the same wording that I used in my Budget spreadsheet. So that gives me the account number.

But what I also need, if we go back to our Rates tab, for each department there is a code that
identifies that particular department. So now, I need to look that figure up and use it. So we're going to add our dash.

Then a second VLOOKUP, we're going to tell it that we are looking for this department. I think I lost my formula here. Looking to that department. Again our Overhead table. We want data from the second column and then a zero for an exact match. So that added my prefix on there.

So at this point, I now have a listing of the – of the numbers by department and also by account number. So on my payroll taxes, you're going to account $6300 which is for payroll taxes. Workers' comp, will to $6400, health insurance has got a $6500. My Overhead charges will get applied to Account 7000.

And so I can tell I had glance that Accounting is – Department 100, Chemists Department 400 and so on.

Now, in a moment we're going to make that into a CSV file. But before we do so, couple of ways that you can back up your data is to – because when you're working on a payroll budget, it's often easier you're usually under the gun and working pretty intensely on it, when you go to save your workbook, in the Save as dialog box, there is a Tools button. And that Tools button is going to vary depending where you put your – what version of Excel are you using.

If you click the Tools button, you can choose General Options and then click Always Create A Backup. So this is an option you have to choose for each workbook individually. But when you click that on, each time you save your file, Excel will create a backup copy of your payroll budget.

So that can kind of give you a fallback position in case something goes awry with your budget. So each time you save your file, that backup copy gets overwritten but it does give you a fallback position.

So the XLK files get put in the same folder as our original file. And we just – if want to open the
XLK file, we just double-click on it.

Another way that we can get a fallback position for our data is to either include version numbers
in our file names which I'm fond of doing. So if I'm working on a project for any length of time, I'll periodically save my spreadsheet and give it a new budget – a new version number.........
Order Now To Read Full Version Along With Conference Material
About Our Speaker(s)

David Ringstrom | Microsoft Excel Courses  SpeakerDavid Ringstrom CPA
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. David’s mantra is “Either you work Excel, or it works you,” so he focuses on what he sees users don’t, but shou... More info

 
We Also Recommend
Recommend this event to a colleague
  • Your Name:
  • Your E-mail:
  • Colleague's Name:
  • Colleague's E-mail:
  •  
    Event Title: Simplifying Payroll Budgets in Excel
    Presenter(s): David Ringstrom, CPA

 
 
 
Our Accreditation Partners
DMCA.com Protection Status