Two separate Excel Sheets – instructions for both are attached

Name: _____________________

HEALTHCARE FINANCE

HAP395 – FALL 2023

FINAL EXAM

Your completed Excel Spreadsheet should be uploaded in the Blackboard

Assignment folder by 10:00 PM, FRIDAY, DECEMBER 8. The exam is open book

and open notes. You may discuss the problem with your classmates, but

your spreadsheet must be done on your own. Be sure to put your name in

the Spreadsheet as in the filename of your attached Excel Spreadsheet. DO NOT

UPLOAD A PDF FILE. Use the Discounted Cash Flow spreadsheet template

and 8-step process that was presented in Module 12 and can be found in

the Skill Building folder. There is no need to create a spreadsheet from

scratch.

A Discounted Cash Flow Analysis Problem

A Cardiovascular Testing Center has been presented with a proposal to offer a

new diagnostic procedure that would require the purchase of a specialized piece

of equipment. This piece of equipment offers physicians the opportunity to

diagnose many types of coronary blood flow and heart rhythm disorders. You

have been asked by the Center’s administrator to use the techniques of

preparing a discounted cash flow analysis to evaluate it from an economic point

of view by computing the Net Present Value and Internal Rate of Return.

Assumptions:

For your analysis assume that the Center uses a 7% discount rate for projects

of this risk level, and that they will initially use a five-year time horizon. This is a

tax-exempt not-for-profit organization so there will not be any income tax effects

to consider in the calculations.

The business after buying the equipment is expected to generate gross revenues

of $170,000 each year in the first two years and is expected to be $240,000 each

year in the next two years, followed by $285,000 in the fifth year. The services

will be paid for by third parties and there is a demand for this new service. Since

the third-party payers will pay less than the full charge, assume that deductions

from revenue to average 20% of gross revenues in each of the five years. The

equipment cost is $560,000 and will cost $40,000 to install. After five years the

equipment will be retired, and it is expected that it could be sold for $80,000.

The costs for the service include part-time staffing costs of $14,000 and supply

costs of $8,000 in each of the first two years. For the following two years,

salaries are expected to be $18,000 and supplies are estimated to be $11,000;

and in the last year five, salaries are expected to be $24,000 and supplies are

expected to be $19,000. The equipment is under warranty in the first year so

there is no extra fee paid. A maintenance contract costing $6,000 per year will

be paid in years 2 through 5.

1

DK 11/26/23 v3.0

Name: _____________________

Required:

1. Use the template spreadsheet and 8-step process to enter the above

assumptions in the appropriate cells.

2. Compute the Net Present Value of Future Cash Flows, and the Internal

Rate of Return. Highlight in yellow those two answers on your

spreadsheet. Note those answers in the table below so that they are

in both places.

3. Note at the bottom of the schedule whether this is an attractive project

from a purely financial point of view based upon the numbers that you

calculated on the spreadsheet. Why did you make that decision? Note

your answers in the table below so that they are in both places.

Optional Additional Point Opportunity:

Copy your spreadsheet tab with your answer and label the new tab “Six

Years”. Add a Year 6 column and assume that year six cash inflows and

outflows will be the same as year 5, with the exception that the equipment

will be sold for $50,000 at the end of year 6 instead of $80,000 at the end

of year 5. Adjust any formulas in the cells as appropriate caused by

the addition of a year 6. Compute the new Net Present value and

Internal Rate of Return for this six-year project. Highlight those answers in

yellow on your spreadsheet. Note those answers in the table below so

that they are in both places.

Summarize your answer in the following table:

Description

Net Present Value (NPV) of Cash Flows:

Internal Rate of Return (IRR):

Is this an attractive project from a purely financial point of

view based upon the numbers that you calculated above?

Why did you make that decision?

Optional Additional Point Opportunity – NPV

Optional Additional Point Opportunity – IRR

Your Answer

When complete, upload in the Assignment folder both this Word document

with your answers included in the table above, and your Excel

Spreadsheet.

2

DK 11/26/23 v3.0

HEALTHCARE FINANCE

HAP395 – FALL 2023

FINAL EXAM –The Creation and Sharing of a “Bb Portfolio – Outward View”

And a Discounted Cash Flow Analysis in Excel

This document outlines the requirements for the Final exam.

1. The Final Exam Project must be completed by 10:00 pm, Friday, December 8. This

includes the “sharing” of your Bb Portfolio; your uploading of your Project Analysis

Excel Spreadsheet and Summary Word document; and completing your Module

14 Final Exam Bb Discussion postings. All parts are open-book and open notes.

2. Students can collaborate in preparing this Final Exam project by having discussions

with their classmates; however, each student must prepare and submit their own

final work.

3. The project consists of these three (3) parts, “A, B and C”:

A. Design, create, and share a “Bb Portfolio” devoted to the theme of

“Portfolios – An Outward View”. Blackboard describes their portfolios as

“a means to demonstrate formative and/or summative progress and

achievement”. It is a collection of “artifacts” that the student can include in

their online Bb portfolio space. These artifacts can be such things as

documents; weblinks; pictures; and articles. Students who completed

HAP309 Healthcare Accounting, completed a Bb Portfolio in their Final Exam.

That assignment introduced the concept of Bb Portfolios and placed an

“inward” focus from the student’s point of view. It was meant to be a learning

tool for the student, and not necessarily shared with anyone else, besides the

HAP309 instructor. Our theme there was a “Learning Portfolio”.

This Bb Portfolio in HAP395 builds upon that experience and changes the

focus to have an “outward” perspective. It is a tool whose purpose is to be

read by others.

A further description of that perspective, and examples that you may

include, as well as other resources on Portfolios are provided in the

Final Exam folder, and we will have a dedicated Bb Discussion Forum

that we will use to share ideas and answer questions.

B. Prepare a separate Excel Spreadsheet completing a Discounted Cash Flow

(DCF) analysis of a proposed project. That problem will be provided as a

separate document. Be sure to use the Discounted Cash Flow

spreadsheet template and the 8-step process provided in Module 12

Skill Building folder. There is no need to reinvent the wheel here.

C. Post in a Bb Discussion Board Forums in Modules 10 through 14, and the

Final Exam as instructed there. These postings will count as credit towards

the Final Exam.

1

DK 11/26/23 v3.0

4. As a guide to the creation of this assignment’s portfolio, the concept of “Design

Thinking” will be used. Using concepts from that model, you can think of this

portfolio as being a “prototype”, and not a final product.

There will be an optional Final Exam Ask the Professor Q&A Bb Discussion Board

Forum set up for any general questions that you may have on this Final Exam.

And there will be a required special Bb Discussion Board Forum devoted

specifically to the “Bb Portfolio – Outward View”.

2

DK 11/26/23 v3.0

DISCOUNTED CASH FLOW (DCF) ANALYSIS MODEL

COMPUTING NET PRESENT VALUE, INTERNAL RATE OF RETURN

PROFITABILITY INDEX, AND PAYBACK PERIOD

INVESTMENT ALTERNATIVE BLANK TEMPLATE

Line #

Start-up Costs

CASH INFLOWS (Incremental net revenue and/or cash savings)

Year 1

1 Gross Revenue

Year 2

$

–

$

Year 3

–

$

Year 4

–

$

–

Year 5

$

–

Total

$

–

2 Deductions from revenue

0

0

0

0

0

0

3 Net revenue (1 – 2 = 3)

0

0

0

0

0

0

4 Cost savings

0

0

0

0

0

0

5 Proceeds from sale of old (or new) equipment

0

0

0

0

0

0

6 Total cash inflow (3 + 4 + 5 = 6)

0

0

0

0

0

0

9 Labor

0

0

0

0

0

0

10 Supplies

0

0

0

0

0

0

11 Maintenance

0

0

0

0

0

0

CASH OUTFLOWS (incremental cash cost)

7 Purchase cost

$

–

8 Installation cost

0

12 Other

0

0

0

0

0

0

0

13 Total cash outflow (Total lines 7 thru 12)

0

0

0

0

0

0

0

14 NET CASH FLOW ( 6 – 13)

0

0

0

0

0

0

0

1.0000

0.9259

0.8573

0.7938

0.7350

0.6806

15 Discount rate and Present Value Factor @ 8.00%

16 Net present value of future cash flows (14 x 15)

$

–

$

–

$

–

$

–

$

–

$

–

17 Cumulative Present Value (to compute payback period) $

–

$

–

$

–

$

–

$

–

$

–

18 Payback Period (when cumulative present value is > 0)

#DIV/0!

19 Internal Rate of Return

using Excel =IRR(range,guess) with range being cash flows

on line 14 and “guess” being the rate on line 15

#NUM!

20 Profitability Index (NPV of Future Cash Flows / Initial Investment)

#DIV/0!

21 Payback Period using discounted cash flows

#DIV/0!

12/9/2023

1

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

$

–

#DIV/0!

14c6e6727afaa5bdb37a9a6c7bcc514f DCF Blank Template 1

DISCOUNTED CASH FLOW (DCF) ANALYSIS MODEL

COMPUTING NET PRESENT VALUE, INTERNAL RATE OF RETURN

PROFITABILITY INDEX, AND PAYBACK PERIOD

INVESTMENT ALTERNATIVE BLANK TEMPLATE

Line #

Start-up Costs

CASH INFLOWS (Incremental net revenue and/or cash savings)

Year 1

1 Gross Revenue

Year 2

$

–

$

Year 3

–

$

Year 4

–

$

–

Year 5

$

–

Total

$

–

2 Deductions from revenue

0

0

0

0

0

0

3 Net revenue (1 – 2 = 3)

0

0

0

0

0

0

4 Cost savings

0

0

0

0

0

0

5 Proceeds from sale of old (or new) equipment

0

0

0

0

0

0

6 Total cash inflow (3 + 4 + 5 = 6)

0

0

0

0

0

0

9 Labor

0

0

0

0

0

0

10 Supplies

0

0

0

0

0

0

11 Maintenance

0

0

0

0

0

0

CASH OUTFLOWS (incremental cash cost)

7 Purchase cost

$

–

8 Installation cost

0

12 Other

0

0

0

0

0

0

0

13 Total cash outflow (Total lines 7 thru 12)

0

0

0

0

0

0

0

14 NET CASH FLOW ( 6 – 13)

0

0

0

0

0

0

0

1.0000

0.9259

0.8573

0.7938

0.7350

0.6806

15 Discount rate and Present Value Factor @ 8.00%

16 Net present value of future cash flows (14 x 15)

$

–

$

–

$

–

$

–

$

–

$

–

17 Cumulative Present Value (to compute payback period) $

–

$

–

$

–

$

–

$

–

$

–

18 Payback Period (when cumulative present value is > 0)

#DIV/0!

19 Internal Rate of Return

using Excel =IRR(range,guess) with range being cash flows

on line 14 and “guess” being the rate on line 15

#NUM!

20 Profitability Index (NPV of Future Cash Flows / Initial Investment)

#DIV/0!

21 Payback Period using discounted cash flows

#DIV/0!

12/9/2023

2

#DIV/0!

#DIV/0!

#DIV/0!

#DIV/0!

$

–

#DIV/0!

14c6e6727afaa5bdb37a9a6c7bcc514f DCF Blank Template 2

DISCOUNTED CASH FLOW (DCF) ANALYSIS MODEL

COMPUTING NET PRESENT VALUE, INTERNAL RATE OF RETURN

PROFITABILITY INDEX, AND PAYBACK PERIOD

INVESTMENT ALTERNATIVE BLANK TEMPLATE

Line #

CASH INFLOWS (Incremental net revenue and/or cash savings)

Start-up Costs

Year 1

1 Gross Revenue

$

Year 2

60,000

$

Year 3

60,000

$

Year 4

Year 5

Total

60,000

$ 60,000

$ 60,000

$

300,000

2 Deductions from revenue

20,000

20,000

20,000

20,000

20,000

100,000

3 Net revenue (1 – 2 = 3)

40,000

40,000

40,000

40,000

40,000

200,000

4 Cost savings

0

0

0

0

0

0

5 Proceeds from sale of old (or new) equipment

0

0

0

0

10,000

10,000

40,000

40,000

40,000

40,000

50,000

210,000

9 Labor

0

0

0

0

0

0

10 Supplies

0

0

0

0

0

0

11 Maintenance

0

0

0

0

0

0

0

10,000

10,000

10,000

10,000

10,000

50,000

13 Total cash outflow (Total lines 7 thru 12)

100,000

10,000

10,000

10,000

10,000

10,000

50,000

14 NET CASH FLOW ( 6 – 13)

(100,000)

30,000

30,000

30,000

30,000

40,000

160,000

1.0000

0.9091

0.8264

0.7513

0.6830

0.6209

22,539

$ 20,490

$ 24,837

$

(4,904)

$ 19,933

1.00

0.20

6 Total cash inflow (3 + 4 + 5 = 6)

CASH OUTFLOWS (incremental cash cost)

7 Purchase cost

$

8 Installation cost

0

12 Other

15 Discount rate and Present Value Factor @

100,000

10.00%

16 Net present value of future cash flows (14 x 15)

$

(100,000)

$

27,273

$

17 Cumulative Present Value (to compute payback period)

$

(100,000)

$

(72,727)

$ (47,934)

$ (25,394)

1.00

1.00

1.00

18 Payback Period (when cumulative present value is > 0)

19 Internal Rate of Return

using Excel =IRR(range,guess) with range being cash flows

on line 14 and “guess” being the rate on line 15

$

$

19,933

4.20

17.23%

20 Profitability Index (NPV of Future Cash Flows / Initial Investment)

1.20

21 Payback Period using discounted cash flows

4.20

12/9/2023

24,793

3

14c6e6727afaa5bdb37a9a6c7bcc514f Project A Answer

DISCOUNTED CASH FLOW (DCF) ANALYSIS MODEL

COMPUTING NET PRESENT VALUE, INTERNAL RATE OF RETURN

PROFITABILITY INDEX, AND PAYBACK PERIOD

INVESTMENT ALTERNATIVE BLANK TEMPLATE

Line #

Start-up Costs

CASH INFLOWS (Incremental net revenue and/or cash savings)

Year 1

1 Gross Revenue

$

Year 2

60,000

$

Year 3

60,000

$

Year 4

Year 5

Total

60,000

$ 60,000

$ 60,000

$

300,000

2 Deductions from revenue

20,000

20,000

20,000

20,000

20,000

100,000

3 Net revenue (1 – 2 = 3)

40,000

40,000

40,000

40,000

40,000

200,000

4 Cost savings

0

0

0

0

0

0

5 Proceeds from sale of old (or new) equipment

0

0

0

0

10,000

10,000

40,000

40,000

40,000

40,000

50,000

210,000

9 Labor

0

0

0

0

0

0

10 Supplies

0

0

0

0

0

0

11 Maintenance

0

0

0

0

0

0

0

10,000

10,000

10,000

10,000

10,000

50,000

13 Total cash outflow (Total lines 7 thru 12)

110,000

10,000

10,000

10,000

10,000

10,000

50,000

14 NET CASH FLOW ( 6 – 13)

(110,000)

30,000

30,000

30,000

30,000

40,000

160,000

1.0000

0.9091

0.8264

0.7513

0.6830

0.6209

22,539

$ 20,490

$ 24,837

$

6 Total cash inflow (3 + 4 + 5 = 6)

CASH OUTFLOWS (incremental cash cost)

7 Purchase cost

$

8 Installation cost

0

12 Other

15 Discount rate and Present Value Factor @ 10.00%

16 Net present value of future cash flows (14 x 15)

110,000

$

(110,000)

$

27,273

$

17 Cumulative Present Value (to compute payback period) $

(110,000)

$

(82,727)

$ (57,934)

$ (35,394)

$ (14,904)

1.00

1.00

1.00

1.00

18 Payback Period (when cumulative present value is > 0)

19 Internal Rate of Return

using Excel =IRR(range,guess) with range being cash flows

on line 14 and “guess” being the rate on line 15

$

$

9,933

9,933

0.60

4.60

13.34%

20 Profitability Index (NPV of Future Cash Flows / Initial Investment)

1.09

21 Payback Period using discounted cash flows

4.60

12/9/2023

24,793

4

14c6e6727afaa5bdb37a9a6c7bcc514f Project A Answer – change cost

DISCOUNTED CASH FLOW (DCF) ANALYSIS MODEL

COMPUTING NET PRESENT VALUE, INTERNAL RATE OF RETURN

PROFITABILITY INDEX, AND PAYBACK PERIOD

INVESTMENT ALTERNATIVE BLANK TEMPLATE

Line #

Start-up Costs

CASH INFLOWS (Incremental net revenue and/or cash savings)

Year 1

1 Gross Revenue

$

Year 2

60,000

$

Year 3

60,000

$

Year 4

Year 5

Total

60,000

$ 60,000

$ 60,000

$

300,000

2 Deductions from revenue

20,000

20,000

20,000

20,000

20,000

100,000

3 Net revenue (1 – 2 = 3)

40,000

40,000

40,000

40,000

40,000

200,000

4 Cost savings

0

0

0

0

0

0

5 Proceeds from sale of old (or new) equipment

0

0

0

0

10,000

10,000

40,000

40,000

40,000

40,000

50,000

210,000

9 Labor

0

0

0

0

0

0

10 Supplies

0

0

0

0

0

0

11 Maintenance

0

0

0

0

0

0

0

10,000

10,000

10,000

10,000

10,000

50,000

13 Total cash outflow (Total lines 7 thru 12)

100,000

10,000

10,000

10,000

10,000

10,000

50,000

14 NET CASH FLOW ( 6 – 13)

(100,000)

30,000

30,000

30,000

30,000

40,000

160,000

1.0000

0.9259

0.8573

0.7938

0.7350

0.6806

23,815

$ 22,051

$ 27,223

$

(636)

$ 26,587

1.00

0.02

6 Total cash inflow (3 + 4 + 5 = 6)

CASH OUTFLOWS (incremental cash cost)

7 Purchase cost

$

8 Installation cost

0

12 Other

15 Discount rate and Present Value Factor @ 8.00%

16 Net present value of future cash flows (14 x 15)

100,000

$

(100,000)

$

27,778

$

17 Cumulative Present Value (to compute payback period) $

(100,000)

$

(72,222)

$ (46,502)

$ (22,687)

1.00

1.00

1.00

18 Payback Period (when cumulative present value is > 0)

19 Internal Rate of Return

using Excel =IRR(range,guess) with range being cash flows

on line 14 and “guess” being the rate on line 15

$

$

26,587

4.02

17.23%

20 Profitability Index (NPV of Future Cash Flows / Initial Investment)

1.27

21 Payback Period using discounted cash flows

4.02

12/9/2023

25,720

5

14c6e6727afaa5bdb37a9a6c7bcc514f Project A Answer – change rate