Welcome to Week 4 of Everyday Excel, Part 2. In this screencast, I'm going to talk to you all about case studies. In fact, this entire week is going to be dealing with case studies and also using what-if analysis. So the case study concept, let's just consider a simple example here. We have a cone here on the left, and maybe we want to calculate the volume of a cone. I know some of you probably haven't seen equations like this for quite some time, maybe since high school or college, but simple geometric shape here. The volume of this cone is given by Pi, that's 3.14 times r squared times the height divided by 3. Maybe we want to ask the question, how does the volume of a cone change as we vary the radius? So maybe we're going to keep the height constant, but we're going to vary the radius. I've got a little table down here on the right. When we're performing a case study, we're basically looking at, how does the output, and the output here is the volume, depend upon an input? The inputs are usually on the right-hand side of an equation. So here we have, radius is an input, height could also be an input, but in this example, we're holding height constant. So we can vary the radius from 0.5 to 2.5, and then what we're going to do in Excel is we're going to calculate the volume. Another example from structural engineering, we might have a cantilever beam here. We've got a wall over on the left, and we have a beam that's just attached to the wall. If you apply a force, the force is F here in Newton's, those are just the units of force. If you apply the force to the end of this, it's almost like a diving board for a swimming pool, it's going to deflect. So we might want to perform a case study where we vary the force on the tip of this cantilever beam from 50 to 150, and we're going to use Excel to calculate the right-hand column of this table, and that is the deflection in millimeters. In the financial realm, maybe we want to determine, how many months will it take us to pay off a loan as a function of the payment amount? So in this spreadsheet here, we have an interest rate that's compounded monthly, we have a loan amount, that's our principle, and we have a payment. Here I've just put into cell C4, a payment of $350, that's negative because payments are negative in Excel. We can use the NPER function that I talked about previously to determine the number of payments of 350 required to pay off this loan at that interest rate. But maybe we want to perform a case study. You want to do a what-if analysis, what if I pay $250? What if I pay $300? What if I can pay 450? How would that reduce the number of periods or months required to pay off this loan? We can also, instead of looking at the payment amount, maybe you want to perform a what-if scenario, what happens if the interest rate changes? You can have a constant payment of $350, but how will the interest rate change the number of payments that you're going to have to make on this loan? Finally, we could do a two-way. This is known as a two-way case study, where you're setting up this table and we have a column here. We have the rate going down, and we have the payment going across, and maybe you just want to fill this out. The contents of the table would be the number of payment periods, and you just want to set up this table as a reference table for you to determine, if I can pay 4.5 percent and $400 a month, what's that going to do to the number of payments? You can have this table set up and it's a what-if analysis, what if you could do these different combinations of rate and payment and how would that affect the total number of payments? So I'm going to show you how to do these types of things. I'm going to start in this screencasts, and this is going to continue on into the next several screencasts. I'm going to start out in this file called cone volume, we're just going to do a simple case study on the volume of a cone. So we have our formula here, volume equals Pir squared h over 3. That's just a simple formula for the volume that you can look up on the Internet. We have the height here, is going to be two, and I'm just going to assume that that's constant. I've named this cell h up here in the name box, and we want to perform a case study, what is the volume as a function of the radius? Now the easiest way to do this is just to type in this function here into cell B6. So we have Pi, there's a Pi function that's built into Excel that some of you probably learned about in Part 1 of the course, so that's just 3.14 times the radius. Now I'm going to use the corresponding radius to the left, that's our cell A6. We're going to square that, we're going to multiply by h divided by 3, and then I can press Enter, and that's calculating the volume, if the radius is 0.2. I can go ahead and copy this down because my formulas are relative references for the radius, the corresponding radius on the left, but we have our named variable. Remember name variables are absolute references. So that's always going to be cell B3, that's our height, and I can press Enter. So that's one way. I've got this little chart over here, plotting the volume as a function of the radius. So that's the most common way that people perform one way case studies in Excel. Let me just real quick show you a slightly easier way to do this using array formulas. I can name this entire array here, this is known as a vector. A vector is a one-dimensional array, it's either a row or a column. So I can highlight that and go up here to the name box and I can name this something like Rad for radius. Then I can highlight this entire region, and I can type in equals Pi times our radius squared times h divided by 3. This is a little bit easier if you know what you're doing. If you have anything other than Office 365, you're going to have to do Control, Shift, Enter, or you can just press Enter if you have Office 365, and it drops that down and just in one fell swoop it calculates the corresponding volumes. So that's how we can perform a one-way case study in Excel. Let's go ahead and do another one. This is our loan payments. I've got my interest rate named i, I have my principal named P, and we have our corresponding monthly payments over here, and we want to determine the number of payments. To determine the number of payments, I already alluded to this when I was talking about this in the slides, but what function can we use to determine number of payments based upon an interest rate, principal, and a payment amount? That's right. We can use the NPER function. So we can use our NPER, our interest rate. This is compounded monthly, so we have to divide by 12. Our payment is going to be whatever is to the left. It's important here, payments in Excel are negative, and I've put these in as positive values. So just make sure you put a negative in front of the payment. Our principal or our present value is P, that's our loan amount. Our future value is going to be zero, so I'm just going to leave that off. I'm going to press Enter. This means that if we're able to pay $150 a month towards this loan, we're going to have to make 60 plus payments. But we can perform a case study. That's what we're doing here, we're doing a what-if analysis. Well, what if I could pay 400? What if I could pay 500? I'm just going to double-click that down to fill that in, and this is a one-way case study. We're looking at the effect that different monthly payments have on the number of payments. Obviously, if you're able to, you want to get this loan paid off as soon as you can. So you want to try to minimize the number of payments, you want to maximize the amount that you're able to put towards that loan. You guys probably know that the quicker you can pay off a loan, the less overall interests you're going to be paying on that loan. So hopefully this screencasts gives you just an introduction to case studies. In the next screencast, I'll be talking about one-way data tables. The data tables are a tool that's built into Excel that makes it easy to do one-way and two-way case studies.
