Spreadsheet 2

1. Spreadsheet 2 Hockey Graphs Averages and other Formulas Calculating Interest

2. Find Some Hockey Stats – 1 Team Find at least 8 different stats using a single team. Create a graph like the one above with the team of your choice. You do not need to use these exact stats.

3. Creating a Graph • Simply highlight the information you wish to use. In this case, all of it.

4. Next steps Go to the Insert tab and click on column. Select 2-D Column

5. You should have something like this. Move the graph down below and make it bigger.

6. Adjusting the X-Axis Double click on the X-axis on the graph

7. Next Steps • Adjust the number under Major Unit to Fixed and 10 . See what happens.

8. It is now easier to read because there are more numbers, but we can focus on individual statistics to make things easier.

9. One Set of Data • This time select only ONE set of data and under Insert select a 2-D Bar Graph for this one set of data. You will have to select the player names as well as. This time, adjust the Axis Major Unit to “1” so that the axis goes 1, 2, 3, 4, 5, 6, etc. instead of skipping numbers.

10. Two Players • Select the info for two players: • Exclude Height and Weight for this selection if it is part of your data.

11. Select the Area Graph

12. Put all 3 graphs below the table • Voila! You have finished the 1st assignment • Call this assignment Spreadsheet2Assign1 and leave it in your INFO folder.

13. Project 1- Comparing With Formulas • Open up your last spreadsheet and take the data from your Hockey table. • Copy and paste it into a new document • You do not need the graphs • Find another team and put the same info below for that team. Do not worry about having duplicate headings (Eg. Points, Goals, Hits)

14. So now we have our two teams, but we can no longer see what the data represents at the top when we scroll down. Hmmmm.

15. Freeze Da Panes • Click on the top left corner of your spreadsheet on the line below your headings. In the Tab section, go to View and select Freeze Panes

16. Now Look! • Your info stays up at the top, even when you scroll down

17. Now we will compare this data • We will be using these formulas. The cells used in the formula are just examples.

18. How to Insert Formulas • Here is an example with some data. I want to add it all together, so I will use the Sum formula. I decide where I want the sum to go, and press the Equals sign on the keyboard. Notice that there is an = sign up at The top. When we put in our formula, It will also display here when you click on that cell

19. Putting in the Formula • I simply find which cells (the range) I need to include and put them in the Sum formula. In this case, the cells are C8 and C13. =Sum(C8:C13) The colon (:) means we are adding from C8 to C13 and everything in between the two.

20. Voila!. Press Enter to see the result.

21. Comparing the Teams • You should now have your two hockey teams in your spreadsheet • Down below your statistics, put this for each team:

22. Put in some borders

23. Average, Minimum and Maximum • So now we will calculate these 3 things • We want to find out the Average for each category. To do this, we will do it to only one category first

24. First, click in the Average row where you want your answer and formula to go. • Find the range of cells you need to put in the formula • I am using cells D8 to D16. My formula would be Average=(D8:D16) Cells used Formula

25. Finding the Rest of the Averages • Put your cursor in the bottom corner of the cell where you entered you formula. Click and drag it across the Average row. The formula will automatically copy and adjust to the other columns. Click, hold and drag across

26. Too many decimal places • Highlight all the cells in the Average row. Right click and do this: Decimals set to Zero.

27. Continuing On • Now finish the last two row, Minimum and Maximum and the other Average column for both teams. • Use the formulas that were listed back a few slides.

28. Down Below • Include a sum of one of the categories for each team. Use a Sum formula

29. Total Number of Players • Underneath the Total Points section, include a section of the number of players for each team. Use the Count formula. You will need to select the names of the players to add up how many there are.

30. Calculating Interest and Loans • Find a car you would like to buy • Look for how much it costs and what the payments for it would be monthly • But FIRST, we will do this exercise together

31. Recreate This Table

32. Notice That… • When speaking about terms for this assignment, Rate is the per period interest rate and NPer is the total number of periods (12 payments a year). In this case, as shown in the picture, we calculate the Rate with B4/B5 (0.5625% per month), and NPer is B3*B5 (360 months). Principalis entered as -B2 (-200,000, negative because we want the answer to be a positive number). You can see that the monthly payment is $1,297.20.

33. . So, we now need to separate that payment into its interest and principal components. • When you take out a loan, you are paying two things: Interest and Principle interest. Principle Interest is the actual amount of the loan that you borrowed. Interest is the interest added onto the loan that you also pay each payment. Unfortunately, you will mostly pay regular Interest first before anything goes into your Principle. Bummer.

34. Deep stuff, dude • Using these formulas, we can see that the interest component of the first payment would be: • Interest in 1st Payment = 200,000 x 0.005625 = $1,125 • and the principal payment is: • Principal in 1st Payment = 1,297.20 - 1,125 = $172.20 • Note that the sum of the interest and principal is the amount of the total payment: • 1,125 + 172.20 = $1,297.20 • That is the case for every single payment over the life of the loan. However, as payments are made the principal balance will decline. This, in turn, means that the interest payment will be lower, and the principal payment will be higher (because the total payment amount is constant), for each successive payment. See, over time the interest goes down and you actually put more money into it

35. Using Built-In Functions • We've now seen how the principal and interest components of each payment are calculated. However, you can use a couple of built-in functions to do the math for you. These functions also make it easier to calculate the principal and/or interest for any arbitrary payment. • The two functions from the Finance menu that we are going to use are the IPMT (interest payment) and thePPMT (principal payment) functions. These functions calculate the amount of interest or principal paid for any given payment.

36. Know the Lingo • So, using our data from above, we can calculate the amount of interest in the first payment with: • =IPMT(B4/B5,1,B3*B5,-B2) • and we get $1,125. The amount of the principal in the first payment is: • =PPMT(B4/B5,1,B3*B5,-B2) • which gives $172.20. Those answers match exactly the ones that we calculated manually above. Note that in both functions, we specified that Per (the payment period) is 1 for the first payment. We would specify 2 for the second payment, and so on. Obviously, we will use a cell reference in our amortization table. • Excel does not have a built-in function to calculate the remaining balance after a payment, but we can do that easily enough with a simple formula. Simply take the beginning balance minus the principal paid in the first payment and you will find that the remaining balance after one payment is $199,827.80: • Principal Balance After 1st Payment = 200,000 - 172.20 = $199,827.80

37. Creating an Amortization Schedule • We will create a chart to keep track of our Mortgage. Amortization means the growth of the money put into the loan. Recreate this on the next slide. You already have the top:

39. Next… • The first thing that we want to do is to set up the table starting with the labels in A8:E8. • Now, in column A we want a series of numbers from 0 to 360 (the maximum number of payments that we are going to allow). • To create this series, select A9 and then choose Edit » Fill » Series from the menus. This will launch the Series dialog box. Fill it in exactly as shown, and then click the Ok button.

40. More Formulas • At this point, we are ready to fill in the formulas. Start with the beginning principal in E9 with the formula: =B2. That will link it to the principal balance as given in the input area. Now, select B10 and enter the formula: =PMT(B$4/B$5,B$3*B$5,-B$2), and you will see that the monthly payment is $1,297.20 as shown above. • In C10 we will calculate the interest portion of the first payment with the formula: =IPMT(B$4/B$5,A10,B$3*B$5,-B$2). The principal portion of the payment can be calculated, in D10 with: =PPMT(B$4/B$5,A10,B$3*B$5,-B$2). Finally, we calculate the remaining balance in E10 with the formula: =E9-D10.

41. All the Way • Check your results against those shown above, being very careful to type the formulas exactly as shown (the $ are important because they freeze the cell references so that they don't change when we copy the formulas down). • Once your results in row 10 match the picture, copy the formulas all the way down to the end of the table in row 369. • (Note: The easiest way to do this is to select B10:E10 and then double-click the Auto Fill handle in the lower right corner of the selection. This will copy the formulas to the end of the current range, which is defined by the last data point in column A.)

42. Graphs!!!!! • The final enhancement that I have made is to create a chart that shows the remaining balance declining over time. Basically, all you need to do is to select A8:A369 and E8:E369 and then create an XY Scatter chart. I've fancied it up a little bit with a live chart title and a scroll bar, but I'll leave those features to another tutorial. The final result is shown below.

43. Here it is…

44. Assignment • Using the same template you have just made, find a new car to buy. Find out how much it costs to finance it and what its payments are. Plug this info into a new version of your previous assignment. We will make our own amortization schedule for the car. • Also create a graph for this one as well.