0:01 - 0:05
- [Instructor] This is Excel,
module nine, SAM Project 1a.
0:06 - 0:09
We will be working with
Mount Moreland Hospital,
0:10 - 0:14
and learning how to perform
financial calculations.
0:16 - 0:19
Pranjali Kashyap is a financial analyst
0:19 - 0:23
at Mount Moreland Hospital
in Baltimore, Maryland.
0:23 - 0:25
She is using Excel workbooks to analyze
0:25 - 0:28
the financial data for a proposed program
0:28 - 0:30
called Neighborhood Nurse.
0:30 - 0:33
The program involves nurse
and nurse practitioners
0:33 - 0:36
providing healthcare services
to Baltimore neighborhoods
0:36 - 0:41
from a van outfitted with
medical equipment and supplies.
0:41 - 0:44
She asks for your help
in correcting errors,
0:44 - 0:48
and making financial
calculations in the workbook.
0:48 - 0:51
So, we will go to the
Loan Payments worksheet.
0:51 - 0:53
The hospital needs a loan
to buy the medical van
0:53 - 0:56
for the Neighborhood Nurse program.
0:56 - 0:59
Before Pranjali can
calculate the principal
0:59 - 1:02
and interest payments on the loan,
1:02 - 1:05
she asks you to correct the
errors in the worksheet.
1:05 - 1:08
So, we will be correcting
the first error as follows.
1:08 - 1:12
In cell H17 we'll use the
Error Checking Command
1:12 - 1:15
to identify the error in the cell,
1:15 - 1:17
and then we will correct that error.
1:21 - 1:25
So, if we look at the
Loan Payments worksheet,
1:25 - 1:30
specifically cell H17, we have
a name error that is showing
1:31 - 1:34
and we want to use the
Error Checking command
1:34 - 1:36
to help us correct that.
1:39 - 1:42
So, if we go to the Formulas tab
1:42 - 1:47
and then we look in the
Formula Auditing group
1:47 - 1:49
you'll see the Error Checking tool.
1:49 - 1:50
Go ahead and click on that tool,
1:50 - 1:52
and there are several ways to help us
1:52 - 1:54
correct this name error.
1:54 - 1:59
Help on this Error will launch
you to the Microsoft site,
2:02 - 2:05
and give you guidance
on how you might correct
2:05 - 2:08
a name error in a spreadsheet.
2:08 - 2:11
So, you can read quite a bit about that
2:11 - 2:13
and decide if that's the
route you want to take
2:13 - 2:14
to correct it.
2:14 - 2:18
You can also try to Show
the Calculations Steps
2:18 - 2:21
for this particular cell,
and it kind of stands out
2:21 - 2:22
that there's a problem.
2:22 - 2:24
We're trying to use the SOME function,
2:24 - 2:28
but it's showing you, it's
evaluating what's happening here
2:28 - 2:30
and you can choose to Evaluate it,
2:30 - 2:32
and it's letting you
know that it is in fact
2:32 - 2:33
a name problem.
2:34 - 2:37
You can Ignore the error and move on,
2:37 - 2:41
or you can simply check, choose
to Edit in the Formula Bar.
2:41 - 2:45
So, up in the Formula Bar, we can see that
2:45 - 2:49
the error has to do with the
way the SOME function has been
2:49 - 2:52
spelled, as well as an at
sign that shouldn't be there.
2:52 - 2:56
So, we'll simply delete that,
and type in the word SUM,
2:57 - 2:58
and press Enter.
2:59 - 3:02
Then we can go ahead and
either Resume or close
3:02 - 3:05
the Error Checking box, and
you can see now that the error
3:05 - 3:10
has been selected, with a
zero balance at this point.
3:13 - 3:16
Task number two wants us
to correct the value error
3:16 - 3:18
in the worksheet as follows.
3:18 - 3:20
We'll be using the Trace
Precedents arrows to find
3:20 - 3:24
the source of the value error in cell C20.
3:24 - 3:27
We'll correct that formula in cell C20,
3:27 - 3:30
which should divide the
remaining principal,
3:30 - 3:35
the amount in cell C19, by the
loan amount found in cell D5.
3:35 - 3:38
Define the percentage
of remaining principal.
3:38 - 3:42
Then we'll fill the range
D20 to G20 with the formula
3:42 - 3:47
in cell C20 to correct the
remaining value errors.
3:47 - 3:50
And if our trace arrows are still active,
3:50 - 3:52
we will remove those.
4:02 - 4:05
So, if you click on cell C20
4:07 - 4:10
and you come up to your Formulas tab,
4:10 - 4:15
Formula Auditing group, and
choose to Trace Precedents,
4:15 - 4:19
you can see that the formula
in cell C20 is dependent
4:19 - 4:21
on the remaining percentage below that,
4:22 - 4:26
and then it is divided by what is actually
4:26 - 4:29
a column heading in cell D4.
4:29 - 4:31
So, you know, right away
that that must be a problem
4:31 - 4:35
because you can't divide anything that is
4:35 - 4:38
located in cell C4 at this point.
4:38 - 4:40
So, I'm just gonna go up to my Formula Bar
4:40 - 4:44
and our instructions said
that it should be C19
4:44 - 4:48
divided by cell D5, and D5 should be
4:48 - 4:50
in an absolute cell reference.
4:50 - 4:54
So, once we do that, the
value error goes away,
4:54 - 4:58
then we can actually
auto-fill over to cell G5
4:58 - 5:01
to correct that error in
each one of those columns.
5:01 - 5:05
Our trace precedents arrows are now gone.
5:05 - 5:08
So, we don't need to worry
about removing those.
5:10 - 5:12
Let's go ahead and save
our file at this point.
5:12 - 5:14
Click on File, Save As.
5:17 - 5:19
And then make sure that the name,
5:19 - 5:22
I'm going to make sure I'm on my Desktop,
5:22 - 5:27
and make sure the file name
ends rather than with ones,
5:27 - 5:29
make sure it ends in _2.
5:32 - 5:35
And we're ready to try step three.
5:36 - 5:39
Now, Pranjali is ready to
calculate the annual principal
5:39 - 5:42
and interest rates for the medical van.
5:42 - 5:44
We will start by calculating
5:44 - 5:47
the cumulative interest
payments as follows.
5:47 - 5:50
In cell C17, we'll enter a formula using
5:50 - 5:53
the cumulative interest payment function
5:53 - 5:57
to calculate the cumulative
interest paid on the loan
5:57 - 5:59
for year one,
5:59 - 6:04
payment one in cell C15
through payment 12 in cell C16.
6:04 - 6:08
We will use zero as the
type argument in our formula
6:08 - 6:13
because payments are made
at the end of the period.
6:13 - 6:17
We'll use absolute cell
references for our rate,
6:17 - 6:20
our number of periods, and
our present value arguments,
6:20 - 6:24
which are listed in the range D5 to D11,
6:24 - 6:26
and we will use relative cell references
6:26 - 6:29
for the start and end arguments.
6:29 - 6:32
Once we have the CUMIPMT
function completed,
6:32 - 6:36
we will fill the range D17
to G17 with the formula
6:36 - 6:41
in cell C17 to calculate the interest paid
6:41 - 6:45
in years two through five
and the total interest.
6:48 - 6:50
All right, so, let's go to cell C17.
6:50 - 6:53
We're going to use the
cumulative interest payment.
6:53 - 6:57
I'm going to use the Function
Argument box for this one
6:57 - 7:01
on the Formulas tab in the
Financial dropdown list.
7:01 - 7:05
Let's choose cumulative
interest payment, or CUMIPMT.
7:10 - 7:14
All right, the rate can
be found in cell D7.
7:16 - 7:19
You always want to choose
the monthly interest rate
7:19 - 7:21
and this needs an absolute cell reference.
7:21 - 7:23
So, I've pressed the Function 4 key.
7:23 - 7:28
The number of periods
can be found in cell D9
7:28 - 7:31
and that should contain an
absolute cell reference.
7:31 - 7:35
The present value is the
loan amount in cell D5
7:35 - 7:38
and that should also be an
absolute cell reference.
7:54 - 7:57
The start period for the
first year can be found
7:57 - 8:02
in cell C15, and the end
period for the first year
8:02 - 8:05
can be found in cell C16
8:05 - 8:07
and these are relative cell references.
8:07 - 8:09
The last thing you need to
do is provide the timing
8:09 - 8:10
for the payment.
8:10 - 8:13
So, I'm going to scroll in
the Function Argument box
8:13 - 8:17
'til I can see the Type, and type a zero.
8:17 - 8:20
Once again, that zero is
actually letting us know
8:20 - 8:23
that the payments are made
at the end of the period.
8:24 - 8:26
Then I can go ahead and click OK.
8:27 - 8:30
And it shows the interest
payment for year one.
8:30 - 8:33
I can also then auto-fill
over to year five
8:33 - 8:36
to show interest payments
for each of the five years.
8:38 - 8:43
Let's Save by choosing Control + S
8:43 - 8:47
and then go back to the
instructions for step four.
8:48 - 8:49
Step four wants us to calculate
8:49 - 8:52
the cumulative principal
payments as follows.
8:52 - 8:56
In cell C18, we'll enter a formula using
8:56 - 8:58
the cumulative principal
function to calculate
8:58 - 9:01
the cumulative principal
paid for year one,
9:01 - 9:06
payment one in cell C15
through payment 12 in cell C16.
9:06 - 9:09
We will use zero as the
type argument in our formula
9:09 - 9:12
because payments are made
at the end of the period.
9:12 - 9:16
We will use absolute cell
references where appropriate
9:16 - 9:19
and relative cell references
when we are providing
9:19 - 9:24
start and end arguments, and
then we'll fill the range
9:24 - 9:28
D18 to G18 with the formula
in cell C18 to calculate
9:28 - 9:31
the principal paid in
years two through five
9:31 - 9:33
and the total principal.
9:37 - 9:40
So, in cell C18 we will use
9:40 - 9:43
the cumulative principal function.
9:43 - 9:46
I'm going to do a one
liner with this function
9:46 - 9:48
for those of you who like to do that.
9:48 - 9:52
So, type in equals, begin
to type in CUMPRINC,
9:52 - 9:54
or cumulative principal.
9:54 - 9:58
Choose from the list the correct function.
9:58 - 10:01
The rate once again is in cell D5.
10:01 - 10:04
Press Function 4 to make it
an absolute cell reference.
10:04 - 10:07
Type a comma to take you
to the number of periods
10:07 - 10:10
of NPER filled.
10:10 - 10:13
That can be found in cell D9.
10:13 - 10:16
Make it an absolute cell reference.
10:16 - 10:17
Type a comma.
10:17 - 10:21
The present value is
the amount of the loan.
10:21 - 10:25
Looks like I've, for my number of periods,
10:25 - 10:29
I think I've got that
right, but I need to do
10:29 - 10:32
my cumulative principal is actually
10:32 - 10:35
the rate can be found in cell D7.
10:35 - 10:37
So, go ahead and make
that correction there,
10:37 - 10:41
and then we can go ahead and
provide the present value
10:41 - 10:43
when prompted to do that.
10:43 - 10:44
Let me start that one again.
10:44 - 10:46
I wanna make sure that you get it right.
10:46 - 10:51
So, equals CUM principal,
cumulative principal.
10:51 - 10:55
The rate is always the monthly rate.
10:55 - 10:58
Make it an absolute cell reference.
10:58 - 10:59
Type a comma.
10:59 - 11:02
The number of periods
can be found in cell D9.
11:02 - 11:07
Type a absolute reference
on that, type a comma.
11:07 - 11:09
The present value can be found in cell D5.
11:11 - 11:13
Make it an absolute
reference, type a comma.
11:13 - 11:18
The start period for year
one is in cell C15, comma.
11:18 - 11:22
The end period can be found in cell C16.
11:23 - 11:26
Neither one of those have
an absolute reference.
11:26 - 11:29
They are relative cell references.
11:29 - 11:31
Type a comma, and the last
thing to do is provide
11:31 - 11:34
the type of payment, and it is a zero,
11:34 - 11:38
because it's at the end of
period, close parenthesis,
11:38 - 11:41
and you should have your
principal payment for year one
11:42 - 11:45
that you can auto-fill to year five.
11:47 - 11:49
Go ahead and save through Control + S,
11:49 - 11:51
and we'll start the next task.
11:53 - 11:56
Task five says to go to
the Depreciation worksheet.
11:56 - 11:59
Pranjali needs to correct
the errors on this worksheet
11:59 - 12:03
before she can perform any
depreciation calculations.
12:03 - 12:06
So, we will correct the errors as follows.
12:06 - 12:08
This time we will use the
trace dependents arrows
12:08 - 12:12
to determine whether the
value error in cell D12
12:12 - 12:15
is causing other errors in the worksheet.
12:15 - 12:20
We will use the trace
precedents arrows to find
12:20 - 12:22
the source of the error in cell D12
12:22 - 12:25
and then we'll correct that error.
12:30 - 12:34
So, let's go ahead, go to
the depreciation worksheet.
12:34 - 12:37
We're specifically looking at cell D12,
12:41 - 12:43
where there is an error.
12:43 - 12:46
We need to use the trace dependent arrows.
12:46 - 12:48
So, I'm gonna go to the Formulas tab
12:48 - 12:50
in the Formula Auditing group
12:50 - 12:53
and activate the Trace Dependents,
12:53 - 12:57
and you can see that the cell
below D12 and to the right
12:59 - 13:02
is depending, both of
those cells are depending
13:02 - 13:05
on this cell to have the
correct formula in there.
13:05 - 13:08
So, it lets us know
that there is a problem.
13:08 - 13:12
If we look at the explanation of what
13:12 - 13:13
the formula should be about,
13:18 - 13:22
in task five c it tells
us to correct the error,
13:22 - 13:26
so the formula in D12
calculates the cumulative
13:26 - 13:29
straight line depreciation
of the medical van
13:30 - 13:34
by adding the cumulative
depreciation value in year one
13:34 - 13:39
to the annual depreciation
value in year two.
13:39 - 13:42
So, in order to do that
it looks like it has tried
13:42 - 13:47
to take C12, which is year
one, that part is correct,
13:47 - 13:48
and add it to B11.
13:49 - 13:53
B11 is once again a row heading.
13:53 - 13:56
And so, our formula is
not working well there.
13:56 - 14:01
So, if we can double click
this formula in cell D12,
14:01 - 14:03
or go up to your Formula
Bar and correct it,
14:03 - 14:06
it should be C12 plus D11.
14:12 - 14:16
Press Enter and it helps the numbers
14:16 - 14:21
in the entire row 12 to be
adjusted as well as in row 13.
14:25 - 14:28
We are to remove any arrows
that are still showing.
14:28 - 14:31
So, I'll go back up to my
ribbon in the Formulas tab,
14:31 - 14:34
Formulas Auditing group, Remove Arrows.
14:39 - 14:42
In task six, Pranjali wants
to compare straight line
14:42 - 14:45
depreciation amounts with
declining balance depreciation
14:45 - 14:48
amounts to determine which
method is more favorable
14:48 - 14:51
for the hospital's balance sheet.
14:51 - 14:54
In the range D5 to D7 she estimates that
14:54 - 14:58
the Neighborhood Nurse
program will have $234,000
14:58 - 15:00
in tangible assets at startup,
15:00 - 15:03
and the useful life of
these assets is seven years,
15:03 - 15:07
with a salvage value of $37,440.
15:07 - 15:09
We will start by calculating
the straight line
15:09 - 15:12
depreciation amounts as follows.
15:12 - 15:15
We'll use the SLN function in cell C11
15:15 - 15:17
to calculate the straight
line depreciation.
15:17 - 15:19
We'll use absolute
references for the cost,
15:19 - 15:22
salvage, and life arguments,
15:22 - 15:25
and then we will fill the range D11 to I11
15:25 - 15:28
with the formula in cell C11
15:28 - 15:30
to calculate the annual and
cumulative straight line
15:30 - 15:33
depreciation in years two through seven.
15:35 - 15:40
So, in cell C11 let's do the
straight line depreciation.
15:40 - 15:43
Formulas tab, Financial functions,
15:43 - 15:47
specifically the straight
line depreciation,
15:47 - 15:49
or SLN, straight line.
15:49 - 15:52
The cost can be found in cell D5,
15:52 - 15:55
and must be an absolute cell reference.
15:55 - 15:58
The salvage value can be found in cell D6.
15:59 - 16:02
Absolute reference, and
the life of the asset
16:02 - 16:04
can be found in cell D7.
16:06 - 16:08
Once again, only in those cells,
16:08 - 16:10
and so, they're absolute references.
16:10 - 16:13
Once we click OK, it
calculates a straight line
16:13 - 16:16
depreciation amount and can be auto-filled
16:16 - 16:18
and you can see that
each one of those amounts
16:18 - 16:21
will be the same, because it's
straight line depreciation.
16:23 - 16:28
Let's now do then the declining
balance depreciation method
16:29 - 16:34
in cell C18 by following the
same procedure that we did
16:34 - 16:38
for the straight line,
except for task seven D
16:41 - 16:43
tells us, actually, it's seven b,
16:43 - 16:47
is gonna have us use year
one, which is cell C17
16:47 - 16:50
as the current period for a
declining balance function
16:50 - 16:53
and then we'll fill the
range appropriately.
16:56 - 17:01
So, in cell C18, we'll use the
declining balance function.
17:01 - 17:05
This time, I will do it
as a one line function
17:05 - 17:09
equals DB for declining balance.
17:10 - 17:12
The cost is required.
17:12 - 17:17
So, we click on cell D5, make
it an absolute reference,
17:17 - 17:18
type a comma.
17:18 - 17:22
The salvage from cell D6, making
that an absolute reference.
17:22 - 17:23
Type a comma.
17:23 - 17:27
The life of the asset is in cell D7.
17:27 - 17:29
Once again, make that an
absolute cell reference.
17:29 - 17:32
Type a comma, because we
actually need the period now
17:32 - 17:35
for the declining balance,
and that can be found
17:35 - 17:39
in cell C17, year one.
17:39 - 17:42
Provide the closing
parenthesis or press Enter
17:42 - 17:43
and you'll see that the declining,
17:43 - 17:47
the annual depreciation for
declining balance number
17:47 - 17:51
and then as you auto-fill
this over to year seven
17:51 - 17:53
you'll see that the numbers change
17:53 - 17:58
and the depreciation becomes
less as the years progress.
18:00 - 18:02
Do a Save, Control + S to Save.
18:04 - 18:06
And let's do task eight.
18:06 - 18:09
Pranjali also wants to determine
the depreciation balance
18:09 - 18:12
for the first year and the
last year of the useful life
18:12 - 18:14
of the medical van.
18:14 - 18:17
So, we will be using in cell E22
18:17 - 18:20
we will use the sum of
the years digit function,
18:20 - 18:24
the SYD function, to
calculate the depreciation
18:24 - 18:28
for the first year, and then
in cell E23 we will enter
18:28 - 18:32
the formula using the sum
of the years digit function
18:32 - 18:35
to calculate the depreciation
for the last year
18:35 - 18:38
using the appropriate cell
references to do that.
18:39 - 18:42
So, let's go to cell E22.
18:46 - 18:50
And we'll use the sum of
the years digits formula,
18:50 - 18:53
or function, from the Financial category.
18:54 - 18:57
The cost is in cell D5.
18:57 - 18:59
We're not copying this formula anywhere,
18:59 - 19:01
so it doesn't need to have
an absolute cell reference.
19:03 - 19:06
The salvage value is in D6.
19:06 - 19:10
The life is in cell D7
19:10 - 19:14
and the period that we are
looking for is the first year.
19:14 - 19:18
So, you'll find that reference
in cell C17, year one,
19:18 - 19:19
and click OK.
19:21 - 19:23
All right, let's do the
sum of the years digit
19:23 - 19:24
for the last year.
19:24 - 19:29
I'll do one liner equals SYD
for sum of the years digit.
19:30 - 19:35
All right, the cost C5, or D5, comma.
19:36 - 19:41
The salvage, D6, comma,
and the life of asset
19:43 - 19:48
is in cell D7, comma, and
the period is year seven.
19:49 - 19:52
So, you would select I7 for that.
19:52 - 19:55
You can type a close parenthesis
or you can press Enter
19:55 - 19:58
and you will get the yearly
depreciation allowance
19:58 - 19:59
for the last year.
20:03 - 20:05
Task nine tells us to go
20:05 - 20:07
to the Earnings Projections worksheet.
20:07 - 20:09
Pranjali has entered most of the income
20:09 - 20:12
and expense data on the worksheet.
20:12 - 20:14
She knows the income from municipal grants
20:14 - 20:17
will be $25,000 in 2022,
20:17 - 20:21
and estimates it will 40,000 in 2026.
20:21 - 20:24
She needs to calculate the
income from the municipal grants
20:24 - 20:28
in the years 2023 through 2025.
20:28 - 20:30
The grant should increase
at a constant amount
20:30 - 20:32
from year to year.
20:32 - 20:35
So, we will be projecting the
income from municipal grants
20:35 - 20:40
for 2023 to 2025 using a
linear trend interpolation.
20:45 - 20:48
So, on the Earnings Projection worksheet
20:48 - 20:50
we're going to row five.
20:50 - 20:53
We're going to select the
data for municipal grants,
20:53 - 20:55
C5 to G5.
20:55 - 20:57
We have a beginning and ending number.
20:59 - 21:01
Then we'll go to the Home tab.
21:03 - 21:06
And then in the Editing group under Fill
21:12 - 21:13
Series.
21:14 - 21:19
It has asked us to do a linear
21:21 - 21:23
interpolation.
21:23 - 21:24
Our series is in rows.
21:24 - 21:28
The type is linear and it is a trend.
21:28 - 21:31
So, we will choose those
options and click OK.
21:31 - 21:35
And we will get a forecast for
the years 2023 through 2025.
21:38 - 21:41
So, task 10 wants us to
do a similar calculation
21:43 - 21:45
to calculate the income from
insurance reimbursem*nts
21:45 - 21:49
in the years 2023 to 2025.
21:49 - 21:50
She knows the starting amount
21:50 - 21:53
and has estimated the amount in 2026.
21:53 - 21:55
She thinks this income will increase
21:55 - 21:57
by a constant percentage.
21:57 - 22:00
So, we will project the income
from insurance reimbursem*nts
22:00 - 22:05
for 2023 to 2025 using a
growth trend interpolation.
22:09 - 22:13
So, we will be selecting C7 through G11.
22:13 - 22:15
We'll go back on the Home
tab in the editing group.
22:15 - 22:19
We will find the Fill Series option
22:19 - 22:22
and we once again our data is in a row.
22:22 - 22:27
The type is growth, and it is a trend.
22:28 - 22:32
So, the step value is not
required in that situation.
22:32 - 22:36
So, click OK and you'll see the trend
22:36 - 22:38
and it will also adjust
in the chart over here
22:38 - 22:40
of the insurance reimbursem*nts.
22:43 - 22:46
Task 11 says that Pranjali
needs to calculate
22:46 - 22:51
the payroll expenses in the
years 2023 through 2026.
22:52 - 22:56
She knows the payroll
will be 140,000 in 2022
22:56 - 23:00
and will increase by at least 5% per year.
23:00 - 23:03
So, we will project the
payroll expenses as follows.
23:03 - 23:07
Project the expenses for 2023 to 2026
23:07 - 23:11
using a growth trend extrapolation,
23:11 - 23:14
rather than an interpolation,
and our step value
23:14 - 23:19
will be 1.05, which is a 5% increase.
23:19 - 23:21
So, this is all about payroll,
23:21 - 23:23
and so, let's go ahead and do that.
23:27 - 23:29
If you see the payroll beginning,
23:29 - 23:32
payroll is 140,000 in 2022.
23:32 - 23:35
We do not have an ending value in 2026.
23:35 - 23:38
So, this makes this an extrapolation,
23:38 - 23:40
rather than an interpolation.
23:40 - 23:42
If you have your range selected then,
23:42 - 23:45
go up to the Fill tool, choose Series.
23:47 - 23:48
The data's in a row.
23:48 - 23:52
It's going to be a growth extrapolation.
23:52 - 23:55
We won't activate the Trend box,
23:55 - 23:58
because our step value is going to 1.05
23:58 - 24:01
which is a 5% increase in the step value.
24:02 - 24:06
Go ahead and click OK
and it kind of shows us
24:06 - 24:09
a project of what will happen to income
24:09 - 24:10
over the next few years.
24:12 - 24:13
Go ahead and save.
24:17 - 24:22
Task number 12 says the
projected revenue line chart
24:22 - 24:25
in the range H4 to Q19 shows the revenue
24:25 - 24:30
Pranjali estimates for
the years 2022 to 2026.
24:30 - 24:34
She wants us to extend
the projection into 2027.
24:34 - 24:37
We will modify the projected
revenue line chart as follows
24:37 - 24:40
to forecast the future trend.
24:40 - 24:42
We will add a linear trend
line to the projected
24:42 - 24:44
revenue line chart.
24:44 - 24:47
Then we will format the
trend line to forecast
24:47 - 24:49
one period forward.
24:53 - 24:55
So, on the Earnings Projection sheet,
24:55 - 24:57
here's the projected revenues chart.
24:57 - 25:00
Let's go ahead and click
on the trend line here,
25:01 - 25:04
and then we can choose our
Add Chart Element tool,
25:06 - 25:10
use the more arrow for
different trend line options
25:10 - 25:11
and choose Linear.
25:14 - 25:17
After we've chosen Linear, we can go ahead
25:17 - 25:20
and choose More Options and
that will give us the ability
25:20 - 25:24
to forecast one period forward.
25:26 - 25:28
And you can see the change on the chart,
25:28 - 25:30
and then I will close the Format Trendline
25:30 - 25:32
task pane for a minute
so you can see the chart
25:32 - 25:34
just a little bit better.
25:35 - 25:39
Task 13 says that the
revenue trend scatter chart
25:39 - 25:43
in range A21 to G40 is
based on monthly revenue
25:43 - 25:46
and estimates listed on
25:46 - 25:48
the Monthly Revenue Projections worksheet.
25:48 - 25:51
Pranjali wants to include
a trend line for this chart
25:51 - 25:54
that shows how revenues
increase quickly at first
25:54 - 25:56
and then level off in later months.
25:56 - 26:00
So, we will modify the trend
scatter chart as follows
26:00 - 26:02
to include a logarithmic trend line.
26:02 - 26:04
We will add a trend line to the chart
26:04 - 26:06
and then format the trend
line to use (mumbles),
26:09 - 26:11
sorry, logarithmic option.
26:14 - 26:17
So, on the Earnings Projections worksheet
26:17 - 26:22
the revenue trend chart,
click on the series revenue.
26:24 - 26:27
Let's add a trend line.
26:32 - 26:33
I'm gonna try that again.
26:33 - 26:37
It seems like it made a
strange selection for me.
26:37 - 26:39
So, I'm gonna delete
my trend line if I can.
26:41 - 26:42
Let's try that again.
26:42 - 26:47
Okay, so, with the chart selected,
I can add a chart element
26:48 - 26:49
and a trend line.
26:49 - 26:52
That looks better, you can see
the trend line taking place
26:52 - 26:57
and then for more options I can choose
26:57 - 26:59
to get my task pane open and choose
26:59 - 27:02
the Logarithmic trend line option.
27:09 - 27:13
Now, for some reason, I have
more than one trend line here.
27:14 - 27:17
So, make sure that you
have the logarithmic
27:18 - 27:20
trend line showing only.
27:26 - 27:29
Task 14 wants us to go to
the Investment worksheet.
27:29 - 27:32
This worksheet should show the
returns potential investors
27:32 - 27:36
could realize if they invested $165,000
27:36 - 27:38
in the Neighborhood Nurse program.
27:38 - 27:40
Pranjali figures a
desirable rate of return
27:40 - 27:42
would be 7.3%.
27:43 - 27:46
She estimates the investment
would pay different amounts
27:46 - 27:49
each year, which is in the range C7 to C12
27:49 - 27:53
and wants to calculate the
present value of the investment.
27:53 - 27:57
So, we'll calculate the
present value of the investment
27:57 - 28:01
in cell C15 using the NPV function
28:01 - 28:03
to calculate the present
value of the investment
28:03 - 28:07
in a medical van for the
Neighborhood Nurse program.
28:07 - 28:09
We will use the desired rate of return
28:09 - 28:11
and cell C14 is the rate argument
28:11 - 28:14
and the range of years one through six
28:14 - 28:19
in cell C7 through C12 as the
returns paid to the investors.
28:22 - 28:26
All right, so, let's go to
the investment worksheet,
28:30 - 28:34
in cell C15.
(computer chimes)
28:38 - 28:41
And calculate the net present value.
28:41 - 28:46
Formulas, Financial
functions, net present value.
28:46 - 28:50
The rate once again can be
found in cell C14 just above.
28:50 - 28:52
We don't need an absolute cell reference.
28:52 - 28:56
We're not copying it anywhere,
and value one should include
28:56 - 28:59
the range C7 through
C12, which is the payment
28:59 - 29:01
over the six years.
29:03 - 29:04
Close parenthesis.
29:05 - 29:08
(computer chimes)
29:08 - 29:09
Okay, let's see.
29:09 - 29:13
Our instructions tell us
that if we happen to find
29:14 - 29:18
a formula that omits an
adjacent cell error warning
29:18 - 29:19
we're supposed to ignore it.
29:19 - 29:22
So, don't worry about that
if you've got an icon here
29:22 - 29:23
with an exclamation point.
29:23 - 29:25
They're having us ignore that.
29:25 - 29:28
All right, let's then
now do the last step,
29:28 - 29:32
which is Pranjali also wants to calculate
29:32 - 29:35
the internal rate of
return on the investment.
29:35 - 29:39
If it is 7% or higher,
she is confident that
29:39 - 29:41
she can attract investors.
29:41 - 29:43
So, we will calculate the
internal rate of return
29:43 - 29:45
on the investment as follows.
29:45 - 29:49
We will use the IRR function in cell C17
29:49 - 29:53
to calculate that internal
rate of return for investing
29:53 - 29:56
in a medical van for the
Neighborhood Nurse program,
29:56 - 29:59
and we will use the payments
for startup in years
29:59 - 30:03
one through six, which are
in the range C6 through C12
30:03 - 30:06
as the returns paid to the investors.
30:08 - 30:12
So, in cell C17 we will use the IRR
30:12 - 30:16
internal rate of return function.
30:19 - 30:22
All right, for values then,
we want to just include
30:22 - 30:26
the range C6, which includes
the initial startup payment
30:26 - 30:31
through C12, close
parenthesis, and press Enter,
30:31 - 30:35
and at 7.52% you would
have the correct answer.
30:35 - 30:38
To understand the IRR function
just a little bit better,
30:38 - 30:43
you could go to your e-text,
specifically exercise 9-12c
30:43 - 30:46
will give you more information
about that function.
30:48 - 30:51
So, your workbook should
look like the final figures.
30:51 - 30:53
I would double check
those in the instructions.
30:53 - 30:56
Let's do one last save of our document,
30:56 - 30:59
so we have a final copy
and we can go ahead
30:59 - 31:00
and submit the project.
31:00 - 31:01
Thank you everyone.