Transcript Spreadsheet 2 - Holy Family Catholic Regional Division No. 37

Spreadsheet 2
• Hockey Graphs
• Averages and other Formulas
• Calculating Interest
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.
Creating a Graph
• Simply highlight the information you wish to
use. In this case, all of it.
Next steps
Go to the Insert tab and click on column. Select 2-D Column
• You should have something like this. Move the
graph down below and make it bigger.
Adjusting the X-Axis
Double click on the X-axis on the graph
Next Steps
• Adjust the number under Major Unit to Fixed
and 10 . See what happens.
• It is now easier to read because there are
more numbers, but we can focus on individual
statistics to make things easier.
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.
Two Players
• Select the info for two players:
• Exclude Height and Weight for this selection if
it is part of your data.
Select the Area Graph
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.
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)
• 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.
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
Now Look!
• Your info stays up at the top, even when you
scroll down
Now we will compare this data
• We will be using these formulas. The cells used in the formula
are just examples.
Formula
What it Does
=Average(A1:A5)
Finds the average
=Min(A1:A5)
Smallest number
=Max(A1:A5)
Largest number
=Sum(A1:A5)
Adds everything up
=Count(A1:A5)
Count
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
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.
Voila!. Press Enter to see the result.
Comparing the Teams
• You should now have your two hockey teams in your spreadsheet
• Down below your statistics, put this for each team:
Put in some borders
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
• 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
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
Too many decimal places
• Highlight all the cells in the Average row. Right
click and do this: Decimals set to Zero.
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.
Down Below
• Include a sum of one of the categories for
each team. Use a Sum formula
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.
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
Recreate This Table
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). Principal is 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.
. 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.
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
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.
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
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:
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.
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.
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.)
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.
Here it is…
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.