IntroductionWelcome| 00:00 | (Music playing.)
| | 00:03 | Hi! I'm Curt Frye and I'd like to
welcome you to Excel 2007: Financial Analysis.
| | 00:08 | In this course, I'll identify the most
essential calculations and formulas that
| | 00:12 | you will need to perform common
financial analysis tasks in Excel.
| | 00:16 | We'll begin by looking at the best
way to set up your worksheets and to
| | 00:19 | troubleshoot the common mistakes.
| | 00:20 | I'll show you how to use special tools
in Excel such as Pivot Tables and Excel
| | 00:24 | tables to organize and analyze data.
| | 00:27 | We'll get you comfortable reading
financial statements, then look at techniques
| | 00:30 | for comparing data from the
financial statements of different companies.
| | 00:34 | I'll show you how to implement several
ratio and interest rate calculations,
| | 00:37 | as well as look at several techniques
for predicting future performance and
| | 00:41 | analyzing bond investments.
| | 00:43 | Financial analysis in Excel requires a
basic knowledge of implementing formulas
| | 00:46 | and formatting data in an Excel worksheet.
| | 00:48 | If you're not familiar with these areas,
you might want to check out Excel 2007
| | 00:52 | Essential Training before watching this course.
| | 00:55 | As a Microsoft Most Valuable Professional,
my goal in this course is to help you
| | 00:59 | unlock Excel's ability to support
your work as a financial analyst.
| | 01:03 | Now, let's get started with
Excel 2007: Financial Analysis.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you are a premium member of the
Lynda.com Online Training Library,
| | 00:04 | or if you're watching this tutorial on a
DVD-ROM, you have access to the exercise
| | 00:09 | files used throughout this title.
| | 00:11 | You can find the files in the Exercise
Files folder, are divided by chapter.
| | 00:15 | If you double-click to open the folder,
you'll find all the files used for
| | 00:19 | a particular chapter.
| | 00:20 | If you are a monthly subscriber or
annual subscriber to Lynda.com, you don't
| | 00:24 | have access to the exercise files, but you can
follow along from scratch or use your own assets.
| | 00:29 | Let's get started.
| | Collapse this transcript |
| Disclaimer| 00:00 | This course, Excel 2007: Financial Analysis,
is designed to be a guide to implementing financial
| | 00:06 | techniques and equations
typically used in the United States.
| | 00:09 | Some of these techniques might be
different in other parts of the world.
| | 00:12 | Nothing in this course should be
considered investment or financial advice.
| | 00:16 | This course should not be considered
a substitute to consultation with a
| | 00:19 | licensed financial advisor.
| | 00:21 | If you would like financial planning or investment
advice, please consult your certified financial planner.
| | Collapse this transcript |
|
|
1. Designing a Financial WorksheetSeparating inputs and formulas| 00:01 | Microsoft Excel is such a
versatile tool that it's easy to create
| | 00:04 | complex worksheets.
| | 00:05 | Sometimes, those worksheets can be so
complex that is difficult to find the
| | 00:08 | data you're looking for.
| | 00:10 | In this lesson, I'll demonstrate some
strategies to make your worksheets easier
| | 00:13 | to understand and maintain.
| | 00:15 | One of the first techniques you should
use is limiting each worksheet to the
| | 00:19 | data you absolutely need for the
calculations on that worksheet.
| | 00:22 | If the data isn't part of the
calculation, it shouldn't be on the worksheet.
| | 00:26 | Also, you'll find that it's very easy
to create crowded worksheets that make it
| | 00:30 | hard to find specific items.
| | 00:31 | For example, on this worksheet, I have
information for two loans, a construction loan
| | 00:35 | where a company is building a
manufacturing facility, and also the
| | 00:40 | automation loan, which is to build the
equipment that's going to part of the
| | 00:44 | construction facility.
| | 00:45 | The information is all
thrown together. It's scrambled.
| | 00:48 | It's almost impossible to tell
which data goes with which loan.
| | 00:51 | Here's one example, just find a
method that works for you and stay
| | 00:55 | consistent with it.
| | 00:56 | What I did was divide my worksheet
into two areas, one for the Construction Loan,
| | 01:00 | one for the Automation Loan, and
within each of those areas, I have
| | 01:04 | the loan terms, the Principal,
the Rate, and the Term.
| | 01:08 | The term is expressed in years.
| | 01:10 | So, I have those separate from the
Monthly Payment and you'll notice that
| | 01:14 | I followed the same structure
here with the Automation Loan.
| | 01:18 | If you like, you can also use color
to distinguish between formula inputs
| | 01:23 | and formula outputs.
| | 01:25 | So, in this case we have
the Principal, Rate, and Term.
| | 01:29 | If I wanted to highlight those,
I could do it in yellow so they stand out,
| | 01:34 | I'll do the same over here.
| | 01:38 | The Monthly Payment is a formula output.
| | 01:42 | So, I'll select those cells and I'll
just make them a neutral gray color.
| | 01:47 | So it's easy to read it as an answer
for the formula, and it's also easy to
| | 01:51 | distinguish it from the formula's inputs.
| | 01:54 | If you follow these design practices,
you'll create worksheets that are
| | 01:57 | much easier for you and your co-workers to
understand and work with for years to come.
| | Collapse this transcript |
| Avoiding common mistakes| 00:01 | It's amazing how many errors can
creep into even the simplest worksheets.
| | 00:04 | Most often the errors come from
actions that seem very logical when you take them,
| | 00:08 | but result in formulas
that give you the wrong answers.
| | 00:10 | Something you should avoid is
using numbers as column headers.
| | 00:14 | Instead, qualify the values with text to
specify the type of data you're working with.
| | 00:18 | For example, use FY 2009 for Fiscal
Year 2009, and CY 2009 for Calendar Year
| | 00:26 | 2009, and I'll show you
why that could be a problem.
| | 00:30 | In this worksheet, we have a Sales
Summary by Region, North America,
| | 00:33 | South America and so on, and
we have the sales numbers here.
| | 00:37 | But let's see what happens if I
create an AutoSum formula in cell B10.
| | 00:41 | So, the cell's clicked, I go up to
the Home tab, click the AutoSum button.
| | 00:45 | Let's take a look at the
formula Excel is trying to create.
| | 00:50 | You'll notice that it includes the year.
| | 00:53 | That's because 2007 is a number, which
means Excel cannot distinguish it from
| | 00:57 | the other values in the column and
it tries to include it in the formula.
| | 01:01 | However, if we change the value in
that cell to CY 2007, I'll delete the
| | 01:10 | formula and create a new AutoSum,
because CY starts with text, Excel does not
| | 01:16 | recognize it as a number, and it
correctly creates the AutoSum formula with
| | 01:20 | the cells B5 through B9.
| | 01:22 | Press Enter and there
you have the correct result.
| | 01:25 | Another error to avoid is
hard-coding values in formulas.
| | 01:30 | Move to this worksheet and you see that
I have a loan, Principal of $10 million,
| | 01:34 | Interest Rate of 6% and a Term of 12 years.
| | 01:37 | Here's the formula that
calculates the Monthly Payment.
| | 01:41 | I'll click that so you can see
the formula in the Formula bar.
| | 01:44 | Now, you'll notice that I have the
interest rate of 6% hard-coded into the formula.
| | 01:50 | Instead of using a cell reference,
we have the cell here B4 with the interest rate.
| | 01:55 | So, if I press Escape to get out of
formula editing mode and change the value
| | 02:00 | in B4 to 5%, the payment doesn't change,
even though the interest rate changed.
| | 02:06 | It should have gone down, because you're
not paying as much interest in each payment.
| | 02:10 | So what I should do, I'll change this
back to 6%, is edit the formula so that
| | 02:16 | it refers to the cell that
contains the interest rate.
| | 02:18 | So, I'll change the reference there to
cell B4 and both of the other arguments,
| | 02:25 | the number of periods, and the loan
principal already used Cell References,
| | 02:29 | so I don't need to change them.
| | 02:31 | I'll press Enter, there we have the value.
| | 02:34 | Now when I change the interest
rate to 5%, the payment will change.
| | 02:39 | One final common mistake that Excel
users make is creating formulas that cannot
| | 02:44 | be copied accurately.
| | 02:46 | They change Cell References when you move them.
| | 02:48 | In this worksheet, I have calculations
for monthly payments for two separate loans.
| | 02:53 | One for a Factory Update and
one for Storage Construction.
| | 02:58 | This is an example of bad design.
| | 03:00 | I have the principal and the term for
each loan listed separately and instead
| | 03:04 | of having the rate in a separate place
entirely, I should have it here with the
| | 03:08 | principal and the term for the two loans.
| | 03:10 | It's just an example of good
design and you'll see why in a second.
| | 03:15 | So, here I have the formula for the
Payment for the Factory Update Loan and
| | 03:19 | everything looks good.
| | 03:20 | What I'll do now is copy that
formula so that it works for the
| | 03:27 | Storage Construction Loan.
| | 03:28 | Now, there is no obvious error, but
if you were to run these calculations
| | 03:33 | you would find that there is actually an
interest rate of 0% being applied to this loan.
| | 03:39 | You can determine that by going up to the
formula bar, where we have Payment and then D3.
| | 03:46 | Now, cell D3 is the cell from which
Excel is attempting to draw the interest rate
| | 03:51 | for this loan, but
you'll note that it's empty.
| | 03:54 | The interest rate that
you want is over in cell B3.
| | 03:58 | The mistake happened because when you
copy a formula from one cell to another,
| | 04:03 | if you don't do anything special to
these Cell References, Excel figures, oh!
| | 04:07 | you want me to shift all the Cell
References as far up-and-down or
| | 04:11 | left-and-right in the worksheet as you
shifted the formula when you copied and pasted it.
| | 04:17 | So, if you compare the two formulas,
here we have D3, D8, and D7, and here,
| | 04:22 | we have B3, B8, and B7.
| | 04:25 | The change occurred when we
copied the formula from here to here.
| | 04:29 | So, how do you keep cell
B3 constant in this formula?
| | 04:34 | That's again where the interest rate is.
| | 04:36 | I'll delete this to show you
what happens when you copy it.
| | 04:41 | What you need to do is click on the
formula argument with the cell that you want
| | 04:46 | to remain constant and press F4.
| | 04:49 | Pressing F4 adds dollar signs in
front of the column and the row.
| | 04:55 | What the $ means is that the
reference which was a relative reference and
| | 04:58 | capable of changing has now
become an absolute reference.
| | 05:02 | That means it will not change.
| | 05:04 | So in this case, both the row
and the column will stay the same.
| | 05:07 | If you press F4 again, now the
column can change but the row can't.
| | 05:11 | If you press it again, the column
will not change but the row can.
| | 05:15 | If you press it again, you're back to
the relative reference at the start, where
| | 05:18 | both the column and the row can change.
| | 05:20 | So, pressing F4 to edit the Cell
Reference, so it will not change.
| | 05:25 | Press Enter to keep the formula and
now when we copy it, you get what turns out
| | 05:31 | to be the right result.
| | 05:32 | By following these strategies you'll
avoid the most common spreadsheet errors
| | 05:35 | and be well on your way to
creating usable and accurate worksheets.
| | Collapse this transcript |
| Tracing formula precedents and dependents| 00:00 | Excel includes a number of very useful
tools you can use to ensure your formulas
| | 00:04 | incorporate the proper values.
| | 00:06 | One of those tools give you the
ability to determine which cells are used in
| | 00:09 | which worksheet formulas.
| | 00:11 | Cells can play two roles in a formula,
as a precedent or as a dependent.
| | 00:16 | A cell that is precedent supplies its
value to the formula in another cell.
| | 00:20 | A dependent is a cell that
contains a formula and has other cells
| | 00:24 | providing values to it.
| | 00:25 | Displaying a cell's precedents indicates
which other cells provide the formula's inputs.
| | 00:30 | Now you can see the two cells here, B3
and B4 in this simple example, but say
| | 00:35 | you had a more complex formula,
| | 00:37 | you can still use this technique to
determine where the values are coming from.
| | 00:40 | So, to display precedents, you go to
the Formulas tab and then in the Formula
| | 00:46 | Auditing group, you click Trace Precedents.
| | 00:50 | You'll see that we have cells B3 and B4 as
advertised that are highlighted with a dot.
| | 00:55 | There's a line with an arrow and it
goes to the cell that contains the formula
| | 00:59 | that uses those two values.
| | 01:00 | So that is how you trace dependents.
| | 01:02 | When you're done doing the tracing, you
can go back up to the Formula Auditing group,
| | 01:06 | click Remove Arrows
and the arrows disappear.
| | 01:10 | So let's see how it works for dependents.
| | 01:12 | On this worksheet, we have Bonds, Notes
and Loan Interest and we have a Total.
| | 01:16 | This is a company's monthly
debt service for the year 2009.
| | 01:20 | If you want to see which cells use the
value from cell B3 as part of its formula,
| | 01:25 | you can click cell B3, again, on the
Formulas tab and in the Formula Auditing group,
| | 01:30 | click Trace Dependents.
| | 01:33 | You'll see that once again you have the
blue circle. The line draws an arrow to
| | 01:37 | the cell that uses the
value as part of it's formula.
| | 01:39 | To remove the arrow, you go up to
the ribbon and click Remove Arrows.
| | 01:44 | Now tracing precedents and dependents also
works for values that are on other worksheets.
| | 01:48 | So, for example, here in cell B5,
I have a loan interest and that is on the
| | 01:53 | Interest worksheet, which is
the one we looked at previously.
| | 01:57 | If you want to see which cell
contains that value, you would select it,
| | 02:04 | click Trace Precedents.
| | 02:06 | Tracer arrows can also show you when
values have been drawn from other worksheets.
| | 02:10 | So, for example, on this worksheet, we
have cell B5, which draws its value from
| | 02:15 | the Interest worksheet.
| | 02:17 | So, if you click cell B5 and then go
up to Trace Precedents to see where it
| | 02:21 | draws its value from, Excel shows you
this indicator, which tells you that the
| | 02:25 | value is drawn from another worksheet.
| | 02:27 | It can also be from another workbook
entirely, but in this case it's just from
| | 02:30 | another worksheet in the same workbook.
| | 02:32 | So, to display that value, you click
the line, the Go To dialog box appears and
| | 02:37 | you can select the location to which
you want to go, click OK, and it takes you
| | 02:43 | to the Interest worksheet, cell B6,
which is where the value came from.
| | 02:47 | Identifying a cell's precedents and
dependents will help you discover and
| | 02:49 | correct many formula inaccuracies.
| | Collapse this transcript |
| Evaluating Excel formulas step by step| 00:00 | Sometimes you'll look over a formula,
verify that it uses the proper inputs, but
| | 00:05 | still find that it returns an incorrect result.
| | 00:07 | If that's the case, you can move to the
formula, with one calculation at a time
| | 00:10 | to identify the problem.
| | 00:12 | Some of the tactics you can use are setting
up the formula with inputs that you know
| | 00:16 | will generate a specific result.
| | 00:18 | If you don't get the result you expect, then
you know there is a problem with the formula.
| | 00:22 | Then you can step through the formula
with one calculation at a time, I'll show
| | 00:25 | you how in a moment, attempt to identify
at which point the formula generates an
| | 00:29 | incorrect result while you are
stepping through it and once you've identified
| | 00:32 | the problem, edit the formula and step
through it again to ensure that you're
| | 00:36 | getting the right result for the right reasons.
| | 00:39 | As an example of a formula that you
might evaluate, take a look at the formula
| | 00:43 | in cell B7, which is Monthly Payment.
| | 00:45 | Now we have a $245,000 loan with a 6.9%
interest rate being paid off over three years.
| | 00:51 | There is no way that you would
have a Monthly Payment of $0.
| | 00:54 | It just doesn't happen.
| | 00:55 | So, how do you find the error in the formula?
| | 00:58 | To do that, you can go to the
Evaluate Formula dialog box.
| | 01:01 | So, I click the cell that contains the
formula I want to evaluate and then on
| | 01:06 | the Formulas tab, in the Formula
Auditing group, click Evaluate Formula.
| | 01:10 | The Evaluate Formula dialog box
displays the formula and allows you to go
| | 01:14 | through and determine the value step by step.
| | 01:18 | So, we'll start out by clicking
Evaluate and it pulled the value from B4,
| | 01:22 | which is 6.9% or 0.069.
| | 01:25 | Next step is to divide that by 12, with
the result you see here and when I click
| | 01:31 | Evaluate again, it will display the
number of years, which is in cell B5.
| | 01:37 | Click Evaluate again.
| | 01:38 | You get 36, which is
the number of payments.
| | 01:41 | You made 12 payments per year,
over three years, that's 36.
| | 01:44 | Then you have the Loan Principal
and here's where we see the error.
| | 01:48 | Instead of a Loan Principal here in
cell B3, I have $245,000, it's 0.
| | 01:54 | So, I have already found the error but
just to show you how it works, when you
| | 01:58 | evaluate the rest of the formula, I'll
click Evaluate again and you get a value of $0.
| | 02:03 | So, I'll click Close and edit this formula
| | 02:07 | so that the Principal is pulled from
cell B3 as opposed to cell B2. Press Return
| | 02:13 | and we get the right result.
| | 02:14 | I won't go through all the steps of
the formula again, but you could use the
| | 02:18 | Evaluate Formula dialog box to determine
that you were getting the proper result
| | 02:22 | for the right reasons.
| | 02:24 | Evaluating Excel formulas
is a powerful technique
| | 02:26 | you can use to verify that a
formula produces the correct result.
| | 02:30 | You can also use formula evaluation to
understand how a formula's logic works.
| | 02:34 | If you encounter a formula that uses a
function you haven't used before, take
| | 02:38 | the time to look at the function in the
Excel Help files and step through the
| | 02:41 | formula so you'll have a better idea of
why the formula is in the workbook and
| | 02:45 | how it derives its result.
| | Collapse this transcript |
|
|
2. Analyzing Cash Flows Using Excel Tables and Pivot TablesTracking income and expenses using an Excel table| 00:01 | In Excel 2003 Microsoft introduced the
list, which enabled you to manage tables
| | 00:05 | of data more efficiently.
| | 00:07 | In Excel 2007, these list objects
became Excel tables, which are much more
| | 00:12 | useful when performing
financial analysis in Excel.
| | 00:14 | Now, on this worksheet, you will see
that I have the beginnings of a data table,
| | 00:19 | where I have the date, category, detail,
income, expense and balance columns,
| | 00:24 | and then I have entries within those
columns and this is what you might think
| | 00:27 | of as an Excel table.
| | 00:29 | But an Excel table is actually a
specific object that you create within Excel,
| | 00:35 | and as opposed to this data, which is in
list format, the Excel table gives you
| | 00:39 | other capabilities that you can use to
analyze your data. It makes it much easier
| | 00:44 | to use and is a real
addition I think to Excel 2007.
| | 00:48 | So how do you create an Excel table?
| | 00:50 | Well, to do that, you click any cell
in the data that you want to include in
| | 00:53 | the table, and then on the Home tab,
click Format as table, and select a table style.
| | 01:00 | Excel indicates the data it
will include in the table.
| | 01:03 | That looks right, and then it says
that your table has headers. It does.
| | 01:07 | You have the date, category, detail and so on.
| | 01:10 | You can click OK, and it creates the table.
| | 01:13 | So here I have my starting balance of
$250,000, and the second row has rent that
| | 01:18 | I received for rent of unit L203 in the
sum of $2,500, and you can see here that
| | 01:26 | I have my running balance of $252,500,
and that includes the previous balance,
| | 01:32 | in cell F2, the income in cell
D3, and any expense in cell E3.
| | 01:38 | When I want to add a new row to the
table, I just click the last cell in
| | 01:41 | the last row, and press the Tab key, and
it gives me a new row to enter my data.
| | 01:45 | And you will also notice that Excel
extended the formula into the new row.
| | 01:50 | So if I were to receive another
rent payment, also on the 5th, for unit
| | 01:57 | L205, for the sum of $5000, then that
formula takes over, and that includes the income.
| | 02:05 | And just for completeness I'll show an
expense, say that I pay my mortgage on
| | 02:12 | the office building. So that is an
expense, call it $25,000, and the Excel table
| | 02:22 | updates my running balance.
| | 02:24 | Now if you want to find a total of the
values in a particular column, say for
| | 02:28 | example, you wanted to find the total
of all of your incomes in the table.
| | 02:33 | To do that, you can add what is
called a total row to the table.
| | 02:37 | So you'd go up to the Table Tools
Design tab that appears anytime you are
| | 02:43 | editing a table, and then in the Table
Style Options group, click Total Row,
| | 02:48 | and the total row appears
at the bottom of the table.
| | 02:51 | Now you will notice that we have a
total of all of your balances, which
| | 02:54 | doesn't make any sense.
| | 02:56 | So to get rid of it, you just click
the cell, click the down arrow, and
| | 02:59 | then select None.
| | 03:01 | So there is no summary there, but if
you want to get a total of all of your
| | 03:04 | income over a particular time,
you click that cell, click the down arrow, and
| | 03:09 | then select Sum, and that gives you the
total of $2500 and $5000, which is $7500.
| | 03:15 | You can do the same here for your
expenses, click Sum, and it gives you the sum
| | 03:19 | of all of your expenses.
| | 03:21 | Excel tables help you summarize your
data effectively and provide a base for
| | 03:24 | more advanced analysis using Pivot Tables,
which I'll show you in the next few lessons.
| | Collapse this transcript |
| Creating a Pivot Table from table data| 00:00 | Excel tables enable you to analyze
financial data in some interesting ways but
| | 00:04 | they're not as flexible or powerful
as another Excel tool, the pivot table.
| | 00:09 | A pivot table lets you rearrange a
data set on the fly so you can analyze it
| | 00:13 | from several different
perspectives with a minimum of effort.
| | 00:16 | To create a pivot table, your source
data must be laid out as a table with
| | 00:20 | column headers and no
extraneous data surrounding the table.
| | 00:24 | Excel tables are the best
source for pivot table data.
| | 00:26 | Now, if you do use a data source that's
not in Excel table, make sure the column
| | 00:31 | headers are formatted differently than the data.
| | 00:34 | What I like to do is format the cell so
that the contents are in bold and centered.
| | 00:39 | That way Excel has no problem
detecting that the column headers are separate
| | 00:44 | from the data that's in the table.
| | 00:47 | So how do you create a pivot table?
| | 00:48 | Well, you select any cell that's part
of the data source and then on the Insert tab,
| | 00:53 | click the Pivot Table button and
you got the Create Pivot Table dialog box.
| | 00:59 | So, you should verify that the data
you selected is being used to create the
| | 01:03 | pivot table and here we have the
Company Revenue table, which is here, and then
| | 01:07 | you've got to choose where you
want the Pivot Table to be placed.
| | 01:10 | Now, I always place my pivot table
on a new worksheet and the reason I do
| | 01:15 | that is because the pivot table will
take up quite a bit of room and
| | 01:19 | you don't want to crowd either your
source data in case you want to go back and
| | 01:22 | look at that, or the pivot table.
| | 01:24 | So it just makes more sense to
me to put it on its own worksheet.
| | 01:28 | So when you are ready, you can click OK and
you get the bare bones outline of a pivot table.
| | 01:35 | Over here on the right side, you'll
see that you have the Pivot Table Field List
| | 01:38 | and a field is simply a fancy name for
a data column, something from the source data.
| | 01:44 | So you will remember we had Year,
Quarter, Month, Company, and Revenue and
| | 01:49 | if you don't see the Pivot Table Field List,
I'll close it to show you how to open it back up.
| | 01:54 | All you need to do to bring it up is
to click anywhere on the pivot table and
| | 01:58 | then on the Pivot Table Options tab
on the ribbon, in the Show/Hide group,
| | 02:04 | click Field List and it comes back.
| | 02:06 | Now, you can start adding fields to
the pivot table. Let's say that
| | 02:11 | I want to have the
companies' names along the top.
| | 02:15 | That would be the Column Labels.
| | 02:16 | So those have been added to the body
of the pivot table, and then we do Year,
| | 02:20 | I'll put that on the Row and then I'll
do Month and you can see we have a great
| | 02:26 | outline for the pivot table.
| | 02:27 | Now, I always add the data last, so
that I have a better idea of how the pivot
| | 02:32 | table looks structurally.
| | 02:35 | It's just a personal preference.
| | 02:36 | You can do it anyway you like and then to
add the Revenue data to the pivot table.
| | 02:41 | You drag it from the Pivot Table Field
List, the choose fields to add to report
| | 02:44 | area and drag it down to Values, and
there we have the revenue and also Excel
| | 02:51 | gives you a fair number of summary options.
| | 02:53 | In this case, we have a Grand Total,
which is for Firm A and Firm B, and then
| | 02:58 | we also have a subtotal for the year 2008.
| | 03:00 | Now, at this point, the
subtotals are at the top of each group.
| | 03:04 | I actually prefer for them to be at the
bottom and there is a way for you to change that.
| | 03:09 | So, what you do is you go up to
the Design tab on the ribbon,
| | 03:14 | in the Layout group click Subtotals and select
the Show all Subtotals at Bottom of Group options.
| | 03:21 | So, now you have 2008 total at the
bottom of the 2008 group and I think that
| | 03:26 | just makes more sense.
| | 03:27 | Now that you've taken the first step
and learned how to create a pivot table,
| | 03:31 | you can move forward and discover just how
powerful a tool pivot tables really are.
| | Collapse this transcript |
| Pivoting a Pivot Table| 00:00 | The real power of a pivot table comes out when
you use it to rearrange your data dynamically.
| | 00:06 | A task that would take several minutes
if done by hand, takes just a few seconds
| | 00:10 | when you summarize your data in a pivot table.
| | 00:12 | When you look at a pivot table, you
realize that the row and column headers
| | 00:15 | provide the structure for the pivot table data.
| | 00:18 | So, here we have a year as our Row
Labels along with month, January,
| | 00:24 | February and so on.
| | 00:25 | And for the Column Labels, we have the
two companies, Firm A and Firm B, and
| | 00:30 | you see that we have that structure
reflected here in the Pivot Table Field area.
| | 00:35 | So how do you rearrange the data?
| | 00:37 | You rearrange the data by dragging the
field headers in the Pivot Table Field
| | 00:41 | List field area to change
the arrangement of the data.
| | 00:44 | So, for example, if I wanted to put
Company at the top of the Row Labels,
| | 00:50 | I would drag it here to the Row Labels
area and you can just barely see a gray
| | 00:55 | line that appears when you move the pivot table
field into a position where it can be dropped.
| | 01:01 | So, I'll drop it and you'll see how Excel
changes the pivot table's data arrangement.
| | 01:06 | So let's take that analysis one step further.
| | 01:09 | Let's say that we wanted to leave
Company and Year as Row Label headers, but
| | 01:13 | move the Month to the Column Label, so
that the data is spread out a little bit more.
| | 01:18 | There you see the new arrangement and
in this case, it really emphasizes the
| | 01:23 | data on a monthly basis
as opposed to yearly basis.
| | 01:27 | You can compare one March to the
previous March for both Firm A and Firm B.
| | 01:32 | Now, here is an interesting trick that
works well when you have a large pivot table,
| | 01:37 | or you have a pivot table
that is summarizing quite a bit of data.
| | 01:41 | If you notice that your performance is
lagging, in other words that it takes
| | 01:44 | some time for Excel to update your
pivot table whenever you pivot it,
| | 01:49 | that means you either have a slow
connection to the data source or there is just
| | 01:53 | too much and Excel needs to work
hard to update your pivot table.
| | 01:56 | If that's the case, you can select the Defer
Layout Update checkbox. Make your changes.
| | 02:04 | Notice that the changes do not
appear in the pivot table yet.
| | 02:07 | Then I'll take Year up to here.
| | 02:09 | When you click Update, Excel updates your
pivot table with the changes that you made.
| | 02:14 | Changing the pivot table's data
arrangement shifts its emphasis, enabling you to
| | 02:18 | examine the data from different
perspectives quickly and easily.
| | Collapse this transcript |
| Filtering a Pivot Table| 00:01 | You can use pivot tables to summarize
huge data sets but the downside of that
| | 00:05 | capability is that you can create pivot
tables that are too large to display on
| | 00:08 | your monitor without scrolling.
| | 00:10 | When you are interested in a subset of
your data, you can filter the pivot table
| | 00:13 | to limit which data appears within it.
| | 00:16 | The way that I prefer to filter data
within a pivot table is to use the headers
| | 00:21 | over here in the Pivot Table Field List.
| | 00:23 | You will notice when you hover your
mouse pointer over a field header, a black
| | 00:27 | down triangle appears in the right corner.
| | 00:30 | So if you click that, you
will see a list of options.
| | 00:34 | So you have sorting and you have
various types of filters available to you.
| | 00:37 | If you want to select which value is
up here in your pivot table, you can go
| | 00:41 | down here to the selection area,
clear Select All which clears all of the
| | 00:45 | checkboxes and then select the
item or items that you want to appear.
| | 00:51 | Just click the one you want, click
OK and Excel filters your pivot table.
| | 00:56 | Now notice that the Pivot Table Field
List task pane disappeared because the
| | 01:01 | cell that was selected before is no
longer in the pivot table because the
| | 01:06 | filtering made the pivot table smaller.
| | 01:08 | But if you click any cell in the pivot table,
the Pivot Table Field List comes right back.
| | 01:13 | If you want to remove a filter, you
can click the same field header and
| | 01:17 | notice that the funnel next to the
down triangle appears to indicate that
| | 01:21 | there is a filter applied.
| | 01:22 | So just click the triangle and you can
either click Clear filter from "Year" or
| | 01:27 | in this case, you can click Select All.
| | 01:29 | Clear filter from "Year" will clear
whichever kind of filter you have set.
| | 01:33 | So you can click that and your data comes back.
| | 01:35 | There is another type of filtering
that you can do in Excel 2007 and that is
| | 01:41 | called the Report Filter.
| | 01:42 | You can see the Report Filter field here
in the Pivot Table Field List fields area.
| | 01:48 | When you add a field to the Report
Filter area, Excel allows you to use that
| | 01:53 | field to filter the pivot table data
but it does not change the arrangement of
| | 01:57 | the data within the pivot table.
| | 01:59 | If we were to add, say, quarter to the
row labels area between year and month,
| | 02:04 | you can see that the pivot
table takes on the new organization.
| | 02:07 | But if I move Quarter to the Report
Filter area, the pivot table returns to its
| | 02:12 | previous organization and
you can see Quarter up here.
| | 02:16 | Now, if you want to filter a pivot
table using a report field area, you
| | 02:20 | can click the down arrow and select which
of the items you want to use as your filter.
| | 02:26 | Now notice that you have a selection
area here that is different from the
| | 02:30 | selection area that appears
for column labels and row labels.
| | 02:34 | I don't know why they are
different, they just are.
| | 02:36 | If you want to select a single value,
you can just click any one of those items.
| | 02:40 | If you want to select multiple items,
then you have to check the Select
| | 02:44 | Multiple Items checkbox, and you get
the familiar interface from the row and
| | 02:49 | column labels areas.
| | 02:50 | So let's say that you wanted to look at
the values just for Quarter 1 and Quarter 3.
| | 02:54 | I already have one selected.
| | 02:56 | Now I'll click 3, click OK and
there you have your applied filter.
| | 03:00 | If you want to remove the filter, just
click the down arrow, select all and click OK.
| | 03:05 | Filtering a pivot table helps you
focus your analysis on the data that's most
| | 03:09 | relevant to the question at hand.
| | Collapse this transcript |
| Adding Pivot Table columns to enhance data analysis| 00:00 | Pivot Tables enable you to analyze
your data efficiently, but some questions
| | 00:04 | can only be answered by performing
calculations on the data used to create your pivot table.
| | 00:09 | To perform that type of analysis,
you can create calculated fields that
| | 00:12 | summarize pivot table data using a formula.
| | 00:16 | Without calculated fields, you would
have to copy the data from your pivot table,
| | 00:19 | paste it onto another worksheet and
create the formulas there. That's a pain.
| | 00:23 | It's extra work that you
absolutely do not have to go through.
| | 00:26 | Now how to create a calculated field in
an Excel 2007 pivot table isn't the most
| | 00:32 | intuitively obvious thing.
| | 00:33 | So I'll just show you where it is on
the user interface so you can do it later.
| | 00:38 | To create a calculated field, first
you select any cell in the pivot table
| | 00:42 | and then on the Pivot Table Tools
Options tab of the ribbon, you go to the
| | 00:46 | Tools group and click Formulas and that's
where you find the Calculated Field menu item.
| | 00:53 | So click that and you get the
Insert Calculated Field dialog box.
| | 00:58 | And what you do here is
create a name for your field.
| | 01:03 | The goal for this field is to
calculate a company's revenue as a proportion of
| | 01:09 | the cost of goods sold.
| | 01:12 | So it will be dividing revenue by COGS.
| | 01:15 | So for that one, I'll just call it Times
COGS, press Tab and the formula will be equal.
| | 01:26 | And then again, I want to
divide revenue by cost of goods sold.
| | 01:29 | So I'll click Revenue, insert that
field into the formula, forward slash to
| | 01:35 | indicate division and then
I insert the Cost of Goods Sold field.
| | 01:42 | So there we have our calculated field
and everything looks correct.
| | 01:47 | So I'll click OK.
| | 01:49 | The Times COGS field appeared in the
Pivot Table Field List and it also appears
| | 01:54 | down here in the pivot table values area.
| | 01:58 | So at present, our pivot
table has two data fields.
| | 02:02 | We have the sum of revenue and we then we
also have the sum of times cost of goods sold.
| | 02:08 | So I'll remove the Revenue field from
the data area for the moment and
| | 02:15 | you can see here, in January 2008, we had
a ratio of 17:1 and the data field is
| | 02:23 | currently configured the way it was
before to display numbers as a currency
| | 02:28 | or accounting field.
| | 02:29 | So I'm going to change the data
type just to make it easier to read.
| | 02:33 | To do that, you right-click a cell
that contains the data that you want to
| | 02:36 | change and click Number Format and
from the list of available values,
| | 02:42 | in this case I'll click Number and
I'll put two decimal places on it.
| | 02:46 | Click OK and there we have it.
| | 02:48 | We have a ratio of 16.75,
which again is different from 17.
| | 02:52 | The rounding got taken care of
when we added the decimal points.
| | 02:57 | Calculated fields extends the types of
analysis you can perform in Excel, which
| | 03:00 | makes pivot tables even more
useful for your financial analyses.
| | Collapse this transcript |
| Tracking cash flow using a Pivot Chart| 00:01 | Pivot Tables are great tools for
summarizing large data collections.
| | 00:04 | But sharing hundreds of individual
values isn't always the best way to
| | 00:07 | discover trends in your data.
| | 00:09 | Creating a Pivot Chart helps you
visualize your data while allowing you to
| | 00:13 | change the data's arrangement within the chart.
| | 00:15 | Now there are two ways to create a Pivot Chart.
| | 00:17 | You can either create a Pivot Table and
a Pivot Chart at the same time from the
| | 00:21 | same data source, or you can create a
Pivot Chart from an existing Pivot Table.
| | 00:26 | You can't create a Pivot Chart by
itself without an underlying Pivot Table.
| | 00:31 | The program just doesn't setup to work that way.
| | 00:33 | You need to have the Pivot Table with
its attendent data objects before the Pivot
| | 00:38 | Chart to work properly.
| | 00:40 | If you do want to create a Pivot Table
and a Pivot Chart at the same time, you
| | 00:45 | can do so by clicking any cell in
your data source, usually an Excel table.
| | 00:50 | And then on the Insert menu, go over to
Pivot Table, and you'll notice that the
| | 00:54 | Pivot Table button is separated into two.
| | 00:57 | There is an upper-half and a lower-half.
| | 00:59 | If you click just the upper-half, you
will create a Pivot Table, but if you
| | 01:03 | want to create a Pivot Chart, you
click the bottom-half, and then click Pivot
| | 01:08 | Chart, and then you can jump in to the
procedure as I'll show you in just a few seconds.
| | 01:13 | But since I already have a Pivot Table
on this worksheet, I'll go here, click
| | 01:17 | any cell in the existing Pivot Table,
and then again on the Insert menu, you can
| | 01:22 | click the type of chart you want to create.
| | 01:25 | So in this case, I'll click
Line to create a line chart.
| | 01:30 | Select the type of chart that I
want and Excel creates the Pivot Chart.
| | 01:34 | Now by default, Excel creates the Pivot
Chart on the same worksheet as the Pivot Table.
| | 01:39 | So I'll just rearrange
things here, there we are.
| | 01:43 | So there is a Pivot Chart and it shows
the revenue in thousands of dollars for
| | 01:48 | FirmA in blue, and FirmB in red, over
the course of these two years and you can
| | 01:53 | see where the months are
delineated along the bottom.
| | 01:57 | So there is the chart.
| | 01:58 | So when you have a Pivot Chart, you can
do all of the things that you can with
| | 02:02 | the Pivot Table, you can
pivot it and you can filter it.
| | 02:06 | So if you want to filter a Pivot Chart,
then you can select the field from
| | 02:10 | within the Pivot Chart Filter pane,
click the Down Arrow, next to the field by
| | 02:14 | which you want to filter, and in this
case, we'll go by year, and since we only
| | 02:17 | want to see 2008, we'll clear the 2009
check-box, click OK, and Excel resizes
| | 02:24 | the chart, so that the data you've
chosen to display fills the available area.
| | 02:29 | Once again, the Pivot Chart Filter pane
indicates that we have an active filter
| | 02:34 | by placing a funnel icon,
as part of the down arrow.
| | 02:39 | So to clear the filter, just click Clear
Filter From "Year", and the Pivot Chart
| | 02:43 | returns to its original value.
| | 02:45 | Pivot Charts help you
summarize your data visually.
| | 02:48 | Combining the ability to change the
Pivot Charts arrangement on the fly with the
| | 02:51 | detailed-oriented analysis in a Pivot Table, you've
a powerful tool in your financial analysis arsenal.
| | Collapse this transcript |
|
|
3. Analyzing Financial StatementsReading a corporate financial statement| 00:01 | Companies create financial
statements to provide information to potential
| | 00:04 | investors and creditors, help
current investors evaluate the health and
| | 00:08 | performance of the company, and
summarize the company's resources, so the
| | 00:12 | government can tax them appropriately.
| | 00:13 | I've selected the annual report from
2008 for the Washington Post Company,
| | 00:20 | just to give you an idea of what the annual
report for large company might look like.
| | 00:25 | I have started out with the income
statement, which summarizes the company's
| | 00:29 | income as a function of all income and
that includes sales revenue, investment
| | 00:34 | income, and takes out
operating expenses and taxes.
| | 00:38 | And like most companies, the Washington
Post Company displays multiple years of data.
| | 00:45 | So for example, we have data from 2008,
2007, and 2006, and they use that as a
| | 00:51 | comparison so that investors and
regulators will have an idea of what the trends
| | 00:56 | within the company look like.
| | 00:58 | It also breaks down the operating
revenues and other costs and expenses by category.
| | 01:03 | So the way the Washington Post
breaks down their operating revenues is by
| | 01:07 | Education. They own the Kaplan educational
publishing service, so they list that separately.
| | 01:13 | Also, they gain revenue from
advertising in the Washington Post and elsewhere.
| | 01:18 | Circulation and Subscriber Income and
then other income, whatever that might be.
| | 01:24 | The next element, Operating Costs and
Expenses, just shows how the Washington Post
| | 01:29 | spends their money in running the company.
| | 01:31 | You've got general operating
expenses, sales general, administrative,
| | 01:35 | depreciation, which we'll cover in
Chapter 10 of this course, and so on.
| | 01:41 | And you also have Income from
Operations, and that includes interest income,
| | 01:45 | so that would be from any investments that
the Washington Post Company happens to have.
| | 01:50 | And then you also see Income Before
Income Taxes and the Cumulative Effect of
| | 01:56 | Change in Accounting Principle.
| | 01:57 | What that means is that the
government changed a rule or regulation and
| | 02:01 | the Washington Post needed to account for
things differently then they had done in the past.
| | 02:05 | That made for a change in
their accounting results.
| | 02:08 | So they are just accounting for
it here in their annual report.
| | 02:12 | So that is the highlight of the income
statement, and if you continue on,
| | 02:20 | you'll see a balance sheet.
| | 02:22 | And a balance sheet summarizes a
company's assets and liabilities.
| | 02:27 | Current Assets are cash or items that
could be converted to cash within a year,
| | 02:32 | and Current Liabilities are
liabilities that must be paid within a year.
| | 02:36 | So for example, for the Current Assets,
the Washington Post Company has Cash and
| | 02:40 | cash equivalents, those are the things
such as bonds or notes that could be sold
| | 02:43 | immediately or at least within a year.
| | 02:46 | So that they could get cash out of them.
| | 02:48 | Accounts receivable are also considered
current assets. Those are generally due
| | 02:52 | within 30 to 90 days.
| | 02:54 | Deferred income taxes are current
assets because it's the money that
| | 02:57 | company still has on hand.
| | 02:58 | Inventory can be converted to cash
through sales, so it's also included and
| | 03:03 | other current assets are just anything
else that the company could convert to cash.
| | 03:08 | When you get away from the Current
Assets, you get into the assets that cannot
| | 03:12 | be converted to cash readily.
| | 03:14 | Buildings, takes a long time to sell them.
| | 03:16 | You can't turn them into cash.
| | 03:17 | Same thing for machinery,
equipment and fixtures and land and so on.
| | 03:22 | So those are the
highlights of the balance sheet.
| | 03:26 | Next we have the Stockholders' Equity statement.
| | 03:29 | The Stockholders' Equity statement
summarizes the company's value to its
| | 03:33 | shareholders by detailing the value of the
stock sold, investment income, and it's balance.
| | 03:38 | It also shows current liabilities
which impact the value of the company to
| | 03:44 | shareholders, because the more money
the company owes to outside sources,
| | 03:47 | the less it has to distribute to shareholders
as either a dividend or another sort of payment.
| | 03:54 | So you have accounts payable and accrued
liabilities, income taxes that are due,
| | 03:59 | and any deferred revenue.
| | 04:00 | This is revenue that is not on the
books this year, but it might be next year.
| | 04:05 | Then you also have short-term
borrowings and those need to be repaid again
| | 04:09 | within a year for them to be
considered a current liability.
| | 04:13 | So you also have pension funds, other
commitments and contingencies and this is
| | 04:17 | where you get into the
Common Shareholders' Equity.
| | 04:21 | The Common Shareholders' Equity
refers to shares of common stock.
| | 04:25 | You generally have two
types of stock for a company.
| | 04:28 | One is preferred stock, which gets paid
first, before the common stock and often
| | 04:33 | at a higher rate of dividend or
payment per share than the common stock.
| | 04:39 | So that's usually counted separately.
| | 04:41 | When you get to the general
shareholders, people like you and me who purchase
| | 04:44 | shares from a stockbroker or through an
online service, then that's where this
| | 04:49 | information comes in.
| | 04:50 | That's the Common Shareholders' Equity.
| | 04:52 | So you have the number of shares of
common stock, whether it's divided into
| | 04:56 | classes and different types of income
or retained earnings, simply money left
| | 05:02 | over from previous years that
wasn't invested or spent by the company.
| | 05:06 | And all of those items
represent aspects of Shareholder Equity.
| | 05:11 | Finally, we have the company's Cash
Flow Statement, and the Cash Flow Statement
| | 05:16 | simply summarizes how the
company earns and spends its cash.
| | 05:18 | Companies generate and spend cash
through operations which includes sales,
| | 05:23 | financing through stock and
other issues and through investments.
| | 05:26 | So this just breaks it down.
| | 05:28 | You have the Net Income at the top,
and the details as you go along.
| | 05:32 | So you can have as much or
as little detail as you like.
| | 05:36 | The government requires a significantly
detailed level of reporting from public companies.
| | 05:40 | That is, those that are
traded on the stock exchange.
| | 05:42 | So that's where you get a lot of these details.
| | 05:46 | Privately held companies do not need to
file this sort of form with the government.
| | 05:50 | You can find the Corporate Financial
statements on the Security and Exchange
| | 05:54 | Commission's website. Just look for the
EDGAR database and search for the 10-K
| | 05:58 | form of the company you want to investigate.
| | Collapse this transcript |
| Introducing common-sizing strategies for analyzing financial statements| 00:00 | Financial statements provide vital
information to potential investors.
| | 00:04 | But most industries have
competitors of various sizes and sales levels.
| | 00:08 | The computer software industry, for
example, has competitors including
| | 00:11 | multi-billion dollar corporations,
all the way down to smaller shops that
| | 00:15 | produce specially products for limited markets.
| | 00:17 | Comparing sales figures, income and
other financial aspects of these diverse
| | 00:21 | companies directly isn't always helpful.
| | 00:23 | But you can perform meaningful
analysis on the financial statements of two
| | 00:26 | separate companies using a
mathematical technique known as Common-Sizing.
| | 00:29 | Common sizing is the practice of
treating financial performance in terms of
| | 00:34 | another factor such as
company size or market share.
| | 00:38 | You can common-size a company sales
figures, for example, by dividing components
| | 00:42 | of those sales figures by the cost
of good sold or buying that sales.
| | 00:46 | As an example, I have a worksheet
with information from two companies, just
| | 00:51 | hypothetical companies of different
sizes but within the same industry.
| | 00:55 | As you can see, Company A with
Operating Revenues of over 4 billion is
| | 00:59 | significantly larger than Company B
which is still impressive but below a
| | 01:04 | billion at 685 million dollars
of operating revenue per year.
| | 01:08 | So the question is, how do you
compare the two companies performance?
| | 01:11 | And once again, the idea is the common-size.
| | 01:14 | So in this example, we'll take these
operating cost and expenses such as general
| | 01:19 | operations, general administrative,
depreciation and so on, and express them as
| | 01:27 | a percentage of operating revenues.
| | 01:29 | So the way that you create that
formula, type an equal sign and then divide
| | 01:34 | the value, in this case B8, the
operating cost by the value in B6, which are
| | 01:42 | the operating revenues.
| | 01:43 | Now I haven't pressed Enter yet, and
that's because I don't want the reference
| | 01:47 | to change when I copy the
formula to other cells in this column.
| | 01:51 | So I'll press F4, which makes the reference
into an absolute reference, and then press Enter.
| | 01:56 | And there you have it.
| | 01:57 | This is about 45% of this value, so it
looks like the calculation is correct.
| | 02:02 | And I'll copy the formula down to the
remaining cells in the column and you see the values.
| | 02:10 | Now we can do the same thing for Company B.
| | 02:13 | So we have cell D8 that are the
operating expenses for Company B, divided by the
| | 02:20 | operating revenues, which are in D6.
| | 02:23 | Again pressing F4 to make D6 into an
absolute reference, so it won't change when
| | 02:28 | we copy the formula and press Enter.
| | 02:30 | And we can see the Company B's
operating expenses are a little bit more but
| | 02:35 | very comparable to those of Company A
. So to calculate the percentage of
| | 02:41 | revenue for the other expenses, we just copy
the formula down the column and there you have it.
| | 02:47 | We are getting a value error in cell
E12, because there is no value or a
| | 02:54 | zero value in cell D12.
| | 02:56 | That's simply just a function of the
data in the worksheet and it's not an
| | 03:00 | error in the formula.
| | 03:01 | So it's nothing to worry about.
| | 03:02 | You can just treat it as a null value.
| | 03:04 | As we can see, Company B is
significantly more efficient in terms of its
| | 03:10 | selling the general and administrative cost,
those are only 27% of their operating revenue.
| | 03:16 | And the two companies are pretty
similar, within a percentage point on
| | 03:21 | amortization of intangible assets.
| | 03:24 | But it looks like Company B has
significantly more depreciation of property
| | 03:30 | plans and equipment, and that
could be due to a couple of factors.
| | 03:33 | The equipment could be newer and the
sum total of the depreciation of all of
| | 03:38 | that equipment is adding up and
allows for a significant expense this year.
| | 03:43 | You do need to be careful when you
perform a common-sizing operation to ensure
| | 03:48 | that your analysis make sense
within the context of an industry.
| | 03:51 | For the software industry, for example,
you have relatively low production cost,
| | 03:55 | so the cost of good sold might not
be the best to use in your analysis.
| | Collapse this transcript |
| Creating common-sized income statements| 00:01 | A company's income statement summarizes
the company's operating expenses, total
| | 00:05 | income and deducts the
provision for income taxes.
| | 00:08 | You can compare two companies
results by dividing the component parts of
| | 00:11 | Operating expenses, Cost of revenue,
Research and development, Sales and
| | 00:15 | marketing, and General and administrative,
by the Total operating income from the year.
| | 00:19 | In the worksheet that you see in front
of you, I pulled some results from the
| | 00:23 | Microsoft 2008 Corporate Annual Report
and specifically, I have the Operating
| | 00:29 | expenses and what I would like to do
is compare them to Operating Income.
| | 00:34 | So I want to point out that unlike
the analysis that I did in the previous
| | 00:38 | lesson, these factors here, these items do
not add up to the total on Operating income.
| | 00:44 | Basically operating income is the
net profit or loss that a company makes
| | 00:50 | based on its operations.
| | 00:52 | It's a function of its total revenue,
so don't be thrown off by the fact that
| | 00:57 | these number don't add up.
| | 00:58 | So to calculate the percentage of
Operating Income, again we're using it as a ratio.
| | 01:03 | Type =B6, which contains the value
for the Cost of revenue divided by B11,
| | 01:09 | the Operating Income.
| | 01:11 | And again, when I copy the formula
further down the column, I want the
| | 01:15 | cell reverence B11 to remain
constant, so I press F4 to make it an
| | 01:20 | absolute reference.
| | 01:22 | Press Enter and we see that Cost of
revenue is 51.57% of Operating Income.
| | 01:28 | And I'll just copy that down.
| | 01:30 | You'll see that in this case, Total
operating expenses were higher than
| | 01:35 | the Operating Income.
| | 01:37 | But that's not unusual.
| | 01:38 | Say a company has an Operating
revenue of $100 million in a year and it has
| | 01:43 | total operating expenses of 80 million.
| | 01:46 | They still have made $20
million on their operations.
| | 01:49 | So they're in the black.
| | 01:50 | The only question is whether they can
reduce their Operating expenses or apply
| | 01:55 | the spending more effectively
so that Operating Income goes up.
| | 01:59 | If you wanted to compare two companies,
one like Microsoft over here which has
| | 02:03 | an extremely high operating revenue and
cost associated with that revenue, you
| | 02:08 | would type in the formula = and the
value is in F6 divided by the value in F11
| | 02:15 | and again, we don't want this all
referenced to F11 to change when we copy the
| | 02:19 | formula, so you press F4, to turn it
into an absolute reference and press Enter
| | 02:25 | and there your have your result.
| | 02:27 | And I'll copy the formula down here
and now you're able to compare the two
| | 02:33 | companies performance in terms of
Operating Income, based on their expenses for
| | 02:37 | Cost of revenue, Research and
development, Sales and marketing, and so on.
| | 02:41 | Common-sizing a company's income
statement enables you to determine what portion
| | 02:45 | of the company's expenses go
to which components of its cost.
| | 02:48 | If one company has a much larger
percentage of general and administrative cost
| | 02:52 | for example, you might want to hold
off investing in that company, until it
| | 02:55 | finds efficiencies in that area, freeing up
more resources for product development and growth.
| | Collapse this transcript |
| Creating common-sized balance sheets| 00:00 | Just as you can compare the income
statements of two differently sized
| | 00:03 | companies, you can common size
entries on their balance sheets to allow for
| | 00:07 | meaningful analysis between those companies.
| | 00:10 | One useful balance sheet element to
use as a basis for common sizing is the
| | 00:13 | current liabilities account.
| | 00:15 | The Current Liabilities Account
includes account payable, income taxes,
| | 00:18 | short-term unearned revenue, securities
lending payable and the even popular, other.
| | 00:25 | Accounts payable is a measure of how
much money is owed to creditors for
| | 00:28 | products and services and securities
lending payable shows how much money is
| | 00:32 | owed to creditors who purchase bonds, and
other debt instruments issued by the company.
| | 00:37 | In this case, I'm going to use the Total
current liabilities as my Common-sizing
| | 00:41 | factor and I'll be
comparing the companies' assets.
| | 00:45 | So first we have the Total cash, cash
equivalents, and short-term investments.
| | 00:48 | We have Total current assets which
includes the cash, but also includes
| | 00:53 | other items that can be converted
to cash within a year and then Total
| | 00:57 | assets which includes things such as
buildings and other assets that can't
| | 01:02 | be converted readily into cash.
| | 01:03 | So, to create the formula, I'll divide
the value in B9 by the value of the Total
| | 01:12 | current liabilities in B7 and again,
when I copy the formula, I don't want the
| | 01:17 | cell reference to B7 to change.
| | 01:19 | I want it to remain absolute, so I'll
press F4 and that makes the cell reference
| | 01:25 | an absolute reference.
| | 01:26 | Press Enter and I'll express these as
percentage with two decimal points and
| | 01:37 | I'll copy the formula down, so that
we've the Total current assets and Total
| | 01:43 | assets also expressed as a
percentage of the Total current liabilities.
| | 01:47 | Here we have Company B, again,
it's significantly smaller.
| | 01:52 | But when we perform the analysis the
common sizing, we take F9, divide that by
| | 01:59 | the Total current liabilities
for the company, which are in F7.
| | 02:02 | Again, pressing F4, say the cell
reference doesn't change when we copy the
| | 02:06 | formula to another cell.
| | 02:08 | Press Enter and again, I'll format
these as percentages adding two decimal
| | 02:13 | points and copy the formula down.
| | 02:18 | So in this case, these two companies
have a lot of cash on hand and also lot of
| | 02:22 | assets in relation to their
total current liabilities.
| | 02:26 | The higher the ratio between a
company's assets and its liabilities the
| | 02:29 | better shape it's in, because the better off
it is in terms of what it owes its creditors.
| | 02:34 | Common-sizing a balance sheet
enables you to determine how well a company
| | 02:38 | is managing its debt.
| | 02:40 | Both the members of its supply chain and
the creditors who bought bonds and notes.
| | 02:44 | That said, you should always check a
company's guidance and explanations in its
| | 02:48 | quarterly or annual report, so you
have a context for the raw numbers.
| | Collapse this transcript |
| Calculating percentage changes in financial statements| 00:01 | When you analyze a company's
performance, it's often useful to see how much
| | 00:04 | the company's revenue or expenses have
increased or decreased since the last
| | 00:08 | month quarter or year.
| | 00:11 | Some company's annual reports include
calculations indicating these changes, but
| | 00:14 | if they don't, you can create formulas
to calculate that difference quickly and
| | 00:17 | easily, especially if you store
your data using an Excel table.
| | 00:21 | So the first step is to input
your data into an Excel table.
| | 00:25 | I have one here in my
Percentage Change worksheet.
| | 00:28 | I have the values for operating cost,
selling, general and administrative,
| | 00:33 | basically operating costs and expenses
for fiscal year 2008 and fiscal year 2007.
| | 00:39 | Once again, my goal is to calculate
the percentage change from 2007 to 2008.
| | 00:45 | To do that, you create a formula, where
you subtract the previous year's value
| | 00:49 | from the current year value and
then divide it by the previous year to
| | 00:52 | calculate the change.
| | 00:53 | So when you put that into operation,
you type in =, the present year or the
| | 00:59 | most recent year 2008 is in cell B6, subtract
the previous year, C6 and close the parenthesis.
| | 01:10 | The reason that I have these two values
in a parenthesis is because when I put
| | 01:15 | in division and divide by C6, Excel
would divide C6 by C6, because division is a
| | 01:24 | higher priority operation than subtraction.
| | 01:27 | So if there were no parenthesis, it
will do the division first, dividing C6 by
| | 01:31 | C6, which is 1, it has to be, and
subtract the value of 1 from the value in B6.
| | 01:36 | And you would get the
wrong answer for your formula.
| | 01:39 | So, by using the parenthesis, I'm able to
calculate their percentage change correctly.
| | 01:43 | So, my formula is in order and I press
Enter and there you have the percent change.
| | 01:49 | That is correct in this case,
so I don't make any changes.
| | 01:52 | But if you did want to make some
changes on how Excel handles formulas that you
| | 01:57 | enter into tables, you can
use the AutoCorrect Options.
| | 02:00 | Click here and then you can either
undo the current operation or you can have
| | 02:04 | Excel not automatically create
calculated columns whenever you enter a formula
| | 02:10 | into a cell at the top of a column.
| | 02:13 | It's useful to compare a year to a
previous year, but you should also compare
| | 02:16 | previous quarters or
months to the previous quarter.
| | 02:19 | If you want, you can also perform To A
Year Ago Analysis where you compare this
| | 02:24 | January to the previous January.
| | 02:26 | That's particularly useful for companies
who have sales that are highly seasonal.
| | 02:31 | Once you've calculated a company's
percentage change for income and expenses,
| | 02:35 | you can compare its values to those of
other companies based on the values in
| | 02:38 | their annual reports.
| | 02:40 | Revenue growth provides one obvious
data point for you to compare, but even
| | 02:44 | negative growth might not be
the worst outcome imaginable.
| | 02:47 | During a recession, the company whose
sales decrease the least will often be in
| | 02:51 | the best position when the economy recovers.
| | Collapse this transcript |
|
|
4. Calculating Profitability RatiosCalculating earnings per share| 00:01 | Investors can make money by purchasing
stock in companies that pay dividends
| | 00:04 | on a per share basis.
| | 00:05 | Companies that pay dividends
regularly, such as every quarter, decide how
| | 00:09 | much money they want to distribute
to shareholders and make that payment
| | 00:12 | after the quarter ends.
| | 00:13 | To calculate Earnings per Share,
you divide the income to be paid to
| | 00:17 | shareholders of common stock by
the number of outstanding shares.
| | 00:20 | As you can see here in our worksheet,
we have 45 million shares. This is
| | 00:25 | 45,000, but because all values are in
thousands, we have 45000x1000, which is
| | 00:31 | 45 million outstanding shares.
| | 00:33 | We also have the net income available
for shares of common stock, which is
| | 00:37 | 18000x1000 or 18 million.
| | 00:40 | So then to find the Earnings per Share,
you create a formula that divides the
| | 00:45 | Income Available for Common Shares,
which is in B6, by the number of
| | 00:49 | Outstanding Shares, which is in B5, press Enter
and you have an earning of 40 cents per share.
| | 00:55 | There are some factors you should take
into account when you're evaluating a
| | 00:59 | company based on their Earnings per
Share. First off is that companies can
| | 01:02 | decide against paying dividends to
shareholders and instead retain the
| | 01:05 | earnings to spend on items such as
research and development, new office
| | 01:09 | buildings or equipment upgrades.
| | 01:10 | Earnings per Share can also be skewed
positive by generating unusually high
| | 01:14 | income such as selling a product line
to another company or negatively by
| | 01:18 | one time charges such as
writing off substantial debt.
| | 01:21 | Companies can also buy back stock to
reduce the number of outstanding shares,
| | 01:24 | making the per share price look better.
| | 01:27 | Be sure to check a company's public
filings to see if they've bought back
| | 01:30 | any shares recently.
| | 01:31 | You should also be sure to determine
whether a company has issued any preferred stock,
| | 01:35 | which has paid its dividend
before consideration, if any, is given to the
| | 01:39 | holders of common stock.
| | 01:41 | Companies often give guidance
regarding how much of a dividend they expect to
| | 01:44 | pay in future quarters. Comparing the
company's Earnings per Share with the
| | 01:47 | amount of money they retain for
capital improvement and business development,
| | 01:50 | provides a solid basis for evaluating
the attractiveness of a company stock.
| | Collapse this transcript |
| Calculating return on equity and return on assets| 00:00 | In this lesson, I'll discuss how to
calculate return on equity and return on
| | 00:05 | assets, two important ratios when you're
considering the financial health of a company.
| | 00:09 | A publicly traded company has one
goal: to maximize a shareholder value.
| | 00:14 | The bottom line measure of a firm
shareholder value is its equity, which is
| | 00:17 | Total Assets minus Total Liabilities.
| | 00:20 | Comparing the relationship between a
company's earnings to its equity provides a
| | 00:23 | clear picture of the company's ability
to generate returns for its stockholders.
| | 00:27 | You divide the company's net income,
which is its income after taxes by its
| | 00:32 | shareholder's equity.
| | 00:33 | So here in the worksheet, I have two
entries, one is the Net Income After Taxes
| | 00:37 | and the other is the Shareholder's Equity.
| | 00:40 | So to divide the two, type equal to
start the formula and we have Net Income
| | 00:45 | After Taxes in cell B5, divide that
by Shareholder's Equity in B6 and press
| | 00:50 | Enter and there you have your
ratio of 0.11 for the return on equity.
| | 00:56 | You can compare this result to other
companies in which you're considering
| | 00:59 | investing, so you can determine which
company has provided a better result
| | 01:02 | for its shareholders.
| | 01:03 | And just as a general basis for
comparison, most firms in the S&P 500 generate
| | 01:09 | ROE ratios of between 10% and 15%.
| | 01:13 | You should also take into
consideration a firm's Return on Assets.
| | 01:17 | Companies need to make sure that
their infrastructure, which includes their
| | 01:20 | office buildings, computers and
manufacturing systems, are of high enough
| | 01:24 | quality to support the company's activities.
| | 01:25 | It's important that companies not
spend too much on their assets, as doing so
| | 01:29 | cuts into profitability, but they
should spend enough to ensure their
| | 01:32 | infrastructure supports the operations.
| | 01:34 | One measure you can use to determine
how efficiently a company spends its money
| | 01:37 | on assets is by calculating
the Return on Assets ratio.
| | 01:42 | To calculate the Return on Assets ratio,
you divide a company's earnings before
| | 01:46 | interest and taxes by
the company's total assets.
| | 01:48 | So here we have those two values, the Earnings
Before Income and Taxes and the Total Assets.
| | 01:54 | So I'll divide earnings, B5/B6
and you have a ratio of 0.04.
| | 02:01 | Comparing a company's Return on
Assets ratio to those of other firms in the
| | 02:04 | sector help you determine how
efficiently the company employs its assets.
| | 02:09 | Reductions in a company's Return on
Assets ratio could indicate either
| | 02:12 | economic opalescence, such as when a
company's manufacturing system falls
| | 02:16 | behind the technology curve or it
could mean that the company just invested a
| | 02:20 | significant amount of money in the new
system and has yet to reap the benefits
| | 02:23 | of the new technology.
| | 02:25 | As always, be sure to read a company's
public filings to help you interpret the
| | 02:28 | numbers included in those documents.
| | 02:30 | You should compare a company's Return
on Equity and Return on Assets ratios to
| | 02:34 | determine how the company
pays for its operations.
| | 02:37 | When a company's Return on Equity is
higher than its Return on Assets, it's
| | 02:40 | usually an indication that the company
finances its operations through sales of
| | 02:44 | stock instead of taking on debt.
| | 02:46 | If Return on Assets is higher than
Return on Equity, then the opposite is true.
| | Collapse this transcript |
| Calculating gross profit margin and net profit margin| 00:01 | One of the most basic measures of
profitability is how much money a company
| | 00:04 | makes on each item it sells.
| | 00:06 | You can analyze a company's profit by
subtracting the cost of goods sold from
| | 00:09 | total revenue and then dividing
that result by total sales revenue.
| | 00:13 | The result is the gross profit ratio.
| | 00:15 | A company's gross revenue ignores all
expenses except the cost of goods sold.
| | 00:20 | In industry lingo, gross revenue
is Earnings Before Interest, Taxes,
| | 00:24 | Depreciation, and Amortization or EBITDA.
| | 00:28 | So here in the worksheet, you can
see that we have the Sales Revenue and
| | 00:33 | the Cost of Goods Sold.
| | 00:34 | So again what we're going to do is
subtract cost of goods sold from sales
| | 00:38 | revenue and then divide it by sales
revenue to get the gross profit margin.
| | 00:42 | So = (B5-B6/B5), press Enter,
and there is our ratio, 0.56.
| | 00:52 | You can calculate a company's gross
profit margin for all of its products, but
| | 00:56 | you can also break the numbers down by
product to get an idea of how efficiently
| | 01:00 | a product contributes to
the company's bottom line.
| | 01:02 | Some companies follow what's called a
Loss Leader Strategy, where they sell one
| | 01:06 | item such as a razor at a loss,
because the ancillary products such as razor
| | 01:10 | blades generate substantial profits.
| | 01:12 | Another example of this Loss Leader
Strategy comes from the video game console market.
| | 01:16 | Companies often sell their consoles at a
loss and make up the difference in game sales.
| | 01:20 | In those cases it might make sense to consider
the products as a group and not individually.
| | 01:24 | Calculating a company's gross profit
margin tells us how effectively the company
| | 01:28 | earns money with its products, but
it ignores expenses such as interest,
| | 01:32 | depreciation and taxes.
| | 01:34 | Because investors need to know more
about a company's finances than simply the
| | 01:37 | amount of money it makes on each sale,
you can calculate the Net Profit Margin,
| | 01:41 | which takes interest, tax, depreciation
and amortization expenses into account.
| | 01:46 | Because of that, it's a true measure of
a company's ability to generate cash in
| | 01:50 | excess of its expenses.
| | 01:52 | To calculate a company's net profit
margin, you subtract all interest, tax,
| | 01:56 | depreciation and amortization expenses
from the company's sales revenue and then
| | 02:00 | divide the result by sales revenue.
| | 02:03 | That also includes cost of goods sold.
| | 02:05 | So on this worksheet, we have the
sales revenue and then we have all of the
| | 02:09 | cost, not just cost of goods sold.
| | 02:11 | So to calculate the Net Profit Margin,
we have a formula that subtracts all of
| | 02:18 | the cost from B5, the sales revenue.
| | 02:20 | So we have sum of B6-B10, and these are
the costs, and divide that result by the
| | 02:30 | sales revenue, which is in cell B5.
| | 02:33 | The result is the Net Profit Margin,
which reflects a company's true ability
| | 02:37 | to generate profits.
| | 02:39 | Calculating the Net Profit Margin for
individual products can generate different
| | 02:42 | results based on how the
company assigns its overhead cost.
| | 02:45 | For example, a company might need to
take out a loan so they can manufacture a
| | 02:48 | product, in which case the company
can assign that facility's interest,
| | 02:52 | depreciation and amortization
expenses to the product's account.
| | 02:55 | In many cases though, the company will
spread the burden of taxes, interest,
| | 02:58 | depreciation and amortization
across all of its product lines.
| | 03:02 | Also, it's not unusual for a company's
gross and net profit margins to vary by season.
| | 03:07 | Many retail businesses rely on brisk
holiday sales to turn their profit for the year.
| | 03:11 | Companies with consistently high profit
margins relative to other companies in
| | 03:15 | their market sector sell products
efficiently and keep their operating expenses in check.
| | 03:19 | The cash they generate can be
distributed to shareholders as dividends making
| | 03:23 | the company more attractive to investors
or be used for research and development
| | 03:27 | to enhance their products.
| | Collapse this transcript |
|
|
5. Calculating Liquidity and Activity RatiosCalculating the current ratio and quick ratio| 00:01 | In this lesson I'll discuss two very
useful ratios, the current ratio and
| | 00:05 | the quick ratio, also known as the acid test.
| | 00:08 | If you want to evaluate a company's short
-term financial health, one of the best
| | 00:11 | ratios you can use is the current
ratio, which compares a company's current
| | 00:15 | assets to its current liabilities.
| | 00:17 | A current asset is an asset that can
be reasonably assumed to be converted to
| | 00:21 | cash within one year.
| | 00:23 | Current assets include cash,
accounts receivable, and inventory.
| | 00:27 | Similarly, a current liability is a
liability that must be paid within one year.
| | 00:32 | To find a company's current ratio, you
divide the value of current assets by the
| | 00:35 | value of current liabilities.
| | 00:37 | I have those values here in this worksheet.
| | 00:39 | It's just data from a
fictitious company, current assets and
| | 00:42 | current liabilities.
| | 00:43 | To calculate the current ratio, create
a formula that divides current assets by
| | 00:49 | current liabilities, and there is the result.
| | 00:52 | Most healthy companies have a current
ratio of at least 1 indicating that their
| | 00:55 | assets are greater than their liabilities.
| | 00:58 | You can also use the two values from
the current ratio, current assets and
| | 01:01 | current liabilities, to
determine the company's working capital.
| | 01:04 | To calculate working capital, which is
essentially the cash the company has on hand,
| | 01:08 | you subtract current
liabilities from current assets.
| | 01:11 | So in this case we have B5, Current
Assets, - B6, Current Liabilities, and there
| | 01:18 | is the amount of working capital that you have.
| | 01:20 | The higher the current ratio, the better.
| | 01:22 | But a low current ratio most likely
won't be a problem for a company that has
| | 01:26 | the potential to borrow money
to increase its cash on hand.
| | 01:29 | Unless a company makes its money
exclusively through consulting services or
| | 01:33 | licensing its patents and other
intellectual property, it will make its money by
| | 01:36 | selling physical goods.
| | 01:38 | Goods in inventory must be sold to
be converted to cash, so analysts
| | 01:42 | developed the quick ratio.
| | 01:43 | The philosophy behind the quick ratio
is that because inventory is the least
| | 01:47 | liquid asset, you should exclude it
from the current assets calculation by
| | 01:51 | subtracting the value of goods and
inventory and then divide the new total by
| | 01:55 | current liabilities.
| | 01:56 | Here we have current assets and
inventory is called out separately and we have
| | 02:01 | current liabilities.
| | 02:03 | So the quick ratio would be B5-B6 and
divide that by B8 to get the quick ratio.
| | 02:12 | Now be aware that companies can alter
their quick ratio by choosing when to make
| | 02:16 | major purchases or sell off assets.
| | 02:19 | If a company intends to buy patents or
other intellectual property from another firm,
| | 02:22 | you should take that into
account if it looks like the deal has been
| | 02:26 | reached before the end of a fiscal year.
| | 02:28 | It's vital that you only use the
quick ratio to compare companies with
| | 02:31 | similar business models.
| | 02:33 | A consulting firm, for
example, would carry no inventory.
| | 02:36 | So its quick ratio would be the same or
very nearly the same as its current ratio.
| | 02:40 | A department store chain, by
contrast, would have a large inventory and
| | 02:44 | therefore a much lower quick
ratio than the consulting firm.
| | Collapse this transcript |
| Calculating the average collection period| 00:00 | The name of the Average Collection
Period ratio is a bit misleading.
| | 00:04 | After all, how can an analyst
accurately measure how long on average it takes
| | 00:08 | a company to collect on its debts without
having access to data on every transaction?
| | 00:13 | The answer, of course, is that they
can't, but you can discover useful
| | 00:16 | information about a company's
collection from its public data.
| | 00:19 | The Average Collection Period
ratio is calculated in two parts.
| | 00:23 | In the first step, you divide Total
Sales by Accounts Receivable, which is the
| | 00:27 | amount yet to be paid for completed sales
to calculate the Receivables Turnover ratio.
| | 00:32 | So on this worksheet, we have Total Sales,
Accounts Receivable and Days in Year,
| | 00:36 | which we'll use later.
| | 00:37 | So to calculate the Receivables
Turnover ratio, it's equal B5 divided by B6 and
| | 00:45 | there you have that ratio and again,
it tells you how many times in a year a
| | 00:49 | company earns the money
currently owed by its creditors.
| | 00:52 | You can compare the Receivables
Turnover ratio to the Times Interest Earned
| | 00:55 | ratio, which I'll cover
elsewhere in this course.
| | 00:58 | That ratio calculates the number of
times a year a company earns enough money to
| | 01:01 | cover its interest payments on its debt.
| | 01:03 | In the second step, you divide 365, the
number of days in a year by the result
| | 01:07 | of the first step, the
Receivables Turnover ratio.
| | 01:10 | You treat the result as a number
of days, which you interpret as the
| | 01:13 | Average Collection Period.
| | 01:16 | So here we have the days in the year,
which are in cell B7, and you divide that
| | 01:21 | by the Receivables Turnover ratio, B9.
| | 01:24 | Press Enter and you have an Average
Collection Period of just over 80 days.
| | 01:29 | A company's Average Collection Period
provides an indirect measure of its health
| | 01:32 | by providing insight into that
company's credit granting policies.
| | 01:35 | If a company's average collection
period is higher than that of other companies
| | 01:38 | in the same sector, it could
indicate that its customers are running into
| | 01:41 | financial difficulties and
therefore pay more slowly.
| | 01:44 | Finally, I would like to point out that
analysts differ on how they calculate a
| | 01:47 | company's Average Collection Period.
| | 01:49 | So don't be surprised if you are
asked to use a different formula.
| | 01:52 | As long as you use the same formula
consistently, your results will be useful.
| | Collapse this transcript |
| Calculating inventory turnover| 00:01 | Companies must walk a fine line
between maintaining enough inventory to
| | 00:04 | enable immediate sales, but without
producing too many units that end up
| | 00:07 | sitting in a warehouse unsold.
| | 00:09 | Modern companies do their best
to produce goods as needed on a
| | 00:12 | just-in-time basis.
| | 00:13 | So they limit their upfront investment and
don't have unsold products sitting in a warehouse.
| | 00:17 | The inventory turnover ratio indicates
how well a company handles that problem.
| | 00:21 | To calculate the inventory turnover
ratio, you divide the cost of goods sold by
| | 00:25 | the value of goods currently in inventory.
| | 00:28 | So, I have those figures here.
| | 00:30 | The cost of goods sold and the inventory,
and to calculate the ratio, we divide
| | 00:36 | B5, cost of goods sold, by B6,
which is the inventory turnover ratio.
| | 00:41 | The inventory turnover ratio is a
measure of how efficiently a company manages
| | 00:45 | its production processes.
| | 00:47 | One example of managing inventory
is the book publishing industry.
| | 00:51 | On a per unit basis, it is much less expensive
to print 100,000 copies of a book than 10,000.
| | 00:56 | So publishers will often
order a huge run of books.
| | 00:59 | So, half of them with the regular price, and
offer the rest of the stores at a deep discount.
| | 01:03 | This process called remaindering
enables stores to purchase for $1 older
| | 01:07 | books that might have been sold at a wholesale
price of $10 when the book was still popular.
| | 01:11 | Selling the extra books at that deeply
discounted price, removes the stock from
| | 01:15 | the publisher's inventory, and improves
the company's inventory turnover ratio.
| | 01:20 | If you're thinking about investing in a
manufacturing firm, you should compare
| | 01:23 | its inventory turnover ratio to the
industry average, and the ratio of other
| | 01:27 | companies in the sector.
| | 01:29 | A relatively high inventory turnover
ratio indicates that goods are sitting in a
| | 01:33 | warehouse, taking up space and depreciating.
| | Collapse this transcript |
|
|
6. Calculating Leverage RatiosCalculating the equity ratio| 00:01 | When a company decides to raise money
by selling stock, it holds an Initial
| | 00:04 | Public Offering or IPO.
| | 00:07 | The company can then use that
cash to fund its operations.
| | 00:10 | One way to determine how well a company
employs its stockholder's investments is
| | 00:14 | by calculating the company's equity ratio.
| | 00:17 | To calculate an equity ratio, you divide a
company's total assets by its total equity.
| | 00:22 | A corporation's total equity is the
sum of its retained earnings, which is
| | 00:26 | unspent cash sitting in a bank account,
and the amount of money received from
| | 00:30 | investors through sales of stock.
| | 00:32 | Total assets are everything the company owns.
| | 00:35 | It can include cash, cash equivalents
such as short-term notes and also anything
| | 00:40 | that's a longer term asset, such as
a building or intellectual property.
| | 00:45 | So, to calculate the equity ratio, you
divide total assets in cell B5 by total
| | 00:52 | equity in cell B6, and there you have the ratio.
| | 00:56 | Now, a low equity ratio isn't
necessarily a bad thing, but a decreasing
| | 01:00 | equity ratio could indicate that a company's
sales, which generate cash, aren't going well.
| | 01:05 | The equity ratio provides an insight
into the ability of a company to generate
| | 01:09 | return on its shareholder's investments.
| | 01:11 | Sudden changes in a company's assets
such as through sales of product lines,
| | 01:15 | buildings, purchases of buildings and so on,
can distort this value in the short term.
| | 01:20 | So, be sure to consider the company's equity
ratio over time before drawing any conclusions.
| | Collapse this transcript |
| Calculating the debt ratio| 00:00 | Like private individuals, companies can
borrow money to finance their operations.
| | 00:04 | Some mechanisms to borrow money
include taking out loans, issuing stock, and
| | 00:08 | selling bonds to raise capital.
| | 00:10 | Also, like private individuals,
companies can only borrow so much money before
| | 00:14 | they start to become bad credit risks.
| | 00:16 | One basic measure of a company's
relative indebtedness is the debt ratio.
| | 00:19 | To calculate the debt ratio, which is
also some times called the total debt
| | 00:23 | ratio, you subtract a company's total
equity from total assets, and divide that
| | 00:27 | result by total assets.
| | 00:29 | So, I have a worksheet here, with
Shareholders' Equity, Total Assets, and Total
| | 00:34 | Liabilities, which we'll use in a moment.
| | 00:35 | Now, before I type in the debt ratio
formula, I'd like to point out that I have
| | 00:39 | a copy of the formula here in cell C9.
| | 00:43 | You can see the formula
up here in the formula bar.
| | 00:46 | Now, the reason that Excel doesn't
treat this formula as a formula, it displays
| | 00:51 | it as text, is because I have an
apostrophe here at the start of the entry.
| | 00:56 | Anytime a cell's contents begin with
an apostrophe, Excel treats the value as
| | 01:01 | text, regardless of whether it's a number,
a currency value, and date, whatever.
| | 01:05 | It just treats it as a string of characters.
| | 01:07 | So, that's the formula I'll be
entering in, without the apostrophe.
| | 01:11 | So Excel treats it as a formula.
| | 01:13 | We have =B6-B5, which is again
assets minus equity, divided by B6.
| | 01:20 | Now, because the two values that Excel
was using in this formula, B6 and B5, are
| | 01:25 | both currency values, it attempted to
display the result as a currency value
| | 01:30 | here, except it doesn't make
any sense because it's a ratio.
| | 01:33 | So, I'll change that
cell's formatting to a number.
| | 01:36 | Now, we see a debt ratio
of 0.39, which is correct.
| | 01:39 | There are two useful ways you
can extend the debt ratio analysis.
| | 01:43 | The first is by dividing the company's
total liabilities by its total equity,
| | 01:47 | which gives you the debt-to-equity ratio.
| | 01:49 | The debt-to-equity ratio is an extremely
useful tool for analyzing a company's health.
| | 01:54 | The rule of thumb is that manufacturing
firms should maintain a debt-to-equity
| | 01:57 | ratio of between 0.5 and 1.5.
| | 02:00 | If a company borrows too much money, it
can run into problems servicing its debt.
| | 02:04 | On the other hand, a company that
doesn't borrow any money or not enough money
| | 02:08 | might be missing out on opportunities
because it's being too conservative.
| | 02:11 | So, to calculate the debt-to-equity
ratio, we divide the company's total
| | 02:15 | liabilities, which are in B7 by its
shareholders' equity in B5, press Return,
| | 02:21 | and there is the result.
| | 02:23 | You can also calculate a measure
called the Equity Multiplier, which is total
| | 02:26 | assets divided by total equity.
| | 02:28 | The equity multiplier shows a
company's total assets per dollar of
| | 02:32 | shareholders' equity.
| | 02:33 | A higher equity multiplier indicates
higher financial leverage, which means the
| | 02:36 | company is relying more on
debt to finance its assets.
| | 02:39 | So, to calculate the equity multiplier,
we divide total assets by shareholders'
| | 02:45 | equity, and there's the result.
| | 02:47 | The debt ratio, debt to equity ratio, and
equity multiplier are all based on the same factors.
| | 02:53 | If you can calculate any one of those
ratios, you can calculate the other two.
| | Collapse this transcript |
| Calculating the times interest earned ratio| 00:01 | When a company borrows money to finance
its operations, it must pay interest on
| | 00:04 | those loans so the lender can
make a profit on the transaction.
| | 00:08 | The more money a company makes,
the more comfortably it can handle
| | 00:10 | those interest payments.
| | 00:12 | The Times Interest Earned Ratio
calculates the number of times a company could
| | 00:15 | pays its yearly interest bill based on
its earnings before interest and taxes.
| | 00:19 | To calculate a company's Times Interest
Earned Ratio, you divide Earnings Before
| | 00:23 | Interest and Taxes or EBIT by
the interest paid for the year.
| | 00:28 | So, in this worksheet I have Earnings
Before Interest and Taxes, Interest,
| | 00:32 | and also Taxes.
| | 00:34 | Even though we don't use the Taxes
figure in this formula, you'll usually find
| | 00:38 | it on a worksheet or in an annual
report along with the Interest and the EBIT,
| | 00:43 | Earnings Before Interest and Taxes.
| | 00:45 | But you don't need to worry.
| | 00:46 | It's not used in the formula.
| | 00:48 | To calculate the Times Interest Earned
Ratio, you divide the Earnings Before
| | 00:52 | Interest and Taxes by the
interest that's due in a given year.
| | 00:55 | So, to that you divide B5, the EBIT,
by Interest, and press Return, and there
| | 01:02 | is your result.
| | 01:03 | This company makes six times the money
it needs to pay its interest every year,
| | 01:07 | which indicates a healthy earning power
and that it's a no danger of defaulting
| | 01:10 | on any of its loans.
| | 01:12 | Now, even though it might seem
prudent to keep a company's Times Interest
| | 01:15 | Earned Ratio as high as possible, an
extremely high ratio might indicate that a
| | 01:19 | company either has not taken on
enough debt to pursue new opportunities, or
| | 01:24 | that it is paying down its current debt
too quickly, using funds that could be
| | 01:27 | invested elsewhere.
| | 01:29 | By contrast, a low Times Interest
Earned Ratio won't be a problem for a company
| | 01:32 | with a lot of cash in the bank, but
the closer the ratio gets to 1, the more
| | 01:36 | likely it is that
company will run into problems.
| | 01:39 | Potential lenders can use the Times
Interest Earned Ratio to evaluate a
| | 01:42 | company's creditworthiness, and
potential investors can use the number to
| | 01:46 | determine whether a company is
managing its debt load effectively.
| | Collapse this transcript |
|
|
7. Calculating the Effect of Interest Rates on Loan RepaymentCalculating simple interest and compound interest| 00:00 | One of the first rules you learn in
business is that no one gives you money for free.
| | 00:05 | If you plan to borrow money with the
goal of making a profit, you should
| | 00:08 | expect to pay interest so that the
institution that loaned you money can make a profit too.
| | 00:12 | There are two main ways to calculate
interest, Simple Interest and Compound Interest.
| | 00:17 | Simple Interest is calculated based on
the principal, interest rate, and time only.
| | 00:22 | In other words if you pay a loan back
over time, the unpaid interest doesn't
| | 00:27 | part of the principal.
| | 00:28 | As an example, take a look at the
loan terms we have in this worksheet.
| | 00:33 | We have a principal of $200,000,
a rate of 6%, and a term of 5 years.
| | 00:39 | So to calculate the total payment on
the loan we'll add the principal to the
| | 00:45 | principal times the rate times the term.
| | 00:51 | And we have $260,000.
| | 00:54 | You can verify that answer is correct.
| | 00:56 | 6% of $200,000 is $12,000.
| | 00:59 | So you multiply that by 5, which would be
$60,000, and you have a total of $260,000.
| | 01:07 | One other scenario that uses simple
interest would be in annuity that guarantees
| | 01:11 | a set return each year.
| | 01:12 | Most loans and investments calculate
their vales using compound interest.
| | 01:16 | But you might counter simple interest
investments, so you should know how to calculate it.
| | 01:21 | Unlike simple interest, investments
that use compound interest include
| | 01:24 | previously earned interest when
determining how much interest to pay the next
| | 01:28 | time a payment is due.
| | 01:30 | One example would be a certificate of
deposit with an annual percentage rate of 6%.
| | 01:35 | So we have the example here in the
worksheet and you'll notice that the terms of
| | 01:38 | the loan are exactly the same as we
had for the simple interest example.
| | 01:43 | We have a principal of $200,000,
a rate of 6%, and term of 5 years.
| | 01:49 | To calculate the total due over the
life of the loan or the life of the investment,
| | 01:52 | you use the formula here in cell D7.
| | 01:55 | You multiply D3, which is the
principal, by 1+ the rate, which is 6%.
| | 02:03 | So, that would be 1.06, and you raise
1.06 to the power of 5, which is the number years.
| | 02:10 | So, to calculate the future value of the
loan, you type in the formula D3,
| | 02:15 | again the principal, times 1+B4, the rate,
raised to the power of the value in B5,
| | 02:26 | the number of years, and press Return.
| | 02:29 | There you have the
future value of the loan.
| | 02:30 | Now, please note that the future value
of the investment is $267,645.12, which
| | 02:37 | is greater than the $260,000 result
from the simple interest calculation.
| | 02:42 | That is the power compounding
interest monthly as opposed to annual and
| | 02:45 | for including previously earned
interest in future interest calculations.
| | 02:49 | Now, you might also be called on to
evaluate a loan or an investment where
| | 02:53 | interest is compounded continuously and
for that you use the formula in cell D9.
| | 03:00 | So you multiply the principal in cell B3
in this case, by the natural logarithm,
| | 03:07 | and this is a function
that you use to calculate it.
| | 03:10 | The math is fairly advanced.
| | 03:11 | You don't really to know it.
| | 03:13 | You just need to know how to
calculate continuously compounded interest.
| | 03:17 | But you multiply B4, the rate, by B5, the term.
| | 03:25 | There you have your answer, $269,971.76.
| | 03:31 | Calculating compound interest is
one of the skills you use frequently
| | 03:34 | in financial analysis.
| | Collapse this transcript |
| Applying nominal versus effective interest rates (APR versus APY)| 00:00 | Financial institutions like to
advertise the interest rates their products
| | 00:04 | return to investors.
| | 00:06 | Most ads list an annual percentage
rate, which you can use to calculate
| | 00:09 | the yearly return using the compound
interest formula you learned earlier.
| | 00:13 | Some ads, however, list an annual
percentage yield, which is the net return and
| | 00:17 | not the actual interest
rate applied to the investment.
| | 00:20 | When comparing two or more investment
opportunities, you must make sure that all
| | 00:24 | of the investments' terms are expressed
using the same type of interest rate.
| | 00:28 | Annual percentage rate
versus annual percentage yield.
| | 00:31 | There are two built-in Excel functions
you can use to convert annual percentage
| | 00:35 | yields to annual
percentage rates and vice versa.
| | 00:37 | The first is the Effect or Effective
function, which you can use to determine
| | 00:42 | the annual interest earned in a year
expressed as a percentage of the principal.
| | 00:46 | You can use the Nominal function to
determine an annual percentage rate when
| | 00:50 | presented with an annual percentage yield.
| | 00:53 | So let's take a look at the
example I have here in the worksheet.
| | 00:56 | We have two investments, one of
which has an annual percentage yield;
| | 01:00 | the other which has an annual percentage rate.
| | 01:02 | Now, the annual percentage rate of 5%
with a principal of $20 million and
| | 01:09 | a term of 10 years, I already have
enough information to calculate the future
| | 01:13 | value of the investment.
| | 01:14 | In other words, because I know the APR,
the principal and the term, I can create
| | 01:18 | a formula, which you see here on the
Formula bar, the compound interest formula,
| | 01:23 | to determine the total value of the investment.
| | 01:26 | However, with the annual percentage
yield, I do not have enough information to
| | 01:30 | calculate the future value of the investment.
| | 01:33 | Instead, I need to determine
the annual percentage rate.
| | 01:36 | So to calculate the annual percentage
rate, given an annual percentage yield,
| | 01:41 | you use the Nominal formula and include
the Effective rate, which is the APY in
| | 01:48 | cell B5 and then the number of periods per year.
| | 01:51 | In other words, if the interest is compounded
within the year, how many times is it compounded?
| | 01:57 | In this case, we are assuming
that the loan is compounded monthly.
| | 02:00 | So we use the value from cell B6 and Return.
| | 02:06 | So with an annual percentage yield of
only 5%, the annual percentage rate is 4.89%.
| | 02:11 | That leads to a future value of
the investment of $32,234,973.
| | 02:20 | Compare that to the future value
of the loan, which has the 5% APR.
| | 02:25 | The future value here is about
$300,000 higher, and when you are talking
| | 02:30 | in terms of a $20 million
investment, $300,000 is real money.
| | 02:34 | So you need to play close attention to it.
| | 02:36 | If you want to express the annual
percentage rate of 5% as an APY, to compare it
| | 02:41 | more directly to the APY of 5% from the
first investment, you can use the Effect
| | 02:46 | or Effective function.
| | 02:48 | So type =EFFECT, then you input
the nominal rate, which is in cell E5.
| | 02:57 | Number of periods per year, which is in E6,
Return and there you have an APY of 5.12%.
| | 03:07 | Every fraction of a percentage point
of interest makes a difference in the
| | 03:10 | value of a loan or an investment,
especially when the amounts are in
[00:03:14.00s]
the millions of dollars.
| | 03:15 | Take the time to ensure the
interest rates of the investments you are
| | 03:18 | considering are
expressed using consistent terms.
| | Collapse this transcript |
| Calculating the number of days between events| 00:01 | Excel 2007 gives you many tools to
calculate the number of calendar days or the
| | 00:05 | number of working days between two dates.
| | 00:07 | When you calculate the number
of working days, Excel excludes
| | 00:10 | weekends automatically.
| | 00:12 | But you also need to maintain a list of
business holidays so the functions can
| | 00:15 | leave them out as well.
| | 00:16 | I want to stress that it is absolutely
vital that you store the dates in cells
| | 00:21 | with the Time/Date format and not as text.
| | 00:25 | I have done that in this worksheet,
which I'm using as my example.
| | 00:28 | I have an Initiation Date and a
Maturity Date for an investment and you'll
| | 00:32 | notice that these cells both have the
date format that is absolutely vital.
| | 00:38 | If you want to determine the total
number of days between two dates, all you
| | 00:41 | need to do is subtract one date from the other.
| | 00:44 | So in this case, we have the later date
of December 24th that's in cell B5, when
| | 00:49 | we subtract the initiation date which is
in cell B3 and we find that we have 174
| | 00:55 | days, but that includes weekends and holidays.
| | 00:58 | How about if you want to determine the
number of working days between those two dates?
| | 01:02 | For that, you use the NETWORKDAYS function.
| | 01:05 | So you type in =NET and then you
have the start date which is the earlier
| | 01:10 | date and that is in cell B3, and end date
which is in cell B5 and then a list of holidays.
| | 01:18 | Now, I can't switch over to the
worksheet right now because I'm in the middle of
| | 01:21 | entering a formula but the holidays
are on the Holidays worksheet in a table
| | 01:26 | called Holidays2010.
| | 01:29 | So what I'll do is I'll just start
typing in the name of the table, holidays,
| | 01:32 | and there you see Holidays2010,
and they are in the Holiday column.
| | 01:36 | So I have completed the formula, hit
Return and you'll see that you have 119
| | 01:42 | working days between July
3rd and December 24th of 2010.
| | 01:48 | Now, let's say that you want to determine
the next workday after December 24th of 2010.
| | 01:54 | The next calendar day is December 25th and
that's Christmas, a holiday in the United States.
| | 01:59 | So that will not be a working day.
| | 02:01 | To find the next working day after a
given number of working days, excluding
| | 02:05 | weekends and dates identified as
holidays, you use the WORKDAY function.
| | 02:09 | Now, when I enter the formula as you
would expect, workday, start date in cell
| | 02:14 | B3, the number of days which is in cell
B9 and then the list of holidays which
| | 02:20 | are in the Holidays2010
table in the Holidays column.
| | 02:25 | You get an incorrect result.
| | 02:27 | Instead of getting the workday after
December 24th, you get the day prior to that.
| | 02:33 | So what happened?
| | 02:34 | Let's take a look at the formula
and it appears to be right, but what's
| | 02:38 | happening is that Excel is counting the
initiation date, July 3rd, as the first day.
| | 02:45 | So what you need to do is add
one to the working days total.
| | 02:50 | So that value comes from cell B9 in this case.
| | 02:53 | So you type +1, hit Return and you get
the next working day which is December
| | 02:58 | 27th, the Monday after Christmas.
| | 03:01 | Finally, some financial calculations
require you to use a 360-day calendar.
| | 03:05 | That assumes a 30-day
month and 12 months a year.
| | 03:09 | You'll use a 360-day calendar for
some bond transactions and also for
| | 03:13 | loans between banks.
| | 03:15 | To calculate the number of days between
two dates using a 360-day calendar, you
| | 03:20 | use the DAYS360 function.
| | 03:22 | So to determine the number of days
between our initiation date and our maturity
| | 03:26 | date, using a 360-day calendar, we use
the start date, E3, the end date of B5
| | 03:35 | and the method we leave blank because
we'll be using the default which is 360,
| | 03:39 | 30 days, 12 months and Return.
| | 03:43 | And we'll find that using that system,
we have 171 days between our initiation
| | 03:48 | date and our maturity date.
| | 03:50 | Working with dates in Excel does seem
confusing at first, but most of the time,
| | 03:54 | you can use the techniques in this
lesson to determine the number of days you
| | 03:57 | have to complete the task or
for an investment to mature.
| | Collapse this transcript |
|
|
8. Determining Future Cash FlowsComputing the future value of an investment| 00:01 | One of the more conservative investment
strategies available is to purchase an
| | 00:04 | instrument such as a certificate of
deposit or fixed rate annuity that enables
| | 00:09 | investors to trade lower risk for
relatively low but known rates of return.
| | 00:14 | You can evaluate this type of
investment using the future value or FV function.
| | 00:20 | The FV function takes five arguments,
rate, number of periods, payment,
| | 00:25 | present value and type.
| | 00:27 | The rate is the annual percentage rate
divided by the number of periods in a year.
| | 00:32 | Usually, that's 12 representing 12 months.
| | 00:35 | NPER is the number of periods in the
investment. That's usually the number of
| | 00:39 | years times the number of periods per year.
| | 00:42 | So a ten-year investment would have 120 periods.
| | 00:46 | PMT or Payment is the amount of money
paid into the investment each period if any.
| | 00:51 | PV is the starting value of the
investment and type indicates whether the
| | 00:55 | payment is due at the beginning of a
period, in which case the value is 0 or
| | 00:59 | omitted or at the end of the
period, in which case the value is 1.
| | 01:04 | If you leave out the Payment argument,
you must include a PV or Present Value to
| | 01:08 | indicate the starting principal balance.
| | 01:10 | So let's take a look at the
formula that I have in my worksheet.
| | 01:14 | I have a rate of 6%, a term of 5 years
and also in this case, we have a payment
| | 01:21 | of $10,000 a month and a
present value of $100,000.
| | 01:26 | So what that indicates is that we
have an initial starting balance of
| | 01:29 | $100,000 in this investment and then
we'll be adding monthly payments of
| | 01:34 | $10,000 to the investment.
| | 01:36 | Now, any money you pay into the
investment is expressed as a negative number
| | 01:40 | because it's a cash flow from your account.
| | 01:43 | Therefore, it decreases your net worth by that
amount so it's expressed as a negative number.
| | 01:48 | In this case, the payment in PV
arguments will have negative values.
| | 01:52 | If you were to take money out each
month, the PMT value would be positive.
| | 01:56 | One such case would be if you created
a retirement account and started taking
| | 02:00 | disbursement after age 55.
| | 02:01 | In that case, you would input the
present value as a negative number and express
| | 02:06 | the PMT or payment as a positive number
to indicate how much money you drew from
| | 02:11 | the account each month.
| | 02:13 | So let's see what the future
value of this investment would be.
| | 02:17 | We start with the FV function and
we begin entering our arguments.
| | 02:22 | So we have a rate in cell B3 of 6%
but in this example, interest is
| | 02:27 | compounded monthly.
| | 02:29 | So we divide by 12 the number of months in
a year, then we enter the number of periods.
| | 02:34 | That is based on the value in cell B4,
the term of the loan or the investment.
| | 02:39 | But because we are using the number of
periods as oppose to the number of years,
| | 02:42 | we multiply 5, the value in B4, by 12.
| | 02:46 | So we get the number of months the
number of times that interest is compounded.
| | 02:50 | Then we have the monthly
payment, which is in cell B5.
| | 02:53 | The present value which is in cell B6
and because we are going with the default
| | 02:57 | type where payments were due at the
beginning of the period, as oppose to the
| | 03:01 | end, we can just leave the Type
argument blank and there you have it.
| | 03:06 | If you start a loan or an investment
with the present value of $100,000 at
| | 03:11 | $10,000 per month, at an annual rate
of 6%, at the end of five years, you
| | 03:16 | will have over $832,000.
| | 03:19 | The FV function offers investors a
straightforward means of evaluating the
| | 03:22 | fixed rate investment. What's more?
| | 03:25 | It gives analyst the ability to evaluate annuities
for the beneficiary he receives periodic payments.
| | Collapse this transcript |
| Calculating present value| 00:01 | Calculating the present value of an
investment enables you to answer this question:
| | 00:05 | how much is a proposed
investment worth in today's dollars?
| | 00:08 | You can answer that question using
the PV or Present Value function.
| | 00:13 | The PV function has five
arguments Rate, NPER, PMT, FV, and Type.
| | 00:20 | Rate is the discount rate,
which I'll explain in a moment.
| | 00:24 | NPER is the number of periods.
| | 00:26 | PMT or payment is the amount of money
deposited into the investment each period.
| | 00:31 | In other words, if you have a one
year investment and you make monthly
| | 00:34 | contributions of $1,000,
that would be the PMT value.
| | 00:39 | FV is the value of the investment
when it reaches maturity, and the Type
| | 00:44 | indicates whether the payments are due
at the end of a period in which case, the
| | 00:47 | value is zero, or you can leave it out,
or at the start of the period in which
| | 00:51 | case you put in the value of one.
| | 00:53 | If you are more comfortable seeing all
of the values as positive numbers, you
| | 00:57 | can multiply the result of the PV formula by -1.
| | 01:00 | As an example, consider a one year
investment where you are asked to pay $9,500
| | 01:05 | for a return of $10,000 after 12 months.
| | 01:09 | If you assume you can generate 6%
return from risk-free investments, you would
| | 01:13 | create this formula in cell B7.
| | 01:15 | That is =PV for present value, B3,
the rate, divided by 12, because we are
| | 01:23 | assuming interest is compounded monthly.
| | 01:25 | B4, the number of periods, but B4 is the
number of years, so we need to multiply
| | 01:32 | it by 12, again to account
for the number of months.
| | 01:34 | We are not making any payments, so
we'll leave the payment argument blank, and
| | 01:39 | we have a target future value
of $10,000, which is cell B5.
| | 01:44 | And we can leave the Type argument
blank because we are assuming that all
| | 01:47 | payments will be at the beginning of the period.
| | 01:49 | When we press Return, we see
that the present value is $9,400.05.
| | 01:54 | And again, because this calculation
represents a cash flow from you as opposed
| | 02:01 | to you, it's expressed as a negative value.
| | 02:04 | So the present value of this investment
is $9,400.05, because the asking price
| | 02:11 | is greater than the investment's present value.
| | 02:13 | You should pass on the opportunity.
| | 02:15 | Evaluating an investment using the Present
Value function makes your decisions easier.
| | 02:19 | Once you establish a discount rate,
which you and your colleague should
| | 02:22 | reevaluate frequently to account for
changes in the market, you can run the
| | 02:26 | numbers and arrive at a
clear yes or no decision.
| | Collapse this transcript |
| Calculating net present value| 00:01 | The net present value of an investment
is the present value of the investment
| | 00:04 | minus the amount of money
accosted by into the investment.
| | 00:08 | All of the investment's cash flows
must occur at the same interval for the
| | 00:11 | calculation to be accurate.
| | 00:13 | In other words, if you have
investments on a monthly basis, then all of them
| | 00:17 | must be on a monthly basis.
| | 00:19 | You calculate an investment's net
present value using the NPV function.
| | 00:24 | This function has two arguments:
| | 00:26 | Rate, which is at the discount rate or
the rate of return that you could gain
| | 00:30 | from a risk-free investment, and
also a range of values that contain the
| | 00:35 | investment's future cash flows.
| | 00:37 | You can list up to 254 cells
that contain cash flow values.
| | 00:41 | As always, you should be sure to adjust
the rate to account for how many times
| | 00:45 | per year interest is compounded.
| | 00:47 | In most cases, interest is compounded
monthly, so you would divide the rate by 12.
| | 00:52 | By accounting convention, if you are
required to pay for the investment at the
| | 00:55 | start, don't include the payment in
the array of future cash flow values.
| | 00:59 | Instead, you should subtract the
investment amount from the total future cash flows.
| | 01:04 | In keeping with standard accounting
practice, you should enter the investment
| | 01:08 | amount as a negative number and
add each of the NPV formula's output.
| | 01:12 | Let's evaluate the two investments
that I have summarized in this worksheet.
| | 01:15 | To do that, we'll create an NPV formula,
and we are assuming a discount rate of
| | 01:21 | 4%, which is in cell B3.
| | 01:24 | Because we are assuming that interest is
compounded monthly, we'll divide that by 12.
| | 01:29 | Now we can enter the values, the cash flows.
| | 01:32 | The positive cash flows
are in cells, A8 through A12.
| | 01:37 | Close the parenthesis and then we
add the initial investment, which is
| | 01:41 | $100,000 in the cell A7.
| | 01:45 | Hit Enter and we have a net present
value for this investment of $23,759.66.
| | 01:48 | Now let's do the same thing
for the second investment.
| | 01:55 | We'll create another NPV formula.
| | 01:58 | Again, we are taking our rate from B3,
dividing by 12 for the number of months in a year.
| | 02:02 | Our positive cash flows, cash flows to
us, run from D8 to D13, and then, we add
| | 02:10 | the initial investment which is -$100,
000 representing a cash flow from us.
| | 02:15 | Hit Return, and we see a net
present value of $23,513.51.
| | 02:22 | Because the cash flows for investment
1 generate a greater net present value,
| | 02:26 | that's the investment you should go with.
| | 02:28 | Now we are assuming that both
investments have the same level of risk.
| | 02:32 | When you use NPV to compare several
investments, if all of the investments
| | 02:36 | have the same level of risk, then you should go
with the investments with the higher net present value.
| | Collapse this transcript |
| Calculating internal rate of return| 00:00 | Calculating an investment's internal
rate of return enables you to identify
| | 00:04 | the interest rate at which the
investment's future cash flows have a net
| | 00:08 | present value of zero.
| | 00:10 | In other words, your formula tells
you the discount rate at which you would
| | 00:13 | break even on a given investment.
| | 00:16 | If the IRR formula returns a value
greater than the interest rate generated by
| | 00:20 | risk-free investments,
you should take the plunge.
| | 00:22 | If not, then you should pass on the opportunity.
| | 00:25 | The IRR function has two arguments, a
range of values that reflects future cash
| | 00:30 | flows, and optionally, a
guess at the rate of return.
| | 00:34 | Excel starts by guessing at a rate of 10
%, which will work in most cases, but in
| | 00:38 | extreme cases, you should start the
guessing at a higher or lower value.
| | 00:42 | The investment cash flows may be
positive representing income to you, or
| | 00:46 | negative representing an expense to
you but they must all occur at regular
| | 00:50 | intervals, such as monthly or annually.
| | 00:53 | So, let's take a look at the two
investments that I have summarized in my worksheet.
| | 00:58 | We have two investments, one, with
an initial investment of $100,000 and
| | 01:04 | payments of these amounts for five
years, then we have the same $100,000
| | 01:09 | investment but we have payments over 6 years.
| | 01:11 | So the idea is to determine which of the
two has the better internal rate of return.
| | 01:16 | To do that, we input our IRR
formulas and the values go from A4 to A9.
| | 01:24 | I won't put it in a guess because I
think Excel's default starting value of 10%
| | 01:28 | will allow it to find the answer that it needs.
| | 01:31 | So I'll just close the parenthesis, hit
Return, and we see that this investment
| | 01:36 | has an internal rate of return of 5.73%.
| | 01:38 | Now, let's do the same thing for the
second investment, =IRR, and the values are
| | 01:46 | in the range of D4 to D10.
| | 01:50 | Again, I'm not going to put it in the guess
because I think the default value is fine.
| | 01:54 | I'll close the parenthesis, hit Return,
and we see that the second investment
| | 01:58 | has an internal rate of return of 5.47%.
| | 02:02 | So we should go with the first investment.
| | 02:05 | If an investment's internal rate of
return is higher than the discount rate, you
| | 02:08 | assume for your risk-free
investments, such as your treasury bills.
| | 02:11 | You should go ahead with the new
plan assuming the risks of the new
| | 02:14 | investment aren't too great.
| | 02:16 | The riskier in investment, the more
you should adjust the internal rate of
| | 02:20 | return to account for the
possibility that the investment might fail.
| | Collapse this transcript |
| Calculating NPV and IRR for uneven input periods (XNPV and XIRR)| 00:01 | Many businesses combine cash flows from
several sources, such as asset sales or
| | 00:05 | retail revenues, to fund other investments.
| | 00:09 | These revenues don't always come in on
a regular monthly or annual schedule, so
| | 00:13 | you can't use the NPV or IRR
functions to calculate net present value and
| | 00:17 | internal rate of return.
| | 00:19 | Excel does have two functions, XNPV and
XIRR, that enable you to calculate net
| | 00:25 | present value and internal rate of return for
cash flows that occur at irregular intervals.
| | 00:30 | The XNPV function has three arguments,
the discount rate, the cash flows, and
| | 00:35 | the dates the cash flows occur.
| | 00:37 | XIRR also has three arguments:
the cash flows and the dates of those cash flows,
| | 00:42 | both of which are required, and if you
want, a guess at the internal rate of return.
| | 00:47 | Both the XNPV discount rate and your
guess, if any, at the internal rate of
| | 00:51 | return, should be expressed
as annual percentage rates.
| | 00:54 | So let's take a look at
the two examples I have here.
| | 00:57 | Actually, it's the same example twice.
| | 00:59 | We are just going to calculate
XNPV for one and XIRR for the other.
| | 01:03 | So to calculate XNPV, type =XNPV, put
in the rate, which is in cell B5,
| | 01:12 | which we divide by 12 because we assume
interest will be compounded monthly.
| | 01:16 | Now we enter the values for the cash flows.
| | 01:19 | Those run from B8 to B16.
| | 01:24 | Now we can enter the dates, those go
from A8 to A16, close the parenthesis, and
| | 01:30 | we have an XNPV or net
present value of $24,158.80.
| | 01:34 | Now let's take a look at the same
investment but this time in terms of
| | 01:40 | its internal rate of return.
| | 01:42 | Type =XIRR. We have the values from
cell E8 to E16, the dates from cell D8 to D16.
| | 01:57 | We'll leave out a guess.
| | 01:58 | I think Excel's default
starting value will be fine.
| | 02:01 | Close the parenthesis.
| | 02:02 | Hit Return.
| | 02:04 | This investment has an
internal rate of return of 10.77%.
| | 02:08 | You are very unlikely to find any sort of a
guaranteed investment with that high of a rate.
| | 02:13 | So it looks like this investment is a
good deal, assuming the risks are in line
| | 02:16 | with what your investment strategy holds.
| | 02:19 | Whenever you need to evaluate
investments, where cash flows occur at irregular
| | 02:22 | intervals, use the XNPV and XIRR functions.
| | Collapse this transcript |
|
|
9. Forecasting Future GrowthProjecting future results using the Forecast function| 00:00 | Financial analysts are fond of saying that past
performance is no guarantee of future results.
| | 00:06 | That's true.
| | 00:07 | But it does make sense to see how a
business's sales, expenses, or market
| | 00:11 | share have progressed over time so you can
make judgments about the company's prospects.
| | 00:15 | If you have a data series that
includes past results, you can use the
| | 00:19 | Forecast function to create a formula to
estimate future values if the trend stays consistent.
| | 00:24 | The Forecast function has three
arguments: X, which is the value often a year
| | 00:29 | for which you want to project a result,
known_y's, which is the set of known
| | 00:34 | results such as sales, and known_
x's, which indicate when the known_y
| | 00:39 | measurements were taken.
| | 00:40 | It's interesting, but the X
values can be in any order.
| | 00:44 | They don't have to be sorted into
ascending or descending order, and you can
| | 00:48 | also skip values in the series.
| | 00:50 | For example, with the numbers in this
worksheet, we could skip the year 2009,
| | 00:55 | and still create a projection for
2010 using the Forecast function.
| | 00:59 | So let's see how the forecast formula will work.
| | 01:03 | Just type in =FORCAST, our X value is
2010, which is in cell A11 and that is the
| | 01:13 | year for which we want to project a value.
| | 01:16 | Our known_y's are the variable
measurements and in this case, those are the
| | 01:20 | sales revenue figures in cells B6 through B10.
| | 01:25 | And our known_x's are the years in
which those measurements were taken, and
| | 01:30 | those are in A6 through A10.
| | 01:34 | Those are all of the
arguments we need for the formula.
| | 01:37 | Close the parenthesis and
there we have our projection.
| | 01:41 | Based on current trends, the sales for
the year 2010 will be 46,265 multiplied
| | 01:48 | by 1,000 because all of the values in
this worksheet are expressed in terms of
| | 01:52 | 1,000s, which means you can
interpret this value as 46,265,000.
| | 01:58 | Excel uses linear regression
techniques to project future values based on
| | 02:02 | the past measurements.
| | 02:03 | The math is not that difficult to work
through, but you don't need to bother.
| | 02:06 | Excel does all the work for you.
| | Collapse this transcript |
| Performing quick forecasts using the Fill handle| 00:01 | If your worksheet contains a data
series that you would like to extend into the
| | 00:04 | future, you can select the cells
containing the existing values and use the Fill
| | 00:08 | Handle to extend the series.
| | 00:10 | To extend the series using the Fill
Handle, select the cells containing the data
| | 00:14 | series you want to extend, grab the
Fill Handle at the bottom right of the
| | 00:17 | selection, and drag.
| | 00:19 | So, I'll show you how to do that here
with the data in cells B8 through B12.
| | 00:26 | So we have the black selection outline,
and here at the bottom right corner,
| | 00:30 | we have what's called the Fill Handle.
| | 00:31 | If you move the mouse pointer over the
top of it, it turns into a black cross
| | 00:36 | and you can drag it down.
| | 00:37 | And Excel extends the data
series for you. I'll click Undo.
| | 00:42 | I'll come back to this in a moment.
| | 00:43 | I would like to note that
you can go forward or backward.
| | 00:48 | So for example, if the sales revenue
cell were blank, I can select here and
| | 00:54 | move up, and you'll see that Excel will
extend the series backwards in time if you want.
| | 00:58 | I do want to emphasis that this
technique is only accurate when you have a data
| | 01:03 | set with no missing values.
| | 01:05 | As compared to the Forecast function,
which interpolates a y value for any x
| | 01:09 | values that are skipped in the
data set, the Fill Handle does not.
| | 01:13 | So I'd like to show you the error in
this data table, and that is that we have
| | 01:16 | the years 2005 to 2008, but we skipped 2009.
| | 01:21 | That means that dragging the Fill
Handle just works with the existing data and
| | 01:26 | it does not attempt to do any sort of
calculation to figure out what the year
| | 01:29 | 2009 would or should have been.
| | 01:31 | I will show you over here, how the
Forecast function handles the same data set.
| | 01:37 | So we have =FORECAST, x is the year 2011
which is in cell D13, the known_y's are
| | 01:44 | in E8 through E12 and the
known_x's are in D8 through D12.
| | 01:53 | Close the parenthesis and
there you have the value of 43,660.
| | 01:57 | Now when you compare that with the Fill
Handle value, 46,265, you see that they
| | 02:05 | are two different values even though
the inputs are exactly the same, and that
| | 02:09 | is because the two functions, FORECAST
on the right, the Fill Handle drag-select
| | 02:14 | on the left, use different techniques.
| | 02:17 | So now let's see what happens if I change
the years to 2009 and 2010 in both cells.
| | 02:25 | I'll just do 2010 and 2009, and I'll
delete the Fill Handle value there.
| | 02:31 | So we have our forecast value of 46,265,
and if I drag the selection over here,
| | 02:36 | you will just see that we get the same value.
| | 02:39 | And again, the difference is just in
the way that the Fill Handle extension and
| | 02:43 | the Forecast formula treat missing values.
| | 02:46 | Extending a data series using the
Fill Handle is a quick way to predict the
| | 02:50 | future value, but you must be certain
that you are working with an uninterrupted
| | 02:54 | data set for the results to be accurate.
| | Collapse this transcript |
| Adding a trendline to a chart| 00:01 | When you analyze financial data,
you should pay close attention to the
| | 00:04 | individual values within your data sets.
| | 00:06 | Even so, it's often beneficial to
take a step back and look at the overall
| | 00:10 | trends in your data.
| | 00:11 | If you summarize your data using a
chart, you can project future values
| | 00:15 | by adding a trendline.
| | 00:17 | A trendline only exists within the
chart and the Excel program memory.
| | 00:21 | It doesn't actually add
any data to your worksheet.
| | 00:24 | So if you want to add a trendline to a
chart, all you need to do is click the
| | 00:29 | data series in the chart and this is important.
| | 00:31 | You don't want to click somewhere in
the chart, just selecting the chart area.
| | 00:36 | Instead, you want to select the
data series and make sure that the data
| | 00:40 | points are highlighted.
| | 00:41 | That's how you know you
are in the right position.
| | 00:43 | As soon as you select the data series,
under Chart Tools, go to Layout, and then
| | 00:49 | in the Analysis group, click
Trendline, and click Linear Trendline.
| | 00:55 | Excel adds the trendline to the
chart so you can see what the average
| | 00:58 | progression will have been.
| | 01:00 | So how do we extend this
trendline into the future?
| | 01:03 | Well first we'll remove the
existing trendline and then with the data
| | 01:07 | series still selected, Chart Tools>
Layout, click Trendline, and then click
| | 01:13 | More Trendline Options.
| | 01:15 | The Trendline Options dialog box
gives a lot of tools that you can use to
| | 01:19 | create a trendline, and there are
different mathematical techniques that you
| | 01:22 | can use to extend the line.
| | 01:23 | Unless you are told differently,
you should always use Linear.
| | 01:27 | The other techniques are
mostly useful in scientific and
| | 01:30 | engineering applications.
| | 01:32 | Now you need to determine how far into
the future you want to create your forecast.
| | 01:36 | To do that, you go down here to the
forecast area, and in the Forward box, you
| | 01:41 | type in how many periods you
want to forecast into the future.
| | 01:46 | The data on this chart is annual, so
our forecast will be for two years.
| | 01:51 | We have that there, two years, click
Close, and Excel updates the trendline.
| | 01:56 | So that now you have the series
extended two years into the future.
| | 02:01 | Your data's measurements should
occur at regular intervals so that your
| | 02:04 | forecasting a given number of time
periods into the future makes sense.
| | 02:08 | In other words, you don't want
some monthly measurements and some
| | 02:11 | annual measurements.
| | 02:13 | Trendlines help you
visualize future trends in your data.
| | 02:16 | Summarizing data using a chart
shouldn't replace detail analysis, but it does
| | 02:21 | help set the stage for your
presentations regarding that data.
| | Collapse this transcript |
|
|
10. Determining Amortization and Depreciation SchedulesIntroducing amortization| 00:00 | When you work with financial transactions,
you will often encounter the term Amortization.
| | 00:05 | It sounds complicated, but
amortization is simply the gradual repayment of
| | 00:09 | a debt in over time.
| | 00:11 | Loans can be fully amortized,
partially amortized, or negatively amortized.
| | 00:17 | A fully amortized loan has payments
that will pay off the entire principal and
| | 00:21 | accumulated interest at
the end of the loan's term.
| | 00:24 | A partially amortized loan has payments
that will pay off only a portion of the
| | 00:28 | principal and accumulated interest.
| | 00:30 | Many such loans have a large payment called a
balloon payment after a set number of years.
| | 00:35 | In the case of a home loan,
which usually runs 30 years,
| | 00:39 | a borrower might have a balloon
payment due at the end of the 15th year.
| | 00:43 | You can also have balloon
payments due at the end of loan's term.
| | 00:46 | As an example of how amortization works,
let's take a look at the example in the worksheet.
| | 00:52 | So we have a $25,000,000 loan,
probably not a home loan, at the rate of 4.9%,
| | 00:58 | and it is going to be paid
back over a term of 10 years.
| | 01:01 | In the first example, we'll assume the
loan is fully amortized, and it will be
| | 01:06 | paid off at the end of the 10 year term.
| | 01:08 | In the second, that there is a
Balloon Payment of $8,000,000 due at the end
| | 01:12 | of the loan's term.
| | 01:13 | When you calculate the monthly
payments on the fully amortized loan, you will
| | 01:17 | see that there will be $263,943.49 per month.
| | 01:22 | On the other hand, when you have an $8,
000,000 Balloon Payment at the end of the
| | 01:26 | loan's term, you have a
monthly payment of $212,148.24.
| | 01:29 | Now that difference of about $51,000
could make a very big difference for a
| | 01:37 | company in terms of cash flow.
| | 01:39 | If they borrow the $25,000,000, and
need to save every penny to make that
| | 01:43 | investment work, then the $51,000 a
month might make a huge difference.
| | 01:48 | Amortization or the repayment of a debt in
over time is the key concept behind loans.
| | 01:54 | The borrower gets the use of the
capital immediately, while the lender makes
| | 01:57 | interest over the term of the loan.
| | 02:00 | Most loans will be fully amortized,
but there are still quite a few that are
| | 02:03 | only partially amortized, and will
have an unpaid balance unless the borrower
| | 02:07 | makes a large payment to
make up the unpaid amount.
| | 02:10 | A negatively amortized loan is a loan
where the monthly payments do not cover
| | 02:14 | the loans interest for that month.
| | 02:16 | This type of lending is
considered predatory and should be avoided.
| | Collapse this transcript |
| Calculating payments on a fully amortized loan| 00:01 | Most loans, whether between
businesses or made by businesses to individual
| | 00:05 | borrowers, are fully amortized.
| | 00:07 | Fully amortized means that the monthly
payments made over the term of the loan
| | 00:11 | pay off the principal and all accrued interest.
| | 00:14 | You can calculate the monthly payments
required to pay off a fully amortized
| | 00:18 | loan using the PMT or Payment function.
| | 00:21 | The PMT function has five arguments.
| | 00:24 | The first three arguments, rate,
nper, and pv, are all required.
| | 00:29 | Rate is the annual percentage rate divided
by the number of payments made each year.
| | 00:33 | That's usually 12, representing the 12 months.
| | 00:36 | nper is the number of payments you
will make on the loan, and present value
| | 00:40 | is the loan principal.
| | 00:42 | The other two optional
arguments are fv and type.
| | 00:46 | fv is the future value of the loan,
which is 0 in a fully amortized loan,
| | 00:50 | because you pay off the entire amount,
and type is zero if your payment is due
| | 00:55 | at the end of a period,
| | 00:56 | that is in arrears or at the
beginning of the period, in advance.
| | 01:01 | Most loans are made in arrears and result in
a zero balance at the end of the loan's term.
| | 01:06 | So you will usually leave the fv
and type arguments out of the formula.
| | 01:11 | So just create the formula here.
| | 01:13 | We have payment, our rate is in cell B5,
which is 7% divided by 12, again, the
| | 01:22 | number of months in a year, and
interest we assume will be compounded monthly.
| | 01:25 | nper is the number of
payments you will make on the loan.
| | 01:29 | So that is the value of B7, multiplied
by 12, again for months in the year, the
| | 01:34 | number of payments you will make in a
year, and the pv or present value of the
| | 01:38 | loan is the principal and that is in cell B3.
| | 01:41 | So you can see that on a $30,000,000
loan at 7% rate, and paid off monthly over
| | 01:48 | 12 years, you will be making
monthly payments of $308,514.
| | 01:55 | As always, make sure you setup your
worksheet, so the inputs are on a separate
| | 01:58 | and easily identified area, and use
cell references to create your formula.
| | 02:04 | It's important to remember that the
PMT function only calculates the monthly
| | 02:07 | payment to cover principal and interest.
| | 02:10 | Many loans have other charges included,
such as property taxes, and perhaps
| | 02:13 | private mortgage insurance for home loans.
| | 02:16 | So be sure to include those other fees and
charges when you calculate your total loan payment.
| | Collapse this transcript |
| Calculating payments on a partially amortized loan (balloon payments)| 00:01 | Just like when you determine
payments for a fully amortized loan, you can
| | 00:04 | use the PMT or Payment function to
determine payments for a partially amortized loan.
| | 00:09 | If you want the lump sum or balloon
payment to be due at the end of the loan's term,
| | 00:13 | you can put the balloon payment
in the PMT functions, fv or future value
| | 00:18 | argument, and then build the formula normally.
| | 00:21 | So in this example, we have a loan of
$30,000,000 with an annual interest rate
| | 00:26 | of 7%, a term of 12 years, and a
Balloon Payment at the End of $10,000,000.
| | 00:32 | So you will be paying out $20,000,000.
| | 00:35 | That's 30,000,000 minus 10,000,000
over the 12 years, and then paying the
| | 00:39 | $10,000,000 in one lump sum at the end.
| | 00:42 | So to create the formula, I have =PMT
and our five arguments are the rate, the
| | 00:49 | number of payments, the present value
of the loan, which is the principal, the
| | 00:53 | future value, which is the balloon
payment at the end, and the type which is
| | 00:57 | whether payments are due at the
end of a period or at the beginning.
| | 01:01 | In this case, the type is zero,
so we'll be able to leave it out.
| | 01:05 | So we type in the rate, which is in cell
B5 and we divide it by 12, which is the
| | 01:11 | number of payments per year.
| | 01:13 | The number of payments is in cell B7,
and we multiply by 12, again for each
| | 01:19 | month for a monthly payment.
| | 01:21 | Present value is the
principal that's in cell B3.
| | 01:24 | Future value is in cell B9,
and that is $10,000,000.
| | 01:29 | Now note that because the balloon
payment is a negative cash flow to you it's
| | 01:35 | expressed as a negative number.
| | 01:36 | We will close the parenthesis,
because we don't have to put in the type
| | 01:39 | argument, hit Return, and you can
see the Monthly Payment on this loan is
| | 01:44 | $264,010 per month, with a $10,000,000
payment due at the end of the loan's term.
| | 01:51 | Loans with large balloon payments are
extremely risky for the borrower, because
| | 01:55 | external factors such as economic
downturns, labor actions, and natural
| | 01:59 | disasters can prevent them from earning
the revenue required to make the balloon
| | 02:02 | payment when it comes due.
| | Collapse this transcript |
| Calculating interest and principal components of loan repayments| 00:00 | Calculating the amount of principal
you've paid on a loan enables you to
| | 00:04 | determine the amount of
equity you have in a purchase.
| | 00:07 | Your equity is your down payment, plus
any loan principal you've paid, plus the
| | 00:11 | asset's appreciation, if any, and also
what your new monthly payment would be if
| | 00:15 | you refinance the loan.
| | 00:17 | Calculating the interest paid on the
loan enables you to find the amount of
| | 00:20 | interest you've paid in the fiscal year,
which you can often write off in your tax return.
| | 00:25 | You can determine the amount of
interest included in a particular loan payment
| | 00:28 | using the IPMT function.
| | 00:31 | IPMT simply stands for Interest Payment.
| | 00:34 | Start the IPMT function
and you have six arguments.
| | 00:38 | The rate, the number of the period for
which you are calculating the interest,
| | 00:43 | the number of periods in the entire loan,
the present value of the loan, which
| | 00:47 | is simply the principal, the future
value of the loan, which is any amount that
| | 00:51 | will be unpaid or that you will have
paid in excess at the end of the loan's term,
| | 00:55 | and also the type.
| | 00:57 | Type is either zero or omitted, in
which case you make your payments in arrears
| | 01:02 | or at the end of a period, or one,
in which you make your payments at the
| | 01:06 | beginning of a period.
| | 01:07 | In almost every case, you will be making
your payments at the end of the period,
| | 01:11 | so you would leave that argument blank.
| | 01:13 | So, in this case, we have the
interest payment, we have the rate in B5, and
| | 01:21 | because we're going to be copying this
formula down the column in the table,
| | 01:26 | we want to leave that cell static or
keep it as an absolute reference, then
| | 01:32 | divide by 12, so we have the proper
rate as divided by the number of months,
| | 01:38 | and then we have the period.
| | 01:39 | The period for this payment is in A12
and we can leave this Cell Reference as a
| | 01:46 | relative reference, so it does change
when we copy the formula down the column.
| | 01:50 | The number of periods is the loan's term,
in this case 30 years, multiplied by
| | 01:56 | 12, which is the number of months in a year.
| | 02:00 | So we have B7, again, we don't want this
Cell Reference to change, so we make it
| | 02:04 | an absolute reference by
pressing F4, multiplied by 12.
| | 02:09 | The present value of the loan is
the principal. That's in cell B3.
| | 02:14 | Again, F4 to make it an absolute reference.
| | 02:17 | We're paying off the entire loan and
we're making our payment at the end of the
| | 02:21 | period, so we can leave the
future value and Type arguments blank.
| | 02:24 | Hit Return and there we have the amount of
interest paid in each of these loan payments.
| | 02:30 | You'll notice that the values are in
red, which means they're negative values
| | 02:34 | representing a cash flow from
you, an expense, in other words.
| | 02:39 | We can do something similar for
principal, to calculate the principal paid
| | 02:43 | in each loan payment.
| | 02:45 | For that, we use the PPMT formula,
and that is Principal Payment.
| | 02:51 | It has the same arguments as the IPMT function.
| | 02:55 | It's the rate, which is in B5, again,
F4 to keep it constant, divide by 12, the
| | 03:03 | period in A12, the number of periods
is B7, absolute reference so it doesn't
| | 03:10 | change, times 12, and then the present
value of the loan, which is in cell B3.
| | 03:16 | F4 to make it an absolute reference.
| | 03:20 | Close it, and there you have the
principal paid in each of these 12 loan payments.
| | 03:26 | You can also determine the cumulative
interest and principal you've paid on a
| | 03:29 | loan by using the
CUMIPMT and CUMPRINC functions.
| | 03:34 | The CUMIPMT and CUMPRINC functions
require you to enter the periods for which
| | 03:39 | you want to calculate
interest and principal paid.
| | 03:42 | For example, in this case, if you
borrow $500,000 at 4.5% and you want to
| | 03:47 | determine how much interest and
principal you will pay over the first year, you
| | 03:50 | can create these formulas.
| | 03:52 | For interest, you will create the
formula =CUMIPMT, have the rate in B5, divide
| | 04:00 | it by 12, the number of periods is the
loan's term in B7, times 12, the present
| | 04:10 | value of the loan, which is in the cell B3.
| | 04:13 | The start period, which because we're
looking at the first year of the loan is
| | 04:16 | one, the first payment, and
then the end period, which is 12.
| | 04:21 | Again, we can leave Type blank,
because we're paying in arrears.
| | 04:24 | So, we enter a type of zero and hit
Return and you'll see that we'll have paid
| | 04:29 | cumulative interest in
the first year of $22334.99.
| | 04:34 | If you want to do the same thing to
calculate the amount of principal you've
| | 04:38 | paid, you enter the CUMPRINC function.
| | 04:43 | You have a rate in cell B5, divided by
12 for the number of payments during a
| | 04:48 | year, the number of periods, which is
the term, B7, times 12, the principal of
| | 04:55 | the loan or the present value, which
is in cell B3, the start period, again
| | 05:00 | that's 1, the end period of 12,
and the type, which is zero.
| | 05:05 | Close the parenthesis and you'll see that
you've paid just over $8,066 against principal.
| | 05:13 | I'll format that value as an accounting
value, so you can see it as a currency value.
| | 05:19 | Calculating the amount of principal
and interest you have or will pay off on
| | 05:23 | your loans reflects both the equity you
have in your purchases and the amount of
| | 05:27 | money you can write off on your taxes.
| | 05:29 | Be sure to watch your interest expenses closely.
| | Collapse this transcript |
| Introducing depreciation| 00:01 | Almost without exception, companies
purchase physical assets to help them do business.
| | 00:05 | Whether the purchases are
relatively small such as computers and
| | 00:08 | printers or large such as company cars
and office buildings, these assets will
| | 00:13 | go down in value over time.
| | 00:15 | Reduction in value due to
age is called depreciation.
| | 00:19 | There is a difference between
the physical life of an asset and
| | 00:22 | the economic life of an asset.
| | 00:24 | Physical life is the amount of time an
asset will perform its function, i.e.,
| | 00:29 | how long will a building be standing
and safe to occupy or a printer will turn
| | 00:33 | out page of reasonable quality.
| | 00:35 | Economic life on the other hand refers
to how long the asset will perform its
| | 00:39 | function until it is rendered
obsolete by wear and tear or falls so far
| | 00:43 | behind the current technology that
it's worth no more than what it could
| | 00:46 | be sold for as scrap.
| | 00:48 | According to the Modified Accelerated
Cost Recovery System, an office building
| | 00:53 | has an economic life of 39 years,
while our computer or printer will have an
| | 00:57 | economic life of five years.
| | 00:59 | You can find more information about
MACRS in IRS Publication 946, available
| | 01:05 | on the IRS website.
| | 01:07 | At the end of an asset's economic life,
it has what is called a salvage value.
| | 01:11 | For example, if you have an old
printer that you want to sell, you can either
| | 01:15 | sell the printer at a substantial
discount or have the technician pull out
| | 01:19 | the usual parts and sell them to other repair
shops that service the same model of printer.
| | 01:24 | There are many different types of schedules
that you can use to account for depreciation.
| | 01:28 | Some of these techniques depreciate
an asset more quickly than others.
| | 01:32 | Why would you use an
accelerated depreciation schedule?
| | 01:34 | Perhaps because you expect to sell the
building after using it for a few years.
| | 01:38 | Because you would hold on to the
property for far less than its economic life,
| | 01:41 | it would be to your
advantage to capture the tax benefits of
| | 01:45 | depreciation as soon as possible.
| | 01:47 | Accountants have developed a wide variety of
depreciation schedules for use by businesses.
| | 01:52 | The schedules vary in the speed at
which the assets are assumed to depreciate.
| | 01:56 | Each approach has specific
financial advantages and disadvantages.
| | Collapse this transcript |
| Calculating straight line depreciation| 00:00 | The most straightforward method you
can use to calculate depreciation is the
| | 00:04 | straight-line method.
| | 00:06 | As the name implies, the straight-line
method assigns depreciation evenly over
| | 00:10 | the economic life of an asset.
| | 00:13 | The starting amount is the asset's
purchase price or initial cost, and
| | 00:17 | the ending value is the asset's salvage value.
| | 00:21 | If your asset has an economic life of
ten years and the salvage value of zero,
| | 00:25 | for example, the asset will depreciate by
10% each year, until its value reaches zero.
| | 00:31 | To calculate straight-line
depreciation, you use the SLN function.
| | 00:36 | The SLN function calculates the
amount of depreciation for a given period,
| | 00:40 | which is usually a year.
| | 00:43 | The SLN function has three arguments:
the initial cost of the asset, which is in
| | 00:49 | cell B5, the salvage value of the asset,
which is in cell B7, in this case, and
| | 00:57 | the economic life of the
asset, which I have in cell B9.
| | 01:02 | Close the parenthesis and there is the value.
| | 01:05 | You'll see that in each of the five
years of this asset's economic life, it will
| | 01:10 | depreciate by $230 until it
reaches its salvage value of $150.
| | 01:16 | Straight-line depreciation is the most
conservative approach you can take to depreciation.
| | 01:21 | It assumes that you will hold on to the
asset until the end of its economic life,
| | 01:24 | so you should spread out the tax benefits of
the depreciation while you still own the asset.
| | Collapse this transcript |
| Calculating declining balance depreciation| 00:00 | The Declining Balance method of
calculating depreciation accelerates the rate at
| | 00:05 | which an asset loses its value over time.
| | 00:08 | You use the DB function to
calculate depreciation in Excel.
| | 00:12 | The Declining Balance method
reduces an assets value by the amount it
| | 00:15 | depreciated in the previous years.
| | 00:18 | Excel then calculates the new
depreciation based on that lower value.
| | 00:21 | Hence the name Declining Balance Method.
| | 00:24 | The DB function has four arguments,
Cost, Salvage Value, Economic Life
| | 00:30 | and Period.
| | 00:32 | The cost is the asset's initial cost.
| | 00:34 | Salvage Value is what the
asset will sell for when it becomes
| | 00:37 | economically obsolete.
| | 00:39 | Economic Life is the number of years
that the asset will be economically viable
| | 00:42 | and Period is the time frame for
which you are calculating depreciation.
| | 00:46 | Unless you are dealing with very
short-lived assets, that period is
| | 00:49 | almost always a year.
| | 00:50 | So, to complete the formula we have =DB,
the Initial Cost is in cell B7, and
| | 00:59 | because we are copying the formula
further down, the column in the table, we'll
| | 01:03 | make that an absolute reference by pressing F4.
| | 01:06 | We have the Salvage Value in cell B9
and make it an absolute reference, the
| | 01:12 | Economic Life, B11, same thing,
make it an absolute reference.
| | 01:16 | The period is in cell A16.
| | 01:21 | And because we are copying the formula
down and we want the formula to draw its
| | 01:26 | value from the cell directly to the left,
such as cell A16 in this case, we'll
| | 01:30 | leave that as a relative reference.
| | 01:32 | And you don't need to specify a
month, because the period is one year.
| | 01:38 | We can close the parenthesis.
| | 01:40 | Press Enter.
| | 01:41 | And you can see the amount of
depreciation each year using the Declining
| | 01:45 | Balance Method.
| | 01:46 | But remember that the second part of
the Declining Balance Method is that you
| | 01:50 | need to know an asset's New
Value at the end of each year.
| | 01:53 | So, now that we have the yearly
depreciation we can calculate the new value, and
| | 01:59 | that formula is =B7, which is the Initial Cost.
| | 02:03 | We'll make that an absolute reference
by pressing F4 and we subtract the sum of
| | 02:08 | all of the previous
depreciation amounts from the Initial Cost.
| | 02:13 | To do that, we have SUM B16, which
is the first depreciation amount.
| | 02:21 | We make this cell reference an absolute
reference so it will not change, then a
| | 02:26 | colon to indicate we are
entering a range and then B16.
| | 02:29 | Now, we have entered B16 twice and the
reason is because we want to create a
| | 02:35 | range that will change as the
formula is copied down the column.
| | 02:40 | So, the first B16 is an absolute
reference, so it will not change.
| | 02:44 | The second reference to B16 is left as
a relative reference so it will change.
| | 02:50 | In other words, in cell C17, it would
be the SUM of B16 to B17, and you'll see
| | 02:57 | how that works in a second.
| | 02:58 | When I close the parenthesis and hit return.
| | 03:02 | You have the depreciation, which is
subtracted from the Initial Cost, and you
| | 03:06 | have the New Value in cell C16,
C17 and so on down to C22.
| | 03:12 | The Declining Balance Depreciation
Method enables companies to capture more
| | 03:16 | depreciation benefits early
in an assets economic life.
| | 03:19 | Decreasing tax payments and thereby
freeing up capital to invest in other areas.
| | Collapse this transcript |
| Calculating double declining balance depreciation| 00:01 | The Declining Balance method of
calculating depreciation enables companies
| | 00:04 | to accelerate the rate at which
they claim the tax benefits inherit in
| | 00:08 | asset depreciation.
| | 00:10 | As the name implies the double declining
balance depreciation method doubles the rate
| | 00:15 | at which the declining balance
method calculates an asset's depreciation.
| | 00:19 | To calculate depreciation using the Double
Declining Balance method, you use the DDB function.
| | 00:25 | The DDB function has five arguments.
| | 00:27 | The first is cost, which is the
initial cost of the investment.
| | 00:31 | And on this worksheet that's found in
cell B7 and I'll press F4 to make that
| | 00:36 | reference an absolute reference so it
won't change as the formula is copied
| | 00:41 | down the table column.
| | 00:43 | Then you have the asset's
salvage value, found here in cell B9.
| | 00:48 | Make it an absolute reference.
| | 00:51 | The economic life of the
asset found in cell B11.
| | 00:55 | Once again, absolute reference.
| | 00:57 | And then we have the period.
| | 00:59 | That is the year and it's found in cell A16.
| | 01:02 | Because we want that cell reference
to vary as we copy the formula down the
| | 01:06 | table column, we'll leave
that as a relative reference.
| | 01:10 | The final argument in the DDB function,
which is called factor, is the amount by
| | 01:15 | which you want to speed up
the depreciation schedule.
| | 01:17 | For example, if for some reason you
want to use triple declining balance
| | 01:21 | depreciation, you would
set the factor argument to 3.
| | 01:24 | You would want to make sure that your
firm's accountant signed off on that decision.
| | 01:29 | The default value for the factor argument is 2.
| | 01:31 | So, we don't have to
change it for this function.
| | 01:34 | Just close out the parenthesis. Hit Return.
| | 01:37 | And there you could see the first ten
years of depreciation for this asset.
| | 01:41 | To calculate the new value of the
asset at the end of each year, you would
| | 01:45 | subtract the accumulated
depreciation from the asset's initial value.
| | 01:50 | To do that we create this formula.
| | 01:53 | You type in the initial value,
the initial cost, which is in cell B7, and
| | 01:58 | make it an absolute reference, so it doesn't change
as we copy the formula down the table column.
| | 02:03 | And you subtract the sum of all
of the accumulated depreciation.
| | 02:07 | So, the first cell in the
Depreciation column is cell B16.
| | 02:13 | We do not want that cell reference to change.
| | 02:16 | So, we make it an absolute reference by
typing F4, enter a colon to indicate
| | 02:22 | we are entering a cell range,
and we type B16 again.
| | 02:25 | In other words, for the first year
the formula will only consider the
| | 02:29 | Depreciation Value in cell B16.
| | 02:31 | But as you'll see when we complete the formula,
| | 02:35 | and it's copied down the table column,
that Excel, because the second B16
| | 02:40 | reference was a relative reference,
| | 02:42 | it changed it as it copied the
formula down the table column.
| | 02:46 | So, here in C17 the last reference is B17,
so it uses the values from these two cells.
| | 02:52 | Same thing here. The last
reference is to B16 to B18 so it uses
| | 02:58 | the depreciation from B16 to B18.
| | 03:02 | The Double Declining Balance method
assigns a high percentage of an asset's
| | 03:05 | depreciation to the first
part of its economic life.
| | 03:09 | As with the Declining Balance method,
companies can use their depreciation
| | 03:12 | related tax savings to invest in other areas.
| | Collapse this transcript |
|
|
11. Analyzing Bond InvestmentsIntroducing bonds and bond terminology| 00:01 | Every financial specialty has its own
vocabulary and bonds are no exception.
| | 00:05 | This lesson introduces some of the terms
used when evaluating bond investments.
| | 00:09 | A bond is a debt instrument issued for
a period of more than one year that an
| | 00:13 | institution uses to raise debt by borrowing.
| | 00:17 | The length of time the borrower has to
use of the money is called the bond's term.
| | 00:20 | U.S. Treasury Bond, which is backed
by the full faith and credit of the US
| | 00:24 | government and therefore has near zero
risk, has a term of more than one year.
| | 00:28 | A US Treasury Bill or T-Bill is also
government-backed but has a term of
| | 00:33 | less than one year.
| | 00:35 | A bond's maturity or maturity date
is the day it comes due for payment.
| | 00:40 | The amount that's paid on the day is
known as the bond's maturity value.
| | 00:44 | A capital gain or a capital loss is
the amount that a bond's market price
| | 00:49 | increases or decreases
after the bond is purchased.
| | 00:52 | Coupon or coupon rate is the interest rate
on a security with a fixed rate of return.
| | 00:57 | A bond's yield is the bond's interest,
interest earned on that interest through
| | 01:01 | compounding and capital gains if any.
| | 01:04 | A bond is said to be callable if you can
redeem it, prior to the end of the bond's term.
| | 01:09 | Most callable bonds specify a first
call date before which the investor
| | 01:13 | can't redeem the bond.
| | 01:15 | Finally risk, the likelihood that some
factor, such as currency fluctuations,
| | 01:20 | inflation, interest rates, or external
events such as natural disasters or wars,
| | 01:25 | will negatively affect the value of a bond.
| | 01:28 | This list of bond related
terms is by no means all-inclusive.
| | 01:31 | You should read as much as you can about
bonds, so you can better understand the
| | 01:34 | intricacies of bond pricing and trading.
| | Collapse this transcript |
| Calculating a bond's yield| 00:01 | One straightforward way to calculate
the value of a bond is to estimate the
| | 00:04 | bond's yield given the investments
condition such as price, coupon rate,
| | 00:08 | and time to maturity.
| | 00:10 | In Excel, you can calculate a bond's
yield perhaps not surprisingly by using
| | 00:14 | the YIELD function.
| | 00:15 | The YIELD function has seven arguments,
Settlement Date, Maturity Date, Percent
| | 00:22 | Coupon which is listed here as its rate,
Price, Redemption Value, Frequency, and Basis.
| | 00:29 | The Settlement Date is the date that
you gain ownership of the security.
| | 00:32 | That date will probably be different
from the bond's issuance date, which is the
| | 00:36 | date the bond is made available for sale.
| | 00:38 | The Maturity Date is the
date the bond will be paid off.
| | 00:42 | Percent Coupon is the bond's interest rate.
| | 00:44 | Price is the amount you
paid for $100 of face value.
| | 00:47 | Redemption is the bond's redemption
value for $100 of face value that's
| | 00:51 | most commonly $100.
| | 00:53 | Frequency is the number of
interest or coupon payments per year.
| | 00:56 | Finally, Basis reflects the way the
investment counts the days of year.
| | 01:00 | The western calendar can have days
of 28, 29, 30, or 31 days and years of
| | 01:05 | either 365 or 366 days.
| | 01:08 | So many investments base their
calculations on a year that is presumed to have
| | 01:12 | 12 months of 30 days each for a 360 day year.
| | 01:15 | The Excel help system describes the
other options available including the
| | 01:19 | European 3360 systems, which is
slightly different from the US system.
| | 01:23 | So to complete the formula using the
values that we find in the Yield worksheet.
| | 01:28 | The Settlement Date is in cell B3,
Maturity Date is in cell B4, Percent Coupon
| | 01:34 | is in cell B5, the Price is in cell B6.
| | 01:39 | The Redemption Value is in cell B7,
Frequency is in cell B8 and the Basis, which
| | 01:44 | will add but we don't have to, it's an
optional argument and we are using the
| | 01:47 | default of 0, but we'll just
complete the formula, is in B9.
| | 01:51 | Close the parenthesis and you see that
this bond has an annual Yield of 4.88%.
| | 01:58 | The YIELD function provides
a simplified look at bonds.
| | 02:00 | The function assumes the bond's interest
rate stays constant, which almost never
| | 02:04 | happens, that you reinvest your bond
interest and that there are no delays in
| | 02:08 | getting paid when the bond matures.
| | 02:10 | Even so the YIELD function provides a
good first look at a bond's potential value.
| | Collapse this transcript |
| Calculating the value of zero coupon bonds| 00:01 | Bonds that issue coupons or pay
interests to investors at regular intervals
| | 00:04 | offer bondholders the option to
invest their earnings elsewhere.
| | 00:08 | Some issuers decide to offer zero-
coupon bonds, which only pay interest at the
| | 00:12 | end of the investment period.
| | 00:14 | A zero-coupon bond sale lists the
price and the payment due when the bond
| | 00:17 | matures, but doesn't usually
list the investment's interest rate.
| | 00:21 | You can calculate that rate for
yourself using the RATE function.
| | 00:24 | The RATE function has three required
arguments and three optional arguments.
| | 00:30 | The required arguments are nper, the
number of periods, pmt or payment, which
| | 00:35 | are any payments made each period.
| | 00:37 | And present value, which is
what the investment is worth today.
| | 00:40 | That is the cost to purchase the investment.
| | 00:43 | The optional arguments are fv, the
future value of the bond, type, whether you
| | 00:48 | pay at the beginning or end of an
investment period and guess, which is a guess
| | 00:52 | as to the interest rate.
| | 00:54 | If you leave guess blank, Excel
assumes a value of 10%, which is often a good
| | 00:58 | enough starting point for Excel to zero
in on the bonds annual percentage rate.
| | 01:02 | So I'll finish filling out this RATE formula.
| | 01:05 | So we have the number of periods,
and that is the number of years in B3,
| | 01:11 | multiplied by the Periods Per Year 12,
which are in cell B4, then the Payment
| | 01:17 | that in this case is 0. That's in cell B5.
| | 01:20 | And then we have the Present Value.
| | 01:22 | The Present Value is the amount that
you pay to invest in the zero-coupon bond.
| | 01:27 | Payment is 0 because you do not make a
monthly payment, instead you put all the
| | 01:31 | money in upfront and that
indicates the present value.
| | 01:35 | Present Value is expressed as a
negative number, because it represents an
| | 01:38 | expense or negative cash flow to you.
| | 01:41 | So you have it as a negative number.
| | 01:43 | And that is in cell B6, and then you
have the Future Value, which is in cell B7.
| | 01:49 | And in this case, you have
a Future Value of $12,500.
| | 01:54 | I'm not going to use either type or guess.
| | 01:56 | So I'll close the parenthesis.
| | 01:58 | Now you'll notice that the formula I
have here below includes a multiplication
| | 02:03 | by the number of periods per year.
| | 02:05 | Not multiplying by the number of
periods per year results in a formula that
| | 02:09 | makes sense, but actually produces
a result other than what you want.
| | 02:13 | So I'll press Return and you see
that you have a result of 0.37%.
| | 02:18 | What that means is that is that
you have calculated the monthly rate.
| | 02:22 | When you multiplied by 12, the
number of periods per year you change the
| | 02:27 | calculation so that you
actually got the rate for a month.
| | 02:31 | So each month you make .37% of interest,
but if I multiply the result of the
| | 02:38 | RATE formula by B4, the number of
periods per year then you get 4.47%.
| | 02:44 | By way of checking my math, I input 4.47%
into a future value formula divided by
| | 02:52 | 12 and then use the same terms that I
used in the RATE calculation to come up
| | 02:57 | with a value of $12,499.27 which is
just a rounding error away from the rate as
| | 03:04 | expressed in cell B9.
| | 03:06 | You should consult with your tax
professional about the advisability of
| | 03:09 | investing in a zero-coupon bond.
| | 03:11 | The reason is that you might owe taxes
on interest earned even if you haven't
| | 03:16 | received any interest payments.
| | Collapse this transcript |
| Pricing bonds to be offered to investors| 00:01 | If your company needs to raise some
cash and has determined that that issuing
| | 00:04 | stock isn't in its best interest, you
might borrow money by issuing bonds.
| | 00:09 | Pricing bonds for sale is a tricky business.
| | 00:11 | In essence, you're betting that you
can earn a higher rate of return on the
| | 00:14 | borrowed money than you
pledged to pay bondholders.
| | 00:17 | Once you know the parameters of the
bond you would like to issue, you can use
| | 00:20 | the PRICE function to find
the break-even issue price.
| | 00:23 | To do that you use the PRICE function,
which has seven arguments, Settlement Date,
| | 00:28 | Maturity Date, Percent Coupon
which is the interest rate, percent Yield,
| | 00:34 | Redemption Value, Frequency, and Basis.
| | 00:37 | The Settlement Date is the date
you gain ownership of the security.
| | 00:41 | The Settlement Date is the date that a
buyer gains ownership of the security.
| | 00:45 | That date will probably be different
from the bonds issuance date, which is the
| | 00:48 | date the bond was made available for sale.
| | 00:51 | The Maturity Date is the
date the bond will be paid off.
| | 00:54 | So we've a Settlement Date in cell
B3 and a Maturity Date in cell B4.
| | 01:00 | Percent Coupon is the bond's interest rate.
| | 01:02 | Yield is the bond's annual yield and
Redemption is the bond's redemption value
| | 01:07 | per $100 of face value,
most commonly that's $100.
| | 01:11 | And Frequency is the number of
interest or coupon payments per year.
| | 01:15 | So we've the rate or Percent Coupon
in cell B5, the Yield in cell B6, the
| | 01:22 | Redemption Value in B7 and the Frequency in B8.
| | 01:28 | Basis reflects the way you
count the days of the year.
| | 01:30 | The western calendar can have months
of 28, 29, 30, or 31 days and years of
| | 01:36 | either 365 or 366 days.
| | 01:39 | So many investments base their
calculations on a year that is presumed to have
| | 01:43 | 12 months of 30 days each for a 360-day year.
| | 01:47 | The Excel help system describes the
other options available including the
| | 01:50 | European 30-360 system, which is
slightly different from the US system.
| | 01:55 | So I'll just type in B9, close out
the formula and there we have a result.
| | 02:01 | The break-even issuance price given
the parameters stated would be $90.36.
| | 02:07 | Like the YIELD function, the PRICE
function assumes bondholders always reinvest
| | 02:11 | their interest and that the
bond's interest rate never changes.
| | 02:15 | Those assumptions are rarely true.
| | 02:17 | But the PRICE function offers a good
first look at what you should charge your
| | 02:20 | bondholders with the goal of
making a profit on the transaction.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:01 | Thanks for joining me for
Excel 2007: Financial Analysis.
| | 00:04 | You now have a broader set of tools
at your disposal to design financial
| | 00:08 | worksheets, analyze financial
statements, perform ratio analysis, forecast
| | 00:12 | future growth, calculate
amortization and depreciation schedules and
| | 00:16 | analyze bond investments.
| | 00:18 | I hope you've enjoyed the course and encourage you
to explore the rest of the lynda.com Training Library.
| | Collapse this transcript |
|
|
