- [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.