[Music] Hi everybody and welcome to week three of 4hour accounting. This week we don't have any 4R material for you to work through but we do have more Excel stuff to show you. I think you'll find this really interesting. This week we are doing data analysis. So I'm going to show you a number of tools that you can use uh when you do data analysis. Now, there isn't a set way in which we do data analysis. Instead, we use a number of different tools, many of which I'm going to show you during this week and next week. Um, and you'll see uh as you go along and as you get more experience, it's going to become easier for you to decide which formulas and functions to use. Okay. So, I've created a practice file for you for week three. You can open up this practice file and you can follow along and try out what we are doing along with me or you can decide to first look at the video and then go to the practice file and try it for yourself whichever way uh you prefer. So let's start off with the first uh stuff that I want to show you. Okay, the the first thing that we need to talk about is absolute and relative cell references. Now, you might have heard this before, but it might not be exactly clear what it means. Okay? So, I'm going to show you by way of an example what an absolute and what a relative cell reference is. Okay? So, if we look at this table where we need to go and do a percentage calculation, we have four different products. We have the sales for each product in units, right? And then we have the sales price in rent per unit of each product. Okay. So now the first thing we need to go and do is multiply the units by the sales price to get the total sales. Right. So there's a number of ways uh in which we can approach this. So what I'm going to do is start off in the cell where I want the answer. press equals. And then I'm going to take um cell B4, which is where we have the units for product A, and I'm going to multiply that by the sales price to get the total sales. Okay. So once you've typed in that or you've selected the relevant cells, you can press enter. Okay. Now before we continue, let's just update the formatting a little bit so that it's easier to read. Okay. Typically, when we work with um noncurrency numbers like this, these ones, these are units. Um it's a little bit easier to read if we have a uh one 100s and thousand separator. Okay. So if we go and select these numbers and we click on the little comma over there, uh it's going to change it to something that looks like this. And then we can just go and decrease the decimal uh places to just show us the numbers like that. So you can see now it has a space in between where the thousands are located and then anything bigger than that. So it makes it a little bit easier to see that this number is 192,000, this is 55,000 and so on. Okay. Um, if we work with rand, what's nice is, um, you can just go and select the numbers on the home tab, go to the numbers group, and then you can go and change that to currency. Okay. Accounting you can also use if you want to. They work broadly the same except for negative numbers. So, let's use uh accounting in this case. It's going to put the rand symbol towards the left and then it's going to um kind of convert it to a currency format. so that we can easily see it's rand. Okay. And we can do the same with the total sales because that is also in rand. Okay. Now if we go and copy what's in this cell or we use the full handle. Let's let's use the full handle and see what happens. Currently if we double click on the cell you can see it is B4 multiplied by C4 to give us the total sales. Right? So if we go and drag the full handle one row down, what do you notice? If we now double click on this cell, it shows you it's B5 multiplied by C5. Okay? So in other words, we are talking about relative cell references here because whatever the answer is in this cell is going to be the amount two cells to the left multiplied by the amount one cell to the left of this one. Okay, if we continue the process, Excel knows what we mean because we are using relative cell references. So for this number over here you can see X has figured out that it is two cells to the left multiplied by one cell to the left which gives us the answer. Okay. Now when we get to um absolute cell references you will see the the difference a bit more clearly. Okay. So now let's first go and add up the total sales. Right. There's two ways in which you can approach this. The first is just to press uh equals sum, open the bracket, select everything we want to add together, close the bracket, and press enter. Okay, so basically it's going to add up all these numbers. If you want a slightly quicker array, what you can do is click in the cell where you want the answer and then on the home tab under the editing group, you'll see that there is a button that looks like this, which is called the auto sum function. And you can do a number of things. You can select the maximum, minimum, whatever. In this case, we want to add the numbers together. So, I'm just going to click in the cell, click on that um button, and then Excel will figure out automatically that we want to add together these numbers. So, it's going to prepopulate that formula for you. All you need to do is check it, and then you can press enter if you are happy with the formula. Okay. Now, the next step that we need to go and complete is to calculate the percentage that each product contributes to the overall total sales. In other words, we need to go and calculate the percentage of uh of the total sales for product A as a percentage of the total. Right? Let's just make this a little bit bold so that we can see that it's a total. Okay? So, how would we do that? We can say equals this number uh in D4 divided by the total, right? Which we can find in D8. If you press enter, you'll get a decimal format. But then, um, if you want to change it to percentage, you can just click on this button over here. Okay. Um, in all likelihood on your computer, it's first going to look like this. So, you'll get a decimal, and then you can just go and press the percentage style uh button on the numbers group, and it will change it to percentage for you. Okay? So, there's no need to multiply by 100 or anything like that. You can just click on the percentage button. Now let's see if we use the full handle. What's going to happen? If we drag it one down, what is it telling us? It says we are dividing by zero, which does not make sense. Okay, but have a look what happens if we look at the source for this formula. Okay, we can see that um Excel has figured out that we want to go one cell to the left to get the sales for product B. But because we have moved one down from the previous calculation, Excel thinks that the number that we want to divide by has also moved one cell down. Okay? And that is obviously not correct. We want to tell uh Excel in some way that the total is always going to be in the same place. So, so the um second part of the calculation is not going to move down down as we go down in the percentage column. Okay. So, how can we do that? Let's change it back to D8, which is the correct one. Okay. And then while you was have your cursor on D8, you can press F4 on your keyboard. And once you've done that, you'll see that some dollar signs appear in the formula. Okay, that basically tells Excel that um it should not change this cell reference if we move the formula around. Okay, it's always going to be in the same place. And that is what we call an absolute cell reference. So no matter what we do, Excel is always going to look for the total in cell D8 if we have locked it with these dollar signs. And like I said, the quickest way is just to press F4 on your keyboard. And that's going to create an absolute cell reference for you. Okay. So let's go back and just fix it in the first uh reference. So you can see we've locked the eight. We have made it an absolute reference and now we can just go and drag it down all the way. So what you can see now is in each case it's going to take the cell one cell to the left and divide it by whatever is in D8. Okay, so that's pretty handy. It means that you only need to do the formula once and then you can just drag it down and it will know in each case it has to look for the total in this cell over here. Okay. All right. So there's just a um another function that I would like to show you and it's called sum product. Okay. Um if you have done weighted average uh cost of capital calculations or the WAC calculations in finance um it's kind of a good example of what some product does, right? When when we do a wack calculation, you have to take the waitings and you have to multiply it by the cost of uh capital in each case and then you have to add all of it together to get the um weighted average cost of capital. Right? In this case, what we have done is we have um taken the units for each product multiplied by the sales price per unit and then added it all together. Okay, we can combine all of those steps into one with a sum product function. So, let me show you what it looks like. Start out with typing equals sum product open bracket. Now, it's asking us for arrays. Now, an array is a set of numbers. So, the first array we want to give this formula will be the units, right? And then the second array will be the unit prices. Okay? So you can see it in the formula bar over here. So it's basically going to take B4 * C4 plus B5 * C5 plus B6 * C6 and so on. Right? It would be a very long formula if you had to go and type that out. But by using the sum product function, it makes it a lot quicker. Okay? So you can see it gives us the same answer as the one that we got here all in one step. Okay? So if ever you need to do something like this, remember to use the sum product function. All right. Now let's look at the second example of um absolute and relative cell references. So I'm just going to scroll down a little bit and you can follow me on your screen as well. Um let's say we have a number of sales persons in our organization. For this example, I've just used some of your lecturers names. Obviously, uh just as an example, these are not real figures. Okay. So, let's say um all of these salesersons earn a certain amount of commission based on the total sales that they have generated in a certain period. Okay. Now, first thing, let's just go and update the formatting. So, the this is obviously sales. So, so it will be currency. It's in rand. Okay. So let's go and change it to uh let's change it to accounting in this case. All right. So it's going to update the formatting for us there. Now for the commission we want to take equals the total sales of of this person multiplied by the commission rate. Okay. Now we can press enter and it's going to calculate the commission. Okay. Now, because we took a random amount and we multiplied it by percentage, Excel knows that this is also going to be a random amount. So, you can see it updates the formatting for us to accounting as well. Okay. Now, again, if we use the full handle and we drag this down, it says that this person has earned no commission, which is obviously not great. And you can see the reason for that is uh the the first cell reference is correct because it's the one to the left. But now it thinks the commission rate for this person is in cell B14 which is obviously not correct. Okay. So let's go back to the first uh cell and obviously um we need to tell Excel that the commission rate is always always going to be in cell B13. So what do we do? we make it an absolute cell reference which means we have to press F4 on the keyboard. Okay. And that's going to lock the um commission rate in this cell. So it's going to tell Excel that always for all the people that we are calculating commission for go and look for the commission rate in cell B3. Okay. Now we can just go and drag it down with the fill handle and it's going to calculate the commission for everybody. And you can just test it by double clicking on a few of the cells. You can see in each case it takes the total sales and it multiplies it by whatever is in cell B13.