Figure 3. GPA study data. You can download the GPAs spreadsheet and use it for the exercises. Step 1: Analyzing the Grades Sue thinks that students are more motivated to do well in their major courses than in coursework that is not in their majors. To do this she would like to find the average of all the students' GPAs in their major courses and in their coursework overall. To do this she ll use the Average function and will enter the range of cells to compute. Conveniently, the function to compute the average of a range of cells is called "Average." Use the average function to compute the average of all cells (except the header) in Column D. Put this in Cell D102. To do this, do this: .Click on cell D102 to select it, and then click on fr in the function bar. Enter "average" where the insert function popup box asks you to enter a brief description of the function you'd like to add. You'll see AVERAGE appear as the most highly recommended function. Just below the function selection you'll see a description of what your function will do: AVERAGE (numberl, number2, ...) Returns the average (arithmetic mean) of its arguments, which can be numbers or names, arrays, or references that contain numbers Excel 5 Page4 Double-click on AVERAGE and another window will pop up with default values as Number 1. It will assume that you want the average of the values in Colum D, so it enters "D2:D101" for you. Click OK. The average of the grades will be shown. " . Step 2: Average of the Overall GPA. Do the same thing for Column E. Which one is greater? Label Row 102 by entering A E102 and give them a green background color erage GPAs" in Cell A102. Bold the cells from A102 to Step 3: How Many Missing? Sue would like to see how many students don't have a grade for their major courses yet The hard way to do it would be to go through all 100 names and see which Major GPA cells are blank. Fortunately there's an easier way to do it. . Select Cell D103 and insert function. Enter something like "count blanks" in the search and see what comes up. Select the function that counts the number of blank cells and click OK. Note that the default value is not quite right. It doesn't include all cells in the column. Change the range to D2:DI0l click OK. . Label the row "Students with no major GPA." bold it, and color it red. Step 4: How Much Higher? Sue now knows the average for the students' major course GPA and their overall GPA. She wants to know how many students have a higher GPA in their major than overall. Sue can use one of the logical functions to highlight the students where their major GPA is higher than their overall GPA. Logical functions return TRUE or FALSE based on the result of the function. A common logical function is IF. -IF has three arguments and it looks like this: -IF (logical test, if_True, if False) Excel 5 Page5 use this to do a logical comparison of two The fist argument is "logical test." You can cells. For example, if you want to see if Cell Al is greater than Cell A2, then logical test would be "AI>A2." The result of the logical test will be either TRUE or FALSE this case, we want to put in how much higher the Major GPA is than Overall GPA. We can enter a formula there. When we have a formula inside a function we don't enter the, If Al is A2, we want to display how much greater Al is than A2. We'll enter "AI-A2" for the if_True argument. The second argument in the -IF is what you want to do if the result of the test is TRUE. In Finally, if Al is not greater than A2, we want to leave the cell blank. If we simply leave if False blank, then Excel will enter FALSE for all cases where A2 is greater than Al. We don 't want that. A good way to say we want an empty cell is to use . That is, two double quotes right next to each other with no space between. So, here's what it looks like. We want to c ompare AI and A2. If Al is greater than A2 then we want to display how much greater. If Al is less than or equal to A2 then we wart to have an empty cell. This is what it looks like IF (Al A2, Al-A2, You could also enter text for the second two arguments. For example, you could have said "Major Higher" and "Overall Higher" for if_True and if False. But what Sue wants is numbers, so that's what we'll do. Enter the -IF statement that will test each cell in Column D against each cell in Column E. Add these to Column F and label Column F as "Major Higher." Make sure that each value has one digit past the decimal point: 1.2, for example. Step 5: How Many Higher? Sue now wants to see how many students have a higher Major GPA than their Overall GPA. Clearly this would be a long, tedious search if she had value and count which ones were higher. That's too much work. to compare each value with each other She already has values in Column F for those students whose Major GPA is higher. Now all she has to do is count them up. You know the function to count blanks. She wants to count the cells that are nor blank. Find the function that counts cells that contain numbers. Enter that function in Cell F104 and label the row "Number of Students with Higher Major GPAs. Bold and color that row Gold Step 6: Min Sue wants to know what's the lowest Major GPA. To do this she uses a function. Use a function to find the minimum value in the Major GPA column and enter that in D105. Label and color that row . Part 2: Financial Functions Sue wants to buy a car. She does business with two banks and she has been approved for two different loans with different terms and different interest rates. Sue wants to decide which bank to use to finance her car purchase. She needs to build a spreadsheet so she can compare the two options. Sue wants to buy a car costing $15,000. Bank 1 offers Sue a 4-year loan at a 3.50% interest rate. Bank 2 offers Sue a 6-year loan at a 3.00% interest rate. Bank I has a short term but a higher rate. Bank 2 has the lower interest rate but a longer loan term. How much will each of the loans cost her? Step 7: Setup Set up the workbook as shown below in Figure 4. Fill in the data for each bank. Excel 5 Page 7 Figure 4. Sue's loan comparison spreadsheet The next throughout the life of the loan. It will show the total payment, how much of the payment goes to principal, how much of it is interest, and the remaining balance of the loan. step is to set up an amortization schedule. This is a table that shows each payment Step 8: Calculate the Payment Sue wants to know how much her payment will be on each of the loans. For that we can use the financial function PMT. You can see the structure of the function in Figure 5. Figure 5. PMT function. The PMT function calculates a payment for a loan based on constant payments and a constant interest rate. It has a few arguments, but some of them are optional. At the very least, PMT Excel 5 Page needs to know the interest rate, the total number of payments in the loan, and how much is left to pay So let's fill in the function. Click on Cell B8 so that's where the first payment will go. Click onfr and select financial and PMT. The interest rate is 3.5% yearly, but we're making monthly payments. We need to divide 3.5% by the number of periods per year to get the monthly interest rate. Cell B5 holds the yearly interest rate and Cell B4 holds the number of periods per year, so the value in Rate will be B5/ B4. Nper is the number of periods in the loan. It's a 4 year loan paid monthly so the number of periods will be Cell B3 (the number of years) B4. Finally, we need the present value of the loan. Sue hasn't made any payments yet so she owes the full value of the loan: $15,000 found in Cell B2. The filled function arguments is shown below in Figure 6. Note that we're using the fixed reference "$" in front of each of the cell numbers. We want to keep pointing to the same cell throughout the amortization table. So we use B$5, and so on. Rate 855/054 15000 Type Calculates the peynert foraloan based on contant-menndsco rternt tab. Fw is the future valut, or a cash balance you want to attain after the laxt payment as formula est- s3534) Figure 6. PMT function arguments. Excel 5 Page 9 Step 9. Caleulate the Principal Part Sue's first payment will be $335.34. But how much of this is principal and how much is interest? We can find the principal payment using the PPMIT function. This returns the payment on principal for the loan. We enter the values in PPMT the same way we entered the values in PMT with one difference. We need to know which payment number we on. As the loan progresses the amount paid on the principal will increase and the amount paid on the interest will Enter a 1 into cell AS to indicate it's the first payment decrease. Rate 855 e54 e 15000 Returms the payment on the principel for a given tment based on peniadc canstant payments and a nteret rate Py is the present value the total amount that a selies of luture Formula resu 52943 Figure 7. Principal payment When we click on OK we now know that $291.59 of the first payment goes to principal, the rest goes to interest on the loan. We can use other functions to calculate the interest portion and the remaining balance, but with the information we have already we can easily calculate those values. Interest will be the total payment minus the principal part. The balance is the loan amount minus the amount paid in principal. Excel 5 Page 10 The first line of the loan payment is shown in Figure 8. 1 35 34) ) 536-208 4 Figure 8. First line of the amortization table. The second line of the amortization table is created just like the first with one important difference. The loan balance needs to be calculated from the previous period's loan balance, not the balance at the beginning of the loan. So the formula for the second period is the previous balance-principal payment. Step 10: Calculate the Rest The rest is easy. Select A9 through E9. There's a small dark box at the corner of the selection area (see Figure 9). Click on that box and drag it down for the total number of payments (48). You'll see that the loan payment remains the same, the amount going to principal increases, the amount going to interest decreases, and the loan balance slowly decreases. Excel 5 Page 11 Figure 9. Second line of the amortization table Step 11: Calculate the Totals Now comes the fun part. Calculate the total of all the payments, the total paid on principal, and the total interest. If everything went well, you'll see that for the life of the loan Sue paid 16,096.32. The total principal was $15,000 (that's good, since that's how much she owed), and she paid $1,069.32 in interest. Label that line as shown in Figure 10. Excel 5 Page 12 , x- 51207) 615.2 Sees Gaus 34j (5323 82 0113 51 41 (5335 34 15327 62 157.72 523520 4 15135 34 15129 33 . 162 3 Figure 10. Totals Step 12: Bank 2 Now you enter a set of columns for Bank 2. You'll be doing same calculations you did for Bank I using Bank 2's data. Start in Cell G8. Enter the formulas for the first month and the second month like you did for Bank 1. Now copy the second month (from G9 to K9) down to all 72 months. Label the amortization tables Bank 1 and Bank 2. The first few lines are shown in Figure 11. Excel 5 Page 13 -35 37 38 39 42 44 45 46 47 49 30 S1 S2 53 54 55 -59 60 61 Figure 11. Bank 2, Top of the amortization table. Step 13: Totals Now calculate the totals for Bank 2. You'll see the result in Figure 12. You should see that Sue will pay $16,409.17. The total principal was $15,000 (once again, that's good, since that's how much she owed), and she paid $1.409.17 in interest. Excel 5 Page 14 Figure 12. Totals for Bank 1 and Bank So which loan is better? Sue will pay about $300 more for Bank 2's loan, but she will be paying about $108 less each month. This is due to the lower interest rate combined with the longer loan term. Step 14: Upload your spreadsheet and save your spreadsheet on your flash drive for backup and go to the quiz to complete this assigament Excel 5 Page 15 Figure 3. GPA study data. You can download the GPAs spreadsheet and use it for the exercises. Step 1: Analyzing the Grades Sue thinks that students are more motivated to do well in their major courses than in coursework that is not in their majors. To do this she would like to find the average of all the students' GPAs in their major courses and in their coursework overall. To do this she ll use the Average function and will enter the range of cells to compute. Conveniently, the function to compute the average of a range of cells is called "Average." Use the average function to compute the average of all cells (except the header) in Column D. Put this in Cell D102. To do this, do this: .Click on cell D102 to select it, and then click on fr in the function bar. Enter "average" where the insert function popup box asks you to enter a brief description of the function you'd like to add. You'll see AVERAGE appear as the most highly recommended function. Just below the function selection you'll see a description of what your function will do: AVERAGE (numberl, number2, ...) Returns the average (arithmetic mean) of its arguments, which can be numbers or names, arrays, or references that contain numbers Excel 5 Page4 Double-click on AVERAGE and another window will pop up with default values as Number 1. It will assume that you want the average of the values in Colum D, so it enters "D2:D101" for you. Click OK. The average of the grades will be shown. " . Step 2: Average of the Overall GPA. Do the same thing for Column E. Which one is greater? Label Row 102 by entering A E102 and give them a green background color erage GPAs" in Cell A102. Bold the cells from A102 to Step 3: How Many Missing? Sue would like to see how many students don't have a grade for their major courses yet The hard way to do it would be to go through all 100 names and see which Major GPA cells are blank. Fortunately there's an easier way to do it. . Select Cell D103 and insert function. Enter something like "count blanks" in the search and see what comes up. Select the function that counts the number of blank cells and click OK. Note that the default value is not quite right. It doesn't include all cells in the column. Change the range to D2:DI0l click OK. . Label the row "Students with no major GPA." bold it, and color it red. Step 4: How Much Higher? Sue now knows the average for the students' major course GPA and their overall GPA. She wants to know how many students have a higher GPA in their major than overall. Sue can use one of the logical functions to highlight the students where their major GPA is higher than their overall GPA. Logical functions return TRUE or FALSE based on the result of the function. A common logical function is IF. -IF has three arguments and it looks like this: -IF (logical test, if_True, if False) Excel 5 Page5 use this to do a logical comparison of two The fist argument is "logical test." You can cells. For example, if you want to see if Cell Al is greater than Cell A2, then logical test would be "AI>A2." The result of the logical test will be either TRUE or FALSE this case, we want to put in how much higher the Major GPA is than Overall GPA. We can enter a formula there. When we have a formula inside a function we don't enter the, If Al is A2, we want to display how much greater Al is than A2. We'll enter "AI-A2" for the if_True argument. The second argument in the -IF is what you want to do if the result of the test is TRUE. In Finally, if Al is not greater than A2, we want to leave the cell blank. If we simply leave if False blank, then Excel will enter FALSE for all cases where A2 is greater than Al. We don 't want that. A good way to say we want an empty cell is to use . That is, two double quotes right next to each other with no space between. So, here's what it looks like. We want to c ompare AI and A2. If Al is greater than A2 then we want to display how much greater. If Al is less than or equal to A2 then we wart to have an empty cell. This is what it looks like IF (Al A2, Al-A2, You could also enter text for the second two arguments. For example, you could have said "Major Higher" and "Overall Higher" for if_True and if False. But what Sue wants is numbers, so that's what we'll do. Enter the -IF statement that will test each cell in Column D against each cell in Column E. Add these to Column F and label Column F as "Major Higher." Make sure that each value has one digit past the decimal point: 1.2, for example. Step 5: How Many Higher? Sue now wants to see how many students have a higher Major GPA than their Overall GPA. Clearly this would be a long, tedious search if she had value and count which ones were higher. That's too much work. to compare each value with each other She already has values in Column F for those students whose Major GPA is higher. Now all she has to do is count them up. You know the function to count blanks. She wants to count the cells that are nor blank. Find the function that counts cells that contain numbers. Enter that function in Cell F104 and label the row "Number of Students with Higher Major GPAs. Bold and color that row Gold Step 6: Min Sue wants to know what's the lowest Major GPA. To do this she uses a function. Use a function to find the minimum value in the Major GPA column and enter that in D105. Label and color that row . Part 2: Financial Functions Sue wants to buy a car. She does business with two banks and she has been approved for two different loans with different terms and different interest rates. Sue wants to decide which bank to use to finance her car purchase. She needs to build a spreadsheet so she can compare the two options. Sue wants to buy a car costing $15,000. Bank 1 offers Sue a 4-year loan at a 3.50% interest rate. Bank 2 offers Sue a 6-year loan at a 3.00% interest rate. Bank I has a short term but a higher rate. Bank 2 has the lower interest rate but a longer loan term. How much will each of the loans cost her? Step 7: Setup Set up the workbook as shown below in Figure 4. Fill in the data for each bank. Excel 5 Page 7 Figure 4. Sue's loan comparison spreadsheet The next throughout the life of the loan. It will show the total payment, how much of the payment goes to principal, how much of it is interest, and the remaining balance of the loan. step is to set up an amortization schedule. This is a table that shows each payment Step 8: Calculate the Payment Sue wants to know how much her payment will be on each of the loans. For that we can use the financial function PMT. You can see the structure of the function in Figure 5. Figure 5. PMT function. The PMT function calculates a payment for a loan based on constant payments and a constant interest rate. It has a few arguments, but some of them are optional. At the very least, PMT Excel 5 Page needs to know the interest rate, the total number of payments in the loan, and how much is left to pay So let's fill in the function. Click on Cell B8 so that's where the first payment will go. Click onfr and select financial and PMT. The interest rate is 3.5% yearly, but we're making monthly payments. We need to divide 3.5% by the number of periods per year to get the monthly interest rate. Cell B5 holds the yearly interest rate and Cell B4 holds the number of periods per year, so the value in Rate will be B5/ B4. Nper is the number of periods in the loan. It's a 4 year loan paid monthly so the number of periods will be Cell B3 (the number of years) B4. Finally, we need the present value of the loan. Sue hasn't made any payments yet so she owes the full value of the loan: $15,000 found in Cell B2. The filled function arguments is shown below in Figure 6. Note that we're using the fixed reference "$" in front of each of the cell numbers. We want to keep pointing to the same cell throughout the amortization table. So we use B$5, and so on. Rate 855/054 15000 Type Calculates the peynert foraloan based on contant-menndsco rternt tab. Fw is the future valut, or a cash balance you want to attain after the laxt payment as formula est- s3534) Figure 6. PMT function arguments. Excel 5 Page 9 Step 9. Caleulate the Principal Part Sue's first payment will be $335.34. But how much of this is principal and how much is interest? We can find the principal payment using the PPMIT function. This returns the payment on principal for the loan. We enter the values in PPMT the same way we entered the values in PMT with one difference. We need to know which payment number we on. As the loan progresses the amount paid on the principal will increase and the amount paid on the interest will Enter a 1 into cell AS to indicate it's the first payment decrease. Rate 855 e54 e 15000 Returms the payment on the principel for a given tment based on peniadc canstant payments and a nteret rate Py is the present value the total amount that a selies of luture Formula resu 52943 Figure 7. Principal payment When we click on OK we now know that $291.59 of the first payment goes to principal, the rest goes to interest on the loan. We can use other functions to calculate the interest portion and the remaining balance, but with the information we have already we can easily calculate those values. Interest will be the total payment minus the principal part. The balance is the loan amount minus the amount paid in principal. Excel 5 Page 10 The first line of the loan payment is shown in Figure 8. 1 35 34) ) 536-208 4 Figure 8. First line of the amortization table. The second line of the amortization table is created just like the first with one important difference. The loan balance needs to be calculated from the previous period's loan balance, not the balance at the beginning of the loan. So the formula for the second period is the previous balance-principal payment. Step 10: Calculate the Rest The rest is easy. Select A9 through E9. There's a small dark box at the corner of the selection area (see Figure 9). Click on that box and drag it down for the total number of payments (48). You'll see that the loan payment remains the same, the amount going to principal increases, the amount going to interest decreases, and the loan balance slowly decreases. Excel 5 Page 11 Figure 9. Second line of the amortization table Step 11: Calculate the Totals Now comes the fun part. Calculate the total of all the payments, the total paid on principal, and the total interest. If everything went well, you'll see that for the life of the loan Sue paid 16,096.32. The total principal was $15,000 (that's good, since that's how much she owed), and she paid $1,069.32 in interest. Label that line as shown in Figure 10. Excel 5 Page 12 , x- 51207) 615.2 Sees Gaus 34j (5323 82 0113 51 41 (5335 34 15327 62 157.72 523520 4 15135 34 15129 33 . 162 3 Figure 10. Totals Step 12: Bank 2 Now you enter a set of columns for Bank 2. You'll be doing same calculations you did for Bank I using Bank 2's data. Start in Cell G8. Enter the formulas for the first month and the second month like you did for Bank 1. Now copy the second month (from G9 to K9) down to all 72 months. Label the amortization tables Bank 1 and Bank 2. The first few lines are shown in Figure 11. Excel 5 Page 13 -35 37 38 39 42 44 45 46 47 49 30 S1 S2 53 54 55 -59 60 61 Figure 11. Bank 2, Top of the amortization table. Step 13: Totals Now calculate the totals for Bank 2. You'll see the result in Figure 12. You should see that Sue will pay $16,409.17. The total principal was $15,000 (once again, that's good, since that's how much she owed), and she paid $1.409.17 in interest. Excel 5 Page 14 Figure 12. Totals for Bank 1 and Bank So which loan is better? Sue will pay about $300 more for Bank 2's loan, but she will be paying about $108 less each month. This is due to the lower interest rate combined with the longer loan term. Step 14: Upload your spreadsheet and save your spreadsheet on your flash drive for backup and go to the quiz to complete this assigament Excel 5 Page 15