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
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
