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 at 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 join 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 a spreadsheet model to take a closer look at this opportunity. In this example, we've been offered an interesting 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 then 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 divided 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 and change. As I mentioned, this is for the first month. I'm starting let's say in January of 2017. There are a 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 text. 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. Give you an example of that, let's 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 reference to 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. And 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 cell M2. And you'll notice that Excel has both copied what was my intended entry in cell D2 from the value in C2, but it recognized it as a date, and therefore typed 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 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 H2, H3 in this case, and I want to copy it through the rest of the year. Now let's 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 Francisco 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 + $6,000. 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 it 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 numbers for estimates for this, let's assume that we're going to spend $100 a week or so, or $400 a month on food. For utilities, this would 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 for getting from place to place, I'll estimate I'll spend $200. I'd like I like to have 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 subtotal 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, in 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 this 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 an array of numbers. And in 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 the value in cell B12, the total of my expenses. So I've lost $7,000 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 with a roommate. So in this case, what I'm going to do is take that $9,000 and divide the value that's in that formula now by 2 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 this cell should equal 3,000 + 6,000 / 2. 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 parentheses, Excel will attend to those first. Secondly, if your 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 equals sign, and then a minus key, which is the key just to the left of that. You'll notice I put a asterisk 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 an indication I was starting a formula. Whenever you see this kind of situation where the information in one cell is overwriting the display of the of 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 separates column B from C, and it 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 will take 6000 divided that by 2 and then add 3000 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 2. And I get a different number. So we have a few more changes to make in this 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 2. 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 and change. In February that also shows just the results of the difference between income and total expenses for the month of February. But I'd actually like to carry forward either the positive or negative balance from the month before. 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, two 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 you're likely to see over the course of that 12 months. Now, in a 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 numbers of columns, it might be hard to keep track of that. But I wanted 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 flow of expenses, and need to be prepared to finance that either through credit or through some other method? So in this case what I'll do is say =MIN function, and MIN will also take an array as an argument or a range of cells. And 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 M13. 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 Francisco adventure at least to the tune of $2,300. So, that's it. This is a 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.
