FIN360Mod1Lect1

Hello and welcome to Module 1, Lecture 1 of Finance 360 Financial Management with Excel. Here, we're going to be looking at some very basic tools within Excel, the first of which is going to be cell references. So, we have a sheet down here. I'm going to double-click and rename the sheet to say Cell Ref.

going to double click and rename the sheet to say cell ref so the first thing about cell references is that the default is an is a relative reference so a relative reference is going to be letter number with no other delineators

So, the first thing about cell references is that the default is a relative reference. A relative reference is going to be letter number with no other delineators.

function to add D 1 to e 1 right so when we see when we click on them right the default is there's a letter that's denoting the column and there's a number denoting the row right in this matrix spreadsheet system here

All right, so we enter in a 1 here and a 2 here. All right, if I hit an equal sign in F1 and I click here on D1, all right, it says equals D1, and we can write a function to add D1 to E1, right? So, when we click on them, right, the default is there's a letter that's denoting the column and there's a number denoting the row, right, in this matrix spreadsheet system here.

based on the relative position of the formula so if I were to say copy and paste right now you see that the same formula when I paste it is giving me 5 right because it's now adjusted to put a 1 + f1 into the equation right

All right, so when we hit enter, right, we obviously get that 1 plus 2 is equal to 3. And now relative references, right, they're called relative because if you copy and paste them into other cells, then the references are going to change based on the relative position of the formula. So, if I were to say copy and paste right now, you see that the same formula when I paste it is giving me 5, right, because it's now adjusted to put a 1 plus F1 into the equation, right?

still doing the same thing it's adding 2 & 3 right to give me five alright so that's it what happens in a relative reference right and just as a note sometimes later in the lecture series you can also copy and paste using this

So, the original equation, right, was built to add the two numbers in the same row in the two immediate cells to the left of where I entered the formula, right? And so, when I copied it over here, right, it's still doing the same thing. It's adding 2 and 3, right, to give me five. All right, so that's it, what happens in a relative reference, right? And just as a note, sometimes later in the lecture series, you can also copy and paste using this crosshair, right? If you position right at the bottom right-hand corner of a cell, you can copy it down and you can copy it across. All right, so I often do that as just kind of a quick shorthand. So, you see when it goes down, right, there's no information over here for E2 and F2 or F2 and G2, right? And we copy across again, now it's adding three and five together, so I get rid of these. So now we go to the next type of reference, an absolute reference. This has a dollar sign in front of both the letter and the number. And so, I'm going to type in one and two again, and now I'm going to put a dollar sign in front of, whoops, both a D and the two, and then I'm going to add E2, and I'm going to do the same thing. But now, when I copy across and copy down, everywhere that it's copied, it's going to D2, right? It does not change when copied when using the absolute reference with the dollar signs. Another thing to note with references is that when you cut and paste instead of copy and paste, right, it keeps the exact same formula. So, if I were to cut and paste, right, you see that it's unchanged, D1 and E1. All right, I'm just going to undo that. All right, so when we copy and paste, the relative reference is going to change. Cut and paste, it does not change. And then with an absolute reference, right, copying or cutting, right, it's going to remain unchanged. It's going to refer to the original cells in the equation. So now we have what's called a partial absolute reference to look at here. In a partial absolute reference, we have a dollar sign in front of the letter or a dollar sign in front of the number. It's going to lock that portion in. And so, let me set up a table here. Let's do one, two, three, four, and five across both the vertical and the horizontal. All right, so let's say I want to build a multiplication table here where I multiply this number by this number. All right, so a relative reference and an absolute reference aren't going to get it done, and so I need to use these partial absolute references. And so, when I, all right, so when I look at this, when I copy across, I always want to multiply by the number in this row, and when I copy down, I want to keep multiplying by the number in this column. All right, so for the column portion, I'm going to put the dollar sign in front of the letter because that's going to lock the column, and then on the row portion, I'm going to put a dollar sign in front of the row number. That will lock just the row, right? So, if I copy the rest of the table, I should get a multiplication table, and so I can copy across and I can copy down, and it seems as if we've got a little bit of an error here. All right, so let me look back at what exactly the formula is doing. All right, so I think I might have done this backwards, so let's try locking in the row 7 and the column C. All right, so, all right, so yes, so originally, right, what I did was reversed, right? So, I ended up just getting a table full of ones, right? So, if we lock the 7 here, right, when I copy down, it's going to keep that at 7, and when I copy across, it will keep this at C. So now we have a multiplication table. All right, so you can see it is very easy to kind of get a little bit tripped up in terms of partial absolute references, right, whether or not you want to select for the dollar sign in front of the letter or the number. All right, so it's a review, right? When I copy down, right, I want to be locking in this row, right, so that it always looks back up to this row 7, and when I copy across, I want to be locking in this column, right, so that when I move down, it adjusts this but doesn't adjust as I go across, and then vice versa, right? With this, it doesn't adjust when I go down, but it will adjust when I go across. All right, so we see this is a functioning multiplication table, right, multiplying the columns by the rows. Let me put in some other things in Excel from a formatting standpoint if you haven't seen this stuff before: borders. And we can put in a right border here, and we can put in a bottom border here, right, to show that we're multiplying through, right, this table. Okay. All right, so that's cell references, extra E there, and we're going to take a quick look now at something called an IF statement, right? And this will be very useful for us throughout the semester. It's a very simple function in Excel. Increase the zoom a little bit here. So, I'm just going to enter a number here, the number 8. All right, so what an IF statement does is we can create a condition. All right, so let's say if this is equal to 8, value of true. Now, we can put in numbers here, we can put in references to other cells, and we can put in text. All right, so let's start with text. And so, if it's true, it types "Great," and if it's false, it types "Not." Let's say you see it types "Great," and we can put in other numbers. We put in a 7 here, right? Notice this includes a reference, right? So, if I copy this down, right, now it's going to look at the number in A2 instead of the number in A1, so now it's false, so it types out "Not." All right, so we can type in six here. We can look at some other conditions. Let's say that greater than five, value of true. We can put in a reference, so value of true, it'll give us eight. Value of false, we click on A2 and we'll say seven. All right, so it's going to display eight. And then, what we can also do is less than. So, if five is less than three, right, and here we'll just do numbers, you do one if true, zero if false. That's a common thing for IF statements, and so zero for false. All right, so there's a lot more we can do with IF statements. You can have IF statements inside of IF statements, but just this general introduction, I think, will give us a good jumping-off point for when they come back later on in the semester. And so, that is all for this first lecture of Module One.

see when it goes down right there's no information over here for e 2 and F 2 or F 2 and G 2 right and we copy across again now it's adding three and five together so I get rid of these so now we

go to the next type of reference and absolute reference this has a dollar sign in front of both the letter and the number and so I'm going to type in one and two again and now I'm going to put a

dollar sign in front of whoops both a D in the two and then I'm going to add e to and I'm gonna do the same thing but now when I copy across and copy down everywhere that it's copied it's going

to d2 II - right it does not change when copied when use the absolute reference with the dollar sentence another thing to note with references is that when you cut and paste instead of copy and paste right it keeps the exact same formula so if I

were to cut and paste right you see that it's unchanged d1 and e1 all right I'm just going to undo that all right so when we copy and paste the relative reference is going to change cut and paste it does not change and then with

an absolute reference right copying or cutting right it's an it's going to remain unchanged it's going to refer to the original cells in the equation so now we have what's called a partial absolute reference to look at here in a

partial absolute reference we have a dollar sign in front of the letter or a dollar sign in front of the number it's going to lock that portion in and so let me set up a table here let's do one two

three four and five across both the vertical and the horizontal all right so let's say I want to build a multiplication table here where I multiply this number by this number all right so a relative reference at an absolute

reference aren't going to get it done and so I need to use these partial absolute references and so when I all right so when I look at this when I copy across I always want to multiply by the

number in this row and when I copy down I want to keep multiplying by the number in this column all right so for the column portion I'm going to put the dollar sign in front of the letter because that's going to lock the column

and then on the row portion I'm going to put a dollar sign in front of the row number that will lock just the row right so if I copy the rest of the table I should get a multiplication table and so

I can copy across and I can copy down and it seems as if we've got a little bit of an error here alright so let me look back at what exactly the formula is doing all right so I think I might have done this

backwards so let's try locking in the row 7 and the column C all right so all right so yes so originally right what I did was reversed right so I ended up just getting a table full of ones right so if

we lock the 7 here right when I copy down it's gonna keep that at 7 and when I copy across it will keep this at C so now we have a multiplication table all right so you can see it is very easy to

kind of get a little bit tripped up in terms of partial absolute references right right whether or not you want to select for the dollar sign in front of the letter or the number all right so it's a review right when I copy down

right I want to be locking in this row right so that it always looks back up to this row 7 and when I copy across I want to be locking in this column right so that when I move down it adjusts this

but doesn't adjust as I go across and then vice versa right with this it doesn't adjust when I go down but it will adjust when I go across all right so we see this is a functioning multiplication table

right multiplying the columns by the rows let me put in some other things in Excel from a formatting standpoint if you haven't seen this stuff before borders and we can put in a right border here and we can put in a bottom border

here right to show that we're multiplying through right this table okay all right so that's cell references extra e there and we're going to take a quick look now at something called an if statement right and this will be very

useful for us throughout the semester it's a very simple function in excel increase the zoom a little bit here so I'm just going to enter a number here the number 8 all right so when an if statement does is we can create a

condition all right so let's say if this is equal to 8 value of true now we can put in numbers here we can put in references to other cells and we can put in text all right so let's start with

text and so if it's true it types great and if it's false it types not let's say you see it tapes great and we can put in other numbers we put in a 7 here right notice this includes a reference right

so if I copy this down right now it's going to look at the number in a 2 instead of the number in a 1 so now it's false so it types out not all right so we can type in six here we

can look at some other conditions let's say that greater than five value of true we can put in a reference so value of true it'll give us eight value of false we click on a two and we'll say seven

all right so it's going to display eight and then um what we can also do less than so if five is less than three right and here we'll just do numbers you do one if true zero false that's a common

thing for if statements and so 0 for false all right so there's a lot more we can do with if statements you can have if statements inside of if statements but just this general introduction I think will give us a good jumping-off

point for when they come back later on in the semester and so that is all for this first lecture of module one