Hi, everyone. Welcome to our first video lecture. We're going to learn a lot about Excel, especially some advanced business techniques in this course. But just to start, I know everyone comes from different backgrounds. I want to do a very simple spreadsheet, go over some very basic functionality of Microsoft Excel, and I thought we could do that just by creating a budget. Everybody is the CEO of themselves. Everyone makes money, everybody spends money. When you save money at the end of the day, it's called your personal savings, of course. So the money you bring in, that's your revenue. What you spend, of course, that's your cost. Then at the end of the day, what you're after, of course, is savings, or in the business world, your profit, which is your revenue minus cost. So let's talk about your income. What I'd like you to do is make this spreadsheet with me, go through, see if you can keep up with the pace that I'm going at. If I ever go too fast, pause the video. My version of Excel may look a little different than your version of Excel, and that's okay. They have all the same functionality. I'm on a PC, you may be on a Mac. If that's the case, just search for where the thing is. If it looks a little different, I'm sure it's very close to a quick search to find it. What I care more about is the functionality of the spreadsheets, the formulas that we'll use, the formatting that we'll use as well. So let's do a very simple budget just to plan things out. I'm going to make up some numbers. If you want to plug in your numbers, that's fine. But let's do a salary. Again, notice I'm labeling the number I'm about to put in. I don't put the label in the number in the same cell. I'm going to make up salary here, let's say, $50,000. Notice that I'm typing the number in naked, as I call it. I'm not putting the dollar signs, I'm not putting the commas. I just want the digits, five and then a bunch of zeros. When I hit "Enter", I get the number now if I want it to display as currency, I can use this dollar sign symbol here at the Home tab, or use the drop-down and display it as currency. Normally, when you work with financial spreadsheets, you don't include the pennies, so we'll kill the pennies. Now, you can use these two little more or less increase or decrease decimal places up on the Home tab. We're getting to know a lot of the features of the Home tab. Of course, whenever you receive your salary, and you receive your paycheck at the end of the day, the number that you make is not equal to the number that is deposited into your account. Nothing is certain in life, but death and taxes. So let's put some taxes in here. Tax brackets may vary both to federal taxes. Let's say they're at 22 percent. Who knows what it'll be? Again, making up some numbers here. Taxes, of course, vary if you have dependents, you're married or single. What happens when I do that is the column gets a little too small. Just a friendly reminder, how do you do that? You widen the column. You can do it automatically by double-clicking. So we'll click-click, if it's too small, and it will auto fit it for you. You could drag it across. That's no big deal. Then we have our first calculation. I would like to know what 22 percent of $50,000 is. So whenever you want Excel to do a calculation, of course, we start with equal. This tells Excel, hey, turn into a calculator. Compute something for me. I want 22 percent of $50,000. Pause the video for a second, and see if you can remember how to compute this. What does "of" mean? Is it add, subtract, multiply, or divide? You're ready? I hope you said multiply. So 22 percent of $50,000 is 22 percent of $50,000, and notice I clicked the cell that contains the $50,000. I do not type $50,000. This is called referencing. I want to link to the cell. When I do that, it puts the address of the cell before, and this is important. You could also type, if you know the address, if it's easy to find, you can go in there and type it, B4. I find it a little easier to click. But whatever works for you, when you're ready, hit "Enter," and there is our taxes that we're going to pay. Now, notice the formula is not showing. Of course, if I highlight the cell, it's showing in the formula bar. But really what I want to do is there's a nice little formula called formula text. You can start typing, and it appears. Hit "Tab," and you fill it. Then once you do, it opens the parentheses, just click the cell with the formula, close the parentheses, and hit "Enter", and it will show you the formula. This is nice when there are actually formulas in the cell. I put it right next to it to show both to you, and then, of course, if we're passing this off to somebody to show them how it's being calculated. So there I have my taxes at 22 percent. Other things that I might want to consider is some sort of medical, maybe I have a dental that gets pulled out, if you have other people coming out here. Who knows what these are? Some people put in for their retirement. Again, all these things will vary depending on what it is, if you have a very expensive medical plan, or you want the finest dental care money can buy. Some people, as a little bit of personal philosophy here, they put as much money as they can into retirement. Some people hold back because they need the cash now. I'm just going to make up some numbers here. There is no right or wrong numbers. Let's say, I'm putting 5,000. Down for dental. Dental is generally cheaper than medical for sure. Again, just making up numbers. So let's put the numbers in. Again, I don't put the dollar signs in, I don't put the commas. You can do batch formatting, you can highlight multiple things. Come over here. Change it to currency. Let's remove the pennies, and there we have it. These are all hard-coded numbers. You could imagine trying to back into this and maybe you know what percent of your retirement is and put another formula, but that's all fine. Some things I'm going to do just to make this spreadsheet a little more pretty, I'm going to bold. I'm going to use a keyboard shortcut, but you can certainly just click the B here on the Home tab. I'm also going to indent. These are these arrows on the alignment portion of the Home tab. You can slide these over, you can make these nice, you can right justify things, although that doesn't look as nice anymore. We will do like that. This is all personal preference. We're just doing this to show you what these buttons do. You choose how you want to display it. As long as it's readable, then I'm fine with it. The last but not least if you want your net income, we'll type in our net income, maybe we'll back this up, not indented, make this bold. Once again, this is a formula. I do not want anyone to pull out a calculator or their phone to work out what this is. Remember, at the heart of it, Excel is a calculator. You can go equals click on the salary and then you could do this a couple of ways, but for the first time, let's just see it in action here. You can subtract off your taxes, you can subtract off your medical, you can subtract off your dental, you can subtract off of your retirement. Hit "Enter" when you're ready. It is a nice formula. So you can see all the pieces in motion here, but this is your salary, and then it comes off the top, and what you're left with at the end of the day, and again, this is yearly. Instead of $50,000, you are actually taking home $28,500. This is a calculation, so I will copy and paste my formula text that's in C9, just to show that this is actually a formula. All the other ones are hard-coded. Great. Now I have my net income, and now I can start to plan my expenses. Some people go across the page, I'm just going to go down. Let's look at expenses. Once again, I'm going to bold it and now I have to think about what are some things that I spend my money on. Again, this is going to vary significantly by person, so maybe you're just a young person starting off in your career. You're living by yourself, or you have a roommate, which makes things a little cheaper. Maybe you're a little more advanced in your career, and you have several children, or pets, or who knows what that are eating into your expenses. I'm just going to make up stuff here as we go along and throw in numbers. If you want to make this spreadsheet useful to you and put it in your numbers, great. If not, just go along my fake ones. Again, I care more about the skills. Can you create a spreadsheet that looks like this, can you get it to work, can you make it readable is what we're after for the first one. I promise they will get more complicated as we go along. We're just starting off very simple just to make sure everyone's on the same page. Let's just make up some stuff. How about your rent, your mortgage? Let's say this is 1,000 bucks. Two thousand bucks a month, I don't even know, so let's say 1500. We'll split the middle here, maybe you have an expensive place. So 1500 and then times 12, wanting to watch out for is your units. All of our income is yearly so if our rent is 1500 a month, I want to multiply that by 12 to get the annual amount, so I hit 1500 times 12. Then what else? We probably want to eat in this apartment. I bold on for no good reason, so we'll turn that off using the keyboard shortcut. Food depends. Do you eat cheap, do you want steak and lobster for every meal, who knows? I'm going to make up a number and say $5,000. Things that you might want to pay also, perhaps you have a car or transportation, and really what I'm going for here is not an exhaustive list of how much I spend on things. Again, you'd probably have to go through really your personal history of credit card or banking statements to see what you spend your money on. Maybe you travel a little bit. Maybe you buy stuff for the apartment. I guess the apartment doesn't come furnished. You probably want to buy some plates and forks or broom, whatever, toilet paper, that sounds important. So you have your food, you have a car, you have your travel. Maybe you have a pet. Maybe you don't. If you don't, of course, you'd leave this as zero. Maybe you want to buy some gifts for the family. Maybe you want to have some personal savings that you just want us to pay maybe you have some loan that you have to pay off. Who knows? I'm at the bottom of the screen, so I made my list. Again, I'm going to totally make up numbers here for no good reason and just put in some numbers, maybe $1,000, and another $1,000, and 500. I'd like to save 5,000 a year, and I have to pay back 10,000 on loans per year. Who knows? So you have this whole list, and you can imagine this goes on and on, but the point of it is let's make it pretty. So I have my list. I'm going to highlight all the numbers, I'm going to change the formatting to currency, I'm going to remove the pennies, I'm going to indent my labels just to show that they're all under, then I'm going to find my total, so total expenses. Now, last time I took the number and I tracked it off every single item in my list. This is a very short list, and I can already think of 10 things that I'm already missing, but that's okay. Instead of going through each single one and going this plus this plus this plus this, I'm going to use a formula, and I'm going to tell Excel to add them all up for me. There's a formula in Excel. If you haven't seen it, we're going to use it a lot, it's called equals sum, and that means to add up all the numbers in the cells that you highlight. You type equals SUM. You open the parenthesis and then you highlight what you want. Now, this is called the range. I'm highlighting B12 to B20 on my screen. I close the parentheses, and I hit "Enter." Once in a while, it yells at you. Just check if you're getting an error, make sure that you close the parentheses. Here, I have my total expenses, $47,000. That's not good. Just watch what happened. I'm making 28, and I'm spending 47. Clearly my numbers are too much. Maybe I don't spend as much on travel, or maybe I don't get the $1500 per month. So what's the last piece we need? We have our income, we have our expenses, and last but not least, we want our profit. This, of course, is going to be, when we go to the business side of things, we're going to call it profit for us. This is going to be our total savings, so our net savings at the end of the day. Then, let's bold. This is an important number. Some people panic when they see they're spending too much, and I think that's the wrong approach. You just planned your numbers. If you didn't make the spreadsheet, then you wouldn't know not to take that expensive vacation or not to take that expensive apartment, or perhaps you shouldn't buy that car. Who knows? The key to all this is just planning it out using the numbers. So here we go. So what I want to do, I want to take my income minus my expenses, and what happens now is there's a minus sign in front of the number. This is a very important number, and often what we do is we'll change this to accounting. Sometimes when you see books, they use parentheses. I just want to show this to you as well. Again, I won't show the pennies just to get rid of some zeros. But if you ever see parentheses around a number, that is the accounting way to say this is negative. They don't like to put the negative on financial sheets because it's too small and if you're scanning a massive financial document is too easy to miss that number, so you'll often see parentheses. Sometimes it's even in red. If you want, you can make it red. I want to call this out with parentheses so that you don't miss the fact that it is negative. So here, I'm running a deficit of $19,000. This is where we play around with it, and I just want to show you again the beauty of Excel, if you're new to it a little bit. Let me shrink the zoom here so we could see it. One more thing that I want to do, I have a beautiful spreadsheet. It's working nicely. I see my loss of 19,000. We'll fix that in a second. But one thing usually you see on spreadsheets is a nice header. Perhaps this table could be presented in a PowerPoint or send off in email, or who knows? So what we can do is take both A1 and B1 and highlight both of them together. Then there's a button on the Home tab called "Merge and Center." What that does is it combines the two cells into one big one, and then let's just center it on the two big ones. There's also a box here called Cell Styles on the Home tab. We can create our own nice style for this header. We can pick our own color that we want. But there's a bunch of things that are built-in with colors and accents and heading. For no good reason, I'm going to pick Heading 1 just to keep it simple, and again, it just makes it pretty. It doesn't change the functionality of the spreadsheet in any way. It just makes it look a little nicer. So I'm going to copy my formulas. Everything with the formula is going to get my little formula text here, show you the formula, and I have my spreadsheet. Now, if this were really me, I might consider doing things, like, I don't know, maybe putting a little less towards retirement. Maybe I don't put 5,000. What's nice is, what if I say, I really need the money now, so let's put 4,000 away. Again, this is the beauty of Excel and probably why you're here to learn Excel. When I hit "Enter," notice everything changes. It flows through the entire spreadsheet and I get now $18,000. Now I have to go through and find a way to cut some savings. Maybe I live with two roommates and I cut my rent down a little bit, and so it's only 500 bucks a month. Again, if this were Microsoft Word or something else, I would have to go through and calculate every single piece over again, recalculate the total, recalculate the net. I can play around with this, so I hit "Enter," and all of a sudden my deficit is starting to crawl down. So maybe I don't get that pet. Maybe I don't have a pet. That's nice. Maybe I'll cut my travel in half. You can really start playing with this and see what you need to do, either break even or make the money you want. Now remember this numbers include in already $5,000 in savings. Maybe that's a little unrealistic. I was too ambitious. You can start just hacking away at it. Again, once you get your savings under 1,000, now you're at zero. Notice it doesn't show zero. You have a dash. That's okay. Again, a very nice spreadsheet that you can pass off. Again, really, I would expand this out. This is just the beginning, but hopefully you were able to keep up, go through all the cosmetic things about the spreadsheet with the indenting, the salaries, moving the columns around, showing the formulas. I hope this was easy. If not, go back, watch the video again, pause the video, work through it, expand it, add some functionality to it. Be creative, and see what you can do. But if you can go through this spreadsheet, if you are able to keep up, that's fantastic, and we're going to go through and make your Excel skills even better and make you better at creating spreadsheet models and using the functionality of Excel. Great job on this video. We'll see you next time.