Answer & Explanation:cash flow estimation, breakeven analysis, and profitability analysis. Please see attached notes, problems, and mini-case. Please have results by Wednesday October 5, 2016 at 12 Noon.
chapter_11_review_notes.xlsx
chapter_11_problems_1.xlsx
chapter_11_mini_case.xlsx
Unformatted Attachment Preview
4/1/2015 UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT
Table of Contents
Chapter 11 — Capital Budgeting
THE IMPACT OF CASH FLOW TIMING (NO MATCHING TEXT SECTION)
CASH FLOW ESTIMATION EXAMPLE (PAGE 482)
EVALUATING PROJECTS WITH UNEQUAL LIVES (PAGE 499)
Note: Each link takes you directly to that section in the next tab, CH11MODEL.
Page numbers in parentheses correspond to the textbook.
A
B
C
D
E
F
G
H
1 4/1/2015 UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT
I
J
K
L
M
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
Chapter 11 — Capital Budgeting
This spreadsheet model performs some of the calculations contained in Chapter 11. We recommend that you use the model in the
following manner:
1. First, recognize that you do not have to use this model at all to understand the basics of capital budgeting. However, if you do
use the model and experiment with it, this will increase your understanding of the concepts, and it will surely help you when you
use spreadsheet models for other purposes, especially any problem sets or cases assigned for this course.
2. Start by reading the chapter in its entirety.
3. Now place the text alongside your computer with this model on the screen. When you come to an explanation of a calculation
in the text, see if the model has a matching calculation. Use the Table of Contents tab for improved navigation of the model.
4. We assume that you know the basics of Excel, but that you have not encountered some of its features or that you may need a
refresher or two. So, we have built in explanations of how to do some of the functions in the model. As a result, you will learn
more about Excel at the same time you learn about the basics of capital budgeting.
5. Throughout this model, page numbers of the matching text calculations are provided in pink. Input data are in red on a
yellow background, and output data are in green on a beige background. You are encouraged to change the input data to learn
more about the calculations in the model.
THE IMPACT OF CASH FLOW TIMING (NO MATCHING TEXT SECTION)
In this section, we analyze two projects (S and L) to examine the effects of cash flow timing. Along the way,
we also discuss the breakeven and profitability measures that are discussed in the text. Here are the input data:
Year (t)
0
1
2
3
4
Expected net
cash flows (CFt)
Project S Project L
($1,000)
($1,000)
500
100
400
300
300
400
100
600
Project S
0
(1,000)
1
500
2
400
3
300
4
100
3
400
4
600
Project L
0
(1,000)
1
100
2
300
Payback Period
The payback period is defined as the expected number of years required to recover the investment, and it was the
first formal method used to evaluate capital budgeting projects. First, we identify the year in which the cumulative
cash inflows exceed the initial cash outflows. That is the payback year. Then we take the previous year and add to it
unrecovered balance at the end of that year divided by the following year’s cash flow. Generally speaking, the
shorter the payback period, the better the investment.
Click fx, then Logical, then AND, then OK to get
dialog box. Then specify you want TRUE if
cumulative CF > 0 but the previous CF < 0.
Project S
There will be one TRUE.
0
1
2
3
4
Click fx, then Logical, then IF, then OK, and then
Net cash flow (1,000)
500
400
300
100
if true, payback is the previous year plus a fraction,
Cumulative cash flow (1,000)
(500)
(100)
200
300
if false, then 0.
FALSE
FALSE
FALSE
TRUE
FALSE
0.00
0.00
0.00
2.33
0.00
Click fx, then Statistical, then MAX, then OK, and
Payback =
2.33
then the range (C57 to G57) to find the payback.
Payback =
2.33
Alternatively, use nested IF statements to
find the payback.
Project L
Net cash flow
Cumulative cash flow
Payback =
0
(1,000)
(1,000)
3.33
1
100
(900)
2
300
(600)
Uses IF statement.
3
400
(200)
4
600
400
A
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
B
C
D
E
F
G
H
I
J
Discounted Payback Period
The discounted payback period is identical to the regular payback period, except that the calculation is
based on discounted cash flows. Note that both projects are assumed to have a cost of capital of 10 percent.
Project cost of capital =
10%
Project S
Net cash flow
Discounted cash flow
Disc. cum. cash flow
0
(1,000)
(1,000)
(1,000)
Discounted payback =
2.95
1
500
455
(545)
2
400
331
(215)
3
300
225
11
4
100
68
79
1
100
91
(909)
2
300
248
(661)
3
400
301
(361)
4
600
410
49
Project L
Net cash flow
Discounted cash flow
Disc. cum. cash flow
0
(1,000)
(1,000)
(1,000)
Discounted payback =
3.88
The inherent problem with both paybacks is that they ignore cash flows that occur after the payback period mark.
While the discounted payback accounts for timing issues, it still falls short of being a complete measure.
However, these two methods do provide information about a project’s liquidity and risk.
Net Present Value (NPV)
To calculate the NPV, we sum the present values of the individual net cash flows. NPV represents the contribution
(on a present value basis) of the project to owner’s wealth.
Project cost of capital =
10%
Project S
Net cash flow
Discounted net CF
NPVS =
$78.82
0
(1,000)
(1,000)
1
500
455
2
400
331
Sums the discounted CFs, or
3
300
225
$78.82
4
100
68
uses the NPV function.
Project L
Net cash flow
Discounted net CF
NPVL =
$49.18
0
(1,000)
(1,000)
1
100
91
2
300
248
Sums the discounted CFs, or
3
400
301
$49.18
4
600
410
uses the NPV function.
The NPV method of capital budgeting dictates that all independent projects that have positive NPV should accepted.
The rationale is that all such projects add wealth, which should be the goal of the financial management function.
If two mutually exclusive projects were being evaluated, the one with the higher NPV should be chosen.
K
L
M
A
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
B
C
D
E
F
G
H
I
J
K
Internal Rate of Return (IRR)
The internal rate of return is defined as the discount rate that equates the present value of a project’s cash inflows
to the present value of its outflows. In other words, the internal rate of return is the discount rate that forces NPV to zero.
The calculation of IRR used to be tedious, but Excel provides an IRR function that merely requires you to access the
function and specify the range of cash flows. The IRRs for Project S and L are shown below:
Year (t)
0
1
2
3
4
IRR =
Expected net
cash flows (CFt)
Project S Project L
($1,000)
($1,000)
500
100
400
300
300
400
100
600
14.5%
The IRR function assumes that the cash flows in the range specified
occur at end of each period, so no adjustment is necessary.
11.8%
Note that if a guess is not entered,
the function assumes a value of
10%. A guess is needed to start
the trial-and-error process that
Excel used to calculate IRR.
Projects should be accepted if their IRR is greater than the cost of capital. When mutually exclusive projects are
being evaluated, the one with the higher IRR should be chosen.
When dealing with independent projects, the NPV and IRR methods will always yield the same accept/reject result.
However, in the case of mutually exclusive projects, NPV and IRR can give conflicting results. One shortcoming of
the internal rate of return is that it assumes that cash flows received are reinvested at the project’s internal rate of
return, which is not usually true. This point is examined in a later section of this model.
Multiple IRRs
Because of the mathematics involved, it is possible for some (but not all) projects that have more than one change of
signs in the net cash flows to have more than one IRR. If you attempted to find the IRR with such a project using a
financial calculator, you would get an error message. The HP-10B says “Error – Soln” and the HP-17B says
“Many/No Solutions; Key in Guess” when such a project is evaluated. The procedure for correcting the problem is
to store in a guess for the IRR, and then the calculator will report the IRR that is closest to your guess. You can
then use a different “guess” value, and you should be able to find the other IRR. However, the nature of the
mathematics creates a scenario in which one IRR is quite extraordinary (often, a few hundred percent). Consider
the case of Project M.
0
(1.6)
1
10
2
(10)
We will solve this IRR twice, the first time using the default guess of 10 percent, and the second time we will enter a
guess of 300 percent. Notice, that the first IRR calculation is exactly as it was above.
IRR =
25.0%
when the default guess of 10% is used to start the calculation.
IRR =
400%
when a guess of 300% is used to start the calculation.
L
M
A
B
C
D
E
F
G
H
I
J
The two solutions to this problem tell us that this project will have a positive NPV for all costs of capital between
25 percent and 400 percent. We illustrate this point by creating a data table and a graph of the project NPVs.
Project M:
k
0%
25%
50%
75%
100%
125%
150%
175%
200%
225%
250%
275%
300%
325%
350%
375%
400%
425%
450%
475%
500%
525%
550%
25.0%
0.00
1
10
2
(10)
= Discount rate
NPV
0.00
(1.60)
0.00
0.62
0.85
0.90
Max.
0.87
0.80
0.71
0.62
0.53
0.44
0.36
0.28
0.20
0.13
0.06
0.00
(0.06)
(0.11)
(0.16)
(0.21)
(0.26)
(0.30)
Multiple Rates of Return
$1.50
$1.00
$0.50
$0.00
NPV
R(R)
NPV =
0
(1.6)
0%
100%
200%
300%
400%
500%
-$0.50
-$1.00
-$1.50
-$2.00
Discount Rate
NPV Profiles
NPV profiles, as illustrated in the graph above, show the relationship between NPV and discount rate (cost of capital = k).
Here we create NPV profiles for Projects S and L.
Project S
$78.82
300.00
249.74
202.77
158.79
117.55
78.82
42.39
8.08
(24.27)
k
0%
2%
4%
6%
8%
10%
12%
14%
16%
Project L
$49.18
400.00
317.63
242.00
172.44
108.35
49.18
(5.53)
(56.20)
(103.21)
k
0%
2%
4%
6%
8%
10%
12%
14%
16%
NPV Profiles of Projects S and L
500
400
Project L
300
NPV
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
200
Project S
100
0
(100)
0%
2%
4%
6%
8%
10%
(200)
Cost of Capital
12%
14%
16%
K
L
M
A
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
B
C
D
E
F
G
H
I
J
K
L
4
5
Modified Internal Rate of Return (MIRR)
The modified internal rate of return is the discount rate that causes a project’s cost (or cash outflows) to equal the
present value of the project’s terminal value. The terminal value is defined as the sum of the future values of the
project’s cash inflows, compounded at the project’s cost of capital. To find MIRR, calculate the PV of the outflows
and the FV of the inflows, and then find the rate that equates the two. Or, you can solve using the MIRR function.
Cost of capital
10%
Project S
0
(1,000)
MIRRS =
1
500
2
400
3
300
4
100
3
400
4
600
440.0
363.0
133.1
1,536.1
12.1%
Project L
0
(1,000)
PV =
1
100
(1,000)
2
300
Terminal value =
MIRRL =
11.3%
MIRRL =
11.3%
The advantage of using the MIRR, relative to the IRR, is that the MIRR assumes that cash flows received are
reinvested at the cost of capital, not the IRR rate. Because reinvestment at the cost of capital is more likely, the MIRR is a
better indicator of a project’s profitability. Moreover, it solves the multiple IRR problem, as a set of cash flows can
have but one MIRR.
CASH FLOW ESTIMATION EXAMPLE (PAGE 482)
The project being analyzed here is the same as in the text, although the analysis is in thousands of dollars. This presents you
with a good opportunity to see how changes to input values affect profitability. For example, change the tax rate input to
40 percent to see what the profitability of the project would be for a taxable hospital.
System cost
Related expenses
Weekly volume
Average charge
Uncollectable percent
Labor costs
Maintenance costs
Supplies cost/patient
Incremental overhead
Depreciation
Net salvage value
Inflation rate
Tax rate
Cost of capital
NPV =
IRR =
MIRR =
$1,500
$1,000
40
$500
25.0%
$50
$150
$15
$10
$350
$750
5.0%
0.0%
10.0%
$82
11.1%
10.7%
System cost
Related expenses
Gross revenues
Deductions
Net revenues
Labor costs
Maintenance costs
Supplies
Incremental overhead
Depreciation
Operating income
Taxes
Net op. income
Depreciation
Net salvage value
Net cash flow
0
($1,500)
(1,000)
($2,500)
1
2
3
$1,000
250
$750
50
150
30
10
350
$160
0
$160
350
$1,050
263
$788
53
158
32
11
350
$186
0
$186
350
$1,103
276
$827
55
165
33
11
350
$212
0
$212
350
$1,158
289
$868
58
174
35
12
350
$240
0
$240
350
$1,216
304
$912
61
182
36
12
350
$270
0
$270
350
750
$510
$536
$562
$590
$1,370
M
A
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
B
C
D
E
F
G
H
I
J
K
L
M
EVALUATING PROJECTS WITH UNEQUAL LIVES (PAGE 499)
Replacement Chain (Common Life) Analysis
If two mutually exclusive projects have different lives, and if the projects can be repeated, then it is necessary to deal explicitly
with those unequal lives. In this section, we use the replacement chain (common life) approach to deal with the problem.
Corporate cost of capital
Year
0
1
2
3
4
5
6
NPV =
IRR =
11.5%
Project C
($40,000)
$8,000
$14,000
$13,000
$12,000
$11,000
$10,000
Project F
($20,000)
$7,000
$13,000
$12,000
$7,165
17.5%
$5,391
25.2%
Initially, based on NPV, it would appear that Project C is the better investment. However, if the firm chooses Project F,
it would have the opportunity to make the same investment three years from now. Therefore, we must reevaluate Project F
using an extended common life of six years.
Year
0
1
2
3
4
5
6
NPV =
IRR =
Project C
($40,000)
$8,000
$14,000
$13,000
$12,000
$11,000
$10,000
Project F
($20,000)
$7,000
$13,000
($8,000)
7,000
13,000
12,000
$7,165
17.5%
$9,281
25.2%
This cell contains the Year 3 cash flow from the first project
plus the investment required to replicate the project.
On the basis of this extended analysis, it is clear that Project F is the better of the two investments. Note that the IRR sends
this signal on both the “regular” and common life analyses.
End of Model
9/1/2014
UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT
Chapter 11 — Capital Budgeting
PROBLEM 1
Winston Clinic is evaluating a project that costs $52,125 and has expected net cash flows of $12,000 per
year for eight years. The first inflow occurs one year after the cost outflow, and the project has a cost of
capital of 12 percent.
a. What is the project’s payback?
b. What is the project’s NPV? Its IRR?
c. Is the project financially acceptable? Explain your answer.
ANSWER
UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT
Chapter 11 — Capital Budgeting
PROBLEM 2
Better Health, Inc., is evaluating two investment projects, each of which requires an up-front expenditure
of $1.5 million. The projects are expected to produce the following net cash inflows:
Year
Project A
Project B
0 -$1,500,000 -$1,500,000
1
$500,000 $2,000,000
2 $1,000,000 $1,000,000
3 $2,000,000
$600,000
a. What is each project’s IRR?
b. What is each project’s NPV if the cost of capital is 10 percent? 5 percent? 15 percent?
ANSWER
UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT
Chapter 11 — Capital Budgeting
PROBLEM 3
Capitol Health Plans, Inc., is evaluating two different methods for providing home health services to its
members. Both methods involve contracting out for services, and the health outcomes and revenues are
not affected by the method chosen. Therefore, the incremental cash flows for the decision are all outflows.
Here are the projected flows:
Year
Method A Method B
0 -$300,000 -$120,000
1 -$66,000 -$96,000
2 -$66,000 -$96,000
3 -$66,000 -$96,000
4 -$66,000 -$96,000
5 -$66,000 -$96,000
a. What is each alternative’s IRR?
b. If the cost of capital for both methods is 9 percent, which method should be chosen? Why?
ANSWER
UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT
Chapter 11 — Capital Budgeting
PROBLEM 4
Great Lakes Clinic has been asked to provide exclusive healthcare services for next year’s World
Exposition. Although flattered by the request, the clinic’s managers want to conduct a financial analysis
of the project. There will be an up-front cost of $160,000 to get the clinic in operation. Then, a net cash
inflow of $1 million is expected from operations in each of the two years of the exposition. However, the
clinic has to pay the organizers of the exposition a fee for the marketing value of the opportunity. This
fee, which must be paid at the end of the second year, is $2 million.
a. What are the cash flows associated with the project?
b. What is the project’s IRR?
c. Assuming a project cost of capital of 10 percent, what is the project’s NPV?
ANSWER
UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT
Chapter 11 — Capital Budgeting
PROBLEM 5
Assume that you are the CFO at Porter Memorial Hospital. The CEO has asked you to
analyze two proposed capital investments: Project X and Project Y. Each project requires a net
investment outlay of $10,000, and the cost of capital for each project is 12 percent. The project’s expected
net cash flows are as follows:
Year
0
1
2
3
4
Project X Project Y
-$10,000 -$10,000
$6,500
$3,000
$3,000
$3,000
$3,000
$3,000
$1,000
$3,000
a. Calculate each project’s payback period, net present value (NPV), and internal rate of return (IRR).
b. Which project (or projects) is financially acceptable? Explain your answer.
ANSWER
UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT
Chapter 11 — Capital Budgeting
PROBLEM 6
The director of capital budgeting for Big Sky Health Systems, Inc., has estimated the following cash flows
in thousands of dollars for a proposed new service:
Year
0
1
2
3
Expected Net
Cash Flow
-100
70
50
20
The project’s cost of capital is 10 percent.
a. What is the project’s payback period?
b. What is the project’s NPV?
c. What is the project’s IRR?
ANSWER
UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT
Chapter 11 — Capital Budgeting
PROBLEM 7
California Health Center, a for-profit hospital, is evaluating the purchase of new diagnostic equipment.
The equipment, which costs $600,000, has an expected life of five years and an estimated pretax salvage
value of $200,000 at that time. The equipment is expected to be used 15 times a day for 250 days a year
for each year of the project’s life. On average, each procedure is expected to generate $80 in collections,
which is net of bad debt losses and contractual allowances, in its first year of use. Thus, net revenues for
Year 1 are estimated at 15 X 250 X $80 = $300,000.
Labor and maintenance costs are expected to be $100,000 during the first year of operation, while utilities
will cost another $10,000 and cash overhead will increase by $5,000 in Year 1. The cost for expendable
supplies is expected to average $5 per procedure during the first year. All costs and revenues, except
depreciation, are expected to increase at a 5 percent inflation rate after the first year.
The equipment falls into the MACRS five-year class for tax depreciation and hence is subject to the
following depreciation allowances:
Year
1
2
3
4
5
6
Allowance
0.2
0.32
0.19
0.12
0.11
0.06
The hospital’s tax rate is 40 percent, and its corporate cost of capital is …
Purchase answer to see full
attachment
