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