Lessons

Lesson Info

Segment 9 - Excel - Budgeting Your Project

So I first realized there's something a little bit different about me when I realized that I love excel that's not something that a lot of people can say but I actually find it really fun to crunch numbers and work with dea and so I have here a sample of the kind of spread sheet that we're going to be making this is what the finished product looks like and so for those of you who are a svp do you have a copy of this trade show budget in your files but we're going to build this completely from nothing and so you can see here the spreadsheet itself with the data we're gonna work with conditional formatting and we're going to make charts out of it as well including doing several different kinds of calculations so I'm going to show you a lot of different ends and outs of really making a functional spreadsheet and again I want to remind you that it doesn't matter if it's about a trade show and it doesn't matter what expenses are here these can be any events across the top and they could be ...

your specific event expenses for your particular event everything else completely will flow with those changes so let's go ahead and get started so I'm going to start by making a new document so I can either go up to that same button that we saw in word the new button or aiken dio file new workbook or aiken do command and all of those will work and it opens up a blank window. Now first let's go over a little bit of the anatomy of the window that we're looking at again just like one note and just like word, we have our menus across the top, so we have everything that you can insert everything that you conform at all of your data tools, and we have another excel segment coming up in a future session where we'll go into mme or that these advanced tools s and the advanced data here is that familiar standard toolbar, and again, it looks exactly the same as it did in word up to about the halfway mark, at which point it has excel, tal's we have the ribbons, home layout and then some new ones for excel tables, charts, formulas, so I'm gonna go back to the home ribbon, and then we have the worksheet itself. And so when you're working in itself, for those of you who have never worked in excel before, all of these little squares are called cells, and they all have cell names or sell addresses. The columns are marked with a b, c d, and then when you get to see it starts over with a a, b, a c, and then when you get to daisy, it goes b a b b, b c and that goes on literally for thousands of cells and in the same way it looks like there's only thirty six rows here, but soon as I start going down and down and down, it never actually stops. And so you can get a lot of data on one spreadsheet there are even. And if that wasn't enough down at the bottom, we hear word says she won. I can add multiple sheets to this as well, and I can have different sets of data all in one workbook. So you have an enormous amount of space to organize an enormous amount of information. We're going to keep it pretty consolidated. We're just gonna work with what we see on the screen right here. I'm going to zoom in a little bit that I'm going to use appear where it says one hundred percent I'm going to zoom that into one hundred and fifty I might seem out again a little later, but at least this way it will be easier for you to see, and I'll maximize that to fill the screen. So we're just going to go ahead and get her hands dirty and start entering in data, so I do want you to kind of follow along with how I'm approaching this let's go in and type in trade show budget in cell, anyone and once you have that typed in, you'll hit return and it will go down and I want you to go down one more row after that, and so now we're going to make the headings on our columns, so I'm going to type in expense and then I'm going to hit the tab key to move over to column b and the first trade show that I'm going to put in is one that I do call the body mind and spirit expelled, and then I'll hit the tab key, and then I'm going to put in by creative life craft show that we saw in outlook and then there's an astrology conference called the northwest astrology conference all put in nor work, and then these are the international society of astrology research, and so those air different places where I go to sell my jury. Now I'm going to go down to a four and now we're going to start listing all of the different expenses that come along with doing a trade show, so there's the booth fee and I have to pay for electricity much of the time I might have to pay for wifi access, so I'll put down internet sometimes I run an ad in the program and then there's my travel expenses, some shows I do right near my home, but some aren't so I've got travel, hotel food then I need to account approximately for the cost of the beads that I've sold, and if I have to hire any staff to help me under the staff category right there. So now these, of course, will be different for your particular event, but you get an idea of the breadth of the things that you want to think about, and when you're deciding what expenses to include, make sure you include both your variable costs and your fixed costs. You know, some things are always the same. Some things change, so make sure you have a category for every expense that you might incur. Now, don't worry if something's air cut off, we are going to address that in a few minutes, so now I need tio put in my prices. So for body, mind, spirit, let's, say it's five hundred fifty dollars, four, I'm going to hit the return key or the enter key to go down to the next level, and I have to pay seventy five dollars for electricity and internet is twelve dollars a day for three days, so I'm just going to put in thirty six I could also if I knew that there was a certain calculation, I'm getting a little ahead of myself, but I could say that it's three days, times and times is an asterisk, the shift eight so I have to pay three days at twelve dollars, and that gives me thirty six dollars, and then I don't even have to do the math in my head. My program at his hundred twenty five dollars for the body, mind spirit expo, I don't have to travel, so I'm going to skip travel and hotel, but it's going to cost me in fifty to fifty dollars to feed myself it's going to cost me around four hundred fifty dollars in beads, and I'm gonna have to pay about one hundred dollars for people who helped me set up my booth and break it down at the end. So I have a number of expenses right there now. One of the features that some people don't know that excel has is something called an auto fill handle. I took a few minutes and I put in all of these numbers, and they're mostly the same for each of the shows that I dio they're not exactly the same, but instead of having to tape type type type tape, you have a tall call the auto fill handle, and so what I'm going to dio is highlight those expenses so from beef or click on before and I dragged down to be twelve so that all those numbers are highlighted, and then when you look in the bottom right hand corner there's a little dot everyone see that if you hold your cursor specifically over the dot the cursor changes and you gotta look for that change and it becomes a thin black plus sign and when it's a thin black plus sign I can click and then drag and then it will replicate that data across no that's one years for the auto full handle but it actually does a couple of really cool things also so I just want to take a minute it's not part of our finished product but it's so handy you're going to love this so let's say I wasn't doing different shows I was doing january february march I could have but I'm just going to move off to the side and done this I could say january and then click off of it and click on it and use that auto fill handle and it will auto fill my months and that also works with abbreviated months I can dio monday tuesday wednesday which is very very handy and I can also use this we already saw it to replicate numbers if it's just a value it will repeat it all the way across but I could also do patterns as well so let's say I wanted to dio um I wanted to count by twos I could say two for six and then on I did three of them to establish the pattern you wanted tio like two four might not be enough because it doesn't know if it's doing two four six or two for eight sixteen so by giving it three numbers have established the interval and now I can use this and we can go down as well with the auto fill handle so it's a wonderful way of entering data without doing check t sell, buy, sell, buy sell one of my favorite things I like to do with it also is you can use it to erase as well so I thought what he raised all of this stuff that I just added to this spreadsheet I'm going to click on g two and go across to k and then I'm going to use the auto fill handle and it's going to overwrite all of this with blank empty cells so it's a quick way of deleting because sometimes when you try and clear sells you don't get the effect that you want it it just clears one cell and not all of them so audio filling blank cells I use it all the time okay so now that I have in these numbers I do need to modify them a little bit for each of my shows so nor wack I'm going to go to d three and nor wack and make that three twenty five and the stars four hundred um nor wack doesn't charge me electricity so I can erase that out and put a zero there norway doesn't charge me for internet, but these are charges me seventy five bucks, so I'm just going to go in and make some of these variables here, so I'm going to put in norway eighty dollars for gas he stars three hundred dollars for a plane ticket, three hundred dollars for a hotel in both places, so I will auto fill that across and, um I don't need any staff at least are so I'll zero that out, so I'm just making a couple changes to give us some variable data, okay? So now that we have that and we're ready to do some calculations and there's a lot of different ways of calculating, so I'm going to show you first the manual long method like thiss takes way too long method, so at least you can see how it formulas put together, then will dio three or four different kinds of of ways of doing the math. So I'm going to start in a thirteen and I want to put in here um my total expenses and so the first way is to type everything in with your hands. This is the only time you're ever going to do this, but I wanted to do is get you started with it, so when you start with the formula, the very first thing that you have to do is type in equal sign and that equal sign is what is telling xl that you're about to do some math so everybody type equals and then let's we put in each of the cell addresses so I do before and knows that puts a little box around it and then a plus sign b five plus b six we start seeing a little rainbow here of all of our of all of ourselves now go ahead and include b eight and b nine even though there's nothing in there because there could be and there might be a change later on now also noticed that I'm not typing in the actual numbers that are in the cells I'm only typing in the addresses of the cells and that's I've seen people actually go ahead and start typing these numbers in but that completely um um over it's the whole purpose of excel because the whole idea is that if these numbers changin we calculates everything for you and if you type in a fifty by fifty plus seventy five plus thirty six we'll be using a calculator so now I have my formula what here's a troubleshoot sometimes some people go on autopilot and they hit the plus sign again at the end of the formula and if you did that when you hit return it would say hey, it found on earth do you want to correct it and you just go ahead and say yes that's if you have an extra plus at the end of everything so now we've done our first formula and I wanted to go over a little bit of terminology now that we have three different kinds of information here any of the words that we use are called labels the numbers are called values and down here we have a formula because it's a national calculation now I want to point out something and that's the status bar the formula bar right here and this tells you what's actually happening inside a cell because this beat thirteen is made up not of the number thirteen, eighty six, but whatever is in before plus whatever's and b five plus whatever is in b six, whereas and we're gonna hit escape before I click on anything else if I click on any of these cells, these are values and this helps me know exactly how that number came to bay because when I click on a fifty I don't know if that's fifty typed in or five times ten or two times twenty five or one hundred divided by two. And so this status bar up here is what's showing me what made up that answer now something that it's also important to note at this point is if you're double clicked inside a cell and I start clicking on other cells it's going to start trying to incorporate them into the formula and that's wrong we don't want that to happen so if you catch that happening before you do anything else just hit the escape key and then all it will wipe out everything that you just did all of this mistakes he just made or of course you could also undo and take it back the way that it wass so now the second way that we're going to make a formula we're going to go to column c and what's the first thing that I have to type in equal sign so type in equals this time what we're going to do is click on each of the cells and I don't even have to put in the plus sign I'm just gonna click click click click click and it goes and it adds them on a pc you actually do still have to hit the plus sign the mac is smart enough to be able to do that okay and I hit return and it takes the answer now the next way that I'm going to do a formula is by actually using a function and so um a function is instead of using all the cell addresses b five plus b six possibly seven plus b eight it's a word that tells excel what kind of math to do and there's a specific syntax for it so I'm going to take this one in myself this first time so of course you start with end equal sign good and I wanted to add so I'm going to use the word some which tells it that I'm going to add and then I'm going to use a parentheses e and this is going to give the range of what sells in orderto add up and so the terminology you can see in the screen tip under my cursor it's is some parentheses number one number two number three those air technically technically called arguments what we're going to do is give it a range from here to here and what's nice is that I can do that by dragging I can click on d four and dragged down to d twelve and it auto fills it for me so what this formula is saying from d for the colon means to and everything in between up to d twelve and then I finish it with a close parentheses e and then hit return and we did get a different number this time but that's because some of our values air different and if we want to see that in action go ahead and change nor work let's say maybe they give me a discount for being a good customer it's only going to three hundred dollars so if I change d four make it three hundred my answer automatically goes down as well now the next way of making a formula is there's a button up on the toolbar right here that's it called an auto some button and by default if I just clicked on it it would at it'll do us some but I also want to point out there's a tiny little arrow to the right of it and if I click on that it has the five most common statistics some average count which will tell me how many numbers there are maximum is the biggest number minimum is the smallest number, so I can just click on this button and click some and boom it does the same thing that I just typed in my hand and so when you do the auto some button first it always looks above and if it doesn't find anything to add above, it always looks to the left instead so now we have some of the four t e twelve and I'll press return so let's take a look at that going vertically as well go to three and type in total and let's just use the auto some button now so I'm going to go back up tio this backwards blue or the blue e and that's called a sigma and I'll click on that and it does some from before tea for perfect okay, I'll hit return click on it again but wait this time it didn't do the right thing this time it went to the number above because like I said first it looks for numbers above then it looks for numbers on the left so the first time there was no number above, so it did the left correctly this time has got the wrong thing, so without clicking anywhere else, I'm going to give it the range that I want I'm going to go over and click on b five and without lifting up my thumb we're out looking at my finger I'm going to go from b five t five and then hit return does that make sense now? I don't want to have to sit here and do that same step over and over and over again, so this is one of those places where the auto full handle comes in really handy I'll go back up to five and all used my little handle in the corner and all payments so that I get the thin black plus sign and I'm going to drag it all the way down until row thirteen and then let go. And so now it replicated the formula and what's neat about this is it uses something called relative cell referencing so every time I move down a row notice that it's updating the formula and it's going from b five to be six to be seven to be eight to be nine automatically and if I had moves to the right it would do the same thing with the columns it would go from b to c to d t e. So whenever you use the auto fill handle, it will update euros in your columns automatically. Okay.