In this lecture, I'll review the basics for navigating a spreadsheet and crafting formulas. We'll take a look at the different types of data that are available to you on a spreadsheet using Excel. And we'll look for some options for displaying them in different ways. We'll examine mathematical operations on both cells and ranges of cells called, arrays. I'll cover how to control the order of processing in formulas, which can sometimes lead to errors. And I'll provide some shortcuts for copying data in formulas. Imagine that you are young, single, with few commitments, and you've just been offered an opportunity to joining a new start-up venture located in San Francisco. At first look, the opportunity sounds exciting. The company has offered you a starting salary of $75,000 and the benefits include full insurance coverage. But let's build spreadsheet model to take a closer look at this opportunity. In this example, we've been offered an interest a new job opportunity with a startup that's based in San Francisco. Now the catch is, it's in San Francisco and we're going to have to move there, and San Francisco can be an expensive place to live. So let's begin by looking at their salary offer. They've been offering as income to me, a salary of $75,000 for the first year. Let me put $75,000 into this cell B3. I call that B3 because it's the intersection of row 3 and column B. Now, notice when I put that number in cell B3, it right justifies. Excel's going to treat this as a value that can be used in formulas as a numeric constant. However, what I'd really like to do in this cell is to show what my payroll take home pay will look like once I've taken the $75,000, divided it by 12 for the 12 months of the year, and applied the tax rate that I'm expecting. So rather than 75,000, what I want to do is to put an equal sign in this cell. Which will alert Excel that I'm expecting to write a formula here. I'll take the $75,000, divide it by the 12 months of the year. And then, there's the issue of the tax rates. San Francisco has fairly high local taxes and combined with the federal and state taxes, you have an effective tax rate of about 35%. So that means, I'm really taking home only 65% or 0.65 of this value. Meaning, my take-home pay should be in the neighborhood of $4,000 in change. As I mentioned, this is for the first month. I'm starting, let's say, in January of 2017. There are number of ways I could indicate on the spreadsheet that I mean January. For instance, if I type 1/1/2017, I see a date format that looks like it's being treated as a string of texts. In truth, Excel recognizes that I'm intending to be working with dates and will allow me therefore to do some calculations with dates if I need to. Given an example of that, let say this second month means I see my second paycheck on 2/1/2017. Now, it's not particularly useful for this model but in others you might want to know what the number of days are between 1,1 and 2,1. You can treat these values as numeric data by taking a referenced cell, C2, in this formula, subtracting from it the value in B2, and I can see that there's 31 days between those two dates. I do this just to illustrate the fact that Excel has a collection of data types, and dates are treated in a way that's different from either numbers or text. In truth, the way I like to represent months is just simply by typing January. And I can, if I like, type February in the next column. But let me show you one other shortcut in Excel that makes this a lot easier to do. Let me zoom out to show you a 12 month spread. Let me stop here and illustrate some of the shortcuts that Excel provides for rapidly building out your spreadsheet models. In this case, what I want to do is illustrate this fill handle that's shown here at the bottom right corner of the selected cell. When I grab that fill handle, when the icon indicates a plus sign, I can pull across to the cell M2 and you'll notice that Excel has both copied what was my intended entry in cell D2 from the value and C2 but it recognize to this a date. And therefore, type out the different months as you see displayed here. Similarly, I can now take my estimated payroll and copy it across the 12 months. Now, in July of the year, the company has suggested that I can participate in their annual pay increase. And I can expect a 5% increase at that point. In order to indicate that in this formula, I'm going to change the value and multiply what is the current formula by the number 1.05, indicating the value will go up by 5% starting in July. Again, I have that change in a particular cell, H3 in this case, and I want to copy it through the rest of the year. Now, let us look at some expenses. The big one is housing. I'd like to be able to walk to my work and in investigating some of the rents that are available for housing near where my workplace is, I've discovered that the least expensive place I can identify is $3,000. As I say, San Fransisco is not a cheap place to live. The Common practice in San Francisco is to collect on the first month's rent an additional two months of deposit. So it's not just $3,000, it's also $3,000 plus $6,000. [NOISE] So that first month I'm going to need to have a total of $9,000 to fund my lease for the first month. Now in the second month, I'll be able to go back to the standard $3,000 and that should repeat itself, according to my lease for the remainder of the year. And using the fill handle, I'll copy across all 12 months. Some additional expenses that I'll have to contend with are food, utilities, travel, entertainment, and then the things I haven't thought of. Just to put in some numbers or estimates for this, let's assume we are going to spend $100 a week or so, or $400 a month on food. For utilities, this will include phone and related utilities, cable television, that kind of thing. Let's say, we'll spend $300. Travel should be interesting. I believe that we can go without a car in San Francisco and rely on public transportation, and as I say, I want to be close enough to my work to walk. So with public transportation and maybe the occasional Uber from getting to place to place. I'll estimate, I'll spend $200. I'd like to get as much as I can for entertainment, but let's say, $500 for now is what I might have to work for. And again, there may be other unanticipated expenses that I need to worry about, and I'll put in $500 for that. Now, let's take a sub-total. And look at these expenses compared to my income. In this particular case, I can use the SUM function that's available in Excel to add the values from cell B6 through cell B11. I start with the equal sign again, to indicate that I'm writing a formula. And in this case, I'm going to type out one of Excel's built in functions, this case it's a sum function. The sum function allows me to take a range of cells, meaning a set of adjacent cells, and indicate that the sum function should add them all together. In this case, to indicate that range, I could type out individual numbers or I could just indicate the beginning of the range, in this case, cell b6. And use the colon to indicate that I'm trying to tell Excel to work on a range or on array of numbers. And then this case, the last cell in that range, or that array is cell B11. So my total expenses are $10,000. My total income is $4,000. This isn't looking very good at the moment. But just to see what the cash on hand might look like I'll write a formula that says, in this case, I want to take the value that's in cell B3 indicating my income for the month and subtract from it value in cell B12, the total of my expenses. So I've lost $7000 already. Now as we go through this 12 month spreadsheet example, I can see some hope in the fact that I get a raise in July, and it's also possible that I'll see some improvement when I get beyond the stage of having that $6,000 balloon payment for housing in January. To copy these formulas across the entire range of time that I'm interested in looking at, I can again use this fill handle and copy all of the formulas at once, which allows me to fill out the entire spreadsheet. And you can see that things do get better but not a whole lot better, as we move through the 12 months of this simulation. So in this particular case, what I have to do is find ways to make some savings in my expenses. To do that, let me first think about what it might mean if what I did to control my housing cost was to share it where I live. So in this case, what I'm going to do is take that $9000 and divide the value that's in that formula now by two people. That makes life a little bit better. It also illustrates a problem with Excel that you need to keep your eye out for, and that's the order of calculation. So in this particular case, the formula says, the value in the cell should equal 3,000 plus 6,000 divided by two. That's not exactly what I mean. And to illustrate the problem, let me jump to another sheet here, where I've put out a description of what I'm calling PEMDAS, or the order of calculation, by default, in Excel. Formulas in Excel follow a four step order of calculation. To begin, if your formulas include items in parenthesis, Excel will attend to those first. Secondly, if you're formula includes an exponent. Exponents are indicated by the up caret symbol, above the six key on your keyboard. Exponents are treated as the second most important item in the order of calculation. Third is multiplication and division. Multiplication and division is indicated by the slash key. Or in the case of multiplication, the star. And finally, I want to show you the symbol for addition and subtraction. Addition is a plus key, which is shift above the equal sign, and then a minus key, which is the key just to the left of that. You'll notice, I put asterisks or apostrophe in front of this formulation. That is an indication to Excel that I should treat this value as text. Otherwise, it would consider the plus sign as indication I was starting a formula. Whenever you see this kind of situation, where the information in one cell is overriding the display of the information in another cell, there's a way to readjust the column size to allow the full display to appear. So in this particular case, I have information in column B that's hidden by some information I have in column D. To make that change, I can either pull column B to make it wider or I can double click on the line that's separates column B from C. And will automatically adjust for the widest entry in that column. So in this formula, as you see, the order of calculation that Excel follows will treat that division sign before it treats this addition sign. Meaning, it'll take 6,000 divided that by two and then add 3,000 to it. That's not what we intend. So using parenthesis, we can force Excel to instead do the addition first, take that product and divide that by two. And I get a different number. So we have a few more changes to make in the spreadsheet before we're done. We made the appropriate adjustment to show the fact that we've decided to take on a roommate for the expenses associated with the January rent. We haven't done anything yet for the rest of the year, so let me add that now. I will edit this formula. To first make it a formula using an equals sign and then divide it by two. And with that, I will grab this fill handle and pull it through the rest of the year. We also need to adjust the cash on hand formula. At the moment, it's showing the results for January of the difference between income and total expenses, as a -2300 in change. In February, that also shows just the results of the difference between the income and total expenses for the month of February. But I actually like to carry forward, either the positive or negative balance from the month of four. So to do that, I will edit this formula. And simply make a reference to whatever the value was in the prior month. When I then use the fill handle to copy that address across, or that formula rather, across. You'll see that we have a running balance that stays negative for the first four months with a positive balance thereafter. Some of these cells have additional indications of pennies to decimal placed pennies. I'll show you a way to control that, so that you can either increase decimals or decrease them using the toolbar. And I'll do that for all of these. Finally, I want to show you one last function, which is the MIN function. Basically, I want to have in a single cell Whatever would be the minimum value, I mean the lowest value we're likely to see over the course of that 12 months. Now in this small spreadsheet like this, you can just kind of spot the fact that January's the bad month. But if things changed if you added travel expenses for instance to go home in July or if you had some other changes that were unexpected in the other expense line that might change and in bigger spreadsheets with larger columns it might be hard to keep track of that, but I want to use this as an illustration of the minimum function. This is an example of using a built-in function to show a primary result you want to keep your eye on. In this case, it's to what extent do I go into the hole with this flaw of expenses and need to be prepared to finance that either through credit or some other method. So in this case, what I'll do is say, equals the MIN function. And MIN will also take an array as an argument or a range of cells. In this case, the cells that I'm interested in are B13 and with the colon I'll indicate the end of the cell range, which is in this case is M 13. And when I hit the Enter key, the result picks the lowest number that we spotted before from January, meaning that I need to be prepared to finance my San Fransisco adventure, at least to the tune of $2,300. So that's it. This is very simple spreadsheet model, used to plan out expenses associated with a possibility of a job opportunity in San Francisco. It's not a model that I would necessarily keep, but it does illustrate some of the elements of what we'll see in models that are used for more elaborate and difficult business decisions in future sessions. Also I hope that for those of you who are new to Excel, you saw some of the basic shortcuts and techniques for navigating and building a spreadsheet.
