Module 9 Project 1A

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